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 · 0 views 0 listing impressions 0 install-command copies
Virus-scanned Reviewed automatically before listing.

Full trust report

Download agents-inc-skills-dist_plugins_mobile-storage-watermelondb_skills_mobile-storage-watermelondb-3a51ef5.zip · 18 KB
agents-inc/skills 24 8 forks MIT Updated 21d ago
Part of agents-inc/skills — 130 skills

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

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:

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

Files (skills)
  • examples
    • core.md 15.2 KB 
      # WatermelonDB - Core Patterns

> Schema, models, decorators, CRUD, queries, and reactive components. See [SKILL.md](../SKILL.md) for decision guidance and red flags.

**Prerequisites:** React Native 0.74+, `@nozbe/watermelondb` v0.27+, decorator support (Babel plugin `@nozbe/watermelondb/babel/plugin`).

---

## Pattern 1: Database Setup

```typescript
import { Database } from "@nozbe/watermelondb";
import SQLiteAdapter from "@nozbe/watermelondb/adapters/sqlite";
import { schema } from "./schema";
import { migrations } from "./migrations";
import { Post } from "./models/post";
import { Comment } from "./models/comment";
import { User } from "./models/user";

const adapter = new SQLiteAdapter({
  schema,
  migrations,
  jsi: true, // Recommended for iOS; enables synchronous native bridge
  // dbName: "myapp",       // Custom database name (optional)
  // onSetUpError: (error) => { /* handle DB load failure */ },
});

export const database = new Database({
  adapter,
  modelClasses: [Post, Comment, User],
});
```

**Why good:** JSI mode enables synchronous bridge for faster operations on iOS, migrations are passed to adapter for automatic schema evolution, model classes are registered centrally

```typescript
// BAD: Creating database inside a component
function App() {
  const db = new Database({ adapter, modelClasses: [Post] }); // New DB every render
  return <DatabaseProvider database={db}>...</DatabaseProvider>;
}
```

**Why bad:** Creates a new database connection on every render, leaking native resources. Database must be a module-level singleton.

---

## Pattern 2: Schema Definition with All Column Types

```typescript
import { appSchema, tableSchema } from "@nozbe/watermelondb";

const SCHEMA_VERSION = 3;

export const schema = appSchema({
  version: SCHEMA_VERSION,
  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: "is_published", type: "boolean" },
        { name: "like_count", type: "number" },
        { name: "created_at", type: "number" },
        { name: "updated_at", type: "number" },
        { name: "author_id", type: "string", isIndexed: true },
      ],
    }),
    tableSchema({
      name: "comments",
      columns: [
        { name: "body", type: "string" },
        { name: "is_active", type: "boolean" },
        { name: "post_id", type: "string", isIndexed: true },
        { name: "author_id", type: "string", isIndexed: true },
        { name: "created_at", type: "number" },
      ],
    }),
    tableSchema({
      name: "users",
      columns: [
        { name: "username", type: "string" },
        { name: "email", type: "string" },
        { name: "avatar_url", type: "string", isOptional: true },
        { name: "is_admin", type: "boolean" },
      ],
    }),
  ],
});
```

**Naming conventions:**

- Tables: plural snake_case (`posts`, `blog_comments`)
- Columns: snake_case (`created_at`, `author_id`)
- Foreign keys: `_id` suffix (`post_id`, `author_id`)
- Booleans: `is_` prefix (`is_pinned`, `is_active`)
- Dates: `_at` suffix with `number` type (`created_at`, `updated_at`)
- `id` column is auto-generated -- never declare it

**Column defaults:** `string` defaults to `""`, `number` to `0`, `boolean` to `false`. Use `isOptional: true` to allow `null`.

---

## Pattern 3: Model with All Decorator Types

