Category: MySQL

Deploying MySQL updates

At some point every developer has to address the elephant in the project, the database.  MySQL/MariaDB is the preferred database in the PHP world.  It is the ‘M’ in LAMP.  But most developers only grudgingly learn enough about MySQL to make it work.  Even fewer become proficient at it.  Less still master it.  In this post we will delve into some techniques for maintaining and deploying MySQL updates.

Then a Miracle Occurs Pic

Then a Miracle Occurs

I have seen many good CI/CD implementations, with good testing and code deployment strategies.  In these same good CI/CD implementations I have seen a “then a miracle occurs” approach to the database.

Deploying MySQL updates Rules

Lets start creating a database deployment strategy by setting up some simple base rules:

  1. All script must be runnable/re-runnable without damaging the current schema or data.
  2. All script files names must start with 5 numeric digits plus a descriptive name.
  3. All currently active script must live in a well-known directory like “sql”
  4. After script have been deployed for awhile they should move to an “old” directory.
  5. Optional script, like test data, should live in an “optional” directory.

These rules will help take some of the “fear” out of maintaining the the database.

Rule 1: Re-runnable SQL scripts

Starting with rule number 1 we need to write our sql scripts with a particular style.

Creating Tables

Our create tables statements should look something like:

Note the “IF NOT EXISTS” creates the new table only if the tables does not yet exists.

Inserting Rows

If the table has a primary key you can insert row in your script like:

Inserts a new value but ignores errors if the primary key already exists.  Variation include using “WHERE” clauses and using the “UPDATE” MySQL statement.

Altering Tables

Several techniques exists, but this one is a good example of the concept:

Variations include altering column sizes change changing column types.

 Triggers

Re-Creating triggers that already exists should be safe.

Going Further

These techniques show the basic of re-runnable MySQL scripts.  By combining these techniques you should be able to write scripts that can be run and re-run safely allowing anyone to run these scripts at will without worry of damaging the existing database.

RULE 2: MySQL script file names

When sorted we want our script file to list in the order they should run.  To achieve this we will left pad our filename with numeric digits.

RULE 3: SQL Directory

Deploying MySQL Updates Directory Structure

Deploying MySQL Updates Directory Structure

The “sql” directory is where to put the MySQL script files.  This is the well-known location so all developers know where to look for the SQL script files.

RULE 4: The “Old” Directory

The “Old” directory is for SQL scripts that are no longer needed.  This is also a well-know location so all the team developers know where the old files are.

RULE 5: The “Optional” Directory

The “Optional” directory is for SQL script that load test data.  Again, all the team developers should know where this directory is.

Automation

The shell scripts updatedb.sh and updatedb.ps1 are Bash and Windows PowerShell scripts for running all the MySQL scrips in the directory in order.  This script can be used in the Continuous Delivery process to deploy the scrips. As any script can be run at any time without worry, running the updatedb script will not damage the target database.

Conclusion of Deploying MySQL updates

This general ideas in this post give you methods to maintain your SQL alongside your code, leveraging your Git repository to maintain a history of database changes over time.  This also lets other developers working on the project to setup their own instances of the database.