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. version: 3.

Installation

npm install sqlml --save-dev

Or globally:

npm install -g sqlml

Commands

SQLML provides several commands to help you manage your database models:

| Command | Description | |---------|-------------| | sqlml | Display possible commands | | sqlml init | Initialize project structure and configuration | | sqlml generate <schema> | Generate SQL and TypeORM entities from a DBML schema | | sqlml direct | Generate TypeORM entities directly from a database | | sqlml -h <host> -d <database> ... | Generate TypeORM models directly from a database | | sqlml --help | Show help information | | sqlml --version | Show the current version of SQLML |

Basic Usage

Initializing a Project

Set up the directory structure and configuration file:

sqlml init

This creates:

• A directory for DBML schema files
• A directory for generated SQL files
• A directory for TypeORM entity models
• A sample DBML file
• A .config-sqlml configuration file

Generating from DBML Schema

Once you've designed your database in DBML:

sqlml generate users

This:

1. Converts the DBML to SQL
2. Applies the SQL to your database
3. Generates TypeORM entity models

Direct Database to Entity Generation

Generate TypeORM entities directly from an existing database:

sqlml -h localhost -d mydatabase -u myuser -x mypassword -e postgres -o ./src/entities

Interactive Mode

Launch the interactive wizard to configure and generate entities:

sqlml

This will guide you through the process of connecting to your database and configuring entity generation options.

SQLML Command With npx CLI

You can also run SQLML using npx without installing it globally:

npx sqlml 

Development Environment

To set up a development environment, ensure you have the following:

• Node.js 20.x or higher
• TypeScript v.5.x or higher
• TypeORM v.0.3.x or higher
• Database client tools (e.g., psql, mysql, sqlcmd, docker)

Configuration File

SQLML uses a .config-sqlml file to store settings. When this file exists, running sqlml with no arguments will use these settings automatically.

Example configuration:

# Database connection settings
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

# Database 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 type (postgres, mysql, etc.)
-s, --schema        Schema name(s), comma-separated
-i, --instance      Named instance (for MSSQL)
--ssl               Use SSL connection

# Table filtering
--skipTables        Tables to exclude, comma-separated
--tables            Tables to include, comma-separated

# Generation options
-o, --output        Output directory for generated models
--noConfig          Don't create tsconfig.json and ormconfig.json
--cf, --case-file   Case convention for file names (pascal, param, camel, none)
--ce, --case-entity Case convention for entity class names (pascal, camel, none)
--cp, --case-property  Case convention for property names (camel, pascal, snake, none)
--eol               End of line character (LF, CRLF)
--pv, --property-visibility  Property visibility (public, protected, private, none)
--lazy              Generate lazy relations
-a, --active-record Use ActiveRecord pattern
--namingStrategy    Path to custom naming strategy file
--relationIds       Generate RelationId fields
--skipSchema        Omit schema identifier in generated entities
--generateConstructor  Generate constructor allowing partial initialization
--disablePluralization  Disable pluralization of relation names
--strictMode        TypeScript strict mode (none, ?, !)
--index             Generate index file
--defaultExport     Use default exports instead of named exports
--suffixFile        Suffix to append to generated file names
--suffixClass       Suffix to append to generated class names

For a complete list of options, run:

sqlml --help

Customization Options

SQLML provides extensive customization options for generated entities:

Naming Conventions

• File names: PascalCase, param-case, camelCase, or original case
• Entity names: PascalCase, camelCase, or original case
• Property names: camelCase, PascalCase, snake_case, or original case

Relationship Options

• Lazy loading: Generate lazy relations with Promise<Type>
• Relation IDs: Generate additional fields for foreign key values
• Pluralization: Automatically pluralize collection relation names

TypeScript Features

• Strict mode: Choose between optional properties (?), non-null assertions (!), or no strict mode
• Property visibility: Set properties as public, protected, private, or default
• Constructors: Generate constructors with partial initialization

Output Options

• Active Record: Use TypeORM's Active Record pattern
• Index file: Generate an index file that exports all entities
• Export type: Use default or named exports

Examples

Basic PostgreSQL Example

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

MySQL with Custom Naming

sqlml -h localhost -d myapp -u root -x root -e mysql --cf=param --ce=pascal --cp=camel -o ./src/models

SQLite with Active Record Pattern

sqlml -d ./mydb.sqlite -e sqlite -a true -o ./src/entities

MSSQL with Schema and Instance

sqlml -h sqlserver -i SQLEXPRESS -d northwind -u sa -x securepass -e mssql -s dbo -o ./src/models

Advanced Usage

Filtering Tables

To include or exclude specific tables:

# Include only specific tables
sqlml -h localhost -d postgres -u postgres -e postgres --tables="users,orders,products"

# Exclude specific tables
sqlml -h localhost -d postgres -u postgres -e postgres --skipTables="migrations,logs,audit"

Custom Naming Strategy

Create a custom naming strategy module and use it:

sqlml -h localhost -d postgres -u postgres -e postgres --namingStrategy="./my-naming-strategy.js"

DBML Workflow

For a complete database development workflow:

1. Initialize your project:

   sqlml init

2. Design your database in DBML: Edit the generated .dbml files in your schema directory.

3. Generate SQL and entities:

   sqlml generate my-schema

4. Use the generated TypeORM entities in your application

Supported Databases

• PostgreSQL
• MySQL
• MariaDB
• Microsoft SQL Server
• SQLite
• Oracle

Important Notes

• Ensure you have the necessary database client tools installed (e.g., psql, mysql, sqlcmd, docker) for your database engine.
• Ensure you add .config-sqlml to your .gitignore file to avoid committing sensitive information like database credentials.

Troubleshooting

If you encounter issues:

• Ensure your database connection details are correct
• Check that database client tools are properly installed
• Verify file paths and permissions
• If using DBML, make sure dbml2sql is installed (npm install -g @dbml/cli)

Support

If you encounter any issues or have questions, please open an issue on the GitHub repository.

License

MIT