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

Get by id (schema API)

Reads one row by its primary key, or by any other column with the second param. With select and deep, it is the detail screen of your app in one call.
Method GET
URL /api/schema/admin/mysql8/inventory/customers/get-by-id/:id[/:primaryKey]
Body none
Query params select, deep, find (extra conditions)
Answer data : the row, or null when nothing matches
Databases MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona
Cached yes, with enableCaching on the table
From code g.sys.db.getById
API id SCHEMA_GET_BY_ID (groups, settings, hooks, WebSocket subscriptions)
The other family Get by id 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/get-by-id/:id[/:primaryKey] : 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

By primary key

GET /api/schema/admin/mysql8/inventory/customers/get-by-id/1

{
    "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 }
}
  • The primary key is the one of the table (_id on MongoDB, the primary key column on SQL, or isPrimaryKey in the schema). An unknown id answers data: null with success: true.

By any column

Name the column as the second param :

GET /api/schema/admin/mysql8/inventory/customers/get-by-id/Bob/first_name
GET /api/schema/admin/mysql8/inventory/customers/get-by-id/382345/pincode
  • When several rows match, the first one is returned.
  • The value is converted to the type of the column, so 382345 is compared as a number.
GET /api/schema/admin/mysql8/inventory/customers/get-by-id/1?select=first_name,last_name
GET /api/schema/admin/mysql8/inventory/customers/get-by-id/1?deep=[{s_key:'customer_id',t_col:'orders',t_key:'customer_id',isMultiple:true}]
  • deep works as on get all, with the relations of the schema when the table has one.

One more condition

find narrows the match further, for example to make sure the row belongs to the caller :

GET /api/schema/admin/mysql8/inventory/customers/get-by-id/1?find={isActive:1}

From your code

g.sys.db.getById 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.