```typescript
import { Model } from "@nozbe/watermelondb";
import {
  field,
  text,
  date,
  readonly,
  nochange,
  json,
  relation,
  immutableRelation,
  children,
  writer,
  reader,
} from "@nozbe/watermelondb/decorators";
import { Q } from "@nozbe/watermelondb";
import type { Query, Relation } from "@nozbe/watermelondb";

const sanitizeTags = (raw: unknown): string[] =>
  Array.isArray(raw) ? raw.map(String) : [];

export class Post extends Model {
  static table = "posts";

  // Declare associations for relations and Q.on queries
  static associations = {
    comments: { type: "has_many" as const, foreignKey: "post_id" },
  } as const;

  // --- Field decorators ---
  @text("title") title!: string; // Trims whitespace
  @text("body") body!: string;
  @field("is_pinned") isPinned!: boolean; // Raw column value
  @field("is_published") isPublished!: boolean;
  @field("like_count") likeCount!: number;

  // --- Date decorators ---
  @date("created_at") createdAt!: Date; // Converts timestamp -> Date
  @readonly @date("updated_at") updatedAt!: Date; // Cannot be set at all

  // --- Constraint decorators ---
  @nochange @field("author_id") authorId!: string; // Set once in create()

  // --- JSON decorator ---
  @json("tags", sanitizeTags) tags!: string[]; // Parses JSON from string column

  // --- Relations ---
  @immutableRelation("users", "author_id") author!: Relation<User>; // Never changes
  @children("comments") comments!: Query<Comment>; // To-many

  // --- Actions ---
  @writer async togglePin() {
    await this.update((post) => {
      post.isPinned = !post.isPinned;
    });
  }

  @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 softDelete() {
    await this.markAsDeleted();
  }

  @reader async fetchCommentCount() {
    return await this.comments.fetchCount();
  }
}
```

**Why good:** Each decorator communicates intent clearly -- `@text` for user-editable fields, `@readonly` for server-controlled timestamps, `@nochange` for create-once FKs, `@json` with sanitizer for validated complex data

```typescript
// BAD: Using @field for user-editable text
class Post extends Model {
  @field("title") title!: string; // Does NOT trim whitespace
  @field("body") body!: string;
}
```

**Why bad:** `@field` does not trim whitespace -- user input with leading/trailing spaces gets stored as-is. Use `@text` for user-editable fields.

```typescript
// BAD: Using @relation for a fixed FK
class Comment extends Model {
  @relation("posts", "post_id") post!: Relation<Post>; // Mutable -- but it never changes
}
```

**Why bad:** A comment's post never changes. Using `@relation` loses the immutability guarantee and performance optimization of `@immutableRelation`.

---

## Pattern 4: Relation API Methods

```typescript
// --- Reading relations ---
// .fetch() returns a Promise of the related record
const author = await comment.author.fetch();

// .observe() returns an Observable for reactive UI
const author$ = comment.author.observe();

// .id returns just the FK value (no database lookup)
const authorId = comment.author.id;

// --- Setting relations (inside create/update only) ---
@writer async reassignComment(newAuthor: User) {
  await comment.update(() => {
    comment.assignee.set(newAuthor);    // Set by record
    // OR: comment.assignee.id = newAuthor.id;  // Set by ID
  });
}
```

### Many-to-Many via Pivot Table

```typescript
// Pivot model
class PostTag extends Model {
  static table = "post_tags";
  static associations = {
    posts: { type: "belongs_to" as const, key: "post_id" },
    tags: { type: "belongs_to" as const, key: "tag_id" },
  } as const;

  @immutableRelation("posts", "post_id") post!: Relation<Post>;
  @immutableRelation("tags", "tag_id") tag!: Relation<Tag>;
}

// On Post model -- query tags through pivot
class Post extends Model {
  @lazy tags = this.collections
    .get<PostTag>("post_tags")
    .query(Q.where("post_id", this.id))
    .extend(Q.on("tags", Q.where("is_active", true)));
}
```

**Why good:** `@immutableRelation` on both sides of pivot enforces that tagging relationships are permanent. `@lazy` creates a computed query property that is only evaluated when accessed.

---

## Pattern 5: Writers, Readers, and database.write()

### Standalone database.write()

When actions don't belong on a model class:

```typescript
import type { Database } from "@nozbe/watermelondb";

async function createPostWithComments(
  database: Database,
  title: string,
  commentBodies: string[],
  author: User,
) {
  await database.write(async () => {
    const post = await database.get<Post>("posts").create((p) => {
      p.title = title;
      p.author.set(author);
    });

    // Create all comments in the same writer -- one transaction
    for (const body of commentBodies) {
      await database.get<Comment>("comments").create((c) => {
        c.post.set(post);
        c.author.set(author);
        c.body = body;
      });
    }
  });
}
```

