POSTED 19 May 2016 by Nathan Voxland
Agile development practices and modern source control systems such as Git have transformed how application software is developed and managed. But for many application developers the way the database is changed is the same as it was in 1995. Well, maybe not exactly the same—now SQL files for each release are stored in a shared Windows drive instead of a shared Novell server – but you catch my drift.
The database schema is an integral part of any application, so changelog files should be stored along side application code. That will allow an existing version control system to ensure everything is kept in sync–whenever application code is committed database changes will go along with it and whenever anyone updates application code they will get the new database schema.
Branches are an integral part of developing application code and database changes need to flow along with them. Because Liquibase tracks each changeSet independently rather than relying on a single incrementing “database version”, when application developers merge in a branch all the new changeSets will execute as expected, even if there were “later” changeSets already executed.
When developers do run into merge issues, the simple text formats used for the changelog files can be easily merged with their favorite tools.
Liquibase supports changelogs written in XML, YAML, JSON and SQL. Use whatever is most readable to you.
Liquibase tracks which changelog statements have run against each database, so once developers create a changeSet they can be confident that it will be deployed through QA and production.
For best results, whenever an application developer need a database change, they can simply append a new changeSet to the local database and then run a Liquibase update to apply it. This works better than making changes to a database directly and re-writing it as a changeSet because they are truly running the same update as everyone else.
XML-formatted changelogs have their advantages, but many DBAs still prefer good, old-fashioned SQL. If that is what you are most comfortable working with, Liquibase-formatted SQL provides the standard changeSet tracking used in any changelog format but lets the DBA specify the exact SQL they want.
Normally, test data is stored in something like CSV files which are loaded into the database after it is built. The problem with this strategy is that any schema changes can and will break something in the data load process. Typically this leads to application development teams spending hours of time trying to figure out what the test data was trying to expose and then adjusting the files to match the new schema.
Instead of loading the data into the final schema, build up the test data within a changelog file. Use loadData or standard sql to load data into the schema as it is now, then as new schema changes are appended to the changelog the test data will be migrated just like production data would be. This not only keeps application developers from having to continually update your CSV files, but also helps verify that existing data is handled correctly.
Another best practice is to use contexts and labels to mark which changeSets contain test data so they are not deployed to production.
Liquibase ships with several tools application development teams can use to: check current database state, make sure changes are being applied correctly and ensure that no one is sneaking in changes out of process.
If your application development team determines their needs extend beyond all the great capabilities Liquibase offers check out Datical. Datical builds upon Liquibase and lets DBAs:
More information on Datical DB can be found here.