Aster Web Data / How it works
From request to SQL
How a request is parsed, planned per database and run as that database's SQL.
Before you put a service in front of a database, you want to know what it will send. With Aster Web Data, a read on one database is one statement in that database's own SQL, two when the client asks for a count, and each statement is tagged with the set it reads so you can find it in the database's logs. The filter, the sort, the page and most expansions are in that statement. Where something cannot run on the database, Aster runs it in its own engine within a row limit, or refuses the request and says why.
Here is a read of EU customers, sorted, with a page of 20 and a count, on PostgreSQL:
| filter | region eq 'EU' |
|---|---|
| orderby | name |
| top | 20 |
| count | true |
As a URL
GET /shop/Customer?$filter=region eq 'EU'&$orderby=name&$top=20&$count=trueThe database receives two statements, /* aster read <table> */ and /* aster count <table> */, in one round trip:
2 statements on PostgreSQL
PostgreSQL, statement 1 of 2
/* aster read customers */ SELECT c."id", c."name", c."email", c."region", CAST(c.xmin AS text) AS c5 FROM "public"."customers" AS c WHERE c."region" = $1 ORDER BY c."name", c."id" LIMIT $2
-- binds: "EU", 20PostgreSQL, statement 2 of 2
/* aster count customers */ SELECT COUNT(*) FROM "public"."customers" AS c WHERE c."region" = $1
-- binds: "EU"The same request on MariaDB:
2 statements on MariaDB
MariaDB, statement 1 of 2
/* aster read customers */ SELECT c.`id`, c.`name`, c.`email`, c.`region` FROM `test_aster_docs_capture`.`customers` AS c WHERE c.`region` = ? ORDER BY c.`name`, c.`id` LIMIT ?
-- binds: "EU", 20MariaDB, statement 2 of 2
/* aster count customers */ SELECT COUNT(*) FROM `test_aster_docs_capture`.`customers` AS c WHERE c.`region` = ?
-- binds: "EU"The filter, the sort and the page are in the statement that reads the rows. The count has the same WHERE and none of the sort or page, and runs in the same snapshot, so it is exact. The sort adds the key as its last column so that pages are stable, as Query your data describes. The PostgreSQL statement also reads the row version, xmin, which becomes the ETag; the MariaDB set declares no concurrency token, so its statement has none.
How a read runs
The caller's row restrictions and hidden fields are added to the plan before it is split, so every part, on every database, carries the rules of its own set and none escapes them. A set the caller may not read is refused with 403. Each database then runs, as SQL in its own dialect, the largest part of the plan it can run in full, and Aster's engine joins the results and runs what no database can. Rows are written to the client as they arrive, so a large read starts arriving at once; the count follows the rows.
The server records each read by its plan shape, the plan with its literal values removed, with its request count, rows and time per stage. Observability lists the metrics.
What the database runs
For a read on one database, the database runs:
- the
$filterpredicate, with its comparisons, string functions, date parts and arithmetic on the database's types; - the
$orderby, with nulls placed as OData orders them; - the page, through
$topand$skip; - the count, a
COUNT(*)over the filtered set; - the related rows of a
$expand. PostgreSQL, MariaDB and SQLite return them in the read statement for most tables; DuckDB reads each level with a statement of its own, keyed by the parent rows. Each database's page gives the conditions.
A write runs on the database that holds its target, in one transaction. The checks of the stored row (the rules, and its ETag against If-Match), the write and the read of the answer go to the database together. On PostgreSQL that is one statement, sent with its BEGIN and COMMIT in one round trip. A PATCH of a customer with If-Match: *:
1 statement on PostgreSQL
PostgreSQL, statement 1 of 1
WITH l0 AS (SELECT "public"."customers"."id" AS l1, "public"."customers"."id" AS l2 FROM "public"."customers" WHERE ("public"."customers"."id" = $1) FOR NO KEY UPDATE), m0 AS (UPDATE "public"."customers" SET "email" = $2 WHERE ("public"."customers"."id" = $3) RETURNING *, CAST("customers".xmin AS text) AS "$etag") SELECT -1 AS m, row_number() OVER () AS o, CAST(l1 AS text) AS d1, CAST(l2 AS text) AS d2, CAST(NULL AS text) AS d3, CAST(NULL AS text) AS d4, CAST(NULL AS text) AS d5 FROM l0 UNION ALL SELECT 0 AS m, row_number() OVER () AS o, CAST(m0."id" AS text) AS d1, CAST(m0."name" AS text) AS d2, CAST(m0."email" AS text) AS d3, CAST(m0."region" AS text) AS d4, CAST(m0."$etag" AS text) AS d5 FROM m0 ORDER BY m, o
-- binds: 1, "billing@umber-studio.example.com", 1A write whose answer holds a decimal, a date or time, or a nested expansion takes a second round trip for the COMMIT. Each database's page gives the statements of each write shape, and What runs on your database shows the reads a DBA is most likely to see.
What runs in Aster's engine
The engine runs what a database cannot, and anything that combines rows from two databases. It reads rows and never writes them. Filters, projections, expansions and pages stream through it one row at a time. Sorts, aggregates and joins hold their rows until they finish, and every row held counts against limits.residual_rows. A request past that limit fails with 400 and QueryTooExpensive, naming the limit.
Where the database runs an operation, the result is the database's own; where the engine runs it, Aster's written rules apply. Comparison and arithmetic rules gives both. The explain header shows, per request, which operations ran where.
A request is refused when it would exceed a cost limit (400, QueryTooExpensive), or when an operation cannot run where its rows are, such as a filter over the rows of another database (501, NotImplemented, naming the sets). Limits lists each limit with its default, including how a $skip or $count over an unbounded fetch is handled, and Error codes lists the codes.
A worked example
This request is the worked example from Query your data. It lists EU customers who have an unpaid invoice over 500, with their latest unpaid invoices, the lines of each invoice and the product of each line:
| filter | region eq 'EU' and invoices/any(i: i/paid eq false and i/amount gt 500) |
|---|---|
| orderby | name |
| count | true |
| top | 5 |
| select | id,name |
| expand | invoices |
| filter | paid eq false |
| orderby | issued desc |
| top | 3 |
| select | id,issued,amount |
| expand | lines |
| select | quantity,unitPrice |
| expand | product |
| select | name |
As a URL
GET /shop/Customer?$filter=region eq 'EU' and invoices/any(i: i/paid eq false and i/amount gt 500)&$orderby=name&$count=true&$top=5&$select=id,name&$expand=invoices($filter=paid eq false;$orderby=issued desc;$top=3;$select=id,issued,amount;$expand=lines($select=quantity,unitPrice;$expand=product($select=name)))The whole read goes to the database that holds the shop, because that database runs every operation the request needs. The explain header's summary shows every operation pushed to the database and none left to the engine:
{
"d": "nodes",
"m": {
"fr": false,
"n": [
20,
20,
0,
0
],
"q": 1,
"why": {}
},
"ops": [
{
"f": [
[
0,
"shop",
true,
[]
]
],
"n": [
[
19,
"Expand",
"p",
0,
""
],
[
7,
"Project",
"p",
0,
""
],
[
6,
"Page",
"p",
0,
""
],
[
5,
"Sort",
"p",
0,
""
],
[
4,
"Count",
"p",
0,
""
],
[
3,
"Filter",
"p",
0,
""
],
[
0,
"Scan",
"p",
0,
""
],
[
2,
"Filter",
"p",
0,
""
],
[
1,
"Scan",
"p",
0,
""
],
[
18,
"Expand",
"p",
0,
""
],
[
12,
"Project",
"p",
0,
""
],
[
11,
"Page",
"p",
0,
""
],
[
10,
"Sort",
"p",
0,
""
],
[
9,
"Filter",
"p",
0,
""
],
[
8,
"Scan",
"p",
0,
""
],
[
17,
"Expand",
"p",
0,
""
],
[
14,
"Project",
"p",
0,
""
],
[
13,
"Scan",
"p",
0,
""
],
[
16,
"Project",
"p",
0,
""
],
[
15,
"Scan",
"p",
0,
""
]
]
}
],
"v": 1
}The database receives two statements:
2 statements on PostgreSQL
PostgreSQL, statement 1 of 2
/* aster read customers */ SELECT q."id", q."name", q.c3, q6.j AS "invoices" FROM (SELECT c."id", c."name", CAST(c.xmin AS text) AS c3 FROM "public"."customers" AS c WHERE c."region" = $1 AND EXISTS (SELECT 1 FROM "public"."invoices" AS i WHERE i."customerId" = c."id" AND i."paid" = FALSE AND i."amount" > $2) ORDER BY c."name", c."id" LIMIT $3) AS q LEFT JOIN LATERAL (SELECT COALESCE(json_agg(json_build_array(q5."id", q5."issued", CAST(q5."amount" AS text), q5.c4, q5."lines") ORDER BY q5."issued" DESC, q5."id"), '[]'::json) AS j FROM (SELECT q2."id", q2."issued", q2."amount", q2.c4, q4.j AS "lines" FROM (SELECT i2."id", i2."issued", i2."amount", CAST(i2.xmin AS text) AS c4 FROM "public"."invoices" AS i2 WHERE i2."customerId" = q."id" AND i2."paid" = FALSE ORDER BY i2."issued" DESC, i2."id" LIMIT $4) AS q2 LEFT JOIN LATERAL (SELECT COALESCE(json_agg(json_build_array(q3."quantity", CAST(q3."unitPrice" AS text), q3."id", q3."productId", q3.c5, q3."product")), '[]'::json) AS j FROM (SELECT i3."quantity", i3."unitPrice", i3."id", i3."productId", CAST(i3.xmin AS text) AS c5, CASE WHEN p."id" IS NULL THEN NULL ELSE json_build_array(json_build_array(p."name", p."id", CAST(p.xmin AS text))) END AS "product" FROM "public"."invoice_lines" AS i3 LEFT JOIN "public"."products" AS p ON p."id" = i3."productId" WHERE i3."invoiceId" = q2."id") AS q3) AS q4 ON TRUE) AS q5) AS q6 ON TRUE ORDER BY q."name", q."id"
-- binds: "EU", 500, 5, 3PostgreSQL, statement 2 of 2
/* aster count customers */ SELECT COUNT(*) FROM "public"."customers" AS c WHERE c."region" = $1 AND EXISTS (SELECT 1 FROM "public"."invoices" AS i WHERE i."customerId" = c."id" AND i."paid" = FALSE AND i."amount" > $2)
-- binds: "EU", 500The invoices/any(...) filter is an EXISTS (SELECT 1 ...) in the WHERE clause. Each nested $expand level is a lateral subquery in the read statement, with its own filter, sort and page. The count is the second statement, with the same WHERE. The response is one document:
HTTP 200
content-type: application/json;odata.metadata=minimal
odata-version: 4.01The first 3 of 5 rows
{
"@odata.context": "http://localhost:8080/shop/$metadata#Customer(id,name,invoices(id,issued,amount,lines(quantity,unitPrice,id,product(name,id))))",
"value": [
{
"@odata.etag": "W/\"54706134\"",
"id": 15,
"name": "Harbor Supply",
"invoices": [
{
"@odata.etag": "W/\"54706138\"",
"id": 54,
"issued": "2026-10-05",
"amount": 1306.50,
"lines": [
{
"@odata.etag": "W/\"54706143\"",
"quantity": 3,
"unitPrice": 129.00,
"id": 143,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Cordless drill",
"id": 2
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 6,
"unitPrice": 129.00,
"id": 140,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Cordless drill",
"id": 2
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 3,
"unitPrice": 24.50,
"id": 142,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Drill bit set",
"id": 3
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 6,
"unitPrice": 12.00,
"id": 141,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Safety glasses",
"id": 8
}
}
]
},
{
"@odata.etag": "W/\"54706138\"",
"id": 53,
"issued": "2026-02-26",
"amount": 82.65,
"lines": [
{
"@odata.etag": "W/\"54706143\"",
"quantity": 5,
"unitPrice": 9.90,
"id": 139,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Tape measure",
"id": 5
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 3,
"unitPrice": 4.80,
"id": 138,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Wood glue",
"id": 9
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 5,
"unitPrice": 3.75,
"id": 137,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Sanding block",
"id": 10
}
}
]
}
]
},
{
"@odata.etag": "W/\"54706134\"",
"id": 17,
"name": "Iris Supply",
"invoices": [
{
"@odata.etag": "W/\"54706138\"",
"id": 59,
"issued": "2026-09-18",
"amount": 830.70,
"lines": [
{
"@odata.etag": "W/\"54706143\"",
"quantity": 3,
"unitPrice": 18.90,
"id": 155,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Claw hammer",
"id": 1
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 6,
"unitPrice": 129.00,
"id": 156,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Cordless drill",
"id": 2
}
}
]
},
{
"@odata.etag": "W/\"54706138\"",
"id": 60,
"issued": "2026-07-30",
"amount": 77.40,
"lines": [
{
"@odata.etag": "W/\"54706143\"",
"quantity": 2,
"unitPrice": 18.90,
"id": 158,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Claw hammer",
"id": 1
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 4,
"unitPrice": 9.90,
"id": 157,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Tape measure",
"id": 5
}
}
]
},
{
"@odata.etag": "W/\"54706138\"",
"id": 61,
"issued": "2026-06-11",
"amount": 133.50,
"lines": [
{
"@odata.etag": "W/\"54706143\"",
"quantity": 6,
"unitPrice": 15.75,
"id": 159,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Spirit level",
"id": 4
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 6,
"unitPrice": 6.50,
"id": 160,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Utility knife",
"id": 6
}
}
]
}
]
},
{
"@odata.etag": "W/\"54706134\"",
"id": 20,
"name": "Juniper Works",
"invoices": [
{
"@odata.etag": "W/\"54706138\"",
"id": 73,
"issued": "2026-06-30",
"amount": 669.50,
"lines": [
{
"@odata.etag": "W/\"54706143\"",
"quantity": 5,
"unitPrice": 129.00,
"id": 187,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Cordless drill",
"id": 2
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 1,
"unitPrice": 24.50,
"id": 186,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Drill bit set",
"id": 3
}
}
]
},
{
"@odata.etag": "W/\"54706138\"",
"id": 75,
"issued": "2026-01-04",
"amount": 523.50,
"lines": [
{
"@odata.etag": "W/\"54706143\"",
"quantity": 4,
"unitPrice": 129.00,
"id": 192,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Cordless drill",
"id": 2
}
},
{
"@odata.etag": "W/\"54706143\"",
"quantity": 2,
"unitPrice": 3.75,
"id": 191,
"product": {
"@odata.etag": "W/\"54706147\"",
"name": "Sanding block",
"id": 10
}
}
]
}
]
}
],
"@odata.count": 7
}When the customers and the invoices are on two databases, the same request reads each level with a statement of its own; Reads across databases shows it.