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

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

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

```
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()"
elements db -c "select count(*) from users"
```

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.

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

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

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

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

```
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=...
```

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. It tries `postgres`, then `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.

TLS is automatic. Elements connects over TLS to any host other than loopback,
so a hosted Postgres is encrypted with nothing configured, and there is no
`sslmode` to pass. The override is `database.ssl` in `config.jsoc`; see
`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 on Postgres `LISTEN` / `NOTIFY`.