### Nesting Writers with callWriter

```typescript
class Post extends Model {
  @writer async publishWithNotification() {
    await this.update((post) => {
      post.isPublished = true;
    });
    // Call another @writer from within this writer
    await this.callWriter(() => this.addComment("Auto-published", systemUser));
  }
}
```

**Gotcha:** Calling a `@writer` method directly from within another writer throws. You MUST use `this.callWriter()` or `this.callReader()` for nesting.

### database.read() for Consistent Reads

```typescript
const stats = await database.read(async () => {
  // No writes can happen during this block
  const postCount = await database.get<Post>("posts").query().fetchCount();
  const commentCount = await database
    .get<Comment>("comments")
    .query()
    .fetchCount();
  return { postCount, commentCount }; // Consistent snapshot
});
```

---

## Pattern 6: Query API

### Basic Conditions

```typescript
import { Q } from "@nozbe/watermelondb";

// Equality (shorthand)
const pinnedPosts = await postsCollection
  .query(Q.where("is_pinned", true))
  .fetch();

// Comparison operators
const popularPosts = await postsCollection
  .query(Q.where("like_count", Q.gt(100)))
  .fetch();

// Multiple conditions (implicitly AND)
const recentPopular = await postsCollection
  .query(
    Q.where("like_count", Q.gte(50)),
    Q.where("created_at", Q.gt(cutoffTimestamp)),
  )
  .fetch();

// OR conditions
const flagged = await postsCollection
  .query(Q.or(Q.where("is_pinned", true), Q.where("like_count", Q.gt(1000))))
  .fetch();
```

### Text Search with Q.like

```typescript
const searchTerm = Q.sanitizeLikeString(userInput); // Escape special chars
const results = await postsCollection
  .query(Q.where("title", Q.like(`%${searchTerm}%`)))
  .fetch();
```

**Gotcha:** Always use `Q.sanitizeLikeString()` on user input. `Q.like` uses `%` for wildcards and is case-insensitive.

### Sorting and Pagination

```typescript
const TOP_POSTS_LIMIT = 10;

const topPosts = await postsCollection
  .query(
    Q.where("is_published", true),
    Q.sortBy("like_count", Q.desc),
    Q.take(TOP_POSTS_LIMIT),
  )
  .fetch();
```

### Cross-Table Queries with Q.on

```typescript
// Posts that have at least one active comment
const postsWithActiveComments = await postsCollection
  .query(Q.on("comments", "is_active", true))
  .fetch();

// Posts with comments containing a specific word
const postsWithKeyword = await postsCollection
  .query(
    Q.on(
      "comments",
      Q.where("body", Q.like(`%${Q.sanitizeLikeString(keyword)}%`)),
    ),
  )
  .fetch();
```

### Execution Methods

```typescript
const posts = await query.fetch(); // Model[]
const count = await query.fetchCount(); // number
const ids = await query.fetchIds(); // string[]
const stream$ = query.observe(); // Observable<Model[]>
const count$ = query.observeCount(); // Observable<number> (throttled 250ms)
const sorted$ = query.observeWithColumns(["like_count"]); // Re-emits on column change
```

---

## Pattern 7: Reactive Components

### DatabaseProvider and useDatabase

```tsx
import { DatabaseProvider, useDatabase } from "@nozbe/watermelondb/react";
import { database } from "./database";

// Wrap app in provider
function App() {
  return (
    <DatabaseProvider database={database}>
      <Root />
    </DatabaseProvider>
  );
}

// Access database in any descendant
function CreatePostButton() {
  const database = useDatabase();

  const handlePress = async () => {
    await database.write(async () => {
      await database.get<Post>("posts").create((post) => {
        post.title = "New Post";
      });
    });
  };

  return <Button onPress={handlePress} title="Create Post" />;
}
```

### withObservables for Reactive Lists

