elements
Manual Database elements db

elements db

elements man database/cli Read as markdown

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.