Supabase version control

Building robust applications requires more than just a functional database; it requires a disciplined approach to managing changes, testing, and deployment. This guide will focus on the DevOps side of Supabase, explaining how to handle database migrations, integrate with CI/CD pipelines, and manage test data.

---

How to Version Control Your Database Schema

The official and recommended way to version control your Supabase database schema is by using the Supabase CLI and its migration commands. This workflow treats your database schema like application code, enabling you to track changes, review them in pull requests, and deploy them to different environments consistently.

Core Concepts

• Migrations: A migration is a SQL file that contains the incremental changes to your database schema (e.g., CREATE TABLE, ALTER TABLE, CREATE FUNCTION).
• Supabase CLI: The CLI is your primary tool. It allows you to run a local Supabase instance, create new migration files, and apply or push those migrations to your remote projects.

The Workflow

1. Initialize your project: If you haven't already, run supabase init in your project's root directory. This creates a supabase/migrations folder where your migration files will live.

2. Create a new migration: Use the supabase migration new command with a descriptive name.

   supabase migration new create_posts_table

   This command generates a new SQL file with a timestamp prefix (e.g., 20241026120000_create_posts_table.sql) inside the supabase/migrations directory.

3. Write your SQL: Edit the new migration file to add your schema changes.

   -- In supabase/migrations/20241026120000_create_posts_table.sql
   CREATE TABLE public.posts (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
     title TEXT NOT NULL,
     content TEXT,
     created_at TIMESTAMPTZ DEFAULT NOW()
   );

4. Apply migrations locally: To test your changes, use supabase db reset. This command drops your local database, reapplies all your migrations from scratch, and runs your seed file.

5. Deploy to remote: Once your migrations are complete and you are ready to deploy, you can push them to your linked Supabase project using the supabase db push command.

---

Using pg_dump, pg_restore, or Flyway with Supabase

While the Supabase CLI's migration system is the recommended approach for schema changes, you can still use standard PostgreSQL tools like pg_dump, pg_restore, and other third-party tools like Flyway.

• pg_dump and pg_restore: These tools are invaluable for data management and backups. You can use them to create a full or partial dump of your Supabase database and restore it to another PostgreSQL instance (or another Supabase project). You'll need the database connection string, which can be found in your Supabase project settings. The pg_dump command is often used to create a backup before a major change or to copy data between environments.
• Flyway: Flyway is a popular database migration tool. You can absolutely use Flyway with a Supabase database by configuring it to connect to your Supabase project's connection string. This approach is not officially recommended as it bypasses the native Supabase CLI workflow, but it can be useful if your team already has an existing CI/CD process built around Flyway [4].

Key consideration: Supabase's managed database has system schemas (like auth and storage) that you should not alter with external migration tools. When using tools like pg_dump or Flyway, it's best to scope your operations to the public schema.

---

Testing Your Database Schema in a CI/CD Pipeline

Automated testing of your database schema is a crucial part of a robust CI/CD pipeline. The goal is to ensure that your migrations are valid, don't break existing functionality, and are consistently applied.

1. Run a local Supabase stack: In your CI/CD pipeline (e.g., GitHub Actions), use the Supabase CLI to start a clean, local database instance.

   - name: Start Supabase local development setup
     run: supabase start

2. Apply all migrations: After the local database is running, apply all your migration files to it.

   - name: Apply database migrations
     run: supabase db reset

3. Run database tests: Supabase supports database unit testing using the pgTAP extension [3]. You can write tests directly in SQL that check for things like:

   • Table and column existence.
   • Correct RLS policy behavior.
   • Function and trigger correctness.

   You can then run these tests with the Supabase CLI.

   # Run tests in the CI pipeline
   supabase test db

This workflow ensures that every time a pull request is opened, your database schema changes are validated on a fresh, isolated environment before they are merged and deployed.

---

Seeding Test Data in Local Development

Seeding your database with consistent, reproducible test data is essential for local development and testing. Supabase provides a simple and effective way to do this with the supabase/seed.sql file.

• Default Seed File: By default, the Supabase CLI looks for a file named supabase/seed.sql. This file contains SQL INSERT statements that are executed every time you run supabase db reset.
• Best Practice: Your seed file should only contain INSERT statements, not CREATE or ALTER statements, as the schema will already have been created by your migrations.
• Using Fakes and UUIDs: To ensure consistency across environments, it's a good practice to use hardcoded UUIDs for key test records. This allows your application tests to rely on predictable data. You can also use libraries like Faker in a script to generate more realistic dummy data and then output the SQL inserts into your seed.sql file [4].

For local development, you can also use tools like Snaplet, a community-contributed project, to automatically generate realistic, production-like dummy data based on your schema [4]. This provides a more powerful and flexible alternative to static SQL seed files.

Sources

1. Supabase. "Local development with schema migrations | Supabase Docs". https://supabase.com/docs/guides/local-development/overview
2. Supabase. "How to backup and restore Supabase Postgres database - Nesin.io". https://nesin.io/blog/backup-restore-supabase-postgres-database
3. Supabase. "Testing Your Database | Supabase Docs". https://supabase.com/docs/guides/database/testing
4. Supabase. "Seeding your database | Supabase Docs". https://supabase.com/docs/guides/local-development/seeding-your-database
5. Supabase. "GitHub Actions | Supabase Docs". https://supabase.com/docs/guides/deployment/ci-cd