```tsx
import { withObservables } from "@nozbe/watermelondb/react";
import { Q } from "@nozbe/watermelondb";
import type { Database } from "@nozbe/watermelondb";

interface PostListProps {
  posts: Post[];
}

function PostList({ posts }: PostListProps) {
  return (
    <FlatList
      data={posts}
      keyExtractor={(item) => item.id}
      renderItem={({ item }) => <EnhancedPostItem post={item} />}
    />
  );
}

const enhance = withObservables([], ({ database }: { database: Database }) => ({
  posts: database
    .get<Post>("posts")
    .query(Q.where("is_published", true), Q.sortBy("created_at", Q.desc))
    .observe(),
}));

export const EnhancedPostList = enhance(PostList);
```

### observeWithColumns for Sorted Lists

```tsx
// If the list is sorted by a field that can change, use observeWithColumns
const enhance = withObservables([], ({ database }: { database: Database }) => ({
  posts: database
    .get<Post>("posts")
    .query(Q.sortBy("like_count", Q.desc))
    .observeWithColumns(["like_count"]), // Re-emits when like_count changes
}));
```

**Why this matters:** `observe()` only emits when records are added or removed from query results. If a record's `like_count` changes (affecting sort order), `observe()` won't emit -- but `observeWithColumns(["like_count"])` will.

### Composing withObservables for Deep Relations

```tsx
import { compose } from "@nozbe/watermelondb/react";

// First level: observe the post and its author relation
const enhanceComment = compose(
  withObservables(["comment"], ({ comment }: { comment: Comment }) => ({
    comment: comment.observe(),
    author: comment.author.observe(),
  })),
);

// For 2nd-level relations, use RxJS switchMap:
import { switchMap } from "rxjs/operators";

const enhance = withObservables(["post"], ({ post }: { post: Post }) => ({
  post: post.observe(),
  authorContact: post.author
    .observe()
    .pipe(switchMap((author) => author.contact.observe())),
}));
```

---

## Pattern 8: CRUD Operations Summary

### Create

```typescript
@writer async createPost(title: string, body: string, author: User) {
  return await this.collections.get<Post>("posts").create((post) => {
    post.title = title;
    post.body = body;
    post.author.set(author);
  });
}
```

### Read

```typescript
// By ID
const post = await database.get<Post>("posts").find("some-id");

// By query
const posts = await database
  .get<Post>("posts")
  .query(Q.where("is_pinned", true))
  .fetch();
```

### Update

```typescript
@writer async updateTitle(newTitle: string) {
  await this.update((post) => {
    post.title = newTitle;
  });
}
```

### Delete

```typescript
// For synced databases -- marks for sync, keeps locally
@writer async softDelete() {
  await this.markAsDeleted();
}

// For local-only databases -- permanently removes
@writer async hardDelete() {
  await this.destroyPermanently();
}
```

**Key distinction:** `markAsDeleted()` flags the record with `_status: 'deleted'` so the sync engine can push the deletion to the server. `destroyPermanently()` removes the record from SQLite entirely.
    • sync.md 8.9 KB 
      # WatermelonDB - Sync, Migrations, and Batch Operations

> Sync protocol, schema migrations, and bulk data operations. See [SKILL.md](../SKILL.md) for decision guidance and red flags. See [core.md](core.md) for schema, model, and query patterns.

---

## Pattern 1: Schema Migrations

Migrations evolve your database schema across app versions. Each migration step has a `toVersion` and an array of `steps`.

```typescript
import {
  schemaMigrations,
  addColumns,
  createTable,
} from "@nozbe/watermelondb/Schema/migrations";

export const migrations = schemaMigrations({
  migrations: [
    {
      // v1 -> v2: Add subtitle to posts
      toVersion: 2,
      steps: [
        addColumns({
          table: "posts",
          columns: [{ name: "subtitle", type: "string", isOptional: true }],
        }),
      ],
    },
    {
      // v2 -> v3: Add tags table and is_featured to posts
      toVersion: 3,
      steps: [
        createTable({
          name: "tags",
          columns: [
            { name: "name", type: "string" },
            { name: "color", type: "string" },
            { name: "post_id", type: "string", isIndexed: true },
          ],
        }),
        addColumns({
          table: "posts",
          columns: [{ name: "is_featured", type: "boolean" }],
        }),
      ],
    },
  ],
});
```

