Streamline Your Remote Database Migrations with Flyway

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.

Genesis: Artificial Intelligence, Hope, and the Human Spirit

(135)

$21.00 (as of December 21, 2024 19:39 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

COLORCORAL Cleaning Gel Universal Dust Cleaner for PC Keyboard Car Detailing Office Electronics Laptop Dusting Kit Computer Dust Remover, Stocking Stuffers for Men Women Kids Teens 160g

(55687)

$6.98 (as of December 21, 2024 08:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Start with Why: How Great Leaders Inspire Everyone to Take Action

(38118)

$10.49 (as of December 21, 2024 19:39 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

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:

   infrastructure/
   └── db/
       └── flyway.conf

2. Configure the Database Connection: Open flyway.conf and add the following configurations, replacing the placeholders with your actual database details:

   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:

   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:

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:

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:

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:

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:

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

Then run:

\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.

Related Posts:

The Difference Between Schemas and Databases and…

Simplifying Database Migrations with Docker, Flyway,…

Choosing the Best TypeScript ORM for Your Project