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

Get all (schema API)

Reads rows of a table. Without a query param it returns every row ; with find, skip, limit, sort, select, deep and getTotalCount it becomes the list API of your screens.
Method GET
URL /api/schema/admin/mysql8/inventory/customers
Body none
Query params find, skip, limit, sort, select, deep, getTotalCount, any column
Answer data : an array of rows, totalCount with getTotalCount=true
Databases MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona
Cached yes, with enableCaching on the table
From code g.sys.db.getAll
API id SCHEMA_GET_ALL (groups, settings, hooks, WebSocket subscriptions)
The other family Get all 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 : 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

Every row

GET /api/schema/admin/mysql8/inventory/customers

{
    "success": true,
    "statusCode": 200,
    "data": [
        { "customer_id": 1, "first_name": "Bob", "last_name": "lin", "last_update": "2022-11-14T04:34:58.000Z", "pincode": 382345, "isActive": 1 },
        { "customer_id": 2, "first_name": "Alice", "last_name": "Page", "last_update": "2022-10-15T02:10:40.000Z", "pincode": 382346, "isActive": 1 }
    ]
}
  • Without limit, every row comes back. Add limit as soon as the table can grow.

Filter with a column name

Any column is a query param. The operators of api-query-params work in the key : >, >=, <, <=, !=, a comma for a list, a regex between slashes.

GET /api/schema/admin/mysql8/inventory/customers?customer_id=1
GET /api/schema/admin/mysql8/inventory/customers?first_name=Bob&last_name=lin          # both must match
GET /api/schema/admin/mysql8/inventory/customers?first_name=Bob,Alice                  # one of them
GET /api/schema/admin/mysql8/inventory/customers?pincode>=382346
GET /api/schema/admin/mysql8/inventory/customers?first_name!=Bob
GET /api/schema/admin/mysql8/inventory/customers?first_name=/^ma/i                     # regular expression
  • Numbers, booleans and ISO dates in a query param are cast to their type before the query.

Filter with find

find takes a JSON5 object with the MongoDB operators, on every database : $eq, $ne, $gt, $gte, $lt, $lte, $in, $nin, $and, $or, $not, $like, $regex, $isNull. See find.

GET /api/schema/admin/mysql8/inventory/customers?find={first_name:'Bob'}
GET /api/schema/admin/mysql8/inventory/customers?find={customer_id:{$in:[1,2,3]}}
GET /api/schema/admin/mysql8/inventory/customers?find={$or:[{pincode:382345},{first_name:'Alice'}]}
GET /api/schema/admin/mysql8/inventory/customers?find={first_name:{$like:'Ma%'}}

Page and sort

GET /api/schema/admin/mysql8/inventory/customers?skip=20&limit=10&sort=-last_update,first_name
  • sort takes column names separated by commas, - for descending. The JSON form sort={last_update:-1} works too.
  • skip and limit are numbers. limit caps the rows of the answer ; skip leaves out the first rows.

Select fields

GET /api/schema/admin/mysql8/inventory/customers?select=first_name,last_name       # only these (the primary key stays)
GET /api/schema/admin/mysql8/inventory/customers?select=-last_update,-isActive     # everything but these

deep replaces an id with the row it points to, from any table of any instance, as many levels as you want. See deep.

GET /api/schema/admin/mysql8/inventory/customers?deep=[{s_key:'customer_id',t_col:'orders',t_key:'customer_id',isMultiple:true,select:'order_no,total'}]

{
    "success": true,
    "statusCode": 200,
    "data": [
        { "customer_id": 1, "first_name": "Bob", "last_name": "lin", "orders": [ { "order_no": 1001, "total": 12900 } ] }
    ]
}

When the schema of the table names the target of a field (collection, column, database, instance), the deep item only needs the source key :

GET /api/schema/admin/mysql8/inventory/customers?deep=[{s_key:'shipping_id'}]

Total count

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

{ "success": true, "statusCode": 200, "data": [ "…10 rows…" ], "totalCount": 5 }
  • totalCount counts every row matching the filter, before skip and limit : what a pager needs.

Everything together

GET /api/schema/admin/mysql8/inventory/customers?find={first_name:'Bob'}&skip=1&limit=4&sort=-customer_id&select=first_name&deep=[{s_key:'customer_id',t_col:'orders',t_key:'customer_id'}]&getTotalCount=true

Caching, hooks, notifications

  • With caching on for the table, the answer comes from Redis until a write through API Maker changes the table ; the response header x-am-data-source says cache or api, and x-am-cache-control: reset_cache forces the database.
  • Pre hooks can add conditions to g.req.query.find (for example the rows of the signed-in person) and post hooks can reshape g.res.output. When the answer comes from the cache, the hooks do not run.
  • Clients subscribed to this table with WebSocket events are notified after a successful call.

From your code

g.sys.db.getAll makes the same call from custom APIs, hooks, events, schedulers and test cases, with the same headers and params. Pass true as the second argument to get the whole response envelope instead of data.

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.