A small offline house log with React Native

export type House = { id: string; name: string };
export interface SqlPort {
  exec(sql: string): Promise<void>;
  run(sql: string, ...values: (string | number)[]): Promise<void>;
  all<T>(sql: string, ...values: (string | number)[]): Promise<T[]>;
}
export class Repository {
  constructor(private db: SqlPort) {}
  async migrate() {
    await this.db.exec('PRAGMA foreign_keys=ON');
    const rows = await this.db.all<{ user_version: number }>('PRAGMA user_version');
    if (rows[0].user_version > 1) throw new Error('Database is newer than this app');
    await this.db.exec(`BEGIN IMMEDIATE;
      CREATE TABLE IF NOT EXISTS houses(id TEXT PRIMARY KEY,name TEXT NOT NULL);
      PRAGMA user_version=1;
      COMMIT;`);
  }
  async save(house: House) {
    await this.db.run('INSERT INTO houses(id,name) VALUES (?,?)', house.id, house.name);
  }
  all() { return this.db.all<House>('SELECT id,name FROM houses ORDER BY name,id'); }
}
export class HouseService {
  constructor(private repository: Repository, private id: () => string) {}
  async create(input: string) {
    const name = input.trim();
    if (!name) throw new Error('Name required');
    await this.repository.save({ id: this.id(), name });
  }
}

import React, { useEffect, useState } from 'react';
import { View, Text, TextInput, Pressable } from 'react-native';
import * as SQLite from 'expo-sqlite';
import * as Crypto from 'expo-crypto';
import { Repository, HouseService, type House } from '../src/repository';
export default function Screen() {
  const [repository, setRepository] = useState<Repository | null>(null);
  const [rows, setRows] = useState<House[]>([]);
  const [name, setName] = useState(''); const [error, setError] = useState('');
  const [busy, setBusy] = useState(false);
  useEffect(() => {
    let alive = true; let database: SQLite.SQLiteDatabase | undefined;
    const startup = (async () => {
      try {
        database = await SQLite.openDatabaseAsync('house_learning.sqlite');
        const db = database;
        const repo = new Repository({
          exec: sql => db.execAsync(sql),
          run: async (sql, ...values) => { await db.runAsync(sql, ...values); },
          all: (sql, ...values) => db.getAllAsync(sql, ...values),
        });
        await repo.migrate(); const data = await repo.all();
        if (alive) { setRepository(repo); setRows(data); }
        else await db.closeAsync();
      } catch (_) { if (alive) setError('Cannot open storage'); }
    })();
    return () => { alive = false; void startup.finally(() => database?.closeAsync()).catch(() => undefined); };
  }, []);
  async function save() {
    if (!repository || busy) return;
    setBusy(true); setError('');
    try {
      await new HouseService(repository, Crypto.randomUUID).create(name);
      setRows(await repository.all()); setName('');
    } catch (_) { setError('Check name or storage'); }
    finally { setBusy(false); }
  }
  return <View style={{ padding: 24, gap: 16 }}>
    <Text>House learning</Text>
    <TextInput value={name} onChangeText={setName} accessibilityLabel="House name" />
    <Pressable disabled={!repository || busy} accessibilityRole="button" onPress={save}><Text>Save</Text></Pressable>
    <Text>{error}</Text>{rows.map(row => <Text key={row.id}>{row.name}</Text>)}
  </View>;
}

import { test, expect } from 'vitest';
import { DatabaseSync } from 'node:sqlite';
import { mkdtempSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { Repository, HouseService, type SqlPort } from '../src/repository';
function port(db: DatabaseSync): SqlPort {
  return { exec: async sql => { db.exec(sql); },
    run: async (sql, ...values) => { db.prepare(sql).run(...values); },
    all: async <T>(sql: string, ...values: (string | number)[]) => db.prepare(sql).all(...values) as T[] };
}
test('same SQL repository retains confirmed data across reopen', async () => {
  const directory = mkdtempSync(join(tmpdir(), 'house_learning_'));
  const file = join(directory, 'data.sqlite');
  let db = new DatabaseSync(file);
  try {
    let repo = new Repository(port(db)); await repo.migrate();
    const service = new HouseService(repo, () => 'h1');
    await service.create(' Home ');
    await expect(service.create('  ')).rejects.toThrow('Name required');
    db.close(); db = new DatabaseSync(file); repo = new Repository(port(db));
    await repo.migrate(); expect(await repo.all()).toEqual([{ id: 'h1', name: 'Home' }]);
  } finally { db.close(); rmSync(directory, { recursive: true }); }
});