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 Database → 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 The function main of any language, 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
import * as T from 'types';

async function main(g: T.IAMGlobal) {
    const meta = await g.sys.system.getTableMeta({ instance: 'mysql8', database: 'inventory', table: 'order_transactions' });
    const hasDescription = meta.some(column => column.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;
Add a column once
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
from am import G


async def main(g: G):
    meta = await g.sys.system.get_table_meta(instance='mysql8', database='inventory', table='order_transactions')
    has_description = any(column.name == 'description' for column in meta)

    if not has_description:
        await g.sys.system.execute_query(
            'mysql8',
            'ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL AFTER `qty`',
        )
    return {'hasDescription': has_description}
Add a column once
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<?php
function main(AmG $g)
{
    $meta = $g->sys->system->getTableMeta(instance: 'mysql8', database: 'inventory', table: 'order_transactions');
    $hasDescription = (bool) array_filter($meta, fn ($column) => $column->name === 'description');

    if (!$hasDescription) {
        $g->sys->system->executeQuery(
            'mysql8',
            'ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL AFTER `qty`',
        );
    }
    return ['hasDescription' => $hasDescription];
}
Add a column once
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
file static class Api
{
    public static async Task<object?> Main(G g)
    {
        var meta = await g.Sys.System.GetTableMeta(new GetTableMeta { Instance = "mysql8", Database = "inventory", Table = "order_transactions" });
        var hasDescription = meta.Any(column => column.Name == "description");

        if (!hasDescription)
        {
            await g.Sys.System.ExecuteQuery(new ExecuteQuery("mysql8",
                "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL AFTER `qty`"));
        }
        return new { hasDescription };
    }
}
Add a column once
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package code

import "apimaker/am"

func Main(g *am.G) (any, error) {
    meta, err := g.Sys.System.GetTableMeta(am.GetTableMeta{Instance: "mysql8", Database: "inventory", Table: "order_transactions"})
    if err != nil {
        return nil, err
    }
    hasDescription := false
    for _, column := range meta {
        hasDescription = hasDescription || column.Name == "description"
    }

    if !hasDescription {
        _, err := g.Sys.System.ExecuteQuery(am.ExecuteQuery{
            Instance: "mysql8",
            Query:    "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL AFTER `qty`",
        })
        if err != nil {
            return nil, err
        }
    }
    return am.M{"hasDescription": hasDescription}, nil
}
Add a column once
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
use am::{Errors, ExecuteQuery, G, GetTableMeta, Value, json};

pub fn main(g: &mut G) -> Result<Value, Errors> {
    let meta = g.sys.system.get_table_meta(GetTableMeta::default().instance("mysql8").database("inventory").table("order_transactions"))?;
    let has_description = meta.iter().any(|column| column.name == "description");

    if !has_description {
        g.sys.system.execute_query(ExecuteQuery::new(
            "mysql8",
            "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL AFTER `qty`",
        ))?;
    }
    Ok(json!({ "hasDescription": has_description }))
}
Add a column once
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
import am.ExecuteQuery;
import am.G;
import am.GetTableMeta;
import am.TableMetaField;
import java.util.List;
import java.util.Map;

class Api {
    static Object main(G g) {
        List<TableMetaField> meta = g.sys.system.getTableMeta(new GetTableMeta().instance("mysql8").database("inventory").table("order_transactions"));
        boolean hasDescription = meta.stream().anyMatch(column -> "description".equals(column.getName()));

        if (!hasDescription) {
            g.sys.system.executeQuery(new ExecuteQuery("mysql8",
                "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL AFTER `qty`"));
        }
        return Map.of("hasDescription", hasDescription);
    }
}
Add a column once
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
import am.ExecuteQuery
import am.G
import am.GetTableMeta

fun main(g: G): Any? {
    val meta = g.sys.system.getTableMeta(GetTableMeta().instance("mysql8").database("inventory").table("order_transactions"))
    val hasDescription = meta.any { it.name == "description" }

    if (!hasDescription) {
        g.sys.system.executeQuery(
            ExecuteQuery("mysql8", "ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL AFTER `qty`"),
        )
    }
    return mapOf("hasDescription" to hasDescription)
}
  • 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) : after a pull, after a deployment hook, with Execute script and on the API testing page alike. A script in another language than TypeScript runs in the sandbox of its language.
  • A script which throws stops with its error, shown by View error on its row ; the next scripts still run.

Recorded from the panel

What you do to tables, collections, indexes and databases in the panel can go into a migration script as you do it : every other environment then makes the same change when it deploys. No SQL to copy, nothing forgotten on staging.
Where The pill REC of the table designer (Create, Apply changes, Run), of Delete of a table or a database, of the form of a database, of the index forms. It says where the change goes : a migration script of the account, a New script you name (or the name it suggests, create_orders), or Don't record. The choice stays for the next changes, and a change recorded into a new script makes it the script of the next ones.
What The statements which ran and changed something, as they ran : a SELECT or a find of the editor of scripts is not recorded. A database made, changed or removed is kept as its design and written again for each environment. An index is kept in the words of create indexes.
Runs The script runs like any other : after a pull, after a deployment hook, or by hand. Each change runs once per environment, in its order : the changes an environment has already (the ones recorded there too) are skipped, so the list can grow and the script can run again and again. A pull which changes the list marks the script as not executed, so the new changes run on the next deploy.
Names Databases are named as your account names them : the database name masking of each environment finds its own database.

The script

A script the panel makes is TypeScript : the list of the changes between two markers, and one call which makes them. Each statement is written between backticks, as it ran : it reads as SQL, and the editor colours it as the SQL of its database.

create_orders : recorded in the panel
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import * as T from 'types';

/**
 * Database changes recorded in the admin panel (format 1) : "create_orders".
 * …
 */

// <am-recorded-changes>
const CHANGES: T.IRecordedChange[] = [
    {
        "id": "20261022-101502-a3f9c1",
        "operation": "CREATE_TABLE",
        "summary": "Create table orders : 3 columns",
        "instance": "mysql8",
        "database": "inventory",
        "table": "orders",
        "statements": [
            `CREATE TABLE \`orders\` (
                \`id\` INT NOT NULL AUTO_INCREMENT,
                \`status\` VARCHAR(20) NOT NULL DEFAULT 'NEW',
                \`total\` DECIMAL(10, 2) NULL,
                PRIMARY KEY (\`id\`)
            )`
        ],
        "recordedAt": "2026-10-22T10:15:02.123Z",
        "recordedBy": "[email protected]"
    },
    {
        "id": "20261022-101733-07be42",
        "operation": "ALTER_TABLE",
        "summary": "Change table orders : add note",
        "instance": "mysql8",
        "database": "inventory",
        "table": "orders",
        "statements": [
            `ALTER TABLE \`orders\` ADD COLUMN \`note\` TEXT NULL`
        ],
        "recordedAt": "2026-10-22T10:17:33.481Z",
        "recordedBy": "[email protected]"
    }
];
// </am-recorded-changes>

async function main(g: T.IAMGlobal) {
    // The changes this server has not made yet run now, in their order. One which fails throws : the script is then
    // not marked as executed, and its next run starts again from that change.
    const results = await g.sys.system.applyRecordedChanges({ changes: CHANGES });
    return results.map(result => `${result.status} : ${result.summary}`);
}
module.exports = main;
  • A change : id (the same in every environment), operation (CREATE_TABLE, ALTER_TABLE, DROP_TABLE, RUN_SCRIPT, CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE, CREATE_INDEX, DROP_INDEX), summary, instance (and tenant), database, table, statements, atomic (one transaction, on PostgreSQL and SQL Server), design and original for a database, index for an index.
  • Edit the list if you need to : it is JSON, read with comments and trailing commas, with each statement between backticks. Remove a change, add one of your own, or write your own code around the call, a backfill of the new column after it for example. The panel only rewrites what is between the markers.
  • A change recorded into a TypeScript script you wrote adds the list above its main and the call as the first line of main. A script in another language takes no recorded change : the pill says so.

A statement between backticks
Escapes A statement is a text of JavaScript between backticks : write \` for a backtick of the statement (the names of MySQL and MariaDB, \`orders\`), \\ for a backslash and \${ for ${. The panel writes them so ; the editor shows their backslash faint. ${…} with code in it is refused : a statement is text.
Indentation The lines after the first are indented with the code around them. Before a statement runs, API Maker takes away the indentation those lines share, so what runs is what was written in the panel. A statement can also start on the line after its backtick, and end with its backtick on a line of its own.
Before Scripts recorded before the backticks hold a statement over several lines as the list of its lines (["CREATE TABLE a (", " id INT", ")"]) : they read and run as they are, and the panel writes their list between backticks the next time it records into them.

In the editor

The Code tab of a script which holds recorded changes knows the list :

  • Each statement is coloured as the SQL of its database (MySQL and MariaDB, PostgreSQL and Oracle, SQL Server), told by how it quotes names (\`orders\`, "orders", [orders]), and the commands of MongoDB as JavaScript.
  • In a statement, the tables of the database of its change and the columns of the tables it names are offered as you type, with the words of SQL.
  • In the list, type change : New recorded change writes a change of your own statements with a new id (the instance and the database to choose). In the statements of a change, New statement adds one. A change written by hand runs like a recorded one : once on each environment, in its order. Keep its id as it is once another environment has it.
  • What became of each change on this server : a bar beside the change, a note after its id (✓ Made here, ◷ Waits to be made here, ✗ Failed here), the whole of it (the error of the database) on hover, and the count on the line of // <am-recorded-changes>.

What each environment did

applyRecordedChanges answers one result per change, and keeps it in the environment, never in Git :

Status
APPLIED Made by this run.
ALREADY_APPLIED Made before : recorded here, run before, or taken as made.
FAILED The database refused it : message says why. The call throws, so the script is not marked as executed and its next run starts again from that change. On MySQL, MariaDB and Oracle, which commit each statement by itself, the statements of the change which ran before the failure are not run again.
NOT_RUN A change before it failed : it runs next time.
PENDING dryRun: true : nothing runs, the change would.

On the Database Migration page, the red count beside a script opens its Recorded changes : each change with what became of it on this server (made here, waiting, failed with the error of the database, taken as made), its statements coloured as their SQL with a button to copy each one (to run it by hand on a server, then take the change as made there), and :

  • Take as made here : this server has it already (made by hand) : a run here skips it. Mark as executed of a script does the same for all of its changes.
  • Make again here : the next run makes it on this server again.
  • Take out : the servers which did not make it yet never will. What it made here stays.
  • Make N here now : runs the script on this server.

Good to know

  • An Oracle user is made with a password, which would go to Git with the script : it is not recorded, the pill says so.
  • A statement of the editor of scripts which names a database itself (USE, `shop`.`orders`) runs in that database everywhere : name the tables alone, each environment runs them in its own database.
  • The schema of a table follows it through Git, as every schema does : the script holds the changes of the database.

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.