CI/CD for database - 2 devops tools for DB versioning and migration | liquibase and flyway

how do you bring your database changes in line with other development efforts keep them under Version Control and enable automatic safe deployments in other words how do you include them in your CI CD process we'll have a look at what's the problem with database schema changes why they differ from application changes and two tools that help us in solving issues with database migration Flyway and liquid base what's so different from deploying new versions of your application code versus deploying new database schema changes typically your application code goes through phases new code new version is source code repository new Deployable artifact new deployment that replaces your application lifecycle for database schema design if an example introduce new columns you can't just redeploy new schema you probably already have data in your production system existing data means State and as we already saw in most recent AI example data is King under no circumstances can you afford to lose it so drop create approach is definitely not valid instead you have to take approach that respects existing data and incrementally add schema changes so-called database migration approach how does database migration approach look in an example of a new column instead of drop create table scripts you'd have alter table add column scripts that will make sure you keep Integrity of existing data but you'd also need a way to track what migrations you've already run not to accidentally run scripts multiple times in essence you need migration templates that tell you how to get to the next version and a way to track what was already executed on environment you are trying to upgrade does the approach database migration tools liquibase and flyby take to help you with migration as for migration templates in liquid base you have explicit file that serves as a ledger of changes called changelog and unit of changes it contains are called change sets Flyway takes more implicit approach where you use file naming schemes to tell the tool that this script should be run as a unit of change to Mark whether a certain change has been run and provide deployment information both tools create tables the first time you run migration in certain database and store change name along with checksum of change content by default liquibase creates database change log table and flyby flyweight schema history table what's nice is that migration templates are just plain text files and can be stored in your Source control alongside your application code they can be integrated in your CI CD pipeline bringing in line your database changes with the rest of the automatic devops deployment process both liquid basic FlyAway provide pretty much every standard way to integrate into your CI CD pipeline Docker Maven command line interface and spring boot has started for both tools allowing you to run migrations on your application startup in Enterprise where your change is traditionally travel through multiple environments like Dev quality assurance user acceptance testing broad liquid base and Flyway are smart enough to run only necessary changes for example Dev environment would probably be the closest to the latest changes so only one script will run while other environments might have multiple schema changes ran on them these tools will primarily design for relational databases that have schema like Oracle postgres MySQL where they shine but have also added support for other types of databases liquid Basin flyover primarily designed for schema migrations although they technically support data migration migration scripts are just text file they they are public and kept in your source code repository so they should not contain any sensitive information if you do migrate data you should restrict yourself just to reference codes your app might need to work like country and currency codes let's look at an example of schema Migration by using liquid base and flower command line tools I've prepared a project where we'll start up two local postgres instances one for Flyway one for liquibase and migrate the same schema to get a sense of how these tools work in the project we have Docker compose file that will start up our instances and directories for FlyAway and for liquibase which contain appropriate files for each of the tools let's start up our instances using Docker compose and then we'll have a look at what each file in the directory contains and just to confirm we have two instances started our Docker compose file contains two Services both based on image for postgres 15 database one for liquid base and one for Flyway and we have them running on different ports so we can have them up at the same time and avoid Port conflicts in order for us not to have to type in username password and database URL each time you use the CLI we Define those properties in configuration files by default liquibase uses liquid-based properties file and flyby uses flybyconf file normally you wouldn't keep your database passwords in a configuration file but would use environment variables or some other mean to provide them at runtime we'll use D beaver free database tool to connect to our instances and we'll notice that currently they are empty there are no tables or any database objects in them for our demonstration we'll try to migrate three tables in a view we'll have accounts table roll stable and account roles table which is in intersection between the two and we'll also have a controls view which connects three tables together after our initial migration We'll add additional column to accounts table and account roles view to demonstrate how to deal with schema change we'll start with Flyway flyby uses file naming convention to determine which SQL files it should run during migration SQL files that are stored under SQL subdirectory and begin with uppercase B followed by the version number double underscore and a custom name are run automatically number behind the uppercase V determines the order so a file named v1.01 is run before a file name v1.02 unlike tables that cannot be created multiple times database source files like views functions procedures triggers Etc are treated differently as they can be recreated multiple times each time they have a change we have to let FlyAway know that we are dealing with objects that can be recreated when changed we do so by beginning our file names with uppercase r instead of uppercase B since these files can be run multiple times we don't need to provide a version let's run our migration I'll use Flava command line tool which you can download from Flyway website you start fly by Migration by using flyby migrate command foreign we'll note this Flyway has created our three tables and now repeatable view migration but also since this is the first time we ran migration in this database instance Flyway schema history table this is the table flyby uses to track which migrations have already been run in this database instance if we refresh our postgres instance where we run our migrations we'll see our four tables and a view popping up if we have a look at flybe schema history table data we'll notice it contains metadata columns about the script being run date when it was run and the version number it also contains checksum column checksum contains hash of script content and we let FlyAway know if someone tampered with the file after the migration has been run to demonstrate this let's go back to our accounts SQL and change username column to username one save and try to run the migration again let's see what do we get this time when we run Flyway migrate again and we get a narrow check some mismatch let's get back to our script and return the old name for our next step we'd like to add first name column to our accounts table we already saw we can't modify this script instead we'll have to create a new one that will alter the table let's create our new script we'll need to call it uppercase V1 Dot zero four double underscores and then we can provide any name we'd like let's say Accounts at first name dot SQL best is to provide some semantic name that has a meaning of what the script does and then there we can add our alter table statement we also want to add the first name column to our view but since the view is repeatable migration we don't need to add a new script we can modify the existing one if we go back to our database instance and have a look at the accounts table we'll notice that there is no first name column similar for our account rules view so let's go and carry out the change we can run our Migration by using flyweight migrate again and we see that two migrations have been run one for adding a column and the other one for repeatable migration for our view if we go back to our database and refresh we'll see the first name column popping out in our view and similar for our accounts table nice let's run the same migration with liquibase liquid base is usually used in a way where naming doesn't matter so I've created a subdirectory called schema and placed our scripts in there notice there's no special prefix the script name is accounts.sql liquid base considers these scripts as change sets and the way you tell liquid base which change sets it needs to run is by including them in change lock file we've included our scripts in a changelog file by providing explicit path to them notice we only have three scripts that's because for one change I wanted to Showcase alternative liquid base provides instead of SQL we can use liquid-based special Syntax for creating database objects advantages that liquid base uses database agnostic properties for an example column types and we'll try to run appropriate SQL depending on which database we are trying to deploy on disadvantage is that you can only run these changes with liquibase as it's not something database native tools can understand to tell Equibase that certain changes are repeatable like those on views packages functions views run on change attribute in order to keep our liquid by CLI commands short we specified which changelog file should liquid base use in liquid-based properties file if we go back to our database instances we'll notice that the instance for liquibase has no tables and has no views in order to run migration with liquibase I'll use Liquid based CLI tool which you can download from liquid-based site and we can use the update command to run our migrations and we see the update was successful if we go back to our liquibase instance and refresh we'll notice that our objects are created we'll also notice two tables we didn't specify if are created database change log table is the table liquid base uses to track which changes have been run on the instance and database change log lock table is helper table to prevent multiple simultaneous schema updates from different sources database change log table has metadata columns for change set liquid base has run like data executed order executed md5 sum let's check some for script content which is the way liquibase knows has someone tampered with the script after it has been run notice that for the ID column we don't have to use numbers instead I'd recommend to use semantic names that tell you what the script purpose is if we go back to our change lock this is the ID I'm talking about if we open one of our scripts and try to change the column name let's say from username to username one I try to run migration again we expect error because the checksum has changed let's try it out and try to run liquid-based update again and we see that change set checksum has failed and our update wasn't successful let's return our old column name and let's create a new migration to add first name column to our accounts table we'll paste alter table statement in there we'll also add first name to our account rules View account rules view script is already included in our changelog file we need to add accounts add first name script and we can add it underneath the create table accounts to keep our accounts changes grouped closely together we'll rename our ID to alter table accounts add first name column and we'll change the path to accounts at first name SQL let's go back to our instance and confirm that currently account rules view doesn't have first name column neither does accounts table let's run liquid base object command to run R2 migrations and they were successful column was added and the view was changed if we go back to our instance and refresh we see the first name column in our table and the first name column in our view if you go to database change log table and refresh we see that the view script was rerun and the add column was run it was sixth script that was executed nice that concludes our liquid-based migration showcase what if you made mistakes in your schema changes and need to roll back both tools offer rollback strategies and allow you to specify rollback's statement for your schema change and sometimes provide it automatically for example drop column is a rollback for create column but again data is king and you have to consider what kind of rollback strategies you'll use in the time you created the column and you realize it was a mistake you might already be having data inside so instead of rollback it might be better to employ so-called fixing forward strategy where you don't just drop the column but before create a new column on some other appropriate table and move data there before dropping it's important to note that I've only scratched the server person what these tools can do snapshotting of schema schema comparison automatic generation of history for deploy changes Etc if you haven't used them already I hope I've convinced you to try them out on your projects

Channel: kanezi

Views: 9,693

Keywords: database, ci cd, devops, liquibase, flyway, migration, schema, cli, versioning, database versioning, database migration, rollback, rollback strategies, DB, relational database, continuous integration, continuous delivery, schema change

Id: KjPlcXkk7xY

Length: 16min 40sec (1000 seconds)

Published: Wed Apr 05 2023