# 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](https://docs.apimaker.dev/v1/docs/apis-all/overview.html) enforce. The [generated APIs](https://docs.apimaker.dev/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
>
> 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 | `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](https://docs.apimaker.dev/v1/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/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

**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/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 = { … }`.

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

**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/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](https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-hash-data-api.html) API. |

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

## 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/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](https://docs.apimaker.dev/v1/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/v1/docs/apis-all/query-params/deep.html) (`deep` on any get), [master save](https://docs.apimaker.dev/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](https://docs.apimaker.dev/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](https://docs.apimaker.dev/v1/docs/features/optimistic-concurrency-control.html) · [Auto increment](https://docs.apimaker.dev/v1/docs/features/auto-increment.html) · [Deep populate](https://docs.apimaker.dev/v1/docs/apis-all/query-params/deep.html) · [Is valid data for table](https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-is-valid-data-for-table-api.html)
- [Generated interfaces](https://docs.apimaker.dev/v1/docs/features/developer-tools.html#typescript-interfaces-from-your-schemas) · [Error codes](https://docs.apimaker.dev/v1/docs/apis-all/error-codes.html)