Agile Databases

Any project following an Agile methodology will usually find itself releasing to production at least 15 - 20 times per year. Even if only half of these releases involve database changes, that's 10 changes to production databases so you need a good lean process to ensure you get a good paper trail but at the same time you don't want something that that will slow you just unduly down. So, some tips in this regard:

Tip 1: Introduce a DB Log table

Use a DB Log table to capture every script run, who ran it, when it was run, what ticket it was associated with etc. Here is an example DDL for such a table for PostGres:

create sequence db_log_id_seq;
create table db_log (id int8 not null DEFAULT nextval('db_log_id_seq'), created timestamp not null,  db_owner varchar(255), db_user varchar(255), project_version varchar(255), script_link varchar(255), jira varchar(255));

W.R.T. the table columns:

• id - primary key for table. 
• timestamp - the time the script was run. This is useful.  Believe me. 
• db_owner - the user who executed the script. 
• db_user - the user who wrote the script 
• project_version_number - the version of your application / project the script was generated in.
• scrip_link - a URL link to a source controlled version of the script 
• jira - a URL to the ticket associated with the script. 

Tip 2: All Scripts should be Transactional

For every script, make sure it happens within a transaction and within the transaction make sure there is an appropriate entry into the db log table. For example, here is a script which removes a column

BEGIN;
ALTER TABLE security.platform_session DROP COLUMN IF EXISTS ttl;
INSERT INTO db_log (
       db_owner, db_user, project_version, script_link, jira, created)
VALUES (
       current_user,
       'alexstaveley',
       '1.1.4',
       'http://ldntools/labs/cp/blob/master/platform/scripts/db/updates/1.1.4/CP-643.sql',
       'CP-643',
       current_timestamp
);
COMMIT;

Tip 3: Scripts should be Idempotent

Try to make the scripts idempotent. If you have 10 developers on a team, every now and again someone will run a script twice by accident. Your db_log will tell you this, but try to ensure that when accidents happen that there is no serious damage. This means you get a simple fail safe,  rather than some newbie freaking out.   In the above script, if it is run twice the answer will be the exact same.

Tip 4: Source Control your Schema

Source control a master DDL for the entire project. This is updated anytime the schema changes. Meaning you have update scripts and a complete master script containing the DDL for entire project. The master script is run at the beginning of every CI, meaning that:

• Your CI always starts with a clean database 
• If a developer forgets to upgrade the master script, the CI will fail and your team will quickly know the master script needs to be updated.
• When you have a master script it gives you two clear advantages: 
• New developers get up and running with a clean database very quickly
• It becomes very easy to provision new environments. Just run the master script! 

Tip 5: Be Dev Friendly

Make it easy for developers to generate the master script. Otherwise when the heat is on, it won't get done.

Tip 6: Upgrade and Revert

For every upgrade script write a corresponding revert script. Something unexpected happens in production, you gotta be able to reverse the truck back out!

BEGIN;

ALTER TABLE security.platform_session ADD COLUMN hard_ttl INT4;
UPDATE security.platform_session  SET hard_ttl = -1 WHERE hard_ttl IS NULL;
ALTER TABLE security.platform_session ALTER COLUMN hard_ttl SET NOT NULL;

ALTER TABLE security.platform_session ADD COLUMN ttl INT4;
UPDATE security.platform_session  SET ttl = -1 WHERE ttl IS NULL;
ALTER TABLE security.platform_session ALTER COLUMN ttl SET NOT NULL;


INSERT INTO db_log (
       db_owner, db_user, platform_version, script_link, jira, created)
       values (
       current_user,
       'alexstaveley',
       '1.1.4',
       'http://ldntools/labs/cp/blob/master/platform/scripts/db/reverts/1.1.4/revert-CP-463.sql',
       'CP-463',
       current_timestamp
    );

COMMIT;

Until the next time take care of yourselves.