**Critical rules:**

- Schema `version` must match the highest migration `toVersion` -- if you add `toVersion: 3`, set schema `version: 3`
- Migrations cannot be newer than schema -- the adapter checks this on startup
- Migrations are run sequentially from the user's current version to the latest
- You can never remove or modify past migrations -- they are permanent history
- Each `toVersion` must be exactly one more than the previous

```typescript
// BAD: Schema version doesn't match migrations
const schema = appSchema({ version: 2, tables: [...] });  // version 2
const migrations = schemaMigrations({
  migrations: [{ toVersion: 3, steps: [...] }],  // migration to version 3!
});
```

**Why bad:** Migration `toVersion: 3` exceeds schema `version: 2`. The adapter will throw on startup. Always bump schema version to match the highest migration.

---

## Pattern 2: Batch Operations

Use `batch()` to group multiple operations into a single native SQLite transaction. Always use `prepare*` methods (without `await`) inside batch.

### Batch Create

```typescript
import type { Database } from "@nozbe/watermelondb";

async function importPosts(
  database: Database,
  rawPosts: RawPost[],
  author: User,
) {
  await database.write(async () => {
    const postsCollection = database.get<Post>("posts");
    const prepared = rawPosts.map((raw) =>
      postsCollection.prepareCreate((post) => {
        post.title = raw.title;
        post.body = raw.body;
        post.author.set(author);
      }),
    );
    await database.batch(...prepared);
  });
}
```

### Mixed Batch (Create + Update + Delete)

```typescript
@writer async reorganizeCategory(
  newPosts: RawPost[],
  outdatedPosts: Post[],
  stalePosts: Post[],
) {
  const postsCollection = this.collections.get<Post>("posts");

  const creates = newPosts.map((raw) =>
    postsCollection.prepareCreate((post) => {
      post.title = raw.title;
      post.body = raw.body;
    }),
  );

  const updates = outdatedPosts.map((post) =>
    post.prepareUpdate((p) => {
      p.isPublished = false;
    }),
  );

  const deletes = stalePosts.map((post) => post.prepareMarkAsDeleted());

  await this.batch(...creates, ...updates, ...deletes);
}
```

**Why good:** Single native transaction is atomic (all or nothing), much faster than individual awaited operations, and falsy values in `batch()` are safely ignored.

```typescript
// BAD: Awaiting individual operations in a loop
@writer async importPosts(rawPosts: RawPost[]) {
  for (const raw of rawPosts) {
    await this.collections.get<Post>("posts").create((post) => {
      post.title = raw.title;
    });
  }
}
```

**Why bad:** Each `create` is a separate native transaction. For 100 records, that's 100 round-trips to native instead of 1 with `batch()`.

### Conditional Batch Items

```typescript
// Falsy values are ignored -- useful for conditional operations
await this.batch(
  postsCollection.prepareCreate((p) => {
    p.title = "Always created";
  }),
  shouldPin
    ? existingPost.prepareUpdate((p) => {
        p.isPinned = true;
      })
    : null,
  shouldDeleteOld ? oldPost.prepareMarkAsDeleted() : null,
);
```

---

## Pattern 3: Sync Protocol with synchronize()

The built-in sync engine handles bidirectional data synchronization. Implement `pullChanges` to fetch server changes and `pushChanges` to send local changes.

### Basic Sync Setup

```typescript
import { synchronize } from "@nozbe/watermelondb/sync";
import type { Database } from "@nozbe/watermelondb";

const API_BASE_URL = "https://api.example.com";

async function syncDatabase(database: Database) {
  await synchronize({
    database,
    pullChanges: async ({ lastPulledAt, schemaVersion, migration }) => {
      const params = new URLSearchParams({
        last_pulled_at: String(lastPulledAt ?? 0),
        schema_version: String(schemaVersion),
      });

      if (migration) {
        params.set("migration_from", String(migration.from));
        params.set("migration_tables", migration.tables.join(","));
        params.set("migration_columns", JSON.stringify(migration.columns));
      }

      const response = await fetch(`${API_BASE_URL}/sync/pull?${params}`);

      if (!response.ok) {
        throw new Error(`Pull failed: ${response.status}`);
      }

      const { changes, timestamp } = await response.json();
      return { changes, timestamp };
    },
    pushChanges: async ({ changes, lastPulledAt }) => {
      const response = await fetch(`${API_BASE_URL}/sync/push`, {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ changes, lastPulledAt }),
      });

      if (!response.ok) {
        throw new Error(`Push failed: ${response.status}`);
      }
    },
    migrationsEnabledAtVersion: 1,
  });
}
```

