Why Drizzle ORM instead of using supabase-js for everything?

supabase-js works fine for simple reads and writes, but two things started causing pain as the app grew. First, the generated types get blurry on complex JOINs and subqueries — you end up casting or ignoring the inference. Second, RLS policies written in the Supabase Dashboard are not in version control, so you can't review them in a PR, diff them, or roll them back. Drizzle solves both: it gives us precise TypeScript types for any query shape, and its `pgPolicy` helper lets us declare RLS policies in the same file as the table they protect. That alone was enough to switch.

What does `(select auth.uid())` do differently from `auth.uid()` in an RLS policy?

In PostgreSQL, `auth.uid()` is a function call. When you write it bare in a policy expression, the planner may call it once per row being evaluated. Wrapping it in a subquery — `(select auth.uid())` — lets the planner treat it as an init-plan, evaluating it exactly once per query and caching the result. For a diary table with thousands of rows, that difference is measurable. Supabase's own documentation recommends this pattern for any stable scalar like `auth.uid()` or `auth.role()`.

Does Expo (React Native) use Drizzle ORM directly?

No. Drizzle ORM runs in Node.js and issues SQL over a direct PostgreSQL connection — that's not the right approach for a mobile client, and Expo doesn't have that kind of database access anyway. Instead, the Expo app talks to Supabase through the PostgREST API using `supabase-js`. The type safety still comes from the shared `@storyie/database` package, which re-exports the types generated by `supabase gen types typescript`. Expo imports those types and passes them to `createClient ()` — so the client is fully typed without ever touching the ORM itself.

How do you keep Drizzle schema types and Supabase-generated types from diverging?

The key is treating Drizzle as the single source of truth and Supabase-generated types as derived. The workflow is: edit the TypeScript schema in `packages/database/src/schemas/`, run `pnpm generate` to produce a SQL migration, apply the migration with `supabase db push`, then regenerate types with `supabase gen types typescript`. After that both type sets reflect the same database state. We never edit the generated types by hand, so there's no manual sync step and no risk of them drifting. If a PR touches a schema file, the reviewer knows to look for a paired migration and type-regen commit.

What is the `prepare: false` option for, and when do you need it?

Supabase routes pooled connections through PgBouncer in transaction mode. PgBouncer in transaction mode does not support named prepared statements — each transaction may land on a different backend connection, so a statement prepared on one connection isn't visible to another. If you omit `prepare: false`, you'll see sporadic "prepared statement does not exist" errors in production that are impossible to reproduce locally. Setting it in the `postgres()` constructor disables Drizzle's use of named prepared statements globally, which is the correct setting for any PgBouncer-pooled Supabase deployment.

How do you handle migrations that need custom SQL, not just schema diffs?

Drizzle Kit generates migrations from schema diffs automatically, which covers the vast majority of changes. For cases where you need to transform existing data — backfilling a column, setting a new default on existing rows, or anything that ALTER TABLE alone can't express — we use `pnpm generate:custom --name= `. That creates an empty migration file we fill in by hand. The file lands in `supabase/migrations/` alongside the auto-generated ones, so it goes through the same review and apply flow. Drizzle Kit treats it as opaque SQL and doesn't try to reverse-engineer or overwrite it on subsequent generates.

Drizzle ORM + Supabase RLS in a monorepo: one package, two apps, zero drift

Storyie runs as a pnpm monorepo with a Next.js web app and an Expo mobile app sharing the same Supabase PostgreSQL database. For the first few months we managed schema changes in the Supabase Dashboard and wrote RLS policies by clicking through the policy editor. It worked until it didn't — once you can't git diff a policy change or roll back a bad one in a PR, you start paying for that convenience in incidents.

This post covers how we moved all of that into a shared @storyie/database package: schema definitions, RLS policies, migrations, and the generated types that the Expo side consumes without ever touching the ORM directly.

TL;DR

All Drizzle table definitions and pgPolicy RLS declarations live in packages/database/src/schemas/. Both apps import @storyie/database as a workspace dependency.
Web (Next.js) uses Drizzle ORM directly for server-side queries and mutations.
Mobile (Expo) can't use Drizzle — it talks to Supabase via PostgREST — but imports the Supabase-generated types re-exported from the same package, so createClient<StrictDB>() is fully typed.
One migration workflow: edit TypeScript schema → pnpm generate → review SQL → supabase db push → regenerate types. Everything happens in one PR.

| Layer | Tool | Where |
| --- | --- | --- |
| Schema + RLS | Drizzle ORM (pgTable, pgPolicy) | packages/database/src/schemas/ |
| Migrations | Drizzle Kit | packages/database/supabase/migrations/ |
| Server queries | Drizzle ORM | apps/web — Server Actions, API routes |
| Mobile types | supabase gen types (re-exported) | packages/database/src/types/database.generated.ts |
| Mobile client | supabase-js typed with StrictDB | apps/expo/supabase/supabase.ts |

