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

Get all by stream

The query of get all, answered as a stream. The rows go to the client as the database sends them. So a million rows cost the server a few kilobytes of memory, not the whole list.

In short

  • GET /api/schema/<user-path>/<instance>/<database>/<table>/stream
  • The same query params as get all.
  • Use it for exports, reports and copies of a table.
  • Never cached. Pre hooks run, post hooks do not.
Method GET
URL /api/schema/admin/mysql8/inventory/customers/stream
Body none
Query params the ones of get all : find, a column, skip, limit, sort, select, deep, getTotalCount
Answer data : a list of rows, sent as they come
Databases MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona
Cached no : a stream always reads the database
From code g.sys.db.getAllByStream
API id SCHEMA_GET_ALL_STREAM
Without a schema /api/gen/admin/mysql8/inventory/customers/stream : below

The call

The examples read the table of get all.

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

{ "data": [
    { "customer_id": 1, "first_name": "Bob" },
    { "customer_id": 2, "first_name": "Alice" }
], "success": true, "statusCode": 200 }
  • data comes first. Its rows go out in bunches while the query runs.
  • success, statusCode and totalCount close the answer. Read the whole answer as JSON, or read it as it comes with a streaming JSON parser.
  • meta closes it too, but only in the admin panel, with x-am-meta: true. See the envelope.
  • If the database fails after some rows went out, the answer still ends as valid JSON : ], "success": false, "statusCode": 500, "errors": [...] }. So always read success at the end.

Good to know

  • Hooks. Pre hooks run before the query, and can change it. Post hooks do not run on a stream. To change the rows, read them in a custom API with g.sys.db.getAllByStream, as below.
  • Columns. What the groups of the caller may not read is taken out of every bunch, as on get all.
  • Headers. The answer is JSON and is never compressed, whatever the headers say. x-am-data-source is always api.
  • The caller leaves. When the client closes the connection, the query stops at once.

From your code

In a custom API, the same call gives you each row as it comes. You never hold the whole list :

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 };

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
count = 0


def take(row):
    nonlocal count
    count += 1  # one object at a time, in order


await g.sys.db.get_all_by_stream(GetAll('mysql8', 'inventory', 'customers', find={'isActive': 1}), take)
return {'count': count}

1
2
3
4
5
$count = 0;
$g->sys->db->getAllByStream(new GetAll('mysql8', 'inventory', 'customers', find: ['isActive' => 1]), function ($row) use (&$count) {
    $count++; // one object at a time, in order
});
return ['count' => $count];

1
2
3
4
5
6
var count = 0;
await g.Sys.Db.GetAllByStream(new GetAll("mysql8", "inventory", "customers") { Find = new JsonObject { ["isActive"] = 1 } }, row =>
{
    count++; // one object at a time, in order
});
return new { count };

1
2
3
4
5
6
7
8
9
count := 0
err := g.Sys.Db.GetAllByStream(am.GetAll{Instance: "mysql8", Database: "inventory", Collection: "customers", Find: am.M{"isActive": 1}}, func(row am.M) error {
    count++ // one object at a time, in order
    return nil
})
if err != nil {
    return nil, err
}
return am.M{"count": count}, nil

1
2
3
4
5
6
let mut count = 0;
g.sys.db.get_all_by_stream(GetAll::new("mysql8", "inventory", "customers").find(json!({ "isActive": 1 })), |_row| {
    count += 1; // one object at a time, in order
    Ok(())
})?;
Ok(json!({ "count": count }))

1
2
3
4
5
int[] count = { 0 };
g.sys.db.getAllByStream(new GetAll("mysql8", "inventory", "customers").find(Map.of("isActive", 1)), row -> {
    count[0]++; // one object at a time, in order
});
return Map.of("count", count[0]);

1
2
3
4
5
var count = 0
g.sys.db.getAllByStream(GetAll("mysql8", "inventory", "customers").find(mapOf("isActive" to 1))) { row ->
    count++ // one object at a time, in order
}
return mapOf("count" to count)

Without a schema : /api/gen

Every table also answers this operation under /api/gen, with or without a schema :
URL /api/gen/admin/mysql8/inventory/customers/stream
API id GEN_GET_ALL_STREAM
From code g.sys.db.gen.getAllByStreamGen

A filter matches the value as you send it : "22" does not match the number 22. On MongoDB, a text that looks like an ObjectId is turned into one.

Headers and errors

This operation takes the request headers of every API. It answers the envelope, with the status codes and the messages of every API.

Related : All APIs · Table schema · Pre hooks · Post hooks · API group permission