Claude Skill

mobile-storage-watermelondb

WatermelonDB reactive local database for React Native - schema, models, decorators, reactive queries, relations, writers/readers, batch operations, migrations, sync

LLM Mart · 0 points · 2 views 0 listing impressions 0 install-command copies
Download agents-inc-skills-dist_plugins_mobile-storage-watermelondb_skills_mobile-storage-watermelondb-707467a.zip · 18 KB

Install

skills CLI npx skills add https://github.com/agents-inc/skills/tree/main/dist/plugins/mobile-storage-watermelondb/skills/mobile-storage-watermelondb
Claude Code claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install agents-inc-skills@llmmart
Git git clone https://github.com/agents-inc/skills.git

The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole agents-inc/skills collection as a plugin from our marketplace. Git is the plain clone.

Skill manifest

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

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




<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

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

Files (skills)
  • examples
    • core.md 15.2 KB
      Loading…
    • sync.md 8.9 KB
      Loading…
  • reference.md 10.3 KB
    Loading…
  • SKILL.md 21.5 KB
    ---
    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
    
    **(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
    
    **(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>
    

Comments (0)

Sign in to join the conversation.

No comments yet.

Reviews (0)

No reviews yet.