Postgres

To test against Postgres docker test container set the platform to postgres in src/test/resources/application-test.yaml

Refer to docs / testing if application-test.yaml doesn't exist yet.

ebean:
  test:
    platform: postgres # h2, postgres, ...
    ddlMode: dropCreate # none | dropCreate | migrations | create
    dbName: my_app

That is all we need to do. Running tests via IDE, maven or gradle will all automatically setup a docker test container for postgres including creating the database and user etc.

The above will use the following defaults:

ebean-postgres dependency

We can use the io.ebean:ebean-postgres dependency rather than io.ebean:ebean if we want to only bring in the Postgres specific platform code. Depending on io.ebean:ebean will bring in all platforms.

Postgres types

UUID

UUID is mapped to native Postgres UUID type.

INET

java.net.InetAddress and io.ebean.types.Inet are both automatically mapped to native Postgres INET type. When using InetAddress we need to take care that it doesn't perform unwanted DNS lookup validating addresses. io.ebean.types.Inet is a simple value type.

Array types - @DbArray

We use @DbArray to map Lists or Sets of UUID, String, Enums, Number types. These are mapped to Postgres array types like uuid[], varchar[], integer[] ...

For more on Postgres Array types.

JSON / JSONB - @DbJson

We can use @DbJson and @DbJsonB to map content to Postgres JSON or JSONB types.

HSTORE - @DbMap

We can use @DbMap to map Map<String,String> properties to Postgres HSTORE type.

Postgres ANY

Postgres has an ANY operator which allows binding an Array of values. This can provide a very big benefit when using = ANY(?) rather than the usual IN (?,?,? ...) when the number of bind values for an IN clause is variable. Effectively no matter the number of bind values with Postgres = ANY we get to use the exact same SQL. This means we need only use a single Ebean query plan, a single JDBC PreparedStatement and the database server also only see the one sql statement and typically means that it only needs to parse that once to determine the query plan ("the database has to do less hard parsing").

For these reasons Ebean will use Postgres = ANY whenever it can rather than use a IN clause.

Additionally you often want to use = ANY in SQL statements for DtoQuery and SqlQuery.

History support

History support for Postgres is provided by generating triggers and history table.

Table Partitioning

Postgres 10 added support for table partitioning. We use @DbPartition to define the table should have range partitioning based on DAY, WEEK, MONTH or YEAR.

@DbPartition(mode = DAY, property = "eventTime")
@Entity
@Table(name = "event")
public class DEvent extends BaseDomain {
  ...

Extensions

We may want to use extensions like hstore and pgcrypto. Note that when using ebean-test with docker it will automatically add those 2 extensions by default.

We can specify extensions that should be installed automatically via:

ebean:
  test:
    platform: postgres #, h2, postgres, mysql, oracle, sqlserver
    ddlMode: dropCreate # none | dropCreate | migrations | create
    dbName: myapp
    postgres:
      extensions: pgcrypto, hstore

Schema

With Postgres it could be considered good practice to create our tables into a named schema (and not the public schema). If we want to do this we have made this easy in Ebean version 11.18.2 where we can specify ebean.dbSchema and this is then used for both DB migrations and create-all.sql.

It is advisable that the DB User matches the DB Schema. When this is done then there is no need to use currentSchema or modify the search path. For example, if I want to use a schema called myapp it is advisable to have the DB user/role match and be myapp.

application.yaml

ebean:
  dbSchema: myapp  ## use this database schema

DatabaseConfig

databaseConfig.setDbSchema("myapp");

Docker container

We can programmatically start a docker container version of Postgres. This can be a useful way to run an application locally.

The below uses ebean-test-docker dependency which already comes with ebean-test. If we do not have a dependency on ebean-test then add io.ebean:ebean-test-docker:5.0 as a dependency.

package main;

import io.ebean.docker.commands.PostgresContainer;

public class StartPostgres {

  public static void main(String[] args) {

    PostgresContainer container = PostgresContainer.newBuilder("14")
      // .port(5432) // Note: defaults to 6432
      .dbName("my_app")
      .user("my_app")
      .password("silly")
      .containerName("pg14x")
      .extensions("hstore,pgcrypto")
      .build();

    container.start();
  }
}

The above will programmatically start a Postgres 13 docker container on port 6432. It will create a database and user with container name pg13x. It will drop and re-create the container if the container already exists.

Docker trace logging

Set the logging level for io.ebean.docker to trace to help trouble shoot any issues or understand what ebean-test-docker is doing.

PostGIS

To use PostGIS goto the PostGIS documentation.