Getting Started with Flyway: Your First Database Migration using PostgreSQL

Managing database schema changes throughout an application’s lifecycle can be a significant challenge for developers. As applications evolve, so does the underlying database structure. Adding new columns, creating tables, or altering data types are common tasks. Performing these changes manually across different environments (development, testing, production) or within a team often leads to errors, inconsistencies, and confusion. Ensuring every database instance shares the exact same schema becomes complex. This is precisely where Flyway steps in, offering a streamlined and automated approach to database migrations.

This guide introduces Flyway, a powerful tool for managing database migrations, and walks you through creating your first migration using PostgreSQL as the example database.

What is Flyway?

Flyway is an open-source database migration tool that essentially provides version control for your database schema. It allows you to define database changes in separate SQL files, each assigned a version number. When executed, Flyway identifies which migration scripts haven’t been applied to the target database yet and runs them in the correct version order. Think of it like Git for your database schema, enabling you to track changes systematically over time.

Key Benefits of Flyway:

Simplicity: You primarily write standard SQL scripts for your changes (e.g., CREATE TABLE, ALTER TABLE). Flyway handles the execution logic. While code-based migrations are supported, SQL migrations are the most common approach.
Versioning: Every migration script is versioned (e.g., V1, V2, V3…). Flyway ensures scripts run sequentially based on their version and prevents the same script from running twice on the same database.
Consistency: Achieve consistent database schemas across all environments – developer laptops, staging servers, and production. Simply run Flyway migrations in each environment to align them.
Broad Database Support: Flyway works seamlessly with numerous databases, including PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, SQLite, and more. Skills learned here are transferable.
Integration: Flyway offers a command-line interface (CLI), plugins for build tools like Maven and Gradle, and native integration with frameworks like Spring Boot. This guide focuses on the CLI for foundational understanding.

In essence, Flyway facilitates managing database changes in a structured, automated, and reliable manner. It replaces manual tracking and guesswork with a clear, versioned history of schema evolution.

Why Use Database Migrations? A Practical Example

Let’s consider a simple scenario: building a To-Do List application. Initially, you need a tasks table to store task details like an id, title, and perhaps a completed status.

Without a migration tool, you might manually connect to your local database and execute a CREATE TABLE tasks ... statement. This works fine initially. However, as the project progresses, you might need to add a due_date column or create a separate categories table. You’d manually run ALTER TABLE or CREATE TABLE commands on your local database again.

Problems arise when collaborating or deploying:

Team Synchronization: Another developer joins the project. How do they get their database schema up-to-date with your latest changes? Manually sharing SQL scripts? Prone to errors and omissions.
Environment Consistency: When deploying to a staging or production server, you must remember to apply all the same SQL changes manually. Forgetting even one ALTER TABLE statement can cause application errors because the code expects a database structure that doesn’t exist on the server.

Flyway elegantly solves these issues. Each schema change is captured in a new, versioned migration file (e.g., V1__create_tasks_table.sql, V2__add_due_date_to_tasks.sql). These files are stored alongside your application code in version control (like Git).

When a developer pulls the latest code, they simply run flyway migrate. Flyway checks their local database, sees which migrations are missing, and applies them automatically.
During deployment, the same flyway migrate command ensures the target server’s database schema is brought up-to-date with all necessary changes in the correct order.

Flyway guarantees schema synchronization across developers and environments, making the process reliable and repeatable. Let’s put this into practice by creating the initial tasks table in PostgreSQL.

Getting Started: Installation and Setup

Before creating migrations, you need Flyway and a PostgreSQL database ready.

Install Flyway CLI: Download the Flyway Community Edition CLI from the official Flyway website (flywaydb.org). Versions are available for Windows, macOS, and Linux. Extract the downloaded archive to a preferred location (e.g., C:\tools\flyway or ~/flyway). The extracted folder contains subdirectories like conf, drivers, sql, and the main flyway executable (flyway.cmd on Windows). For ease of use, add the Flyway installation directory to your system’s PATH environment variable, or navigate to this directory in your terminal when running commands.
Prepare PostgreSQL Database: Ensure you have access to a PostgreSQL server. You can use a locally installed instance or run PostgreSQL easily using Docker. Here’s a Docker command to start a temporary PostgreSQL container:
```
docker run -d --name postgres-flyway -p 5432:5432 \
    -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_DB=flyway_demo \
    postgres:latest
```
This command starts a PostgreSQL container on port 5432, sets the default postgres user’s password to postgres, and automatically creates a database named flyway_demo. If you have PostgreSQL installed locally, you can create a new database manually (e.g., using createdb flyway_demo or a GUI tool). The goal is to have an empty database for Flyway to work with.
Configure Flyway Connection: Tell Flyway how to connect to your PostgreSQL database. Open the flyway.conf file located in the conf subdirectory of your Flyway installation using a text editor. Find and modify the following lines:
```
flyway.url=jdbc:postgresql://localhost:5432/flyway_demo
flyway.user=postgres
flyway.password=postgres
flyway.locations=filesystem:sql
```
Explanation:
- flyway.url: The JDBC connection URL for your database. Adjust the host (localhost), port (5432), and database name (flyway_demo) if your setup differs.
- flyway.user & flyway.password: Database login credentials. The example uses the default postgres user and the password set earlier (postgres). Change these to match your database credentials.
- flyway.locations: Specifies where Flyway should look for migration script files. filesystem:sql tells Flyway to look in the sql subdirectory within the Flyway installation folder. We’ll place our migration script there.
Save the changes to flyway.conf. Flyway is now configured to connect to your flyway_demo database.

