Relational database migrations (with Flyway)

I will show how you can use Flyway to have a consistent way to migrate databases (MySql, Maria, Oracle, SQL Server and a lot more) automatically during deployment/as a part of your continuous deployment process.

This can also help you with your integration tests.

* What are migrations?

* What are no migrations?

* Why should I automate it?

* How does Flyway work?

* Flyway in an automated deployment process

* Live Demo

Transcript

1. www.onwerk.de Relational database migrations (with Flyway) Jens Doose, Onwerk GmbH RheinNeckarJS Meetup 15.02.2017 @onwerk OnwerkGmbH Onwerk
2. www.onwerk.de In a nut shell Database migrations… … transform a database (schema) … from one version to another
3. www.onwerk.de In a nut shell Flyway helps you to have a consistent, reliable, reproducible and automatable way to migrate databases of multiple vendors
4. www.onwerk.de Why think about migrations anyway? Database version hell: Manual application of SQL scripts What state is the database in? Which version? Has the SQL script been applied to the database or not? Hassle to re-create the database from scratch, matching a certain source code version  we don‘t want that.
5. www.onwerk.de Why thinking about it? What we want: Version control of database schema (like code) Always know exactly what database version is on production server, test server, developer machine Always be able to fully recreate database schema to any point in time Automatic Deployment process  continuous deployment
6. www.onwerk.de Definitions Database schema (structure): tables, views, stored procedures, etc. „immutable data“: rarely changing configuration data,  not editable within the UI „mutable data“: Data the user entered
7. www.onwerk.de What are migrations? Migrations Transformation of database schema Manipulation/Import of „immutable data“ Transformation of „mutable data“ (e.g. adding new columns) No migrations are Import of user specific data Import of „mutable data“
8. www.onwerk.de Flyway One tool for database migrations. Others exist. available for: Command-line, Java API, Maven, Gradle, Ant, SBT Configuration by config file, command line or both Main commands: flyway migrate: Migrating a database, if necessary flyway info: show current state of database, show applicable migrations
9. www.onwerk.de Executing a migration flyway migrate automatically scans directory for applicable migrations, executes them Regular SQL files Order is determined by file naming specification: V1_0__Baseline.sql V1_1__Adding_Address_#598.sql V1_2__Removing_zipcode_#1102.sql
10. www.onwerk.de Demo
11. www.onwerk.de Transaction / Revert Single migration = transaction  Only useful if database supports DDL transaction! only DB2, PostgreSQL, Derby and to a certain extent SQL Server support DDL statements inside a transaction  Don’t rely on transaction / rollback Downgrade / revert to previous version? NO!  backup database / test migrate database during deployment
12. www.onwerk.de More cool stuff Placeholders specified by command line or config file Usage in SQL file: ${placeholder} (configurable) Callbacks on events like beforeMigrate, beforeEachMigrate Special file names when using command line
13. www.onwerk.de Use cases Development Easy database setup for (new) developers Unit / Integration Tests Test migrations Build up database for integration tests with real databases (Continuous) Deployment Service shutdown  Backup  Test migration on copied database  Migrate “real” database  Deploy app  service start
14. www.onwerk.de Anti-Use cases / Limitations Yes, there are limitations MS SQL Server: Some ALTER DATABASE commands won’t work, Line break settings in stored procedures Flyway core is a Java module. Command Line Tool is a Java program.  Java must be installed (no support for load balancing / high availability / multiple nodes)
15. www.onwerk.de Best practices / tips It is not magical Backup, backup, backup (Dude, d’oh!) no database name in SQL migration files  use placeholder (inspect your generated SQL dump files!) use configuration files Avoid any(!) manual steps and/or modifications on the database Deployment / migration first! Right from the very beginning of project Visual Studio Schema Compare  SQL Migration file
16. www.onwerk.de Alternatives? NoSql databases Liquibase (http://www.liquibase.org/) Originally XML, YML, JSON: Description of modifications later: SQL support Doctrine (PHP) Many more
17. www.onwerk.de Questions? Follow us: @onwerk OnwerkGmbH Onwerk