SQLML: Database to TypeORM Model Generator

SQLML is a powerful tool that streamlines database development workflows by providing a complete pipeline from database schema design (DBML) to TypeORM entity models.

Table of Contents

• Installation
• Quick Start
• Commands Reference
• Usage Examples
• Migration System
• Schema Management
• Web UI
• Configuration
• Supported Databases
• Troubleshooting

Installation

Global Installation (Recommended)

npm install -g sqlml

After global installation, you can use sqlml command directly:

sqlml --help

Local Installation

npm install sqlml --save-dev

With local installation, use npx to run commands:

npx sqlml --help

Requirements

• Node.js 18.x or higher
• TypeScript v5.x or higher (for TypeORM entities)
• Database client tools for your database engine (optional, for SQL execution):
  • PostgreSQL: psql
  • MySQL/MariaDB: mysql
  • MSSQL: sqlcmd
  • Oracle: Oracle Instant Client

Quick Start

1. Initialize a New Project

sqlml init

This interactive command will:

• Create directory structure for schemas, SQL, and entities
• Set up database connection configuration
• Create a sample DBML schema file
• Save settings to .config-sqlml

2. Design Your Schema (DBML)

Edit the generated .dbml file in your schema directory:

Table users {
  id integer [primary key]
  username varchar [not null, unique]
  email varchar [not null, unique]
  created_at timestamp [default: `now()`]
}

Table posts {
  id integer [primary key]
  title varchar [not null]
  body text [not null]
  user_id integer [not null, ref: > users.id]
  created_at timestamp [default: `now()`]
}

3. Generate Entities

sqlml generate my-schema

This will:

1. Convert DBML to SQL
2. Apply SQL to your database
3. Generate TypeORM entity models

Commands Reference

Core Commands

| Command | Description | |---------|-------------| | sqlml | Show help or run with saved config | | sqlml init | Initialize project structure | | sqlml ui | Start the web UI (server and client) | | sqlml generate <schema> | Generate from DBML schema | | sqlml generate | Generate entities for all tables | | sqlml direct | Direct database to entity generation | | sqlml --help | Show help information | | sqlml --version | Show version |

Migration Commands

| Command | Description | |---------|-------------| | sqlml migration:init | Initialize migration system | | sqlml migration:create <name> | Create empty migration | | sqlml migration:generate <name> | Generate migration from changes | | sqlml migration:up | Run pending migrations | | sqlml migration:down | Revert migrations | | sqlml migration:status | Show migration status | | sqlml migration:show | Show pending migrations | | sqlml migration:validate | Validate migration files | | sqlml migration:force <version> | Mark as executed without running |

Schema Commands

| Command | Description | |---------|-------------| | sqlml diff | Show DBML schema changes | | sqlml snapshot:create | Create schema snapshot | | sqlml snapshot:list | List all snapshots | | sqlml snapshot:show <version> | Show specific snapshot | | sqlml migration:from-dbml [name] | Generate migration from DBML | | sqlml schema:history | Show schema change history |

Usage Examples

Generate Entities from Existing Database

PostgreSQL

sqlml -h localhost -d mydb -u postgres -x password -e postgres -o ./src/entities

MySQL

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

MariaDB

sqlml -h localhost -d mydb -u root -x password -e mariadb -p 3306 -o ./src/entities

SQLite

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

Microsoft SQL Server

sqlml -h localhost -d mydb -u sa -x password -e mssql -s dbo -o ./src/entities

MSSQL with Named Instance

sqlml -h localhost -i SQLEXPRESS -d mydb -u sa -x password -e mssql -o ./src/entities

Oracle

sqlml -h localhost -d ORCL -u system -x password -e oracle -p 1521 -o ./src/entities

Interactive Mode

Run without arguments to use the interactive wizard:

sqlml

Or use the direct command for interactive mode:

sqlml direct

Using npx (Without Global Install)

# Show help
npx sqlml --help

# Initialize project
npx sqlml init

# Generate from database
npx sqlml -h localhost -d mydb -u postgres -x pass -e postgres -o ./entities

# Generate from DBML
npx sqlml generate my-schema

With Custom Options

