Database

Overview

The database layer uses:

PostgreSQL via Supabase
Supabase Client for type-safe queries
Supabase Migrations for schema changes
Row Level Security for access control

Architecture

client.ts

server.ts

config.toml

Supabase Clients

Server Client

Use in Server Components, API routes, and server actions:

src/lib/supabase/server.ts

import 'server-only';

import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';
import { Database } from '@/types/db';

export async function createSupabaseServerClient() {
  const cookieStore = await cookies();

  return createServerClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll: () => cookieStore.getAll(),
        setAll: (cookiesToSet) => {
          cookiesToSet.forEach(({ name, value, options }) =>
            cookieStore.set(name, value, options)
          );
        },
      },
    }
  );
}

Browser Client

Use in Client Components:

src/lib/supabase/client.ts

import { createBrowserClient } from '@supabase/ssr';
import { Database } from '@/types/db';

export function createClient() {
  return createBrowserClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );
}

Direct Postgres Client

App tables use a direct Postgres connection through Drizzle:

src/lib/db/database.ts

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const client = postgres(process.env.DATABASE_URL!, {
  prepare: false,
});

export const db = drizzle(client);

Use Supabase for auth/session flows. Use Drizzle + DATABASE_URL for app-table reads and writes.

Database Queries

Select

const user = await db.query.users.findFirst({
  where: (fields, { eq }) => eq(fields.id, userId),
});

const activeProducts = await db.query.products.findMany({
  with: {
    prices: true,
  },
  where: (fields, { eq }) => eq(fields.active, true),
});

Insert

await db.insert(customers).values({
  userId,
  stripeCustomerId,
});

Update

await db
  .update(users)
  .set({ fullName: 'New Name' })
  .where(eq(users.id, userId));

Delete

await db.delete(customers).where(eq(customers.userId, userId));

Relations (Joins)

Query related tables with Drizzle relations:

const subscription = await db.query.subscriptions.findFirst({
  with: {
    price: {
      with: {
        product: true,
      },
    },
  },
  where: (fields, { eq }) => eq(fields.userId, userId),
});

Migrations

Create a Migration

Create a new SQL file in supabase/migrations/:

supabase/migrations/20240101_create_posts.sql

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own posts
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);

Apply Migrations

# Reset database (applies all migrations)
bunx supabase db reset

# Push to production
bunx supabase db push

Type Generation

Generate TypeScript types from your database schema:

bunx supabase gen types typescript --local > src/types/db.ts

This creates type-safe interfaces for all tables:

import { Database } from '@/types/db';

type User = Database['public']['Tables']['users']['Row'];
type NewUser = Database['public']['Tables']['users']['Insert'];

Row Level Security

All tables should have RLS enabled. Common patterns:

-- Users can only read their own data
CREATE POLICY "Users can view own data"
  ON users FOR SELECT
  TO authenticated
  USING (auth.uid() = id);

-- Users can update their own data
CREATE POLICY "Users can update own data"
  ON users FOR UPDATE
  TO authenticated
  USING (auth.uid() = id);

-- Anyone can read public data
CREATE POLICY "Public read access"
  ON posts FOR SELECT
  TO anon
  USING (is_public = true);

Project Structure - Where files live
Environment Variables - Database URL config
Authentication - User management

Was this page helpful?

Database

On this page