In a linkage of biblical proportions, Log Buffer #83 pointed me to Tom Kyte’s reiteration which pointed me to Coding Horror’s rant about source controlling the database schema.

Now, for starters, I agree with Tom’s sarcasm and Coding Horror’s rant — the database schema really should be source controlled in the same place as the application code, because otherwise how do you know what changes happened when, and which version of the code goes with which version of the schema.

The problem I have is this — being a purist (and by the way, *not* a programmer so there could very well be concepts I’m missing), I really want to source control the DDL/GRANT statements and whatever that are applied to the production database, because that way anyone can see exactly what was run, and I can do a schema backup and compare to the source controlled version.

But then you have the problem where you have the old schema + the code *to be run* and then you have the new schema, and what do you do with that file of code to be run? It’s now old, but you don’t want to delete it.

If the answer is “don’t source control the change statements, just the differences” then you’re requiring folks to either figure out how to get from point A to point B, or start with sample data only.

Plus, sometimes the DDL/GRANT statements are complex, or there are a lot of them to be run in a particular order, such that I *want* to source control them for backup and revision’s sake — even though SQL is declarative, there are still a few ways to skin each cat. How do I resolve that? (a separate and/or private repository?)

And if that all is the case, what are the security ramifications of having the schema, the code, and the data all in one place (the repository)? I’ve worked at places that have *real* 3-point authentication for a reversible hash — a user-supplied password, that wasn’t anywhere in the code (as far as I know it was passed mouth-to-mouth and not even in documentation); the encrypted data itself; and the code with the encryption/decryption algorithms. Sure, in the repository you might have test scripts, but where and how do you back up the repository? In the same manner as the data?

I guess folks have to be careful about any real data they backup, making sure it’s in separate places that their repository is backed up?