PostgreSQLのスキーマをJDBC接続で一括指定する方法

PostgreSQLでアプリケーション開発をしていると、「publicスキーマじゃなくて別スキーマを使いたいけど、全SQLにスキーマ名を書くのは面倒だな…」と感じることってありますよね。

特に既存システムのリプレース時に、スキーマ構成だけ変えたいけどSQL文は全部同じにしたい、というケースは実務でよく遭遇します。

この記事では、JDBC接続文字列にcurrentSchemaパラメータを指定するだけで、すべてのSQL文で自動的にスキーマを補完してくれる方法を解説します。設定ファイルを1行変更するだけで完結するので、とても便利ですよ。

なぜスキーマを明示的に指定したいのか

PostgreSQLでは特に何も設定をせずに

SQL

SELECT * FROM test;

というSQLを発行すると、デフォルトでpublicスキーマが使われます。
つまり

SQL

SELECT * FROM public.test;

と同じ動作になります。

リプレース時の困りごと

実務では本番環境のリプレース時にこんな要件が発生することがあります。

デフォルトのpublicスキーマではなく専用のスキーマ（例：app_schema）を作成したい。
テーブルやビューの構成は既存と全く同じにする。
既存のSQL文は変更せずにそのまま使いたい。

この場合全てのSQL文に対して手動でスキーマ名を付け加えるのは現実的ではありません。SQLファイルが数百、数千とある場合は修正漏れのリスクも高まりますよね。

そこで活躍するのがJDBC接続時のスキーマ指定です。

JDBC接続文字列で解決する方法

PostgreSQL JDBCドライバーのcurrentSchemaパラメータ

PostgreSQL公式のJDBCドライバーにはcurrentSchemaというパラメータが用意されています。

PostgreSQL JDBC Driver公式ドキュメントによると以下のように説明されています。

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.

要約すると「このパラメータで指定したスキーマがテーブル名などの非修飾オブジェクト名の解決に使われる」ということです。

つまり接続時にcurrentSchema=app_schemaと指定すれば、アプリケーション内のすべてのSQL文でスキーマ名が省略されている場合に自動的にapp_schemaスキーマのテーブルを参照してくれるようになります。このcurrentSchemaの指定がなかった場合にデフォルトとしてpublicをPostgreSQLが参照してくれています。

この方法が適用されるケース、されないケース

上記の公式ドキュメントにもあった非修飾オブジェクト名の解決に使われるという点に注意が必要です。

例えばスキーマを明示的に指定したSQLをプログラムでハードコーディングしている場合、currentSchemaの指定に関わらずプログラムで指定しているスキーマのテーブルを参照します。currentSchemaの指定はあくまでスキーマの参照をPostgreSQLに任せているSQLで使える手段です。

適用されるケース

SQL文でテーブル名等にスキーマを指定していない。

SQL

SELECT * FROM test;

適用されないケース

SQL文で明示的にテーブル名等にスキーマを指定している。

SQL

SELECT * FROM public.test;
SELECT * FROM app_schema.test;

設定方法（jdbc.diconファイルの例）

S2JTAやSeasar2を利用している場合、jdbc.diconなどの設定ファイルに記載されているJDBC接続文字列を以下のように変更します。

この例ではapp_schemaというスキーマを優先的に参照するよう設定しています。

jdbc.dicon（変更前）

jdbc:postgresql://localhost:5432/mydb

jdbc.dicon（変更後）

jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema

動作イメージ

設定後アプリケーションから以下のようなSQLを発行すると、

SQL

SELECT * FROM test;

実際には以下のように解釈されます。

SQL

SELECT * FROM app_schema.test;

これにより既存のSQL文を一切変更することなく、別スキーマへの切り替えが完了します。

複数スキーマを検索パスに含める場合

currentSchemaパラメータはカンマ区切りで複数のスキーマを指定できます。

jdbc.dicon

jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema,public

この設定では、まずapp_schemaを検索し見つからなければpublicスキーマを参照します。公式のドキュメントやサンプルデータはpublicに残しておきたい場合などに便利ですね。

注意点とトラブルシューティング

スキーマ名の大文字・小文字

PostgreSQLではスキーマ名に大文字を使う場合は引用符で囲む必要があります。ただし、currentSchemaパラメータでは引用符なしで指定できます。

jdbc.dicon

jdbc:postgresql://localhost:5432/mydb?currentSchema=AppSchema

もし大文字小文字を厳密に扱いたい場合はスキーマ作成時に引用符を使って定義しましょう。

接続プールを使用している場合

接続プールを利用している環境では設定変更後にアプリケーションサーバーの再起動が必要です。既存の接続が残っていると古い設定が使われ続けることがあるので注意してください。

権限不足エラーが出る場合

ERROR: permission denied for schema app_schema

このようなエラーが出る場合は接続ユーザーに対してスキーマのUSAGE権限が付与されているか確認してください。

SQL

GRANT USAGE ON SCHEMA app_schema TO your_db_user;

また、テーブルへのアクセス権限も必要です。

SQL

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO your_db_user;

まとめ

この記事ではPostgreSQLでJDBC接続時にデフォルトスキーマを指定する方法を解説しました。

JDBC接続文字列にcurrentSchemaパラメータを追加するだけで全SQLで自動的にスキーマが補完されるよ。
既存のSQL文を変更せずにスキーマ移行ができるため環境変化時時に有効だよ。
カンマ区切りで複数スキーマの検索パスを設定できるよ。
スキーマへの適切な権限付与を忘れずに。

リプレースや環境移行時の作業効率が大幅に向上しますので、ぜひ活用してみてくださいね。