Schemats: TypeScript Interface Generator

3.0.3 verified Thu Apr 23 auth: no javascript

Schemats is a command-line interface (CLI) tool designed to automatically generate TypeScript interface definitions directly from existing SQL database schemas, specifically supporting PostgreSQL and MySQL. The current stable version is 3.0.3. While it doesn't adhere to a strict release cadence, updates are made to enhance compatibility and features. Its primary differentiator lies in enabling strong static typing for database queries by bridging the gap between relational databases and TypeScript applications. Users can generate type definitions for entire schemas or specific tables, either through command-line arguments or a `schemats.json` configuration file, which then allows for type-safe database interactions and enhanced developer experience with autocompletion and static checks in their application code.

Common errors

error schemats: command not found ↓

cause The `schemats` executable is not in your system's PATH.

fix

Install globally: npm install -g schemats or run with npx: npx schemats generate ...

error Error: password authentication failed for user "postgres" ↓

cause Incorrect username, password, or host in the database connection string.

fix

Verify your database connection string, ensuring the username, password, host, and port are correct and the user has appropriate permissions. Example: postgres://user:password@host:port/database

error Error: database "mytestdb" does not exist ↓

cause The specified database in the connection string does not exist on the server.

fix

Ensure the database specified in your connection string (-c) has been created on your PostgreSQL or MySQL server.

error Error: No tables were found matching the specified criteria for schema 'public'. ↓

cause No tables were found in the specified schema, or the schema/table names were incorrect.

fix

Double-check the database name, schema name (-s), and table names (-t) in your command or schemats.json config. Ensure the connected user has permissions to see these tables.

Warnings

gotcha Embedding database credentials directly in command-line arguments or configuration files can pose a security risk, especially in shared environments or version control. Consider using environment variables for sensitive information. ↓

fix Use environment variables (e.g., `PGUSER=postgres PGHOST=localhost schemats generate -c "postgres://localhost/mydb" ...`) or a separate `.env` file loaded securely at runtime.

gotcha If `schemats` is not installed globally (`npm install -g schemats`), the `schemats` command might not be found directly in your shell's PATH. This leads to 'command not found' errors. ↓

fix Either install `schemats` globally (`npm install -g schemats`) or use `npx schemats` to execute the package binary from your `node_modules` without global installation.

breaking There's no documented automatic migration path or `ng update`-style schematic for major version upgrades (e.g., from v2 to v3). Users should review their setup and generated types for manual adjustments. ↓

fix Carefully review the `schemats` README and any release notes for the new major version. Regenerate your TypeScript types and manually adapt any application code that consumes them, as underlying type inference or naming conventions might have changed.

gotcha Generated types for `NUMERIC` or `DECIMAL` SQL types in PostgreSQL often map to `string` in TypeScript by default to prevent precision loss. If you require `number` types, you'll need to handle the conversion manually or configure your ORM/database client. ↓

fix When consuming the generated types, be aware that `price: string` might be expected instead of `price: number`. Parse or convert these values explicitly (e.g., `parseFloat(item.price)`) where numeric operations are required.

Install

npm install schemats

yarn add schemats

pnpm add schemats

Imports

GeneratedTypes
```
import * as dbTypes from './path/to/generated/db.ts'
```
Schemats itself is primarily a CLI tool; its main 'import' interaction is when user code imports the *generated* TypeScript files. The generated file typically exports interfaces under a namespace or as direct interfaces.
generate
wrong
```
const schemats = require('schemats'); schemats.generate(...)
```
correct
```
import { generate } from 'schemats'
```
While Schemats is mostly a CLI tool, its `generate` function can be imported and used programmatically. However, the CLI is the common method. Ensure your project is set up for ESM if importing directly.

Quickstart

This quickstart demonstrates how to set up a PostgreSQL database, create some tables, then use the `schemats` CLI to generate TypeScript interfaces, and finally shows a basic example of how these generated types would be used in application code.

import { Client } from 'pg';
import * as path from 'path';
import { execSync } from 'child_process';

// 1. Ensure schemats is installed globally or accessible via npx
// npm install -g schemats

const dbUrl = 'postgres://postgres:password@localhost:5432/mytestdb';
const outputFile = path.join(process.cwd(), 'db-types.ts');

async function setupAndGenerate() {
  // For demonstration, ensure a test database exists
  const client = new Client({
    connectionString: 'postgres://postgres:password@localhost:5432/postgres'
  });
  try {
    await client.connect();
    await client.query(`DROP DATABASE IF EXISTS mytestdb;`);
    await client.query(`CREATE DATABASE mytestdb;`);
    await client.end();

    const testClient = new Client({ connectionString: dbUrl });
    await testClient.connect();
    await testClient.query(`
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(255) NOT NULL UNIQUE,
        email VARCHAR(255) NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
      );
      CREATE TABLE IF NOT EXISTS products (
        product_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price NUMERIC(10, 2) NOT NULL
      );
    `);
    await testClient.end();

    console.log('Database and tables created. Generating types...');

    // 2. Generate TypeScript interfaces from the schema
    // Using npx for local schemats binary or if not globally installed
    const command = `npx schemats generate -c "${dbUrl}" -s public -o "${outputFile}"`;
    execSync(command, { stdio: 'inherit' });

    console.log(`
Generated TypeScript types to ${outputFile}:
`);

    // 3. Demonstrate usage of generated types
    // (In a real app, you'd import generated types from outputFile)
    // For this example, we'll simulate the types:
    interface Users {
      id: number;
      username: string;
      email: string;
      created_at: Date;
    }
    interface Products {
      product_id: string; // UUIDs often map to strings in TS
      name: string;
      price: string; // NUMERIC can be string or number, safer as string without specific parser
    }

    const exampleUser: Users = { id: 1, username: 'testuser', email: 'test@example.com', created_at: new Date() };
    const exampleProduct: Products = { product_id: 'a1b2c3d4-e5f6-7890-1234-567890abcdef', name: 'Example Item', price: '99.99' };

    console.log('Example user with generated type:', exampleUser);
    console.log('Example product with generated type:', exampleProduct);

  } catch (error) {
    console.error('Error during setup or generation:', error);
    process.exit(1);
  }
}

setupAndGenerate();