JATSG - Just another TypeScript Generator

Generate TypeScript interfaces from your SQL Server database schema automatically. Keep your types in sync with your database structure effortlessly.

🚀 Features

• One Command Setup - Generate all your database types with a single command
• Single File Output - All types in one file that updates when you run it again
• Comprehensive Type Mapping - Accurate SQL Server to TypeScript type conversion
• Flexible Configuration - CLI options, config files, or environment variables
• Schema Filtering - Choose which schemas and tables to include/exclude
• Smart Naming - Configurable interface naming conventions
• Null Safety - Optional null union types for nullable columns
• Rich Comments - JSDoc comments with SQL type information

📦 Installation

Global Installation (Recommended)

npm install -g jatsg

Local Project Installation

npm install --save-dev jatsg

🏃‍♂️ Quick Start

1. Initialize Environment

jatsg init

This creates:

• .env.example - Template with all configuration options
• .env - Your actual configuration file (gitignored)

2. Configure Database Connection

Edit the .env file with your database details:

DB_SERVER=localhost
DB_DATABASE=your_database_name
DB_USER=your_username
DB_PASSWORD=your_password
DB_ENCRYPT=true
DB_TRUST_CERTIFICATE=true

3. Generate Types

jatsg

🔧 CLI Usage

Basic Usage

# Using .env file (recommended)
jatsg

# Using config file
jatsg --config jatsg.config.json

# Using command line options
jatsg \
  --server localhost \
  --database myapp \
  --user myuser \
  --password mypass \
  --output ./src/types/database.ts

All CLI Options

jatsg [options]

Options:
  -V, --version                    output the version number
  -c, --config <path>              path to configuration file
  -s, --server <server>            SQL Server instance
  -d, --database <database>        database name
  -u, --user <user>                username
  -p, --password <password>        password
  -o, --output <path>              output file path (default: "./src/lib/db-types.ts")
  --port <port>                    SQL Server port
  --schemas <schemas>              comma-separated list of schemas (default: "dbo")
  --exclude <tables>               comma-separated list of tables to exclude (default: "sysdiagrams")
  --interface-format <format>      interface name format (default: "${table}Row")
  --no-comments                    exclude JSDoc comments
  --no-nullable                    don't add null union types
  --camel-case                     convert column names to camelCase
  --encrypt                        use encrypted connection
  --trust-server-certificate       trust server certificate (dev only)
  -h, --help                       display help for command

Commands:
  init [options]                   create a configuration file

Environment Variables (.env file)

The recommended approach is to use a .env file:

# Database connection (required)
DB_SERVER=localhost
DB_DATABASE=myapp
DB_USER=myuser
DB_PASSWORD=mypass
DB_PORT=1433
DB_ENCRYPT=true
DB_TRUST_CERTIFICATE=true

# Generation options (optional)
JATSG_OUTPUT=./src/lib/db-types.ts
JATSG_SCHEMAS=dbo,app
JATSG_EXCLUDE=sysdiagrams,migrations
JATSG_INTERFACE_FORMAT=${table}Row
JATSG_CAMEL_CASE=false
JATSG_INCLUDE_COMMENTS=true
JATSG_NULLABLE=true

Then simply run:

jatsg

⚙️ Configuration Methods

JATSG supports multiple configuration methods with the following priority (highest to lowest):

1. Command Line Options - Override everything
2. Config File - JSON configuration file
3. Environment Variables - .env file (recommended for local dev)
4. Defaults - Built-in sensible defaults

Method 1: Environment Variables (.env) - Recommended

# Initialize
jatsg init

# Edit .env file with your credentials
# Run generation
jatsg

Method 2: Config File

# Create config-only setup
jatsg init --config-only

# Edit jatsg.config.json
jatsg --config jatsg.config.json

Method 3: Command Line Only

jatsg --server localhost --database myapp --user myuser --password mypass

📄 Configuration File Format

The configuration file supports all CLI options plus additional settings:

{
    "server": "localhost",
    "database": "your_database",
    "user": "username",
    "password": "password",
    "port": 1433,
    "options": {
        "encrypt": true,
        "trustServerCertificate": true,
        "connectionTimeout": 30000,
        "requestTimeout": 30000
    },
    "output": "./src/lib/db-types.ts",
    "schemas": ["dbo", "app"],
    "exclude": ["sysdiagrams", "temp_table"],
    "interfaceNameFormat": "${table}Row",
    "includeComments": true,
    "nullable": true,
    "camelCase": false
}

