PostgreSQLでアプリケーション開発をしていると、「publicスキーマじゃなくて別のスキーマを使いたいけど、全SQLにスキーマ名を書き直すのは現実的じゃないな…」と感じることってありますよね。
特に既存システムのリプレース時、スキーマ構成だけ変えたいのにSQL文は全部そのまま使いたい、というケースは実務でよく遭遇します。
この記事では、JDBC接続文字列にcurrentSchemaパラメータを追加するだけで、すべてのSQL文でスキーマを自動補完してくれる方法を解説します。設定ファイルを1行変えるだけで完結するので、とても便利ですよ。
PostgreSQLの検証環境をDockerで用意する方法はこちらもどうぞ。
この記事でわかること
- PostgreSQLでデフォルトスキーマを切り替えたい理由と背景
currentSchemaパラメータの仕組みと適用範囲jdbc.diconなど設定ファイルへの具体的な書き方- 複数スキーマを検索パスに含める方法
- 権限エラーなどトラブル時の対処法
なぜスキーマを一括指定したいのか
PostgreSQLで何も設定せずに下記のSQLを発行すると、デフォルトでpublicスキーマが参照されます。
SELECT * FROM test;これは実質的に以下と同じ動作です。
SELECT * FROM public.test;リプレース時によくある要件
実務でのリプレース案件だと、こんな要件が出てきます。
- デフォルトの
publicスキーマではなく専用スキーマ（例：app_schema）を用意したい。 - テーブルやビューの構成は既存と全く同じにする。
- 既存のSQL文は変更せずにそのまま流用したい。
SQLファイルが数百・数千あるケースで手動スキーマ名追記は現実的ではありませんし、修正漏れのリスクも高いですよね。
そこで使えるのがJDBC接続時のスキーマ指定です。
JDBC接続文字列で解決する方法
currentSchemaパラメータとは
PostgreSQL公式のJDBCドライバーにはcurrentSchemaというパラメータが用意されています。
currentSchema (String) Default null Specify the schema (or several schema separated by commas) to be set in the search-path. This schema will be used to resolve unqualified object names used in statements over this connection.
Initializing the Driver | pgJDBC
要約すると「指定したスキーマが、スキーマ名を省略したオブジェクト名の解決に使われる」ということです。
接続時にcurrentSchema=app_schemaと指定すれば、アプリ内のすべてのSQL文でスキーマ名が省略されている場合に自動的にapp_schemaを参照してくれます。指定がない場合はPostgreSQLのデフォルトであるpublicが使われます。
適用されるケース・されないケース
公式ドキュメントにある非修飾オブジェクト名の解決という点が重要です。SQL文でスキーマ名をハードコーディングしている場合はcurrentSchemaの指定に関係なく、そちらが優先されます。
| ケース | 説明 | 適用 |
|---|---|---|
| スキーマ名なしのSQL | SELECT * FROM test; | ✅ 適用される |
| スキーマ名ありのSQL（public） | SELECT * FROM public.test; | ❌ 適用されない |
| スキーマ名ありのSQL（別スキーマ） | SELECT * FROM app_schema.test; | ❌ 適用されない |
設定方法（jdbc.diconの例）
S2JTAやSeasar2を利用している場合、jdbc.diconなどの接続設定ファイルを以下のように変更します。
jdbc:postgresql://localhost:5432/mydbjdbc:postgresql://localhost:5432/mydb?currentSchema=app_schemaたったこれだけです。
動作イメージ
設定後にアプリケーションから下記のSQLを発行すると、
SELECT * FROM test;PostgreSQL内部では以下のように解釈されます。
SELECT * FROM app_schema.test;既存のSQL文を一切変更せずにスキーマ切り替えが完了します。
複数スキーマを検索パスに含める場合
currentSchemaパラメータはカンマ区切りで複数のスキーマを指定できます。
jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema,publicこの設定ではまずapp_schemaを検索し、見つからなければpublicスキーマを参照します。公式のサンプルデータや拡張機能などをpublicに残しておきたい場合に便利ですね。
注意点とトラブルシューティング
スキーマ名の大文字・小文字について
PostgreSQLではスキーマ名に大文字を使う場合、通常は引用符で囲む必要があります。ただしcurrentSchemaパラメータでは引用符なしで指定できます。
jdbc:postgresql://localhost:5432/mydb?currentSchema=AppSchema大文字小文字を厳密に管理したい場合は、スキーマ作成時に引用符を使って定義しておくのが安全です。
接続プールを使用している場合
接続プールを利用している環境では設定変更後にアプリケーションサーバーの再起動が必要です。既存の接続が残っていると古い設定が使われ続けることがあるので注意してください。
権限エラーが出る場合
下記のようなエラーが出るケースがあります。
ERROR: permission denied for schema app_schema接続ユーザーに対してスキーマのUSAGE権限が付与されているか確認してください。
GRANT USAGE ON SCHEMA app_schema TO 【DBユーザー名】;テーブルへのアクセス権限も別途必要です。
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO 【DBユーザー名】;Q&A
PostgreSQLやDocker関連記事はここでまとめて確認！
まとめ
- JDBC接続文字列に
currentSchemaパラメータを追加するだけで全SQLでスキーマが自動補完されるよ。 - スキーマ名を省略していないSQL（ハードコーディングあり）には適用されないので注意だよ。
- カンマ区切りで複数スキーマの検索パスを設定できるよ。
- 接続ユーザーへのスキーマ
USAGE権限とテーブルアクセス権限を忘れずに付与してね。 - 接続プール環境では設定変更後にアプリサーバーの再起動が必要だよ。
リプレースや環境移行の作業効率が大幅に上がりますので、ぜひ活用してみてくださいね。