Any of your databases
getTableMeta reads the columns of a table, executeQuery runs SQL or a MongoDB command, and g.sys.db moves data with the usual APIs.
Team & delivery
Database Migrations
Change the structure and data of every environment with TypeScript scripts that run once, in order.
A migration script is TypeScript with g: it reads table metadata, runs SQL or MongoDB commands on any of your databases, and moves data. Scripts go to Git with the rest of the project. After a deployment, the ones an environment has not run yet run one by one, in your order.
See it live
Step through it, slow it down, or open the full canvas.
api-maker/features/database-migrationsLive- Scripts
- 3in order
- Executed
- 2on PROD
- Columns
- 4order_transactions
- ALTER TABLE
- 0runs
A migration is a script in order. TypeScript run in the sandbox with g: read metadata, run SQL on any of your databases, import data. Each script has a number that sets its turn.
How it works
A migration is a script in order.
TypeScript run in the sandbox with g: read metadata, run SQL on any of your databases, import data. Each script has a number that sets its turn.
A deploy runs what is pending.
After a Git pull is committed, the scripts not executed yet on this account run one by one, in order. create-tables and seed-countries ran before, add-description is pending.
It checks, then changes only what is missing.
It reads the columns of order_transactions: description is missing, so it runs ALTER TABLE and adds it after qty, then the script is marked done. A script that fails is reported and runs again next time.
Run it again: nothing breaks.
The next pull finds nothing pending. Even run by hand, the script sees the column already there and changes nothing.
What you get
Deployed with Git
Scripts are part of the project in Git. Pull a branch on a server and its pending scripts run after the pull commits.
Once and in order
API Maker records which scripts ran on each account, so every script runs once, following its order number.
Failures come back
A script that fails is reported and not marked as done: it runs again the next time.
Run on demand
The admin panel shows the scripts still pending, and you can run the ones you pick.
In the sandbox
Scripts run in the sandbox like the rest of your code, with your packages. lodash and moment come with every sandbox.
An example
A new column in every environment
A feature needs a description column. The developer writes the script next to the API that uses it, and when QA, UAT and production pull the branch, each server adds the column before the new code serves its first request.
import * as T from 'types';import * as _ from 'lodash';async function main(g: T.IAMGlobal) { const columns = await g.sys.system.getTableMeta({ instance: 'mysql8', database: 'inventory', table: 'order_transactions', }); if (_.find(columns, { name: 'description' })) return 'already there'; await g.sys.system.executeQuery({ instance: 'mysql8', query: 'ALTER TABLE `inventory`.`order_transactions` ADD COLUMN `description` varchar(255) NULL AFTER `qty`', }); return 'added';}module.exports = main;
Good to know
- Make scripts safe to run twice: check before you change, as a failed script runs again.
- Scripts run on the database servers of the environment that pulls them. They do not undo themselves: write a new script to revert.
Questions
Which databases can a script change?
Any instance of the account: MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server or Oracle.
How long may a script run?
Up to 10 minutes each when run after a pull.
Learn more
Documentation