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:

​

1. Direct Drizzle (Server-Side)

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

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

​

3. Supabase Client (Client-Side)

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

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])
}

​

Which Pattern to Use?

• Server operations? → Use Direct Drizzle
• Client UI with loading states? → Use React Query + Server Actions
• Client-side auth or RLS needs? → Use Supabase Client

​

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