npm package: @sqlml/cli
Description: DBML-first database toolkit — generate TypeORM/Prisma models, manage migrations, version schemas, and browse data via Web UI.
Author: Ruffiano
Latest version: 0.3.8

<p align="center">
  <h1 align="center">@sqlml/cli</h1>
  <p align="center">DBML-first database toolkit for TypeORM and Prisma</p>
</p>

<p align="center">
  <a href="https://www.npmjs.com/package/@sqlml/cli"><img src="https://img.shields.io/npm/v/@sqlml/cli.svg" alt="npm version"></a>
  <a href="https://www.npmjs.com/package/@sqlml/cli"><img src="https://img.shields.io/npm/dm/@sqlml/cli.svg" alt="npm downloads"></a>
  <a href="https://github.com/Ruffiano/sqlml/blob/main/LICENSE"><img src="https://img.shields.io/npm/l/@sqlml/cli.svg" alt="license"></a>
  <a href="https://github.com/Ruffiano/sqlml"><img src="https://img.shields.io/badge/node-%3E%3D20.0.0-brightgreen.svg" alt="node version"></a>
</p>

---

Design your database schema in **DBML**, generate **TypeORM entities** or **Prisma schemas**, manage **migrations**, track **schema versions**, and browse your data with a built-in **Web UI** — all from one CLI.

## Install

```bash
npm install -g @sqlml/cli
```

## Quick Start

```bash
# 1. Initialize project (picks TypeORM or Prisma)
sqlml init

# 2. Edit your DBML schema
#    → sqlml/src/lib/schema/my-schema.dbml

# 3. Generate ORM models from DBML
sqlml generate my-schema

# 4. Set up migrations
sqlml migration:init
sqlml migration:generate add-users
sqlml migration:up

# 5. Launch the Web UI
sqlml ui
```

## What It Does

| Capability | Description |
|------------|-------------|
| **Schema Design** | Write database schemas in DBML — a clean, readable DSL |
| **Model Generation** | Auto-generate TypeORM entity classes or Prisma schema files |
| **Migrations** | Generate, run, revert, and track migrations (both ORMs) |
| **Schema Versioning** | Snapshots, diffs, and change history for your DBML |
| **Web UI** | Visual dashboard, schema editor, data browser, SQL query editor |
| **Multi-Database** | PostgreSQL, MySQL, MariaDB, SQLite, MSSQL, Oracle |

## DBML Example

