
# 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

```yaml
# pubspec.yaml
dependencies:
  spring_boot_starter_data: ^0.9.0
  postgres: ^3.5.0
```

```properties
# 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, else `postgres`) as usual.
- A user or password with special characters is percent-encoded (`%40` for `@`).
- The parameters after `?` are `sslmode`, `sslrootcert`, `connect_timeout` (seconds), `application_name` and `currentSchema` (or `search_path`). Any other is an error, so that a typo such as `sslMod=require` never 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 the `jsonb` operators). 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; `readUncommitted` runs as read committed, as PostgreSQL documents.
- Read-only transactions refuse writes (`25006`, an `InvalidDataAccessResourceUsageException`).
- `nested` uses savepoints.
- A transaction timeout or `spring.jdbc.template.query-timeout` cancels the statement on the server: a `QueryTimeoutException`, 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](locks.md).

## 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 `@Query` or a `save`, it is refused before anything is sent (an `InvalidDataAccessApiUsageException`), and the message never holds the value.
- In a `@Query`, `like :p` with a value ending in `\` is PostgreSQL's `22025`, an `InvalidDataAccessApiUsageException` saying so. Derived `Like`-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=create` writes the tables, with foreign keys after them so references in a cycle work.
- `validate` reads `information_schema` and `pg_catalog`: any integer width fits an `int`, `real` and `numeric` a `double`, `varchar` and `uuid` a `String`, `date` and both timestamps a `DateTime`. Foreign keys (with their `ON DELETE`) and unique keys are compared too.
- `ON DELETE RESTRICT` is 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`):

```bash
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)

```properties
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

- [Data sources](data-sources.md)
- [Transactions](transactions.md)
- [Errors](errors.md)
