# Table Schema

> The schema of a table in API Maker - types, conversions, validations, defaults, keys, relations, virtual fields, auto increment, encryption and hashing - written in TypeScript, checked against the code, with the errors each rule gives and a field to copy for each rule.

Source: https://docs.apimaker.dev/v4/docs/schema/schema.html

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, whatever [language](https://docs.apimaker.dev/v4/docs/languages/index.html#basic-info) your code is written in, and it is what the [schema APIs](https://docs.apimaker.dev/v4/docs/apis-all/overview.html) enforce. The [generated APIs](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-get-all-api.html#without-a-schema) never read it.

> Diagram : The stages of a write through a schema API, in order, from the request to 201 or 400
>
> What a schema API does to every write : refuses unknown keys, converts the types, trims and cases strings, runs your conversion function, encrypts or hashes, fills ids and defaults, runs the rules and your validator function, then saves and answers 201, or answers 400 with every error at once and saves nothing.

| | |
|---|---|
| Where | `APIs & Logic → Instance API (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](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-is-valid-data-for-table-api.html) check. |
| Gives | Validation, conversions, defaults, relations, the [TypeScript interface](https://docs.apimaker.dev/v4/docs/features/developer-tools.html#typescript-interfaces-from-your-schemas) `db.<instance>.<database>.I<Table>`, the sample payloads of the API testing page and of Swagger. |

## 1. A schema, field by field

**orders**

```typescript
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](https://docs.apimaker.dev/v4/docs/getting-started/first-api.html) 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**

```typescript
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.
- Columns of SQL which hold more than one value : a vector is `[EType.number]`, an array of PostgreSQL `[EType.string]`, and a JSON column `EType.string` which takes objects and lists. [Data types](https://docs.apimaker.dev/v4/docs/schema/data-types.html) has every type of every database.

**Nested documents and arrays (MongoDB)**

```typescript
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](https://docs.apimaker.dev/v4/docs/secrets/secrets.html). The same text encrypts differently every time. The APIs answer the value as it is stored : your code reads it back on the server with `g.sys.system.decrypt` and the same keys (see below). An object `{ encryptionAlgorithm, secret }` 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](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-hash-data-api.html) API. It is fast, with one key for every row and no salt per row : not a password hash like Argon2id or bcrypt. |

**Trim and case**

```typescript
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**

```typescript
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**

```typescript
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**

```typescript
contact_email: <ISchemaProperty>{ __type: EType.string, conversions: { encryption: true } }, // answered as stored : cipher text
password: <ISchemaProperty>{ __type: EType.string, conversions: { hashing: true } },         // compare with the hash data API
```

**Find by a hashed field, read an encrypted one back (custom API)**

```typescript
// The same text always gives the same hash : hash what you look for
const phone = await g.sys.system.hash(g.req.body.phone);
const reviews = await g.sys.db.query({
    instance: 'mongodb', database: 'shop', collection: 'reviews',
    find: { contact_phone: phone },
    select: { review_no: 1, title: 1, contact_email: 1 },
});
// The keys the schema encrypted with : the plain text exists on the server only
const [algorithm, pass] = await g.sys.system.getSecret(['common.encryptionAlgorithm', 'common.secret']);
for (const review of reviews) {
    review.contact_email = await g.sys.system.decrypt(review.contact_email, algorithm, pass);
}
```

## 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](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-create-indexes-api.html)). | `unique` |

**required, min and max, minLength and maxLength**

```typescript
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**

```typescript
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**

```typescript
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**

```typescript
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](https://docs.apimaker.dev/v4/docs/apis-all/error-codes.html).

### Where the functions run

`conversionFun`, `defaultFun` and `validatorFun` are JavaScript, whatever language the code of the account is written in. They run for whoever answers the save : the server for a request, or, for a save your code makes through `g.sys`, [the sandbox of that code](https://docs.apimaker.dev/v4/docs/languages/index.html#g-sys-in-the-sandbox).

The schema itself is [settings](https://docs.apimaker.dev/v4/docs/languages/index.html#settings-run) : API Maker runs its file once, when you save it, and keeps every function as its text. A function therefore runs on its own later, without the file around it : it sees its arguments, `require` and what every script sees, not a variable or an import of the top of the schema. Inside a function write `require('dayjs')`, where it runs with the packages of its sandbox ; the top of the schema imports `types` and your utility classes written in TypeScript, nothing of npm.

| The request is answered by | Its functions run in |
|---|---|
| The server, or code in TypeScript : a schema API called directly, a custom API or a hook in TypeScript | the sandbox of TypeScript of the account (Node.js or Bun), with the npm packages of its [settings](https://docs.apimaker.dev/v4/docs/settings/sandboxSettings.html#packages) |
| Code in another [language](https://docs.apimaker.dev/v4/docs/languages/index.html) : a custom API or a hook in Python, PHP, C#, Go, Rust, Java or Kotlin which saves through a schema | the sandbox of that language, in the JavaScript engine API Maker has there (QuickJS) |

- A function answers the same in both : the values, the row it changes, the dates, the ids and what it throws.
- The engine of the other languages has JavaScript itself (promises, `async`, `Date`, `JSON`, `Math`, regular expressions, `BigInt`), `require('types')`, `crypto` (`randomUUID`, `randomBytes`, `randomInt`, `getRandomValues`, `createHash` and `createHmac` with `md5`, `sha1`, `sha256`, `sha384` and `sha512`), `Buffer`, `console`, the timers and `process.env`.
- It has no packages of npm and no `Intl` : `require('dayjs')` throws there (`Cannot find module 'dayjs' : … not the packages of npm.`), and `localeCompare` compares letter by letter, without the rules of a language. A function which needs one of them belongs to a table which is saved by code in TypeScript or through the schema APIs ; otherwise do that part in the code which saves.
- An error JavaScript itself throws (calling what is no function, `BigInt(1.5)`) is worded by the engine which ran it, so its message can differ between the two. A message your function throws is the same.

## 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](https://docs.apimaker.dev/v4/docs/features/auto-increment.html). 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](https://docs.apimaker.dev/v4/docs/features/optimistic-concurrency-control.html). |

**Primary keys, generated ids and the version field**

```typescript
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**

```typescript
// 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](https://docs.apimaker.dev/v4/docs/apis-all/query-params/deep.html) (`deep` on any get), [master save](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-master-save-api.html) (one call saves the order and its items) and the [ER diagram](https://docs.apimaker.dev/v4/docs/logs/logs-and-dashboards.html#er-diagram).
- A virtual field can not be used in `find` : the error `virtualFieldUsedInFind` says so.

## 7. Other keys

- 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.

## Related

- [Optimistic concurrency control](https://docs.apimaker.dev/v4/docs/features/optimistic-concurrency-control.html) · [Auto increment](https://docs.apimaker.dev/v4/docs/features/auto-increment.html) · [Deep populate](https://docs.apimaker.dev/v4/docs/apis-all/query-params/deep.html) · [Is valid data for table](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-is-valid-data-for-table-api.html)
- [Generated interfaces](https://docs.apimaker.dev/v4/docs/features/developer-tools.html#typescript-interfaces-from-your-schemas) · [Error codes](https://docs.apimaker.dev/v4/docs/apis-all/error-codes.html)