Vishal's Blog

A Comprehensive Guide to Supabase Migration in Next.js Projects

Vishal's photo
Vishal
·

5 min read

In this article, we will walk you through the detailed steps of setting up and managing database migrations for a Next.js project integrated with Supabase. Database migrations are a crucial part of maintaining a structured, versioned approach to managing your database schema changes. Supabase makes this process simple by providing a powerful migration system, and the Supabase CLI tool helps streamline the process.

Let’s dive into how you can configure and apply migrations in your Supabase-powered Next.js project.

1. Directory Structure for Supabase Migration

The first step is to set up the directory structure for your Supabase migrations. In your project root, create a supabase folder and include subfolders for migrations and configuration files. Here's how the structure should look:

pgsqlCopy/supabase
  ├── migrations
  │    ├── 20240206_create_users.sql
  │    ├── 20240206_create_llm_providers.sql
  ├── supabase/config.toml
  ├── supabase/schema.sql

  • migrations: This folder holds all your migration scripts. Each file in this directory should represent a single migration step with a timestamp to maintain the order.

  • config.toml: This configuration file stores settings related to your Supabase project, including the database connection details.

  • schema.sql: This file is used to define the initial schema of your database (optional, but useful for setting up your structure initially).

2. Adding SQL Queries to Migration Files

Each migration is a separate SQL file with queries that will modify the database schema. For example, you might want to create a users table and an llm_providers table. Here are two sample migration files:

20240206_create_users.sql

sqlCopyCREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

20240206_create_llm_providers.sql

sqlCopyCREATE TABLE llm_providers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    api_url VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Make sure each SQL file follows the timestamp-based naming convention (<timestamp>_<migration_name>.sql) as shown above. The migration file names should have the same timestamp format (e.g., 20240206_), ensuring they run in the proper order.

3. Install Supabase CLI

To interact with Supabase and manage migrations from your local machine, you'll need to install the Supabase CLI.

Step 3.1: Install Scoop (if not already installed)

Scoop is a command-line installer for Windows. If you don't have it installed, you can install it by running the following command in PowerShell:

bashCopySet-ExecutionPolicy RemoteSigned -scope CurrentUser
iex ((New-Object System.Net.WebClient).DownloadString('https://get.scoop.sh'))

Step 3.2: Install Supabase CLI using Scoop

Once Scoop is installed, you can install the Supabase CLI with the following command:

scoop install supabase

This command installs the Supabase CLI, which you will use to interact with your Supabase project.

4. Login to Supabase Using CLI

After installing the Supabase CLI, you need to log in to your Supabase account using the CLI tool. Run the following command:

supabase login

You will be prompted to authenticate via a browser, where you can log into your Supabase account and grant access to the CLI.

5. Initialize Supabase in Your Project Directory

Once logged in, you need to initialize Supabase within your project directory. This will create the necessary configuration files (like supabase/config.toml).

Run the following command in the root directory of your Next.js project:

supabase init

This command sets up the configuration for your Supabase instance and prepares it for local development.

To link your local project with a remote Supabase project, use the following command:

supabase link

This will connect your local project to an existing Supabase project by asking you to select the project from a list. You will be prompted with a list of projects, and you can select the one you want by pressing Enter.

You will also be prompted to enter your database password. Note that the password will not be visible as you type it. Enter your password and press Enter.

Once linked, the supabase/config.toml file will be created in your project directory with the necessary connection information.

7. Run the Migrations

With your migrations and the Supabase project linked, it's time to push the migrations to the remote database.

Run the following command to push your migrations:

supabase db push

If everything is set up correctly, the CLI will check for migration files in your supabase/migrations directory and attempt to apply them to the remote database.

Example Output:

bashCopyWARN: no seed files matched pattern: supabase/seed.sql
Connecting to remote database...
Skipping migration commands.txt... (file name must match pattern "<timestamp>_name.sql")
Do you want to push these migrations to the remote database?
 • 20240208_create_users.sql
 • 20240209_create_llm_providers.sql

[Y/n] y
Applying migration 20240208_create_users.sql...
Applying migration 20240209_create_llm_providers.sql...
Finished supabase db push.

  • Warning Message: The warning about "no seed files matched" can be ignored unless you're using a supabase/seed.sql file to populate initial data.

  • Confirming Migrations: The CLI will show you the migrations that are about to be applied. You'll be asked for confirmation before proceeding.

8. Order of Migrations

It is essential to ensure the correct order of migrations, especially when tables reference each other. For example, if your users table references the llm_providers table, the users migration should be applied after the llm_providers migration.

In the example provided, make sure the 20240208_create_users.sql migration runs before 20240209_create_llm_providers.sql, because the users table relies on the llm_providers table.

The Supabase CLI handles this automatically as long as your migrations are named and timestamped properly.

Best Practices for Supabase Migrations

  • Version Control: Always commit your migration files to version control (e.g., Git). This way, your migrations stay synchronized with the application code.

  • Atomic Migrations: Each migration file should contain only one logical change to the database schema. This helps maintain clarity and allows for easy rollback if necessary.

  • Test Locally First: Before pushing migrations to the remote database, always test them in your local development environment to ensure they work as expected.

  • Keep Migrations Incremental: Avoid large, monolithic migrations that make it difficult to track changes. Instead, make incremental changes over time.

  • Backup Database: Before running complex migrations on production, ensure you have a backup of your database to prevent accidental data loss.

Conclusion

With Supabase’s powerful database management tools and the Supabase CLI, managing migrations in your Next.js project becomes a straightforward task. By following the steps outlined in this guide, you can efficiently set up and apply database migrations to keep your schema versioned and in sync with your application. Migrations are critical to maintaining your database structure, and Supabase makes the process easy and accessible, especially when combined with the Supabase CLI.

supabasebackendWeb DevelopmentFrontend Developmentsoftware developmentDatabasesMySQLPostgreSQL