@seedts/introspect

Database schema introspection and entity generation for SeedTS - Automatically generate entity files from existing database schemas.

Installation

npm install @seedts/introspect
# or
pnpm add @seedts/introspect
# or
yarn add @seedts/introspect

Features

• 🔍 Database Introspection - Analyze database schemas to extract table and column information
• 📝 Entity Generation - Automatically generate SeedTS entity files from database schemas
• 🗄️ Multi-Database Support - PostgreSQL and MySQL support
• 🔗 Foreign Key Detection - Identify and document table relationships
• ⚙️ Customizable Output - Control which tables to introspect and where to generate files
• 🎯 Type Mapping - Automatic mapping from database types to TypeScript types
• 🚀 CLI Integration - Use via command line or programmatically

Quick Start

Using the CLI

The easiest way to use introspection is through the SeedTS CLI:

# PostgreSQL
seedts introspect \
  --type postgresql \
  --host localhost \
  --port 5432 \
  --database mydb \
  --user postgres \
  --password secret \
  --output ./src/entities

# MySQL
seedts introspect \
  --type mysql \
  --host localhost \
  --port 3306 \
  --database mydb \
  --user root \
  --password secret \
  --output ./src/entities

Programmatic Usage

import { createIntrospector } from '@seedts/introspect';

const introspector = createIntrospector({
  type: 'postgresql',
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'postgres',
  password: 'secret',
});

// Introspect and generate entities
const result = await introspector.introspectAndGenerate(
  {
    schema: 'public',
    tables: ['users', 'posts', 'comments'],
  },
  {
    outputDir: './src/entities',
    overwrite: true,
  }
);

console.log(`Generated ${result.generatedFiles.length} entity files`);

await introspector.disconnect();

CLI Usage

Command

seedts introspect [options]

Options

Connection Options:

• -t, --type <type> - Database type (postgresql, mysql)
• -h, --host <host> - Database host (default: localhost)
• -p, --port <port> - Database port (default: 5432 for PostgreSQL, 3306 for MySQL)
• -d, --database <database> - Database name (required)
• -u, --user <user> - Database user (required)
• -w, --password <password> - Database password
• --ssl - Enable SSL connection

Introspection Options:

• -s, --schema <schema> - Database schema (default: public)
• --tables <tables> - Comma-separated list of tables to introspect
• --exclude <tables> - Comma-separated list of tables to exclude
• --include-views - Include database views

Generation Options:

• -o, --output <dir> - Output directory for entities (default: ./src/entities)
• --overwrite - Overwrite existing entity files

Examples

Introspect all tables:

seedts introspect \
  --type postgresql \
  --database myapp \
  --user postgres \
  --password secret

Introspect specific tables:

seedts introspect \
  --type mysql \
  --database myapp \
  --user root \
  --password secret \
  --tables users,posts,comments

Exclude system tables:

seedts introspect \
  --type postgresql \
  --database myapp \
  --user postgres \
  --password secret \
  --exclude migrations,sessions

Custom output directory:

seedts introspect \
  --type postgresql \
  --database myapp \
  --user postgres \
  --password secret \
  --output ./src/my-entities \
  --overwrite

With SSL:

seedts introspect \
  --type postgresql \
  --host db.example.com \
  --database myapp \
  --user postgres \
  --password secret \
  --ssl

Programmatic API

createIntrospector()

Create an introspector instance.

import { createIntrospector } from '@seedts/introspect';

const introspector = createIntrospector({
  type: 'postgresql', // or 'mysql'
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'postgres',
  password: 'secret',
  ssl: false, // optional
});

introspect()

Introspect database schema without generating files.

const result = await introspector.introspect({
  schema: 'public',
  tables: ['users', 'posts'], // optional: specific tables
  excludeTables: ['migrations'], // optional: exclude tables
  includeViews: false, // optional: include views
});

console.log(`Found ${result.tables.length} tables`);

for (const table of result.tables) {
  console.log(`Table: ${table.name}`);
  console.log(`  Columns: ${table.columns.length}`);
  console.log(`  Primary Keys: ${table.primaryKeys.join(', ')}`);
}

generateEntities()

Generate entity files from introspection result.

const introspectionResult = await introspector.introspect();

const files = await introspector.generateEntities(introspectionResult, {
  outputDir: './src/entities',
  overwrite: true,
  includeFaker: false, // future feature
  includeRelations: false, // future feature
});

console.log(`Generated files: ${files.join(', ')}`);

introspectAndGenerate()

Introspect and generate in one step.

const result = await introspector.introspectAndGenerate(
  {
    schema: 'public',
  },
  {
    outputDir: './src/entities',
    overwrite: true,
  }
);

console.log(`Introspected ${result.introspection.tables.length} tables`);
console.log(`Generated ${result.generatedFiles.length} files`);

Helper Function

Use the helper function for one-time introspection:

import { introspectAndGenerate } from '@seedts/introspect';

const files = await introspectAndGenerate(
  {
    type: 'postgresql',
    host: 'localhost',
    port: 5432,
    database: 'mydb',
    user: 'postgres',
    password: 'secret',
  },
  {
    schema: 'public',
    tables: ['users', 'posts'],
  },
  {
    outputDir: './src/entities',
    overwrite: true,
  }
);

console.log(`Generated ${files.length} entity files`);

Generated Entity Format

The introspector generates fully-functional SeedTS entity files:

