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
- Host (default
localhost), port (5432) and database (the user's name, elsepostgres) as usual. - A user or password with special characters is percent-encoded (
%40for@). - The parameters after
?aresslmode,sslrootcert,connect_timeout(seconds),application_nameandcurrentSchema(orsearch_path). Any other is an error, so that a typo such assslMod=requirenever silently weakens a setting. - The
spring.datasource.postgresql.*properties set the same, and win over 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
- The statements keep
?; the starter turns it into$1,$2. Quoted text, comments and$$bodies are left alone, and??is a literal?(for thejsonboperators). Values always go as parameters.
| 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
- Every isolation level is offered;
readUncommittedruns as read committed, as PostgreSQL documents. - Read-only transactions refuse writes (
25006, anInvalidDataAccessResourceUsageException). nesteduses savepoints.- A transaction timeout or
spring.jdbc.template.query-timeoutcancels the statement on the server: aQueryTimeoutException, and the connection stays usable. - DDL is transactional, so a failing migration leaves nothing of itself, and the migrations of several instances queue on an advisory lock.
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
- A NUL character (
\u0000) in text cannot be stored by PostgreSQL. In a derived query, a@Queryor asave, it is refused before anything is sent (anInvalidDataAccessApiUsageException), and the message never holds the value. - In a
@Query,like :pwith a value ending in\is PostgreSQL's22025, anInvalidDataAccessApiUsageExceptionsaying so. DerivedLike-style queries escape the value themselves.
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
spring.data.jdbc.schema=createwrites the tables, with foreign keys after them so references in a cycle work.validatereadsinformation_schemaandpg_catalog: any integer width fits anint,realandnumericadouble,varcharanduuidaString,dateand both timestamps aDateTime. Foreign keys (with theirON DELETE) and unique keys are compared too.ON DELETE RESTRICTis checked when the statement ends, not row by row as in SQLite.
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.