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:

// 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:

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

Boolean columns should have names starting with is_:

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

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

{ 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 - you shouldn't touch them yourself).

You can add special created_at / updated_at columns to enable automatic create/update tracking.

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.

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

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