SQL

sql --db PATH.db is the app database. --db is always required. The path is a directory bundle (connection, optional data.sqlite, migrations/, seeds/), not a lone SQLite file you open by hand.

Default engine is sqlite. Foreign Postgres is optional via createdb --url. This is not a page collection and not the filter command. Globs list other pages at compile time. JSON / front-matter file sets use Filter. See Collections.

Done looks like

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

prints { "rows": […], "columns": […] }. First use with no connection file provisions sqlite, writes connection (sqlite), runs migrations/, then seeds/. ls app/app.db/ shows connection, data.sqlite, migrations/, seeds/.

Worked example

cp -r /templates/crm-app ./app
sql --db ./app/app.db "SELECT 1"
sql --db ./app/app.db "SELECT id, name FROM contacts ORDER BY name LIMIT 3"
sql --db ./app/app.db --exec \
  "INSERT INTO contacts (id, name, email) VALUES (?, ?, ?)" \
  "c_demo" "Ada" "ada@example.com"
sql --db ./app/app.db "SELECT name, email FROM contacts WHERE id = ?" "c_demo"

Bind with ? and trailing args (or --params JSON array). Do not concatenate user input. Multi-statement strings are rejected. Query output is { "rows": […], "columns": […] }. --exec is { "affected": 1 } (and rows when the SQL has RETURNING).

Bundle layout

app.db/
  connection          # tenant-specific; missing in templates; written on first use
  data.sqlite         # sqlite bytes (content-addressed blob); omitted for Postgres
  migrations/         # numbered NNNN_description.sql
  seeds/              # ordered *.sql (idempotent by convention)

connection first non-comment line:

A leftover sails-db:// handle (old platform tenant Postgres) errors. Recreate as sqlite (createdb app.db or delete connection and first-use again) or createdb --url postgres://… / ssec_….

Do not treat overlay / read_file on data.sqlite as the live database. Cap is 64 MiB. Store large objects as VFS files. Use foreign Postgres when you outgrow sqlite.

Commands

createdb app.db                      # sqlite (default)
createdb app.db --url ssec_app-db    # foreign DSN; no CREATE DATABASE
# first: secret set --origin https://db.example.com --as app-db "postgres://…"
dropdb app.db                        # sqlite: removes connection + data.sqlite; foreign: connection only

sql --db app.db "SELECT 1"
sql --db app.db --rls "SELECT * FROM todos"
sql --db app.db --exec "INSERT INTO todos (title) VALUES (?)" "$title"
sql --db app.db --params '["active"]' "SELECT * FROM contacts WHERE visibility = ?"
sql --db app.db --explain "SELECT * FROM todos"

sql begin --db app.db
sql commit --db app.db
sql rollback --db app.db
sql transaction --db app.db --exec "INSERT …" --query "SELECT …"

sql migrate --db app.db
sql migrate status --db app.db
sql seed --db app.db
sql owner enable --db app.db TABLE [--column owner_id] [--assign-null-to ID]
sql owner disable --db app.db TABLE
sql owner status --db app.db
sql show --db app.db
sql list
sql reload --db app.db

Use ? binds for both engines. sqlite keeps ?; Postgres rewrites to $1. JSON columns in sqlite templates are TEXT (JSON strings), not jsonb.

sqlite vs Postgres

Row ownership

Sails tells your SQL who is calling. Tables that are not owner-scoped are unfiltered: an unscoped SELECT returns every row to whoever can run it.

Declare a sqlite table owner-scoped so invoker / --rls queries cannot see another caller's rows:

sql owner enable --db app.db invoices --column owner_id
sql owner enable --db app.db invoices --assign-null-to "$SAILS_IDENTITY_ID"

--column defaults to owner_id. The column must already exist. Enable is atomic. A missing column leaves the table name unchanged. sql owner disable also restores a leftover _sails_ownd_* rename.

Rows whose owner column is NULL become invisible to invoker / --rls after enable. Enable warns with the NULL count. Pass --assign-null-to <id> to stamp those rows, or update them from workspace sql.

Enforcement is not a WHERE you have to remember. Joins, subqueries, and CTEs cannot read another identity's rows on an owner-scoped table. Invoker / --rls SQL rejects any unquoted REPLACE token. Quote a column or function named replace ("replace" or [replace]). ON CONFLICT DO UPDATE is also rejected. INSERT OR IGNORE, OR FAIL, OR ABORT, and OR ROLLBACK do not delete another caller's row. SQLite rejects ON CONFLICT DO NOTHING on the owner view; use INSERT OR IGNORE. sql owner enable refuses a table whose PRIMARY KEY or UNIQUE uses ON CONFLICT REPLACE. Recreate it without that clause and retry. Workspace sql without --rls and definer handlers keep full access so an accountant can see every client while each client only sees their own invoices.

A primary-key or UNIQUE collision can still reveal that a key exists (UNIQUE constraint failed, without the hidden table name). Prefer random ids such as uuid v7. Foreign keys that point into an owner-scoped table do not work from invoker SQL.

sails.current_user_id() is still a sqlite scalar. Use it on tables you have not opted in. Templates do not enable ownership for you. auth: required is still the sign-in gate. Foreign Postgres is unchanged; use that database's own policies.

sql owner status --db app.db
sql owner disable --db app.db invoices

Published handlers

Query, exec, and sql transaction only. Blocked: createdb, dropdb, migrate, seed, owner, begin / commit / rollback, reload. Any sql --db is ETag-volatile (Cache-Control: no-store). sqlite mutations persist (blob + tree hash) before HTTP 200. One live connection per --db path per request so a later SELECT sees an earlier INSERT.

Timeouts

Tenants cannot disable limits. Statement timeouts are enforced by the platform.

Failure modes

How to: Keep app data in a database.