# Using Drizzle ORM with Supabase in Next.js: A Complete Guide

> In this comprehensive guide, we'll walk through integrating Drizzle ORM with your Next.js Supabase project, offering you the best of both worlds: Supabase's powerful features and Drizzle's type-safe query builder.

*Published: 2024-12-21*
*Canonical: https://makerkit.dev/blog/tutorials/drizzle-supabase*

---

**TL;DR: Drizzle ORM works with Supabase by connecting straight to your Supabase Postgres database via a connection string. You pull your existing schema with `drizzle-kit pull`, generate TypeScript types from it, and get fully type-safe queries — while keeping Supabase Auth, Storage, and Row Level Security intact. Setup takes about 15 minutes.**

In modern web development, type safety and developer experience are paramount. While Supabase provides an excellent PostgreSQL database with a powerful client, combining it with Drizzle ORM can enhance your development workflow with type-safe database queries and improved developer tooling.

In this comprehensive guide, we'll walk through integrating Drizzle ORM with your Makerkit-based Next.js Supabase project, offering you the best of both worlds: Supabase's powerful features and Drizzle's type-safe query builder.

## What is Drizzle ORM?

Drizzle is a TypeScript ORM designed for developer productivity. It provides:
- Type-safe database queries with TypeScript
- SQL-like query builder syntax
- Robust transaction support
- Automatic schema generation
- Enhanced developer experience with autocomplete

Still deciding between ORMs? Our [Drizzle vs Prisma comparison](/blog/tutorials/drizzle-vs-prisma) covers the trade-offs in depth — and if you're on TanStack Start instead of Next.js, see [which ORM fits a TanStack Start SaaS](/blog/tutorials/tanstack-start-drizzle-vs-prisma).

## Prerequisites

