Zenn
⛈️

Drizzle ORMを使ってSupabase でトランザクション

2023/12/11に公開
Next.js
supabase
Drizzle ORM
tech

モチベーション

Supabaseクライアントにトランザクション機能を付け足したいと思うことがあるのですが、今のところそういったものは存在していない（ストアドプロシージャを作らないといけない）状況です。つまり、トランザクションをしたい場合、PL/pgSQLやPLV8を書かないといけなくなるため、やや敷居が高い印象があります。もしかしたらFirebaseのバッチ書き込みのような機能が実装されるかもしれませんが、すぐには無理そうです。
この記事では、Drizzle ORMとNext.jsを使ってSupabaseでトランザクションができるようにしてみます。Prismaを使わずDrizzle ORMを使うのは、Supabaseクライアントにコードの書き味が近いためです。DrizzleはORMではありますが、どちらかと言うとクエリビルダーに近い書き方をします。公式サイトにもIf you know SQL — you know Drizzle.とあることから、SQLに近いことが伺えます。
この記事は、SupabaseとNext.jsを触ったことがある人を対象にしています。

作るもの

認証機能付きのデータ読み込みサイトを作ります。CRUD全部実装しようとすると大変なのでREADだけ実装します。RLSを使ってユーザーごとにしか自分のTODOが表示されないようにします。認証機能はsupabase-ssr（ヘルパー）を使います。Nextのバージョンはv14です。drizzle-ormのバージョンは0.29.0です。今回はお試しなので、ローカルでの動作確認だけをすることにします。

接続構成

Next.jsのRoute Handler内でDrizzleを用いてpgBouncerとつなぎます。2024年1月15日にpgBouncerのサポートが切れるようなので、本当はSupavisorとつなぎたかったのですが、現時点ではconfig.tomlに設定がないみたいなのでパスしました。詳細はこちらのdiscussionsをご確認ください。本番環境ではSupavisorが使えるみたいなので、あとで試したいところです。

プロジェクトを作る

このドキュメントに沿ってコマンドを実行します

npx create-next-app -e with-supabase

作り終わったら、ルートで以下のコマンドを打って、supabaseフォルダを作っておきます。バックエンドとフロントエンドが１つのリポジトリにある、いわゆるモノレポ構成です。

npx supabase init

テーブルを作る

プロジェクトにdrizzle-ormとdrizzle-kitをインストールして、テーブルを作る準備をします。
詳しくはこちらをご参照ください。
https://orm.drizzle.team/docs/quick-postgresql/supabase
今回はルートにschemaフォルダを作って、そこにモデルのファイルを置いておくことにします。
アウトプットはsupabaseのmigrationsフォルダにしておきます。

drizzle.config.ts
import type { Config } from "drizzle-kit";

export default {
  schema: "./schema/*",
  out: "./supabase/migrations",
} satisfies Config;

npm scriptsにsqlとdropという２つのコマンドを入れておきます。npm run sqlでdrizzle-kit generate:pgが動き、sqlファイルが生成されます。npm run dropでマイグレーションをなかったことにします。

package.json
  "scripts": {
    ...
    "sql": "drizzle-kit generate:pg",
    "drop": "drizzle-kit drop"
  },

2つテーブルを作ります。１つはprofilesテーブルでauth.usersテーブルを補足する情報を持つpublicスキーマのテーブルです。もう１つはtodosテーブルです。

schema/profiles.ts
import { pgTable, timestamp, uuid, text } from "drizzle-orm/pg-core";

export const profiles = pgTable("profiles", {
  id: uuid("id").defaultRandom().primaryKey(),
  userId: uuid("user_id").unique().notNull(),
  userName: text("user_name").notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});
schema/todos.ts
import { pgTable, timestamp, uuid, text, boolean } from "drizzle-orm/pg-core";
import { profiles } from "./profiles";

