Source Control Management and Databases

Source controlling or versioning database schemas is a recurring topic on StackOverflow and on database-related blogs and web sites.

There does not seem to be a single correct solution, rather everybody tries to find their own way to deal with this topic.

The core of the problems seems to be the different types of data between file-based source code and database schemas. Whereas source control systems typically have to deal with source code in files, and every check-in relies on the ability to compile every revision in source control (well, in an ideal world), versioning databases is different in the aspects:

• store a version of the full database schema (for installation)
• store a change script / migration script to upgrade from one version to the next (for upgrades)
• store the values of certain tables (lookup tables, system and application constants)

Command-line tools

In recent years I developed a set of tools that I think may be helpful to the task of versioning databases:

SMOscript is a tool which scripts all database objects in an MS SQL Server database to file using the MS SMO library. Using the -f switch, one file per object is created in a directory. If that directory is under source control, the developer has all CREATE statements in the same revision tool as their application source code.

Oraddlscript is the equivalent of SMOscript for Oracle databases. It makes use of the built-in function DBMS_METADATA.GET_DDL, which provides the CREATE statements for any object inside an Oracle database.

Integrated solution

dbscript is a web application which tries to integrate the 3 aspects mention above:

• dbscript keeps in its database every schema ever uploaded or imported.
• dbscript compares any two database schema versions displaying the differences
• dbscript generates a migration script of selected objects as a result of this comparison
• dbscript compares a set of schema versions to display a timeline of version changes
• dbscript generates T-SQL value scripts and C# constant declarations based on database data

The generated files can be stored inside the dbscript database (always related to a schema version or a comparison between versions), and / or downloaded to a directory covered by source control.

Note: I am biased, since I use dbscript for the projects I develop since its first stable and reliable version. Actually, dbscript development itself uses dbscript, as can be seen in the SQL files shipped for installation and upgrades.

For more information on the products, see the company website. Free downloads are availabe here.