Recipe

SQLite Primer

SQLite is the most widely deployed database engine on the planet, embedded in every phone, browser, and countless desktop apps. This primer walks you through opening a database, running queries, and shaping schemas for production use inside a Meridian project.

01.Opening a database

A SQLite database is a single file on disk. Opening it is as simple as pointing the driver at a path. If the file does not exist, SQLite creates it for you on the first connection. Use WAL mode for concurrent reads and writes in production workloads.

import Database from 'better-sqlite3'

const db = new Database('meridian.db')
db.pragma('journal_mode = WAL')
db.pragma('foreign_keys = ON')

02.Designing a schema

SQLite is dynamically typed, but you should still declare column types for clarity and to take advantage of type affinity. Use INTEGER PRIMARY KEY for rowid aliases, and lean on CHECK constraints to keep data clean at the storage layer.

Prefer TEXT over VARCHAR(n).
Store timestamps as ISO-8601 strings or unix epoch integers.
Index foreign keys explicitly; SQLite does not do it for you.

03.Querying safely

Always use prepared statements with bound parameters. Never concatenate user input into SQL strings. The driver caches prepared statements, which means repeated queries are also dramatically faster than ad-hoc string building.

const insert = db.prepare(
  'INSERT INTO users (email, created_at) VALUES (?, ?)'
)
const row = insert.run('founder@example.com', Date.now())

const get = db.prepare('SELECT * FROM users WHERE id = ?')
const user = get.get(row.lastInsertRowid)