Deploy Database Changes To Production

Med Park 360

Last year I started a side project to help me take a hands-on approach to learning new technologies and methodologies. This project is Med Park 360. I started this project mainly to get hands-on experience in creating and a Microservice architecture application and learn all the bits in between that make all the individual services function together. Most of my blog posts will probably reference this application as we go along and as I learn/implement new features.

Continuous Integration

As I’m in the process of having this application hosted on Azure for everyone to play around with, I am setting up a DevOps pipeline for each of the services. These pipelines will be responsible for building each service and deploying them to Azure where they will be hosted.

Database Updates

To streamline this entire process of continuously deploying these services, from time to time database changes will be a part of this. I already have the entire build and release processes automated, so having to manually update the databases of each service feels counterproductive. For this I can look no further than Entity Framework which I am already using in my services. Entity framework has a nice little feature for you to generate an SQL script file for the database migrations you have in your project. This allows me to automate the database update process.

The first step is to generate this script during the build process. To do this, we need to run the script below:

dotnet ef migrations script -o sql/Service_DB_Migrations.sql

The EF Core command-line tool has been removed from the .NET Core SDK starting with .NET Core 3. Due to this fact, a separate package has to be installed.

Below is a sample of the build pipeline I have setup for one of my services. After I installed the new EF Core global tool, I proceed to generate the SQL script. As you can see, I added the Idempotent (-i) flag to script. This is to ensure that the script is Idempotent and that it can be run over and over again without failure. Basically, what it does is add the IF EXISTS operator to every query in the script file it creates.


Continuous Deployment

Once the build has successfully completed and artifact, which contains the SQL script file produced during the build process will be drop for the release to use. My release currently consists of one step, to run the SQL script with all the migrations for this service on my SQL server. Below is an example of said step in my release pipeline:

This step will connect to my SQL server, and execute an SQL script file that I am providing. The location of the script file is in the drop folder of the build artifact.

There you have it. Deploying your database changes to production without doing much. It is super easy to setup a build pipeline for your project on Azure DevOps, just follow these instructions. Azure DevOps will also not cost you a cent because it is FREE.

If you have any follow-up questions, please do not be hesitate to reach out to me on twitter.