3. A database

Migrations are .sql files, queries take ? binds, and rows come back as Hashes keyed by column name.

Every app gets one SQLite database, embedded in the binary and sitting on the local disk. There is no ORM and no connection string. db is a method, available anywhere in your app, that opens the database on first use and applies the migrations.

Migrations are files

melee new already wrote one:

-- db/migrations/001_init.sql
CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  text TEXT NOT NULL,
  created_at INTEGER NOT NULL
);

Add a column for ticking notes off — a new file, never an edit to the old one:

-- db/migrations/002_done.sql
ALTER TABLE notes ADD COLUMN done INTEGER NOT NULL DEFAULT 0;

The rules are short:

Files in db/migrations/, applied in name order, which is why they are numbered.
Each runs once, ever. Never edit one that has been applied — the database has no way to un-apply it. Write the next number instead.
They are checked at build time by running each one against a scratch database, so a typo is caught before it is deployed:
```
db/migrations/003_bad.sql:1: near "TABEL": syntax error
```

Querying

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

Call	Gives you
`db.query(sql, *binds)`	an `Array` of rows
`db.first(sql, *binds)`	the first row, or `nil`
`db.run(sql, *binds)`	how many rows changed
`db.exec(sql)`	for DDL and `PRAGMA`; no binds, no rows
`db.last_id`	the rowid of the last insert
`db.transaction { ... }`	all of it, or none of it

A row is a Hash keyed by column name as a String:

note["text"]      # yes
note[:text]       # nil — always index rows with a String

Values are whatever SQLite stored: Integer, Float, String or nil. A SQLite integer column holding a boolean comes back as 0 or 1, so note["done"].to_i == 1 rather than if note["done"].

Writing, and binds

post "/notes" do
  text = params.fetch(:text).strip
  halt 400, "A note needs some text" if text.empty?
  db.run "INSERT INTO notes (text, created_at) VALUES (?, ?)", text, Time.now.to_i
  redirect "/"
end

post "/notes/:id/done" do
  db.run "UPDATE notes SET done = 1 - done WHERE id = ?", params.fetch(:id).to_i
  redirect "/"
end

Always ? placeholders, never interpolation. db.run "... WHERE id = #{params[:id]}" is a SQL injection and nothing in the platform will stop you writing it.

Two other things in those six lines:

params.fetch(:text) returns a String, "" if the field was not sent. params[:text] returns String or nil. Every parameter value is a String — convert with .to_i or .to_f when you need a number.
halt 400, "..." ends the request immediately with that status and body. So does redirect, which is always a 303. Neither returns.

The form

<%# locals: (notes:) %>
<form method="post" action="/notes">
  <%= csrf_field %>
  <input type="text" name="text" placeholder="Something to remember" required>
  <button>Add</button>
</form>
<ul class="notes">
  <% notes.each do |note| %>
    <%== partial :note, note: note %>
  <% end %>
</ul>
<p class="count"><%= notes.size %> open</p>

<%# locals: (note:) %>
<li>
  <%= note["text"] %>
  <form method="post" action="/notes/<%= note["id"] %>/done">
    <%= csrf_field %>
    <button>Done</button>
  </form>
</li>

csrf_field is not optional. Every non-GET request is checked against the session’s CSRF token, and a form without it gets a 403. Step 4 explains what that is protecting.

Restart and try it

melee dev: prepared 3 templates, 2 migrations
GET / -> 200 (4.3 ms)
POST /notes -> 303 (0.3 ms)
POST /notes -> 400 (0.1 ms)
GET / -> 200 (0.2 ms)

Add a note with an & or a < in it and view the source — it comes back escaped, because the template used <%= %>.

One writer

SQLite allows many readers but one writer at a time. melee runs four request processes at once by default, so two of them writing at the same moment is a real thing that happens. The database is in WAL mode, so readers never block; a second writer waits up to five seconds and then raises. It is fine for the size of app melee is for, as long as transactions stay short — Working with the database has the detail.

Forms, sessions and a login — stop everyone being able to edit the board.

Full detail: Database.

Keyboard shortcuts

melee