Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Master save

Saves a whole tree of related rows in one call : an order with its customer and its items, a city with its state and country. Each row is inserted, or updated when its primary key is already there.

In short

  • POST /api/schema/<user-path>/<instance>/<database>/<table>/master-save
  • A row with the key of a row that is there updates that row. Any other row is inserted.
  • The relations of the schemas say which table each nested object goes to.
  • If one row fails, every row the call wrote is taken back.
Method POST
URL /api/schema/admin/mysql8/inventory/customers/master-save
Body one object, or a list of objects, with nested related objects
Query params select and deep shape the answer. returnData=false : the count only.
Answer 201. data : the saved rows, with the ids of their related rows. With returnData=false : { savedRowsCount }.
Databases MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona
Cached no. It clears the cached answers of the table.
From code g.sys.db.masterSave
API id SCHEMA_MASTER_SAVE
Without a schema /api/gen/admin/mysql8/inventory/customers/master-save : below
Master Save: Write a Tree of Rows Across Databases in One Call9 chapters and the transcript

Insert or update

POST /api/schema/admin/mysql8/inventory/customers/master-save
Body : no primary key, so the row is inserted
{ "first_name": "Bob", "last_name": "Lin" }
Body : customer 27 is there, so only its pincode changes
{ "customer_id": 27, "pincode": 382330 }
  • The same rule holds at every level of the tree.
  • An update changes only the columns you send.
  • A key that matches no row is inserted, with that key.
  • A list in the body saves each object, and the answer is a list.

A tree of rows

Each nested object needs a relation in the schema of its column (table, column, and database or instance when they differ). The nested row is saved first, in its own table. Then its id goes into the column of the parent.

Body : three tables in one call
{
    "city_name": "Wembley",
    "state_id": {
        "state_name": "London",
        "country_id": { "country_name": "UK" }
    }
}
Answer
{
    "success": true,
    "statusCode": 201,
    "data": { "id": 107, "city_name": "Wembley", "state_id": 205 }
}

The country is saved first, and its id goes into the state. Then the state is saved, and its id goes into the city. The tables can be in other databases and instances : the relation says where.

Master save : the deepest object first, its id goes up into its parent the body : one call { "city_name": "Wembley",   "state_id": {     "state_name": "London",     "country_id": {       "country_name": "UK" } } } saved in this order 1 · countries UK → id 31 2 · states London, country 31 → id 205 3 · cities Wembley, state 205 → id 107 A failure on the way reverts every row the call wrote : no state and no country are left without their city. An object with its primary key is updated, one without is inserted, at every level. The relations of the schemas say where each object goes : other tables, other databases, other instances.

To get the related rows back in the answer, add deep : ?deep=[{s_key:'state_id',deep:[{s_key:'country_id'}]}].

A list of child rows : virtual fields

A virtual field (isVirtualField, with s_columnVirtualLinker and t_columnVirtualLinker) takes a list. Each item is saved in the other table, with the key of the parent.

Body : an order and its items
{
    "order_no": 1001,
    "customer_id": 1,
    "items": [
        { "product_id": "66fa1c9a9b1e4a0012a3c002", "qty": 2 },
        { "id": 55, "qty": 3 }
    ]
}

The first item is inserted. The second has its primary key, so it is updated.

When something fails

  • Every row the call wrote is taken back when a later row fails. A wrong column in the country of the tree above leaves no city and no state behind.
  • The schema checks each row as save single or multiple does. The errors name the column. A unique error also names the row with dataIndex.

Only the count : returnData=false

POST .../master-save?returnData=false answers { "savedRowsCount": 2 } : how many rows of the body were saved, nested rows not counted. The nested rows are saved and linked as always.

Optimistic concurrency control

A table whose schema has a version column (isConcurrencyControlField) needs the version in every update. An old version is refused with 400. See Optimistic concurrency control.

From your code

g.sys.db.masterSave makes the same call from custom APIs, hooks, events, schedulers and test cases. Pass returnData: false next to saveData for the count only.

Without a schema : /api/gen

Every table also answers this operation under /api/gen, with or without a schema :
URL /api/gen/admin/mysql8/inventory/customers/master-save
API id GEN_MASTER_SAVE
From code g.sys.db.gen.masterSaveGen

Without a schema, nothing says which rows belong to other tables. So master save works like save single or multiple : every row is inserted as you send it, nothing is updated. On MongoDB, a nested object stays an embedded document.

Headers and errors

This operation takes the request headers of every API. It answers the envelope, with the status codes and the messages of every API.

Related : All APIs · Table schema · Pre hooks · Post hooks · API group permission