Deep Populate: Join Data Across Six Databases in One API Call

Joins by hand

0:00 Deep populate swaps the ids in your rows for the rows they point to. One call, any depth, in one database or across six engines. An order is mostly ids : a customer, a coupon, an employee, its lines. To show it, teams write a join for every driver. Many end up with one query per row, the N plus one problem. When the customer lives in another database, no join can reach it, so a glue service merges the results by hand.

0:30 And each hop is one more permission check to forget. With API Maker, you name the column in deep. It reads each related table once, for all the rows, through that table's own API. We start in one database, then make one call across six engines. Then we shape every level, see how it runs, call it from code, and end with a few habits to keep.

One database

0:56 Open API Testing. Pick the instance mysql, the database sales, the table order_items, and Get all. In Query Params, find the lines of order three, and in deep, type order_id. Send. Each line now carries its whole order, in place of the number three. You typed only a column name. So where does order_id point? Open Instance API, the table order_items, and its schema. Table orders, column id. API Maker wrote these lines itself, from the MySQL foreign key, when it generated the schema.

1:36 A relation is declared once, in one place, and every call uses it. Now the other way : one order with all its lines. Pick orders, then Query for get data. In the body, two keys. Find picks order three. Deep names the field items, and isMultiple true makes it a list, with every line of the order. Send. Order three comes back with its four lines, read in one query.

2:05 Items is a virtual field : one schema line that points back from order_items. The next video is about them.

One call, six engines

2:13 Calm Home keeps its data in six databases, and the schema of orders links them. The customer is in PostgreSQL : the same lines, with an instance and a database. The coupon is in MariaDB, and the employee who handled the order, in Oracle. Its lines and its shipments are virtual fields, one in MySQL, one in SQL Server. Now one call for all of it. On orders, in Query for get data, the body asks for order three with five deep items : the customer and its city, the coupon, the employee, the lines with their products, and the shipments.

2:52 Each item selects only the columns it needs. Send. One answer, from six engines. The order, from MySQL. The customer and its city, from PostgreSQL. The coupon, from MariaDB. The employee, from Oracle, with its upper case names. The lines, from MySQL, their products from MongoDB, and the shipment, from SQL Server. Your code gets one JSON document. No second driver, no glue service, no merge loop.

Shape every level

3:25 Every deep item takes the keys of a query, as the top level does. Here, find, sort and limit ask for the four largest delivered orders. On the customer, find keeps gold customers only. On the lines, sort by price, descending, and limit one : the most expensive line of each order. Send. The gold customer is filled, the others keep their id, and each order shows one line. Skip works the same way, and the primary key of each row always stays.

3:59 A level inside a level has two spellings. Nest a deep inside the item, or list the paths : customer_id, then customer_id dot city_id, the city of that customer. In a query string, the same names, separated by commas. All three give the same answer. Pick one style for your team, and your code reads the same everywhere. A link the schema does not have? Name the target in the item.

4:28 Here, the support team, in Oracle. For each employee, the orders they handled, in MySQL. In a deep item, S stands for source, and T for target. s_key, the source key, is the employee's id. t_instance, t_db and t_col name the target table, orders, and t_key, its column that holds that id. isMultiple asks for a list, sorted by total, two each. Send. The id is replaced by the list.

4:58 To keep the id and add a field beside it, use a virtual field, as in the next video. A typo does not fail in silence. Ask for customer instead of customer_id, and the answer is a 400. Errors name the missing property, and data still holds the rows. It is the same error format as every API of API Maker, so a client handles it once.

How it runs

5:24 How does it run? One query reads the orders. Then, for each deep item, API Maker collects the ids of every row, and asks the target table once, with an IN list : customers, coupons, employees, lines and shipments, one query each. Then the next level, cities and products, one query each again. Eight queries, whether you ask for twenty orders or five hundred. Fetched row by row, the same twenty orders take one hundred seventy three queries : one per customer, per city, per coupon, per employee, per order for its lines and its shipments, and one per line for its product.

6:04 With deep, eight. Fewer round trips, and no loop in your code to get wrong. Each of these queries goes through the query API of its table, inside the server. So the rules of that table apply. The ids are converted to the types of the target schema. Your token is checked on every table it reaches, and the columns your group may not read are removed. A table with caching on answers from its cache.

6:31 And the hooks of the target do not run for these reads.

From code

6:35 From your own code, it is the same call. In Custom API, Order Overview, on API Testing. Its code calls g.sys.db.getAll on orders, with deep in its query params : the twenty largest delivered orders, with the same five items. No driver, no connection string, no join to write. Send. Twenty orders come back, filled from six engines. Open Index Maker, then DB Calls : one row per table, each called once.

7:08 Eight queries for twenty orders. The same deep exists in all eight languages, typed. In Python, a DeepItem. In Go, an am.DeepItem with SKey. C sharp, Java, Kotlin, PHP and Rust have the same class. In TypeScript, the editor knows every key of an item. One shape everywhere, so code written by an AI assistant follows the same standard, and the server checks every s_key against the schema.

Habits to keep

7:40 A few habits. Declare relations in the schema, so callers only name the column. Select only the columns you need, at every level. Give every list a sort and a limit. For one to many, use a virtual field, so the id stays. And to filter rows by a related table, use find and join, which works with deep.

Recap

8:02 Deep populate, in short. Name a column, get its row. Any depth, across six engines, in one call. Find, select, sort and limit on every level. One query per item, through each table's own API. The same call from code, in eight languages. Next, virtual fields : the one to many side of a relation. API Maker. Build faster, spend smarter, deliver better.