House Log uses Expo SQLite on Android. Its repository tests use real SQLite files through Node.js. Both paths share repository and migration rules. This tutorial demonstrates SQL directly using Node SQLite so foreign keys and transactions are reproducible without a device. Application code later receives a SqlConnection through a port.
01Open a real SQLite connection
Node.js 22.13 or newer includes node:sqlite. :memory: creates a real SQLite database that exists only for this run. A file path would persist the data. A possible experimental warning is not a test failure.
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:');
db.exec(`PRAGMA foreign_keys=ON;
CREATE TABLE houses(id TEXT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE entries(id TEXT PRIMARY KEY, house_id TEXT NOT NULL REFERENCES houses(id), title TEXT NOT NULL, cost_cents INTEGER NOT NULL);`);
db.exec('BEGIN IMMEDIATE');
try {
db.prepare('INSERT INTO houses VALUES (?, ?)').run('h1', 'Home');
db.prepare('INSERT INTO entries VALUES (?, ?, ?, ?)').run('e1', 'h1', 'Roof', 12990);
db.exec('COMMIT');
} catch (error) {
db.exec('ROLLBACK');
throw error;
}
console.log(db.prepare('SELECT title, cost_cents FROM entries').all());
db.close();
Run / check
node database.mjs02Separate SQL from values
prepare creates a parameterised statement. run binds values to ?. They are not copied into an SQL string. This also prevents names containing quotes from being incorrectly interpreted as SQL code.
03Complete a transaction
BEGIN IMMEDIATE reserves write access. Record and house are written together. catch rolls back. This guarantee applies to SQL. Files in FileVault additionally need coordinated drafts and reference rules.
04Verify migrations and queries
The real repository configures foreign keys, WAL and busy_timeout. It checks user_version and adds indexes in schema 2. Tests must open an old database containing records and then verify they survive. An empty new installation is insufficient.
Output contains Roof and cost_cents 12990. The transaction completes.
Your exercise
Before COMMIT, insert a record for house_id missing. Then check the house count inside catch.
Show the solution and explanation
The foreign key fails. After ROLLBACK, the previously inserted house is also gone. SELECT count(*) FROM houses returns 0. This combined effect is the purpose of the transaction.
Project context and sources
The example is simplified for learning. The linked source version shows its use in the project and the checks applied there.