# Custom naming conventions
sqlml -h localhost -d mydb -u postgres -x pass -e postgres \
  --cf=param \
  --ce=pascal \
  --cp=camel \
  -o ./src/entities

# With Active Record pattern
sqlml -h localhost -d mydb -u postgres -x pass -e postgres \
  --active-record \
  -o ./src/entities

# Filter specific tables
sqlml -h localhost -d mydb -u postgres -x pass -e postgres \
  --tables="users,posts,comments" \
  -o ./src/entities

# Skip certain tables
sqlml -h localhost -d mydb -u postgres -x pass -e postgres \
  --skipTables="migrations,logs,sessions" \
  -o ./src/entities

Migration System

Initialize Migration System

sqlml migration:init

This creates the migration directory structure and configuration.

Create a Migration

# Create empty migration
sqlml migration:create add-user-roles

# Generate migration from schema changes
sqlml migration:generate add-user-roles

Run Migrations

# Run all pending migrations
sqlml migration:up

# Dry run (show SQL without executing)
sqlml migration:up --dry-run

# Run up to specific version
sqlml migration:up --to 000003

# Skip confirmation prompt
sqlml migration:up --force

Revert Migrations

# Revert last migration
sqlml migration:down

# Revert last 3 migrations
sqlml migration:down --steps 3

# Revert all migrations
sqlml migration:down --all

# Revert to specific version
sqlml migration:down --to 000002

# Dry run
sqlml migration:down --dry-run

Check Migration Status

# Show all migrations and their status
sqlml migration:status

# Show only pending migrations
sqlml migration:show

# Validate migration files
sqlml migration:validate

Schema Management

Working with DBML Schemas

# Show changes between current schema and last snapshot
sqlml diff

# Show changes in specific format
sqlml diff --format=sql
sqlml diff --format=json
sqlml diff --format=table

# Compare specific versions
sqlml diff --from=000001 --to=000003

Snapshots

# Create a snapshot of current schema
sqlml snapshot:create --name "Initial schema"

# List all snapshots
sqlml snapshot:list

# Show specific snapshot
sqlml snapshot:show 000001

Generate Migration from DBML Changes

# Generate migration from DBML changes
sqlml migration:from-dbml add-new-tables

# Auto-generate migration name from changes
sqlml migration:from-dbml --auto-name

# Dry run
sqlml migration:from-dbml --dry-run

View Schema History

sqlml schema:history

Web UI

SQLML includes a web-based user interface for visual database management. The UI uses SQLite for metadata storage, so no external database server is required.

Quick Start

Start the Web UI with a single command:

sqlml ui

This will:

• Check for configuration (create if missing)
• Install UI dependencies automatically (first run)
• Start both backend and frontend servers
• Display URLs when ready

Once started, open your browser:

• Frontend: http://localhost:5173
• API Docs: http://localhost:4200/api/docs

Press Ctrl+C to stop all services.

UI Features

The web interface provides:

• Dashboard - Overview of your database and migrations
• Schema Editor - Visual DBML schema editing with Monaco Editor
• Migrations - View and manage database migrations
• Database - Database connection management
• Settings - Configuration options

Data Storage

The UI stores its metadata in a local SQLite database:

.sqlml/metadata.db

This file is created automatically in your project directory. No external database server is required.

Configuration

On first run, sqlml ui will check for a .config-sqlml file. If not found, you'll be prompted to:

• Create a default UI config
• Run sqlml init for full project setup
• Continue with defaults

The UI configuration file uses JSON format:

{
  "database": {
    "type": "postgres",
    "host": "localhost",
    "port": 5432,
    "name": "database",
    "user": "postgres",
    "ssl": false
  },
  "paths": {
    "migrations": "./migrations",
    "entities": "./src/entities",
    "schema": "./schema"
  },
  "generation": {
    "entityCase": "pascal",
    "fileCase": "pascal",
    "propertyCase": "camel",
    "pluralize": true
  },
  "ui": {
    "port": 4200,
    "theme": "dark",
    "openBrowser": true
  }
}

Environment Variables

| Variable | Default | Description | |----------|---------|-------------| | PORT | 4200 | Backend server port | | CORS_ORIGIN | http://localhost:5173 | Allowed CORS origin | | SQLML_DB_PATH | .sqlml/metadata.db | SQLite database path |