> Validate your DBML schema online at [dbdiagram.io](https://dbdiagram.io/d)

```dbml
Table users {
  id uuid [primary key, default: `uuid_generate_v4()`]
  username varchar [not null, unique]
  email varchar [not null, unique]
  role user_role [not null, default: 'user']
  created_at timestamptz [default: `now()`]
}

Table posts {
  id uuid [primary key, default: `uuid_generate_v4()`]
  title varchar(255) [not null]
  body text
  author_id uuid [not null, ref: > users.id]
  published_at timestamptz
}

Enum user_role {
  admin
  user
  moderator
}
```

## Commands

### Core

```bash
sqlml init                    # Initialize project (interactive)
sqlml generate [schema]       # Generate SQL + entities from DBML
sqlml direct                  # Generate entities from existing database
sqlml ui                      # Start Web UI at localhost:4200
sqlml ui-stop                 # Stop Web UI
```

### Migrations

```bash
sqlml migration:init          # Baseline existing database
sqlml migration:generate <n>  # Generate migration from schema diff
sqlml migration:up            # Run pending migrations
sqlml migration:revert        # Revert last migration
sqlml migration:status        # Show migration status
sqlml migration:from-dbml [n] # Generate migration from DBML changes
```

### Schema Versioning

```bash
sqlml diff                    # Show schema changes since last snapshot
sqlml snapshot:create         # Create schema snapshot
sqlml snapshot:list           # List snapshots
sqlml snapshot:show <version> # Show specific snapshot
sqlml schema:history          # Full change history
```

## ORM Support

### TypeORM

```bash
sqlml init        # → Select "typeorm"
sqlml generate    # → Generates decorated entity classes
```

Generates entity files with `@Entity`, `@Column`, `@PrimaryColumn`, relationship decorators, indexes, and configurable naming conventions.

### Prisma

```bash
sqlml init        # → Select "prisma"
sqlml generate    # → Generates schema.prisma
```

- Generates `schema.prisma` from DBML
- `migration:init` baselines with `prisma migrate diff` + auto-adds `uuid-ossp` extension
- `migration:up` runs `prisma migrate deploy`
- `migration:generate` uses `prisma migrate dev --create-only`

## Web UI

```bash
sqlml ui
```

Opens at **http://localhost:4200** with:

- **Dashboard** — Project overview, ORM-specific command reference
- **Schema Editor** — Monaco Editor with DBML syntax highlighting
- **Schema Diff** — Side-by-side snapshot comparison with color-coded changes
- **Migrations** — View, run, and revert migrations
- **Database** — Connection manager, schema tree browser, paginated data grid with inline editing, SQL query editor (Cmd+Enter to run)
- **Settings** — Configuration editor

See [@sqlml/ui](https://www.npmjs.com/package/@sqlml/ui) for standalone installation.

## Database to Entities (Direct Mode)

Generate entities from an existing database without DBML:

```bash
# PostgreSQL
sqlml -h localhost -d mydb -u postgres -x pass -e postgres -o ./src/entities

# MySQL
sqlml -h localhost -d mydb -u root -x pass -e mysql -p 3306 -o ./src/entities

# SQLite
sqlml -d ./database.sqlite -e sqlite -o ./src/entities

# MSSQL
sqlml -h localhost -d mydb -u sa -x pass -e mssql -o ./src/entities
```

## Configuration

Settings are stored in `.config-sqlml` (auto-created by `sqlml init`).

> Add `.config-sqlml` and `.sqlml/` to `.gitignore` to protect credentials.

### Connection

| Variable | Default | Description |
|----------|---------|-------------|
| `ORM_TYPE` | `typeorm` | ORM to use: `typeorm` or `prisma` |
| `DB_TYPE` | `postgres` | Database engine: `postgres`, `mysql`, `mariadb`, `sqlite`, `mssql`, `oracle` |
| `DB_HOST` | `localhost` | Database server hostname |
| `DB_PORT` | `5432` | Database server port |
| `DB_NAME` | — | Database name (or file path for SQLite) |
| `DB_USER` | — | Database username |
| `DB_PASSWORD` | — | Database password |
| `DB_SCHEMA` | `public` | Schema(s) to use — comma-separated for multiple (e.g. `auth,store,cms`) |
| `DB_SSL` | `false` | Enable SSL connection |

### File Paths

| Variable | Default | Description |
|----------|---------|-------------|
| `SCHEMA_DIR` | `sqlml/src/lib/schema` | Directory for `.dbml` schema files |
| `SQL_DIR` | `sqlml/src/lib/sql` | Directory for generated SQL files |
| `ENTITIES_DIR` | `sqlml/src/lib/entities` | Directory for generated entity/model files |
| `MIGRATIONS_DIR` | `sqlml/src/lib/migrations` | Directory for migration files |

### Table Filtering

| Variable | Default | Description |
|----------|---------|-------------|
| `SKIP_TABLES` | — | Comma-separated list of tables to exclude from generation |
| `ONLY_TABLES` | — | Comma-separated list of tables to include (excludes all others) |

### Naming Conventions

| Variable | Default | Values | Description |
|----------|---------|--------|-------------|
| `CASE_FILE` | `param` | `param`, `pascal`, `camel`, `snake`, `none` | File naming convention (e.g. `user-profile.ts`) |
| `CASE_ENTITY` | `pascal` | `pascal`, `camel`, `snake`, `none` | Entity class naming (e.g. `UserProfile`) |
| `CASE_PROPERTY` | `camel` | `pascal`, `camel`, `snake`, `none` | Property naming (e.g. `firstName`) |
| `SUFFIX_FILE` | — | any string | Suffix appended to file names (e.g. `.entity`) |
| `SUFFIX_CLASS` | — | any string | Suffix appended to class names (e.g. `Entity`) |

### Generation Options

| Variable | Default | Description |
|----------|---------|-------------|
| `PROPERTY_VISIBILITY` | `none` | Property visibility modifier: `none`, `public`, `protected`, `private` |
| `STRICT_MODE` | `none` | TypeScript strict mode: `none` or `!` (definite assignment) |
| `LAZY` | `false` | Generate lazy-loaded relations |
| `ACTIVE_RECORD` | `false` | Use Active Record pattern (extends `BaseEntity`) |
| `RELATION_IDS` | `true` | Generate `@RelationId` decorated properties |
| `SKIP_SCHEMA` | `false` | Skip `schema` option in `@Entity` decorator |
| `GENERATE_CONSTRUCTOR` | `true` | Generate constructor in entity classes |
| `PLURALIZE_NAMES` | `true` | Pluralize relation property names |
| `INDEX_FILE` | `true` | Generate `index.ts` barrel file for entities |
| `EXPORT_TYPE` | `named` | Export style: `named` or `default` |
| `EOL` | `LF` | Line ending style: `LF` or `CRLF` |

### Web UI

| Variable | Default | Description |
|----------|---------|-------------|
| `UI_PORT` | `4200` | Port for the Web UI server |
| `UI_SERVER_PORT` | — | Override server port (takes precedence over `UI_PORT`) |
| `UI_THEME` | `dark` | UI color theme: `dark` or `light` |
| `UI_OPEN_BROWSER` | `true` | Automatically open browser when running `sqlml ui` |

### Example

```ini
# Connection
ORM_TYPE=typeorm
DB_TYPE=postgres
DB_HOST=localhost
DB_PORT=5432
DB_NAME=mydatabase
DB_USER=postgres
DB_PASSWORD=
DB_SCHEMA=public
DB_SSL=false

# File Paths
SCHEMA_DIR=./sqlml/src/lib/schema
SQL_DIR=./sqlml/src/lib/sql
ENTITIES_DIR=./sqlml/src/lib/entities
MIGRATIONS_DIR=./sqlml/src/lib/migrations

# Table Filtering
# SKIP_TABLES=migrations,seeds
# ONLY_TABLES=users,posts,comments

# Naming Conventions
CASE_FILE=param
CASE_ENTITY=pascal
CASE_PROPERTY=camel

# Web UI
UI_PORT=4200
# UI_SERVER_PORT=4200
UI_THEME=dark
UI_OPEN_BROWSER=true

# Generation Options
PROPERTY_VISIBILITY=none
STRICT_MODE=none
LAZY=false
ACTIVE_RECORD=false
RELATION_IDS=true
SKIP_SCHEMA=false
GENERATE_CONSTRUCTOR=true
PLURALIZE_NAMES=true
INDEX_FILE=true
EXPORT_TYPE=named
EOL=LF
```

## Supported Databases

| Database | Engine | Driver |
|----------|--------|--------|
| PostgreSQL | `postgres` | pg |
| MySQL | `mysql` | mysql2 |
| MariaDB | `mariadb` | mysql2 |
| SQLite | `sqlite` | sqlite3 |
| MSSQL | `mssql` | mssql |
| Oracle | `oracle` | oracledb |

## Requirements

- **Node.js** >= 20.0.0 (tested on 20.x, 22.x, 23.x, 24.x, 25.x)
- **TypeORM** >= 0.3.20 or **Prisma** (peer dependency)

## Links

- [GitHub Repository](https://github.com/Ruffiano/sqlml)
- [Report Issues](https://github.com/Ruffiano/sqlml/issues)
- [@sqlml/ui on npm](https://www.npmjs.com/package/@sqlml/ui)

## License

MIT