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

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

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:

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.

// 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:

// 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'
  })
  .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:

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:

// 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.

// 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.

// 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.

// 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.

// 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

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
Row Level Security: The regular client respects Supabase's RLS policies. Only use the admin client when absolutely necessary and in controlled environments.
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:

Type Safety: Leverage TypeScript's type system for database queries
Developer Experience: Enjoy autocomplete and inline errors in your IDE
Performance: Benefit from Drizzle's efficient query building and execution
Maintainability: Keep your database schema in sync with your TypeScript types
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 and Supabase documentation.