# Database Migration Scripts

> Migration scripts in API Maker - TypeScript 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.

Source: https://docs.apimaker.dev/v1/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 | `Utility → 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/v1/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 | `async function main(g: T.IAMGlobal)` with the same `g` as a custom API. Test it on the [API testing page](https://docs.apimaker.dev/v1/docs/features/developer-tools.html#api-testing-page). |

## A script

**Add a column once**

```typescript
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](https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-get-table-meta-api.html) tells what is there, [execute plain query](https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html) runs any SQL or MongoDB command, [create indexes](https://docs.apimaker.dev/v1/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).
- 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](https://docs.apimaker.dev/v1/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/v1/docs/Git/git.html) · [Execute plain query](https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html) · [Create indexes](https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-create-indexes-api.html)