SupabaseでDrizzle ORMを利用してトランザクションに対応する

こんにちは、電通総研の瀧川亮弘です。
現在、Flutter（FlutterFlow）とSupabaseによるアプリ開発を行っています。

本記事ではSupabaseのEdge FunctionsでDrizzle ORMを利用してトランザクションに対応する実装について記載します。

Edge FunctionsからDBへのアクセスには、Supabase Javascript Client（以下supabase-js）とDrizzle ORM（以下drizzle）という二つのライブラリを併用しています。
drizzleはsupabase-jsに備わっていないトランザクション機能を補う目的で利用しています。
https://github.com/orgs/supabase/discussions/526

クライアントの初期化
トランザクション処理の実装
- ユーザー退会
- ユーザー登録
終わりに

クライアントの初期化

それぞれのクライアントのインスタンス化処理です。

依存ライブラリ

まずは必要な依存ライブラリをインポートします。

{
    "imports": {
      "supabase": "https://esm.sh/@supabase/supabase-js@2.40.0",
      "drizzle-orm": "npm:drizzle-orm",
      "drizzle-orm/": "npm:/drizzle-orm/",
      "postgres": "https://deno.land/x/postgresjs@v3.4.4/mod.js",
    }
}

supabase-jsの初期化

デフォルトで用意されている環境変数からDBの接続情報を取得しインスタンスを生成します。

import { createClient } from "supabase";

// 環境変数から必要な情報を取得
const supabaseUrl = Deno.env.get("SUPABASE_URL") as string;
const supabaseKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") as string;

// Supabaseのクライアントを初期化
export const supabase = createClient(supabaseUrl, supabaseKey);

drizzleの初期化

自身で設定した環境変数CUSTOM_SUPABASE_DB_URLからSupervisorの接続情報を取得しインスタンスを生成します。
Supervisor経由でDBに接続することでmax connectionsエラーを抑制できます。
詳細は以下をご参照ください。
https://supabase.com/docs/guides/database/connecting-to-postgres

import { drizzle as createDrizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

// 環境変数から必要な情報を取得
const connectionString = Deno.env.get("CUSTOM_SUPABASE_DB_URL") as string;

// Postgresのクライアントを初期化
const pg = postgres(connectionString, {
  prepare: false,
  ssl: true,
});

// Drizzleのクライアントを初期化
export const drizzle = createDrizzle(pg, { logger: true });

トランザクション処理の実装

まず基本方針としてauthスキーマに対してはsupabase-js、publicスキーマに対してはdrizzleでアクセスするという使い分けをしています。

authスキーマはSupabaseがデフォルトで用意しているスキーマであり、開発者はスキーマの変更を行いません。
supabase-jsを通して、ユーザー登録やメールアドレス変更など認証関連の処理を実行することで、間接的にauthスキーマにアクセスします。
具体的な処理はsupabase-jsが隠蔽しているため、開発者はスキーマの詳細を知る必要がありません。

publicスキーマではアプリケーションに必要な任意のテーブルを管理しています。
supabase-jsでもpublicスキーマへのアクセスは可能ですが、トランザクションをサポートしていないため、drizzleでアクセスしています。

Supabaseで開発を行う場合、2つのスキーマ（auth, public）にまたがり、副作用のある処理を実行したいケースがあります。
しかし、supabase-jsとdrizzleでトランザクションを共有できないため、若干の工夫が必要です。
例としてユーザー退会とユーザー登録について記載します。

ユーザー退会

ユーザー退会では、auth.users（authスキーマのusersテーブル）とpublic.profilesのレコードを同時に論理削除します。
まずは、Edge Functionsでpublicスキーマに対する処理のみを実装します。

import { drizzle, supabase } from "../_shared/client.ts";

// ユーザー退会
const handleDelete = async (req: Request): Promise<Response> => {

  // 省略

  await drizzle.transaction(async (tx) => {

    // お気に入り削除
    await tx.delete(favorite).where(eq(favorite.userId, uid));

    // プロフィール削除
    await tx.update(profiles)
      .set({
        isDeleted: true,
      })
      .where(eq(users.authUserId, uid));
  });
  // 省略
    
};

Deno.serve((req) => {
  return handleDelete(req);
});

次にauthスキーマに対する処理を追加します。
auth.usersへの削除処理に失敗した場合、明示的にtx.rollback();でpublic側もロールバックする点がポイントです。

import { drizzle, supabase } from "../_shared/client.ts";

// ユーザー退会
const handleDelete = async (req: Request): Promise<Response> => {

  // 省略

  await drizzle.transaction(async (tx) => {

    // お気に入り削除
    await tx.delete(favorite).where(eq(favorite.userId, uid));

    // プロフィール削除
    await tx.update(profiles)
      .set({
        isDeleted: true,
      })
      .where(eq(profiles.authUserId, uid));

    // ユーザー削除
    const { error: errorDeleteAuthUser } = await supabase
      .auth
      .admin
      .deleteUser(
        uid,
        true, // 論理削除
      );

    if (errorDeleteAuthUser) {
      tx.rollback(); // 明示的にロールバック  
    
      // 省略
    
    }
  });

  // 省略
    
};

Deno.serve((req) => {
  return handleDelete(req);
});

ユーザー登録

ユーザー登録では、auth.usersとpublic.profilesのレコードを同時に追加する必要があります。
外部キーの依存関係の都合上、auth.usersから先にインサートします。
public側の処理に失敗した場合に、drizzleのトランザクション機能でauth側をロールバックすることはできないため、ユーザー退会と同様の方法は取れません。

結果、Edge Functionsでのpublic.profilesへのインサートは諦め、公式ページの通りの実装としました。
https://supabase.com/docs/guides/auth/managing-user-data#advanced-techniques

auth.usersのインサートをトリガーにPostgreSQLの関数でpublic.profilesへのインサート処理を行っています。
関数内でエラーが発生した場合、auth側の処理もロールバックされることは確認できました。

おおよそ公式の通りですが、一応ソースも掲載します。

-- ユーザー登録関数
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  role public."Role";
BEGIN
  role := (NEW.raw_user_meta_data ->> 'role')::public."Role"; 

  INSERT INTO public.profiles (auth_user_id, role, is_deleted)
  VALUES (NEW.id, role, false);
  
  RETURN NEW;
END;
$$;

-- ユーザー登録関数を実行するトリガー
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();