Database Migrations

Managing database schema changes systematically is crucial for application stability and development consistency. The Kenya Estates platform utilizes Supabase Migrations to handle this process.

What are Database Migrations?

Database migrations are a form of version control for your database schema. Each migration represents a set of changes to the database, such as creating tables, adding columns, or modifying constraints. They allow developers to evolve the database schema in a structured and repeatable way.

Why Use Migrations?

  • Consistency: Ensures all environments (local, staging, production) have the same database schema.
  • Collaboration: Allows multiple developers to work on database changes without conflicts.
  • Version Control: Schema changes are tracked in version control (Git), just like application code.
  • Rollback (Potentially): While not always straightforward, migrations can be designed to be reversible.
  • Automation: Simplifies deployment processes.

Supabase Migrations Workflow

We use the Supabase CLI to manage database migrations. The typical workflow is as follows:

1. Link Project and Start Local Development

Ensure your local environment is linked to your Supabase project and that your local Supabase services are running.

# Link to your Supabase project (only needs to be done once)
supabase login
supabase link --project-ref YOUR_PROJECT_ID
supabase start # Starts local Supabase stack (Docker)

It's recommended to pull any remote schema changes before making new ones:

supabase db pull

2. Make Schema Changes Locally

You can make schema changes using the Supabase Studio (locally at http://localhost:54323), a GUI like pgAdmin, or by directly executing SQL commands against your local database instance.

For example, creating a new table or adding a column to an existing table.

3. Generate a New Migration File

Once you've made your desired schema changes locally, you generate a new migration file. The Supabase CLI detects the differences between your current local database schema and the last migration applied.

supabase db diff -f MIGRATION_NAME

Replace MIGRATION_NAME with a descriptive name for your changes (e.g., add_user_bio_field). This command creates a new SQL file in the supabase/migrations directory.

Example: supabase/migrations/[timestamp]_add_user_bio_field.sql

It's good practice to review the generated SQL file to ensure it accurately reflects the intended changes.

4. Apply Migrations Locally (Optional Reset)

To ensure your migrations work correctly from a clean state, you can reset your local database and apply all migrations:

supabase db reset # Resets local database and applies all migrations
# OR to apply only new migrations:
# supabase migration up (if you haven't reset)

5. Commit and Push Migration Files

Add the new migration file(s) in supabase/migrations to Git, commit, and push them to your repository.

git add supabase/migrations
git commit -m "feat: add user bio field migration"
git push

6. Apply Migrations to Remote Environments (Staging/Production)

Migrations are typically applied to remote Supabase environments (like staging or production) through a CI/CD pipeline or manually using the Supabase CLI.

# Ensure you are linked to the correct project
# supabase link --project-ref TARGET_PROJECT_ID

supabase migration up

Alternatively, Supabase provides a GitHub Action for deploying migrations automatically when changes are pushed to specific branches. You can also manage migrations via the Supabase Dashboard under Database - Migrations.

Migration File Structure

Migration files are plain SQL files. They contain the SQL statements needed to apply the schema changes.

Example (supabase/migrations/[timestamp]_create_posts_table.sql):


CREATE TABLE posts (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID REFERENCES auth.users(id),
    title TEXT,
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add RLS policies
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own posts"
ON posts FOR SELECT
USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own posts"
ON posts FOR INSERT
WITH CHECK (auth.uid() = user_id);

Generating TypeScript Types

After applying migrations that change the database schema, it's essential to update your TypeScript types to reflect these changes. This ensures type safety in your application code.

supabase gen types typescript --project-id YOUR_PROJECT_ID --schema public > types/supabase.ts

This command fetches the schema from your Supabase project and generates TypeScript definitions, typically saved to a file like types/supabase.ts. This file should also be committed to version control. The package.json likely contains a script for this, e.g., npm run db:types.

Best Practices

  • Small, Atomic Migrations: Keep migrations focused on a single logical change.
  • Descriptive Names: Use clear names for migration files.
  • Test Migrations: Always test migrations in a local or staging environment before applying to production.
  • Avoid Manual Changes in Production: All schema changes to production should go through the migration process.
  • Backup Data: Before running significant migrations on production, ensure you have a recent database backup.
  • Idempotency: If possible, write SQL that can be run multiple times without causing errors (e.g., using IF NOT EXISTS or IF EXISTS). Supabase's diffing tool generally handles this well.