How to incorporate a database into a branch-per-issue version control process?

Question

We're hoping to move to a new branch-per-issue process with development. To prepare for this, a lot of research and experimenting has been happening in terms of version control, CI, and process of promotion. The one thing I don't want to fall off the project is the database and it's schema.

The current process we have in place puts us into an expensive position. We have four SQL Server boxes, each with their own environment worth of data. There is Staging (for development), Quality (for QA), Beta (for client prototyping), and Production. We're wanting to begin switching out some of our database model with MariaDB as we implement this new process as well. However, I'm having trouble putting my head around how we'll go about putting our SQL changes into version control.

Our new process will start off with a master branch, which will be our main code, followed by an integration branch and issue branches. Each developer will have their own web instance installed to make the needed changes on whatever branch they're working on at the time. However, we've always worked on the global Staging server for our SQL. This doesn't seem viable to me in the new process we plan on having. My guess for this is that each person will have a version of a database (MariaDB) that they make any schema changes to. So, that's my main question.

Do I use a process of only committing and merging schema changes? I wouldn't think that committing an entire database is a good idea. And at that point, how would this be stored in version control (we'll be using Git as our version control)? Would each schema change be stored as a separate file?

For clarity-sake, what we're thinking of using is a PHP/Apache server to serve out our web content, a Java API back-end, a MariaDB (which we'll move the SQL Server away from) and a Git repository for the version control on the web code and API code.

Answer 1

I propose looking into in code migrations for your language or technology. I have .NET stack and I can use Entity Framework Migrations where every schema change is single migration that can be automatically applied to database when needed. What is also possible is downgrading database, so pattern for db migration is that you have method for Up() and Down() this is really time saver when you have to deal with test, staging, production to keep changes in one place and don't bother with updating things manually.

But you could update question with technology stack you have available, then someone could provide you with your technology specific details.

Answer 2

The approach that I follow is based on the following premises:

1. Developers are not to be bothered with production data. That's for the operations department to worry about.

2. Configuration xml files, scripts, etc must be kept at a minimum.

3. Everything must be in the source code repository.

4. The source code repository is to contain only diff-able text files, nothing binary.

5. RDBMSes are nothing but tools that we use to persist our data: they exist to serve us, we should not be wasting our time curating them.

So, what I do is that I treat the database files as something completely expendable.

I have a set of classes describing my "model", from which the database schema is automatically computed, and upon instantiation the model also populates the database with all necessary predefined data if it is missing. So, as a developer you are always working on your own local database, (which, incidentally, may be HSQLDB for speed,) and you can at any moment drop all the tables of your database, or even the entire catalog, and re-run the application, at which point the application will re-create anything that it does not find to exist. This means that you can revert to any revision or switch to any branch at any moment, and you are immediately good to go.

The tests also create their own throw-away catalogs that they additionally populate with the sample data that they need in order to run. They make use of the model classes, so the database is first automatically created for them, and then each one of them adds the test data that it needs. I use the package name of the test (namespace name in .Net parlance) as the name of the catalog for each test, so that one test suite does not foul up the data of another test suite.

Maintaining the actual business data for the production environment is the sole responsibility of the operations department. When a release is made by the developers to the operations, it is the operations who need to convert any old data that they have to the new database format. Developers could in theory help them by providing entry points that can be invoked to convert an old version to a new version, but it turns out that this causes more trouble than it saves, so in actuality the operations are left completely on their own. They have engineers, they know how to write scripts for the RDBMS that they use, they can handle it just fine. They are also the ones who have the knowledge of precisely how the old information should be converted to the new information, and what kinds of values are suitable as defaults for newly introduced columns.

From this it follows that the only kinds of files that need to be stored into the version control system are the code, and a minimal amount of configuration files.

One more thing as a bonus:

You might be tempted to maintain the structure of your database as an SQL file containing all the DDL necessary to describe your schema, and all the DML necessary to populate the new schema with predefined data. Don't do it.

Information contained within configuration files and scripts must never overlap or duplicate information represented in code. A schema file which lists the tables of the database and the columns of each table is evil, because your code will presumably have to be written to also have knowledge of these same entities, so altering the schema file will not do you any good unless the corresponding changes are made in the code, and changing the code is also pointless without reflecting the changes to the schema file with painstaking attention to doing it exactly right. Clearly, one of the two must go. You cannot get rid of the code, so the schema file is the one that must go.

Not only "Code first", but also "Code only".