Database

​

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

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

​

Why These Patterns Exist

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

​

1. Direct Drizzle (Server-Side)

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

• 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

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

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

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

• 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

• 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?

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

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

• 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

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

​

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

'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

​

Development Workflow

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

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

   bun run db:generate
   

3. Apply RLS policies
   Copy

   bun run db:rls
   

4. Push migrations to dev database
   Copy

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

   # 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)
   Copy

   # 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

1. ALWAYS backup first
   Copy

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

2. Apply to production
   Copy

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

   Copy

   # 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:
   Copy

   bun run db:generate
   bun run db:push
   

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

   bun run db:rls
   

​

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

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

   bun run db:generate
   bun run db:push
   

​

Iterative Schema Development

• 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

• 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

​

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