How to Create Supabase Database Migration Files in Vue.js

Combining Supabase with Vue.js allows you to build robust, scalable apps with real-time support. During the project's early development phase, migration files automate the process of resetting and rebuilding database tables with just a few commands, saving developers a substantial amount of time.

In this guide, we'll look at how to create migration files for a remote Supabase database so that the database can be updated directly from our Vue.js project.

What is Supabase?

Supabase is an open-source alternative to Firebase, providing all the backend services you need to build a web or mobile application. With Supabase, you can set up a PostgreSQL database, manage authentication, and create real-time APIs quickly and efficiently.

If you prefer learning by watching video tutorials, checkout the Vue.js Master Class 2024 Edition course on Vue School. In this course, we use Supabase extensively as a backend solution to our project management app that’s we’re building throughout the course.

Why Use Supabase with Vue.js

Integrating Supabase with Vue.js allows you to leverage these powerful features in your frontend applications. Let’s have a look at some of those features:

1. Seamless Integration

Supabase offers a JavaScript library that easily integrate with Vue.js, enabling you to manage your backend directly from your frontend code. This smooth integration simplifies the development process, reducing the amount of boilerplate code and allowing you to focus on building features.

2. Real-Time Capabilities

With Supabase's real-time capabilities, you can build dynamic and responsive Vue.js applications that reflect live changes without needing additional infrastructure. This is particularly useful for applications that require instant updates, such as chat applications, live dashboards, and collaborative tools.

3. Authentication and Authorization

Supabase provides comprehensive authentication solutions, making it easy to manage user sessions and permissions in your Vue.js applications. With built-in support for social logins and JWT-based authentication, you can implement secure and scalable user management with minimal effort.

4. Scalable Backend

Built on PostgreSQL, Supabase ensures that your database can scale with your application, handling large amounts of data and high traffic.

5. Developer-Friendly

Both Supabase and Vue.js are designed to be developer-friendly, with extensive documentation, community support, and straightforward APIs. This reduces the learning curve and speeds up development, allowing you to deliver high-quality applications faster.

6. Cost-Effective

Supabase offers a generous free tier, making it a cost-effective solution for startups and small projects. As your application grows, you can scale your usage and only pay for what you need, ensuring cost efficiency without compromising on performance.

By leveraging the powerful combination of Supabase and Vue.js, you can build modern web applications that are not only feature-rich but also easy to maintain and scale.

Speaking of features, one of the best features in Supabase is the ability to create migration files, which leads us to the next section.

Understanding Database Migration Files

Database migrations are scripts that help you manage and version-control changes to your database schema. They are crucial for keeping your database structure aligned with your application's needs and for collaborating with your development team.

Importance of Migration Files

Database schema migration files are crucial for keeping your database structure updated and in sync with your application's needs. Acting like version control, they make it easy to track, undo, and collaborate on changes. Some of the benefits of creating migration files are:

1. Version Control: Just like Git tracks changes to your codebase, migration files track changes to your database schema. This allows you to roll back changes if something goes wrong.
2. Collaboration: When working in a team, migration files ensure that everyone is working with the same database structure, reducing conflicts and errors.
3. Consistency: Migrations help maintain consistency between different environments (development, staging, production) by applying the same schema changes across all environments.
4. Automation: Automated migrations streamline the deployment process, ensuring that schema changes are applied automatically when deploying new versions of your application.

Creating and Applying Supabase Databa Migrations in Vue.js Apps

Creating migration files involves writing SQL scripts that define changes to your database schema, such as adding or modifying tables, columns, and indexes. Applying migrations involves running these scripts to update the database schema. Tools like the Supabase CLI make it easy to manage migrations within your development workflow.

In this guide, we will focus on using migration files to resetting and rebuilding the remote Supabase database during the initial development phase.

Prerequisites

Before we dive into creating Supabase database migration files in Vue.js, ensure you have the following set up:

1. Node.js and npm: Ensure you have Node.js and npm installed on your machine. You can download and install them from Node.js.
2. A Vue.js 3 Application: Make sure you have a Vue.js 3 project initialized. If not, you can create one using create-vue:

npm create vue@latest

3. Supabase Project: You need a Supabase project. If you haven't created one yet, sign up at Supabase and create a new project. Note down the project reference ID, as we will need it later.

Setting Up Supabase CLI

To get started, we need to install the Supabase CLI in our project as a dev dependency. Follow these steps:

1. Visit the Supabase CLI documentation.
2. Copy the npm installation command:

npm install supabase --save-dev

3. Open your terminal in VS Code, make sure you’re inside the project root directory, paste the command, and hit enter.

Initializing Supabase in Your Project

Next, we need to set up Supabase in our project. This involves initializing Supabase configurations.

1. Open your package.json file and add the following script:

{
  "scripts": {
    "supabase:init": "supabase init"
  }
}

2. Run the script in your terminal:

npm run supabase:init

This command generates a new supabase directory containing the necessary configuration files.

