Keep app data in a database

App records live in Postgres behind a .db directory, 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 creates the platform database, writes app/app.db/connection (sails-db://<uuid>), runs migrations/, then seeds/. Expected query shape:

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

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
  migrations/     # 0001_description.sql
  seeds/          # ordered *.sql, idempotent by convention

createdb app.db

That provisions a platform tenant DB and writes sails-db://<uuid> into connection. Templates usually skip createdb and let the first sql --db do the same thing.

Foreign Postgres (you already have a DSN):

secret set "postgres://…"
# prints ssec_…
createdb app.db --url ssec_…

--url attaches; it does not CREATE DATABASE. Put ssec_… in connection, not the DSN. 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

Templates do not ship RLS policies. Add USING (owner_id = sails.current_user_id()) when you want row filters.

Failure modes

See Collections when the job is a list of pages, and Put a form on a site when the job is POST → write → redirect.