API Maker

The backend engine for the AI era

Chapters

What you will learn

  • A dot in find follows the relation
  • One database, two engines, three engines
  • The innermost table first, one query per table
  • Three queries, on screen
  • Counts and update many too
  • Where it works, and its rules
In the docsfind
Transcript651 words · click a time to play from there

Intro

Sometimes you want to filter rows by a linked table, even when that table is in another database. In API Maker, a dot in find follows the relation. In this video, we use find and join across one, two, and three databases.

Filtering by a related table

Filtering by a linked table is usually hard. You need a JOIN, and SQL cannot join two databases. So code loads whole tables, and filters them in memory. Or a job copies data from one database to another. And every new filter is one more endpoint. With API Maker, a dot in find follows the relation of the schema, across databases, with one query for each table, in reads, counts, and updates.

In this video: one database, two databases, two steps away, three databases, how it runs, and counts and updates.

One database

We start with order_items, in MySQL. In find, we ask for order_id dot total, one thousand five hundred or more. order_id points to orders, so the dot follows it there. Send. We get the lines of the big orders, filtered by a column of another table.

Two engines

Now the orders, in MySQL. We want the orders of gold customers, so we use customer_id dot loyalty_tier. The customers are in PostgreSQL. A deep shows each customer too. Send. We get the three biggest orders of gold customers, with their customer. One filter, across two databases.

Two hops

Now two steps away: customer_id dot city_id dot name. It goes from orders, to customers, to cities. Send. We get the orders of customers who live in Pune.

Three engines

Now three databases. On shipments, in SQL Server, we use order_id dot handled_by dot FIRST_NAME. It goes from shipments, to orders in MySQL, to employees in Oracle. Send. We get the nine shipments of the orders that Elena handled.

How it runs

How does it run? The table at the end of the path goes first. Oracle finds Elena, one row. MySQL finds the orders with her ID in handled_by. And SQL Server finds the shipments of those orders, with an IN list. One query for each table, whatever the number of rows. You can use the same filter from code, in a custom API. Here is Shipments by Employee. It calls g.sys.db.query, with the same dotted key.

Send. Open Index Maker, then DB Calls. EMPLOYEES gives one row, orders gives fifteen, and shipments gives nine. Three queries, in that order.

Counts and updates

It works for more than reads. We use Count on orders, with the same filter. Send. There are eighteen orders of gold customers. Now the support tickets, with Update many. The NORMAL tickets of gold customers become HIGH. Send. Six rows are updated. We read them back, and now they are HIGH.

Where it works

It works in every API that takes find: get all, query, the streams, count, distinct, update many, remove by query, and the first match of an aggregate. It works with deep too. You can filter on one relation, and fill another. And for rare cases, the find_join array of the REST APIs names the join itself.

Rules

The relation comes from the schema, on the schema APIs. On the generated APIs, a dotted key goes to the database as it is. A virtual field cannot be used. Filter the table that holds the link instead. Each step is a real call of the API of the linked table. So the token of the caller is checked there, and its hooks run.

Built on standards

There is one way to filter by a relation, on every database. No data is copied between databases, so nothing gets old. And the access rules of every table hold on every step.

Recap

To sum up. A dot in find follows the relation. It works in one database, two, or three. The last table of the path goes first, with one query for each table. It works in counts and updates too. Next: request headers, to shape every answer. API Maker. Build faster, spend smarter, deliver better.