Database Migrations in Node.js with db-migrate

Database migrations are an essential part of managing and evolving your database schema over time. In Node.js, there are several tools available to facilitate this process, and one popular choice is db-migrate. In this article, we'll explore how to use db-migrate to handle your database migrations in a Node.js application.

What is db-migrate?

db-migrate is a database migration framework for Node.js that allows developers to define and manage database schema changes in a structured and version-controlled way. It works with a variety of database systems, including PostgreSQL, MySQL, SQLite, and others.

Getting Started

Before we dive into using db-migrate, you need to ensure that you have Node.js and npm (Node Package Manager) installed on your system. You can install db-migrate globally by running the following command:

npm install -g db-migrate

Setting up a New Migration

To create a new migration, you can use the db-migrate create command. Here's the basic syntax:

db-migrate create migration_name

For example, if you want to create a migration to add a new table called "users," you can run:

db-migrate create add-users-table

This will generate two files in your project's migration directory: an "up" script and a "down" script. The "up" script defines the changes to be applied to the database, while the "down" script defines how to revert those changes if needed.

Writing the Migration

Open the "up" script generated by db-migrate in your preferred code editor. Here, you can use JavaScript to define the changes you want to make to your database schema. For example, to create a "users" table, you can use the following code:

exports.up = function (db, callback) {
  db.createTable('users', {
    id: { type: 'int', primaryKey: true, autoIncrement: true },
    username: 'string',
    email: 'string',
    created_at: 'datetime',
    updated_at: 'datetime',
  }, callback);
};

The code above defines a table with columns for id, username, email, created_at, and updated_at. You can customize this structure to match your application's needs.

Running Migrations

To apply the migration and update your database, you can use the following command:

db-migrate up

This command will execute all pending migrations. If you wish to revert a migration, you can use the down command:

db-migrate down

Configuring Your Database

Before running migrations, you need to configure your database connection. This is done through a configuration file typically named database.json. You can create this file in your project's root directory. Here's a basic example for a PostgreSQL database:

{
  "dev": {
    "driver": "pg",
    "user": "your_username",
    "password": "your_password",
    "host": "localhost",
    "database": "your_database"
  }
}

Make sure to replace "your_username", "your_password", and "your_database" with your database credentials.

Conclusion

Using db-migrate in your Node.js project is a great way to manage and version your database schema changes. It ensures that your database evolves in a predictable and controlled manner. By creating and running migrations, you can easily keep your database schema in sync with your application's needs.

In this article, we covered the basics of using db-migrate, including setting up a new migration, writing migration scripts, running migrations, and configuring your database connection. As your application grows and evolves, you can continue to create new migrations to adapt your database schema accordingly.