Input Database Table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(100) NOT NULL,
  age INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Generated Entity File

/** @jsxImportSource @seedts/jsx-runtime */

import { Entity, Attribute } from '@seedts/jsx-runtime';
import type { ExecutionContext } from '@seedts/types';

/**
 * Users entity
 */
export interface Users {
  id: number;
  email: string;
  username: string;
  age?: number;
  created_at?: Date;
}

export type UsersFactory = {
  email: (ctx: ExecutionContext) => string;
  username: (ctx: ExecutionContext) => string;
  age?: (ctx: ExecutionContext) => number;
  created_at?: (ctx: ExecutionContext) => Date;
};

interface UsersEntityProps {
  factory?: Partial<UsersFactory>;
}

/**
 * Users entity component
 */
export const UsersEntity = (props: UsersEntityProps = {}) => {
  const factory = props.factory || {};

  return (
    <Entity>
      <Attribute name="id" type="number" autoIncrement />
      <Attribute name="email" type="string" factory={factory.email} />
      <Attribute name="username" type="string" factory={factory.username} />
      <Attribute name="age" type="number" factory={factory.age} />
      <Attribute name="created_at" type="Date" factory={factory.created_at} />
    </Entity>
  );
};

Type Mapping

PostgreSQL → TypeScript

| PostgreSQL Type | TypeScript Type | |----------------|-----------------| | varchar, text, char, uuid | string | | integer, serial, bigint, smallint, numeric, decimal, real, double, float | number | | boolean, bool | boolean | | timestamp, date, time, timestamptz | Date | | json, jsonb | string |

MySQL → TypeScript

| MySQL Type | TypeScript Type | |-----------|-----------------| | varchar, text, char, enum, set, blob | string | | int, tinyint, smallint, bigint, decimal, float, double | number | | boolean, bool, tinyint(1) | boolean | | date, datetime, timestamp, time | Date | | json | string |

Features Details

Auto-Increment Detection

The introspector automatically detects auto-increment/serial columns:

<Attribute name="id" type="number" autoIncrement />

Nullable Fields

Nullable columns are marked as optional in TypeScript interfaces:

export interface Users {
  id: number;
  email: string;
  age?: number; // nullable field
}

Foreign Key Detection

Foreign keys are detected and documented in generated files:

export const PostsEntity = (props: PostsEntityProps = {}) => {
  const factory = props.factory || {};

  // Foreign Keys:
  // user_id -> users.id

  return (
    <Entity>
      <Attribute name="id" type="number" autoIncrement />
      <Attribute name="user_id" type="number" factory={factory.user_id} />
      <Attribute name="title" type="string" factory={factory.title} />
    </Entity>
  );
};

Column Comments

Database column comments are preserved in generated files:

/** @jsxImportSource @seedts/jsx-runtime */

export interface Users {
  /** User's unique email address */
  email: string;
  /** Display name for the user */
  username: string;
}

Workflow Example

1. Introspect your existing database:

seedts introspect \
  --type postgresql \
  --database production \
  --user readonly \
  --password secret \
  --output ./src/entities

2. Generated entity files are created in ./src/entities/:

src/entities/
  Users.tsx
  Posts.tsx
  Comments.tsx
  Categories.tsx

3. Use the generated entities in your seeds:

import { Seed, Action } from '@seedts/jsx-runtime';
import { UsersEntity } from './entities/Users';
import { PostsEntity } from './entities/Posts';
import { faker } from '@seedts/faker';

export const UsersSeed = (
  <Seed name="users" adapter={adapter}>
    <Action count={100}>
      <UsersEntity
        factory={{
          email: faker.email(),
          username: faker.username(),
          age: faker.int({ min: 18, max: 80 }),
        }}
      />
    </Action>
  </Seed>
);

Best Practices

1. Use Read-Only Database User - For production introspection, use a read-only user
2. Version Control Generated Files - Commit generated entities to track schema changes
3. Regenerate After Schema Changes - Re-run introspection after database migrations
4. Exclude System Tables - Use --exclude to skip migration and system tables
5. Review Generated Files - Check and customize generated factories as needed
6. Add Faker Integration - Enhance generated entities with Faker.js factories

TypeScript Types

import type {
  ConnectionConfig,
  IntrospectionOptions,
  IntrospectionResult,
  EntityGenerationOptions,
  TableInfo,
  ColumnInfo,
  ForeignKeyInfo,
  TypeScriptType,
} from '@seedts/introspect';

Database Support

Currently Supported

• ✅ PostgreSQL (9.6+)
• ✅ MySQL (5.7+) / MariaDB

Future Support

• 🔜 SQLite
• 🔜 Microsoft SQL Server
• 🔜 Oracle

Limitations

• View introspection is basic (columns only, no view definition)
• Complex constraints (CHECK, etc.) are not captured
• Enum types are mapped to string
• Generated factories need manual implementation
• Composite primary keys are detected but require manual factory setup

Contributing

Contributions welcome! Areas for improvement:

• Additional database support (SQLite, SQL Server, Oracle)
• Enhanced type mapping
• Factory template generation with Faker.js
• Relationship inference and generation
• Custom template support
• Schema diff and migration support

License

MIT

See Also

• @seedts/core - Core SeedTS functionality
• @seedts/cli - Command-line interface
• @seedts/faker - Faker.js integration
• SeedTS Documentation - Main documentation