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

Get all by stream (schema API)

The same query as get all, answered as a stream : rows go to the client as the database produces them, so a million rows cost the server a few kilobytes of memory, not the whole result.
Method GET
URL /api/schema/admin/mysql8/inventory/customers/stream
Body none
Query params the same as get all : find, skip, limit, sort, select, deep, getTotalCount, any column
Answer data : an array of rows, streamed
Databases MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona
Cached no : streams always come from the database
From code g.sys.db.getAllByStream
API id SCHEMA_GET_ALL_STREAM (groups, settings, hooks, WebSocket subscriptions)
The other family Get all by stream as a generated API

Generated (/api/gen) or schema (/api/schema) ?

Both families offer the same operations on the same URL pattern. The schema APIs read the schema of the table : values are converted to their types, validated, encrypted, defaulted and numbered before they reach the database. The generated APIs are schemaless : they pass your data as it is, so a number sent as "22" stays a string. Use the schema APIs for the tables your application writes to, and the generated APIs for quick reads and for tables without a schema.

The URL

/api/schema/admin/mysql8/inventory/customers/stream : replace admin with the user path of your account, mysql8 with your instance, inventory with the database and customers with the table. The examples use this table :

customer_id first_name last_name last_update pincode isActive
1 Bob lin 2022-11-14 04: 34: 58 382345 1
2 Alice Page 2022-10-15 02: 10: 40 382346 1
3 Mallory Brown 2022-09-13 03: 44: 05 382347 1
4 Eve Mathly 2022-11-12 01: 59: 33 382348 1
5 Eve Page 2022-11-12 01: 59: 33 382349 1

The call

GET /api/schema/admin/mysql8/inventory/customers?find={isActive:1}&sort=customer_id&select=customer_id,first_name

{ "data": [
    { "customer_id": 1, "first_name": "Bob" },
    { "customer_id": 2, "first_name": "Alice" }
], "success": true, "statusCode": 200, "meta": {} }
  • The envelope is the same as get all, but data comes first and its rows are written in chunks while the query runs. success, statusCode, meta and totalCount close the answer. Parse it as JSON when it is complete, or read it with a streaming JSON parser as it arrives.
  • If the database fails while rows were already sent, the answer still ends as valid JSON : ], "success": false, "statusCode": 500, "errors": [...] }. Read success at the end.

When to use it

  • Exports, reports, synchronisations, copies of a table : anything which reads many rows and would not fit a normal answer.
  • Every filter of get all works : find, sort, select, deep, skip, limit.
  • The answer is never cached, and the rows are never compressed in memory : x-am-data-source is always api.

Hooks on a stream

  • Pre hooks run before the query and can change it.
  • Post hooks run when the stream ends. They can not change what was sent, and g.res.output holds only the last chunk of rows, so do not use a post hook to reshape a stream : reshape in a custom API around g.sys.db.getAllByStream.

From your code

In a custom API the same call hands you every row as it arrives, without building the whole array :

1
2
3
4
5
6
7
8
let count = 0;
await g.sys.db.getAllByStream({
    instance: 'mysql8', database: 'inventory', collection: 'customers',
    queryParams: { find: { isActive: 1 } },
}, (row) => {
    count++; // one object at a time, in order
});
return { count };

Headers

Every call takes the request headers : the tokens, x-am-response-case, x-am-content-type-response (JSON, XML, YAML…), x-am-response-object-type: make_flat, x-am-cache-control, x-am-get-encrypted-data, x-am-internationalization, x-am-tenant-username, x-am-meta.

Errors

Code When
401 The token in x-am-authorization is missing or invalid.
403 No group of the API user grants this API of this table, or a field of the request.
404 The instance, database or table of the URL does not exist, or the table has no schema (use /api/gen).
400 The query or the body is wrong : the error messages say which key and why.