Working with the database

One SQLite file per app, on the local disk, with one writer at a time. Migrations are files; rows are Hashes keyed by String.

db.query("SELECT id, text FROM notes WHERE done = 0 ORDER BY id DESC")
db.first("SELECT COUNT(*) AS n FROM notes")["n"]
db.run("UPDATE notes SET done = 1 WHERE id = ?", id)
db.transaction { ... }
db.last_id
db.exec("PRAGMA optimize")

db is a method, never a constant. Melee.db.transaction do ... end and db.transaction do ... end both work; DB.transaction do ... end on a constant does not compile.

Rows

A row is a Hash keyed by column name as a String, with SQLite’s own types as values — Integer, Float, String or nil.

row["text"]        # yes
row[:text]         # nil, always
row["done"].to_i == 1

There is no type mapping and no boolean: a column holding 0/1 comes back as 0/1. Narrow explicitly at the edges — .to_i, .to_s, .to_f — which is the same habit params needs, and which the compiler likes because it makes the type of every value obvious.

Name your columns in the SELECT rather than using *. It is what makes the row shape readable to you, to the compiler and to whoever reads the template.

Binds, always

db.run "INSERT INTO notes (text, created_at) VALUES (?, ?)", text, Time.now.to_i    # yes
db.run "DELETE FROM notes WHERE id = #{params[:id]}"                               # SQL injection

? placeholders are never parsed as SQL. Nothing in the platform stops you interpolating, so this is a habit, not a guard rail.

Migrations

Files in db/migrations/, applied in name order, once each, on the database’s first use.

db/migrations/001_schema.sql
db/migrations/002_done.sql
db/migrations/003_index_on_date.sql

• Never edit an applied migration. Write the next number.
• They are checked at build time against a scratch database, so SQL that does not parse fails the build with the file and line.
• A durable object’s schema lives separately, in db/objects/<class_name>/NNN.sql, and is applied per object.

SQLite’s ALTER TABLE is limited — you can add a column, rename, and drop, but not change a type or add a constraint. The usual workaround is a new table, a INSERT INTO ... SELECT, and a rename, in one migration.

The one-writer rule

This is the thing to understand about melee’s storage.

The database runs in WAL mode (synchronous=NORMAL, foreign_keys=ON, a 5-second busy timeout). WAL means readers never block — a SELECT runs happily during a write. But there is one writer at a time, and your app runs four requests at once by default, so contention is real.

A second writer waits up to five seconds and then raises. So:

# no: the HTTP call holds the write lock for as long as the network takes
db.transaction do
  res = HTTP.get(url, timeout: 10)
  db.run "UPDATE feeds SET body = ? WHERE id = ?", res.body, id
end

# yes: do the slow part first, hold the lock for microseconds
res = HTTP.get(url, timeout: 10)
db.transaction do
  db.run "UPDATE feeds SET body = ? WHERE id = ?", res.body, id
end

db.transaction uses BEGIN IMMEDIATE, so it takes the write lock at the start rather than failing halfway through. Do not nest transactions.

Rules of thumb: keep transactions to the writes; never do I/O, parsing or template rendering inside one; and if you are writing on every request, think about whether that write belongs in a durable object on a timer instead.

setting, for one-off values

setting("display_token")                 # -> String or nil
setting("display_token", random_token)   # writes and returns it

A small key/value table the platform creates for you. Good for “the app remembers one thing”; not a cache and not a substitute for a column.

Two databases, not one

They are separate files with separate transactions. A write to each is not atomic across both — if two things must commit together, put them in the same database. Background jobs has the worked version of this trap.

Backups

There is no backup. The database is a file in the app’s data directory on one machine’s local disk, and nothing copies it anywhere. Until replication exists, backing it up is the operator’s job — see Logs, backups and upgrades.

For an app you care about, a durable object on a daily timer that writes a copy of the important rows somewhere else is a reasonable stopgap, bearing in mind that HTTP has no put and no request signing yet.

Performance

• A query is a function call into SQLite in the same process. There is no network hop and no connection pool, so “too many queries” costs much less here than in a Rails app.
• Add the indexes you need in a migration. EXPLAIN QUERY PLAN works through db.query.
• The whole database is on local disk with a 128 MB memory cap on the process. Tens of megabytes of data are comfortable; tens of gigabytes are not what this is for.

See also

• Database — the full reference.
• Durable objects — storage and object migrations.