Welcome back to Introduction to Continuous Integration. I'm Ben Lambert and I'll be your instructor for this lecture.

In this lecture, we'll take a look at some general ways to handle database schema changes and how we can automate those changes. If you're going to automate building and testing that means for a lot of applications, you're gonna need some way to handle database changes. By the end of this lecture, you'll have an idea of the challenge that schema migrations pose and some methods to deal with them.

I mentioned previously that ideally your unit test will not hit an actual database and instead will use some form of a fake implementation. However, this isn't always possible. Sometimes you'll need an actual database for any number of reasons. And while unit tests may not typically hit the database, other tests such as security audits may.

So here's the problem we're trying to solve. When writing or changing code for an application, often times the database will need to change to support that code. This could be in the form of a new column, maybe to store something like the date a user last logged in, or it could be a new table, a column rename or something along these lines.

When you make a change in code that depends on changes to your database schema, you need to be able to test those changes. That means you need to be able to apply the schema changes to any databases that are used for testing, and eventually, for production. There are different ways of handling this. However, I like using an ORM with schema migration tools. And ORM is an object-relational mapper, which means it allows you to represent your database with classes in your code. Using an ORM, you can keep your logic in code and have it versioned. And by having schema migrations in code as well, you get the same benefit.

Most platforms offer some method for turning schema changes into code. For .NET, Entity Framework allows for Code First Migrations. For Java, you could use Hibernate and Flyway. For Ruby, assuming you're using Rails, you have the functionality built into Active Record. For Python, if you're using Django, then the ORM and migrations are built in. If you're using Python but not Django you could use SQLAlchemy and Alembic. Now, I'm not gonna go through every language. Rather, this is just to show that most modern programming languages have some sort of library to handle schema migrations in code.

So an ORM and schema migrations are a good start but it needs to be paired with a strategy for ensuring that we can continuously deliver and mitigate the risk of breaking things. So what does that strategy look like? First, your database should be versioned. The tools I mentioned earlier handle this for you by creating some sort of migrations table that will track what changes have been applied already. Next, each schema change should be its own migration. A migration filled with several changes makes it difficult to isolate problems should they arise during the migration process.

Next, changes should be non-destructive. As an example, instead of renaming columns, add a new column, populate it with data from the old one and have your code continue to read the old column but write to both. Once you've confirmed that the database changes were successful and that the data is being written successfully, then you can edit the application to use the new column. And once everything looks good you can remove the old one.

Next, we need to ensure that newly created columns use sane default. What I mean by this is if you have a new non-nullable column in an existing table with data in it already, you need to first make sure that you set a value for all existing records before changing it to non-nullable.

Let's check on an example with Django. We're going to covert a full name field into two columns, a first name and a last name. Here in the user interface, you can see that we have this full name field, but that's it. So next, in code, I'm going to add these two fields, first name and last name. And then I'm going to run the command to actually make the migration. Next I'm going to apply the migrations with the migrate command and now if we look at the user interface again, we have two new fields, but they're empty. Next, I'll ad an empty migration and it's going to serve as our entry point to populate the first and last name field. So I'm gonna run the Make Migration command to add an empty migration. And now I'm gonna paste my pre-written code into this migration. Now once I run my migration, and if we look back at the user interface, you're going to see that it's populated our new columns.

So doing it this way, should something have gone wrong during the migration process, we can keep our code running because we didn't actually remove anything just yet. Now this is just the beginning. Once you have your database changes under version control, you can roll forwards and back. You'll start seeing ways to improve the process as you go. Some will be generic ways that apply to all and others will be non-generic ways that are specific to your code base.

Alright, in this lecture we learned that database schema changes can introduce challenges when you need to keep a code base in sync with that database. We learned that using an ORM and schema migration tools can help to mitigate that challenge.

In our next lecture, we're going to take a look at an actual Continuous Integration pipeline using Jenkins, a popular CI tool.

Alright, let's get started.