Zenn
⛈️

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

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

モチベーション

Supabaseクライアントにトランザクション機能を付け足したいと思うことがあるのですが、今のところそういったものは存在していない（ストアドプロシージャを作らないといけない）状況です。つまり、トランザクションをしたい場合、PL/pgSQLやPLV8を書かないといけなくなるため、SQLに慣れ親しんでいない人にとっては敷居が高い印象があります。もしかしたら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です。今回はお試しなので、ローカルでの動作確認だけをすることにします。下記のような構成で作ります。postgresとdrizzleの間にSupavisor（コネクションプーラー）があるのが特徴的です。

プロジェクトを作る

ご参考までにソースをGitHubに挙げましたのでこちらからgit cloneしてください。node_modulesがルートとsupabaseディレクトリの２つあるので、お気をつけください。
https://github.com/smallStall/drizzleorm-supabase-nextjs

以下では、このリポジトリですでに設定してあることについて、どのような考えで行ったのかを述べます。

テーブルを作る

プロジェクトにdrizzle-ormとdrizzle-kitをインストールして、テーブルを作る準備をします。
詳しくはこちらをご参照ください。
https://orm.drizzle.team/docs/quick-postgresql/supabase
また、詳細は省略しますが、SupabaseのDockerは動かしておいてください。
今回はルートに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で下記のような関係図が表示されます。

LocalでSupavisorを有効にする

下記のようにconfig.tomlに記述し、Local環境でSupavisorを有効化します。

config.toml
[db.pooler]
enabled = true
# Port to use for the local connection pooler.
port = 34329
# Specifies when a server connection can be reused by other clients.
# Configure one of the supported pooler modes: `transaction`, `session`.
pool_mode = "transaction"
# How many server connections to allow per user/database pair.
default_pool_size = 20
# Maximum number of client connections allowed.
max_client_conn = 100

RLSを有効にする

多くのドキュメントではDrizzleはpostgresユーザーとしてSupavisorにログインしていますが、それだと権限が強すぎてRLSが効かなくなってしまうので、pgbouncerユーザーとしてログインします。デフォルトではpgbouncerにpublicスキーマのアクセス権限がないので、下記SQLを実行します。

supabase/migrations/9004_local_schema.sql
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to pgbouncer;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to pgbouncer;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to pgbouncer;

「Supavisor」のユーザーやテナントを作る話については、このページが参考になりそうです。

次に全テーブルのRLSを有効化させるPL/pgSQLを書いておきます。user_idカラムの値が現在のユーザーのIDと一致するものだけを取得するようにします。つまり、プライベートな情報を扱うときのRLSをかけます。

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に変更しています。ポート番号はconfig.tomlで変更できます。

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

.env.local
SUPABASE_URL=http://127.0.0.1:34321
SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24iLCJleHAiOjE5ODM4MTI5OTZ9.CRXP1A7WOeoJeXxjNni43kdQwgnWNReilDMblYTn_I0
SUPAVISOR_PORT=localhost:34329
SUPAVISOR_USER=pgbouncer
SUPAVISOR_PASSWORD=postgres
SUPAVISOR_TENANT=pooler-dev

drizzleとPostgresの接続は下記の通りです。

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

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

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

export const db = drizzle(client);

トランザクション可能なクエリ

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でかけておいたauth.uid()が有効化されるようになります。

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を効かせるためにトランザクションを使いましたが、それに限らず一般的なトランザクションが実行できます。
ログインパスワードはCEwkVf3ddQ4jにしています。

最後に

この記事はSupabaseアドベントカレンダー11日目として書きました。
この構成は既存のSupabaseクライアント構成に比べてTypeScriptでできることが増えるため、フロントエンドエンジニアにとってはとっつきやすく、BaaSへの依存も少ないと思います。ご興味があれば試してみるのも良いと思います。本記事では知名度が高いNext.jsを使いましたが、Remixを使ってエッジ上にデプロイするのもおもしろそうです。ではでは。

Discussion