Skip to main content

Flyway - Database Migrations made easy & How not to accidentally Roleback all of your migrations

Flyway - by boxfuse: Is a schema migration tool and it acts more of like a version control for your relational databases.

If you are manually executing your sql scripts or if your administrator is manually executing the sql scripts, on your production or UAT environment, you definitely need this tool to be setup in all of your environments.

Before we proceed:

Statutory Warning: 

Never ever execute the following command, be it your production or UAT environment:

$ flyway clean   # Do not execute this, ever!!!!

Wondering what it does? It roles back whatever table migrations/changes you have done through flyway, along with their data. 

In short, Don't ever execute this command.

Now that we are done with all the warnings:


It is fairly straight forward:

Run the above command in a shell prompt.
Running the above creates a directory called as flyway-x.x.x/
Inside this directory are many other directories of which, the two most import directories are:
  •  conf/ - Configuration for each of the databases are kept here as individual conf files
  •  sql/ - sql migrations are kept under different directories for each of the above configurations

Setting up the Configuration file:

If this is your first time with flyway, I would urge you to go through the configuration file from top to bottom, it's kinda fun, comical and scary too. Especially, this part -  quote and quote from the default configuration:

# Whether to disabled clean. (default: false)
# This is especially useful for production environments where running clean can be quite a career limiting move.

It's all fun until one day you accidentally do a clean.
Again, make sure that this option flyway.cleanDisabled is set to true, at all costs.

First Things First - User creation in the Database:

Make sure you have two users created in your database.

1) A normal user which should be used at all times - doesn't have delete or drop privileges:

E.g.: In mysql:

2) And a deleteOnlyUser which should be used only during repair operations and delete/drop operations in a database. The reason why we have such alternate user is to have a much more clear access control over the database.

E.g.: In mysql

SQL Setup:

Place all the sql files in their individual directories corresponding to each of the databases under the sql directory inside flyway-x.x.x.

Each of the sql files should be named with a flyway friendly convention, as:


Make sure that the V in the filename is an uppercase.

Configuration Setup:

Delete the default configuration file under conf and substitute it with something like the following. Once again, there will be two configurations one for default user and another for the deleteOnlyUser as:

1) DefaultUser Configuration:

2) DeleteUser Configuration:

All Set for migration:

Now, there are some basic commands in flyway for migration, repair and displaying the information.

$ flyway -configFiles='flyway-x.x.x/conf/$file_name.conf' info

Displays the schema versions and baseline related information from the mysql's schema_version table.


$ flyway -configFiles='flyway-x.x.x/conf/$file_name.conf' migrate

Migrate command scans the filesystem for available migrations. It also compares these with the completed migrations. It is the center piece, aiding in the migration of the sql files.


$ flyway -configFiles='flyway-x.x.x/conf/$file_name.conf' repair

When there's a failed migration, upon correction; the checksums need to be Realigned of the applied migrations with the ones of the available migrations.


$ flyway -configFiles='flyway-x.x.x/conf/$file_name.conf' repair

Don't even think about it. If you are still wondering, it rolls back all of your migrations. Not suitable for Production/UAT/Preprod or anywhere else.

BONUS: Migrating to a different version of flyway or Starting afresh with a new set of SQL scripts:

Let's say, our database grows in size and there comes a scenario where the old migrations need to be archived. In that case, the following maintenance needs to be done.


In Mysql:

mysql> drop table flyway_schema_history;
mysql> drop table schema_version;

Alter your configuration file to locate to the recent sql files and set the baseline to a different version number.



$ flyway -configFiles='flyway-x.x.x/conf/$file_name.conf' baseline

This baselines the database with the mentioned version. This will cause migrate to ignore all migrations upto and including that particular version.

That wraps up our discussion and flyway.

And remember kids; Always set your flyway.cleanDisabled as True.

# Whether to disabled clean. (default: false)
# This is especially useful for production environments where running clean can be quite a career limiting move.

Happy safe Wrangling!!!


Popular posts from this blog

ES Index - S3 Snapshot & Restoration:

The question is.. What brings you here? Fed up with all the searches on how to back-up and restore specific indices? 

Fear not, for your search quest ends here.!

After going through a dozens of tiny gists and manual pages, here it is.. We've done all the heavy-lifting for you.

The following tutorial was tested on elasticsearch V5.4.0

And before we proceed, remember:


Make sure that the elasticsearch version of the backed-up cluster/node <= Restoring Cluster's version.


Unless it's highly necessary;

curl -XDELETE 'http://localhost:9200/nameOfTheIndex

      - deletes a specific index

Especially not, when you are drunk!:

curl -XDELETE 'http://localhost:9200/_all

      - deletes all indexes (This is where the drunk part comes in..!!)

Step1:Install S3 plugin Support:        sudo bin/elasticsearch-plugin install repository-s3
sudo /usr/share/elasticsearch/bin/elasticsearch-plugin install repository-s3

Depends on w…

The No-BS guide to AutoComplete and FuzzySearch in Elasticsearch

Before we begin.. Here are a few basics.Analyzer: An analyzer does the analysis or splits the indexed phrase/word into tokens/terms upon which the search is performed with much ease.

An analyzer is made up of tokenizer and filters.

There are numerous analyzers in elasticsearch, by default;
here, we use some of the custom analyzers tweaked in order to meet our requirements.
Filter: A filter removes/filters keywords from the query. Useful when we need to remove false positives from the search results based on the inputs.

We will be using a stop word filter to remove the specified keywords in the search configuration from the query text.
Tokenizer: The input string needs to be split, in order  to be searched against the indexed documents. We are about to use ngram here, which splits the query text into sizeable terms.
Mappings: The created analyzer need to be mapped to a fieldname, for it to be efficiently used while querying.
T'is time!!! Now that we have covered the basics, t'is t…

ELK Stack... Not!!! FEK, it is.!!! Fluentd, Elasticsearch & Kibana

If you are here, you probably know what elasticsearch is and at some point, trying to get into the mix. You were searching for the keywords "logging and elasticsearch" or perhaps, "ELK"; and probably ended up here. Well, you might have to take the following section with a pinch of salt, especially the "ELK Stack"  fam.
At least from my experience, working for start-ups teaches oneself, a lot of lessons and one of the vast challenges include minimizing the resource utilization bottlenecks. On one hand, the logging and real-time application tracking is mandatory; while on the the other hand, there's a bottle neck in the allocated system resource, which is probably an amazon EC2 instance with 4Gigs of RAM.
ELK Stack 101: Diving in, ELK => Elasticsearch, Logstash and Kibana. Hmm, That doesn't add up; don't you think? Elasticsearch stores the reformed log inputs, Logstash chops up the textual logs and transforms them to facilitate query, deriva…