### Changes Format

The sync protocol uses a specific changes format per table:

```typescript
// pullChanges must return this shape:
interface SyncPullResult {
  changes: {
    [tableName: string]: {
      created: RawRecord[]; // New records from server
      updated: RawRecord[]; // Modified records from server
      deleted: string[]; // IDs of deleted records
    };
  };
  timestamp: number; // Server's current time (for next sync)
}

// pushChanges receives this shape:
interface SyncPushChanges {
  changes: {
    [tableName: string]: {
      created: RawRecord[]; // Locally created records
      updated: RawRecord[]; // Locally modified records
      deleted: string[]; // Locally deleted record IDs
    };
  };
  lastPulledAt: number;
}
```

**Key constraints:**

- `pullChanges` must return ALL changes since `lastPulledAt` for ALL synced tables
- Raw records use column names (snake_case), not model property names
- The server must provide a consistent snapshot (use database transactions or read locks)
- If `pushChanges` fails, the server must revert all changes (atomic)
- `lastPulledAt` is `null` on first sync

### Sync Configuration Options

```typescript
await synchronize({
  database,
  pullChanges,
  pushChanges,
  migrationsEnabledAtVersion: 1, // Enable schema-aware sync
  sendCreatedAsUpdated: false, // If true, created records go in `updated` array
  // conflictResolver: customResolver,    // Custom conflict resolution
  // log: syncLog,                        // Diagnostic logging object
  // onDidPullChanges: async () => {},    // Callback after pull applied
  // onWillApplyRemoteChanges: async () => {},  // Callback before applying
});
```

### Error Handling in Sync

```typescript
async function syncWithRetry(database: Database) {
  const MAX_RETRIES = 3;
  const RETRY_DELAY_MS = 2000;

  for (let attempt = 1; attempt <= MAX_RETRIES; attempt++) {
    try {
      await syncDatabase(database);
      return; // Success
    } catch (error) {
      if (attempt === MAX_RETRIES) {
        throw error; // Final attempt failed
      }
      await new Promise((resolve) =>
        setTimeout(resolve, RETRY_DELAY_MS * attempt),
      );
    }
  }
}
```

---

## Pattern 4: Diagnostics (v0.27+)

WatermelonDB v0.27 added diagnostic utilities for debugging sync and data integrity issues.

```typescript
import {
  diagnoseDatabaseStructure,
  diagnoseSyncConsistency,
  censorRaw,
} from "@nozbe/watermelondb/diagnostics";

// Find orphaned records and schema inconsistencies
const structureReport = await diagnoseDatabaseStructure(database);

// Compare local vs server state (pass your pull endpoint)
const syncReport = await diagnoseSyncConsistency(database, pullEndpoint);

// Censor raw records for logging (masks values, preserves IDs)
const safeRecord = censorRaw(rawRecord);
```

**When to use:** Debugging sync failures, identifying orphaned records after failed migrations, logging raw records without exposing user data.
  • reference.md 10.3 KB 
    # WatermelonDB Quick Reference

> API reference, decision framework, and decorator cheat sheet. See [SKILL.md](SKILL.md) for red flags and anti-patterns.

---

## Decorator Reference

