In this post I describe some decision that you need to make when putting your database development under source control.
“Commit a SQL script to a local repository and the push to a central repository” sounds easy. In fact the all process should be easy.
The SQL script is the fundamental resource of the database source control process.
The script can represent two perspectives:
- The desired definition of the database object (table, view, stored procedure, function…), a.k.a state-based approach;
- The desired modification to be applied on the database object, a.k.a the migrations-based approach;
About this two approaches I like the definition presented by Alex Yates:
- State: Your source of truth is how the database should be.
- Migrations: Your source of truth is how the database should change.
So, this is your first big decision: state-based approach or migrations-based approach? Can I use both approaches? (The discussion of which one it’s the better approach will be explored on a future post).
The correct answer it’s directly related to… your budget. Each one of the solutions (state, migrations or even the combination of the two) will do the work and will cost time and money.
In your budget you should consider the following tools:
- For state-based approach
- For migrations-based approach
The next decision is to choose the source control system. All the tools above identified support or can be used with the following source control systems:
- Mercurial
- Is a distributed source control management tool (+info)
- Git
- Distributed version control system designed to handle everything from small to very large projects with speed and efficiency (+info)
- TFVC
- Team Foundation Version Control (TFVC) is a centralized version control that lets you apply granular permission and restrict access down to a file level (+info)
As “normal” user, meaning that I do not use APIs to work with this systems, my preference was built through the use of GUIs:
Currently my database repositories are Mercurial or Git because I prefer distribute systems. Then, once I use the migrations-based approach my first choice is Mercurial/TortoiseHg because in my opinion have a better branch representation. It´s less powerful than Git making it easier to use and teach (I taught a telecommunications team use TortoiseHg with great success).
Summing up: to answer the question “How to put database under source control?” you need to make two main decisions:
- SQL scripting strategy
- State-based or migrations-based approach
- Source Control System
- Centralized or distributed system