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.

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.

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.

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.

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:

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:

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:

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.

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.

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.

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:

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.