| Decorator                       | Import       | Purpose                                  | Notes                             |
| ------------------------------- | ------------ | ---------------------------------------- | --------------------------------- |
| `@field(col)`                   | `decorators` | Raw column value (string/number/boolean) | Matches schema column type        |
| `@text(col)`                    | `decorators` | Text with auto-trim                      | Use for user-editable text        |
| `@date(col)`                    | `decorators` | Unix timestamp -> JS Date                | Column must be `number` type      |
| `@readonly`                     | `decorators` | Prevents any assignment                  | Use with `@date` for `updated_at` |
| `@nochange`                     | `decorators` | Set once in `create()` only              | Throws on `update()`              |
| `@json(col, sanitizer)`         | `decorators` | Parse JSON from string column            | Cannot query JSON contents        |
| `@relation(table, fk)`          | `decorators` | Mutable to-one relation                  | FK can be reassigned              |
| `@immutableRelation(table, fk)` | `decorators` | Immutable to-one relation                | FK set once, better perf          |
| `@children(table)`              | `decorators` | To-many relation (Query)                 | Returns Query, not array          |
| `@lazy`                         | `decorators` | Computed query property                  | For complex/M2M queries           |
| `@writer`                       | `decorators` | Method that modifies DB                  | Required for all writes           |
| `@reader`                       | `decorators` | Method for consistent reads              | Prevents writes during execution  |

All decorators import from `@nozbe/watermelondb/decorators`.

---

## Query Operators (Q.\*)

| Operator                  | Example                                        | Purpose                          |
| ------------------------- | ---------------------------------------------- | -------------------------------- |
| `Q.where(col, val)`       | `Q.where("is_pinned", true)`                   | Equality match                   |
| `Q.eq(val)`               | `Q.where("status", Q.eq("active"))`            | Explicit equality                |
| `Q.notEq(val)`            | `Q.where("status", Q.notEq("archived"))`       | Inequality                       |
| `Q.gt(val)`               | `Q.where("count", Q.gt(0))`                    | Greater than (excludes null)     |
| `Q.weakGt(val)`           | `Q.where("count", Q.weakGt(0))`                | Greater than (includes null)     |
| `Q.gte(val)`              | `Q.where("count", Q.gte(10))`                  | Greater than or equal            |
| `Q.lt(val)`               | `Q.where("count", Q.lt(100))`                  | Less than                        |
| `Q.lte(val)`              | `Q.where("count", Q.lte(100))`                 | Less than or equal               |
| `Q.between(a, b)`         | `Q.where("count", Q.between(10, 100))`         | Range                            |
| `Q.oneOf(arr)`            | `Q.where("status", Q.oneOf(["a", "b"]))`       | IN array                         |
| `Q.notIn(arr)`            | `Q.where("status", Q.notIn(["x"]))`            | NOT IN array                     |
| `Q.like(pat)`             | `Q.where("title", Q.like("%search%"))`         | Pattern match (case-insensitive) |
| `Q.notLike(pat)`          | `Q.where("title", Q.notLike("%spam%"))`        | Inverse pattern match            |
| `Q.includes(str)`         | `Q.where("body", Q.includes("keyword"))`       | Substring containment            |
| `Q.column(col)`           | `Q.where("likes", Q.gt(Q.column("dislikes")))` | Column-to-column comparison      |
| `Q.and(...)`              | `Q.and(Q.where(...), Q.where(...))`            | AND conditions                   |
| `Q.or(...)`               | `Q.or(Q.where(...), Q.where(...))`             | OR conditions                    |
| `Q.on(table, ...)`        | `Q.on("comments", "is_active", true)`          | JOIN condition                   |
| `Q.sortBy(col, dir)`      | `Q.sortBy("created_at", Q.desc)`               | Sort (Q.asc / Q.desc)            |
| `Q.take(n)`               | `Q.take(20)`                                   | Limit results                    |
| `Q.skip(n)`               | `Q.skip(10)`                                   | Skip first N                     |
| `Q.sanitizeLikeString(s)` | `Q.sanitizeLikeString(userInput)`              | Escape special chars for Q.like  |

Import: `import { Q } from "@nozbe/watermelondb"`

---

## Query Execution Methods

| Method                      | Returns               | Reactive?                          |
| --------------------------- | --------------------- | ---------------------------------- |
| `.fetch()`                  | `Promise<Model[]>`    | No                                 |
| `.fetchCount()`             | `Promise<number>`     | No                                 |
| `.fetchIds()`               | `Promise<string[]>`   | No                                 |
| `.observe()`                | `Observable<Model[]>` | Yes -- add/remove                  |
| `.observeWithColumns(cols)` | `Observable<Model[]>` | Yes -- add/remove + column changes |
| `.observeCount()`           | `Observable<number>`  | Yes (throttled 250ms)              |

