
# PostgreSQL

スターターの隣に `postgres` パッケージを挙げ、ジェネレーターを実行し、`postgresql://` の URL を指定します。ジェネレーターはパッケージの上に `PostgresBinding` と `postgresJdbcDriver` の Bean を書き、スターターの `PostgresJdbcDriver` と `PostgresDialect` が残りを行います。`postgres` パッケージはアプリケーションの依存関係で、決してスターターのものではありません。

## 使う理由

PostgreSQL はサーバーアプリケーションの一般的なデータベースです。複数のインスタンスがそれを共有し、本物の行ロックとすべての分離レベルを持ち、制約を強制します。スターターの PostgreSQL のサポートは、そのトランザクションのルールを測定する基準です。304 のシナリオを PostgreSQL に対して、スターターと Spring Boot のアプリケーションの両方で実行し、その結果を比較しています。

## 例

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

# または、Spring Boot から貼り付けたとおりに:
# spring.datasource.url=jdbc:postgresql://db.example.com:5432/shop
```

## URL

- ホスト (デフォルトは `localhost`)、ポート (`5432`)、データベース (ユーザーの名前、なければ `postgres`) は通常どおりです。
- 特殊文字を含むユーザーやパスワードはパーセントエンコードします (`@` は `%40`)。
- `?` の後のパラメーターは `sslmode`、`sslrootcert`、`connect_timeout` (秒)、`application_name`、`currentSchema` (または `search_path`) です。それ以外はエラーです。`sslMod=require` のようなタイプミスが設定を黙って弱めることがないようにするためです。
- `spring.datasource.postgresql.*` プロパティは同じものを設定し、URL より優先されます。

## 設定

| キー | デフォルト | 意味 |
|---|---|---|
| `spring.datasource.postgresql.sslmode` | `prefer` | 接続が TLS を使うかどうかとその方法: `disable`、`allow`、`prefer`、`require`、`verify-ca`、`verify-full`。 |
| `spring.datasource.postgresql.ssl-root-cert` | システムのルート | `verify-*` モードがサーバーを照合する認証局の PEM ファイル。 |
| `spring.datasource.postgresql.connect-timeout` | `15s` | 接続と TLS ハンドシェイクを待つ最長時間: `500ms`、`30s`、`1m`、またはミリ秒。このキーは `PT30S` を受け取らない。 |
| `spring.datasource.postgresql.application-name` | なし | `pg_stat_activity` に表示される。 |
| `spring.datasource.postgresql.search-path` | サーバーのもの | `search_path` のスキーマ。カンマ区切り。 |

同じキーが、`spring.datasources.<name>.postgresql.*` の下に、`spring.datasource.postgres.*` (ドライバーの名前が優先される) として、そして環境変数 (`SPRING_DATASOURCE_POSTGRESQL_SSLMODE=verify-full`、名前付きのデータベースには `SPRING_DATASOURCES_SHOP_POSTGRESQL_SSLMODE`) としても存在します。

## TLS

`sslmode` は libpq のものです。

| モード | 暗号化 | 証明書のチェック |
|---|---|---|
| `disable` | なし | なし |
| `allow`、`prefer` (デフォルト) | サーバーが TLS を提供すれば。そうでなければ平文 | なし |
| `require` | あり | なし |
| `verify-ca`、`verify-full` | あり | あり。ホスト名も (`verify-ca` は `verify-full` と同じく厳格) |

デフォルトの `prefer` は、サーバーに TLS がなければ平文で接続しますが、接続を遮断できる攻撃者はそれを強制できます。フォールバックすると、ドライバーはデータベースごとに一度警告をログに出します。開発用のデータベース以外には、`require` か `verify-full` を使ってください。

## 値

- 文は `?` を保ち、スターターがそれを `$1`、`$2` に変えます。引用符で囲んだテキスト、コメント、`$$` の本体はそのままにされ、`??` はリテラルの `?` です (`jsonb` の演算子のため)。値は常にパラメーターとして送られます。

| Dart | PostgreSQL |
|---|---|
| `int` | `BIGINT` |
| `String`、列挙型 | `TEXT` |
| `bool` | `BOOLEAN` |
| `double` | `DOUBLE PRECISION` |
| `DateTime` | `TIMESTAMPTZ` (時点。UTC で読み戻される) |
| `Uint8List` | `BYTEA` |
| `BigInt` | `NUMERIC` |

生成される ID は `BIGINT GENERATED BY DEFAULT AS IDENTITY` で、`RETURNING` で読み戻されます。自分で書いた ID はシーケンスを進めません。

## トランザクション

- すべての分離レベルが提供されます。`readUncommitted` は、PostgreSQL のドキュメントのとおり read committed として実行されます。
- 読み取り専用のトランザクションは書き込みを拒否します (`25006`、`InvalidDataAccessResourceUsageException`)。
- `nested` はセーブポイントを使います。
- トランザクションのタイムアウトや `spring.jdbc.template.query-timeout` は、サーバーで文をキャンセルします。`QueryTimeoutException` で、接続は使える状態のままです。
- DDL はトランザクショナルなので、失敗したマイグレーションは何も残さず、複数のインスタンスのマイグレーションはアドバイザリーロックで順番を待ちます。

## ロック

`@Lock(LockMode.pessimisticWrite)` は `FOR UPDATE`、`pessimisticRead` は `FOR SHARE` です。ゼロの `timeout` は `NOWAIT`、より長いものはクエリの前の `SET LOCAL lock_timeout`、`skipLocked` は `SKIP LOCKED` です (ゼロのタイムアウトより優先されます: サーバーは両方の語を拒否するためです)。SQLite と異なり、`@Lock` のクエリはトランザクションの最初の文である必要はありません。[ロック](locks.md) を参照してください。

## エラー

エラーは SQL ステートで変換され、サーバーのメッセージを持ちますが、バインドされた値やパスワードは決して持ちません。

| SQL ステート | 例外 |
|---|---|
| `23505` | `DuplicateKeyException` (制約の名前付き) |
| その他の `23xxx`、`22xxx` | `DataIntegrityViolationException` |
| `40001` | `CannotSerializeTransactionException` |
| `40P01` | `DeadlockLoserDataAccessException` |
| `55P03` (`NOWAIT`、`lock_timeout`) | `CannotAcquireLockException` |
| `57014` | `QueryTimeoutException` |
| `42601`、`42P01`、`42703` とその他の構文と名前のエラー | `BadSqlGrammarException` |
| `25006` | `InvalidDataAccessResourceUsageException` |
| `42501` (権限がない、行レベルセキュリティのポリシー) | `PermissionDeniedDataAccessException` |
| `08xxx`、`28xxx`、`53xxx`、`57P01`、存在しないデータベース | `DataAccessResourceFailureException` |

メッセージからは、`DETAIL` の行 (重複キーのものは値を含みます) と、`invalid input syntax for type integer` の拒否された値が取り除かれます。

## データベースが拒否する引数

- テキストの中の NUL 文字 (`\u0000`) は、PostgreSQL には保存できません。派生クエリ、`@Query`、`save` では、何かが送られる前に拒否され (`InvalidDataAccessApiUsageException`)、メッセージが値を含むことは決してありません。
- `@Query` で、`\` で終わる値を伴う `like :p` は PostgreSQL の `22025` で、その旨を示す `InvalidDataAccessApiUsageException` です。派生の `Like` 系のクエリは値を自分でエスケープします。

## 長い In のリスト

派生の `In` / `NotIn` はリストを一つの配列パラメーターとしてバインドする (`= any($1)`、`<> all($1)`) ため、どの長さでも動きます。`@Query` の `in (:ids)` は値ごとに一つのマーカーに展開されます。プロトコルの 65 535 個のパラメーターを超えると、文が送られる前に `InvalidDataAccessApiUsageException` が投げられます。

## テキストの順序

テキストに対する `ORDER BY` と範囲の演算子は、データベースの照合順序を使います。`en_US.UTF-8` で作ったデータベースは `a_b` を `Cryptonomicon` より前に置きますが、メモリ上のストアと SQLite はコードユニットで比較します。`IgnoreCase` は `lower()` で、ロケールが知っている文字を畳み込みます (`C`: ASCII のみ)。

## スキーマ

- `spring.data.jdbc.schema=create` はテーブルを書き、その後に外部キーを書くため、循環する参照も動きます。
- `validate` は `information_schema` と `pg_catalog` を読みます。どの幅の整数も `int` に、`real` と `numeric` は `double` に、`varchar` と `uuid` は `String` に、`date` と両方のタイムスタンプは `DateTime` に合います。外部キー (その `ON DELETE` を含む) と一意キーも比較されます。
- `ON DELETE RESTRICT` は、SQLite のように行ごとではなく、文の終わりにチェックされます。

## PostgreSQL でテストを実行する

サーバーを必要とするスターター自身のテストは、`SPRING_TEST_POSTGRES_URL` が設定されていればそれに対して実行され、そうでなければ Docker があれば PostgreSQL のコンテナを起動します (タグ `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
```

## Spring (Java) との対応

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

同じ URL がここでも動きます。エラーは Spring の PostgreSQL のエラーコードの表に対応するため、クラスは SQL ステートで変換する Spring 7 の `JdbcTemplate` より細かくなります。たとえば、Spring が `CannotAcquireLockException` を返すところで `CannotSerializeTransactionException` です。スーパータイプは同じなので、`ConcurrencyFailureException` を捕まえるリトライのコードは同じように動きます。

## 関連項目

- [データソース](data-sources.md)
- [トランザクション](transactions.md)
- [エラー](errors.md)
