データベースのスキーマ定義をGitHub Actionsで更新しよう！ #SQL

概要

ドキュメント管理や開発環境データベースのスキーマ定義更新等の負荷を軽減したいというのがあり、
GitHub Actionsを使えば手軽にできると思いました。
そこで、データベースのスキーマの定義をGitHub Actionsで更新します。
今回は、定義ファイル生成とプルリクの作成までを紹介します。

ゴール

SQLのクエリごとにファイルを整理します。
差分のプルリクを作ります。

サンプルコード

update-scheme.yml

name: スキーマ定義更新

on: workflow_dispatch

env:
  SQL_FILE_NAME: sample.sql

jobs:
  create-pull-request:
        
  runs-on: ubuntu-latest
  steps:
    - uses: actions/checkout@v4
      with:
        token: {Personal access tokens (classic)}
    - name: initialize
      id: initialize
      run: |
        sudo apt-get install postgresql-client

        # auhor 設定
        git config --global user.email "41898282+github-actions[bot]@users.noreply.github.com"
        git config --global user.name "github-actions[bot]"
        
        # ブランチ名を生成
        echo BRANCH_NAME="update-$(date '+%Y%m%d%H%M%S')" >> $GITHUB_OUTPUT
    - name: create branch
      run: |
        git switch -c ${{steps.initialize.outputs.BRANCH_NAME}}
    - name: Dump SQL
      run: |
        pg_dump -t public.* -h {データベースホスト} -U {デーベースユーザ名} --schema-only {データベース名} > ${{ env.SQL_FILE_NAME }}
    - name: Create SQL
      run: |
        # {出力するファイル名}からシーケンスの定義のみを抽出
        awk '/CREATE SEQUENCE/,/;/ {print}' ${{ env.SQL_FILE_NAME }}  > CREATE-SEQUENCE.sql
              
        # {出力するファイル名}からビューの定義のみを抽出
        awk '/CREATE VIEW/,/;/ {print}' ${{ env.SQL_FILE_NAME }} > CREATE-VIEW.sql
              
        # {出力するファイル名}からテーブルの定義のみを抽出
        awk '/CREATE TABLE/,/;/ {print}' ${{ env.SQL_FILE_NAME }}  > CREATE-TABLE.sql
     - name: detect diff
       id: detect-diff
       run: |
         git add .
         DIFF_COUNT=$(git diff origin/main --name-only | wc -l)
         echo "DIFF_COUNT=$DIFF_COUNT" >> $GITHUB_OUTPUT
     - name: push branch
       if: ${{steps.detect-diff.outputs.DIFF_COUNT != '0'}}
       run: |
         git commit -m "データベース 更新"
         git push origin ${{steps.initialize.outputs.BRANCH_NAME}}

create-pull-request.yml

name: プルリク作成

on:
  push:
    branches-ignore:
      - 'master'

permissions:
  contents: write
  pull-requests: write

jobs:
  create-pull-request:
    runs-on: ubuntu-latest
    env:
      GH_TOKEN: ${{ secrets.GITHUB_TOKEN }}
    steps:
      - uses: actions/checkout@v4
      - name: Create Release Pull Request
    run: |
      gh pr create \
      -B master \
      -t '${{ github.event.head_commit.message }}' \
      -a ${{ github.actor }}  \
      --body "sample body"

環境

GitHub Actions
- runs-on: ubuntu-latest
- uses: actions/checkout@v4
  - token: Personal access tokens (classic)
PostgreSQL
- バージョン: 16.1
ディレクトリ構造

  .github
    |- workflows
      |- update-scheme.yml
      |- create-pull-request.yml

処理の手順

準備
ブランチを変更
テーブルの定義を取得
取得した定義を整理
差分検知
ブランチをGithubへpush
プルリク作成

注意

デフォルトブランチはorigin/masterです。
ブランチ名やファイル名、コミットコメント等はハードコーディングしてます。
至らぬ点がありましたら、ご指摘いただけると幸いです。

1. 準備

postgresql-client をインストールします。
gitのユーザを設定します。

update-scheme.yml

- name: initialize
  id: initialize
  run: |
    sudo apt-get install postgresql-client
    # auhor 設定
    git config --global user.email "41898282+github-actions[bot]@users.noreply.github.com"
    git config --global user.name "github-actions[bot]"

2. ブランチを変更

update-scheme.yml

- name: create branch
  run: |
    git switch -c update

3. テーブル定義を取得

update-scheme.yml

- name: Dump SQL
  run: |
    pg_dump -t public.* -h {データベースホスト} -U {デーベースユーザ名} --schema-only {データベース名} > {出力するファイル名}

4. 取得した定義を整理

今回は、CREATE SEQUENCE, CREATE VIEW, CREATE TABLE を取得します。

update-scheme.yml

- name: Create SQL
  run: |
    # {出力するファイル名}からシーケンスの定義のみを抽出
    awk '/CREATE SEQUENCE/,/;/ {print}' {出力するファイル名}  > CREATE-SEQUENCE.sql

    # {出力するファイル名}からビューの定義のみを抽出
    awk '/CREATE VIEW/,/;/ {print}' {出力するファイル名}  > CREATE-VIEW.sql

    # {出力するファイル名}からテーブルの定義のみを抽出
    awk '/CREATE TABLE/,/;/ {print}' {出力するファイル名}  > CREATE-TABLE.sql

5. 差分検知

origin/masterブランチと差分があるか確認をします。
差分があるファイルを取得し、そのファイル数をDIFF_COUNT変数に入れます。

update-scheme.yml

- name: detect diff
  id: detect-diff
  run: |
    DIFF_COUNT=$(git diff origin/master --name-only | wc -l)
    echo "DIFF_COUNT=$DIFF_COUNT" >> $GITHUB_OUTPUT

6. ブランチをGitHubへpush

5にて定義したDIFF_COUNT変数を取得し、0以外の場合にブラッチをGitHubへpushします。

update-scheme.yml

- name: push branch
  if: ${{steps.detect-diff.outputs.DIFF_COUNT != '0'}}
  run: |
    git commit -m "データベース 更新"
    git push origin update

7. プルリク作成

masterブランチ以外がpushされた場合にプルリクを作ります。

create-pull-request.yml

name: プルリク作成

on:
  push:
    branches-ignore:
      - 'master'

permissions:
  contents: write
  pull-requests: write

jobs:
  create-pull-request:
    runs-on: ubuntu-latest
    env:
      GH_TOKEN: ${{secrets.GITHUB_TOKEN}}
    steps:
      - uses: actions/checkout@v4
      - name: Create Release Pull Request
        run: |
          gh pr create \
          -B master \
          -t '${{ github.event.head_commit.message }}' \
          -a ${{ github.actor }}  \
          --body "template"

まとめ

上記の手順でGitHub Actionsを動かすことでデータベースのスキーマの定義を更新することができます。
この方法を応用すればできることは広がります。
例えば EloquentやEntityFrameworkなどのORMフレームワークで使用するモデル等の更新 などです。

ぜひ、活用してみてください。

最後に

GoQSystemでは一緒に働いてくれる仲間を募集中です！

ご興味がある方は以下リンクよりご確認ください。