Flyway is a widely used version control tool used for cross-database version control too. It supports more than twenty databases and, by far, one of the easiest and most flexible tools to manage database migrations. Whether you are just starting as a database administrator or even an expert data professional, it is advantageous to know more about Flyway and add it to your database administration arsenal.
Flyway was first released in 2010, developed by Axel Fontaine, and was under Apache 2.0 license. Axel was looking for a tool that lets you integrate the application and DB changes easily using plain SQL. As he did not find any such tool out there, he thought of developing one of that kind. Flyway had a huge acceptance in the developer community, which further contributed to its fast evaluation.
Database migration
Database administrators may be familiar with the concept of database migration, which has several implications in enterprise applications management. This primarily means moving a database from one platform to another. In another typical scenario, data migration may be moving an on-premises database to a cloud or PaaS solution.
This schema migration is also another type of database migration: evolving a database schema through reversible, incremental, and consistent changes. This approach also helps to integrate database changes with version control and processes related to application deployment.
The latest software development practice like DevOps requires people of different skill sets to work together by closely collaborating. This approach aims to knock down the conventional silos and long-existing bottlenecks between various teams involved in the development process. One major such separation is between the development and operations teams.
In any standard enterprise database development project, various teams’ collaboration is crucial to ensure timely accomplishment and success of the project. The DBAs, developers, and testers should work in sync and assess the impact of the proposed database changes before implementing them in real-time. Anyone involved in the process can take the initiative to start the conversations about whether the code of the enterprise database is secure, optimal, scalable, etc., to ensure that best practices in database building are followed. For a better insight into database building best practices, you can approach the consultants at RemoteDBA.com.
Usage of version control
No doubt that everybody involved is benefitted from version control. All those who participate in the development project must be included in the loop to track the contributors’ changes. From the application codes to testing, scripts, and build scripts, etc., which are used to create the database environment, scratch needed to be built this way.
Every single database change which is created even at the earliest stages of development must be captured. The migration scripts must include but not be limited to the modifications made on database schema as DDL or DML and the data correction changes to resolve any production data problems. Nowadays, it is also very common to have shared databases for organizations. This approach has a risk of delays in development projects due to resource contention sometimes. In some other cases, delays may be due to interruptions by development teams or other functional teams.
Leveraging benefits of continuous integration
Continuous Integration or otherwise known as CI in short is a standard development practice in which we merge all the changes from a developer to the given software branch. CI best practice is that every developer must integrate all changes from their workspace to the version control repository from time to time. There are many tools available for us to set up the CI process. Some popular choices of CI tools are Circle CI, Jenkins, GitLab, and Travis CI, etc.
Considering the fundamental theory behind CI practice, we can find some ideal characteristics of such database migration tools are:
- Each migration should be identified with a unique identifier.
- There should be a migration history table to record all migrations.
- Migrations must be reversible and repeatable.
All such practices of continuous integration and allied characteristics may be attractive and ideal to speed up overall development effort in database development.
Flyway for version control
Flyway advocates convention and simplicity over configuration to facilitate CI for various frontline databases. You can easily write migrations on plain SQL. These migrations follow specific syntax rules for different database engines as PL – PostgreSQL, T-SQL – SQL Server, PL/SQL – Oracle, and so on. Migrations using Flyway can also be executed manually using the CLI or programmatically with Java API, Maven plugins, Docker containers, Gradle, etc. As of late, Flyway supports about twenty database engines, irrespective of the DBs hosted on the cloud or on-premises environment.
In the beginning, executing database migrations using Flyway may not be very easy. Based on the choice you make to start with as Java classes or SQL script-based migrations, the learning curve may shorten or lengthen. You can explore detailed documentation of Flyway to quickly check, learn, and get proper guidance for the usage of every single command and functionality of each tool.
You also need not worry about any detailed track of all the changes made to your database. You can find all such details regarding migrations from the very beginning at the schema history table of Flyway. This table has all the details about every migration and the type of migration as to SQL, Java, who initiated, when it was done, and what all changes are made with each migration to the database. Flyway also resolves the pain point of mismatch in the database schema. With the recreation of the database from scratch along with specifying the exact schema version to deploy, it is a very powerful approach Flyway takes to apply the changes which belong to a particular application version.
So, what we discussed in this article is about evolutionary techniques for database design and best practices and techniques for database migration with tools like Flyway. We also had a look into Flyway structure and components and how this wonderful tool revolutionized database migration by making it an easier and rewarding task.
