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

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


# 困ったこと

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

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

# 使ったモノ

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

# 今回チャレンジする処理の流れのイメージ
![postgres.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/87235265-3de4-7fe1-0e1e-824c9ab40395.png)


# 準備

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/3630d7eb-3c3e-1cce-2ff1-a92b6f78e2a8.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/c161cb32-1eae-a980-b0e3-4002a5c85f12.png)

# ファイル構成

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

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

https://gitlab.com/tamoco-mocomoco/gitlab-postgres-function

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

```yaml
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`の話にあまり関係はない

```sql
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', 'keitaro@puyopuyo.com', 1, true);
INSERT INTO target.users (name, email, company_id, active) 
values ('puni-wadamin', 'wadamin@punipuni.com', 2, true);
INSERT INTO target.users (name, email, company_id, active) 
values ('boyo-wadajin', 'wadajin@boyoboyo.com', 3, true);
INSERT INTO target.users (name, email, company_id, active) 
values ('puyo-wadashi', 'wadashi@puyopuyo.com', 1, true);
INSERT INTO target.users (name, email, company_id, active) 
values ('puyo-yametan', 'yametan@puyopuyo.com', 1, false);
INSERT INTO target.users (name, email, company_id, active) 
values ('boyo-yametan', 'yametan@boyoboyo.com', 3, false);
```

## function.sql

```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` されているんやで！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/a20b9fbd-2204-af79-fd8d-dc54b6a93f61.png)

# ElephantSQLで関数を使ってみる

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/e51ba36a-c145-d3de-305d-b3f98d560df8.png)

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

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

```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
   WHERE
      u.active is true;   
END; $$
 
LANGUAGE 'plpgsql';
```

# ElephantSQLでもう一度関数を使ってみる
- ちゃんと関数が更新されています
- 辞めたやつ（yametanって書いてるやつ）も表示されていません

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/2641dca9-54cb-00dc-b9b0-4882d447f1fd.png)

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

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

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

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

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