---

## Model Instance Methods

| Method                               | Context   | Purpose                       |
| ------------------------------------ | --------- | ----------------------------- |
| `record.update(builder)`             | `@writer` | Modify record fields          |
| `record.prepareUpdate(builder)`      | `batch()` | Prepare update for batch      |
| `record.markAsDeleted()`             | `@writer` | Soft delete (sync-aware)      |
| `record.prepareMarkAsDeleted()`      | `batch()` | Prepare soft delete for batch |
| `record.destroyPermanently()`        | `@writer` | Hard delete (permanent)       |
| `record.prepareDestroyPermanently()` | `batch()` | Prepare hard delete for batch |
| `record.observe()`                   | any       | Observable of record changes  |
| `collection.create(builder)`         | `@writer` | Create new record             |
| `collection.prepareCreate(builder)`  | `batch()` | Prepare create for batch      |
| `database.get(table)`                | any       | Get collection by table name  |
| `database.batch(...)`                | `@writer` | Execute batch operations      |

---

## Schema Column Types

| Type      | Default | JS Type   | Use For                     |
| --------- | ------- | --------- | --------------------------- |
| `string`  | `""`    | `string`  | Text, IDs, JSON strings     |
| `number`  | `0`     | `number`  | Counts, timestamps, amounts |
| `boolean` | `false` | `boolean` | Flags, toggles              |

Add `isOptional: true` to allow `null`. Add `isIndexed: true` for query-heavy columns (especially FKs).

---

## Migration Steps

| Function        | Purpose                       | Parameters                                  |
| --------------- | ----------------------------- | ------------------------------------------- |
| `addColumns()`  | Add columns to existing table | `{ table, columns }`                        |
| `createTable()` | Create new table              | `{ name, columns }` (same as `tableSchema`) |

Import: `import { schemaMigrations, addColumns, createTable } from "@nozbe/watermelondb/Schema/migrations"`

---

## Sync API

| Parameter                    | Type                                                                           | Required | Purpose                            |
| ---------------------------- | ------------------------------------------------------------------------------ | -------- | ---------------------------------- |
| `database`                   | `Database`                                                                     | Yes      | WatermelonDB instance              |
| `pullChanges`                | `async ({ lastPulledAt, schemaVersion, migration }) => { changes, timestamp }` | Yes      | Fetch server changes               |
| `pushChanges`                | `async ({ changes, lastPulledAt }) => void`                                    | No       | Send local changes                 |
| `migrationsEnabledAtVersion` | `number`                                                                       | No       | Enable schema-aware sync           |
| `sendCreatedAsUpdated`       | `boolean`                                                                      | No       | Created records in `updated` array |
| `conflictResolver`           | `function`                                                                     | No       | Custom conflict resolution         |
| `log`                        | `object`                                                                       | No       | Diagnostic logging                 |

Import: `import { synchronize } from "@nozbe/watermelondb/sync"`

---

## React Helpers

All from `@nozbe/watermelondb/react`:

| Export                          | Purpose                                       |
| ------------------------------- | --------------------------------------------- |
| `DatabaseProvider`              | Context provider for database instance        |
| `useDatabase()`                 | Hook to access database from context          |
| `withObservables(deps, mapper)` | HOC for reactive component data               |
| `compose(...enhancers)`         | Combine multiple HOCs                         |
| `withDatabase`                  | HOC that injects `database` prop from context |

---

## Version History

| Version | Key Changes                                                                                                   |
| ------- | ------------------------------------------------------------------------------------------------------------- |
| v0.27   | React helpers consolidated to `@nozbe/watermelondb/react`, diagnostics API, removed `@nozbe/with-observables` |
| v0.28   | Requires RN 0.74+, Node 18+, iOS 12+ deployment target                                                        |
  • SKILL.md 21.7 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

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

Comments (0)

Sign in to join the conversation.

No comments yet.

Reviews (0)

No reviews yet.

Related

Claude Prompt
Tighten-my-prose editing prompt
Claude Skill
Adversarial code-review skill for Claude
Claude Workflow
Deep-research workflow (fan-out + verify)
Claude Skill
Conventional-commit writer for Claude