In a dev team, often times you manage database release scripts via "release***.SQL" files that are built up by various team members and saved in version control systems such as SVN or GIT. One problem is that when developer A makes some data or schema/design changes to the database, developer B & C don't necessarily know if it will cause duplicate records, or with schema changes it can cause errors if the scripts are run more than once. Here's a few concrete examples:

  1. For a Users table, developer A writes a sql script to insert some new records. Developer C updates to HEAD in SVN and runs the scripts from developer A. A few days later, Developer B writes some additional scripts to insert other new records. Developer C updates SVN again and wants to make sure his database is up-to-date, but he's smart and doesn't want to mess up anything with duplicate records, so he asks Developer A and B which scripts have already been run, etc. Developer A and B can't remember exactly, so Developer C does some SVN diffs and confirms which records have already been written to his local copy of the database. Now he knows he only has to run the new scripts from Developer B, and can skip the ones from Developer A.
  2. Developer A creates some new tables. Developer C runs the scripts on his local database. Developer B creates more new tables. When Developer C runs the scripts again, he gets errors about a database table already existing. He has to investigate a little to see what the error was all about, and if it's really a problem, or just an expected side-effect.

Yuck! That's too much of a headache for team members to have to worry about. So, let's make some rules about release scripts for the database:

  1. There should never be errors. If there's an error, then there was a problem in the script and someone should fix that error. If not, then we should be able to safely assume the database is properly updated.
  2. You should be able to always run all the scripts in a release file without causing errors or duplicate rows of data. Write your scripts so that every time the scripts are updated, the next developer can run the entire file safely. This takes extra work up-front, but saves a lot of confusion and headaches over the long term.

For creating new tables, MySQL provides native syntax: CREATE TABLE IF NOT EXISTS `Employees` (...

For making sure you only add a column once, or only change a column name once, the code below includes stored procedures to handle it: AddColumnUnlessExists () and ChangeColumnUnlessExists().

For making sure you only add a row of data once, MySQL also provides some native, though verbose, syntax. Here's the SQL to make it happen.

Note that there's also version control tools for databases, but that may or may not work for your team and I am not familiar with those tools.... yet.