# Models

URL: https://zerodev.ca/docs/prism/developer-documentation/models

> Database tables for your module and the methods to read and write them.



A model is one database table. Prism creates the table on startup, adds new columns when the schema changes and gives you methods to read and write it. The same model works on PostgreSQL, MySQL and MariaDB. Put models in `src/models/`.

```js title="modules/example/src/models/items.js"
import { Models } from "#prism/modules";

export class ItemsModel extends Models {
    constructor(prism) {
        super(prism, {
            name: "example_items",
            search: ["name", "identifier"],
            filters: ["active"],
            schema: {
                identifier: { type: "string", required: true, unique: true },
                name: { type: "string", required: true },
                description: "text",
                quantity: { type: "integer", default: 1 },
                price: { type: "float", default: 0 },
                active: { type: "boolean", default: true },
                metadata: "json",
                expires_at: "date"
            }
        });
    }
}
```

Get it from anywhere with `this.prism.modules.models.get("example", "example_items")`.

## Options

| Key       | Default  | What it does                                                        |
| --------- | -------- | ------------------------------------------------------------------- |
| `name`    | Required | The model name and table name. Prefix it with your module ID.       |
| `schema`  | `{}`     | The columns. You can also override `schema()` and return them.      |
| `search`  | `[]`     | Columns that `page({ search })` matches against, ignoring case.     |
| `filters` | `[]`     | Query string keys that `query()` turns into `where` conditions.     |
| `indexes` | `[]`     | Extra Sequelize indexes.                                            |
| `dynamic` | `false`  | Store fields that are not in the schema in a JSON `options` column. |

## Fields

A field is a type name, or an object with `type`, `required`, `unique` and `default`.

| Type      | SQL                       |
| --------- | ------------------------- |
| `string`  | `VARCHAR(255)`            |
| `text`    | `TEXT`                    |
| `integer` | `INTEGER`                 |
| `bigint`  | `BIGINT`                  |
| `float`   | `FLOAT`                   |
| `boolean` | `BOOLEAN`                 |
| `date`    | Timestamp                 |
| `json`    | `JSON`                    |
| `uuid`    | `UUID`, filled in for you |

Every table also gets `id`, `created_at` and `updated_at`. An unknown type stops the module from loading.

## Methods

| Method                  | Returns                                                                     |
| ----------------------- | --------------------------------------------------------------------------- |
| `create(values)`        | The new row.                                                                |
| `find(id)`              | The row with that `id` as a plain object, or `null`.                        |
| `require(id)`           | The same. Throws a `404` if missing.                                        |
| `get(where)`            | The first matching row, or `null`.                                          |
| `list(where, options)`  | All matching rows. `options` goes to Sequelize, such as `order` or `limit`. |
| `count(where, options)` | How many rows match.                                                        |
| `edit(where, values)`   | Updates matching rows.                                                      |
| `delete(where)`         | Deletes matching rows and returns how many.                                 |
| `page(options)`         | `{ rows, total }` for a list page.                                          |
| `query(request.query)`  | Turns a query string into options for `page()`.                             |
| `plain(row)`            | A row as a plain object.                                                    |
| `pick(values)`          | Only the keys that are in the schema.                                       |

`get` and `list` return Sequelize instances. Pass them through `plain()` before sending them on. `this.op` is Sequelize's `Op`, for conditions like `{ created_at: { [this.op.gte]: date } }`.

### Paging

```js
async GET(request) {
    const items = this.prism.modules.models.get(this.module, "example_items");
    return items.page(items.query(request.query));
}
```

`query()` reads `search`, `limit`, `offset` and your `filters`. `page()` sorts newest first unless you pass `order`, and returns at most 500 rows.

### Errors

A duplicate value in a `unique` field throws with status `409`. A missing `required` field throws with status `400`. Inside a route, both are sent back to the caller as `{ error }`.