npm package: @seedts/introspect
Description: Database schema introspection and entity generation for SeedTS
Author: SeedTS Contributors
Latest version: 0.1.1

# @seedts/introspect

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

## Installation

```bash
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:

```bash
# 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

```typescript
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

```bash
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:**
```bash
seedts introspect \
  --type postgresql \
  --database myapp \
  --user postgres \
  --password secret
```

**Introspect specific tables:**
```bash
seedts introspect \
  --type mysql \
  --database myapp \
  --user root \
  --password secret \
  --tables users,posts,comments
```

**Exclude system tables:**
```bash
seedts introspect \
  --type postgresql \
  --database myapp \
  --user postgres \
  --password secret \
  --exclude migrations,sessions
```

**Custom output directory:**
```bash
seedts introspect \
  --type postgresql \
  --database myapp \
  --user postgres \
  --password secret \
  --output ./src/my-entities \
  --overwrite
```

**With SSL:**
```bash
seedts introspect \
  --type postgresql \
  --host db.example.com \
  --database myapp \
  --user postgres \
  --password secret \
  --ssl
```

## Programmatic API

### createIntrospector()

Create an introspector instance.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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:

```typescript
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

```sql
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

```tsx
/** @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:

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

### Nullable Fields

Nullable columns are marked as optional in TypeScript interfaces:

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

### Foreign Key Detection

Foreign keys are detected and documented in generated files:

```tsx
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:

```tsx
/** @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:**

```bash
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:**

```tsx
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

```typescript
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) - Core SeedTS functionality
- [@seedts/cli](../cli) - Command-line interface
- [@seedts/faker](../faker) - Faker.js integration
- [SeedTS Documentation](../../README.md) - Main documentation