@ttoss/postgresdb-cli

@ttoss/postgresdb-cli

CLI for managing PostgreSQL databases with Sequelize.

Installation

pnpm add -D @ttoss/postgresdb-cli

Prerequisites

Define your db object using @ttoss/postgresdb. The CLI imports this object to load models and interact with the database.

Set connection environment variables in .env files:

DATABASE_NAME=postgres
DATABASE_USER=postgres
DATABASE_PASSWORD=mysecretpassword
DATABASE_HOST=localhost
DATABASE_PORT=5432

Environment-specific configuration: Use --environment or -e flag to load .env.<environment> files (e.g., .env.Production, .env.Staging) instead of the default .env. This prevents accidental use of production credentials.

Commands

sync

Synchronize database schema with models:

pnpm dlx @ttoss/postgresdb-cli sync -e Development

⚠️ Required: The --environment or -e flag is mandatory to explicitly specify which environment credentials to use. This prevents accidental operations on the wrong database.

Using environment-specific credentials:

pnpm dlx @ttoss/postgresdb-cli sync --alter -e Production

This loads variables from .env.Production.

Behavior:

• Without --alter: Creates new tables only (preserves existing schema)
• With --alter: Creates new tables, adds/removes columns to match models, creates new indexes (preserves tables and indexes not in models). Requires confirmation before executing.

⚠️ Caution: The --alter flag modifies your database schema. Removing columns will delete data permanently. The CLI will prompt for confirmation before proceeding. Always backup your database before using --alter in production. For production environments, use proper migration tools instead of sync.

Testing before using --alter: Always ensure your models have comprehensive tests before running sync --alter. Tests validate that all model properties are correctly defined and prevent accidental column removal. If a column is missing from your model definition, --alter will drop it from the database. See the @ttoss/postgresdb testing guide for details on setting up model tests.

Add to package.json for convenience:

{
  "scripts": {
    "sync": "ttoss-postgresdb sync"
  }
}

Then run pnpm sync -e Development or pnpm sync --alter -e Production from the command line.

Options:

• --db-path, -d: Path to db object file (default: ./src/db.js)
• --alter: Alter schema to match models (default: false)
• --environment, -e: (Required) Specify environment to load .env.<environment> file

erd

Generate an Entity-Relationship Diagram from your models:

pnpm dlx @ttoss/postgresdb-cli erd

Note: This command generates diagrams from model definitions only - database credentials are not required unless you need to validate against an actual database.

Add to package.json for convenience:

{
  "scripts": {
    "erd": "ttoss-postgresdb erd"
  }
}

Options:

• --db-path, -d: Path to db object file (default: ./src/db.js)
• --engine: Layout engine - circo, dot, fdp, neato, osage, twopi (default: circo)