# Schema

When using WatermelonDB, you're dealing with **Models** and **Collections**. However, underneath Watermelon sits an **underlying database** (SQLite or LokiJS) which speaks a different language: **tables and columns**. Together, those are called a **database schema** and we must define it first.

## Defining a Schema

Say you want Models `Post`, `Comment` in your app. For each of those Models, you define a table. And for every field of a Model (e.g. name of the blog post, author of the comment) you define a column. For example:

```js
// model/schema.js
import { appSchema, tableSchema } from '@nozbe/watermelondb'

export const mySchema = appSchema({
  version: 1,
  tables: [
    tableSchema({
      name: 'posts',
      columns: [
        { name: 'title', type: 'string' },
        { name: 'subtitle', type: 'string', isOptional: true },
        { name: 'body', type: 'string' },
        { name: 'is_pinned', type: 'boolean' },
      ]
    }),
    tableSchema({
      name: 'comments',
      columns: [
        { name: 'body', type: 'string' },
        { name: 'post_id', type: 'string', isIndexed: true },
      ]
    }),
  ]
})
```

**Note:** It is database convention to use plural and snake_case names for table names. Column names are also snake_case. So `Post` become `posts` and `createdAt` becomes `created_at`.

### Column types

Columns have one of three types: `string`, `number`, or `boolean`.

Fields of those types will default to `''`, `0`, or `false` respectively, if you create a record with a missing field.

To allow fields to be `null`, mark the column as `isOptional: true`.

### Naming conventions

To add a relation to a table (e.g. `Post` where a `Comment` was published, or author of a comment), add a string column ending with `_id`:

```js
{ name: 'post_id', type: 'string' },
{ name: 'author_id', type: 'string' },
```

Boolean columns should have names starting with `is_`:

```js
{ name: 'is_pinned', type: 'boolean' }
```

Date fields should be `number` (dates are stored as Unix timestamps) and have names ending with `_at`:

```js
{ name: 'last_seen_at', type: 'number', isOptional: true }
```

### Special columns

All tables _automatically_ have a string column `id` (of `string` type) to uniquely identify records -- therefore you cannot declare a column named `id` yourself. (There are also special `_status` and `_changed` columns used for [synchronization](./Sync/Intro.md) - you shouldn't touch them yourself).

You can add special `created_at` / `updated_at` columns to enable [automatic create/update tracking](./Advanced/CreateUpdateTracking.md).

### Modifying Schema

Watermelon cannot automatically detect Schema changes. Therefore, whenever you change the Schema, you must increment its version number (`version:` field).

During early development, this is all you need to do - on app reload, this will cause the database to be cleared completely.

To seamlessly update the schema (without deleting user data), use [Migrations](./Advanced/Migrations.md).

⚠️ Always use Migrations if you already shipped your app.

### Indexing

To enable database indexing, add `isIndexed: true` to a column.

Indexing makes querying by a column faster, at the expense of create/update speed and database size.

For example, if you often query all comments belonging to a post (that is, query comments by its `post_id` column), you should mark the `post_id` column as indexed.

However, if you rarely query all comments by its author, indexing `author_id` is probably not worth it.

In general, most `_id` fields are indexed. Occasionally, `boolean` fields are worth indexing (but it's a "low quality index"). However, you should almost never index date (`_at`) columns or `string` columns. You definitely do not want to index long-form user text.

⚠️ Do not mark all columns as indexed to "make Watermelon faster". Indexing has a real performance cost and should be used only when appropriate.

## Advanced

### Unsafe SQL schema

If you want to modify the SQL used to set up the SQLite database, you can pass `unsafeSql` parameter
to `tableSchema` and `appSchema`. This parameter is a function that receives SQL generated by Watermelon,
and you can return whatever you want - so you can append, prepend, replace parts of SQL, or return
your own SQL altogether. When passed to `tableSchema`, it receives SQL generated for just that table,
and when to `appSchema` - the entire schema SQL.

⚠️  Note that SQL generated by WatermelonDB is not considered to be a stable API, so be careful about your transforms as they can break at any time.

```js
appSchema({
  ...
  tables: [
    tableSchema({
      name: 'tasks',
      columns: [...],
      unsafeSql: sql => sql.replace(/create table [^)]+\)/, '$& without rowid'),
    }),
  ],
  unsafeSql: (sql, kind) => {
    // Note that this function is called not just when first setting up the database
    // Additionally, when running very large batches, all database indices may be dropped and later
    // recreated as an optimization. More kinds may be added in the future.
    switch (kind) {
      case 'setup':
        return `create blabla;${sql}`
      case 'create_indices':
      case 'drop_indices':
        return sql
      default:
        throw new Error('unexpected unsafeSql kind')
    }
  },
})
```

* * *

## Next steps

➡️ After you define your schema, go ahead and [**define your Models**](./Model.md)
