mobile-storage-watermelondb · git:20260709.eeb9e6c · 2026-07-09 · sha256 ee78d1b55694f111
mobile-storage-watermelondb git:20260709.eeb9e6cA
Immutable. This exact content is served forever at /api/v1/blob/ee78d1b55694f111.
---
name: mobile-storage-watermelondb
description: WatermelonDB reactive local database for React Native - schema, models, decorators, reactive queries, relations, writers/readers, batch operations, migrations, sync
---
# WatermelonDB Patterns
> **Quick Guide:** Use WatermelonDB for offline-first React Native apps with large local datasets. Define schemas with `appSchema`/`tableSchema`, models with decorators (`@field`, `@text`, `@date`, `@readonly`, `@relation`, `@children`). All writes MUST go through `@writer` methods or `database.write()`. Connect components reactively with `withObservables` from `@nozbe/watermelondb/react`. Use `batch()` for multi-record operations. Lazy loading means nothing is loaded until requested -- queries run on a native SQLite thread.
---
<critical_requirements>
## CRITICAL: Before Using This Skill
> **All code must follow project conventions in CLAUDE.md** (kebab-case, named exports, import ordering, `import type`, named constants)
**(You MUST wrap ALL database modifications in `@writer` methods or `database.write()` -- writes outside a writer throw at runtime)**
**(You MUST keep schema version and migration `toVersion` in sync -- migrations cannot be newer than the schema version)**
**(You MUST use `@immutableRelation` for relations that never change after creation -- it provides extra safety and performance over `@relation`)**
**(You MUST use `prepareCreate`/`prepareUpdate`/`prepareMarkAsDeleted` inside `batch()` -- never `await` individual operations in a batch)**
</critical_requirements>
---
**Auto-detection:** WatermelonDB, @nozbe/watermelondb, appSchema, tableSchema, @field, @text, @date, @readonly, @json, @nochange, @writer, @reader, @relation, @immutableRelation, @children, @lazy, withObservables, useDatabase, DatabaseProvider, observe, observeWithColumns, synchronize, pullChanges, pushChanges, schemaMigrations, Q.where, Q.on, database.write, database.batch, markAsDeleted, destroyPermanently
**When to use:**
- Building offline-first React Native apps with large local datasets (thousands+ records)
- Defining relational data models with typed fields and relations
- Connecting React components to live-updating database queries
- Syncing local data with a remote server via `synchronize()`
- Migrating database schema across app versions
- Performing bulk operations with `batch()`
**Key patterns covered:**
- Schema definition with `appSchema`/`tableSchema` and column types
- Model classes with field decorators (`@field`, `@text`, `@date`, `@readonly`, `@json`)
- Relations (`@relation`, `@immutableRelation`, `@children`, `@lazy`)
- Writers/readers for safe database mutations and reads
- Reactive components with `withObservables` and `observe()`/`observeWithColumns()`
- Query API with `Q.where`, `Q.on`, `Q.sortBy`, `Q.like`, `Q.oneOf`
- Batch operations for multi-record create/update/delete
- Schema migrations with `schemaMigrations`/`addColumns`/`createTable`
- Sync protocol with `synchronize()`, `pullChanges`, `pushChanges`
**When NOT to use:**
- Simple key-value storage (use a key-value store)
- Apps with small datasets that fit comfortably in memory
- Data that only lives on the server with no offline requirement
- Non-relational storage needs (flat preferences, tokens)
**Detailed Resources:**
- [examples/core.md](examples/core.md) - Schema, models, decorators, CRUD, queries, reactive components
- [examples/sync.md](examples/sync.md) - Sync protocol, migrations, batch operations
- [reference.md](reference.md) - API tables, decision framework, decorator reference
---
<philosophy>
## Philosophy
WatermelonDB is a **reactive, lazy-loading** database built on SQLite for React Native apps that need to handle thousands of records without blocking the JS thread. The key insight: nothing is loaded until requested, and all querying runs on a separate native SQLite thread.
**Core principles:**
1. **Lazy by default** -- records are not loaded into JS memory until accessed. A collection with 10,000 records costs nothing until you query it.
2. **Reactive** -- `observe()` and `withObservables` push updates to components automatically when underlying data changes. No manual refetching.
3. **Schema-first** -- define your database structure with `appSchema`/`tableSchema`, then create Model classes that map to those tables via decorators.
4. **Writers enforce safety** -- all mutations must go through `@writer` or `database.write()`. This guarantees mutual exclusion -- only one writer runs at a time, preventing race conditions.
5. **Sync-ready** -- built-in `synchronize()` handles pull/push with conflict resolution, designed for offline-first architectures.
**Performance characteristics:**
| Scenario | Behavior |
| ------------------------- | --------------------------------------------------- |
| 10,000 records in a table | Zero JS cost until queried |
| Complex query | Runs on native SQLite thread, resolves instantly |
| List re-rendering | `observe()` emits only when matching records change |
| Bulk operations | `batch()` groups into single native transaction |
**v0.27+ architecture:** All React helpers consolidated under `@nozbe/watermelondb/react` (replaces `@nozbe/with-observables`, `@nozbe/watermelondb/DatabaseProvider`, `@nozbe/watermelondb/hooks`). v0.28 requires React Native 0.74+ and Node.js 18+.
</philosophy>
---
<patterns>
## Core Patterns
### Pattern 1: Schema Definition
Schemas define the database structure. Column types are `string`, `number`, or `boolean`. Use `isOptional: true` for nullable columns and `isIndexed: true` for query-heavy columns.
```typescript
import { appSchema, tableSchema } from "@nozbe/watermelondb";
export const schema = appSchema({
version: 1,
tables: [
tableSchema({
name: "posts",
columns: [
{ name: "title", type: "string" },
{ name: "body", type: "string" },
{ name: "subtitle", type: "string", isOptional: true },
{ name: "is_pinned", type: "boolean" },
{ name: "created_at", type: "number" }, // dates stored as timestamps
{ name: "author_id", type: "string", isIndexed: true }, // FK
],
}),
tableSchema({
name: "comments",
columns: [
{ name: "body", type: "string" },
{ name: "post_id", type: "string", isIndexed: true },
{ name: "author_id", type: "string", isIndexed: true },
],
}),
],
});
```
**Why good:** `isIndexed` on foreign keys speeds up relation queries, dates use `number` type (unix timestamps), snake_case naming follows convention
**Naming conventions:** Tables are plural snake*case (`posts`, `blog_comments`). Columns are snake_case. FKs use `_id` suffix. Booleans use `is*`prefix. Date columns use`\_at` suffix.
See [examples/core.md](examples/core.md) for full schema with all column types.
---
### Pattern 2: Model with Field Decorators
Models are classes extending `Model` that map to schema tables. Decorators bind properties to columns.
```typescript
import { Model } from "@nozbe/watermelondb";
import {
field,
text,
date,
readonly,
json,
nochange,
relation,
children,
immutableRelation,
} from "@nozbe/watermelondb/decorators";
const sanitizeTags = (raw: unknown) =>
Array.isArray(raw) ? raw.map(String) : [];
class Post extends Model {
static table = "posts";
static associations = {
comments: { type: "has_many" as const, foreignKey: "post_id" },
};
@text("title") title!: string;
@text("body") body!: string;
@field("is_pinned") isPinned!: boolean;
@date("created_at") createdAt!: Date;
@readonly @date("updated_at") updatedAt!: Date;
@json("tags", sanitizeTags) tags!: string[];
@nochange @field("author_id") authorId!: string;
@immutableRelation("users", "author_id") author!: Relation<User>;
@children("comments") comments!: Query<Comment>;
}
```
**Why good:** `@text` trims whitespace (for user input), `@date` converts timestamps to Date objects, `@readonly` prevents any assignment, `@nochange` prevents modification after creation, `@json` with sanitizer validates parsed data
**Key decorator rules:**
- `@field` -- raw column value (string/number/boolean), guaranteed to match schema type
- `@text` -- like `@field` but trims whitespace, use for user-editable text
- `@date` -- converts stored unix timestamp to JS `Date` object
- `@readonly` -- cannot be set at all (server-set fields in sync)
- `@nochange` -- can be set in `create()` but not in `update()`
- `@json(column, sanitizer)` -- parses JSON from string column, sanitizer validates the parsed output
See [examples/core.md](examples/core.md) for the complete decorator reference with good/bad examples.
---
### Pattern 3: Relations
Use `@relation` for mutable to-one relationships, `@immutableRelation` for to-one that never changes, and `@children` for to-many (returns a `Query`).
```typescript
class Comment extends Model {
static table = "comments";
// Immutable -- a comment's post never changes
@immutableRelation("posts", "post_id") post!: Relation<Post>;
// Mutable -- assignee can be reassigned
@relation("users", "assignee_id") assignee!: Relation<User>;
// To-many -- all replies to this comment
@children("replies") replies!: Query<Reply>;
}
```
**When to use `@immutableRelation`:** When the FK is set once at creation and never changes (comment belongs to post, order belongs to user). Provides extra protection and performance.
**When to use `@relation`:** When the FK can be reassigned (task assignee, category).
See [examples/core.md](examples/core.md) for relation API methods (`.set()`, `.id`, `.fetch()`, `.observe()`) and many-to-many via pivot tables.
---
### Pattern 4: Writers, Readers, and Actions
All database modifications MUST go through a `@writer` or `database.write()`. Readers ensure consistent reads with mutual exclusion.
```typescript
class Post extends Model {
static table = "posts";
@writer async addComment(body: string, author: User) {
return await this.collections.get<Comment>("comments").create((comment) => {
comment.post.set(this);
comment.author.set(author);
comment.body = body;
});
}
@writer async markAsPinned() {
await this.update((post) => {
post.isPinned = true;
});
}
@writer async softDelete() {
await this.markAsDeleted(); // Marks for sync, keeps in DB
}
@reader async fetchActiveComments() {
return await this.comments.extend(Q.where("is_active", true)).fetch();
}
}
```
**Why good:** `@writer` guarantees mutual exclusion (only one writer at a time), `@reader` prevents writes during multi-step reads, `markAsDeleted` preserves record for sync
**Key rules:**
- `@writer` methods can create, update, delete records
- `@reader` methods can only read (fetch, count)
- Writers/readers are async and return Promises
- Only one writer runs at a time -- others queue
- Use `callWriter()`/`callReader()` to call other action methods from within a writer/reader
See [examples/core.md](examples/core.md) for `database.write()` standalone usage and nesting rules.
---
### Pattern 5: Reactive Components with withObservables
Connect components to live database data using `withObservables` from `@nozbe/watermelondb/react`. Components re-render automatically when observed data changes.
```tsx
import { withObservables } from "@nozbe/watermelondb/react";
interface PostItemProps {
post: Post;
commentCount: number;
}
function PostItem({ post, commentCount }: PostItemProps) {
return (
<View>
<Text>{post.title}</Text>
<Text>{commentCount} comments</Text>
</View>
);
}
const enhance = withObservables(["post"], ({ post }: { post: Post }) => ({
post: post.observe(),
commentCount: post.comments.observeCount(),
}));
const EnhancedPostItem = enhance(PostItem);
```
**Why good:** Component re-renders only when the specific post or its comment count changes, not on any database change
**`observe()` vs `observeWithColumns()`:**
- `observe()` -- emits when the record itself changes, or when query results add/remove records
- `observeWithColumns(["column_a", "column_b"])` -- also emits when matched records change specified columns (use for sorted lists)
See [examples/core.md](examples/core.md) for `DatabaseProvider`, `useDatabase`, sorted lists with `observeWithColumns`, and composition patterns.
---
### Pattern 6: Query API
Queries are built with `Q` conditions and executed with `fetch()`, `observe()`, `fetchCount()`, or `observeCount()`.
```typescript
import { Q } from "@nozbe/watermelondb";
const RECENT_DAYS = 7;
const cutoff = Date.now() - RECENT_DAYS * 24 * 60 * 60 * 1000;
// Basic conditions
const recentPosts = await database
.get<Post>("posts")
.query(
Q.where("created_at", Q.gt(cutoff)),
Q.where("is_pinned", true),
Q.sortBy("created_at", Q.desc),
Q.take(20),
)
.fetch();
// Cross-table JOIN with Q.on
const postsWithActiveComments = await database
.get<Post>("posts")
.query(Q.on("comments", "is_active", true))
.fetch();
```
**Key operators:** `Q.eq`, `Q.notEq`, `Q.gt`, `Q.gte`, `Q.lt`, `Q.lte`, `Q.between`, `Q.oneOf`, `Q.notIn`, `Q.like`, `Q.notLike`, `Q.and`, `Q.or`, `Q.on`, `Q.sortBy`, `Q.take`, `Q.skip`
**Gotcha:** `Q.like` uses `%` for wildcards and is case-insensitive. Always use `Q.sanitizeLikeString()` on user input to escape special characters.
See [examples/core.md](examples/core.md) for the full query API with complex conditions and text search.
---
### Pattern 7: Batch Operations
Use `batch()` to group multiple operations into a single native transaction. Use `prepare*` methods (not `await`ed individual operations).
```typescript
@writer async importPosts(rawPosts: RawPost[]) {
const postsCollection = this.collections.get<Post>("posts");
const prepared = rawPosts.map((raw) =>
postsCollection.prepareCreate((post) => {
post.title = raw.title;
post.body = raw.body;
}),
);
await this.batch(...prepared);
}
```
**Why good:** Single native transaction is atomic and much faster than individual creates. Falsy values in `batch()` are ignored (useful for conditional operations).
**Prepare methods:** `collection.prepareCreate()`, `record.prepareUpdate()`, `record.prepareMarkAsDeleted()`, `record.prepareDestroyPermanently()`
See [examples/sync.md](examples/sync.md) for batch patterns with mixed create/update/delete.
---
### Pattern 8: Schema Migrations
Evolve your database schema across app versions. Each migration step increments `toVersion` and applies changes.
```typescript
import {
schemaMigrations,
addColumns,
createTable,
} from "@nozbe/watermelondb/Schema/migrations";
export const migrations = schemaMigrations({
migrations: [
{
toVersion: 2,
steps: [
addColumns({
table: "posts",
columns: [{ name: "subtitle", type: "string", isOptional: true }],
}),
],
},
{
toVersion: 3,
steps: [
createTable({
name: "tags",
columns: [
{ name: "name", type: "string" },
{ name: "post_id", type: "string", isIndexed: true },
],
}),
],
},
],
});
```
**Critical rule:** Schema `version` must equal the highest migration `toVersion`. If schema is version 3, you need migrations up to `toVersion: 3`.
See [examples/sync.md](examples/sync.md) for migration strategies and the relationship between schema version and sync.
---
### Pattern 9: Sync with synchronize()
Built-in sync engine for offline-first architectures. Implement `pullChanges` and `pushChanges` to connect to your backend.
```typescript
import { synchronize } from "@nozbe/watermelondb/sync";
async function syncDatabase(database: Database) {
await synchronize({
database,
pullChanges: async ({ lastPulledAt, schemaVersion, migration }) => {
const response = await fetch(
`https://api.example.com/sync/pull?last=${lastPulledAt}&schema=${schemaVersion}`,
);
const { changes, timestamp } = await response.json();
return { changes, timestamp };
},
pushChanges: async ({ changes, lastPulledAt }) => {
await fetch("https://api.example.com/sync/push", {
method: "POST",
body: JSON.stringify({ changes, lastPulledAt }),
});
},
migrationsEnabledAtVersion: 1,
});
}
```
**Key constraints:**
- `pullChanges` returns `{ changes, timestamp }` where `changes` has `{ created: [], updated: [], deleted: [] }` per table
- `pushChanges` receives local changes in the same format
- Server must provide a consistent snapshot (use transactions or read locks)
- `migrationsEnabledAtVersion` enables schema-aware sync
See [examples/sync.md](examples/sync.md) for the complete sync protocol, conflict resolution, and error handling.
</patterns>
---
<decision_framework>
## Decision Framework
```
What kind of local data do you need?
|
+-> Simple key-value pairs (preferences, tokens)?
| +-> Use a key-value store (not WatermelonDB)
|
+-> Relational data with queries?
| +-> Small dataset (<100 records) with no offline sync?
| | +-> Consider simpler storage first
| +-> Large dataset (1000+ records) or offline-first?
| +-> WatermelonDB
|
+-> Need offline sync with a server?
| +-> WatermelonDB with synchronize()
|
+-> Only server data, always online?
+-> Use your data fetching solution (not WatermelonDB)
```
### When to Use Each API
| Scenario | API |
| ----------------------------- | ------------------------------------------------ |
| Define database structure | `appSchema`/`tableSchema` |
| Map columns to properties | `@field`, `@text`, `@date`, `@json` |
| Prevent field modification | `@readonly` (never set), `@nochange` (set once) |
| One-to-one relation (fixed) | `@immutableRelation` |
| One-to-one relation (mutable) | `@relation` |
| One-to-many relation | `@children` |
| Create/update/delete records | `@writer` method or `database.write()` |
| Consistent multi-step reads | `@reader` method or `database.read()` |
| Bulk create/update/delete | `batch()` with `prepare*` methods |
| Reactive component data | `withObservables` + `observe()` |
| Reactive sorted list | `observeWithColumns(["sort_column"])` |
| Access database in component | `useDatabase()` from `@nozbe/watermelondb/react` |
| Evolve schema across versions | `schemaMigrations` + `addColumns`/`createTable` |
| Sync with remote server | `synchronize()` with `pullChanges`/`pushChanges` |
</decision_framework>
---
<red_flags>
## RED FLAGS
**High Priority Issues:**
- Modifying records outside a `@writer` or `database.write()` -- throws at runtime, all mutations require a writer context
- Schema version and migration `toVersion` out of sync -- causes database corruption or failed migrations
- Using `await collection.create()` inside `batch()` -- use `collection.prepareCreate()` (no await) for batch operations
- Missing `static associations` on Model classes -- relations and `Q.on` queries will not work without declared associations
- Importing from `@nozbe/with-observables` (v0.27+ moved everything to `@nozbe/watermelondb/react`)
**Medium Priority Issues:**
- Using `@relation` when the FK never changes after creation -- use `@immutableRelation` for safety and performance
- Not indexing foreign key columns (`isIndexed: true`) -- relation queries become slow on large tables
- Calling `destroyPermanently()` on synced records -- use `markAsDeleted()` so deletions sync to the server
- Storing large blobs (>1MB) in WatermelonDB -- SQLite is not optimized for large binary data, use the filesystem
- Not using `Q.sanitizeLikeString()` on user input in `Q.like()` queries -- special characters break the query
**Gotchas & Edge Cases:**
- Column defaults: string defaults to `""`, number to `0`, boolean to `false` -- use `isOptional: true` if `null` is a valid state
- `@json` fields cannot be queried or counted by their contents -- they are opaque string columns
- `@date` stores unix timestamps (milliseconds) in a `number` column but returns a JS `Date` object -- schema column must be `number` type
- `observe()` on a query emits when records are added/removed but NOT when existing records change fields -- use `observeWithColumns()` for field-level reactivity
- `markAsDeleted()` keeps the record in the local database (flagged for sync) -- `destroyPermanently()` actually removes it
- `callWriter()`/`callReader()` are required to call other `@writer`/`@reader` methods from within a writer/reader -- direct calls throw
- Many-to-many relationships require a pivot table with `@immutableRelation` on both sides
- `Q.gt(0)` excludes `null` values -- use `Q.weakGt(0)` if nulls should be included
- The `id` column is auto-generated (string UUID) -- never declare it in your schema
- `_status` and `_changed` columns are reserved for the sync engine -- never use these names
- v0.28 requires React Native 0.74+ and Node.js 18+
</red_flags>
---
<critical_reminders>
## CRITICAL REMINDERS
> **All code must follow project conventions in CLAUDE.md**
**(You MUST wrap ALL database modifications in `@writer` methods or `database.write()` -- writes outside a writer throw at runtime)**
**(You MUST keep schema version and migration `toVersion` in sync -- migrations cannot be newer than the schema version)**
**(You MUST use `@immutableRelation` for relations that never change after creation -- it provides extra safety and performance over `@relation`)**
**(You MUST use `prepareCreate`/`prepareUpdate`/`prepareMarkAsDeleted` inside `batch()` -- never `await` individual operations in a batch)**
**Failure to follow these rules will cause runtime crashes, data corruption, or silent sync failures.**
</critical_reminders>