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