Before we begin, ensure you have:
- A working Makerkit Next.js project, however most advice applies to any Next.js project
- A Supabase project set up and running
- Basic understanding of TypeScript and SQL
- Familiarity with package managers (we'll use pnpm in this guide)

## Implementation Guide

### 1. Setting Up Dependencies

First, let's add the necessary packages to your project. We'll need Drizzle ORM, its toolkit, and some supporting libraries:

```bash
pnpm --filter "@kit/supabase" add jwt-decode postgres
pnpm --filter "@kit/supabase" add -D drizzle-orm drizzle-kit
```

### 2. Configuring Drizzle

Create a Drizzle configuration file to define how it should interact with your Supabase database. This configuration will handle schema generation and database connections.

```javascript
// drizzle.config.js
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/schema.ts',
  out: './src/drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL ?? 'postgresql://postgres:postgres@127.0.0.1:54322/postgres'
  },
  schemaFilter: ['public'],
  verbose: true,
  strict: true,
});
```

### 3. Creating the Drizzle Client

The heart of our implementation is the Drizzle client. We'll create two versions:
- An admin client that bypasses Row Level Security (RLS)
- A regular client that respects RLS

Here's our implementation:

```typescript
// src/clients/drizzle-client.ts
import 'server-only';
import { DrizzleConfig, sql } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/postgres-js';
import { JwtPayload, jwtDecode } from 'jwt-decode';
import postgres from 'postgres';
import { z } from 'zod';

import * as schema from '../drizzle/schema';

const SUPABASE_DATABASE_URL = z
  .string({
    description: 'The URL of the Supabase database',
    required_error: 'The environment variable SUPABASE_DATABASE_URL is required',
  })
  .url()
  .parse(process.env.SUPABASE_DATABASE_URL!);

// Configuration setup
const config = {
  casing: 'snake_case',
  schema,
} satisfies DrizzleConfig<typeof schema>;

// Create both admin and RLS-protected clients
const adminClient = drizzle({
  client: postgres(SUPABASE_DATABASE_URL, { prepare: false }),
  ...config,
});

const rlsClient = drizzle({
  client: postgres(SUPABASE_DATABASE_URL, { prepare: false }),
  ...config,
});
```

### 4. Schema Generation and Management

Generate your TypeScript schema from your existing Supabase database:

```bash
pnpm --filter "@kit/supabase" drizzle pull
```

This command creates a `schema.ts` file containing TypeScript types that match your database schema. Remember to add the auth schema reference:

```typescript
// src/drizzle/schema.ts
/* eslint-disable */
const authSchema = pgSchema('auth');

const usersInAuth = authSchema.table('users', {
  id: uuid('id').primaryKey(),
});
```

### 5. Practical Examples

Let's explore some common use cases using Drizzle with our schema:

#### Account Management

In the example below, we'll create a new team account and add it to the `accounts` table.

```typescript
// Create a new team account
async function createTeamAccount(name: string, ownerUserId: string) {
  const client = await getDrizzleSupabaseClient();

  return client.runTransaction(async (tx) => {
    // Create the account
    const [account] = await tx
      .insert(accounts)
      .values({
        name,
        primaryOwnerUserId: ownerUserId,
        isPersonalAccount: false,
        slug: name.toLowerCase().replace(/\s+/g, '-'),
        createdAt: new Date().toISOString(),
      })
      .returning();

    return account;
  });
}

// Get user's accounts with memberships
async function getUserAccounts(userId: string) {
  const client = await getDrizzleSupabaseClient();

  return client.runTransaction(async (tx) => {
    const userAccounts = await tx
      .select({
        account: accounts,
        role: accountsMemberships.accountRole,
      })
      .from(accounts)
      .innerJoin(
        accountsMemberships,
        eq(accounts.id, accountsMemberships.accountId)
      )
      .where(eq(accountsMemberships.userId, userId));

    return userAccounts;
  });
}
```

#### Project Management

In the example below, we'll create a new project with an owner.

```typescript
// Create a new project with owner
async function createProject(name: string, accountId: string, ownerId: string) {
  const client = await getDrizzleSupabaseClient();

  return client.runTransaction(async (tx) => {
    // Create project
    const [project] = await tx
      .insert(projects)
      .values({
        name,
        accountId,
        createdAt: new Date().toISOString(),
      })
      .returning();

    // Add owner as project member
    await tx.insert(projectMembers).values({
      projectId: project.id,
      accountId: ownerId,
      role: 'owner',
      createdAt: new Date().toISOString(),
    });

    return project;
  });
}

// Get project with members
async function getProjectWithMembers(projectId: string) {
  const client = await getDrizzleSupabaseClient();

  return client.runTransaction(async (tx) => {
    const project = await tx
      .select()
      .from(projects)
      .where(eq(projects.id, projectId))
      .limit(1);

    if (!project.length) return null;

    const members = await tx
      .select({
        member: projectMembers,
        account: accounts,
      })
      .from(projectMembers)
      .innerJoin(
        accounts,
        eq(projectMembers.accountId, accounts.id)
      )
      .where(eq(projectMembers.projectId, projectId));

    return {
      ...project[0],
      members,
    };
  });
}
```

#### Subscription Management

In the example below, we'll get the account's active subscription with relative subscription items.

```typescript
// Get account's active subscription with items
async function getActiveSubscription(accountId: string) {
  const client = await getDrizzleSupabaseClient();

  return client.runTransaction(async (tx) => {
    const subscription = await tx
      .select()
      .from(subscriptions)
      .where(
        and(
          eq(subscriptions.accountId, accountId),
          eq(subscriptions.active, true)
        )
      )
      .limit(1);

    if (!subscription.length) return null;

    const items = await tx
      .select()
      .from(subscriptionItems)
      .where(eq(subscriptionItems.subscriptionId, subscription[0].id));

    return {
      ...subscription[0],
      items,
    };
  });
}

// Track a new order
async function createOrder(
  accountId: string,
  customerId: number,
  items: Array<{ productId: string, variantId: string, quantity: number }>
) {
  const client = await getDrizzleSupabaseClient();

  return client.runTransaction(async (tx) => {
    const [order] = await tx
      .insert(orders)
      .values({
        accountId,
        billingCustomerId: customerId,
        status: 'pending',
        billingProvider: 'stripe',
        totalAmount: 0, // Calculate based on items
        currency: 'USD',
        createdAt: new Date().toISOString(),
      })
      .returning();

    // Insert order items
    await tx.insert(orderItems).values(
      items.map(item => ({
        orderId: order.id,
        ...item,
        createdAt: new Date().toISOString(),
      }))
    );

    return order;
  });
}
```

#### Working with Notifications

In the example below, we'll add a new notification to the `notifications` table.

```typescript
// Add a new notification
async function addNotification(accountId: string, message: string) {
  const client = await getDrizzleSupabaseClient();

  return client.runTransaction(async (tx) => {
    const [notification] = await tx
      .insert(notifications)
      .values({
        accountId,
        type: 'info',
        body: message,
        channel: 'in_app',
        createdAt: new Date().toISOString(),
      })
      .returning();

    return notification;
  });
}

// Get unread notifications
async function getUnreadNotifications(accountId: string) {
  const client = await getDrizzleSupabaseClient();

  return client.runTransaction(async (tx) => {
    return tx
      .select()
      .from(notifications)
      .where(
        and(
          eq(notifications.accountId, accountId),
          eq(notifications.dismissed, false),
          gt(notifications.expiresAt, new Date())
        )
      )
      .orderBy(desc(notifications.createdAt));
  });
}
```

## Security Considerations

1. **Environment Variables**: Keep your `SUPABASE_DATABASE_URL` secure and never commit it to your repository. Store it in:
   - `.env.development` for local development
   - Your hosting provider's environment variables for production
2. **Row Level Security**: The regular client respects Supabase's RLS policies. Only use the admin client when absolutely necessary and in controlled environments.
3. **Server-Side Usage**: Drizzle clients should only be used in server-side code (Server Components, Server Actions, or API Routes). They will fail if included in client-side components.

## Benefits and Best Practices

Working with Supabase and Drizzle ORM can provide several benefits:

1. **Type Safety**: Leverage TypeScript's type system for database queries
2. **Developer Experience**: Enjoy autocomplete and inline errors in your IDE
3. **Performance**: Benefit from Drizzle's efficient query building and execution
4. **Maintainability**: Keep your database schema in sync with your TypeScript types
5. **Security**: Respect RLS policies while maintaining the ability to bypass them when needed

## Conclusion

Integrating Drizzle ORM with Supabase in your Next.js project provides a powerful combination of features and developer experience. While it requires some initial setup, the benefits of type-safe queries, improved developer tooling, and maintained security features make it a valuable addition to your stack.

Remember to keep your database credentials secure, respect RLS policies, and follow server-side rendering best practices. With these considerations in mind, you can build robust, type-safe applications with confidence.

For more advanced usage and configuration options, refer to the [Drizzle documentation](https://orm.drizzle.team/docs/overview) and [Supabase documentation](https://supabase.com/docs).

Ready to learn how to build a SaaS? Check out the [free Makerkit course](/courses/nextjs-turbo/introduction) to get started.

**Are you looking for a Drizzle-native kit?** Check out the [Drizzle SaaS Starter Kit](/drizzle) to get started.

**Weighing Drizzle against Prisma first?** Read the [Drizzle vs Prisma comparison](/blog/tutorials/drizzle-vs-prisma) for the full breakdown.