agents-inc/mobile-storage-watermelondb

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 - Schema, models, decorators, CRUD, queries, reactive components
• examples/sync.md - Sync protocol, migrations, batch operations
• 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.

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

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 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).

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

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

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 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().

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 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 awaited individual operations).

@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 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.

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

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