How do I run SQL in the environment?

Table of Contents

Runs psql against the environment's database with credentials, host and database name taken from .env — no connection arguments to remember. Successor to the retired run_sql.sh.

Question

How do I run SQL — a one-liner, a file, or an interactive session — against this environment's database?

Answer

The shape is compass sql [-u postgres|ddl] [-- <psql args>]: compass owns everything before the --, psql owns everything after it.

  1. Run a one-liner: 

    ./projects/ores.compass/compass.sh sql -- -c "select count(*) from refdata.currencies"

  2. Script-friendly output (unaligned, tuples only): 

    ./projects/ores.compass/compass.sh sql -- -At -c "select code from dq.change_reasons"

  3. Execute a SQL file, with psql variables if needed: 

    ./projects/ores.compass/compass.sh sql -- -v tenant_code=ores.dev.local3 -f my_script.sql

  4. Interactive session — no arguments at all drops you into psql: 

    ./projects/ores.compass/compass.sh sql

  5. As the DDL user instead of the postgres superuser — needed when writes must pass account-validating triggers: 

    ./projects/ores.compass/compass.sh sql -u ddl -- -c "\dt refdata.*"

The -- matters: without it, compass's own argument parser would try to interpret psql flags such as -c or -f.

compass sql is a top-level alias for compass db sql — both forms are identical.

Script

compass db sql in projects/ores.compass/src/compass_db.py — reads ORES_TEST_DB_DATABASE, ORES_TEST_DB_HOST (default localhost) and the role's password from .env, then execs psql with the passthrough arguments.

Tested by

Manual. Exercised constantly as the standard way to query an environment; compass bearings uses the same connection plumbing for its database status line.

See also

Emacs 29.1 (Org mode 9.6.6)