Database Migrations in TypeScript or Python, Once per Environment

Intro

0:00 Changes to your databases which run once in every environment, in order, in the language of your team. In this video, the database migrations of API Maker.

Migrations, the usual way

0:11 Database changes usually mean SQL scripts run by hand, from someone's laptop, forgotten on staging, or run twice on production, and nobody sure which ones ran where. With API Maker, ordered scripts live in git with the project, each runs once per environment, and every server remembers what it ran, in eight languages, with the same g as your APIs. In this video : the Database Migration page, a script in TypeScript, a script in Python, a test before the real run, the dialog of the scripts to run, changes recorded from the panel, and deploys.

The Database Migration page

0:48 Database Migration. One script is there already : am_index_maker, from the previous video. The green dot says it ran on this server. Index Maker owns it : it can not be removed.

A script in TypeScript

1:02 Customers will earn loyalty points : their table needs a column. Add New. The name, add_loyalty_points : it can not change later. A label for the list. Order two : scripts run from the lowest order. A timeout of five minutes. The code, in TypeScript : g.sys.system.executeQuery adds the column on PostgreSQL, if it is not there yet, so a second run changes nothing. It returns how many customers there are.

1:33 Save : listed, not executed here yet.

A script in Python

1:37 A second script, by someone who writes Python. The language picker : Python. recompute_ratings, its label, order three, five minutes. The Python template, and the change : the rating of each product becomes the average of its reviews. One aggregate on MongoDB, then one update per product. Save.

Test, then run

1:59 Before the real run, a test. Each script has Test API : API Testing, category Database Migration Scripts. Send : thirty six products, thirty three ratings changed. Again : zero. Written to run twice without harm. And a test run does not count as executed. Now load any page : the panel lists the scripts which have not run on this server yet, from the lowest order. The same dialog opens on every environment a pull brings new scripts to.

2:31 Tick both, Execute : they run in their order, and the page reloads. Database Migration : both executed here. In API Testing : postgres, crm, customers, Get all. Every customer has loyalty points, zero. The ratings were changed by the test already, so the real run found nothing to change.

Recorded from the panel

2:58 A script can also write itself, while you change your tables in the panel. Calm Home starts referrals : postgres, crm. The tables card says changes are not recorded yet. Design a new table : referrals, an email, and the times it was made and changed. The pill of the footer says where this change goes : a new script, add_referrals, with a label. Done. Create table : the table is made here, and the change is recorded into add_referrals.

3:31 Its schema can wait. The tables card says the next changes go there too. Now, Database Migration. add_referrals, with its count : one change, made here, where it was designed. It holds the statement which ran, as it ran. add_referrals is code, like any script. Between two markers, the list of changes. Each statement sits between backticks, coloured as SQL ; typed in, it offers the tables of crm and their columns.

4:02 Beside each change, what became of it on this server : made here, and when. The script is a list of changes, and one call : g.sys.system.applyRecordedChanges. On every server which deploys the project, each change runs once, in its order. What a server made already is skipped, so the list can grow. The changes name each database as your account names it : on every server, database name masking finds the real one.

4:32 A change which fails stops the ones after it, and the next run starts again from it.

Deploys

4:38 The scripts live in git with the project, with their order. What ran is kept by each server, never in git. A deployment hook pulls the branch, then runs what has not run there yet, in order, and answers with the migration errors. A script which fails is not marked as executed : the next deploy runs it again.

Good to know

4:59 Eight languages, with the same g as your custom APIs : new in the Rust edition. Mark as executed records a script without running it, for a server which has the change already. A column added by a script comes back in full answers ; add it to the schema of the table so a select can name it. And a name is for good : a new script with the name of a deleted one counts as executed already.

Built on standards

5:25 Every change to a database is a reviewed script in git, with an order. Each environment runs it once, and knows it did. So a change written by an AI assistant goes through the same review and the same order, and is never run by hand on production.

Recap

5:41 To sum up. Ordered scripts, in git. Once per environment, remembered by each server. In TypeScript, Python, and six more languages. Tested first, written to run twice. Changes of the panel, recorded for every server. Run by the dialog, or by every deploy. Next : multi-tenant APIs. API Maker. Build faster, spend smarter, deliver better.