Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Database migration scripts

A migration script changes the structure or the data of your databases : add a column, backfill a value, create an index. It is code of the account, kept in Git, and API Maker runs each script once per environment, in order.
Page Utility → Database Migration : name, label, order, timeout in minutes, active, and the code.
Runs After a Git pull and after a deployment hook : the active scripts not yet executed on this environment, by order. Or by hand : Execute script, Execute Selected Migration.
Remembers The names of the scripts executed, per environment. Mark as executed skips one without running it ; a script already run shows Script executed.
Code async function main(g: T.IAMGlobal) with the same g as a custom API. Test it on the API testing page.

A script

Add a column once
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
import * as T from 'types';
import * as _ from 'lodash';

async function main(g: T.IAMGlobal) {
    const meta = await g.sys.system.getTableMeta({ instance: 'mysql8', database: 'inventory', table: 'order_transactions' });
    const hasDescription = !!_.find(meta, { name: 'description' });

    if (!hasDescription) {
        await g.sys.system.executeQuery({
            instance: 'mysql8',
            query: 'ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL AFTER `qty`',
        });
    }
    return { hasDescription };
}
module.exports = main;
  • Get table meta tells what is there, execute plain query runs any SQL or MongoDB command, create indexes adds indexes the same way on every database. Data moves with g.sys.db.
  • Write scripts which can run twice without harm, like the one above : a script which fails half way is run again after the fix.

Order and timeout

  • order sorts the scripts ; the pull runs them from the lowest. Use it for a script which needs the column another one adds.
  • Timeout in minutes stops a script which runs too long (10 when empty).
  • A script which throws stops with its error, shown by View error on its row ; the next scripts still run.

The Index Maker script

  • am_index_maker is managed by Index Maker : it holds the indexes decided by the automatic mode, the suggestions and the index form as a JSON list, runs on every deploy, and can not be deleted while Index Maker uses it. Edit the code outside the marked region as you like.