Skip to content
Permalink
master
Switch branches/tags
Go to file
 
 
Cannot retrieve contributors at this time
<html>
<head>
<title>Postgres | Ebean ORM</title>
<meta name="layout" content="_layout2/base-docs.html"/>
<meta name="bread1" content="Database platforms" href="/docs/database"/>
<meta name="bread2" content="Postgres" href="/docs/database/postgres"/>
<#assign n0_docs="active">
<#assign n1_platforms="active">
<#assign n2_postgres="active">
</head>
<body>
<h2 id="postgres">Postgres</h2>
<p>
To test against Postgres docker test container set the <em>platform</em> to <code>postgres</code> in
<code>src/test/resources/application-test.yaml</code>
</p>
<p>
Refer to <a href="/docs/testing">docs / testing</a> if application-test.yaml doesn't exist yet.
</p>
<pre content="yml">
ebean:
test:
platform: postgres # h2, postgres, ...
ddlMode: dropCreate # none | dropCreate | migrations | create
dbName: my_app
</pre>
<p>
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.
</p>
<p>
The above will use the following defaults:
</p>
<table class="compact w100">
<tr><th>username:</th><td>{dbName}</td></tr>
<tr><th>password:</th><td>test</td></tr>
<tr><th>port:</th><td>6432</td></tr>
<tr><th>url:</th><td>jdbc:postgresql://localhost:{port}/{dbName}</td></tr>
<tr><th>image:</th><td>postgres:{version:12}</td></tr>
</table>
<h2 id="types">Postgres types</h2>
<h5>UUID</h5>
<p>
UUID is mapped to native Postgres UUID type.
</p>
<h5>INET</h5>
<p>
<em>java.net.InetAddress</em> and <em>io.ebean.types.Inet</em> 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. <em>io.ebean.types.Inet</em> is a simple value type.
</p>
<h5>Array types - @DbArray</h5>
<p>
We use <a href="/docs/mapping/extensions/dbarray">@DbArray</a> to map Lists or Sets of UUID, String, Enums, Number types.
These are mapped to Postgres array types like <em>uuid[], varchar[], integer[] ...</em>
</p>
<p>
For more on <a href="arraytype">Postgres Array types</a>.
</p>
<h5>JSON / JSONB - @DbJson</h5>
<p>
We can use <a href="/docs/mapping/extensions/dbjson">@DbJson</a> and <a href="/docs/mapping/extensions/dbjson">@DbJsonB</a> to map content to Postgres JSON or JSONB
types.
</p>
<h5>HSTORE - @DbMap</h5>
<p>
We can use <a href="/docs/mapping/extensions/dbmap">@DbMap</a> to map <code>Map&lt;String,String&gt;</code> properties to
Postgres HSTORE type.
</p>
<h2 id="any">Postgres ANY</h2>
<p>
Postgres has an <code>ANY</code> operator which allows binding an Array of values. This can provide a very big
benefit when using <code> = ANY(?)</code> rather than the usual <code> IN (?,?,? ...)</code> when the number of
bind values for an IN clause is variable. Effectively no matter the number of bind values with Postgres <code>= ANY</code>
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").
</p>
<p>
For these reasons Ebean will use Postgres <code>= ANY</code> whenever it can rather than use a <code>IN</code> clause.
</p>
<p>
Additionally you often want to use <code>= ANY</code> in SQL statements for <a href="/docs/query/dtoquery#any">DtoQuery</a>
and <a href="/docs/query/sqlquery#any">SqlQuery</a>.
</p>
<h2 id="history">History support</h2>
<p>
History support for Postgres is provided by generating triggers and history table.
</p>
<h2 id="partitioning">Table Partitioning</h2>
<p>
Postgres 10 added support for table partitioning. We use <code>@DbPartition</code> to define the
table should have range partitioning based on DAY, WEEK, MONTH or YEAR.
</p>
<pre content="java">
@DbPartition(mode = DAY, property = "eventTime")
@Entity
@Table(name = "event")
public class DEvent extends BaseDomain {
...
</pre>
<h2 id="extensions">Extensions</h2>
<p>
We may want to use extensions like <code>hstore</code> and <code>pgcrypto</code>.
Note that when using ebean-test with docker it will automatically add those 2
extensions by default.
</p>
<p>
We can specify extensions that should be installed automatically via:
</p>
<pre content="yml">
ebean:
test:
platform: postgres #, h2, postgres, mysql, oracle, sqlserver
ddlMode: dropCreate # none | dropCreate | migrations | create
dbName: myapp
postgres:
extensions: pgcrypto, hstore
</pre>
<h2 id="schema">Schema</h2>
<p>
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 <code>ebean.dbSchema</code>
and this is then used for both DB migrations and create-all.sql.
</p>
<p>
It is advisable that the DB User matches the DB Schema. When this is done then there is no need to use
<code>currentSchema</code> or modify the <code>search path</code>. For example, if I want to use a schema called
<code>myapp</code> it is advisable to have the DB user/role match and be <code>myapp</code>.
</p>
<h5>application.yaml</h5>
<pre content="yml">
ebean:
dbSchema: myapp ## use this database schema
</pre>
<h5>DatabaseConfig</h5>
<pre content="java">
databaseConfig.setDbSchema("myapp");
</pre>
<h2 id="starting">Starting docker postgres programmatically</h2>
<p>
We can programmatically start a docker container version of Postgres. This can be a useful way to run an
application locally.
</p>
<p>
The below uses <code>ebean-test-docker</code> dependency which already comes with <code>ebean-test</code>.
If we do not have a dependency on <code>ebean-test</code> then add <code>io.ebean:ebean-test-docker:4.2</code>
as a dependency.
</p>
<pre content="java">
package main;
import io.ebean.docker.commands.PostgresConfig;
import io.ebean.docker.commands.PostgresContainer;
public class StartPostgres {
public static void main(String[] args) {
PostgresConfig config = new PostgresConfig("13");
// config.setPort(5432); // Note: defaults to 6432
config.setDbName("my_app");
config.setUser("my_app");
config.setPassword("silly");
config.setContainerName("pg13x");
config.setExtensions("hstore,pgcrypto");
PostgresContainer container = new PostgresContainer(config);
container.startWithDropCreate();
}
}
</pre>
<p>
The above will programmatically start a Postgres 13 docker container on <b>port 6432</b>. It will create a database
and user with container name <em>pg13x</em>. It will drop and re-create the container if the container already exists.
</p>
<h3>Docker trace logging</h3>
<p>
Set the logging level for <code>io.ebean.docker</code> to <code>trace</code> to help trouble shoot any issues or
understand what ebean-test-docker is doing.
</p>
<h2 id="postgis">PostGIS</h2>
<p>
To use PostGIS goto the <a href="postgis">PostGIS documentation</a>.
</p>
<@next_edit "MySql" "/docs/database/mysql" "/docs/database/postgres/index.html"/>
</body>
</html>