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. |
