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:

username:{dbName}
password:test
port:6432
url:jdbc:postgresql://localhost:{port}/{dbName}
image:postgres:{version:12}

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.

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");

PostGIS

To use PostGIS goto the PostGIS documentation.