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:

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