One Query Language for Six Databases: find, select, sort, aggregate

Intro

0:00 One query language for six databases : find, select, sort and limit, with the operators of MongoDB, on MySQL, Oracle and the others too. In this video, the dashboards of Calm Home ask their questions.

Reading data, the usual way

0:16 Reading from several databases usually means a dialect each : LIMIT here, FETCH NEXT there, a query builder per engine, or strings glued together, and big exports that load every row before the first byte goes out. With API Maker, one query, written in JSON, turned into the language of each engine by the server, and big reads streamed, a bunch of rows at a time. In this video : one query on three engines, count and distinct, an aggregate on MongoDB, and a million rows, streamed.

One query, three engines

0:49 API Testing, mysql, orders, Query for get data. The orders shipped or delivered, of three hundred or more : find, with in and gte. Three columns, sorted by total, the first three, and the total count. Three orders, and fifty eight in all. Now MongoDB, the products. The same keys : bedroom or lighting, a hundred or more. Three products, four in all. And Oracle, the employees : a salary of nine thousand or more.

1:28 The top three, eleven in all. Three engines, one language, one shape of answer. Behind the same query, each engine is asked in its own words : LIMIT on MySQL, FETCH NEXT on Oracle, a limit of its cursor on MongoDB. An operator that only MongoDB knows is refused by name on SQL, never ignored.

Every operator

1:51 Every operator of MongoDB works on the SQL engines too, with the meaning SQL gives it. On the orders of MySQL : coupon_code, null. SQL asks IS NULL : a hundred and six orders without a coupon. $expr compares two columns of the same row : sixty orders whose discount is larger than their shipping. On PostgreSQL, the customers : $nor, none of its conditions, and a regular expression that ignores case.

2:22 Jonas and Jack : a first name with a J, and not active. And on MongoDB, the products : $like takes the pattern of SQL, and $slice keeps one variant of each. Two lamps, one variant each. Behind each operator, the words of each engine. null is IS NULL on all of them, never equals null, which matches no row. $expr compares the columns themselves. A regular expression is REGEXP on MySQL, a tilde on PostgreSQL, REGEXP_LIKE on Oracle, and on SQL Server, which has none, the LIKE that says the same.

3:00 And under every operator, each value is first turned into the type of its column, by the schema.

Count and distinct

3:08 order_events, a million rows : Count, the delivered events. One hundred and forty two thousand, eight hundred and fifty seven. On the orders, Distinct, the field status : its six values.

Aggregate

3:25 On MongoDB, the aggregation pipeline too : Aggregate. Group the reviews by rating, sort them : sixty reviews of five stars, forty five of four, fifteen of three.

Streamed

3:37 For big reads, the stream APIs : get all by stream, and query by stream. A million order events : over a hundred megabytes, in about a second, on this laptop. The rows leave the database a thousand at a time, and go out while the next thousand is read.

Good to know

3:54 The same keys work as query params of get all, written in JSON5. getTotalCount counts every match, whatever the limit. A select that names a missing column is refused, with its name. And deep populate adds the rows of other tables to any read : video ten.

Built on standards

4:12 One query language, the one of MongoDB, on every engine. Each engine asked in its own dialect, by the server. So a client, or an AI assistant, learns one language and reads from all six databases.

Recap

4:27 To sum up. One query, in JSON, on every engine. Count, distinct and aggregate. A million rows, streamed. Next : the write APIs : save, update, replace and remove. API Maker. Build faster, spend smarter, deliver better.