SQL Import

Overview

The SQL Import feature parses SQL DDL (Data Definition Language) statements and converts them into ChartDB diagrams. It supports multiple SQL dialects and extracts tables, columns, indexes, constraints, and relationships.

SQL Parser Result Interface

interface SQLParserResult {
    tables: SQLTable[];
    relationships: SQLForeignKey[];
    types?: SQLCustomType[];
    enums?: SQLEnumType[];
    warnings?: string[];
}

Core Interfaces

SQLTable

Represents a parsed table from SQL:

interface SQLTable {
    id: string;
    name: string;
    schema?: string;
    columns: SQLColumn[];
    indexes: SQLIndex[];
    checkConstraints?: SQLCheckConstraint[];
    comment?: string;
    order: number;
    isView?: boolean;
}

string

required

Unique identifier generated during parsing

name

string

required

Table name extracted from CREATE TABLE statement

schema

string

Schema name if specified (e.g., public.users)

columns

SQLColumn[]

required

Array of column definitions

indexes

SQLIndex[]

required

Array of indexes from CREATE INDEX statements

checkConstraints

SQLCheckConstraint[]

Array of CHECK constraints

comment

string

Table comment from COMMENT ON TABLE statement

order

number

required

Parse order (0-based index)

isView

boolean

Whether this is a VIEW instead of a TABLE

SQLColumn

Represents a parsed column:

interface SQLColumn {
    name: string;
    type: string;
    nullable: boolean;
    primaryKey: boolean;
    unique: boolean;
    typeArgs?: {
        length?: number;
        precision?: number;
        scale?: number;
    } | number[] | string;
    comment?: string;
    default?: string;
    increment?: boolean;
}

name

string

required

Column name

type

string

required

Data type (e.g., 'varchar', 'integer', 'timestamp')

nullable

boolean

required

Whether NULL values are allowed

primaryKey

boolean

required

Whether this is a primary key column

unique

boolean

required

Whether this has a UNIQUE constraint

typeArgs

object | number[] | string

Type arguments for sized/precision types:Object format:

length: For VARCHAR(n), CHAR(n)
precision: For DECIMAL(p,s), NUMERIC(p,s)
scale: For DECIMAL(p,s), NUMERIC(p,s)

Array format (SQL Server):

[length] or [precision, scale]

String format:

'max' for VARCHAR(MAX)

comment

string

Column comment

default

string

Default value expression

increment

boolean

Auto-increment flag

SQLForeignKey

Represents a foreign key relationship:

interface SQLForeignKey {
    name: string;
    sourceTable: string;
    sourceSchema?: string;
    sourceColumn: string;
    targetTable: string;
    targetSchema?: string;
    targetColumn: string;
    sourceTableId: string;
    targetTableId: string;
    updateAction?: string;
    deleteAction?: string;
    sourceCardinality?: Cardinality;
    targetCardinality?: Cardinality;
}

name

string

required

Constraint name

sourceTable

string

required

Table with the foreign key

sourceColumn

string

required

Foreign key column

targetTable

string

required

Referenced table

targetColumn

string

required

Referenced column (typically primary key)

updateAction

string

ON UPDATE action (CASCADE, SET NULL, RESTRICT, etc.)

deleteAction

string

ON DELETE action (CASCADE, SET NULL, RESTRICT, etc.)

SQLIndex

Represents an index:

interface SQLIndex {
    name: string;
    columns: string[];
    unique: boolean;
    type?: string;
}

name

string

required

Index name

columns

string[]

required

Array of column names in the index

unique

boolean

required

Whether this is a UNIQUE index

type

string

Index type (e.g., 'btree', 'gin', 'hash')

Conversion Function

convertToChartDBDiagram

Converts SQL parser result to a ChartDB diagram:

