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 WHERE owner_id = sails.current_user_id()"
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 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

Identity and RLS

sails.current_user_id() is a sqlite scalar. Filter with WHERE owner_id = sails.current_user_id(). Templates do not ship RLS policies. Foreign Postgres identity is whatever the remote database provides.

Published handlers

Query, exec, and sql transaction only. Blocked: createdb, dropdb, migrate, seed, 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.