PostgreSQL トリガー関数を Prisma + Supabase でうまく使う方法

ある開発案件における、prismaでトリガー関数をうまくGitバージョン管理に含めたかったので、実践したその方法の紹介です。

Twitter やってますのでこちらもご覧ください →Twitter @takuxone
プログラミングコースも出したりしてます → コース一覧

Table of Contents

トリガー関数をマイグレーション履歴に含めて開発が👍

Supabaseではダッシュボード上で、PostgreSQLのファンクションやトリガーを設定できます。

便利ですが、Git/Githubなどで開発を進めている場合、何もしないとソースコードに載ってこないのは不便です。

例えば開発用と本番用でデータベースを分けている場合、両方とも手動で設定しないといけないですし、

開発用などで頻繁にデータベースをリセットする場合は特に大変です。

Prismaは便利なORMサービスです。

マイグレーションもしっかりできるので、データベーススキーマ（モデル）定義は変更の都度、SQLファイルとして残っていきます。

プロジェクトでPrismaを使用している場合はこのマイグレーション履歴にトリガー関数のSQL文を含めることで、開発をスムーズに進めることが可能です。

前提

今回はNext.jsのプロジェクトで使ってみたいと思いますが、Next.jsに限らず応用できる内容です。
DBについても今回はSupabaseですが、他でも問題ないと思います。
Supabaseの初期設定などについては解説しませんので、ググってみてください。

サンプルプロジェクトの準備

Next.jsはサンプルなので Create Next App で作成したいと思います。

npx create-next-app@latest --ts

prismaをインストールします。

yarn add -D prisma
yarn add @prisma/client

prismaを初期化します。

npx prisma init

プロジェクトに prisma ディレクトリが作成されます。

supabaseとの接続は詳細は省きますが、.envや.env.localなどのファイルに下記のように記載します。

それぞれの項目はご自身のsupabaseの情報に置き換えてください。

DATABASE_URL=xxx
SUPABASE_URL=xxx
SUPABASE_KEY=xxx

Prismaスキーマ定義

prismaディレクトリの中のshema.prismaファイルにモデルを定義します。

今回はシンプルに下記のような例で定義します。

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model post {
  id    Int    @id @default(autoincrement())
  title String
}

model notification {
  id    Int    @id @default(autoincrement())
  title String
}

postテーブルは投稿を、 notificationテーブルはトリガー関数が実行された時に挿入される役目とします。

はじめのマイグレーション

それではまずはここで初めのマイグレーションを行なってみます。

npx prisma migrate dev

マイグレーション履歴の名前（フォルダ名）を求められますが、ここでは first_migration と入力しておきます。入力せずにエンターでも大丈夫です。

完了すると、次のように migrations フォルダや sql文が記載されたファイル（migration.sql）などが作成されます。

PrismaStudioを立ち上げてテーブルを確認してみます。

npx prisma studio

テーブルができています。

一応supabaseダッシュボードからも確認すると次のように作成されています。

サンプルとして投稿機能を実装

簡単に投稿機能をつけてみます。

pages/api 直下に posts.ts ファイルを作成し、postを作成するエンドポイントとします。

import { PrismaClient } from "@prisma/client";
import { NextApiRequest, NextApiResponse } from "next";

const prisma = new PrismaClient();

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse
) {
  if (req.method === "POST") {
    const post = await prisma.post.create({
      data: {
        title: req.body.postTitle,
      },
    });
    if (!post) throw Error("not created");
    res.status(200).json(post);
  } else {
    res.setHeader("Allow", "POST");
    res.status(405).end("Method Not Allowed");
  }
}

次にpages直下のindex.tsxファイルを次のようにします。

import type { NextPage } from "next";

const Home: NextPage = () => {
  const handleSubmit: React.FormEventHandler<HTMLFormElement> = async (e) => {
    e.preventDefault();
    const response = await fetch("/api/posts", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        postTitle: e.currentTarget.post_title.value,
      }),
    });
    const createdPost = await response.json();
    console.log(createdPost);
  };
  return (
    <div>
      <form onSubmit={handleSubmit}>
        <input type="text" name="post_title" placeholder="post title" />
        <button type="submit">Submit</button>
      </form>
    </div>
  );
};

export default Home;

この状態で yarn dev でサーバーを立ち上げて確認してみます。

見にくいですがフォームが設置できています。

試しに TITLE 1 と入力して送信してみます。

うまくいけばコンソールに作成された post が表示されます。

Prisma Studio でも確認しておきます。

トリガー関数をマイグレーション履歴に含める

ここから本題のトリガー関数の作成です。

今回はサンプルとして、postテーブルに新しくレコードが挿入（INSERT）されたら、それをトリガーとして notificationテーブルにもレコードを挿入するようにします。

トリガー関数は shema.prisma に記載するわけではないので、sqlファイルを用意する必要があります。

prismaでは次のようにするとファイルのみ生成可能です。

npx prisma migrate dev --create-only

またまた名前を聞かれるので仮に trigger_function と入力しておきます。

xxx_trigger_function フォルダの中の migration.sql ファイルを編集します。

create or replace function public.insert_notification()
  returns trigger as $$
begin
  insert into public.notification(title) 
  values (new.title || ' was created');
  return new;
end;
$$ language plpgsql;

create trigger on_insert_post
  after insert
  on public.post
  for each row
  execute procedure public.insert_notification();

関数名: insert_notification
トリガー名: on_insert_post

上記は post テーブルに新しくレコードが挿入された場合に発動し、

notificationテーブルにもレコードを生成します。

このとき、postレコードの title フィールドの値を使用して、

[その挿入されるレコードのタイトル] + " was created" という文字列とし、

notificationレコードのタイトルとします。

それではこれを適用するためにマイグレートを行います。

npx prisma migrate dev

supabaseを確認すると、Database > Functions のところに insert_notification という関数が作成されています。トリガーについても同様に作成されています。

では想定通り機能するか、試してみます。

まずはトップページで今度は TITLE 2 と入力して送信してみます。

これは通常通り postに追加されています。

合わせてトリガー関数がうまくいっているか、notificationテーブルを確認します。

notificationにもしっかりとデータが登録されていることが確認できました。

PrismaとSupabaseでトリガー関数を使う方法については以上です！