function convertToChartDBDiagram(
    parserResult: SQLParserResult,
    sourceDatabaseType: DatabaseType,
    targetDatabaseType: DatabaseType
): Diagram {
    // Convert SQL tables to DBTable objects
    const tables: DBTable[] = parserResult.tables.map((table, index) => {
        const fields: DBField[] = table.columns.map((column) => {
            // Map SQL type to DataType
            const mappedType = mapSQLTypeToGenericType(
                column.type,
                sourceDatabaseType
            );
            
            // Handle array types
            const isArrayType = column.type.endsWith('[]');
            const baseType = isArrayType 
                ? column.type.slice(0, -2) 
                : column.type;
            
            return {
                id: generateId(),
                name: column.name,
                type: mappedType,
                nullable: column.nullable,
                primaryKey: column.primaryKey,
                unique: column.unique,
                increment: column.increment,
                isArray: isArrayType || undefined,
                default: column.default || '',
                createdAt: Date.now(),
                // Add type arguments
                characterMaximumLength: column.typeArgs?.length,
                precision: column.typeArgs?.precision,
                scale: column.typeArgs?.scale,
            };
        });
        
        // Convert indexes
        const indexes = table.indexes.map((sqlIndex) => ({
            id: generateId(),
            name: sqlIndex.name,
            fieldIds: sqlIndex.columns.map(/* map to field IDs */),
            unique: sqlIndex.unique,
            type: sqlIndex.type,
            createdAt: Date.now(),
        }));
        
        return {
            id: generateId(),
            name: table.name,
            schema: table.schema || '',
            fields,
            indexes,
            x: (index % 4) * 300,
            y: Math.floor(index / 4) * 300,
            color: defaultTableColor,
            isView: table.isView ?? false,
            createdAt: Date.now(),
        };
    });
    
    // Convert relationships
    const relationships: DBRelationship[] = parserResult.relationships.map((rel) => ({
        id: generateId(),
        name: rel.name,
        sourceTableId: /* find table ID */,
        targetTableId: /* find table ID */,
        sourceFieldId: /* find field ID */,
        targetFieldId: /* find field ID */,
        sourceCardinality: rel.sourceCardinality || 'one',
        targetCardinality: rel.targetCardinality || 'many',
        createdAt: Date.now(),
    }));
    
    return {
        id: generateDiagramId(),
        name: `SQL Import (${sourceDatabaseType})`,
        databaseType: targetDatabaseType,
        tables,
        relationships,
        createdAt: new Date(),
        updatedAt: new Date(),
    };
}

Type Mapping

mapSQLTypeToGenericType

Maps SQL types to ChartDB DataType:

function mapSQLTypeToGenericType(
    sqlType: string,
    databaseType?: DatabaseType
): DataType {
    const normalizedType = sqlType.toLowerCase();
    
    // Get dialect-specific mappings
    const typeMap: Record<string, string> = {
        // PostgreSQL
        'int4': 'integer',
        'int8': 'bigint',
        'int2': 'smallint',
        'bool': 'boolean',
        'character varying': 'varchar',
        
        // MySQL
        'tinyint': 'tinyint',
        'mediumint': 'mediumint',
        
        // SQL Server
        'nvarchar': 'nvarchar',
        'uniqueidentifier': 'uniqueidentifier',
        
        // Common
        'int': 'integer',
        'varchar': 'varchar',
        'text': 'text',
        'timestamp': 'timestamp',
        'decimal': 'decimal',
    };
    
    const mappedId = typeMap[normalizedType] || normalizedType;
    return { id: mappedId, name: mappedId };
}

Helper Functions

quoteIdentifier

Quotes identifiers based on database type:

function quoteIdentifier(str: string, dbType: DatabaseType): string {
    switch (dbType) {
        case DatabaseType.MYSQL:
        case DatabaseType.MARIADB:
            return `\`${str}\``;
        case DatabaseType.POSTGRESQL:
        case DatabaseType.SQLITE:
            return `"${str}"`;
        case DatabaseType.SQL_SERVER:
            return `[${str}]`;
        default:
            return str;
    }
}

buildSQLFromAST

Builds SQL expressions from Abstract Syntax Tree nodes:

function buildSQLFromAST(
    ast: SQLASTNode | null | undefined,
    dbType: DatabaseType = DatabaseType.GENERIC
): string {
    if (!ast) return '';
    
    // Handle default value wrapper
    if (ast.type === 'default' && 'value' in ast) {
        return buildSQLFromAST(ast.value, dbType);
    }
    
    // Handle cast expressions (e.g., 'value'::type)
    if (ast.type === 'cast') {
        const expr = buildSQLFromAST(ast.expr, dbType);
        return `${expr}::${ast.target[0].dataType}`;
    }
    
    // Handle function calls
    if (ast.type === 'function') {
        const funcName = typeof ast.name === 'string' 
            ? ast.name 
            : ast.name.name[0].value;
        const args = ast.args?.value.map(buildSQLFromAST).join(', ');
        return `${funcName}(${args || ''})`;
    }
    
    // Handle literals
    if (ast.type === 'single_quote_string') {
        return `'${ast.value}'`;
    }
    
    if (ast.type === 'number') {
        return String(ast.value);
    }
    
    return '';
}

Example SQL Import

PostgreSQL Example

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    tags TEXT[],
    CHECK (email LIKE '%@%')
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID NOT NULL,
    total DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_tags ON users USING GIN(tags);

Parsed result:

{
    tables: [
        {
            id: 'table_1',
            name: 'users',
            columns: [
                {
                    name: 'id',
                    type: 'uuid',
                    nullable: false,
                    primaryKey: true,
                    unique: true,
                    default: 'gen_random_uuid()'
                },
                {
                    name: 'email',
                    type: 'varchar',
                    nullable: false,
                    primaryKey: false,
                    unique: true,
                    typeArgs: { length: 255 }
                },
                {
                    name: 'tags',
                    type: 'text[]',
                    nullable: true,
                    primaryKey: false,
                    unique: false
                }
            ],
            indexes: [
                {
                    name: 'idx_users_tags',
                    columns: ['tags'],
                    unique: false,
                    type: 'gin'
                }
            ],
            checkConstraints: [
                { expression: "email LIKE '%@%'" }
            ],
            order: 0
        },
        {
            id: 'table_2',
            name: 'orders',
            columns: [/* ... */],
            indexes: [/* ... */],
            order: 1
        }
    ],
    relationships: [
        {
            name: 'orders_user_id_fkey',
            sourceTable: 'users',
            sourceColumn: 'id',
            targetTable: 'orders',
            targetColumn: 'user_id',
            sourceTableId: 'table_1',
            targetTableId: 'table_2'
        }
    ]
}

Supported SQL Features

Feature	PostgreSQL	MySQL	SQL Server	SQLite
CREATE TABLE	✅	✅	✅	✅
PRIMARY KEY	✅	✅	✅	✅
FOREIGN KEY	✅	✅	✅	✅
UNIQUE	✅	✅	✅	✅
CHECK	✅	✅	✅	✅
DEFAULT	✅	✅	✅	✅
NOT NULL	✅	✅	✅	✅
AUTO_INCREMENT	✅	✅	✅	✅
CREATE INDEX	✅	✅	✅	✅
CREATE VIEW	✅	✅	✅	✅
CREATE TYPE (ENUM)	✅	❌	❌	❌
Array Types	✅	❌	❌	❌
COMMENT ON	✅	Inline	Extended Props	❌

Core Types

Import & Export

Overview

SQL Parser Result Interface

Core Interfaces

SQLTable

SQLColumn

SQLForeignKey

SQLIndex

Conversion Function

convertToChartDBDiagram

Type Mapping

mapSQLTypeToGenericType

Helper Functions

quoteIdentifier

buildSQLFromAST

Example SQL Import

PostgreSQL Example

Supported SQL Features

Core Types

Import & Export

​Overview

​SQL Parser Result Interface

​Core Interfaces

​SQLTable

​SQLColumn

​SQLForeignKey

​SQLIndex

​Conversion Function

​convertToChartDBDiagram

​Type Mapping

​mapSQLTypeToGenericType

​Helper Functions

​quoteIdentifier

​buildSQLFromAST

​Example SQL Import

​PostgreSQL Example

​Supported SQL Features

​Related

Overview

SQL Parser Result Interface

Core Interfaces

SQLTable

SQLColumn

SQLForeignKey

SQLIndex

Conversion Function

convertToChartDBDiagram

Type Mapping

mapSQLTypeToGenericType

Helper Functions

quoteIdentifier

buildSQLFromAST

Example SQL Import

PostgreSQL Example

Supported SQL Features

Related