Database Change Control, Part 2

Code-First is a great way to stand up a data access layer quickly and with minimal pain. Code-First really excels because it allows you to easily blow away your schema. But Code-First isn’t a great scheme if you want to do a lot of stored procedures, or if your system contains a lot of views. In those systems, it makes more sense to have something more focused on the SQL part.

In this post, we are going to discuss using database projects as a means to database change control. The database project has been with Visual Studio for a long time. They are similar to Entity Framework Code-First, in that they will create a database that matches the configured schema.

Visual Studio database projects and Entity Framework Code-First have a big advantage over change-based storage schemes (we will cover those later). The big advantage is that in source control you can see what your current system should be. You can look at a single TimeLogItem.cs file or a TimeLogItem.sql file to determine what the underlying database table should be. When we dive into change based schemes this problem will become more obvious.

Database projects are nice in that they provide the best of two worlds. You can see what your current database schema should be (that way you can also use source control to see how things have changed over time) and they support adding random SQL things, such as views / stored procedures / triggers.

But database projects also have a downside, which is the same downside as Entity Framework Code-First: it’s painful if you want to make a change, and you risk harming your data. With a schema-based system, you often have to step out of the easy path and do something weird when you are writing scripts that will affect existing data. Creating a data migration with either schema is a little awkward.

So how do we perform a data migration to a database project? We’ll add a database project to our existing Visual Studio project.

Since we already have a database, we will import it. Right-click on the database project and select “Import | Database”.

Connect to the DbSrcExampleBase database.

Then we will turn off automatic migrations.

And we are done. It’s that simple. To perform an upgrade, just right-click on the database project and click “Publish”.

To test this, we’ll add a new database table named “tests”.

Right-click and publish the update. You should now have a new table “tests” in the database.

The database project has two advantages. First, it is a schema-based system so it works great with source control for tracking database changes over time. Second, it uses SQL scripts as the source, which I think works better for many developers. Personally, I like using database scripts because they fit well into how I develop applications by allowing me to store the scripts I create along the way.

In the end, it’s important to remember that database projects fall into the same problems as Entity Framework Code-First in that they don’t work well when you need to make big changes, especially big changes that have repercussions for data.