Keep app data in a database

App records live in a .db directory bundle (sqlite by default, or foreign Postgres), not in a page glob and not in sails.data.json. --db PATH.db is required on every sql call.

A collection (glob: pages/blog/**) only indexes other pages at compile time. See Collections.

Done looks like

sql --db app.db "SELECT * FROM notes" prints JSON with a rows array. A later --exec insert shows up in the next SELECT. sql list shows the bundle.

Worked example: notes table from a template

Job: copy a working app, let first use provision the tenant database, then insert one row.

cp -r /templates/crm-app ./app
sql --db ./app/app.db "SELECT 1"

First use provisions sqlite, writes app/app.db/connection (sqlite), creates data.sqlite, runs migrations/, then seeds/. Expected query shape:

{ "rows": [{ "1": 1 }], "columns": ["1"] }

The CRM template already has contacts. Insert one note-shaped row you control, or query what the seed loaded:

sql --db ./app/app.db "SELECT id, name, email FROM contacts ORDER BY name LIMIT 5"

Expected shape:

{
  "rows": [{ "id": "…", "name": "…", "email": "…" }],
  "columns": ["id", "name", "email"]
}

Bind values. Do not concatenate user input into SQL. Multi-statement strings are rejected.

sql --db ./app/app.db --exec \
  "INSERT INTO contacts (id, name, email) VALUES (?, ?, ?)" \
  "c_demo" "Ada Lovelace" "ada@example.com"

--exec prints { "affected": 1 } (and rows if the SQL has RETURNING).

sql --db ./app/app.db "SELECT name, email FROM contacts WHERE id = ?" "c_demo"

What a .db bundle is

app.db/
  connection      # written on first use; missing in templates (line: sqlite | ssec_… | postgres://…)
  data.sqlite     # sqlite bytes; omitted for foreign Postgres
  migrations/     # 0001_description.sql
  seeds/          # ordered *.sql, idempotent by convention

createdb app.db

That provisions sqlite and writes sqlite into connection (plus data.sqlite). Templates usually skip createdb and let the first sql --db do the same thing.

Foreign Postgres (you already have a DSN):

secret set --origin https://db.example.com --as app-db "postgres://…"
# prints ssec_…
createdb app.db --url ssec_…

--url attaches; it does not CREATE DATABASE. Put ssec_… in connection, not the DSN. A leftover sails-db:// handle errors — recreate as sqlite or with --url. See Store a secret.

Commands you will actually run

sql help
sql list
sql show --db app.db
sql migrate --db app.db
sql migrate status --db app.db
sql seed --db app.db
sql --db app.db "SELECT 1"
sql --db app.db --exec "INSERT INTO notes (id, body) VALUES (?, ?)" "n1" "hello"
sql --db app.db --params '["active"]' "SELECT * FROM contacts WHERE visibility = ?"
sql --db app.db --rls "SELECT * FROM todos WHERE owner_id = sails.current_user_id()"
sql --db app.db --explain "SELECT * FROM contacts"

Workspace also has sql begin / commit / rollback and sql transaction. Published handlers may run query, exec, and sql transaction only. Blocked on a published mount: createdb, dropdb, migrate, seed, begin / commit / rollback, reload.

From a form handler

A *.api.sh on the published site talks to the same bundle. Prefer invoker (default) when the visitor is signed in. Use security: definer only for anonymous public writes.

---
methods: [POST]
auth: required
body:
  type: object
  required: [title]
  properties:
    title: { type: string, minLength: 1 }
---
title="$(request param title)"
sql --db app.db --exec "INSERT INTO todos (title) VALUES (?)" "$title"
respond redirect /todos

Dry-run without hitting the live URL:

handler-test pages/api/tasks/create.api.sh \
  --method POST --json '{"title":"Ship"}' --auth '{"signed_in":true}'

--auth only toggles signed_in. You cannot forge sub. See Put a form on a site.

Identity

Opt a table in with sql owner enable --db app.db TABLE. Invoker and --rls queries then see only the caller's rows. Rows with a NULL owner column become invisible; pass --assign-null-to <id> or update them from workspace sql. Prefer uuid v7 keys so a UNIQUE collision cannot probe whether another caller already has that id. Foreign keys into an owner-scoped table do not work from invoker SQL. Tables that are not opted in stay unfiltered. See SQL.

Failure modes

See SQL for the feature reference, Filter when the job is JSON files, Collections when the job is a list of pages, and Put a form on a site when the job is POST → write → redirect.