npm package: @redredchen01/db-tools
Description: Database migration and management CLI — migrate, rollback, status, seed, diff, dump, reset
Author: redredchen01
Latest version: 1.0.0

# @redredchen01/db-tools

> Database migration and management CLI — migrate, rollback, status, seed, diff, dump, reset

[![npm version](https://img.shields.io/npm/v/@redredchen01/db-tools.svg)](https://www.npmjs.com/package/@redredchen01/db-tools)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

## Install

```bash
npm install -g @redredchen01/db-tools
```

## Quick Start

```bash
# Set DB URL once
export DB_URL=postgres://user:pass@localhost:5432/mydb

db-tools status          # check migration status
db-tools migrate         # run pending migrations
db-tools rollback        # rollback last migration
db-tools seed            # seed the database
db-tools dump --schema-only --output schema.sql
```

## Commands

### `migrate` — Run pending migrations

Execute all pending SQL files from `./migrations/` sorted by filename (YYYYMMDDHHMMSS prefix recommended).

```bash
db-tools migrate --url postgres://user:pass@localhost:5432/mydb
db-tools migrate --dir ./db/migrations --strict
```

Options: `--url`, `--dir DIR`, `--strict`, `--json`

---

### `rollback` — Rollback last N migrations

Removes tracking entries from `_foundry_migrations`. Does NOT execute reverse SQL automatically.

```bash
db-tools rollback --url postgres://...
db-tools rollback --count 3
```

Options: `--url`, `--count N`, `--json`

---

### `status` — Show migration status

List all migration files with `[OK]` (executed) or `[PEND]` (pending) indicators.

```bash
db-tools status --url postgres://...
db-tools status --dir ./db/migrations
```

Options: `--url`, `--dir DIR`, `--json`

---

### `seed` — Execute seed files

Run all `*.sql` files in `./seeds/` directory. Seeds are not tracked and re-execute every run.

```bash
db-tools seed --url postgres://...
db-tools seed --dir ./db/seeds
```

Options: `--url`, `--dir DIR`, `--json`

---

### `diff` — Compare two schemas

Dump and diff schemas from two database URLs to detect schema drift.

```bash
db-tools diff \
  --url1 postgres://user:pass@localhost:5432/mydb_dev \
  --url2 postgres://user:pass@staging:5432/mydb_staging
```

Options: `--url1 URL`, `--url2 URL`, `--json`

---

### `dump` — Export schema or data

Export using `pg_dump` (PostgreSQL) or `mysqldump` (MySQL).

```bash
db-tools dump --url postgres://... --schema-only --output schema.sql
db-tools dump --url mysql://... --data-only --output data.sql
db-tools dump --url postgres://... > full_dump.sql
```

Options: `--url`, `--schema-only`, `--data-only`, `--output FILE`, `--json`

---

### `reset` — Drop all tables and re-migrate

**DESTRUCTIVE.** Drops all tables and re-runs all migrations. Requires typing `yes` three times.

```bash
db-tools reset --url postgres://user:pass@localhost:5432/mydb_dev
db-tools reset --url postgres://... --force   # skip confirmations (CI only)
```

Options: `--url`, `--dir DIR`, `--force`, `--json`

---

## Database URL Format

```
postgres://user:pass@host:port/dbname
postgresql://user:pass@host:port/dbname
mysql://user:pass@host:port/dbname
```

Set via `--url` flag or `DB_URL` environment variable.

## Migration Tracking

Executed migrations are tracked in a `_foundry_migrations` table (auto-created on first run).

## JSON Output (Pipe Protocol)

All commands support `--json` for structured output compatible with the Skill Foundry pipe protocol:

```bash
db-tools status --json | some-other-tool
```

## License

MIT