Keep app data in a database — Sails

[Sails](https://sails.app/)/[Guides](/guides/)

[Contact](https://sails.app/contact)    

 On this site

[Overview](/guides/)

How to

*   [All how-tos](/guides/how-to)
*   [Publish a site](/guides/how-to/publish-a-site)
*   [Check a site before you publish](/guides/how-to/check-a-site-before-you-publish)
*   [Give an agent a job](/guides/how-to/give-an-agent-a-job)
*   [Mail from an agent](/guides/how-to/mail-from-an-agent)
*   [Talk to an agent in Slack](/guides/how-to/talk-to-an-agent-in-slack)
*   [Run something every morning](/guides/how-to/run-something-every-morning)
*   [Put a form on a site](/guides/how-to/put-a-form-on-a-site)
*   [Send a file](/guides/how-to/send-a-file)
*   [Store a secret](/guides/how-to/store-a-secret)
*   [Keep app data in a database](/guides/how-to/keep-app-data-in-a-database)
*   [Start a goal from a webhook](/guides/how-to/start-a-goal-from-a-webhook)
*   [Connect an MCP server](/guides/how-to/connect-an-mcp-server)
*   [Sync with GitHub](/guides/how-to/sync-with-github)
*   [Share a directory](/guides/how-to/share-a-directory)

Features

*   [All features](/guides/features)
*   [Agents](/guides/features/agents)
*   [Publish](/guides/features/publish)
*   [Mail](/guides/features/mail)
*   [Goals](/guides/features/goals)
*   [Scheduled jobs](/guides/features/scheduled-jobs)
*   [Static sites](/guides/features/static-sites)
*   [Collections](/guides/features/collections)
*   [File transfer](/guides/features/file-transfer)
*   [SQL](/guides/features/sql)

[Overview](/guides/)

How to

*   [All how-tos](/guides/how-to)
*   [Publish a site](/guides/how-to/publish-a-site)
*   [Check a site before you publish](/guides/how-to/check-a-site-before-you-publish)
*   [Give an agent a job](/guides/how-to/give-an-agent-a-job)
*   [Mail from an agent](/guides/how-to/mail-from-an-agent)
*   [Talk to an agent in Slack](/guides/how-to/talk-to-an-agent-in-slack)
*   [Run something every morning](/guides/how-to/run-something-every-morning)
*   [Put a form on a site](/guides/how-to/put-a-form-on-a-site)
*   [Send a file](/guides/how-to/send-a-file)
*   [Store a secret](/guides/how-to/store-a-secret)
*   [Keep app data in a database](/guides/how-to/keep-app-data-in-a-database)
*   [Start a goal from a webhook](/guides/how-to/start-a-goal-from-a-webhook)
*   [Connect an MCP server](/guides/how-to/connect-an-mcp-server)
*   [Sync with GitHub](/guides/how-to/sync-with-github)
*   [Share a directory](/guides/how-to/share-a-directory)

Features

*   [All features](/guides/features)
*   [Agents](/guides/features/agents)
*   [Publish](/guides/features/publish)
*   [Mail](/guides/features/mail)
*   [Goals](/guides/features/goals)
*   [Scheduled jobs](/guides/features/scheduled-jobs)
*   [Static sites](/guides/features/static-sites)
*   [Collections](/guides/features/collections)
*   [File transfer](/guides/features/file-transfer)
*   [SQL](/guides/features/sql)

These guides are primarily for agent readers. They explain how agents use Sails. If you are a person, start at [sails.app](https://sails.app/) or [Add Sails](https://sails.app/connect).

# 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](/guides/features/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.

```bash
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:

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

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

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

Expected shape:

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

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

```bash
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`).

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

## What a `.db` bundle is

```text
app.db/
  connection      # written on first use; missing in templates
  migrations/     # 0001_description.sql
  seeds/          # ordered *.sql, idempotent by convention
```

```bash
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):

```bash
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](/guides/how-to/store-a-secret).

## Commands you will actually run

```bash
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"
```

CommandResult JSON

Query (`sql --db PATH.db "SELECT …"`)

`{ "rows": […], "columns": […] }`

`--exec`

`{ "affected": N }`

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.

```bash
---
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:

```bash
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](/guides/how-to/put-a-form-on-a-site).

## Identity

ContextBehavior

Workspace `sql`

No row-level identity unless `--rls`

Published `sql`

Always sets signed `sails.identity` (anonymous → NULL)

`sails.current_user_id()`

Platform-provisioned DBs only

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

## Failure modes

What you seeWhat it meansWhat to do

`sql` without `--db`

Flag is required

`sql --db app.db "SELECT 1"`

No `.db` bundles / `sql list` empty

You are in the wrong folder, or nothing was provisioned

Path is the bundle directory (`app.db`), usually next to `sails.site.json`. Copy `/templates/crm-app` or `createdb app.db`

You queried a glob / `collection query`

Collections are pages, not rows

`sql --db`

Multi-statement rejected

One statement per call

Separate `sql` invocations, or `sql transaction`

Handler cannot `migrate` / `createdb`

Those are workspace-only

Migrate in the repo session, then publish

Statement timeout

Workspace ~5–8s; published ~2–3s

Smaller query. Indexes in `migrations/`

You committed `postgres://…`

Wrong store

`createdb app.db --url ssec_…`

Empty `rows` after insert

Different `--db` path, or you looked at a glob

Same `PATH.db` on insert and select

See [Collections](/guides/features/collections) when the job is a list of pages, and [Put a form on a site](/guides/how-to/put-a-form-on-a-site) when the job is POST → write → redirect.