# Database Migration Scripts > Migration scripts in API Maker - code in TypeScript, Python, PHP, C#, Go, Rust, Java or Kotlin with the same g as a custom API, run once per environment in order after a Git pull or a deployment hook, or by hand from the Database Migration page, with the Index Maker script among them, and the changes of tables recorded from the admin panel. Source: https://docs.apimaker.dev/v4/docs/features/database-migration.html 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](https://docs.apimaker.dev/v4/docs/Git/git.html#deployment-hooks) : 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](https://docs.apimaker.dev/v4/docs/languages/index.html), with the same `g` as a custom API. Test it on the [API testing page](https://docs.apimaker.dev/v4/docs/features/developer-tools.html#api-testing-page). | ## A script **TypeScript** **Add a column once** ```typescript 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; ``` **Python** **Add a column once** ```python 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} ``` **PHP** **Add a column once** ```php 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]; } ``` **C#** **Add a column once** ```csharp file static class Api { public static async Task 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 }; } } ``` **Go** **Add a column once** ```go 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 } ``` **Rust** **Add a column once** ```rust use am::{Errors, ExecuteQuery, G, GetTableMeta, Value, json}; pub fn main(g: &mut G) -> Result { 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 })) } ``` **Java** **Add a column once** ```java 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 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); } } ``` **Kotlin** **Add a column once** ```kotlin 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](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-get-table-meta-api.html) tells what is there, [execute plain query](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html) runs any SQL or MongoDB command, [create indexes](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-create-indexes-api.html) 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](https://docs.apimaker.dev/v4/docs/schema/table-designer.html) (**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](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-create-indexes-api.html). | | 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](https://docs.apimaker.dev/v4/docs/features/team-work.html#mask-database-names) 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** ```typescript import * as T from 'types'; /** * Database changes recorded in the admin panel (format 1) : "create_orders". * … */ // 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": "admin@admin.com" }, { "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": "admin@admin.com" } ]; // 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 `// `. ### 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](https://docs.apimaker.dev/v4/docs/schema/schema.html) 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](https://docs.apimaker.dev/v4/extensions/index_maker/introduction.html) : 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. ## Related - [Git integration](https://docs.apimaker.dev/v4/docs/Git/git.html) · [Execute plain query](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html) · [Create indexes](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-create-indexes-api.html) · [Table designer](https://docs.apimaker.dev/v4/docs/schema/table-designer.html)