How To Guides

Streamline Your Remote Database Migrations with Flyway

by

When managing database schemas, ensuring smooth migrations can be a daunting task, especially when dealing with remote databases. As a software engineer, I’ve found Flyway to be an invaluable tool in automating and managing these migrations. In this blog post, I’ll guide you through the process of using Flyway to run migrations on a remote database, specifically focusing on a macOS setup.

Thank me by sharing on Twitter 🙏

Why Flyway?

Before diving into the how-to, let’s discuss why Flyway is an excellent choice for database migrations:

  1. Version Control: Flyway uses a version control mechanism for your database, similar to Git for your code.
  2. Automation: It automates the process of applying and rolling back migrations.
  3. Flexibility: Flyway supports numerous databases and can be integrated into CI/CD pipelines.

Prerequisites

Before we start, ensure you have the following:

  • Flyway installed on your macOS system. You can download it from the official Flyway website.
  • A remote database set up and accessible.
  • Basic knowledge of SQL and database schemas.

Setting Up Flyway

Installing Flyway

First, download and install Flyway. After downloading, extract the files and add the Flyway executable to your system’s PATH. This allows you to run Flyway commands from any directory in your terminal.

Configuring Flyway

Next, we need to configure Flyway to connect to our remote database and locate our migration scripts. Let’s create a configuration file.

  1. Create the Configuration File: Navigate to your project directory and create a folder named infrastructure. Inside this folder, create another folder named db. Finally, create a file named flyway.conf within the db folder. Your directory structure should look like this:
Plaintext
   infrastructure/
   └── db/
       └── flyway.conf
  1. Configure the Database Connection: Open flyway.conf and add the following configurations, replacing the placeholders with your actual database details:
Plaintext
   flyway.url=jdbc:<your-database-type>://<your-remote-db-host>:<port>/<database-name>
   flyway.user=<your-db-username>
   flyway.password=<your-db-password>
   flyway.locations=filesystem:infrastructure/db/migrations

Here’s a practical example if you’re using a PostgreSQL database:

Plaintext
   flyway.url=jdbc:postgresql://db.example.com:5432/mydatabase
   flyway.user=myuser
   flyway.password=mypassword
   flyway.locations=filesystem:infrastructure/db/migrations

Organizing Migration Scripts

Creating Migration Scripts

Flyway uses a specific naming convention for migration scripts, typically V1__Description.sql, V2__Description.sql, etc. Create a folder named migrations inside the db directory to store these scripts.

Your updated directory structure should now be:

Plaintext
infrastructure/
└── db/
    ├── flyway.conf
    └── migrations/
        ├── V1__Initial_migration.sql
        └── V2__Add_new_table.sql

Writing Migration Scripts

Inside the migrations folder, create your migration scripts. For example, your V1__Initial_migration.sql might look like this:

SQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

And V2__Add_new_table.sql might contain:

SQL
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL
);

Running Migrations

Executing Flyway Commands

With your configuration and migration scripts in place, you can now run Flyway to apply these migrations to your remote database.

Open your terminal, navigate to the Flyway installation directory, and execute the following command:

ShellScript
flyway migrate -configFiles=infrastructure/db/flyway.conf

This command tells Flyway to use the specified configuration file and apply the migrations found in the migrations folder to the remote database.

Verifying the Migration

To ensure that the migrations were applied successfully, you can log in to your remote database and check if the tables have been created:

ShellScript
psql -h <your-remote-db-host> -U <your-db-username> -d <your-database-name>

Then run:

Plaintext
\dt

This command lists all the tables in the current database, and you should see the users and roles tables.

Conclusion

Using Flyway to manage and automate your database migrations can significantly simplify the process, especially when dealing with remote databases. By following the steps outlined in this guide, you can set up Flyway on your macOS, configure it for your remote database, and organize your migration scripts efficiently. This setup not only saves time but also ensures consistency and reliability in your database schema changes.

Share this:

Related Posts:

FlywayPostgres

Leave a Reply