Drizzle ORM

Drizzle lets you describe your schema in TypeScript and generates SQL migrations from it with drizzle-kit. Its query builder, like any Postgres ORM, needs a direct database connection.

On SupaHost, Postgres isn't reachable from the internet. Direct connections exist only on Business and Enterprise, through a restricted SSH tunnel opened from a machine you control. So:

Setup

npm install drizzle-orm postgres
npm install drizzle-kit --save-dev

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/schema.ts',
  out: './supabase/migrations',
  migrations: { prefix: 'supabase' },
  entities: { roles: { provider: 'supabase' } },
  dbCredentials: { url: process.env.DATABASE_URL! },
})

Two options make Drizzle play well with the Supabase CLI:

• migrations.prefix: 'supabase' names files with the timestamp format the CLI uses, so they sit in supabase/migrations next to hand-written ones.
• entities.roles.provider: 'supabase' tells drizzle-kit that roles such as anon and authenticated already exist, so it doesn't try to create them.

Define a table with policies

Drizzle can declare Row Level Security policies next to the table. The helpers in drizzle-orm/supabase give you the standard roles and auth.uid():

// src/db/schema.ts
import { sql } from 'drizzle-orm'
import { bigint, pgPolicy, pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'
import { authenticatedRole, authUid } from 'drizzle-orm/supabase'

export const messages = pgTable(
  'messages',
  {
    id: bigint('id', { mode: 'number' }).primaryKey().generatedAlwaysAsIdentity(),
    channelId: bigint('channel_id', { mode: 'number' }).notNull(),
    authorId: uuid('author_id').notNull().default(sql`auth.uid()`),
    body: text('body').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    pgPolicy('members read messages', {
      for: 'select',
      to: authenticatedRole,
      using: sql`true`,
    }),
    pgPolicy('authors post as themselves', {
      for: 'insert',
      to: authenticatedRole,
      withCheck: sql`${authUid} = ${t.authorId}`,
    }),
  ]
)

Declaring a policy turns RLS on for the table. In a real team chat, the select policy would check channel membership instead of true.

Develop against the local stack

Start the Supabase CLI stack (npx supabase start, see Local development) and use its database:

# .env
DATABASE_URL=postgresql://postgres:[email protected]:54322/postgres

While you're experimenting, npx drizzle-kit push applies the schema directly. When the shape is right, generate a migration and rebuild from scratch to check it:

npx drizzle-kit generate --name add_messages
npx supabase db reset

Read the generated SQL. You can add anything Drizzle doesn't model, such as grants, triggers or functions, to the same file before committing it.

Apply migrations to SupaHost

Any plan: apply the generated file with MCP apply_migration or paste it into Studio's SQL editor. Details are in Migrations workflow.

Business and Enterprise: open the SSH tunnel and let drizzle-kit apply what's pending:

ssh -N -L 5432:127.0.0.1:5432 [email protected]

DATABASE_URL='postgresql://postgres:<password>@127.0.0.1:5432/p_app_one' \
  npx drizzle-kit migrate

The connection string comes from Connect → SSH & CLI → Show connection string. drizzle-kit migrate keeps its own history table (drizzle.__drizzle_migrations), separate from the supabase_migrations table that MCP uses. Use one method per project so the two don't disagree.

To start from an existing database, npx drizzle-kit pull over the tunnel (or against a local copy) writes a TypeScript schema from your current tables.

Querying in your app

Your deployed app can't use the tunnel, so query through the Data API with supabase-js. Requests run as the signed-in user, so the policies above apply:

const { data, error } = await supabase
  .from('messages')
  .select('id, body, author_id, created_at')
  .eq('channel_id', channelId)
  .order('created_at')

For types, generate them from the database with npx supabase gen types typescript locally or MCP generate_typescript_types, and pass them to createClient<Database>(). See Generating TypeScript types.

Drizzle queries are still useful for scripts you run over the tunnel on Business:

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import { messages } from './db/schema'

const client = postgres(process.env.DATABASE_URL!)
const db = drizzle(client)

const recent = await db.select().from(messages).limit(20)
await client.end()

These connect as postgres, which owns the tables, so RLS policies don't restrict them.

Related

• Can I use Drizzle or Prisma without supabase-js?
• Row Level Security
• SSH access and direct Postgres
• Prisma