Try Flyway DB version control with Docker PostgreSQL in 3 steps

Flyway is SQL-based database version control

Step 1: Spin up Docker PostgreSQL

Start up with basic docker-compose.yml to spin up Postgres

my-postgres:
  image: postgres
  ports:
    5555:5432
  environment:
    POSTGRES_USER: example
    POSTGRES_PASSWORD: example

I map 5432 to 5555 since I already have a db running at 5432 .

docker-compose up

Step 2: Flyway config

Flyway need to know where is the database and user to login so you going to add url , user and pass to it.

Create flyway.conf

flyway.url=jdbc:postgresql://localhost:5555/example
flyway.user=example
flyway.password=example
flyway.locations=filesystem:./

I also add migration script location to be this location.

Test running the flyway by

flyway info

should got message like this

Flyway Community Edition 7.7.0 by Redgate
Database: jdbc:postgresql://localhost:5555/example (PostgreSQL 13.2)
...

Step 3: Flyway first migration

Then you create a ‘migration’ file in sql

The convention is VXXX__your-file-name.sql where XXX is flexible

checkout their docs for this.

For our case, I just create

-- filename: V1__create-accounts.sql
CREATE TABLE accounts (
        user_id serial PRIMARY KEY,
        username VARCHAR ( 50 ) UNIQUE NOT NULL,
        password VARCHAR ( 50 ) NOT NULL,
        email VARCHAR ( 255 ) UNIQUE NOT NULL,
        created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP 
);

Then I run it with

flyway migrate

Here we go we have the migrated table and the flyway_schema_history to track the database migration version

example=# \dt
 public | accounts              | table | example
 public | flyway_schema_history | table | example

Unfortunately …

when I run flyway undo

ERROR: Flyway Teams Edition upgrade required: undo is not supported by Flyway Community Edition.

I think this is basic operation I need.

So I finish my trial here and will checkout sqitch if they have this options.

Hope this help!