Note on Security: Storing passwords directly in configuration files can be insecure, especially in shared environments. For production scenarios, consider using environment variables (FLYWAY_PASSWORD) or command-line parameters (-password=...) instead of hardcoding the password in the file. For this learning exercise, the configuration file approach is simpler.

With Flyway installed and configured, you’re ready to create your first migration.

Creating Your First Migration

Let’s create the first migration script to set up the tasks table for our To-Do List application.

Create the Migration File: Navigate to the sql subdirectory within your Flyway installation folder. Create a new file named according to Flyway’s naming convention: V<VERSION>__<DESCRIPTION>.sql. The convention uses:
- V: Prefix for versioned migrations.
- <VERSION>: A version number (e.g., 1, 1.1, 202310271000). Versions are sorted numerically.
- __: Double underscore separator.
- <DESCRIPTION>: A descriptive name for the migration (use underscores _ for spaces).
- .sql: File extension.
For our first migration, create a file named: V1__create_tasks_table.sql.
Write the SQL Script: Open V1__create_tasks_table.sql and add the SQL command to create the tasks table:
```
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT FALSE
);
```
This script creates a tasks table with the following columns:
- id: An auto-incrementing integer primary key (using PostgreSQL’s SERIAL type).
- title: A non-nullable string column for the task title.
- completed: A boolean column indicating task completion status, defaulting to FALSE.
Save the file. This is your first versioned migration script (V1).

Running the Migration

Now, let’s apply this change to the database using Flyway.

Execute the Migrate Command: Open your terminal or command prompt, navigate to the Flyway installation directory (where the flyway executable is), and run:
```
flyway migrate
```
Interpret the Output: Flyway will connect to the database, scan the sql folder for migrations, and apply any new ones. You should see output similar to this:
```
Flyway Community Edition 9.x.x by Redgate
Database: jdbc:postgresql://localhost:5432/flyway_demo (PostgreSQL 15)
Successfully validated 1 migration (execution time 00:00.012s)
Creating Schema History table "public"."flyway_schema_history" ...
Current version of schema "public": << Empty Schema >>
Migrating schema "public" to version 1 - Create tasks table
Successfully applied 1 migration to schema "public" (execution time 00:00.045s)
```
Key points from the output:
- Flyway successfully connected to the flyway_demo PostgreSQL database.
- It validated one migration script found (V1__create_tasks_table.sql).
- Since this is the first run on this database, Flyway created its internal tracking table: flyway_schema_history in the public schema. This table records which migrations have been applied.
- The database schema was initially empty.
- Flyway applied migration version 1 (“Create tasks table”).
- The process completed successfully.
If you see similar output without errors, congratulations! Your first database migration using Flyway was successful. The tasks table now exists in your database, and Flyway has recorded this fact.

Tip: If you run flyway migrate again immediately, Flyway will check the flyway_schema_history table, see that version 1 is already applied, and report that the database is up-to-date. This prevents accidental re-execution of migrations.

Verifying the Migration

It’s good practice to verify that the migration had the intended effect.

Using Flyway Info: Flyway provides a command to check the migration status:
```
flyway info
```
This command displays a table showing all discovered migrations, their status (Applied/Pending), and when they were applied. You should see an entry for Version 1 - Create tasks table with a status indicating it has been successfully applied (e.g., Success or Installed). It will also show the current schema version is 1.
Checking the Database Directly: You can connect to the flyway_demo database using a tool like psql (PostgreSQL’s command-line client) or a graphical tool (like DBeaver, PgAdmin) and inspect the schema.
- List Tables: Using psql:
```
psql -h localhost -U postgres -d flyway_demo -c "\dt"
```
  (You might be prompted for the password ‘postgres’). This should list both the tasks table and the flyway_schema_history table.
- Inspect Table Structure: Using psql:
```
psql -h localhost -U postgres -d flyway_demo -c "\d tasks"
```
  This will show the columns (id, title, completed) and their types, confirming the table was created correctly.
- Check Flyway History Table:
```
SELECT version, description, success FROM flyway_schema_history;
```
  Running this SQL query against flyway_demo should show a row for version 1, description “Create tasks table”, and a success status (true).

If these verification steps confirm the tasks table exists and Flyway’s history table reflects the applied migration, your first migration is complete and verified.

Conclusion

You have successfully executed your first database migration using Flyway and PostgreSQL. Starting with an empty database, you defined a schema change in a versioned SQL file and applied it reliably using a single flyway migrate command. This structured approach can scale as your project grows – simply create new migration files (V2, V3, etc.) for subsequent changes and let Flyway manage their application.

This foundation sets the stage for more advanced Flyway usage, such as adding further migrations, handling different migration types, and managing rollbacks or changes in more complex scenarios.

How Innovative Software Technology Can Help

At Innovative Software Technology, we understand that managing database schema evolution is crucial for robust application development and operational stability. Leveraging powerful tools like Flyway alongside databases such as PostgreSQL, MySQL, and SQL Server, we help businesses streamline their database migration processes. Our expertise ensures consistency across all development, testing, and production environments, significantly minimizing deployment risks and preventing costly errors. We implement automated database version control workflows, allowing your development teams to focus on building core features with confidence, knowing the underlying database foundation is solid, reliable, and scalable. Partner with Innovative Software Technology for expert database management solutions and CI/CD integration that accelerate your delivery cycles and support your business growth through dependable software infrastructure.