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

Table schema

A schema describes one table or collection : the type of every field, how a value is cleaned before it is saved, what makes it invalid, which fields point to other tables. It is a TypeScript file kept with the table in Git, and it is what the schema APIs enforce. The generated APIs never read it.

The stages of a write through a schema API, in order, from the request to 201 or 400 POST body "price": "25" "name": " Mouse " Keys not in the schema : 400 Types "25" → 25 date, objectId Clean trim, case defaults conversionFun your function, its return is kept Secure encrypt · hash with your secret Ids ObjectID, UUID auto increment Rules required, min, length, email, enum validatorFun your rule, with the whole object 201 · saved, row returned ids and defaults filled in 400 · every error at once nothing is saved Generated APIs (/api/gen) skip all of this.
Where API Info → DB API : pick the instance, the database and the table, then the schema editor. Generate writes a first schema from the columns or from the documents of the table.
Shape const schema: ISchemaType = { field: EType.string, other: { __type, validations, conversions, … } } and module.exports = { schema }.
Used by The /api/schema/… APIs : save, master save, update, replace, get with deep, the is valid data for table check.
Gives Validation, conversions, defaults, relations, the TypeScript interface db.<instance>.<database>.I<Table>, the sample payloads of the API testing page and of Swagger.

1. A schema, field by field

orders
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
import { EType, ISchemaType, ISchemaProperty } from 'types';

const schema: ISchemaType = {
    _id: <ISchemaProperty>{ __type: EType.objectId, isPrimaryKey: true, isAutoGenerateByAM: { valueGeneratorType: 'ObjectID' } },
    order_no: <ISchemaProperty>{ __type: EType.number, isAutoIncrementByAM: { start: 1, step: 1 } },
    customer_id: <ISchemaProperty>{ __type: EType.number, validations: { required: true }, database: 'shop', collection: 'customers', column: 'customer_id' },
    status: <ISchemaProperty>{ __type: EType.string, validations: { enum: ['PENDING', 'PAID', 'SHIPPED', 'CANCELLED'] }, conversions: { toUpperCase: true, defaults: { defaultValue: 'PENDING' } } },
    total_cents: <ISchemaProperty>{ __type: EType.number, validations: { min: 0 } },
    created_at: <ISchemaProperty>{ __type: EType.date, conversions: { defaults: { defaultFun: () => new Date() } } },
    items: <ISchemaProperty>{ __type: EType.objectId, isVirtualField: true, database: 'shop', collection: 'orderItems', s_columnVirtualLinker: '_id', t_columnVirtualLinker: 'order_id' },
    version: <ISchemaProperty>{ __type: EType.number, isConcurrencyControlField: true },
};

module.exports = { schema };
  • This is the orders table of the sample shop every new account gets : open it in the admin panel to see the whole thing with the products, customers and orderItems schemas.
  • A field is a type alone (name: EType.string) or an object with __type and the rules below. A key the schema does not have is refused on save : the error names it.
  • The examples of the sections below are single fields : paste them inside const schema: ISchemaType = { … }.

2. Types

__type Holds Notes
EType.string text minLength, maxLength, email, enum, trims and case conversions
EType.number numbers min, max, auto increment
EType.boolean true / false
EType.date dates min, max ; sent and received as ISO strings
EType.objectId MongoDB ObjectId strings in JSON
EType.file, EType.files uploaded files the request schema of a custom API, not a table
One field per type
1
2
3
4
5
first_name: <ISchemaProperty>{ __type: EType.string, validations: { required: true } },
employee_no: <ISchemaProperty>{ __type: EType.number, validations: { required: true } },
is_active: <ISchemaProperty>{ __type: EType.boolean },
joined_at: <ISchemaProperty>{ __type: EType.date },
department_id: <ISchemaProperty>{ __type: EType.objectId, validations: { required: true } },
  • MongoDB documents nest : address: { city: EType.string } is an embedded object, tags: [EType.string] an array of strings, lines: [{ sku: EType.string, qty: EType.number }] an array of objects. SQL tables are flat.
Nested documents and arrays (MongoDB)
1
2
3
address: { city: EType.string, zip: EType.string },
tags: [EType.string],
lines: [{ sku: EType.string, qty: EType.number }],

3. Conversions : clean the value first

Conversions run before the validations, on save and on update.

Key Does
trim, trimStart, trimEnd Removes the spaces around a string.
toLowerCase, toUpperCase Changes the case.
conversionFun: (value, row) => … Your function ; its return value is stored. It runs even when the field is absent from the payload, which is how a updated_at: () => new Date() field is stamped on every save and update.
defaults { defaultValue } or { defaultFun: () => … } for a field absent from a save or a replace. defaultValue wins over defaultFun. shouldReplaceNullWithDefault and shouldReplaceEmptyStringWithDefault also replace null and "".
encryption: true Stored encrypted with encryptionAlgorithm and secret of the secret, decrypted when read. An object { encryptionAlgorithm, secret, nonce } names other keys of the secret.
hashing: true Stored as an HMAC SHA-256 hash with the nonce (or secret) of the secret : passwords, tax ids. Look a hashed value up with the hash data API.
Trim and case
1
2
3
first_name: <ISchemaProperty>{ __type: EType.string, conversions: { trim: true } },
code: <ISchemaProperty>{ __type: EType.string, conversions: { toUpperCase: true } },
email: <ISchemaProperty>{ __type: EType.string, conversions: { toLowerCase: true, trimStart: true, trimEnd: true } },
conversionFun : change the value, or stamp it on every save and update
1
2
3
4
5
6
7
8
city_name: <ISchemaProperty>{
    __type: EType.string,
    conversions: { conversionFun: (city_name, fullObj) => city_name ? city_name + '_IND' : city_name },
},
updated_at: <ISchemaProperty>{
    __type: EType.date,
    conversions: { conversionFun: () => new Date() },   // runs even when the field is absent from the payload
},
defaults
1
2
3
4
5
6
7
8
active: <ISchemaProperty>{
    __type: EType.boolean,
    conversions: { defaults: { defaultValue: true, shouldReplaceEmptyStringWithDefault: true, shouldReplaceNullWithDefault: true } },
},
created_at: <ISchemaProperty>{
    __type: EType.date,
    conversions: { defaults: { defaultFun: () => new Date() } },   // defaultValue wins over defaultFun when both are given
},
Encrypted and hashed fields
1
2
card_number: <ISchemaProperty>{ __type: EType.string, conversions: { encryption: true } },   // decrypted when read
password: <ISchemaProperty>{ __type: EType.string, conversions: { hashing: true } },         // compare with the hash data API

