Why use Supabase Migrations?
Introduction
Supabase is a great way to off-load a lot of your DevOps onto an external provider. Out of the box, they give you a Postgres database, user authentication and management, logging and analytics, and a bunch of other useful tools. This allows you to focus on building critical feature sets, instead of having to also juggle setting up servers, manage updates, and all of that other stuff that is usually necessary as you scale from 0-10K users. I like not having to worry about that stuff too early.
The service also provides a nice way to easily interact with your databases while you are iterating. It let's you quickly alter the structure of your db and check the state of the data as you work. It makes for really quick development and I appreciate that a lot. It's fast and loose, and feels comfortable.
This is fine if you're just one person and you're not working on anything too serious. It's not good practice though because it isn't repeatable or scalable.
Benefits of Migrations
There are a many benefits to using migrations:
- Easy repeatability of changes
- Easy rollback of changes
- Easy to collaborate with a team
- Easy to deploy changes to production
- Version control
A migration is simply a set of instructions that you feed through your application into your database that does things like creating tables, columns, and sometimes seeding with data for testing. Migrations mean that you can make a set of changes locally while in development, which after they are confirmed working, you can implement on your production database without a hitch (hopefully).
Vewrite has reached a state of maturity that requires a more standardized workflow, and migrations are being added so that working on it locally will be easier.
Working with Supabase Migrations
Install the Supabase CLI
If you haven’t already installed the Supabase CLI, follow the installation instructions from the Supabase CLI documentation:
# Using npm:
npm install -g supabase
# Or using Homebrew (on macOS):
brew install supabase/tap/supabase
Verify the installation with:
supabase --version
Initialize Your Supabase Project
If you haven’t initialized a Supabase project locally yet, run:
supabase init
This creates a supabase directory in your project with the necessary configuration files.
Create a New Migration
To create a new migration, use the migration new command with a descriptive name:
supabase migration new add_users_table
This command will create a new SQL file in the supabase/migrations directory with a timestamp prepended to the filename. It will then open the file in your default editor (if configured) or simply create the file for you to edit.
Edit the Migration SQL File
Open the newly created migration file (e.g., supabase/migrations/20250202T123456_add_users_table.sql) and add your SQL statements. For example, to create a new users table:
-- Create users table
CREATE TABLE IF NOT EXISTS users (
id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
username text NOT NULL UNIQUE,
email text NOT NULL UNIQUE,
created_at timestamptz DEFAULT now()
);
Note: Make sure to include both up and down migration logic if you plan on rolling back changes manually. However, by default, Supabase’s migration workflow focuses on forward migrations.
Apply (Run) the Migration
Once you’ve written your migration, run it with:
supabase db push
This command will apply any pending migrations to your local database. If you’re working against your remote Supabase database, make sure you have your connection details configured correctly in your environment.
Version Control Your Migrations
Since migrations are just SQL files stored in your project, add them to your version control (e.g., Git) to keep track of database changes and collaborate with your team.
Rolling Out to Production
When you’re ready to deploy your changes:
- Ensure your production environment has the latest migration files.
- Run the migration command (similar to supabase db push) against your production database, or use your CI/CD pipeline to apply the migrations.
Additional Tips
- Review the Supabase CLI Docs: There are other commands available (like supabase db reset, supabase start, etc.) that help manage your local development environment.
- Test Locally First: Always run your migrations locally before applying them to your production database.
- Backup: Ensure you have proper backups before running migrations on production systems.
This should give you a quick start with creating and managing migrations using Supabase. Happy coding!