Database

This guide covers working with Supabase PostgreSQL and Drizzle ORM in Titan projects.

​

Core Concepts

• PostgreSQL: Supabase’s underlying database engine
• Drizzle ORM: Type-safe database client for TypeScript
• Row Level Security (RLS): PostgreSQL’s feature for row access control
• Migrations: Version-controlled database schema changes

​

Development vs Production

​

Initial Setup

After you’ve spun up a fresh Titan project, you would’ve had a thorough conversation with agent mode in Cursor to come up with all the relevant documentation and all the tables you would need for your MVP.

You can now use this guide by leveraging the database schema that you’ve created to start creating your initial tables in your database, and then subsequently making changes to the database schema as you see fit over time.

1. Run the Database Initialization Script

• This will create a new database in Supabase and apply the initial schema to it - i.e. create all the tables defined in db/schema/index.ts.
• You can optionally seed the database with test data (It will ask you if you want to do this).

bun run db:init

2. Verify the Setup

• This will open the database studio where you can explore your database to check that the tables have been created correctly.

bun run db:studio

​

Data Access Patterns

Titan uses three primary patterns for database access. Each serves different needs:

​

Why These Patterns Exist

The Problem: Different parts of your app have different needs:

• Server pages need fast, direct database access
• Interactive UIs need loading states and caching
• Client-side features need security and real-time updates

The Solution: Use the right pattern for each situation instead of forcing one approach everywhere.

​

1. Direct Drizzle (Server-Side)

Why use this?

• Fastest possible database queries (no extra network hops)
• Full TypeScript safety (catches errors at compile time)
• Perfect for server pages that render once

When to use:

• Loading data for server components (pages that don’t need interactivity)
• Form submissions and mutations
• API routes
• Any time you don’t need loading states or caching

// utils/actions/users.ts
'use server'
import { createDirectClient } from '@/lib/drizzle'
import { users } from '@/db/schema'
import { eq } from 'drizzle-orm'

export async function getUser(id: string) {
  const db = createDirectClient()
  const user = await db.select().from(users).where(eq(users.id, id))
  return user[0] || null
}

​

2. React Query with Server Actions

Why use this?

• Shows loading spinners while data loads (better UX)
• Caches data so repeated requests are instant
• Automatically refetches stale data
• Handles error states gracefully

When to use:

• Interactive components that need loading states
• Data that gets requested multiple times (user profiles, lists)
• When you want automatic background refetching
• Client components that need to show “Loading…” or error messages

// 1. Server action
'use server'
import { createDirectClient } from '@/lib/drizzle'
import { users } from '@/db/schema'

export async function getUsers() {
  const db = createDirectClient()
  return db.select().from(users)
}

// 2. React Query hook
'use client'
import { useQuery } from '@tanstack/react-query'
import { getUsers } from '@/utils/actions/users'

export function useUsers() {
  return useQuery({
    queryKey: ['users'],
    queryFn: getUsers,
    staleTime: 60 * 1000 // Cache for 1 minute
  })
}

// 3. Use in component
function UsersList() {
  const { data: users, isLoading, error } = useUsers()
  
  if (isLoading) return <div>Loading users...</div>
  if (error) return <div>Error loading users</div>
  
  return (
    <div>
      {users?.map(user => <div key={user.id}>{user.name}</div>)}
    </div>
  )
}

​

3. Supabase Client (Client-Side)

Why use this?

• Automatically enforces Row Level Security (RLS) policies
• Enables real-time subscriptions (data updates live)
• Handles authentication seamlessly
• Works directly from the browser

When to use:

• Real-time features (chat, live updates, collaborative editing)
• Client-side authentication flows
• When you need RLS to automatically filter data by user
• File uploads to Supabase Storage

Don’t use for:

• Simple data fetching (React Query + Server Actions is better)
• Server-side operations (Direct Drizzle is faster)

'use client'
import { createClient } from '@/lib/supabase'
import { useState, useEffect } from 'react'