📄 Generated Output

Example generated TypeScript file:

// Generated TypeScript interfaces from SQL Server database
// Generated on: 2024-01-15T10:30:00.000Z
// This file is auto-generated. Do not edit manually.

export interface UsersRow {
    /** int, not nullable */
    id: number;
    /** nvarchar(255), nullable */
    name?: string | null;
    /** varchar(100), not nullable */
    email: string;
    /** datetime2, nullable */
    created_at?: Date | null;
    /** bit, not nullable */
    is_active: boolean;
}

export interface ProductsRow {
    /** int, not nullable */
    product_id: number;
    /** nvarchar(500), not nullable */
    product_name: string;
    /** decimal(10,2), nullable */
    price?: number | null;
    /** text, nullable */
    description?: string | null;
}

// Database utility types
export type DatabaseTables = {
    users: UsersRow;
    products: ProductsRow;
};

export type TableNames = "users" | "products";

export type TableRow<T extends TableNames> = DatabaseTables[T];

🎯 Usage in Your Code

import type {
    UsersRow,
    ProductsRow,
    DatabaseTables,
    TableNames,
    TableRow,
} from "./src/lib/db-types";

// Use specific interfaces
const user: UsersRow = {
    id: 1,
    name: "John Doe",
    email: "[email protected]",
    created_at: new Date(),
    is_active: true,
};

// Use utility types
function getTableData<T extends TableNames>(
    tableName: T
): Promise<TableRow<T>[]> {
    // Your database query logic here
    return Promise.resolve([]);
}

// Type-safe database operations
const users = await getTableData("users"); // Returns UsersRow[]
const products = await getTableData("products"); // Returns ProductsRow[]

📝 NPM Scripts Integration

Add to your package.json:

{
    "scripts": {
        "generate-types": "jatsg",
        "types": "jatsg",
        "dev": "npm run types && vite dev",
        "build": "npm run types && vite build"
    }
}

For teams, you can also add a verification script:

{
    "scripts": {
        "types:check": "jatsg && git diff --exit-code src/lib/db-types.ts || (echo 'Database types are out of sync! Run npm run types' && exit 1)"
    }
}

🔄 Type Mapping

| SQL Server Type | TypeScript Type | | ------------------------------------------------------- | --------------- | | int, bigint, smallint, tinyint | number | | decimal, numeric, float, real, money | number | | varchar, nvarchar, char, nchar, text, ntext | string | | bit | boolean | | datetime, datetime2, date, smalldatetime | Date | | time | string | | uniqueidentifier | string | | binary, varbinary, image, timestamp | Buffer | | xml | string |

🔒 Security Best Practices

1. Use .env Files for local development (recommended):

   # .env file (automatically gitignored)
   DB_SERVER=localhost
   DB_DATABASE=myapp
   DB_USER=readonly_user
   DB_PASSWORD=secure_password

2. Use Read-Only Database User for type generation

3. Exclude Sensitive Tables using the exclude option

4. Enable Encryption for production databases

5. Never commit .env files or config files with credentials

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

📄 License

MIT License - see the LICENSE file for details.

🐛 Troubleshooting

Common Issues

Connection Failed

# Check your .env file has correct credentials
cat .env

# Try with trust server certificate for local development
echo "DB_TRUST_CERTIFICATE=true" >> .env

# Or use CLI override
jatsg --trust-server-certificate

# Verify SQL Server is accessible
telnet localhost 1433

No .env File

# Create .env files
jatsg init

# Or create manually
cp .env.example .env

Permission Denied

# Make sure your user has read permissions on INFORMATION_SCHEMA
# Grant minimal required permissions:
GRANT SELECT ON INFORMATION_SCHEMA.TABLES TO [your_user]
GRANT SELECT ON INFORMATION_SCHEMA.COLUMNS TO [your_user]

Types Not Generated

• Check that tables exist in the specified schema
• Verify exclude list doesn't contain your tables
• Ensure output directory is writable

📞 Support

• 🐛 Report Issues
• 💬 Discussions