SQLite & Offline-First Apps

Core concepts

1. Open and query: Use openDatabaseAsync, execAsync for schema, runAsync for writes and getAllAsync for reads, always with parameter placeholders. — Expo SQLite
2. Migrations: Keep a schema_version and run ordered migrations at startup so app updates can change tables safely. — Expo SQLite
3. ORMs: Drizzle ORM supports Expo SQLite and gives typed queries, which agents handle well. — Drizzle
4. Sync strategy: Add updated_at and deleted_at columns and a queue of pending changes. Push local changes, pull remote ones, and resolve conflicts by a stated rule. — Expo local-first

Concept videos

YouTube channels and searches for these topics. On a large screen they open in the reading panel next to the roadmap.

• Expo SQLite tutorial — YouTube search: expo-sqlite tutorial

Explanation

Soft deletes for sync

If you delete rows locally, the server never learns about it. Mark rows deleted with a timestamp and sync the deletion.

Parameterise queries

Never build SQL by concatenating user input; use placeholders to avoid injection bugs.

What Is It?

SQLite is an embedded relational database stored as a file on the device.

Why Should I Care?

It supports filtering, sorting and joins offline, which key-value storage cannot.

Real World Analogy

It is a small, well-organised ledger in your pocket that works with no internet and reconciles with head office later.

Visual Mental Model

UI -> local SQLite (source of truth) -> sync queue -> server

Smallest Possible Example

import * as SQLite from 'expo-sqlite';

const db = await SQLite.openDatabaseAsync('app.db');
await db.execAsync('PRAGMA journal_mode = WAL; CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY NOT NULL, title TEXT NOT NULL, updated_at INTEGER NOT NULL);');
await db.runAsync('INSERT INTO notes (title, updated_at) VALUES (?, ?)', 'Hello', Date.now());
const rows = await db.getAllAsync<{ id: number; title: string }>('SELECT id, title FROM notes ORDER BY updated_at DESC');

Common Mistakes

❌ String-concatenating SQL.

Use ? placeholders.

❌ No migrations.

An app update crashes for existing users because the table shape changed.

Build This Mini Project

Build a local-first notes app with SQLite, soft deletes and a manual 'sync now' that posts pending rows to an API.

Prompts for Cursor, Claude Code & Codex

Paste these into Cursor, Claude Code, Codex, or any coding agent. Replace the bracketed parts with your app details, and keep the agent in plan mode first so you review the approach before it edits files.

SQLite layer

Create lib/db.ts with expo-sqlite: open the database, a migrations runner with schema_version, typed helpers for notes (create, list, update, soft delete) and a pending-changes table for sync. Use parameter placeholders and add unit tests.

Rules, databases & blogs to use

• PostgreSQL rule: SQL conventions
• Mobile rules: mobile data rules

Resources

• Expo SQLite
• Drizzle ORM