Alternative: NPM Scripts

You can also run the UI using npm scripts:

# Install UI dependencies
npm run ui:install

# Start backend server only
npm run ui:server

# Start frontend only
npm run ui:client

# Build for production
npm run ui:build

Configuration

Configuration File

SQLML uses .config-sqlml file to store settings:

# Database connection
DB_TYPE=postgres
DB_HOST=localhost
DB_PORT=5432
DB_NAME=mydatabase
DB_USER=postgres
DB_PASSWORD=yourpassword
DB_SCHEMA=public
DB_SSL=false

# Directory structure
SCHEMA_DIR=sqlml/src/lib/schema
SQL_DIR=sqlml/src/lib/sql
ENTITIES_DIR=sqlml/src/lib/entities

# Table filtering
SKIP_TABLES=migrations,temp_logs
ONLY_TABLES=

# Entity generation options
CASE_FILE=param
CASE_ENTITY=pascal
CASE_PROPERTY=camel
PROPERTY_VISIBILITY=none
STRICT_MODE=?
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
SUFFIX_FILE=
SUFFIX_CLASS=Entity

Command Line Options

Connection Options:
  -h, --host          Database server address
  -d, --database      Database name(s), comma-separated
  -u, --user          Database username
  -x, --pass          Database password
  -p, --port          Database port
  -e, --engine        Database engine (postgres, mysql, mariadb, mssql, sqlite, oracle)
  -s, --schema        Schema name(s), comma-separated
  -i, --instance      Named instance (MSSQL only)
  --ssl               Use SSL connection

Table Filtering:
  --tables            Include only these tables (comma-separated)
  --skipTables        Exclude these tables (comma-separated)

Generation Options:
  -o, --output        Output directory for entities
  --noConfig          Don't create tsconfig.json and ormconfig.json
  --cf, --case-file   File name case (pascal, param, camel, none)
  --ce, --case-entity Entity class case (pascal, camel, none)
  --cp, --case-property  Property case (camel, pascal, snake, none)
  --eol               Line ending (LF, CRLF)
  --pv, --property-visibility  Visibility (public, protected, private, none)
  --lazy              Generate lazy relations
  -a, --active-record Use ActiveRecord pattern
  --namingStrategy    Path to custom naming strategy
  --relationIds       Generate RelationId fields
  --skipSchema        Omit schema identifier
  --generateConstructor  Generate partial constructor
  --disablePluralization  Disable relation pluralization
  --strictMode        Strict mode (none, ?, !)
  --index             Generate index file
  --defaultExport     Use default exports
  --suffixFile        File name suffix
  --suffixClass       Class name suffix

Supported Databases

| Database | Driver | Status | |----------|--------|--------| | PostgreSQL | pg | Full Support | | MySQL | mysql2 | Full Support | | MariaDB | mysql2 | Full Support | | SQLite | sqlite3 | Full Support | | Microsoft SQL Server | mssql | Full Support | | Oracle | oracledb | Full Support |

Troubleshooting

Common Issues

Connection refused

• Verify database server is running
• Check host, port, and firewall settings
• Ensure credentials are correct

Permission denied

• Check database user permissions
• Verify schema access rights

Tables not found

• Verify schema name (use -s option)
• Check table name filters

DBML parsing errors

• Validate DBML syntax at https://dbml.dbdiagram.io/
• Check for missing semicolons or brackets

Debug Mode

For verbose output, check the generated SQL files in your SQL directory after running sqlml generate.

Database Client Tools

For the generate command to apply SQL directly, you need:

• PostgreSQL: psql command-line tool
• MySQL/MariaDB: mysql command-line tool

If these tools are not installed, SQLML will generate the SQL files but won't apply them automatically.

Important Notes

• Add .config-sqlml and .sqlml/ to .gitignore to protect credentials and local data
• The dbml2sql tool is bundled with SQLML (via @dbml/cli)
• Back up your database before running migrations in production
• The Web UI uses SQLite locally - no external database server required

Support

For issues or questions, please open an issue on the GitHub repository.

License

MIT