# 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/v1/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, and it is what the [schema APIs](/v1/docs/apis-all/overview.html) enforce. The [generated APIs](/v1/docs/apis-all/generated-apis/auto-generated-get-all-api.html) never read it.

> Diagram : The stages of a write through a schema API, in order, from the request to 201 or 400

| | |
|---|---|
| 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](/v1/docs/apis-all/system-apis/system-generated-is-valid-data-for-table-api.html) check. |
| Gives | Validation, conversions, defaults, relations, the [TypeScript interface](/v1/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

```typescript title="orders" linenums="1"
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](/v1/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 = { … }`.

<a id="__type"></a><a id="types"></a>

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

```typescript title="One field per type" linenums="1"
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.

```typescript title="Nested documents and arrays (MongoDB)" linenums="1"
address: { city: EType.string, zip: EType.string },
tags: [EType.string],
lines: [{ sku: EType.string, qty: EType.number }],
```

<a id="conversions"></a><a id="defaultfun"></a>

## 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](/v1/docs/secrets/secrets.html), 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](/v1/docs/apis-all/system-apis/system-generated-hash-data-api.html) API. |

```typescript title="Trim and case" linenums="1"
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 } },
```

```typescript title="conversionFun : change the value, or stamp it on every save and update" linenums="1"
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
},
```

```typescript title="defaults" linenums="1"
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
},
```

```typescript title="Encrypted and hashed fields" linenums="1"
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
```

<a id="validations"></a>

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

```typescript title="required, min and max, minLength and maxLength" linenums="1"
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 } },
```

```typescript title="email and enum" linenums="1"
mail_id: <ISchemaProperty>{ __type: EType.string, validations: { required: true, email: true } },
country: <ISchemaProperty>{ __type: EType.string, validations: { enum: ['India', 'Africa'] } },
```

```typescript title="validatorFun : your own rule, run last" linenums="1"
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;
        },
    },
},
```

```typescript title="unique : checked with a query before the write" linenums="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](/v1/docs/apis-all/error-codes.html).

<a id="isprimarykey"></a><a id="autogeneratebyapimaker"></a><a id="isautoincrementbyam"></a><a id="isautoincrementbydb"></a><a id="keys-and-generated-values"></a>

## 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](/v1/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](/v1/docs/features/optimistic-concurrency-control.html). |

```typescript title="Primary keys, generated ids and the version field" linenums="1"
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() } },
```

<a id="collectiontable"></a><a id="database"></a><a id="instance"></a><a id="relations-and-virtual-fields"></a>

## 6. Relations and virtual fields

```typescript title="A relation, its reverse, and a relation to another instance" linenums="1"
// 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](/v1/docs/apis-all/query-params/deep.html) (`deep` on any get), [master save](/v1/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](/v1/docs/dashboard/diagram.html).
- 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.

## Related

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