# Drizzle ORM

> Use Drizzle with SupaHost to define tables and RLS policies in TypeScript and generate SQL migrations. Its runtime needs direct Postgres, which SupaHost offers only via the Business SSH tunnel.

Source: https://supahost.app/docs/integrations/drizzle · Updated 2026-11-11

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:

| You want to | Plan | How |
|---|---|---|
| Define tables and policies in TypeScript, generate SQL | Any | `drizzle-kit generate`, then apply the SQL to SupaHost |
| Iterate quickly with `drizzle-kit push` | Any | Against the Supabase CLI's local database |
| Run `drizzle-kit migrate` against SupaHost | Business, Enterprise | Through the SSH tunnel |
| Run Drizzle queries from your deployed app | Not supported | Use supabase-js (the Data API) in the app |

## Setup

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

```ts
// 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()`:

```ts
// 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](/docs/local-development/overview)) and use its database:

```bash
# .env
DATABASE_URL=postgresql://postgres:postgres@127.0.0.1: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:

```bash
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](/docs/local-development/migrations-workflow).

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

```bash
ssh -N -L 5432:127.0.0.1:5432 supahost@acme.supahost.space
```

```bash
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:

```ts
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](/docs/api/generating-types).

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

```ts
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?](/kb/orm-without-supabase-js)
- [Row Level Security](/docs/database/row-level-security)
- [SSH access and direct Postgres](/docs/platform/ssh)
- [Prisma](/docs/integrations/prisma)