PostgreSQL

List the postgres package beside the starter, run the generator, and give a postgresql:// URL. The generator writes a PostgresBinding over the package and the postgresJdbcDriver bean; the starter's PostgresJdbcDriver and PostgresDialect do the rest. The postgres package is the application's dependency, never the starter's.

Why

PostgreSQL is the usual database of a server application: several instances share it, it has real row locks and every isolation level, and it enforces your constraints. The starter's PostgreSQL support is the one its transaction rules are measured on: 304 scenarios run against PostgreSQL in the starter and in a Spring Boot application, and their outcomes are compared.

Example

# pubspec.yaml
dependencies:
  spring_boot_starter_data: ^0.9.0
  postgres: ^3.5.0
# lib/resources/application.properties
spring.datasource.url=postgresql://db.example.com:5432/shop?sslmode=verify-full
spring.datasource.username=shop
spring.datasource.password=${SHOP_DB_PASSWORD}
spring.datasource.postgresql.application-name=shop-api
spring.datasource.postgresql.search-path=shop,public
spring.datasource.postgresql.connect-timeout=5s

# or, as pasted from Spring Boot:
# spring.datasource.url=jdbc:postgresql://db.example.com:5432/shop

The URL

Settings

Key Default Meaning
spring.datasource.postgresql.sslmode prefer Whether and how the connection uses TLS: disable, allow, prefer, require, verify-ca, verify-full.
spring.datasource.postgresql.ssl-root-cert the system's roots PEM file of the certificate authorities a verify-* mode checks the server against.
spring.datasource.postgresql.connect-timeout 15s The longest wait for the connection and the TLS handshake: 500ms, 30s, 1m, or milliseconds; this key does not take PT30S.
spring.datasource.postgresql.application-name none Shown in pg_stat_activity.
spring.datasource.postgresql.search-path the server's The schemas of search_path, comma separated.

The same keys exist under spring.datasources.<name>.postgresql.*, as spring.datasource.postgres.* (the driver's name wins), and as environment variables (SPRING_DATASOURCE_POSTGRESQL_SSLMODE=verify-full, SPRING_DATASOURCES_SHOP_POSTGRESQL_SSLMODE for a named database).

TLS

sslmode is libpq's:

Mode Encrypted Certificate checked
disable no no
allow, prefer (default) if the server offers TLS; plain text otherwise no
require yes no
verify-ca, verify-full yes yes, with the host name (verify-ca is as strict as verify-full)

The default prefer connects in plain text when the server has no TLS, which an attacker who can cut the connection can force. When it falls back, the driver logs a warning, once for each database. Use require or verify-full for anything but a development database.

Values

Dart PostgreSQL
int BIGINT
String, enums TEXT
bool BOOLEAN
double DOUBLE PRECISION
DateTime TIMESTAMPTZ (the instant, read back in UTC)
Uint8List BYTEA
BigInt NUMERIC

A generated id is BIGINT GENERATED BY DEFAULT AS IDENTITY, read back with RETURNING. An id you write yourself does not move the sequence.

Transactions

Locks

@Lock(LockMode.pessimisticWrite) is FOR UPDATE, pessimisticRead is FOR SHARE. A zero timeout is NOWAIT, a longer one is SET LOCAL lock_timeout before the query, and skipLocked is SKIP LOCKED (it wins over a zero timeout: the server refuses both words). Unlike SQLite, the @Lock query need not be the first statement of its transaction. See Locks.

Errors

Errors are translated by SQL state, with the server's message but never a bound value or a password:

SQL state Exception
23505 DuplicateKeyException (with the constraint's name)
other 23xxx, 22xxx DataIntegrityViolationException
40001 CannotSerializeTransactionException
40P01 DeadlockLoserDataAccessException
55P03 (NOWAIT, lock_timeout) CannotAcquireLockException
57014 QueryTimeoutException
42601, 42P01, 42703 and other syntax and name errors BadSqlGrammarException
25006 InvalidDataAccessResourceUsageException
42501 (a missing privilege, a row-level security policy) PermissionDeniedDataAccessException
08xxx, 28xxx, 53xxx, 57P01, a missing database DataAccessResourceFailureException

The message drops the DETAIL line (a duplicate key's holds the values) and the rejected value of invalid input syntax for type integer.

Arguments the database refuses

Long In lists

A derived In / NotIn binds the list as one array parameter (= any($1), <> all($1)), so any length works. A @Query in (:ids) is expanded to one marker per value: past the protocol's 65 535 parameters, an InvalidDataAccessApiUsageException is thrown before the statement is sent.

Text order

ORDER BY and range operators on text use the database's collation: a database made with en_US.UTF-8 puts a_b before Cryptonomicon, where the in-memory store and SQLite compare code units. IgnoreCase is lower(), which folds the letters the locale knows (C: ASCII only).

The schema

Running the tests on PostgreSQL

The starter's own tests that need a server run against SPRING_TEST_POSTGRES_URL when it is set, and otherwise start a PostgreSQL container when Docker is there (tag postgres):

docker run --rm -d --name spring-pg-test -e POSTGRES_PASSWORD=test -p 55432:5432 postgres:17
SPRING_TEST_POSTGRES_URL=postgresql://postgres:test@localhost:55432/postgres dart test -t postgres
docker stop spring-pg-test

In Spring (Java)

spring.datasource.url=jdbc:postgresql://db.example.com:5432/shop?sslmode=verify-full

The same URL works here. Errors map to Spring's PostgreSQL error-code table, so the classes are finer than Spring 7's JdbcTemplate, which translates by SQL state: CannotSerializeTransactionException where Spring gives CannotAcquireLockException, for example. The supertypes are the same, so retry code that catches ConcurrencyFailureException works the same.

See also