This page View Markdown Open in ChatGPT Open in Claude

Query parameters¶

The read APIs of a table take their options in the URL : ?limit=10&sort=-customer_id. The query APIs take the same keys in their JSON body.

In short

find filters, sort orders, skip and limit make pages, select picks the columns, deep brings related rows.
getTotalCount=true adds the total number of matching rows to the answer.
Without limit, a read returns every row that matches.
Values are JSON5 : find={first_name:'Bob'} works without quotes around the keys.

The examples read this table, customers of the instance mysql8.

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 params¶

Param	Example	What it does
find	`?find={first_name:'Bob',pincode:{$gte:382345}}`	Filters with the operators of MongoDB, on every database.
a column	`?first_name=Bob,Alice`	A column name is a filter too. See A column as a query param.
skip	`?skip=20`	Leaves out the first rows.
limit	`?limit=10`	At most this many rows.
sort	`?sort=-last_update,first_name`	The order. `-` for descending.
select	`?select=first_name,last_name`	Only these columns. `-password` : all but this one.
deep	`?deep=state_id`	Puts the related row in place of its id.
getTotalCount	`?getTotalCount=true`	Adds `totalCount` : the rows that match, before `skip` and `limit`.
upsert	`?upsert=true`	Inserts the row when the id is not there.
returnDocument	`?returnDocument=before`	Answers the row as it was before the change.
throwErrorIfRecordNotFound	`?throwErrorIfRecordNotFound=true`	Answers `404` for an unknown id, in place of `data: null`.

Which API takes which¶

API	What it takes
Get all, get all by stream, distinct	Every read param of the URL, and columns as filters.
Get by id	`select` and `deep`.
Save single or multiple, master save	`select` and `deep`, which shape the answer, and `returnData=false`.
Update by id, replace by id	`select`, `deep`, `upsert`, `returnDocument`, `throwErrorIfRecordNotFound`.
Remove by id	`select`, `deep`, `throwErrorIfRecordNotFound`.
Query, query by stream	The same keys, in the JSON body.
Count, distinct with query, update many, remove by query	Their own JSON body, with a `find`. See the page of each API.

skip¶

Leaves out the first rows. With limit, it makes pages : page 3 of 10 rows is skip=20&limit=10.

GET /api/schema/admin/mysql8/inventory/customers?skip=20&limit=10&sort=customer_id

A whole number. 0, or no skip, leaves out nothing.
Always sort when you make pages. Without an order, the database may send the rows in any order : a page may repeat a row or miss one.

limit¶

At most this many rows.

GET /api/schema/admin/mysql8/inventory/customers?limit=2&sort=customer_id

Answer

{
    "success": true,
    "statusCode": 200,
    "data": [
        { "customer_id": 1, "first_name": "Bob", "last_name": "Lin" },
        { "customer_id": 2, "first_name": "Alice", "last_name": "Page" }
    ]
}

Without limit, get all and the query APIs return every row that matches. Give a limit as soon as a table can grow.
To read a whole big table, use a stream (get all by stream) : the rows go out as they come, without filling the memory of the server.

sort¶

Orders the rows. Name the columns, with - in front of a column for descending order.

GET /api/schema/admin/mysql8/inventory/customers?sort=-last_update,first_name

In the body of the query APIs, sort is a text or an object :

{ "find": {}, "sort": "-last_update,first_name" }

{ "find": {}, "sort": { "last_update": -1, "first_name": 1 } }

Spaces around the commas are fine.
On MongoDB, a nested field sorts too : sort=address.city.
Sorting a big table on a column without an index is slow. Index Maker finds the queries that need an index.

getTotalCount¶

getTotalCount=true adds totalCount to the answer : how many rows match the filter, before skip and limit. One call gives the page and the total.

GET /api/schema/admin/mysql8/inventory/customers?find={isActive:1}&limit=2&getTotalCount=true

Answer

{
    "success": true,
    "statusCode": 200,
    "data": [
        { "customer_id": 1, "first_name": "Bob", "last_name": "Lin" },
        { "customer_id": 2, "first_name": "Alice", "last_name": "Page" }
    ],
    "totalCount": 5
}

It works on get all, the query API and the two streams. A stream sends totalCount after the rows.
The count is a second query on the database. On a very big table with a hard filter, it costs as much as the query itself.
Need the number only ? Call the count API.

upsert¶

upsert=true makes update by id and replace by id insert the row when the id is not there.

PUT /api/schema/admin/mysql8/inventory/customers/update-by-id/999?upsert=true

Body

{ "first_name": "John", "last_name": "Sina", "pincode": 382350, "isActive": 0 }

Row 999	What happens	Answer
is there	It is updated with the body.	The row after the change.
is not there	It is inserted : `customer_id: 999` and the body.	The new row.

Without upsert, an unknown id changes nothing, and the answer is data: null.
An inserted row is checked like a new row : with a schema, every required column must be in the body.

returnDocument¶

Which version of the row update by id and replace by id answer : after the change (the default), or before it. It changes the answer only, never what is saved.

PUT /api/schema/admin/mysql8/inventory/customers/update-by-id/1?returnDocument=before

Answer : the row before the new pincode

{
    "success": true,
    "statusCode": 200,
    "data": { "customer_id": 1, "first_name": "Bob", "last_name": "Lin", "pincode": 382345 }
}

select and deep apply to the version in the answer.
With upsert, a row that was inserted had no version before : before answers null.

throwErrorIfRecordNotFound¶

For update by id, replace by id and remove by id. With true, an unknown id answers 404 with the message Record not found. Without it, the answer is 200 with data: null.

DELETE /api/schema/admin/mysql8/inventory/customers/999?throwErrorIfRecordNotFound=true

Answer

{ "success": false, "statusCode": 404, "errors": [ { "code": 404, "message": "Record not found." } ] }

Write them right¶

Values are JSON5 : find={first_name:'Bob'} needs no quotes around the keys, and takes single quotes. Strict JSON works too.
Encode the value for the URL when your client does not : find=%7Bfirst_name%3A'Bob'%7D. The API Testing page and g.sys.db do it for you.
In the body of the query APIs, the same keys are plain JSON :

{ "find": { "first_name": "Bob" }, "sort": "-customer_id", "skip": 0, "limit": 10, "select": "first_name,last_name", "getTotalCount": true }

In a deep item, skip, limit and sort apply to the related rows of each row.
From code, queryParams of g.sys.db.getAll takes the same keys, as objects : see the code examples.
The schema APIs convert the values of find to the types of the schema : "8" matches the number 8. The generated APIs compare what you send as it is.