Extending the Database Schema

This guide explains how to extend your database schema using Supabase migrations and CLI commands.

Creating a New Migration

Open your terminal and navigate to the Supabase package directory:
```
cd packages/supabase
```
Create a new migration file:
```
npx supabase migration new add_projects_table
```
This will create a new file in packages/supabase/migrations/ with a timestamp prefix.

Edit the migration file to define your schema changes. For example:

-- Create a new table for projects
create table public.projects (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  description text,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
  owner_id uuid references auth.users(id) on delete cascade not null
);

-- Enable RLS
alter table public.projects enable row level security;

-- Create policies
create policy "Users can view their own projects"
  on public.projects
  for select
  using (auth.uid() = owner_id);

create policy "Users can create their own projects"
  on public.projects
  for insert
  with check (auth.uid() = owner_id);

create policy "Users can update their own projects"
  on public.projects
  for update
  using (auth.uid() = owner_id);

create policy "Users can delete their own projects"
  on public.projects
  for delete
  using (auth.uid() = owner_id);

-- Create indexes
create index projects_owner_id_idx on public.projects(owner_id);
create index projects_created_at_idx on public.projects(created_at);

-- Add updated_at trigger
create trigger handle_updated_at
  before update on public.projects
  for each row
  execute function common.handle_updated_at();

Add TypeScript types for your new table:

// packages/supabase/types.ts
export interface Project {
  id: string;
  name: string;
  description: string | null;
  created_at: string;
  updated_at: string;
  owner_id: string;
}

Applying the Migration

To apply the new migration to your local Supabase instance:

Make sure your local Supabase is running:
```
npx supabase start
```
Reset the database and apply all migrations:
```
npx supabase db reset
```

Testing the Changes

After applying migrations, you should:

Write integration tests for your RLS policies
Update end-to-end tests to cover new functionality
Test database functions and triggers

Example integration test:

import { describe, it, expect } from "vitest";
import { supabase } from "@/lib/supabase";

describe("Projects RLS Policies", () => {
  it("allows users to create their own projects", async () => {
    const { data, error } = await supabase
      .from("projects")
      .insert({
        name: "Test Project",
        description: "A test project",
      })
      .select()
      .single();

    expect(error).toBeNull();
    expect(data).toBeDefined();
    expect(data.name).toBe("Test Project");
  });
});

Useful Supabase CLI Commands for Local Development

Here are some helpful Supabase CLI commands for managing your local development environment:

Start Supabase: npx supabase start This starts all Supabase services locally.
Stop Supabase: npx supabase stop This stops all running Supabase services.
Reset Supabase: npx supabase db reset This resets your local database and reapplies all migrations.
Generate Types: pnpm generate:types This updates TypeScript types based on your database schema.
Create a New Migration: npx supabase migration new my_migration_name This creates a new migration file.
Apply Migrations: npx supabase db push This applies pending migrations to your database.

Remember to run these commands from the packages/supabase directory.

Best Practices

Migration Files:
- Use descriptive names
- Include both "up" and "down" migrations
- Keep migrations atomic and focused
- Comment complex SQL operations
Testing:
- Write integration tests for RLS policies
- Test edge cases in your policies
- Verify triggers and functions
- Run pnpm test:integration to test database changes
- Run pnpm test:e2e to verify end-to-end functionality
Type Safety:
- Keep TypeScript types up to date
- Use generated types in your application
- Validate data at runtime

Next Steps

Now that you know how to extend your database schema and use Supabase CLI commands, you're ready to:

Creating a New Migration​

Applying the Migration​

Testing the Changes​

Useful Supabase CLI Commands for Local Development​

Best Practices​

Next Steps​