Next.js : Drizzle ORM で Supabase のトランザクション制御

Supabase は Firebase みたいな mBaaS で、リアルタイムDBとして Firebase みたいな NoSQL でなくて RDB (PostgreSQL) を使うことができるのが特徴。
無料で作れるプロジェクトは2つまでで、もう少し使えると嬉しいところですけど。

javascript 用のクライアントSDKもあるのだけど、なぜか標準ではトランザクションの手動制御ができないっぽい。
つまり1クエリ1トランザクションになる。
これだと複数テーブルまたいで更新したい場合などに、テーブル間の整合性を担保できない可能性がある。

制御したければ Database Functions として定義しろということみたいだが、いやそれはちょっと、、ストアドみたいなのメンテしたくないし。

別の手段として、公式SDKクライアントじゃなくて Drizzle というORMを使うとトランザクションを手動で制御できるという情報が見つかる。
どうやら PostgreSQL に直接つなぎに行く感じになるようだ。

テーブルスキーマの定義

Drizzle を使えばテーブル作成＆マイグレーションもコードからできるようだが、とりあえず今回はテーブル自体はコンソール画面上でポチポチやるとして、クエリ発行時のトランザクション制御が達成できればいいものとする。

とはいえ、テーブルスキーマはコードでも定義しておく必要がある。

このご時勢なのでまずは gpt に書いてみてもらってから勘でいじっていく。
先人の英知は等しくAIの糧となるのだ。

> npm i drizzle-orm

使用バージョンは 0.38.4。

schema.ts を作ってまとめてテーブル構成を定義する。

// authスキーマ
const authSchema = pgSchema("auth");

// supabase authentication で用意されている auth.users テーブル
export const authUsers = authSchema.table("users", {
    id: uuid().primaryKey()
});

// こっちはアプリケーション側で作成した user テーブル
export const user = pgTable("user", {
    // auth.users テーブルのIDにリレーション
    id: uuid().notNull().primaryKey().references(() => authUsers.id, {
        onDelete: "cascade",
        onUpdate: "cascade",
    }),
    name: varchar().notNull(),
});

// 複合キーのテーブルならこんな感じ
export const roomUser = pgTable("room_user", {
    roomId: uuid().notNull().references(() => room.id, {
        onDelete: "cascade",
        onUpdate: "cascade",
    }),
    userId: uuid().notNull().references(() => authUsers.id, {
        onDelete: "cascade",
        onUpdate: "cascade",
    }),
    score: integer().notNull().default(0),
}, table => {
    return [{
        pk: primaryKey({ columns: [table.roomId, table.userId] })
    }]
});

デフォルトのスキーマは public になる。
public スキーマ内のテーブルであれば pgTable で定義すれば良い。
それ以外のスキーマに属するテーブルは pgSchema.table で定義する。

というか、実は Supabase から作成済みのテーブル構成を引っ張ってきて自動で schema.ts を作ることができる。
ちゃんと公式を読みましょう。

orm.drizzle.team

PostgreSQL 接続文字列の取得

Supabase の管理画面で、PostgreSQL に直接つなぐための接続文字列を取得できる。
現時点のレイアウトだと、「Connect」という管理画面上部のボタンから、「Direct Connection」のところ*1。
postgresql://[ユーザー名]:[パスワード]@xxxxxxxxxxxxxxxx.supabase.co:5432/postgres という感じの文字列。
DBのパスワードは Supabase の管理画面で（現時点のレイアウトだと）Settings > Database から設定できる。

これを、 .env ファイルを作って環境変数に SUPABASE_DATABASE_URL として定義しておく。
最終的には Vercel 上で動かすので、Vercel の Environment Variable にも定義しておく。

drizzle クライアントの初期化

なんかたしか gpt が最初に作ったコードベースだとこんな感じにしていたが、

import { drizzle } from 'drizzle-orm/node-postgres';
// pg パッケージを追加
import { Client } from 'pg';
import * as schema from "./schema";  // 先ほどの schema.ts

const client = new Client({
    connectionString: process.env.SUPABASE_DATABASE_URL,
});

client.connect();

export const ormClient = drizzle(client, { schema });

公式だと postgres パッケージを使ってこんな感じにしていて、たぶんこっちが正解な気がする。
一応どちらでも動いたが。

// node-postgres じゃなくて postgres-js を使っている
import { drizzle } from "drizzle-orm/postgres-js";
import * as schema from "./schema";
import postgres from "postgres";

const client = postgres(process.env.SUPABASE_DATABASE_URL!, { prepare: false });
export const ormClient = drizzle({ client, schema });

vercel にデプロイすると動かない

これでローカルで実行し、うまく動いたと思いきや、vercel でホスティングして動かしてみると、Supabase に接続しにいくところで 500 エラーになることに気がつく。

error {
  userId: 'xxxxxxxxxxxxxxxxxxxx',
  method: 'POST',
  url: 'https://xxxxxxxxxxxxxx.vercel.app/api/foo',
  error: Error: getaddrinfo ENOTFOUND db.xxxxxxxxxxxxxxxxx.supabase.co
      at GetAddrInfoReqWrap.onlookupall [as oncomplete] (node:dns:120:26)
      at GetAddrInfoReqWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
    errno: -3007,
    code: 'ENOTFOUND',
    syscall: 'getaddrinfo',
    hostname: 'db.xxxxxxxxxxxxxxxx.supabase.co'
  }
}

最初 ssl 接続が有効じゃないからかとか思って、postgres() に { ssl: true } とかやってみたがダメ。
そんで Supabase の管理画面で接続情報のところを眺めていると、こんな記載があることに気がつく。

Vercel でダイレクトコネクションしたかったら IPv4 サポートが必要だよ、そしてそれは有料だよ。と私には読めますね。

そしてこの Supabase の接続情報をさらに下にスクロールしていったところ、「Direct connection」のほかに「Transaction pooler」と書かれた接続文字列があって、こんなふうな、AWSっぽいURLになっている。

postgresql://[ユーザー名].xxxxxxxxxxxxxxxxxxx:[パスワード]@aws-0-ap-northeast-1.pooler.supabase.com:6543/postgres

こっちであれば Vercel からでも動作するようだ。
接続先をこっちに変えて、Vercel 上でもエラーにならないことを確認できた。

Supavisor というコネクションプーラを使ってトランザクション単位でコネクションをプーリングするということらしい。
ただこれだと、プリペアドステートメントが使えなくなるっぽい（先のサンプルで { prepare: false } を指定していたのもそういうこと）。

とりあえず今回はここまで。
AIに奪われるプログラミングという嗜みの最期の空気感を味わっていきたい。

*1:後述のとおり Direct Connection を使うと問題あり。