Deploying Databases

Deploying application code is one thing but deploying databases can be a daunting task if not done properly. There are two pain points when deploying databases: structure and records.

With a database’s structure, you have the issue of adding, updating, and removing columns/fields from tables, along with updating the corresponding stored procedures, views, and other table-related functions to reflect the table updates.

With records, the process isn’t as tricky as changing a table’s structure. The frequency of updating records is not as regular, but when it does, happen that’s when you either want to seed a database with default records or update those seed records with new values.

The following sections will cover some common practices when deploying databases in a CI/CD pipeline.

Backing up Before Deploying

Since company data is essential to a business, it’s mandatory to back it up before making any modifications or updates to the database.

One recommendation is to make the entire database deploy a two-step process: back up the database, then apply the database updates.

The DevOps team can include a pre-deployment script to automatically back up the database before applying the database updates. If the backup was successful, you can continue deploying your changes to the database. If not, you can immediately stop the deployment and determine the cause of failure.

As discussed in the previous section, this is necessary for a “fallback” approach instead of a “fall forward” strategy.

Creating a Strategy for Table Structures

One strategy for updating a table is to take a non-destructive approach:

• Adding a column: When adding columns, place a default value on the column for when a record is created. This will prevent the application from erroring out when you add a record, notifying the user that a field didn’t have a value or is required.
• Updating/renaming a column: Updating a column is a little different because you may be changing a data type or value in the database. If you’re changing the column name and/or type to something else, add a new column with the new column type, make sure you default the value, and proceed to use it in your application code. Once the code is solid and is performing as expected, remove the old column from the table and then from your code.
• Removing a column: There are several different ways to handle this process. If the field was created with a default value, make the appropriate changes in your application code to stop using the column. When records are added to the table, the default value won’t create an error. Once the application code has been updated, rename the column in the table instead of deleting it. If your code is still using it, you’ll be able to identify the code issue and fix it. Once your code is running without error, it’ll be safe to remove the column from your table.

While making the appropriate changes to table structures, don’t forget about updating the additional database code to reflect the table changes, including stored procedures, views, and functions.

Creating a Database Project

If your Visual Studio solution connects to a database, there’s another project type you need to add to your solution called the Database Project type. When you add this project to your solution, it takes a snapshot of your database and adds it to your project as code.

Why include this in your solution? There are three reasons to include it in your solution:

1. It provides a database schema as T-SQL when you create a database from scratch.
2. It allows you to version your database, in keeping with the Infrastructure as Code (IaC) paradigm.
3. When you’re building your solution in Visual Studio, it automatically generates a DAC file from your Database Project for deployment with the option to attach a custom script. With the DAC included in your solution, the pipeline can deploy and update the database with the DAC file first. Once the database deployment (and backup) is finished, the pipeline can deploy the artifact.

As you can see, it’s pretty handy to include with your solution.

Using Entity Framework Core’s Migrations

Entity Framework has come a long way since its early days. Migrations are another way to include database changes through C# as opposed to T-SQL.

Upon creating a migration, Entity Framework Core takes a snapshot of the database and DbContext and creates the delta between the database schema and DbContext using C#.

With the initial migration, the entire C# code is generated with an Up() method.

Any subsequent migrations will contain an Up() method and a Down() method for upgrading and downgrading the database, respectively. This allows developers to save their database delta changes, along with their code changes.

Entity Framework Core’s migrations are an alternative to using DACs and custom scripts. These migrations can perform database changes based on the C# code.

If you require seed records, then you can use Entity Framework Core’s .HasData() method for easily creating seed records for tables.

In this section, we learned how to prepare our database deployment by always creating a backup, looked at a common strategy for adding, updating, and deleting table fields, and learned how to deploy databases in a CI/CD pipeline using either a DAC or Entity Framework Core’s migrations.