database-cli

A Rails-inspired database management CLI for PostgreSQL with Bun. Designed for AI agent workflows with full interactive support for human developers.

Design Philosophy

Rails-Inspired Convention Over Configuration

This CLI draws heavy inspiration from Ruby on Rails' approach to database management:

• Generators - rails generate model becomes database-cli generate model
• Migrations - Simple SQL files with UP/DOWN sections, tracked in a schema_migrations table
• Associations - Rails-style relationships: belongs_to, has_many, has_many_through, etc.
• Naming Conventions - Automatic pluralization, snake_case tables, PascalCase models
• Environment Separation - dev, test, and staging with isolated databases and ports

AI-First Developer Experience

This CLI is optimized for AI agent workflows while remaining fully usable by humans:

• Non-interactive mode (--non-interactive) - Skip all prompts, use sensible defaults
• Explicit flags - Every prompt can be bypassed with a corresponding flag
• Predictable output - Consistent success/error messages for parsing
• Exit codes - 0 for success, 1 for errors
• Composable commands - Chain operations without manual intervention

Agent Guidance: Fix Issues at the Source

Important for AI agents: When you encounter bugs or limitations in this CLI, fix them in the database-cli package itself rather than working around them in user code.

• Do NOT monkey-patch generated code
• Do NOT create wrapper scripts to handle CLI quirks
• Do NOT add workarounds in the user's project
• DO fix the issue in src/ and add tests in tests/
• DO run bun test to verify your fix doesn't break existing functionality

The CLI has 291 tests covering all commands, edge cases, and error handling. Add tests for any new functionality or bug fixes.

Features

• Code Generation: Generate models, schemas, migrations, services, and API routes
• Migration Management: Run, rollback, and track database migrations
• Docker Integration: Built-in Docker Compose support for local PostgreSQL
• Multiple Environments: Support for dev, test, and staging environments
• Custom Connections: Connect to any PostgreSQL instance with custom host/port/database
• Type-Safe: Full TypeScript support with generated type definitions
• Agent-Friendly: Full non-interactive mode for CI/CD and AI agent workflows

Installation

bun add database-cli

Or install globally:

bun add -g database-cli

Quick Start

1. Initialize a new project

database-cli init

This creates:

• database.config.ts - Database configuration
• .env.database - Environment variables
• docker-compose.yml - PostgreSQL containers (optional)
• db/migrations/ - Migration files directory
• db/seeds/ - Seed files directory
• src/models/ - Generated models
• src/services/ - Generated services
• src/api/routes/ - Generated API routes

2. Start the database

database-cli docker:up

3. Create the database

database-cli db:create

4. Generate a model

database-cli generate model User name:string email:string:unique age:integer:nullable

This creates:

• src/models/schema/UserSchema.ts - Type definitions
• src/models/User.ts - Model class
• db/migrations/{timestamp}_create_users.sql - Migration file

5. Run migrations

database-cli migrate

Commands

Initialization

database-cli init [options]

Options:

• --project-name <name> - Project name (default: directory name)
• --environments <envs> - Comma-separated environments (default: dev,test)
• --skip-docker - Don't generate docker-compose.yml
• --skip-core - Don't generate BaseModel and BaseService

Code Generation

Generate Model

database-cli generate model <name> [columns...]

Column format: name:type[:modifier]

Types:

• string - TEXT
• text - TEXT
• integer - INTEGER
• decimal - DECIMAL
• boolean - BOOLEAN
• timestamp - TIMESTAMP
• references - INTEGER with foreign key
• json - JSONB

Modifiers:

• nullable - Allow NULL values
• unique - Add UNIQUE constraint
• default:<value> - Set default value
  • For timestamps: default:now generates DEFAULT NOW()
  • For UUIDs: default:uuid generates DEFAULT gen_random_uuid()

Examples:

# Basic model
database-cli generate model User name:string email:string:unique

# With nullable and default
database-cli generate model Product name:string price:decimal:default:0 active:boolean:default:true

# With timestamp DEFAULT NOW()
database-cli generate model Article title:string published_at:timestamp:default:now

# With relationships
database-cli generate model Post title:string body:text --belongs-to User

# Skip migration
database-cli generate model Comment body:text --skip-migration

Options:

