PostgresSQLの関数をGit(GitLab-CI)でバージョン管理する

概要

• PostgresSQLではFUNCTIONという関数を使えます
• 複数のクエリを実行するなどの複雑な処理をするときに使われたりします
• PostgresSQLで関数化しておけば関数を呼び出すだけで実行できて便利です
• ただし次のように困ったことが起こります・・・

困ったこと

• データベース上にある関数であるためバージョン管理しづらいです
• データベース自体をマイグレーションの管理で対応してもよさそうですが・・・
  • 関数だけを対象にしたいし他データは巻き込みたくない
  • そもそもデータベースはPGDUMPとかバックアップやってたりするし・・・
  • なんかちょっとデータベースでソース管理というのはオカシイかもしれない

結局どうしたいの？

• ぶっちゃけていうとGitで管理したいんや　← コレ
• Gitで管理してソースをPushしたらデータベースに関数を反映する仕組みをつくるんやで！

使ったモノ

• ElephantSQL (Postgresのホスティングサーバー)
  • 我々の本番環境を模したデータベースとする！
  • 我が軍は軍資金がないので無料プランである！
• GitLab
  • ソースの管理はココで行う
  • GitLab-CIでElephantSQLに対して関数を更新する

今回チャレンジする処理の流れのイメージ

準備

ElephantSQLでデータベースのサーバーを準備

ElephantSQLの設定情報からGitLab.CIの環境変数を設定

ファイル構成

• .gitlab-ci.yml
• data.sql
• function.sql

GitLabに公開しておりますので一覧を確認したい方は以下をご確認ください。

.gitlab-ci.yml

• postgresのイメージを使ってpsqlでSQLファイルを実行します
• ここで若干ハマったのはpsqlで普通にアクセスするとパスワードを求められて上手くいきませんでした。
• 予めPGPASSWORDにパスワードを格納しておくことでパスワードを省略できます

image: postgres:12.2-alpine

update_function:
  stage: deploy
  script:
  - PGPASSWORD=${POSTGRES_PASSWORD} psql -U ${POSTGRES_USER} -d ${POSTGRES_DATABASE} -h ${POSTGRES_HOST} -f ./function.sql
  only:
  - master

data.sql

• 以下のデータをElephantSQLに準備しておきます
  • スキーマ (target)
  • テーブル（target.companyとtarget.users）
  • データ （INSERTもろもろ）
• ぶっちゃけこのファイルは、ソース管理とかGitLab-CIの話にあまり関係はない

CREATE SCHEMA target;

CREATE TABLE target.company (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  address VARCHAR(255) NOT NULL
);

CREATE TABLE target.users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  company_id SERIAL,
  active BOOLEAN NOT NULL,
  FOREIGN KEY (company_id) REFERENCES target.company(id)
);

INSERT INTO target.company (name, address)
values ('株式会社プヨプヨケイタロス', '東京都新宿区なんとかビル');
INSERT INTO target.company (name, address)
values ('（株）プニミンズ', '東京都港区ほんわかビル');
INSERT INTO target.company (name, address)
values ('ボヨヨール株式会社', '東京都品川区のんびりビル');

INSERT INTO target.users (name, email, company_id, active) 
values ('puyo-keitaro', '[e-mail]', 1, true);
INSERT INTO target.users (name, email, company_id, active) 
values ('puni-wadamin', '[e-mail]', 2, true);
INSERT INTO target.users (name, email, company_id, active) 
values ('boyo-wadajin', '[e-mail]', 3, true);
INSERT INTO target.users (name, email, company_id, active) 
values ('puyo-wadashi', '[e-mail]', 1, true);
INSERT INTO target.users (name, email, company_id, active) 
values ('puyo-yametan', '[e-mail]', 1, false);
INSERT INTO target.users (name, email, company_id, active) 
values ('boyo-yametan', '[e-mail]', 3, false);

function.sql

CREATE OR REPLACE FUNCTION target.get_user_list ()
   RETURNS TABLE (
      user_name VARCHAR,
      user_email VARCHAR,
      company_name VARCHAR
   )
AS $$
BEGIN
   RETURN QUERY SELECT
      u.name,
      u.email,
      c.name
   FROM target.users u
   INNER JOIN target.company c
   ON u.company_id = c.id;
END; $$

LANGUAGE 'plpgsql';

上記のファイルをPushすると・・・

CREATE FUNCTION されているんやで！

ElephantSQLで関数を使ってみる

• ちゃんと使えてます
• でも辞めたやつ（yametanって書いてるやつ）はいらんかもです

ソースを更新して再度Pushすると・・・

• 以下のようにソースを更新
  • WHEREでtarget.usersテーブルのactiveカラムがtrueなユーザーだけに絞り込む
  • 事前に入れたデータについても辞めたやつ（yametanって書いてるやつ）はactiveがfalseになっています
  • 以下のようにソースを更新して再びPushします

CREATE OR REPLACE FUNCTION target.get_user_list ()
   RETURNS TABLE (
      user_name VARCHAR,
      user_email VARCHAR,
      company_name VARCHAR
   )
AS $$
BEGIN
   RETURN QUERY SELECT
      u.name,
      u.email,
      c.name
   FROM target.users u
   INNER JOIN target.company c
   ON u.company_id = c.id
   WHERE
      u.active is true;   
END; $$

LANGUAGE 'plpgsql';

ElephantSQLでもう一度関数を使ってみる

• ちゃんと関数が更新されています
• 辞めたやつ（yametanって書いてるやつ）も表示されていません

課題や考えられる改善案

• 最低限の仕組みなので1つのfunction.sqlを対象にしている
  • いろいろ関数があった場合は複数のファイルにした方がよさそう
• .gitlab-ci.ymlに追記していくよりもShellなどを作った方がよさそう
• 削除するときは手動でDROPする必要がある
• ホスティングしている環境によってはIP制限があるので許可してもらう必要ありそう
  • 面倒なのはデフォルトのGitLabのRunnerはIPが変わるかも
  • Runnerを別のサーバーにやってもらったりするとよさそう

これができて何が嬉しいの？

• Gitでソース管理できるので差分を確認しやすいです
• GitLabにアクセスできる人なら関数に対してコードレビューもできます
• 今のままだとmasterブランチにマージされれば関数を更新できます
  • 別のブランチだったら関数を更新せずにPushだけできます

もっと嬉しいことはないの？

• 今回は必要なデータなども準備しているのでローカルのコンテナなどで事前に検証できる
• 検証できるということはテストすることもできたりするわけで・・・
• GitLab-CIにテストを実行させてパスしたら関数を更新する仕組みも作れます