function UserProfile({ userId }) {
  const [user, setUser] = useState(null)
  
  useEffect(() => {
    async function fetchUser() {
      const supabase = createClient()
      const { data } = await supabase
        .from('users')
        .select('*')
        .eq('id', userId)
        .single()
      setUser(data)
    }
    fetchUser()
  }, [userId])
  
  // Real-time example
  useEffect(() => {
    const supabase = createClient()
    const channel = supabase
      .channel('user-changes')
      .on('postgres_changes', 
        { event: 'UPDATE', schema: 'public', table: 'users' },
        (payload) => setUser(payload.new)
      )
      .subscribe()
    
    return () => supabase.removeChannel(channel)
  }, [])
}

​

Decision Tree: Which Pattern to Use?

Ask yourself these questions in order:

1. Is this running on the server? (Server Components, API routes, form actions) → Use Direct Drizzle - Fastest and most direct

2. Do users need to see loading states? (Interactive UI, dashboards) → Use React Query + Server Actions - Best user experience

3. Do you need real-time updates? (Chat, live collaboration, notifications) → Use Supabase Client - Only option for real-time

4. Do you need client-side auth? (Login forms, protected routes) → Use Supabase Client - Handles auth automatically

​

Common Beginner Mistakes

❌ Don’t do this:

• Using Supabase Client for everything (slower, more complex)
• Using Direct Drizzle in client components (won’t work)
• Using React Query for server components (unnecessary overhead)

✅ Do this instead:

• Match the pattern to your specific need
• Start with Direct Drizzle for server-side data
• Add React Query when you need loading states
• Only use Supabase Client for real-time or auth features

​

Schema Design and Queries

​

Schema Definition

Define your database schema with TypeScript:

// db/schema/users.ts
import { pgTable, serial, text, timestamp, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow()
});

// Export all tables from index
// db/schema/index.ts
export * from './users';
export * from './products';

​

Best Practices

• Organize tables by domain in separate files
• Use UUID or serial for IDs
• Add created/updated timestamps to all tables
• Create indexes for frequently queried columns
• Define relationships with references
• Use enums for fixed values
• Add appropriate constraints

​

Common Query Patterns

// Select
const users = await db.select().from(userTable);

// With conditions
const activeUsers = await db.select()
  .from(userTable)
  .where(eq(userTable.status, 'active'));

// Insert
const newUser = await db.insert(userTable)
  .values({ name: 'John', email: 'john@example.com' })
  .returning();

// Update
const updated = await db.update(userTable)
  .set({ status: 'inactive' })
  .where(eq(userTable.id, userId))
  .returning();

// Delete
const deleted = await db.delete(userTable)
  .where(eq(userTable.id, userId));

​

Row Level Security (RLS)

RLS restricts which rows a user can access based on their identity.

​

Common RLS Patterns

-- Users can only access their own data
CREATE POLICY "Users can access own data"
ON users FOR ALL
USING (auth.uid()::text = user_id);

-- Public read, authenticated write
CREATE POLICY "Public read access"
ON posts FOR SELECT
USING (true);

CREATE POLICY "Auth users can create"
ON posts FOR INSERT
WITH CHECK (auth.uid()::text = author_id);

​

Authorization in Server Actions

Always add authorization checks in server actions:

'use server'
import { createDirectClient } from '@/lib/drizzle'
import { isAuthorized } from '@/utils/data/user/isAuthorized'

export async function updateUser(userId, data) {
  // Check authorization
  const { authorized, message } = await isAuthorized(userId)
  if (!authorized) throw new Error(message)
  
  // Proceed with database operation
  const db = createDirectClient()
  return db.update(users)
    .set(data)
    .where(eq(users.id, userId))
    .returning()
}

​

Migrations

Titan uses Drizzle for database migrations:

​

Development Workflow

The typical development workflow for database schema changes:

1. Create or modify schema in db/schema/ files and update RLS policies in db/rls.sql

   // Example: Add a new column to an existing table
   export const users = pgTable('users', {
     // Existing columns
     id: serial('id').primaryKey(),
     email: varchar('email').notNull().unique(),
     // New column being added
     phoneNumber: varchar('phone_number', { length: 15 }),
   });

2. Generate migrations based on schema changes

   bun run db:generate

3. Apply RLS policies

   bun run db:rls

4. Push migrations to dev database

   bun run db:push

5. Test your changes thoroughly with the updated schema

6. If you’re not satisfied with the schema, you have two options:

   Option A: Reset and start over (for significant changes)

   # Drop all tables and reset the schema
   bun run db:drop
   
   # Edit your schema files, then regenerate and push
   bun run db:generate
   bun run db:push
   
   # Reapply RLS policies
   bun run db:rls
   
   # Optionally reseed with test data
   bun run db:seed

   Option B: Create additional migrations (for incremental changes)

   # Edit your schema files with the new changes
   
   # Generate new migration
   bun run db:generate
   
   # Push the new migration
   bun run db:push

7. Repeat steps 1-5 until satisfied with your schema

8. When ready for production, follow the safe deployment process

​

Safe Dev-to-Production Workflow

Always follow this workflow to safely apply database changes:

1. ALWAYS backup first

   # Using Supabase CLI (if available)
   supabase db dump --project-ref YOUR_PROD_REF -f backup.sql

2. Apply to production

   # Update your .env file to use the production database credentials first
   bun run db:push
   bun run db:rls

   # OR use a production config
   bun run db:push --config=drizzle-prod.config.ts
   bun run db:rls

​

Quick Reference

​

Command Cheatsheet

# Drizzle Commands
bun run db:generate         # Generate migrations from schema changes
bun run db:push             # Apply migrations to Supabase (dev or prod based on config)
bun run db:studio           # Open database studio to explore/edit data
bun run db:rls              # Apply RLS policies
bun run db:init             # Initialize database (first-time setup)
bun run db:seed             # Seed database with test data
bun run db:drop             # Drop database tables (careful!)

​

Best Practices

1. For Database Access

   • Use Direct Drizzle in Server Actions for performance and type safety
   • Use React Query for client-side state management
   • Always add authorization checks in server actions

2. For Schema Design

   • Organize tables by domain
   • Use proper indexes and constraints
   • Add timestamps to all tables

3. For Security

   • Define comprehensive RLS policies
   • Use authorization checks in server actions
   • Follow principle of least privilege

4. For Performance

   • Use React Query’s caching
   • Create proper database indexes
   • Use transactions for related operations

​

Common Scenarios

​

Adding a New Table with RLS

1. Create or edit schema file in db/schema/
2. Export the table from the main schema file
3. Generate and apply migrations:

   bun run db:generate
   bun run db:push

4. Add RLS policies to db/rls.sql
5. Apply RLS policies:

   bun run db:rls

​

Modifying an Existing Table (adding a new column etc.)

1. Edit the schema file
2. Generate and apply migrations:

   bun run db:generate
   bun run db:push

​

Iterative Schema Development

When to reset vs. when to create additional migrations:

Reset approach (Option A) is best when:

• You’re in early development with no valuable data in the dev database
• You’ve made complex structural changes that are difficult to migrate
• You want a clean slate to avoid migration conflicts
• You’re experimenting with significantly different schema designs

Incremental migration approach (Option B) is best when:

• You have test data you want to preserve
• Changes are small and straightforward
• You’re closer to production and want to test actual migration paths
• You want to maintain migration history for documentation

Remember that in production, you’ll almost always use incremental migrations unless you’re doing a complete application redesign.

​

Troubleshooting Database Issues

• Reset database setup: bun run db:init
• Check database state: bun run db:studio
• For deeper issues, you may need to:
  1. Drop all tables: bun run db:drop
  2. Regenerate migrations: bun run db:generate
  3. Apply migrations: bun run db:push
  4. Apply RLS: bun run db:rls
  5. Seed data: bun run db:seed