Database Version Control – Best Practices

databaseversion control

Should database files(scripts etc.) be on source control?
If so, what is the best method to keep it and update it there?

Is there even a need for database files to be on source control since we can put it on a development server where everyone can use it and make changes to it if needed. But, then we can't get it back if someone messes it up.

What approach is best used for databases on source-control?

Best Answer

Yes. You should be able to rebuild any part of your system from source control including the database (and I'd also argue certain static data).

Assuming that you don't want to have a tool to do it, I'd suggest you want to have the following included:

  • Creation scripts for the basic table structures including schemas, users, tables, keys, defaults and so on.
  • Upgrade scripts (either altering the table structure or migrating data from a previous schema to the new schema)
  • Creation scripts for stored procedures, indexes, views, triggers (you don't need to worry about upgrade for these as you just overwrite what was there with the correct creation script)
  • Data creation scripts to get the system running (a single user, any static picklist data, that sort of thing)

All scripts should include the appropriate drop statements and be written so they can be run as any user (so including associated schema / owner prefixes if relevant).

The process for updating / tagging / branching should be exactly as the rest of the source code - there's little point in doing it if you can't associate a database version with an application version.

Incidentally, when you say people can just update the test server, I'm hoping you mean the development server. If developers are updating the test server on the fly then you're looking at a world of pain when it comes to working out what you need to release.

Related Topic