Database

Overview

The database layer uses:

PostgreSQL via Supabase
Drizzle for application-table queries
Supabase clients for Auth and Storage
Supabase Migrations for schema changes
Row Level Security for access control

Supabase Clients

Server Client

Use the existing request-bound factory in server functions, services and API handlers. It preserves provider cookie chunks and pending refreshes within the native Start request:

import { createSupabaseServerClient } from '@/lib/supabase/server';

async function readCurrentUser() {
  const supabase = await createSupabaseServerClient();
  const { data: { user }, error } = await supabase.auth.getUser();
  if (error) throw error;
  return user;
}

Browser Client

Use the existing cached browser factory for Auth or Storage. Prefer the shipped hooks for their existing UI flows:

import { createClient } from '@/lib/supabase/client';

const supabase = createClient();

App tables use the existing server-only getDb() owner. It validates DATABASE_URL, reuses the connection, and registers the complete Drizzle schema and relations. Reuse it inside a repository method:

import '@tanstack/react-start/server-only';
import { getDb } from '@/lib/db/database';

async function findUserRecord(userId: string) {
  return getDb().query.users.findFirst({
    where: (fields, { eq }) => eq(fields.id, userId),
  });
}

Use Supabase for Auth/session flows. Use Drizzle for app-table reads and writes. The direct database credential may bypass RLS: services must verify the actor and authorize the requested organization or object before calling a repository.

Database Queries

The examples below belong inside server-only repository methods after const db = getDb(). They illustrate Drizzle operations; they do not replace the existing services' authorization or billing rules. Reuse the shipped repositories for their current features. Stripe webhooks own purchase and subscription updates.

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 timestamped migration with the Supabase CLI, then edit its SQL:

bunx supabase migration new create_posts

supabase/migrations/<timestamp>_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

For an existing local database, apply pending migrations without resetting it:

bunx supabase migration up --local

bunx supabase db reset --local recreates the local database and deletes its data. Use reset only for a new disposable database, as described in Getting Started.

For your selected hosted project, review the pending migration before applying it:

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);

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

Was this page helpful?