# elements db

The database subcommands: shell, migrate, reset, and pointing at a remote
or hosted Postgres.

The `elements db` command runs database operations from the command line. It
reads connection parameters from `config.jsoc` and the active environment.

Common options apply to every subcommand:

- `-remote=<env>`: target a deploy machine's database, for example
  `production` or `production#machine1`.
- `-test`: target the test database instead of the app database.
- `-cluster`: connect to the Postgres cluster itself rather than the project's
  database. Use it for cluster-level work such as `\l` to list databases or
  `create database`. Available on `db shell`.
- `-sql="<stmt>"` (`-c`): run a single SQL statement and exit instead of
  opening an interactive session. Equivalent to `psql -c "<stmt>"`; output
  format matches `psql -c`. Available on `db` and `db shell`.
- `-json`: with `-sql`, write the rows as JSON instead of psql's table.
- `-quiet` (`-q`): suppress progress output.

The subcommands are `migrate`, `shell`, `create`, `drop`, `reset`, and `dump`.

### Migrate

`elements db migrate` (alias `elements db m`) applies pending migrations on
demand. Migrations also run as part of every build, so this is for the cases
where you want them applied without a build: against a deploy machine, or after
editing a migration by hand.

```bash Terminal
elements db migrate
elements db migrate -remote=production
```

Writing migrations is `elements man migrations`.

### Shell

`elements db shell` (alias `elements db sh`) opens a `psql` session against the
configured database. Bare `elements db` is a shortcut for the same thing.

```bash Terminal
elements db
elements db -test
elements db -remote=production
elements db -remote=production#machine1
```

Pass `-sql "<stmt>"`, or its alias `-c "<stmt>"`, to run a one-shot query and
exit. The output is identical to what `psql -c "<stmt>"` writes to stdout: the
same column headers, separators, and row count line. This is the agent-friendly
path: no interactive session, no PTY, just the formatted result.

```bash Terminal
elements db -sql "select count(*) from users"
elements db -sql "select id, email from users limit 5"
elements db -remote=production -sql "select now()"
elements db shell -sql "select version()"
```

Add `-json` to get the rows as a JSON array of objects, one per row, keys in
column order. Numbers, booleans and `json` columns keep their JSON type; every
other value is the text psql would show. A statement that returns no rows
writes `[]`. `-json` needs `-sql` and takes no psql options.

```bash Terminal
elements db -json -sql "select id, email from users limit 5"
```

psql's own options go after `--` and reach psql as typed, so the output format
is yours to pick:

```bash Terminal
elements db -sql "select id from users" -- -At
elements db -sql "select * from users" -- --csv
```

Pipe a SQL file on stdin to run it as a script. psql runs every statement in it
and exits:

```bash Terminal
elements db < seed.sql
cat seed.sql | elements db
```

A statement or a piped script skips your `~/.psqlrc`, so its settings and
banners do not land in output something else reads. The interactive shell
keeps it.

SQL through `elements db` is converted the way app code and migrations are (see
`elements man database/sql`): a camelCase identifier becomes its snake_case
column or table name, so a one-shot statement and a piped script can use the
same names your code does:

```bash Terminal
elements db -sql "select firstName, createdAt from users"
```

psql output shows the names Postgres stores (`first_name`, `created_at`);
`-json` writes camelCase keys (`firstName`), the way `sql()` returns rows. What you
type in the interactive shell reaches psql as typed, so write snake_case there.

### Dump

`elements db dump` writes the database contents as SQL to stdout. Redirect to a
file to capture a backup.

```bash Terminal
elements db dump > backup.sql
elements db dump -test > test-backup.sql
```

### Create

`elements db create` creates the project's app and test databases if they do not
already exist. The project server creates these databases automatically on
startup, so this subcommand is mostly useful when bootstrapping a new
environment or recreating a database that has been dropped.

### Drop and Reset

`elements db drop` removes the project's app and test databases.
`elements db reset` drops them and recreates empty ones in their place. Both
subcommands operate on the app database and the test database together as a
pair, both erase all of your application data, and both delete the migration
backups in `.elements/db/`, which can no longer restore a database that was
dropped. To stop a project and drop its databases in one step, before deleting
its folder, run `elements kill -destroy`.

Both commands refuse to run without `-force`. The flag is the safety check:
without it, the command prints what it would do and stops. Pass `-force` only
when you have read the message and intend to destroy the data.

```bash Terminal
elements db reset -force
```

**Never run `drop` or `reset` against a production database.** Both commands
erase all application data, and there is no recovery short of restoring from a
dump. Reserve these commands for local development. If you are running against
anything beyond your own machine, including staging, a deploy machine, or
production, get explicit confirmation from the user before proceeding, and never
pass `-force` on their behalf without that confirmation.

## Remote and Hosted Postgres

The bundled cluster is the default for development and for a single-machine
deployment. Several machines share one Postgres cluster, which you run yourself
or rent from a host. To point at it, set `DB_HOST`
(and `DB_PORT`, `DB_USER`, `DB_PASSWORD`, and the app-user variants as needed)
in the active environment's env file. When `host` is anything other than the
local loopback cluster, Elements connects to that server and ignores the bundled
cluster entirely. Every feature works across all the machines that share it with
no other change: live tables, channels, sessions, jobs, and cron. Uploaded files
go in Postgres, where every machine reads them, or in S3 or any object store.

```text config/env/production.env
# config/env/production.env, remote example
DB_HOST=db-postgresql-sfo3-12345.example.com
DB_PORT=5432
DB_USER=doadmin
DB_PASSWORD=...
```

The app and test databases are created on that server too. On the first build
against the environment, Elements creates `<name>` and `<name>_test` if they do
not already exist, the same as it does on the bundled cluster. An empty managed
cluster needs no bootstrapping by hand.

Creating a database cannot run over a connection to that database, so Elements
connects to an existing one first: `postgres`, or else `defaultdb`, the
name DigitalOcean Managed Databases uses for a server that has no `postgres`
database. Set `DB_DEFAULT`, or `database.default` in `config.jsoc`, when your
provider uses a third name.

Every connection to a host other than loopback is encrypted with TLS, with
nothing configured. To verify the server too (libpq's `verify-full`: the
certificate chains to your provider's CA and names the host), save the
provider's CA certificate in the project and set one more variable:

```text config/env/production.env
DB_SSL_ROOT_CERT=config/certs/db-ca.pem
```

The build, `elements db`, the job server, and the app all connect with the
same settings, including `elements db -remote`, whose tunnel still checks the
certificate against the real host name. The modes, `DB_SSL`, and the providers'
CA files are in `elements man database`.

Any provider that exposes a standard Postgres connection over the network works:
Digital Ocean Managed Databases, AWS RDS, Neon, Supabase, Railway, Crunchy
Bridge, and others. Use Postgres 16 or newer; Elements relies on modern Postgres
features.

## Related

- Migrations: `elements man migrations`. SQL migrations, applied as part of the
  build.
- LiveTable: `elements man livetable`. Real-time CRUD on top of `sql` and
  `Channel`.
- Channels: `elements man channel`. Pub/sub from the server to every listening
  browser.