Why Drizzle

supabase-js covers basic reads and writes without much ceremony. The problems surfaced once the schema got complex.

Type safety at the edges. The generated Database type from supabase gen types is accurate for simple row fetches, but falls apart on joins and subqueries. Drizzle's InferSelectModel and relational query builder stay precise regardless of query shape.

Migration history. When schema was managed through the Dashboard's SQL editor, git history had no record of what changed or why. Any rollback meant manually reversing SQL by hand. Drizzle Kit generates migration files as SQL that goes into version control like any other code.

RLS in code review. This is the one that actually pushed us to switch. An RLS policy written in the Dashboard is invisible to reviewers. With pgPolicy, the policy sits in the same file as the table it governs. A PR that changes access rules shows up as a diff, not as a note in the description.

Package layout

packages/database/
├── src/
│   ├── client.ts              # postgres() + drizzle() instance
│   ├── schema.ts              # re-exports all schemas
│   ├── schemas/
│   │   ├── tenants.ts
│   │   ├── tenant-members.ts
│   │   ├── diaries.ts
│   │   ├── subscriptions.ts
│   │   └── ...                # 30+ table files
│   └── types/
│       └── database.generated.ts  # supabase gen types output
├── supabase/
│   └── migrations/            # drizzle-kit output
├── drizzle.config.ts
└── package.json

Both apps/web and apps/expo declare @storyie/database as a workspace dependency. The web app gets the Drizzle client and schemas. The Expo app gets the generated types. Neither has to know how the other uses the package.

RLS policies colocated with table definitions

The most useful property of Drizzle's pgPolicy is that access rules read right next to the table columns they protect. There's no tab-switching to the Dashboard to answer "who can write to this table."

Here's the diaries table with its policies:

export const diaries = pgTable(
  "diaries",
  {
    id: uuid("id").primaryKey().notNull().default(sql`uuid_generate_v4()`),
    userId: uuid("user_id").notNull(),
    visibility: diaryVisibility("visibility").notNull().default("private"),
    content: jsonb("content").notNull(),
    // ...
  },
  (table) => ({
    // Public entries are readable by anyone; private entries only by their owner
    selectPolicy: pgPolicy("diaries_select", {
      for: "select",
      using: sql`
        visibility = 'public'
        OR user_id = (select auth.uid())
      `,
    }),
    // Only the owner can create entries
    insertPolicy: pgPolicy("diaries_insert", {
      for: "insert",
      withCheck: sql`user_id = (select auth.uid())`,
    }),
    // ...
  })
);

During code review, the question "can a user read another user's private diary?" has a one-line answer in the diff.

Multi-tenant RLS design

Storyie uses a per-user subdomain model. Tenants can have members, and member roles determine what operations are permitted. All of that access control lives in RLS.

export const tenantMembers = pgTable(
  "tenant_members",
  {
    tenantId: uuid("tenant_id").notNull().references(() => tenants.id, { onDelete: "cascade" }),
    userId: uuid("user_id").notNull().references(() => authUsers.id, { onDelete: "cascade" }),
    role: memberRoleEnum("role").notNull().default("member"),
    // ...
  },
  (table) => ({
    selectPolicy: pgPolicy("members_can_view_same_tenant", {
      for: "select",
      using: sql`${table.tenantId} IN (
        SELECT tenant_id FROM tenant_members
        WHERE user_id = (SELECT auth.uid())
      )`,
    }),
    insertPolicy: pgPolicy("admins_can_insert_members", {
      for: "insert",
      withCheck: sql`${table.tenantId} IN (
        SELECT tenant_id FROM tenant_members
        WHERE user_id = (SELECT auth.uid())
        AND role IN ('owner', 'admin')
      )`,
    }),
    // update and delete follow the same pattern
  })
);

A few design choices worth calling out.

`(select auth.uid())` instead of `auth.uid()`

auth.uid() is a function call. PostgreSQL can call it once per candidate row if it appears bare in a policy expression. Wrapping it in (select auth.uid()) turns it into an init-plan — the planner evaluates it once per query and caches the result. Supabase's documentation recommends this pattern; we apply it everywhere auth.uid() appears in a policy.

Owners live in `tenant_members` too

We don't store the tenant owner in a separate tenants.owner_id column. Everyone — including the original creator — is a row in tenant_members with an appropriate role. This means every policy can reason about membership in one place without special-casing ownership.

`service_role` for server-only writes

Some data is written from a server action rather than from the user's request directly — view logs, for example. Those tables have INSERT restricted to service_role. The server writes with the service role client; the policy blocks any client-side write attempt.

Migration workflow

Edit a schema file in src/schemas/.
pnpm --filter @storyie/database generate — Drizzle Kit diffs the schema against the current migration history and generates SQL.
Review the generated SQL. For data migrations that require custom SQL, use pnpm generate:custom --name=<name> to create an empty file and fill it in by hand.
Commit schema + migration together.
supabase db push applies the migration to the remote database.
pnpm --filter @storyie/database types:generate regenerates database.generated.ts.

