One of the things that’s often overlooked when people talk about CI/CD is the database. In the .Net world, where I live, that’s because, in most cases, this is handled by Entity Framework. I’ve written several posts on EF in the past. Entity Framework uses, what is known as a migration based approach to solve this problem.
A migration approach is one of two methods used to solve the problem of how to update a database using CI/CD.
Migration
In my early career, I worked at a company that used an Oracle DB for the data. Entity Framework hadn’t been born yet; however, the way that they managed the database was to use a migration based approach. The DBA would maintain a script - just one - which contained an idempotent list of DB operations. This wasn’t so different from the migration based approach that EF and other migration based packages use; the main difference being that the entire script was run for every deploy; whereas most of the modern packages support a principle whereby you store each change in its own script, and which scripts have been applied are written to the DB itself. The scripts still need to be idempotent; it just means that it only runs what it needs, so it’s faster.
The main advantages of this approach are that you can have the scripts that change the DB in source control. However, one big disadvantage is, if the state of the DB changes for some reason (for example someone makes a live change), you won’t see it.
State
The second option is known as state based. The idea here is that the state of the database is captured and stored in source control. When it comes to deploy, this state is compared with the state of the running DB, and a script is generated to make the running DB the same as the source controlled one.
When I’ve used this in the past, we kept this script, and source controlled the script as well; however, it’s specific to the DB at that time. What I mean by that is that, imagine you have a live and test database; you can’t necessarily take the script that runs on test and apply it to live. You might be able to, and you probably should be able to, but it’s not a given.
The tool that we used at the time was SQL Server Data Tools (dacpac).
Resources
A list of useful resources can be found here.