4. Validations : refuse a bad value

Key Refuses Error type
required: true A missing, null or empty value on save. On update, only a null or empty value sent. required
min, max A number or a date outside the range. min, max
minLength, maxLength A string or an array outside the length range. minLength, maxLength
email: true A string which is not an email address. emailNotValid
enum: [ … ] A value outside the list. An empty list checks nothing. The sample data generator picks from the list. enumValidation
validatorFun: (value, row) => … Your function, run last : return true to accept ; return false or a message, or throw, to refuse. invalidValue
unique: true A value another row already has, or which appears twice in the same batch. Checked with a query before the write, so prefer a unique index for busy tables (create indexes). unique
required, min and max, minLength and maxLength
1
2
registration_number: <ISchemaProperty>{ __type: EType.number, validations: { required: true, min: 4, max: 10 } },
address: <ISchemaProperty>{ __type: EType.string, validations: { required: true, minLength: 20, maxLength: 100 } },
email and enum
1
2
mail_id: <ISchemaProperty>{ __type: EType.string, validations: { required: true, email: true } },
country: <ISchemaProperty>{ __type: EType.string, validations: { enum: ['India', 'Africa'] } },
validatorFun : your own rule, run last
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
city_name: <ISchemaProperty>{
    __type: EType.string,
    validations: {
        required: true,
        validatorFun: (city_name, fullObj) => {
            if (city_name && ['AHMEDABAD', 'SURAT'].includes(city_name)) throw new Error(`You can not save city_name as '${city_name}'`);
            return true;
        },
    },
},
unique : checked with a query before the write
1
mobile_no: <ISchemaProperty>{ __type: EType.number, validations: { unique: true } },   // prefer a unique index for busy tables
  • The answer of a refused save is 400 with one entry per problem in errors : { type, field, message, code, dataIndex }, dataIndex being the position of the row in an array body. See Error codes.

5. Keys and generated values

Key Meaning
isPrimaryKey: true The primary key of the table : the id of get by id, update by id, remove by id.
isAutoIncrementByDB: true The database assigns the value (SQL identity columns). API Maker sends nothing for it.
isAutoIncrementByAM: true or { start, step } API Maker assigns the next number, on every database, MongoDB included : see Auto increment. No effect with isAutoIncrementByDB.
isAutoGenerateByAM: { valueGeneratorType } A random id when the payload has none : ObjectID, GUID_UUID, ULID or ShortUUID. Wins over isAutoIncrementByAM.
isConcurrencyControlField: true The version field of optimistic concurrency control.
Primary keys, generated ids and the version field
1
2
3
4
5
customer_id: <ISchemaProperty>{ __type: EType.number, isPrimaryKey: true, isAutoIncrementByAM: { start: 1000, step: 1 } },
id: <ISchemaProperty>{ __type: EType.number, isPrimaryKey: true, isAutoIncrementByDB: true },
_id: <ISchemaProperty>{ __type: EType.objectId, isPrimaryKey: true, isAutoGenerateByAM: { valueGeneratorType: 'ObjectID' } },
public_id: <ISchemaProperty>{ __type: EType.string, isAutoGenerateByAM: { valueGeneratorType: 'ULID' } },   // GUID_UUID, ULID, ShortUUID, ObjectID
version: <ISchemaProperty>{ __type: EType.number, isConcurrencyControlField: true, conversions: { conversionFun: () => new Date().getTime() } },

6. Relations and virtual fields

A relation, its reverse, and a relation to another instance
1
2
3
4
5
6
7
8
// orderItems.order_id points to orders._id
order_id: <ISchemaProperty>{ __type: EType.objectId, validations: { required: true }, database: 'shop', collection: 'orders', column: '_id' },

// orders.items : the line items of an order, read from orderItems through their order_id
items: <ISchemaProperty>{ __type: EType.objectId, isVirtualField: true, database: 'shop', collection: 'orderItems', s_columnVirtualLinker: '_id', t_columnVirtualLinker: 'order_id' },

// a relation to a table of another instance and database
owner_id: <ISchemaProperty>{ __type: EType.number, instance: 'mysql8', database: 'crm', table: 'owners', column: 'id' },
Key Meaning
collection or table, column The table and the column this field points to. database and instance when they differ from the ones of the schema.
isVirtualField: true A field which is not in the table : it is filled from the other table, and can be saved through master save. s_columnVirtualLinker is the column of this table, t_columnVirtualLinker the column of the other table which holds it.
  • Relations feed deep populate (deep on any get), master save (one call saves the order and its items) and the ER diagram.
  • A virtual field can not be used in find : the error virtualFieldUsedInFind says so.

7. Other keys

  • uim : settings of the deprecated UI Maker ; they change nothing at runtime.
  • A schema file can also export validations, a list of SUPER_KEY rules which refuse two rows with the same combination of fields. It is deprecated : create a unique index on the fields instead, it is faster and it holds for writes made outside API Maker too.