How do I recreate the database?

Table of Contents

compass db recreate is the single command that handles every phase of database recreation: drops the existing database and its roles, recreates roles and grants, runs all DDL, applies RLS, and seeds metadata. It reads all connection details from .env — no manual psql arguments needed. See Database Lifecycle and Script Reference for the full list of lifecycle commands.

Question

How do I recreate the database from scratch — dropping and rebuilding the schema, roles and seed data?

Answer

  1. Stop services first. Active connections block the drop: 

    ./compass.sh services stop

  2. Recreate the database. The standard invocation kills any remaining connections and skips the interactive confirmation prompt: 

    ./compass.sh db recreate --kill --yes

    Without --yes compass prompts you to type yes interactively. Without --kill it refuses if active connections are found — use that as the default in manual sessions so you don't accidentally drop a database another worktree is using.

  3. Restart services and bootstrap once the database is ready: 

    ./compass.sh services start

Flags

Flag Meaning
--kill Terminate active connections before dropping (pg_terminate_backend)
--yes Skip the interactive "type yes to proceed" confirmation

When schema changes require recreation

After any codegen run that alters projects/ores.sql/ DDL files (e.g. adding a new column or table), the schema version advances and the database must be recreated. compass db recreate is the only supported upgrade path — there are no incremental migration scripts.

What it does internally

compass db recreate executes these phases in order:

  1. Terminates active connections (if --kill).
  2. Drops the environment database (e.g. ores_dev_bright_faraday).
  3. Drops and recreates all environment roles via drop_roles.sql / create_roles.sql.
  4. Creates the database from scratch (not from a template) for environment isolation.
  5. Applies DDL: schema, tables, indexes, constraints, RLS policies.
  6. Populates database metadata (schema version, build environment, git commit).

Script

compass db recreate is implemented in projects/ores.compass/src/compass_db.py (cmd_recreate). The underlying SQL lives in ores.sql under:

  • projects/ores.sql/drop_roles.sql
  • projects/ores.sql/create_roles.sql
  • projects/ores.sql/recreate_database.sql
  • projects/ores.sql/create/ — per-component DDL fragments

Tested by

Manual. Run after every schema-changing codegen pass as the standard verification step that DDL is clean. Also exercised in CI environment provisioning.

See also

Emacs 29.3 (Org mode 9.6.15)