No N+1 queries
The ids of every row of a level are collected and read with a single $in query. Three levels cost three queries, whatever the number of rows.
Database APIs
Deep Populate
Get a row with its related rows in one request, even from other databases.
Add deep to a request and API Maker replaces the ids in the answer with the rows they point to, level after level. The ids of all rows are read together, with one query per level, whether the related table is in the same database or on another server.
See it live
Step through it, slow it down, or open the full canvas.
api-maker/features/deep-populateLive- Levels
- 0populated
- DB queries
- 0sent
- Databases
- 0types
- API calls
- 0from the app
Say what to populate, in one request. deep names the key to follow and where its rows live: another instance, database and type. Levels nest.
How it works
Say what to populate, in one request.
deep names the key to follow and where its rows live: another instance, database and type. Levels nest.
The first table is read as usual.
API Maker reads the cities from PostgreSQL. Each row holds a state_id: a key into Oracle.
Keys are collected, then read in one query.
The state ids of all rows go in a single $in query to Oracle. The rows come back keyed by id and replace the numbers.
Every level repeats it, in any database.
Inside each state, country_id is read from MySQL the same way: one query with $in, however many rows point to it.
One query per level, not per row.
Three levels cost three queries. Reading row by row would have cost seven here, and thousands on real data.
Filter and shape every level.
Each deep item takes find, select, sort, skip, limit and isMultiple. With relations in the schema, deep=state_id is enough.
What you get
Across databases
Cities in PostgreSQL, states in Oracle, countries in MySQL: each level can point to another instance and database type.
Shape every level
Each deep item takes find, select, sort, skip and limit, and isMultiple to get an array instead of one object.
Short with a schema
When the schema holds the relation, deep=state_id is enough. Without a schema, name the target with t_instance, t_db, t_col and t_key.
Parent to children too
Virtual fields in the schema bring the rows pointing to a row, like the states of a country, in chunks of 1000 by default.
Rules still apply
The field access of the API user is applied to populated rows as well, so hidden fields stay hidden.
An example
An order screen
An order page needs the customer, the city of the customer and the product of every order line. One query API call with deep on customer_id and on the lines returns all of it, instead of one request per customer and per product.
POST /api/gen/admin/postgresql/geo/cities/queryx-am-authorization: <API user token>{ "find": {}, "deep": [{ "s_key": "state_id", "t_instance": "oracle", "t_db": "inventory", "t_col": "states", "t_key": "id", "deep": [{ "s_key": "country_id", "t_instance": "mysql", "t_db": "inventory", "t_col": "countries", "t_key": "id", "select": "country_name" }] }]}GET /api/schema/admin/postgresql/geo/cities?deep=state_idWith schema APIs the table state_id points to comes from the schema of cities.
{ "success": true, "statusCode": 200, "data": [{ "id": 101, "city_name": "AHMEDABAD", "state_id": { "id": 201, "state_name": "GUJARAT", "country_id": { "id": 301, "country_name": "INDIA" } } }]}
Good to know
- deep works on get all, get by id, query and their streams, and on the answers of save, master save, update, replace and remove by id.
- skip and limit inside a deep item are applied after the rows of the level are read, because all ids are read at once.
- deep only reaches the instances of your own account.
Questions
How many database queries does deep make?
One per deep item and level. The ids of all the rows of a level are sent in one $in query.
Can I populate two fields at once?
Yes. deep is an array: add one item per field, each with its own target and its own nested deep.
Do I need a schema?
No. With generated APIs you name the target table in the deep item. With schema APIs the relations of the schema are used, and the target tables need a schema too.
Learn more
Documentation