Logging into Supabase

Before proceeding, we need to log into Supabase.

1. Add the login script to your package.json:

{
  "scripts": {
    "supabase:login": "supabase login"
  }
}

2. Run the login command in your terminal:

npm run supabase:login

3. Follow the prompts to complete the login process.

Linking Supabase Configuration with a Remote Project

To link our local Supabase configuration with the remote Supabase project, follow these steps:

1. Add the link script to your package.json:

{
  "scripts": {
    "supabase:link": "supabase link --project-ref YOUR_PROJECT_REF"
  }
}

2. Replace YOUR_PROJECT_REF with the project reference ID from your Supabase dashboard.
3. Run the link command in your terminal:

npm run supabase:link

Linking your local configuration with the remote project ensures that any database changes you make locally are mirrored on your live database.

Creating a New Migration File

With the setup complete, we can now create a new migration file.

1. Add the migration script to your package.json:

{
  "scripts": {
    "db:migrate:new": "supabase migration new"
  }
}

2. Run the migration command in your terminal:

npm run db:migrate:new projects-schema

This command creates a new directory called migrations inside the supabase directory. It also generates a timestamped file named projects-schema, where we will define our database schema.

Defining Your Database Schema

Let's define the schema for the projects table in the generated migration file. Here’s the SQL code:

DROP table if exists projects;

create table
  projects (
    id bigint primary key generated always as identity not null,
    created_at timestamptz default now() not null,
    name text not null,
    slug text unique not null,
    collaborators text array default array[]::varchar[] not null
  );

This SQL script will check to see if the database has a projects table already and drop it if exists. Then, it will create a projects table with columns such as id, created_at, name, slug, and collaborators.

Warning: Dropping tables in production is dangerous. You need to be careful not to run any migration file that drops important tables in production.

Resetting the Database

To apply our migrations and reset the database, we need to add and run a reset script.

1. Add the reset script to your package.json:

{
  "scripts": {
    "db:reset": "supabase db reset --linked"
  }
}

2. Run the reset command in your terminal:

npm run db:reset

This command resets the linked project's database according to the schema defined in the migration file. It drops existing tables and types, then recreates them based on the migration scripts.

Populating the Database with Seeders

After resetting the database, it's often useful to populate it with initial data. This process is called seeding, and it helps to set up a consistent initial state for your application.

Creating Seeder Scripts

Seeder scripts are used to insert predefined data into your database. This can include default users, configuration settings, or sample data for development and testing. By automating this process, you ensure that every environment (development, staging, production) starts with the same baseline data.

Example Seeder Script

You can write the SQL scripts for seeding the Supabase database in the supabase/seed.js file that was created when we ran the Supabase’s initialization script. Here’s an example of how you might write a seeder script for your projects table:

INSERT INTO projects (name, slug, collaborators)
VALUES
  ('Project Alpha', 'project-alpha', '[]'),
  ('Project Beta', 'project-beta', '["1", "2"]');

This script inserts two sample projects into the projects table. You can create similar scripts for other tables in your database.

In our Vue.js Master Class 2024 Edition course, while developing our project management app, we opted for a unique approach. We combined faker.js and the Supabase client to populate our remote database. This approach may not be conventional, but it resonates well with frontend developers. Explore the Seeding the Supabase Remote Database with Faker.js lesson for a firsthand look at this implementation.

Running Seeder Scripts

Each time you run supabase start or supabase db reset, the supabase/seed.sql file is executed. Seeding occurs once all database migrations have completed. As a general rule, you should only include data in your seed file rather than schema declarations.

This ensures that your database is always populated with the necessary data for your application to function correctly.

Best Practices for Database Migrations

While creating Supabase database migration files, there are few things to keep in mind to ensure a smooth workflow:

1. Keep Migrations Small and Focused: Each migration should focus on a single change or a small set of related changes. This makes it easier to understand and manage the changes. Unless you’re using the migration files for resetting the entire database, then you’re free to create a migration file that starts by dropping the table then re-create it.
2. Test Migrations Thoroughly: Always test your migrations in a development environment before applying them to staging or production. This helps catch any issues early and ensures that the migrations work as expected.
3. Use Descriptive Names: Name your migration files descriptively to indicate the changes they contain. For example, modify-tasks-table or projects-schema .
4. Document Your Migrations: Maintain documentation for each migration, explaining what changes were made and why. This helps other team members understand the purpose of each migration.

Conclusion

In this guide, we've shown you how to create Supabase database migration files in your Vue.js projects. Using these migration files helps keep your database schema up-to-date and in sync with your application, saving you time and effort. They also allow you to easily reset the remote Supabase database during development.

For more in-depth learning about Supabase migrations, seeds, authentication, and more, check out the Vue.js 3 Master Class at Vue School. This course provides detailed tutorials and insights to help you master Supabase and Vue.js.

By following this guide and exploring additional resources, you'll be well-prepared to take full advantage of Supabase and Vue.js, creating applications that are both efficient and effective.