Add Drizzle Sqlite

Add Drizzle + expo-sqlite (Offline-First)

Type-safe ORM over SQLite. Perfect for offline-first apps.

Install

bashCopy code
pnpm add drizzle-orm
pnpm add -D drizzle-kit
npm exec --no -- expo install expo-sqlite

Schema

tsCopy code
// db/schema.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const habits = sqliteTable('habits', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  streak: integer('streak').default(0),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
});

DB client

tsCopy code
// db/index.ts
import { drizzle } from 'drizzle-orm/expo-sqlite';
import { openDatabaseSync } from 'expo-sqlite';
import * as schema from './schema';

const expoDb = openDatabaseSync('app.db');
export const db = drizzle(expoDb, { schema });

Migrations

tsCopy code
// drizzle.config.ts
import type { Config } from 'drizzle-kit';
export default {
  schema: './db/schema.ts',
  out: './db/migrations',
  dialect: 'sqlite',
  driver: 'expo',
} satisfies Config;

Generate:

bashCopy code
npm exec --no -- drizzle-kit generate

Apply on app start:

tsxCopy code
import { useMigrations } from 'drizzle-orm/expo-sqlite/migrator';
import migrations from './db/migrations/migrations';

const { success, error } = useMigrations(db, migrations);

Query

tsCopy code
import { eq } from 'drizzle-orm';

const all = await db.select().from(habits);
const one = await db.select().from(habits).where(eq(habits.id, '123'));
await db.insert(habits).values({ id: '123', name: 'Read', createdAt: new Date() });
await db.update(habits).set({ streak: 5 }).where(eq(habits.id, '123'));
await db.delete(habits).where(eq(habits.id, '123'));

Sync to backend

Pattern:

• Local writes immediate (write to SQLite, mark dirty)
• Background sync to Supabase/backend
• Pull deltas on app open
• Last-write-wins or CRDT for conflicts

Use TanStack Query mutation with onMutate for instant UI + background sync.

Encryption

bashCopy code
pnpm add expo-sqlite/build-utils  # for encryption

Use SQLCipher via expo config plugin if you need encrypted DB.

Pair with

• add-tanstack-query for sync orchestration
• add-supabase-auth-android for backend