# 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:

- `-connect=<env>` (`-c`): 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>"`: 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`.
- `-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.

```
elements db migrate
elements db migrate -connect=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.

```
elements db
elements db -test
elements db -connect=production
elements db -connect=production#machine1
```

Pass `-sql "<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.

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

`psql` sends your text straight to Postgres, so the camelCase-to-snake_case
conversion Elements applies in app code (see **camelCase** above) does not
happen here. Write the actual snake_case column and table names:

```
elements db -sql "select first_name, created_at from users"
```

Postgres folds an unquoted `firstName` to lowercase `firstname` and then reports
that no such column exists, so camelCase identifiers fail in `elements db` and
`-sql`. Use the snake_case names stored in the database.

### Dump

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

```
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, and both erase all of your application data.

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.

```
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 and covers development and most deployments.
Pointing at a remote or hosted Postgres is an opt-in escape hatch: 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.

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

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 on Postgres `LISTEN` / `NOTIFY`.