01 April 2014

We don’t need Source Control: we’re Database Developers

As part of our long-running series of articles where we ask working database developers how database source control improves their work within development teams, we made the mistake of asking Hugh Bin-Haad, Database dev and relational theorist.

So you want me to use source control for my database? OK, let’s consider what you’re proposing to do by putting my database code in source control. We have a multi-user system, and we’re taking the code from it, and putting it into another system so that several developers can work on the code independently? What part of the term ‘multi-user’ don’t you understand?

Any developer who’s entitled to work on the development database can do so. We don’t have to build and integrate the database either in order to test it, because it is already built. It is even cleverer than source control because we can give permissions on the individual schemas or even objects to control the access of the individual developers. We can even specify the type of permission

Oh. So I shouldn’t be using a ‘shared’ database for development? Each developer should use their own development database, put their code in source control and then build a database from source control? So, instead of building a multi-user system in a multi-user way, we should pretend it isn’t a multi-user system? If we do that , we then have to devise a way of resolving the differences when the different contributions are merged.

Ah, so if the database is in source control, then people working on the same bit of code can have their code merged? Wow. So if two people simultaneously add a column to a table, or a parameter to a stored procedure, they both get put in? Hmm. Do you believe that? Well, I don’t, since a VCS doesn’t even attempt to detect a semantic difference, just a textual difference. It can’t even tell whether a change is to code or to a comment! Are you sure that it is better to attempt a merge than simply bin the work of one of the developers? In SQL Server, if two people work on the same table and both of them insert a column using ALTER TABLE, , then by the miracle of ACID, both columns get inserted. Why are two developers working on the same schema at once, anyway? The schema is there to allow a partitioning of large databases, which means databases large enough to require more than one person working on them, by logical areas of functionality. On any sensible SQL Server development system, all changes are recorded, anyway.

So, source code will be lost if we allow developers to work together on a development database? Nope. Code won’t get trashed unless we behave like lunatics. If the worst happens, then it is still there. It is a database after all and we take scheduled backups of it just like any other database. What’s that? You really think your VCS backups are as reliable as database backups? I thought not. So why export the code from a database into a “database” that isn’t as good?

Ah, we don’t know who made what alteration to the database unless we use source control? Wrong again. All my development servers have server triggers that record all changes and save them to a table in master. It even contains the DDL, so I always know what changes were made, to which database, when and by whom. Almost always, I’ll know what code was there before because my trigger will have recorded it when the code changed previously. Don’t believe me? Here’s the trigger…

Ah. So we need to save the database build script in source control so that we can be sure that the application is at the same revision level as the application. I can understand the need to do that if there is only one application accessing the database. What if there are two or more? Do we save the database in two source control archives, simultaneously? I think not. What about the typical corporate systems which involve several applications accessing several databases? Into which of the applications’ source control archive do the databases get stored?

So you’re saying that the only way we can get a good builds is to ‘version’ the database, and the only way to do that is source control. We need to save the database changes along with the code changes so they can be built and deployed together. Firstly, we don’t necessarily have to version the database, but it is far more important to ‘version’ the individual interfaces with the applications that interact with it. By saying that we need to version the entire database, I think you’re really saying you want unfettered access to all the base tables of the database. This is fine for the database of your DVD collection, but how does that fit in with the security requirements of any large organization? Surely a better way is to ‘version’ the interface with the application. It isn’t hard to ‘attach a ‘version’ to a schema using extended properties. You don’t need source control to enable you to do this.

You’ve got to remember that SQL Server is a relational database, and so it is good at keeping track of data, even when the data is the source of the database that you’re developing. All the database objects such as tables, procedures and functions, and their source where relevant, are stored in a highly efficient relational way. We always know when they were created or last modified, what their dependencies are, and what attributes they have. A simple SQL statement will tell you a great deal about the object and its context within the database. Don’t fight it, use it!

So let’s sum up. By developing as a team on a development server, with a realistically-large data set and hardware, we don’t have to continually build or integrate the databases because they’re already there. We don’t lose what we’ve done because we back up, save our work, and audit every change on the development server. We can even have an automated process to lodge changes in a source control system at the end of every day so as to keep management happy. We can attach version numbers to our work, at schema or database level, script out, or script-in application interfaces to suit the progress of individual applications. We can generate migration scripts between database versions by using SQL Compare between snapshots. We’re happy, we’re working in a multi-user way and we don’t knock heads together. So why are database developers cast as the retrograde luddites of IT development?

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Tags: , , , ,


  • Rate
    [Total: 9    Average: 4.6/5]

Hugh is both a distinguished expert in object-oriented conmputer languages, and a DBA who has spent sixteen years working with financial applications in the UK and the Middle-East, and been a delegate on the international ANSI committee for the standardisation of Relational Theory. Hugh is well-known in the Database world. He lectures in Relational Theory at Chiltern University UK. He is the author of many Journal articles and has co-authored several books on UML, SOLID, DRY, Object Modelling techniques and relational theory, as well as a definitive criticism of the black-and-white films of Ingmar Bergman.

View all articles by Hugh Bin-Haad

  • Phil Factor

    Easier ways of getting some of the metadata schema changes
    Actually, Hugh, if you just want to see what your co-workers have done, and are doing when doing a shared-model development, there is a report in SSMS that will tell you what’s going on. Just right-click on the instance in the object-browser window, pick ‘reports’ from the context window, pick ‘standard reports’ from the next context window, and finally, from the standard reports, click ‘Schema change History’. You will then see a table, obtained from the default trace, showing who did what, to what, and when. As it is coming from the default trace, it is likely to go back a long way but I agree it doesn’t give you quite as much information as your trigger.

    Oh, just forgotten: you don’t use SSMS but use VIM instead.

  • Robert young

    Ah, Religion
    — use VIM instead.

    C’mon Phil, not everyone craves the arthritis and carpal tunnel pain that EMACS delivers. CUA is just as bad. Real editor widgets do give you the choice of at least those three, some even still have Brief.

    And Hugh seems to have abandoned his usual sense of humour with this post? It seems the lithium tabs have begun to work.

  • David

    Got who, what, when and where, but missing why
    Really interesting article. Using a trigger to record the database mods is a great idea, and there are a lot of great points here.

    In thinking about why I use source control, though, it seems to me there’s something missing in this process. Having a list of all the changes made to a database object is very useful, but it’s more useful if it includes notes about why the changes were made. Source control can be used to enforce checkin comments, and ideally make the connection to an issue tracker.