We're past 50 migration files. Drizzle Kit operates from the current schema state rather than replaying history, so the file count has no effect on developer experience.

What the monorepo buys you

Extracting the database concerns into a package produces concrete benefits that aren't obvious until you've lived with the alternative.

Shared schema types across both apps. Importing @storyie/database from apps/web gives you InferSelectModel<typeof diaries> with full column types and relation types. No duplication, no copy-paste.

Schema changes are localized. A PR that changes a table structure touches packages/database and nothing else until the consuming app files are updated. Reviewers know exactly where to look.

Other packages can depend on the database. packages/jobs (background jobs) and packages/emails import the db instance and schemas from @storyie/database directly. They don't need their own database client configuration.

Connection pooling in one place. PgBouncer settings — prepare: false, connection limits, idle timeouts — are configured once in client.ts and inherited by every consumer.

Things that bit us

`prepare: false` is mandatory with PgBouncer

Supabase's pooled connection string routes through PgBouncer in transaction mode. In transaction mode, named prepared statements prepared on one backend connection aren't visible to another. Omitting prepare: false causes sporadic "prepared statement does not exist" errors in production that vanish when you retry — and are completely absent locally because local development doesn't go through the pool.

const client = postgres(DATABASE_URL, {
  prepare: false, // required when using Supabase's PgBouncer pooler
  max: 5,
});

Two type systems, one source of truth

supabase gen types typescript and Drizzle's InferSelectModel both describe the same database but aren't interchangeable. We settled on a simple rule: Drizzle types are authoritative for server-side code; Supabase-generated types are for the supabase-js client (Expo and any Realtime usage on web). As long as migrations stay in sync with the generated types — which the workflow above enforces — they never diverge.

Expo gets type safety without Drizzle

Drizzle ORM runs in Node.js and issues SQL over a direct PostgreSQL connection. That's the right tool for a server, not for a React Native app that should be talking to an API. Expo uses supabase-js against the PostgREST layer, not a direct database connection.

The type safety still comes from @storyie/database. We re-export the Supabase-generated types from the package index:

// packages/database/src/index.ts
export * from "./types/database.generated";

Expo imports StrictDB (a strict variant of the generated Database type) and passes it to the client constructor:

// apps/expo/supabase/supabase.ts
import type { StrictDB } from "@storyie/database";

export const supabase = createClient<StrictDB>(supabaseUrl, supabaseAnonKey, {
  auth: {
    storage: AsyncStorage,
    autoRefreshToken: true,
    persistSession: true,
    detectSessionInUrl: false,
  },
});

Service files use the Database type to derive row and insert types for each table:

// apps/expo/services/noteService.ts
import type { Database, Json } from "@storyie/database";

type Tables<T extends keyof Database["public"]["Tables"]> =
  Database["public"]["Tables"][T]["Row"];
type TablesInsert<T extends keyof Database["public"]["Tables"]> =
  Database["public"]["Tables"][T]["Insert"];

export type Note = Omit<Tables<"notes">, "content"> & {
  content: Json;
};

The two type sets have different jobs

| Type source | Used for | Where |
| --- | --- | --- |
| Drizzle (InferSelectModel etc.) | Server-side ORM queries | Next.js Server Actions, background jobs |
| Supabase-generated (Database) | PostgREST client calls | Expo, Next.js Realtime |

Both derive from the same database. Running supabase gen types typescript after any migration keeps them in sync. Neither is written by hand.

The monorepo makes this practical

If the Expo app were in a separate repository, keeping the generated types in sync would require either manual file copying or publishing @storyie/database as an npm package and updating the version after every schema change. In the monorepo, the Expo app's workspace dependency on @storyie/database means a single PR can contain the schema edit, the migration, the regenerated types, and the consuming app changes — and CI validates everything together.

Takeaways

Drizzle's pgPolicy is the main reason to use it over supabase-js alone if you care about keeping RLS auditable. Having access rules in the same file as the table they protect changes how code review handles security questions.
Centralizing the database package pays off more the more consumers you add. With two apps, one jobs package, and one emails package all importing @storyie/database, a single schema change propagates everywhere with no coordination overhead.
The mobile app doesn't need ORM access to be fully typed. Re-exporting Supabase-generated types from the shared package means Expo gets the same guarantees as the server, just via a different API surface.
prepare: false in the Postgres client config is non-negotiable for Supabase's PgBouncer. Skip it and you'll spend time debugging errors that only appear in production under load.

Building a Monorepo with pnpm and TypeScript — workspace conventions and how packages are wired together
Database Schema Design at Storyie — how the schema evolved as the product grew
Cross-platform Lexical with use dom — applying the same shared-package principle to the rich text editor

Try Storyie

The schema described in this post backs storyie.com and the iOS app. Same database, same RLS policies, two runtimes — and the type system catches mismatches before any of it ships.