Easily Manage PostgreSQL Database Migrations With DBeaver And Redgate Flyway

We must update the database configuration details in a “conf” file inside the “conf” folder.

flyway.conf

flyway.url=jdbc:postgresql://localhost:5432/Sarathlal
flyway.user=postgres
flyway.password=Sarath
flyway.locations=filesystem:D:\flyway-7.12.0\sql

We can create a simple database script inside the “sql” folder to create new customer table.

V1.1__customer-table.sql

CREATE TABLE public.Customer (
    id serial NOT NULL,
    name character varying(100) NOT NULL,
    address character varying(100) NULL,
    city character varying(100) NULL,
    country character varying(100) NULL,
    CONSTRAINT pk_customer_id PRIMARY KEY (id)
);

It is very important to keep the naming convention of the script. Name must start with letter “V” and followed by a major version and minor version and with double underscore. If you do not follow the correct name, Flyway will throw an error.

We can execute the Flyway commands now.

Please open Command Prompt and locate to the Flyway extracted folder.

Execute below info command with configuration file, which was created earlier by us.

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf info

We must first execute with baseline command to ensure that database baseline is created properly.

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf baseline

If you check the database, you can see that a new table “flyway_schema_history” created now.

This table is used to control the migration history.

We can use migrate command to complete the migration.

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf migrate

We can see that one more record is inserted inside the migration history table.

Also notice that, “customer” table is created now.

We can add one more script to modify customer table with a new column.

You must follow the version number in ascending order. Previous version was V1.1. So next version should be V1.2. You can give any other next number also. If you give next version as V1.3, then another version should be one greater than current version. You can’t give V1.2 after V1.3. Flyway will ignore it. I will show you that.

I am creating new script as V1.3__customer-table-alter.sql

V1.3__customer-table-alter.sql

alter table customer add column if not exists isactive bool;

We can execute migrate command.

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf migrate

We can see the current database status using info command.

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf info

As we discussed, I am giving next script version as V1.2. We can see that what happens with Flyway command.

V1.2__customer-data-insert.sql

insert into public.Customer (name,address,city,country,isactive)
values ('Sarath','Kakkanad','Kochi','India',true);

We can see the current database status using info command.

You may notice that current version 1.2 is ignored by Flyway.

Hence, we can change the version to 1.4 for same file (V1.4__customer-data-insert.sql).

We can again execute migrate command and see that new script is executed successfully.

Please note that, you can’t edit the previous script after executed migrate command. Flyway is keeping checksum for each script statement. If you try to modify existing script, it will show error. We can try to modify last script.

V1.4__customer-data-insert.sql

insert into public.Customer (name,address,city,country,isactive)
values ('Sarath','Kakkanad','Kochi','India',true);
insert into public.Customer (name,address,city,country,isactive)
values ('Aradhya','Kakkanad','Kochi','India',true);

You can’t run any further migration with this error. To overcome this situation, you can revert the code to original or use repair command.

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf repair

Though, the repair command will resolve the migration issue, it will not execute the new modified statement. It retains the same old SQL statement and database changes. Flyway will also support CI/CD pipeline to automate the deployments.

Conclusion

In this post, we have seen the details of installing PostgreSQL latest version along with DBeaver community version. After that we have downloaded the Flyway command line tool. Using Flyway tool, we have published database objects from database scripts. We have seen the usage of various Flyway command like “baseline”, “info”, “migrate” and “repair”. I have covered very basic things but hopefully it will be useful to administer PostgreSQL.