• --skip-migration - Don't generate migration file
• --skip-timestamps - Don't add created_at/updated_at columns
• --belongs-to <Model> - Add belongsTo relationship (can be repeated)
• --has-many <Model> - Add hasMany relationship (can be repeated)
• --has-many-polymorphic <Model:name> - Add polymorphic hasMany (e.g., Vote:voteable)
• --belongs-to-polymorphic <name> - Add polymorphic belongsTo (creates {name}_id and {name}_type columns)

Polymorphic Conflict Detection:

When generating models with --has-many or --has-one, the CLI automatically checks the database schema for potential conflicts:

• If the child table has polymorphic columns ({name}_id + {name}_type), suggests using --has-many-polymorphic
• If the child table already has FKs to other parents, warns about potential need for polymorphic association

$ database-cli generate model Post --has-many Vote

! Table 'votes' has polymorphic columns: voteable_id + voteable_type
  Suggestion: Use --has-many-polymorphic "Vote:voteable" instead of --has-many Vote

? Continue with generation anyway? [y/N]:

Generate Migration

database-cli generate migration <name>

Creates an empty migration file for custom SQL:

database-cli generate migration add_role_to_users

Generate Service

database-cli generate service <ModelName>

Creates a service class with CRUD operations.

Generate Route

database-cli generate route <ModelName>

Creates Hono API routes for the model.

Generate All

database-cli generate all <name> [columns...]

Generates model, schema, migration, service, and route in one command:

database-cli generate all Product name:string price:decimal --belongs-to Category

Generate API Client

database-cli generate client

Creates a typed API client for frontend use based on existing models.

Migrations

Run Migrations

database-cli migrate [options]

Options:

• -e, --environment <env> - Target environment (default: dev)
• --steps <n> - Only run n migrations
• --dry-run - Preview SQL without executing (shows syntax-highlighted SQL)
• -p, --port <port> - Custom database port
• -H, --host <host> - Custom database host
• -d, --database <name> - Custom database name

Rollback Migrations

database-cli rollback [options]

Options:

• -e, --environment <env> - Target environment (default: dev)
• --steps <n> - Rollback n migrations (default: 1)
• --all - Rollback all migrations
• --dry-run - Preview SQL without executing (shows syntax-highlighted SQL)
• -f, --force - Skip confirmation prompt

Sync Migration State

database-cli migrate:sync [options]

Syncs migration tracking with existing database tables. Use this when tables exist in the database but migrations aren't tracked (e.g., tables created manually or by another process).

Options:

• -e, --environment <env> - Target environment (default: dev)
• --dry-run - Preview what would happen without making changes
• --non-interactive - Skip confirmation prompt (for CI/automation)
• -p, --port <port> - Custom database port
• -d, --database <name> - Custom database name

Example scenario:

# Tables exist but migrate fails with "relation already exists"
database-cli migrate
# PostgresError: relation "users" already exists

# Use migrate:sync to fix the state
database-cli migrate:sync --dry-run  # Preview first
database-cli migrate:sync            # Sync and run remaining migrations

The command will:

