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 scripts that run once, in order.
A migration script is code with g, in the language you pick: 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. Code in your language, 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.
Code in your language, 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
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.
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';async function main(g: T.IAMGlobal) { const columns = await g.sys.system.getTableMeta({ instance: 'mysql8', database: 'inventory', table: 'order_transactions', }); if (columns.find(c => c.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;Watch it
On the real admin panel, with real data.
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?
Each script has its own timeout, in minutes, set next to its order.
Learn more
Documentation