export const todos = pgTable("todos", {
  id: uuid("id").defaultRandom().primaryKey(),
  userId: uuid("user_id")
    .notNull()
    .references(() => profiles.userId),
  todoName: text("todo_name").notNull(),
  isDone: boolean("is_done").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

npm run sqlと打ってsqlファイルを生成します。
supabaseフォルダに移動してnpx supabase db resetと打ってSupabaseのDockerをリセットします。これにより、生成したsqlファイルが読み込まれます。
Supabase StudioのSchema Visualizerで下記のような関係図が表示されます。

RLSを有効にする

デフォルトではDrizzleにpostgres権限を持たせますが、それだとRLSが効かなくなってしまうので、権限を新たに作ります。drizzleormという権限を作り、publicスキーマにアクセスできるようにしました。authスキーマ部分はSupabaseに任せるので、DrizzleはpublicスキーマのCRUDをメインにやってもらうようにします。下記のようなSQLを書いてPostgresに変更を反映させます。

supabase/migrations/9000_schema_local.sql
CREATE ROLE drizzleorm LOGIN PASSWORD 'drizzleormPWD1';

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to drizzleorm;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to drizzleorm;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to drizzleorm;

このままではテーブルを作ってもRLSが効いていないので、全テーブルのRLSを有効化させるPL/pgSQLを書いておきます。user_idカラムの値が現在のユーザーのIDと一致するものだけを取得するようにするためのものです。

supabase/migrations/9002_local_rls.sql
--RLSの設定
DO $$
DECLARE
  sql TEXT := '';
  table_name TEXT;
BEGIN
  FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
      sql := sql || 'CREATE POLICY user_policy_' || table_name || ' ON ' || table_name ||
              ' USING (auth.uid() = user_id);';
  END LOOP;
  EXECUTE sql;
  sql := '';
  FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
      sql := sql || 'ALTER TABLE ' || table_name || ' ENABLE ROW LEVEL SECURITY;';
  END LOOP;
  EXECUTE sql;
END;
$$;

トリガーを作る

auth.usersテーブルにデータが入った際に自動的にprofilesテーブルにデータが挿入されるようにトリガーを作ります。

supabase/migrations/9001_local_trigger.sql
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (user_id, user_name)
  values (new.id, new.raw_app_meta_data ->>'user_name');
  return new;  
end;
$$;

-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

Seedを作る

以下のSQLを作成します。

supabase/migrations/9003_local_seed.sql
INSERT INTO "auth"."users" ("instance_id", "id", "aud", "role", "email", "encrypted_password", "email_confirmed_at", "invited_at", "confirmation_token", "confirmation_sent_at", "recovery_token", "recovery_sent_at", "email_change_token_new", "email_change", "email_change_sent_at", "last_sign_in_at", "raw_app_meta_data", "raw_user_meta_data", "is_super_admin", "created_at", "updated_at", "phone", "phone_confirmed_at", "phone_change", "phone_change_token", "phone_change_sent_at", "email_change_token_current", "email_change_confirm_status", "banned_until", "reauthentication_token", "reauthentication_sent_at", "is_sso_user", "deleted_at") VALUES
	('00000000-0000-0000-0000-000000000000', '00000000-285f-4bc5-b0b4-3f625cc266da', 'authenticated', 'authenticated', 'hoge@mail.como', crypt('CEwkVf3ddQ4j', gen_salt('bf')), '2023-10-09 10:18:25.090068+00', NULL, '', NULL, '', NULL, '', '', NULL, '2023-10-09 23:02:03.964702+00', '{"user_name": "hoge"}', NULL, NULL, '2023-10-09 10:18:25.090068+00', '2023-10-11 13:58:23.145385+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL, false, NULL),
	('00000000-0000-0000-0000-000000000000', '11111111-285f-4bc5-b0b4-3f625cc266da', 'authenticated', 'authenticated', 'fuga@mail.como', crypt('CEwkVf3ddQ4j', gen_salt('bf')), '2023-10-09 10:18:26.090068+00', NULL, '', NULL, '', NULL, '', '', NULL, '2023-10-09 23:02:04.964702+00', '{"user_name": "fuga"}', NULL, NULL, '2023-10-09 10:19:25.090068+00', '2023-10-11 13:58:2.145385+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL, false, NULL);

INSERT INTO "public"."todos" ("id", "user_id", "todo_name", "is_done", "created_at", "updated_at") VALUES
	('18164bbb-1e8b-4867-8afb-588ceae871d3', '00000000-285f-4bc5-b0b4-3f625cc266da', 'hogeのTODO', false, '2023-11-26 12:24:46.616396', '2023-11-26 12:24:46.616396'),
	('774dc4b1-3aac-434a-9194-daa7cc50bb4d', '11111111-285f-4bc5-b0b4-3f625cc266da', 'fugaのTODO', false, '2023-11-26 12:24:56.838078', '2023-11-26 12:24:56.838078');

シード用のSQLはローカルのDBからdumpで取ってくるのがオススメです。

ターミナル
supabase db dump --db-url postgresql://postgres:postgres@127.0.0.1:34322/postgres -f supabase/backup/local_seed.sql --data-only

データベースコネクション

接続文字列はPrismaやPostgresのサイト等を参考にして下記のように設定しました。すでにポート番号54321は使っていたので34321に変更しています。

.evn.localは以下のとおりです。

.env.local
SUPABASE_URL=http://127.0.0.1:34321
SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24iLCJleHAiOjE5ODM4MTI5OTZ9.CRXP1A7WOeoJeXxjNni43kdQwgnWNReilDMblYTn_I0
DATABASE_HOST=127.0.0.1
PGBOUNCER_PORT=34329
DATABASE_USER=drizzleorm
DATABASE_PASSWORD=drizzleormPWD1

pgBouncerをローカルで使えるように設定しておきます。

config.toml
[db.pooler]
enabled = true #falseからtrueに変更

drizzleとpgBouncerの接続は下記の通りです。接続文字列でpgbouncer=trueオプションがなぜか効かなかったので、直接ポートとつないでいます。

utils/database.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const connectionString = `postgresql://${process.env.DATABASE_USER}:${process.env.DATABASE_PASSWORD}@${process.env.DATABASE_HOST}:${process.env.PGBOUNCER_PORT}/postgres`;

//Disable prefetch as it is not supported for "Transaction" pool mode
const client = postgres(connectionString, { prepare: false });

export const db = drizzle(client);

トランザクションとRLSを有効化したクエリ

RLSにおいて設定したauth.uid()を使うためにset_configにて設定する必要が出てきます。詳細はこちらをご覧ください。https://github.com/drizzle-team/drizzle-orm/issues/594
コードは以下の通りになりました。

app/todo/page.tsx
import { createClient } from "@/utils/supabase/server";
import { cookies } from "next/headers";
import { todos } from "@/schema/todos";
import { userRLSQuery } from "@/utils/database";

export default async function Todo() {
  const cookieStore = cookies();
  const supabase = createClient(cookieStore);
  const user = await supabase.auth.getUser();
  if (user.data.user == null) return Response.json({ error: "Not logged in" });

  const data = await rlsQuery(user.data.user.id, async (tx) => {
    return await tx.select().from(todos);
  });
  return (
    <section className="place-items-center h-[100%] flex">
      <ul>
        {data.map((todo) => {
          return <li key={todo.id}>{todo.todoName}</li>;
        })}
      </ul>
    </section>
  );
}

rlsQuery関数はdb.transactionのラッパーで、以下のとおり、トランザクション中にset_configを実行して、その後にコールバック関数を実行することで、RLSが有効化されるようになります。

utils/database.ts
...
export const rlsQuery = async <T>(
  userId: string,
  txFunc: QueryInTransaction<T>
) =>
  await db.transaction(async (tx) => {
    await tx.execute(
      sql`SELECT set_config('request.jwt.claim.sub', '${sql.raw(
        userId
      )}', TRUE)`
    );
    return await txFunc(tx);
  });

成果

これまでの内容をまとめると、RLSが効いて個人ごとのToDoを得ることができるようになります。RLSを効かせるためにトランザクションを使いましたが、それに限らず一般的なトランザクションが実行できます。

ご参考までにソースをGitHubに挙げました。
https://github.com/smallStall/drizzleorm-supabase-nextjs

最後に

この記事はSupabaseアドベントカレンダー11日目として書きました。
Drizzle ORMがSupabaseをサポートしていることもあってある程度すんなり接続できましたが、Supavisorがconfig.tomlで設定できるとより良いと思いました。一方で、この構成は既存のSupabaseクライアント構成に比べてTypeScriptでできることが増えるため、フロントエンドエンジニアにとってはとっつきやすく、BaaSへの依存も少ないと思います。こういった構成にご興味があれば試してみるのも良いと思います。ではでは。

Discussion