1. Mark migrations as "applied" if their target tables already exist
2. Run migrations for tables that don't exist
3. Warn about ambiguous cases (some tables exist, some don't)

View Migration Status

database-cli migrate:status [options]

Shows which migrations have been applied and which are pending.

Database Management

Create Database

database-cli db:create [-e environment]

Drop Database

database-cli db:drop [-e environment] [-f]

Reset Database

database-cli db:reset [-e environment] [-f]

Drops the database, recreates it, and runs all migrations.

Open Console

database-cli console [-e environment]

Opens an interactive psql shell connected to the database.

Schema Introspection

View database schema like Rails' db/schema.rb:

# View all tables
database-cli schema

# View specific table
database-cli schema --table users

# Show with indexes
database-cli schema --show-indexes

# Show only polymorphic relationships
database-cli schema --polymorphic

# Export as JSON (useful for AI agents)
database-cli schema --json

Options:

• --table <name> - View single table schema
• --show-fks - Show foreign key references (default: true)
• --show-indexes - Show table indexes
• --polymorphic - Show only polymorphic relationships
• --json - Output schema as JSON

Example Output:

Database Schema (dev)
────────────────────────────────────────────────────────────────

Table: users
  id            SERIAL       PRIMARY KEY
  email         TEXT         NOT NULL, UNIQUE
  name          TEXT
  created_at    TIMESTAMP    DEFAULT NOW()

Table: posts
  id            SERIAL       PRIMARY KEY
  title         TEXT         NOT NULL
  user_id       INTEGER      -> users(id)

Table: votes
  id            SERIAL       PRIMARY KEY
  voteable_id   INTEGER
  voteable_type TEXT         <- polymorphic
  user_id       INTEGER      -> users(id)

Polymorphic Relationships:
  * voteable (voteable_id + voteable_type) on votes

Docker Management

Start Containers

database-cli docker:up [-e environment]

Starts PostgreSQL containers and waits for them to be ready.

Stop Containers

database-cli docker:down [-e environment]

Container Status

database-cli docker:status

Seeding

database-cli seed [options]

Options:

• -e, --environment <env> - Target environment
• --file <name> - Run specific seed file

Seed files should be placed in db/seeds/ and export a default function or seed function.

Custom Connection Options

All database commands support custom connection options:

# Use custom port
database-cli migrate -p 15432

# Use custom host
database-cli migrate -H db.example.com

# Use custom database name
database-cli migrate -d my_custom_db

# Combine options
database-cli db:create -p 5555 -H localhost -d myapp_production

Environment Configuration

Environment Variables

The CLI respects these environment variables:

# Default connection
DATABASE_URL=postgres://user:pass@host:port/database

# Environment-specific (override defaults)
DATABASE_URL_DEV=postgres://...
DATABASE_URL_TEST=postgres://...
DATABASE_URL_STAGING=postgres://...

# Individual components
DB_HOST=localhost
DB_HOST_DEV=localhost
DB_PORT=5432
DB_PORT_TEST=5433
DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=myapp_dev

Default Ports by Environment

• dev: 5432
• test: 5433
• staging: 5434

Migration File Format

Migrations use a simple SQL format with UP and DOWN sections:

-- UP
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT UNIQUE,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

-- DOWN
DROP TABLE users;

Generated Code Examples

Schema (UserSchema.ts)

import type { ColumnDefinition, SchemaDefinition } from "database-cli";

export interface User {
  id: number;
  name: string;
  email: string;
  created_at: string;
  updated_at: string;
}

export type UserCreate = Omit<User, "id" | "created_at" | "updated_at">;
export type UserUpdate = Partial<UserCreate>;

export const UserSchema: SchemaDefinition = {
  tableName: "users",
  columns: {
    id: { type: "SERIAL", primaryKey: true },
    name: { type: "TEXT" },
    email: { type: "TEXT", unique: true },
    created_at: { type: "TIMESTAMP" },
    updated_at: { type: "TIMESTAMP" },
  },
};

Service (UserService.ts)

import { BaseService } from "./BaseService";
import type { User, UserCreate, UserUpdate } from "../models/schema/UserSchema";

export class UserService extends BaseService<User, UserCreate, UserUpdate> {
  constructor(sql: any) {
    super(sql, "users");
  }
}

Route (users.ts)

import { Hono } from "hono";
import { UserService } from "../../services/UserService";

const app = new Hono();

app.get("/", async (c) => {
  const service = new UserService(c.get("sql"));
  const users = await service.findAll();
  return c.json(users);
});

app.get("/:id", async (c) => {
  const service = new UserService(c.get("sql"));
  const user = await service.findById(parseInt(c.req.param("id")));
  if (!user) return c.json({ error: "Not found" }, 404);
  return c.json(user);
});

app.post("/", async (c) => {
  const service = new UserService(c.get("sql"));
  const data = await c.req.json();
  const user = await service.create(data);
  return c.json(user, 201);
});

app.put("/:id", async (c) => {
  const service = new UserService(c.get("sql"));
  const data = await c.req.json();
  const user = await service.update(parseInt(c.req.param("id")), data);
  return c.json(user);
});

app.delete("/:id", async (c) => {
  const service = new UserService(c.get("sql"));
  await service.delete(parseInt(c.req.param("id")));
  return c.json({ success: true });
});

export default app;

Programmatic Usage

The CLI can also be used as a library:

import {
  createDatabaseService,
  createMigrationService,
  createSchemaService,
  GeneratorService,
  DockerService,
} from "database-cli";

// Create services
const dbService = createDatabaseService(process.cwd(), "dev");
const migrationService = createMigrationService(process.cwd(), "dev");
const schemaService = createSchemaService(dbService);
const generatorService = new GeneratorService(process.cwd());
const dockerService = new DockerService(process.cwd());

// Use custom connection
const customDbService = createDatabaseService(process.cwd(), "dev", {
  port: 15432,
  host: "db.example.com",
  database: "custom_db",
});

// Database operations
await dbService.create();
await dbService.testConnection();
const tables = await dbService.listTables();

// Schema introspection
const schema = await schemaService.getFullSchema();
const tableDetails = await schemaService.getTableSchema("users");
const polymorphic = await schemaService.getAllPolymorphicPatterns();
const conflict = await schemaService.analyzeRelationConflict("Post", "Vote", "hasMany");

// Migration operations
await migrationService.up();
await migrationService.down(1);
const status = await migrationService.status();

// Code generation
const definition = generatorService.parseModelDefinition({
  name: "Product",
  columns: ["name:string", "price:decimal"],
});
await generatorService.generateAll({ name: "Product", columns: ["name:string"] });

// Docker operations
await dockerService.up("dev");
await dockerService.waitForReady("dev");
await dockerService.status();
await dockerService.cleanup();

Type Handling

Decimal/Numeric Columns Return Strings

DECIMAL and NUMERIC columns are returned as strings in JSON responses to preserve precision:

{
  "amount": "120000.50",
  "rate": "0.05"
}

This is intentional behavior to prevent floating-point precision loss. To convert in application code:

const amount = parseFloat(record.amount);
// or for precise calculations:
const precise = new Decimal(record.amount);

Timestamp Format

All timestamps are returned as ISO 8601 strings:

{
  "created_at": "2024-12-15T00:51:21.914Z"
}

Column Type Reference

| CLI Type | PostgreSQL Type | TypeScript Type | Notes | |----------|-----------------|-----------------|-------| | string | TEXT | string | | | text | TEXT | string | | | integer | INTEGER | number | | | bigint | BIGINT | number | Large integers (v1.4.0) | | smallint | SMALLINT | number | Small integers (v1.4.0) | | decimal | DECIMAL | string | Precision preserved | | boolean | BOOLEAN | boolean | | | timestamp | TIMESTAMP | string | ISO 8601 format. Use :default:now for NOW() | | date | DATE | string | Date only (v1.4.0) | | time | TIME | string | Time only (v1.4.0) | | uuid | UUID | string | Use :default:uuid for gen_random_uuid() (v1.4.0) | | money | NUMERIC(10,2) | number | Fixed precision currency (v1.4.0) | | text[] | TEXT[] | string[] | Text array (v1.4.0) | | json | JSONB | Record<string, unknown> | | | references | INTEGER | number | With foreign key | | enum[val1,val2,...] | PostgreSQL ENUM | Union type | Creates named type |

Naming Conventions

The CLI follows Rails-style naming conventions:

| Context | Convention | Example | |---------|------------|---------| | Model names | PascalCase | JobTitle, TimeOffRequest | | Table names | snake_case plural | job_titles, time_off_requests | | Route files | snake_case plural | job_titles.ts, time_off_requests.ts | | URL paths | kebab-case plural | /job-titles, /time-off-requests | | Foreign keys | snake_case_id | job_title_id, user_id | | Relation names | camelCase | jobTitle, timeOffRequests |

Advanced Relation Options

Inverse Relations (--inverse)

When using --belongs-to, add --inverse to automatically add the inverse hasMany relation on the parent model:

database-cli generate model Post title:string --belongs-to User --inverse

This creates:

• PostSchema.ts with user: belongsTo(User)
• Updates UserSchema.ts with posts: hasMany(Post)

Self-Referential Relations (--belongs-to-self)

For hierarchical data (like categories with subcategories or employees with managers):

database-cli generate model Department name:string --belongs-to-self parent

This creates:

• parent_id nullable foreign key referencing departments(id)
• parent: belongsTo(Department) relation

Scoped/Nested Routes (--scoped-to)

Generate routes nested under a parent resource:

database-cli generate route Employment --scoped-to Employee

Generates routes like:

• GET /employees/:employeeId/employments
• POST /employees/:employeeId/employments
• PATCH /employees/:employeeId/employments/:id

Workflow Action Routes (--workflow)

Generate action routes for workflow state changes:

database-cli generate route TimeOffRequest --workflow approve,deny,cancel

Generates:

• Standard CRUD routes plus:
• POST /time-off-requests/:id/approve
• POST /time-off-requests/:id/deny
• POST /time-off-requests/:id/cancel

Advanced Features (v1.3.0)

ENUM Type Support

Create PostgreSQL ENUM columns with automatic type generation:

# Basic enum
database-cli generate model Task status:enum[pending,active,completed]

# Enum with default value
database-cli generate model Task status:enum[pending,active,completed]:default:pending

# Multiple enums in one model
database-cli generate model Document status:enum[draft,published,archived] visibility:enum[public,private]

This generates:

• CREATE TYPE tasks_status_enum AS ENUM ('pending', 'active', 'completed') in migration
• TypeScript union type: status: 'pending' | 'active' | 'completed'
• Proper DROP TYPE IF EXISTS in down migration

Custom Foreign Key Names (--belongs-to Model:alias)

Use aliases when you need multiple foreign keys to the same table:

# Multiple FKs to same table
database-cli generate model Post --belongs-to User:author --belongs-to User:editor

# Creates: author_id and editor_id columns both referencing users(id)

The alias becomes the FK column name (author → author_id) while still referencing the correct table.

Nullable Foreign Keys (--belongs-to Model:nullable)

Create optional relationships with ON DELETE SET NULL:

# Optional relationship
database-cli generate model Post --belongs-to User:removed_by:nullable

# Creates: removed_by_id INTEGER (nullable) with ON DELETE SET NULL

Nullable FKs:

• Allow NULL values in the FK column
• Use ON DELETE SET NULL instead of ON DELETE CASCADE
• Generate optional TypeScript types (removed_by_id?: number)

Composite Primary Keys (--composite-pk, --skip-id)

Create join tables or tables with composite primary keys:

# Join table with composite PK
database-cli generate model CommunityMembership role:string \
  --belongs-to Community --belongs-to User \
  --composite-pk community_id,user_id --skip-id

Options:

• --composite-pk col1,col2,... - Specify columns for composite primary key
• --skip-id - Skip auto-generated id SERIAL PRIMARY KEY column

This generates:

CREATE TABLE community_memberships (
  community_id INTEGER NOT NULL REFERENCES communities(id),
  user_id INTEGER NOT NULL REFERENCES users(id),
  role TEXT,
  PRIMARY KEY (community_id, user_id)
);

Testing

Test Philosophy

Tests verify that the CLI works exactly as users (both human and AI) would invoke it:

1. Real subprocess execution - Commands run via Bun.spawn(), not direct function calls
2. Isolated environments - Each test gets its own temp directory and database
3. Real database operations - Docker containers with actual PostgreSQL
4. Dynamic code verification - Generated models are imported and executed to verify they work
5. Comprehensive coverage - 291 tests covering all commands, edge cases, and error handling

Running Tests

# Run all tests
bun test

# Run CLI integration tests only
bun test tests/cli/

# Run service unit tests only
bun test tests/services/

# Run specific test file
bun test tests/cli/init.cli.test.ts

# Run with pattern match
bun test -t "workflow"

Test Coverage

| Category | Tests | Description | |----------|-------|-------------| | CLI Integration | 175 | All commands via subprocess | | Service Unit | 116 | DatabaseService, MigrationService, GeneratorService, etc. | | Total | 291 | |

See tests/cli/README.md for detailed test documentation and patterns.

Project Structure

database-cli/
├── src/
│   ├── index.ts              # Public API exports
│   ├── types.ts              # TypeScript definitions
│   ├── cli/
│   │   ├── index.ts          # CLI entry point
│   │   ├── output.ts         # Console output utilities
│   │   ├── prompt.ts         # Interactive prompts
│   │   └── commands/         # Command implementations
│   ├── services/
│   │   ├── DatabaseService.ts    # Database lifecycle & introspection
│   │   ├── DockerService.ts      # Docker Compose management
│   │   ├── FileService.ts        # File operations
│   │   ├── GeneratorService.ts   # Code generation
│   │   ├── MigrationService.ts   # Migration management
│   │   ├── SchemaService.ts      # Schema introspection & conflict detection
│   │   └── TemplateService.ts    # EJS template processing
│   ├── utils/
│   │   ├── naming.ts         # String transformations
│   │   ├── sql-types.ts      # Type mapping
│   │   └── timestamp.ts      # Migration timestamps
│   └── templates/            # EJS templates for code generation
├── tests/
│   ├── naming.test.ts
│   ├── sql-types.test.ts
│   ├── timestamp.test.ts
│   └── integration.test.ts
├── package.json
└── tsconfig.json

Requirements

• Bun >= 1.0.0
• PostgreSQL >= 14 (for local development)
• Docker (optional, for docker:* commands)
• psql (optional, for console command)

Publishing

From the repository root:

bun run publish:database

Or directly:

./scripts/codeartifact-publish.sh Modules/database-cli

License

MIT