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