Database

The app’s SQLite database: queries, binds, transactions, migrations as files, and the one-writer rule.

Every app gets one SQLite database. db is a method returning it; call it fresh each time, do not assign it to a constant (a yielding call on a constant receiver does not compile under Spinel).

get "/notes" do
  render :notes, notes: db.query("SELECT id, text, done FROM notes ORDER BY id")
end

The five calls

post "/notes" do
  db.run "INSERT INTO notes (on_date, text) VALUES (?, ?)", params.fetch(:on_date), params.fetch(:text)
  redirect "/notes/#{db.last_id}"
end

post "/feeds/:id/delete" do
  id = params.fetch(:id).to_i
  db.transaction do
    db.run "DELETE FROM events WHERE feed_id = ?", id
    db.run "DELETE FROM feeds WHERE id = ?", id
  end
  redirect "/admin"
end

Always use ? placeholders. Never interpolate a value into SQL — "... WHERE id = #{id}" is an injection and there is no escaping helper to make it safe.

The row shape

A row is a Hash keyed by String column names, in SELECT order. Values come back as the SQLite type: Integer, Float, String, or nil for NULL. There is no type coercion and no model layer.

row = db.first("SELECT MIN(fetched_at) AS t FROM feeds")
t = row.nil? ? nil : row["t"]
stale = t.nil? || Time.now.to_i - t.to_i > 3600

Two rules that follow from it: index with row["name"] (never row[:name], which is always nil), and narrow a value with .to_i / .to_s / is_a? before arithmetic, because the static type is “Integer or Float or String or nil”. Booleans are 0 and 1; timestamps are best stored as INTEGER Unix seconds (Time.now.to_i) or TEXT in YYYY-MM-DD form.

Migrations are files

db/migrations/001_schema.sql
db/migrations/002_add_colour.sql

Each file is plain SQL and may hold several statements. The build step runs every file against a throwaway in-memory database — a typo fails the build with db/migrations/002_add_colour.sql:1: <sqlite message> — then embeds them all in the binary. At the app’s first database use they are applied in filename order and recorded by name in a melee_migrations table, so a file already applied is never re-run and adding a file later does not re-run the earlier ones.

Consequences: never edit an applied migration, add a new file instead; name files so the sort order is the apply order (001_, 002_, …); there is no rollback, no schema.rb, and no melee migrate command.

-- db/migrations/001_schema.sql
CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  on_date TEXT NOT NULL,
  text TEXT NOT NULL,
  done INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX notes_on_date ON notes (on_date);

setting

A built-in key/value table for small state the app writes and reads back: tokens, last-run times, flags.

def display_token = setting("display_token") || setting("display_token", random_token)

post "/admin/rotate" do
  setting "display_token", random_token
  redirect "/admin"
end

setting(key) -> String | nil reads. setting(key, value) -> String writes and returns value.to_s. Keys and values are stringified. It uses a melee_settings table it creates on first use; you do not migrate it. Use ENV for configuration you set from outside (melee env), setting for what the app itself decides.

SQLite facts that matter

• The file is app.sqlite in the app’s data directory, in WAL mode with synchronous=NORMAL, foreign_keys=ON and a 5 s busy timeout.
• WAL means readers never block, but there is one writer at a time. Two requests writing at once means one waits up to 5 s and then raises. Keep transactions short: do the HTTP call, the parsing and the formatting outside the db.transaction block, and only the writes inside it.
• db.transaction uses BEGIN IMMEDIATE, so it takes the write lock at the start rather than failing halfway. Do not nest transactions.
• The database is per app. There is no connection pool, no second database, and no way to reach another app’s data. A Durable object’s storage is a separate SQLite file per object, reached through storage, never through db (see objects.md).
• A failing statement raises; the class is the backend’s, so rescue StandardError if you must rescue at all. Prefer letting it become a 500 with a log line.