Flyway – database migration tool

How to setup migration tool for any IDE

There are many ways how to setup migration tool for a specific project, here I will use a command line approach.

As you already know by now, flyway just runs your SQL files with migration DDL commands.  You create new file for each change, incrementally as your database evolve.

We need a folder where we will put files under the project tree.

File names should follow special naming convention for versioned migrations.

Command “fw”

Because I use bash (cygwin) environment inside Netbeans IDE, i will first enable “flyway” tool as a command inside cygwin bash terminal. I create “fw” command file and put it in the “bin” folder which is on the PATH.

#!/bin/sh
/cygdrive/c/Programs/flyway-4.0.3/flyway $@

Netbeans project structure

I have simple hello world java web project. I added a resources, db and migrations folders for data migration files:

├── resources
│   └── db
│       └── migrations
│           ├── V201611222253__Create_book_table.sql
│           └── V201611222338__Add_field_book_table.sql

Configuraton

In root folder, where I will run “fw” command, I added configuration file with the name flyway.conf. This file contain important config settings for flyway to work.

flyway.url=jdbc:postgresql://localhost:5432/bsg_taskmgr_db
flyway.user=postgres
flyway.password=postgres
flyway.schemas=public
flyway.locations=filesystem:./resources/db/migrations

There are many possible settings. You can take prepared flyway.conf file as a base for your configuration file.  Look at conf folder under flyway installation folder, there is sample conf file with comments around every possible configuration key.

Final structure

In simplified view (I omit lot of files here) the structure of a project folder looks like :

.
├── build.xml
├── flyway.conf
├── library.xml
├── build
├── dist
├── nbproject
├── resources
│   └── db
│       └── migrations
│           ├── V201611222253__Create_book_table.sql
│           └── V201611222338__Add_field_book_table.sql
├── src
│   ├── conf
│   │   └── MANIFEST.MF
│   └── java
│       ├── com
│       │   └── bisaga
│       │       └── demo
│       │           ├── generated
│       │           ├── HelloResource.java
│       │           └── HelloService.java
│       └── org
│           └── netbeans
│               └── rest
│                   └── application
│                       └── config
│                           └── ApplicationConfig.java
├── test
└── web
    ├── calc.js
    ├── index.html
    ├── META-INF
    │   └── context.xml
    └── WEB-INF
        ├── beans.xml
        └── web.xml

Migrations SQL files

This two files are simple migration files:

create table book (
    id serial not null,
    title text not null,

    constraint book_pk primary key (id)
);

and second file where we add new field to the table :

alter table book add column author text not null;

Running migrations

There are only few commands. For start, you need only two (info and  migrate). After you write your first migration file, just try out “info” command. You should see pending migration record for your SQL file.

$ fw info
$ fw info

Database: jdbc:postgresql://localhost:5432/bsg_taskmgr_db (PostgreSQL 9.4)

+--------------+-------------------+---------------------+---------+
| Version      | Description       | Installed on        | State   |
+--------------+-------------------+---------------------+---------+
| 201611222253 | Create book table |                     | Pending |
+--------------+-------------------+---------------------+---------+

Now you can migrate first file:

$ fw migrate
$ fw migrate
$ Flyway 4.0.3 by Boxfuse

Database: jdbc:postgresql://localhost:5432/bsg_taskmgr_db (PostgreSQL 9.4)
Successfully validated 1 migration (execution time 00:00.012s)
Creating Metadata table: "public"."schema_version"
Current version of schema "public": << Empty Schema >>
Migrating schema "public" to version 201611222253 - Create book table
Successfully applied 1 migration to schema "public" (execution time 00:01.265s).

After adding next SQL file in “migrations” folder you can again try status of current migrations with “info” comand:

$ fw info
$ Flyway 4.0.3 by Boxfuse

Database: jdbc:postgresql://localhost:5432/bsg_taskmgr_db (PostgreSQL 9.4)

+--------------+----------------------+---------------------+---------+
| Version      | Description          | Installed on        | State   |
+--------------+----------------------+---------------------+---------+
| 201611222253 | Create book table    | 2016-11-22 23:36:10 | Success |
| 201611222338 | Add field book table |                     | Pending |
+--------------+----------------------+---------------------+---------+

You see there is a Pending change from your last migration file. After another “migrate” command, again this migrations will all be marked as “Installed”.

Ok, that’s it ! Migrations are now in full working state !

How to change “current” folder to “project” folder

For successful execution of any flyway command we need to be in proper folder first. The proper folder is folder where flyway.conf file reside. This is project root folder in my example.

Don’t forget to install “TerminalExtras” plugin for Netbeans, then you can just press Alt+. when you staying on the project root node. Change directory (cd) command will be executed inside terminal window and focus will be set to terminal window.

2016-11-23-00_56_35-helloworld-netbeans-ide-8-2

 

 

Bash script

How to write bash script

I am using cygwin on windows and I wish to use bash as my primary scripting tool.

Write script file with the special first line which define interpreter. Put file in a folder accessible  from anywhere (folder must be on the PATH). Change file characteristics to be executable (chmod +x).

#!/bin/sh
/cygdrive/c/Program\ Files\ \(x86\)/Notepad++/notepad++.exe $@ &
$ chmod +x note

This script will call notepad++ editor and open file from passed parameters (test.txt).

$ note test.txt

Unix (LF) – proper line endings

Well, don’t forget (like I was), that cygwin is linux on the windows ! It means , your script files need to be in proper format, as on linux.  If you use console editors as for example “nano”,  it will work automatically, but with notepad++ (windows app), files will be created with windows line endings by default.

2016-11-24-21_30_20-h__home_demo-notepadYou need to change line endings with right click menu … to Unix(LF) , or on the menu /Edit/EOL Conversion/.

2016-11-24-22_16_49-settings Example

#!/bin/sh
myvar1=Hello
myvar2=World
echo $myvar1 $myvar2

2016-11-24-22_25_59-settingsIf you ran demo script and you didn’t get expected “Hello World” but only ” World” maybe, well check your line ending definition.

Want to know more ? Look at this nice little beginners bash tutorial or special hacker web site.