In unserer neuen Blogpost-Serie „Tech up!“ dreht sich alles um technische Themen – unsere System Technicians und Software Engineers zeigen ihre Expertise. In diesem Beitrag schreibt Philipp (Senior Software Engineer) zu Database Schema Migration. Blogpost in Englisch.
Many software projects still require manually applied SQL scripts. Therefore the application has to be disabled until the DBA successfully applied all scripts. And to fix a problem some SQL statements have to be quickly executed. Those projects all mess with the same problems. In this blog post we show you how database schema migration should be done right.
Continuous Database Migrations
In the classical world migration scripts were created, shipped, tracked and executed separately. Developers became inventive on how to avoid migrations by creating workarounds instead of making required database changes. This lead to database migrations becoming a huge pain as applications had to be stopped, scripts carefully applied and fixed quickly in case of any errors.
What Jez Humble and David Farley said in their book about Continuous Delivery applies perfectly to database changes:
„If It Hurts, Do It More Frequently, and Bring the Pain Forward“
Nowadays database migrations can be developed, tested, and deployed inside the CI/CD pipeline together with the source code itself. This lowers the barrier of making database changes and produces builds where code changes and database migrations are inline.
Frameworks tracking all migrations
Often the same application is deployed in different version. Production could be running on a stable release while integration is already testing the new features. A new developer might join and starts his setup from scratch.To support these scenarios it is important to bundle all migrations in the application. Frameworks track executed migrations and know which migrations still need to be applied.
Therefore it is important to include all migrations in the application, applied migrations are tracked and that new migrations are applied in order. We use frameworks that support this and make the necessary changes when the application starts up.
Frameworks achieve this by storing metadata like the version, the name and a checksum of each migration in the database. With this metadata the database becomes versioned by the tool and we can determine the actual version anytime we want. The framework can now migrate the database in a deterministic way from your current version to a newer one or recreate your database from scratch.
Shared Database Library for multiple applications
In many projects we find multiple applications that share a single database. In these situations, if one application upgrades the database other applications unaware of this change may break.
This can be easily prevented if we treat the database access layer together with the database migrations and the migration tool as a library. The applications depend on this library and changes to that library command that applications are updated as well. This way we ensure that all application use the database access code that corresponds to the latest schema changes.
Gain your DBA’s trust
In some projects only authorised persons may apply changes to the database. In such a case it is important to include the DBA in the design of the schema changes and explain how these changes will be applied when the new version of the application is deployed. At first, it might take some effort to get their support but after several successful schema migrations the benefit of this approach will become apparent.
Liquibase or Flyway?
This is a very popular database migration tool is Liquibase. The migrations can be defined in an XML, YAML or JSON based DSL which makes the migrations database independent. But Liquibase also supports plain SQL.
The following example shows an XML changeLog which creates a new table „department“:
<changeSet id="1" author="bob"> <createTable tableName="department"> <column name="id" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="name" type="varchar(50)"> <constraints nullable="false"/> </column> <column name="active" type="boolean" defaultValueBoolean="true"/> </createTable> </changeSet>
All changeSets are executed in the same order as they are listed in the XML file.
<?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="..."> <changeSet id="1" author="bob"> <createTable/> </changeSet> <changeSet id="2" author="alice"> <alterTable/> </changeSet> </databaseChangeLog>
Multiple XML files can be inclued in a master XML with the
Liquibase DSL is very powerful and supports schema migration (DDL), data manipulation (DML), data loading from external CVS files, plain sql statements and self controlled rollbacks among other things. Liquibase also supports comparing databases and generating DSL changelogs.
Compared to Liquibase, Flyway is a really flyweight. It supports the execution of migrations written in SQL or Java. Each migration resides in a file on the classpath or on the file system. The version and the description of the migration is defined by a naming pattern which also defines the execution order of the database changes:
Source picture: https://flywaydb.org/documentation/migration/sql
A SQL-based migration that creates the above department table would be simpler than the Liquibase DSL, but have to be written in the right SQL dialect.
create table department ( id integer primary key , name varchar(50) not null , active boolean default true );
For recurring actions like maintenance or housekeeping tasks Flyway offers the possibility to hook into its lifecycle by using callbacks.
For recurring actions like maintenance or housekeeping tasks Flyway offers the possibility to hook into its lifecycle by using Callbacks. The lifecycle for a Callback is also defined by a naming convention, for example
beforeMigrate.sql, beforeEachMigrate.sql, afterEachMigrate.sql or afterMigrate.sql.
The choice of the tool is often a personal preference. The only thing that matters is that we use a database migration tool at all. I personally love Flyway because of the much better readability of an SQL expression compared with an XML used to describe database migrations. Further I prefer the convention over configuration approach of Flyway. The only thing to remember is to save the file with the correct naming pattern in the correct directory.