Do you use source control for your database items? [closed]

I feel that my shop has a hole because we don't have a solid process in place for versioning our database schema changes. We do a lot of backups so we're more or less covered, but it's bad practice to rely on your last line of defense in this way.

Surprisingly, this seems to be a common thread. Many shops I have spoken to ignore this issue because their databases don't change often, and they basically just try to be meticulous.

However, I know how that story goes. It's only a matter of time before things line up just wrong and something goes missing.

Are there any best practices for this? What are some strategies that have worked for you?

Must read Get your database under version control. Check the series of posts by K. Scott Allen.

When it comes to version control, the database is often a second or even third-class citizen. From what I've seen, teams that would never think of writing code without version control in a million years-- and rightly so-- can somehow be completely oblivious to the need for version control around the critical databases their applications rely on. I don't know how you can call yourself a software engineer and maintain a straight face when your database isn't under exactly the same rigorous level of source control as the rest of your code. Don't let this happen to you. Get your database under version control.

The databases themselves? No

The scripts that create them, including static data inserts, stored procedures and the like; of course. They're text files, they are included in the project and are checked in and out like everything else.

Of course in an ideal world your database management tool would do this; but you just have to be disciplined about it.

I absolutely love Rails ActiveRecord migrations. It abstracts the DML to ruby script which can then be easily version'd in your source repository.

However, with a bit of work, you could do the same thing. Any DDL changes (ALTER TABLE, etc.) can be stored in text files. Keep a numbering system (or a date stamp) for the file names, and apply them in sequence.

Rails also has a 'version' table in the DB that keeps track of the last applied migration. You can do the same easily.

Check out LiquiBase for managing database changes using source control.

You should never just log in and start entering "ALTER TABLE" commands to change a production database. The project I'm on has database on every customer site, and so every change to the database is made in two places, a dump file that is used to create a new database on a new customer site, and an update file that is run on every update which checks your current database version number against the highest number in the file, and updates your database in place. So for instance, the last couple of updates:

if [ $VERSION \< '8.0.108' ] ; then
  psql -U cosuser $dbName << EOF8.0.108
    BEGIN TRANSACTION;
    --
    -- Remove foreign key that shouldn't have been there.
    -- PCR:35665
    --
    ALTER TABLE     migratorjobitems
    DROP CONSTRAINT migratorjobitems_destcmaid_fkey;
    -- 
    -- Increment the version
    UPDATE          sys_info
    SET             value = '8.0.108'
    WHERE           key = 'DB VERSION';
    END TRANSACTION;
EOF8.0.108
fi

if [ $VERSION \< '8.0.109' ] ; then
  psql -U cosuser $dbName << EOF8.0.109
    BEGIN TRANSACTION;
    --
    -- I missed a couple of cases when I changed the legacy playlist
    -- from reporting showplaylistidnum to playlistidnum
    --
    ALTER TABLE     featureidrequestkdcs
    DROP CONSTRAINT featureidrequestkdcs_cosfeatureid_fkey;
    ALTER TABLE     featureidrequestkdcs
    ADD CONSTRAINT  featureidrequestkdcs_cosfeatureid_fkey
    FOREIGN KEY     (cosfeatureid)
    REFERENCES      playlist(playlistidnum)
    ON DELETE       CASCADE;
    --
    ALTER TABLE     ticket_system_ids
    DROP CONSTRAINT ticket_system_ids_showplaylistidnum_fkey;
    ALTER TABLE     ticket_system_ids
    RENAME          showplaylistidnum
    TO              playlistidnum;
    ALTER TABLE     ticket_system_ids
    ADD CONSTRAINT  ticket_system_ids_playlistidnum_fkey
    FOREIGN KEY     (playlistidnum)
    REFERENCES      playlist(playlistidnum)
    ON DELETE       CASCADE;
    -- 
    -- Increment the version
    UPDATE          sys_info
    SET             value = '8.0.109'
    WHERE           key = 'DB VERSION';
    END TRANSACTION;
EOF8.0.109
fi

I'm sure there is a better way to do this, but it's worked for me so far.