PostgresSQLの関数をGit(GitLab-CI)でバージョン管理する
17769 ワード
概要
-
PostgresSQL
ではFUNCTION
という関数を使えます
- 複数のクエリを実行するなどの複雑な処理をするときに使われたりします
-
PostgresSQL
で関数化しておけば関数を呼び出すだけで実行できて便利です
- ただし次のように困ったことが起こります・・・
困ったこと
- データベース上にある関数であるためバージョン管理しづらいです
- データベース自体をマイグレーションの管理で対応してもよさそうですが・・・
- 関数だけを対象にしたいし他データは巻き込みたくない
- そもそもデータベースは
PGDUMP
とかバックアップやってたりするし・・・
- なんかちょっとデータベースでソース管理というのはオカシイかもしれない
結局どうしたいの?
- ぶっちゃけていうと
Git
で管理したいんや ← コレ
- Gitで管理してソースをPushしたらデータベースに関数を反映する仕組みをつくるんやで!
使ったモノ
-
ElephantSQL (Postgresのホスティングサーバー)
- 我々の本番環境を模したデータベースとする!
- 我が軍は軍資金がないので無料プランである!
-
GitLab
- ソースの管理はココで行う
- GitLab-CIでElephantSQLに対して関数を更新する
今回チャレンジする処理の流れのイメージ
PostgresSQL
ではFUNCTION
という関数を使えますPostgresSQL
で関数化しておけば関数を呼び出すだけで実行できて便利です- データベース上にある関数であるためバージョン管理しづらいです
- データベース自体をマイグレーションの管理で対応してもよさそうですが・・・
- 関数だけを対象にしたいし他データは巻き込みたくない
- そもそもデータベースは
PGDUMP
とかバックアップやってたりするし・・・ - なんかちょっとデータベースでソース管理というのはオカシイかもしれない
結局どうしたいの?
- ぶっちゃけていうと
Git
で管理したいんや ← コレ
- Gitで管理してソースをPushしたらデータベースに関数を反映する仕組みをつくるんやで!
使ったモノ
-
ElephantSQL (Postgresのホスティングサーバー)
- 我々の本番環境を模したデータベースとする!
- 我が軍は軍資金がないので無料プランである!
-
GitLab
- ソースの管理はココで行う
- GitLab-CIでElephantSQLに対して関数を更新する
今回チャレンジする処理の流れのイメージ
Git
で管理したいんや ← コレ-
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', '[email protected]', 1, true);
INSERT INTO target.users (name, email, company_id, active)
values ('puni-wadamin', '[email protected]', 2, true);
INSERT INTO target.users (name, email, company_id, active)
values ('boyo-wadajin', '[email protected]', 3, true);
INSERT INTO target.users (name, email, company_id, active)
values ('puyo-wadashi', '[email protected]', 1, true);
INSERT INTO target.users (name, email, company_id, active)
values ('puyo-yametan', '[email protected]', 1, false);
INSERT INTO target.users (name, email, company_id, active)
values ('boyo-yametan', '[email protected]', 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って書いてるやつ)も表示されていません
-
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';
- ちゃんと関数が更新されています
- 辞めたやつ(yametanって書いてるやつ)も表示されていません
課題や考えられる改善案
- 最低限の仕組みなので1つの
function.sql
を対象にしている
- いろいろ関数があった場合は複数のファイルにした方がよさそう
-
.gitlab-ci.yml
に追記していくよりもShell
などを作った方がよさそう
- 削除するときは手動で
DROP
する必要がある
- ホスティングしている環境によってはIP制限があるので許可してもらう必要ありそう
- 面倒なのはデフォルトの
GitLab
のRunner
はIP
が変わるかも
-
Runner
を別のサーバーにやってもらったりするとよさそう
これができて何が嬉しいの?
-
Git
でソース管理できるので差分を確認しやすいです
-
GitLab
にアクセスできる人なら関数に対してコードレビューもできます
- 今のままだと
master
ブランチにマージされれば関数を更新できます
- 別のブランチだったら関数を更新せずに
Push
だけできます
もっと嬉しいことはないの?
- 今回は必要なデータなども準備しているのでローカルのコンテナなどで事前に検証できる
- 検証できるということはテストすることもできたりするわけで・・・
-
GitLab-CI
にテストを実行させてパスしたら関数を更新する仕組みも作れます
function.sql
を対象にしている
- いろいろ関数があった場合は複数のファイルにした方がよさそう
.gitlab-ci.yml
に追記していくよりもShell
などを作った方がよさそうDROP
する必要がある- 面倒なのはデフォルトの
GitLab
のRunner
はIP
が変わるかも -
Runner
を別のサーバーにやってもらったりするとよさそう
-
Git
でソース管理できるので差分を確認しやすいです -
GitLab
にアクセスできる人なら関数に対してコードレビューもできます - 今のままだと
master
ブランチにマージされれば関数を更新できます- 別のブランチだったら関数を更新せずに
Push
だけできます
- 別のブランチだったら関数を更新せずに
もっと嬉しいことはないの?
- 今回は必要なデータなども準備しているのでローカルのコンテナなどで事前に検証できる
- 検証できるということはテストすることもできたりするわけで・・・
-
GitLab-CI
にテストを実行させてパスしたら関数を更新する仕組みも作れます
GitLab-CI
にテストを実行させてパスしたら関数を更新する仕組みも作れますAuthor And Source
この問題について(PostgresSQLの関数をGit(GitLab-CI)でバージョン管理する), 我々は、より多くの情報をここで見つけました https://qiita.com/tamoco/items/120c26a5d03dc949f8a9著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .