Database source control
When source-controlling databases, there are two main tasks that you might want to do: creating a database, and upgrading an existing database. There are (roughly) two schools of thoughts on how to handle these tasks, but we'll first introduce a couple of common notions:
- Upgrade scripts, also known by other names such as migrations. Each upgrade script describes how to transform a database from one state to another. Examples include creating tables on a blank database, adding tables to an existing database, or modifying existing tables.
- Completed upgrades table: a table in the database that contains the names of upgrade scripts that have already been run.
There are other ways of accomplishing the same job without some or all of these notions, but they're sufficient to illustrate the two main schools of how to handle database source control, specifically:
- A database is described by all upgrade scripts. The way you create a database is by running all upgrades, starting from a blank database. In this scenario, you always have to keep all upgrade scripts, since they're also your creation scripts. When you want to bring a database up-to-date, you run all upgrade scripts that haven't been run in the past, which is any script whose name isn't in the completed upgrades table.
- A database is described by a set of creation scripts, with separate upgrade scripts. Rather than running upgrade scripts to create a database, you run the creation scripts. Upgrading a database is the same as before: you run all upgrade scripts that haven't been run in the past. You can delete upgrade scripts once you're sure that there are no existing databases that are missing that upgrade.
The advantage of #1 is consistency: freshly created databases and databases that have been upgraded many times should have had the same set of operations applied to them. The biggest downside is that there's no easy way to inspect the schema of the database: you have to apply all the upgrades to work out what the current schema is. Additionally, if your schema has changed a lot, you're doing a lot of unnecessary work to create a database,
The advantage of #2 is that the creation scripts provide an easy way to tell what the current schema of the database is, as well as avoiding unnecessary work when creating a database. If you're releasing regularly, then upgrade scripts can be deleted fairly rapidly, helping to keep your source code repository tidy and understandable. If you're worried about separate creation and upgrade scripts causing inconsistency, then you can use some form of SQL diff to check.
Middle ground
There is something of a middle ground between these two approaches. We start with approach #1: a database is described by all upgrade scripts. Periodically, we combine all upgrade scripts into a single upgrade script that avoids any unnecessary work, such as creating tables that later get deleted.
Although it's possible to make this approach work, I'd suggest that you may as well as use the second approach. By rolling the upgrade scripts into one, you're essentially creating a creation script, but you're potentially doing so long after the original upgrade script was written. Over time, you might forget some of the details or nuance that you had in mind when the upgrade script was originally written.
ORMs
Many ORMs require you to describe the database schema using the ORM, such as the names of tables and the details of each column (name, type, etc.). This information is often sufficient to allow the ORM to automatically generate a creation script. In other words, if you're already using such an ORM, approach #2 becomes more attractive since you get the creation scripts for little extra effort.