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

Get all (generated 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/gen/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.gen.getAllGen
API id GEN_GET_ALL (groups, settings, hooks, WebSocket subscriptions)
The other family Get all as a schema API

Schemaless : the data passes as it is

The generated APIs never read the schema of the table. Nothing is converted, validated, defaulted, encrypted or numbered : what you send is what the database gets, and a filter matches the type you send ("22" does not match the number 22). On MongoDB, strings which look like ObjectIds are converted. Every table has these APIs, with or without a schema ; the same operations under /api/schema apply the schema.

The URL

/api/gen/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/gen/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/gen/admin/mysql8/inventory/customers?customer_id=1
GET /api/gen/admin/mysql8/inventory/customers?first_name=Bob&last_name=lin          # both must match
GET /api/gen/admin/mysql8/inventory/customers?first_name=Bob,Alice                  # one of them
GET /api/gen/admin/mysql8/inventory/customers?pincode>=382346
GET /api/gen/admin/mysql8/inventory/customers?first_name!=Bob
GET /api/gen/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/gen/admin/mysql8/inventory/customers?find={first_name:'Bob'}
GET /api/gen/admin/mysql8/inventory/customers?find={customer_id:{$in:[1,2,3]}}
GET /api/gen/admin/mysql8/inventory/customers?find={$or:[{pincode:382345},{first_name:'Alice'}]}
GET /api/gen/admin/mysql8/inventory/customers?find={first_name:{$like:'Ma%'}}

Page and sort

GET /api/gen/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/gen/admin/mysql8/inventory/customers?select=first_name,last_name       # only these (the primary key stays)
GET /api/gen/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/gen/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 } ] }
    ]
}

Total count

GET /api/gen/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/gen/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.gen.getAllGen 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.
400 The query or the body is wrong : the error messages say which key and why.