Software Development

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.

Reference: Agile Databases from our JCG partner Alex Staveley at the Dublin’s Tech Blog blog.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button