DDL Management - full360/sneaql GitHub Wiki

Over the lifetime of a database, you will need to make changes to the structure, by adding more objects, changing column attributes, etc. The scripts below give you an example of how SneaQL can be used to manage these changes.

Each block of code shown below correlates to a step file that is specified in sneaql.json:

[
  {"step_number" : 1, "step_file" : "initialize.sql"},
  {"step_number" : 2, "step_file" : "0.0.1.sql"},
  {"step_number" : 3, "step_file" : "0.0.2.sql"},
  {"step_number" : 4, "step_file" : "0.0.3.sql"},
]

The first step file, initialize.sql, creates the database objects necessary for tracking the current version of your database. Each time a patch version is successfully applied to your schema, the version number is logged into the db.version table:

/*-execute-*/
create schema if not exists db;

create table if not exists db.version
(
  version varchar(24)
);

/*-assign_result db_version-*/
select
  coalese(max(version),'0.0.0')
from
  db.version;

The db_version variable is will be set to '0.0.0' on the first run.

The step below, 0.0.1.sql, has a guard statement that exits the step if the file if the version number is higher than the version applied by the step. This prevents a step from being applied twice.

/*-exit_step_if db_version > '0.0.1'-*/

/*-execute-*/
create table if not exists a
(
  b integer
);

insert into db.version values('0.0.1');

After the table is created, the step success is logged.

The next file does the same thing for version 0.0.2, this time adding a new column.

/*-exit_step_if db_version > '0.0.2'-*/

/*-exit_step_if db_version > '0'-*/

/*-execute-*/
alter table a add column c integer;

insert into db.version values('0.0.2');

The third step demonstrates a more complex operation, where a new table is created with different data types, populated with the old table, and ultimately replacing the old table through a series of renames.

/*-exit_step_if db_version > '0.0.3'-*/

/*-execute-*/
create table a_new
(
  b bigint
  ,c float
);

insert into a_new select * from a;
alter table a rename to a_old;
alter table a_new rename to a;

insert into db.version values('0.0.3');

The approach shown above becomes a DDL deployment script manager, which you can use in your CI/CD workflow. If you manage your git tags correctly, you can provide the tag as high watermark for which version should be applied. If you created a tag for each version shown above, and passed in the 0.0.2 commit to an empty database, it would apply all steps up to 0.0.2.