SQL Export

Overview

The SQL Export feature generates SQL DDL (Data Definition Language) statements from ChartDB diagrams. It supports multiple database dialects with dialect-specific syntax and features.

Export Functions

exportBaseSQL

Main export function that generates SQL DDL:

const exportBaseSQL = ({
    diagram,
    targetDatabaseType,
    isDBMLFlow = false,
    onlyRelationships = false,
    skipFKGeneration = false,
}: {
    diagram: Diagram;
    targetDatabaseType: DatabaseType;
    isDBMLFlow?: boolean;
    onlyRelationships?: boolean;
    skipFKGeneration?: boolean;
}): string => {
    // Generate SQL script
};

diagram

Diagram

required

The diagram to export

targetDatabaseType

DatabaseType

required

Target database type for SQL generation

isDBMLFlow

boolean

default:false

Whether this is for DBML export (affects syntax)

onlyRelationships

boolean

default:false

Only export ALTER TABLE statements for foreign keys

skipFKGeneration

boolean

default:false

Skip foreign key generation (used for DBML export)

exportSQL

Advanced export with AI-based dialect conversion:

const exportSQL = async (
    diagram: Diagram,
    databaseType: DatabaseType,
    options?: {
        stream: boolean;
        onResultStream: (text: string) => void;
        signal?: AbortSignal;
    }
): Promise<string> => {
    // Generate base SQL
    const sqlScript = exportBaseSQL({
        diagram,
        targetDatabaseType: databaseType,
    });
    
    // If same database type, return as-is
    if (databaseType === diagram.databaseType) {
        return sqlScript;
    }
    
    // Otherwise, use AI to convert dialect
    // Uses OpenAI API or custom endpoint
};

Generated SQL Structure

1. Schema Creation

CREATE SCHEMA IF NOT EXISTS "public";
CREATE SCHEMA IF NOT EXISTS "auth";

2. Custom Types (PostgreSQL)

-- Enum types
CREATE TYPE public.user_status AS ENUM ('active', 'inactive', 'suspended');

-- Composite types
CREATE TYPE public.address AS (
    street varchar,
    city varchar,
    zipcode varchar
);

3. Sequences (PostgreSQL)

CREATE SEQUENCE IF NOT EXISTS public.users_id_seq;

4. Table Definitions

CREATE TABLE "public"."users" (
    "id" uuid NOT NULL DEFAULT gen_random_uuid(),
    "email" varchar(255) NOT NULL UNIQUE,
    "status" public.user_status NOT NULL DEFAULT 'active',
    "created_at" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "tags" text[],
    PRIMARY KEY ("id"),
    CHECK (email LIKE '%@%')
);

5. Table Comments

-- PostgreSQL
COMMENT ON TABLE "public"."users" IS 'User accounts and authentication';
COMMENT ON COLUMN "public"."users"."email" IS 'Unique email address';

-- MySQL (inline)
CREATE TABLE users (
    id INT PRIMARY KEY COMMENT 'User identifier',
    email VARCHAR(255) COMMENT 'Email address'
) COMMENT='User accounts';

6. Indexes

-- Standard index
CREATE INDEX "idx_users_email" ON "public"."users" ("email");

-- Unique index
CREATE UNIQUE INDEX "idx_users_username" ON "public"."users" ("username");

-- GIN index (PostgreSQL)
CREATE INDEX "idx_users_tags" ON "public"."users" USING GIN ("tags");

-- Composite index
CREATE INDEX "idx_orders_user_date" ON "orders" ("user_id", "created_at");

7. Foreign Keys

ALTER TABLE "public"."orders" 
ADD CONSTRAINT "fk_orders_user_id" 
FOREIGN KEY ("user_id") 
REFERENCES "public"."users" ("id");

ALTER TABLE "public"."order_items" 
ADD CONSTRAINT "fk_order_items_order_id" 
FOREIGN KEY ("order_id") 
REFERENCES "public"."orders" ("id") 
ON DELETE CASCADE;

Database-Specific Features

PostgreSQL

-- Serial types
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    -- or
    id INTEGER GENERATED BY DEFAULT AS IDENTITY
);

-- Array types
CREATE TABLE posts (
    tags text[],
    scores integer[]
);

-- JSONB
CREATE TABLE products (
    metadata jsonb
);

-- Check constraints
CREATE TABLE products (
    price decimal(10, 2) CHECK (price >= 0)
);

MySQL / MariaDB

-- Auto-increment
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- Engine specification
CREATE TABLE users (
    id INT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inline comments
CREATE TABLE users (
    id INT PRIMARY KEY COMMENT 'User ID',
    email VARCHAR(255) COMMENT 'Email address'
) COMMENT='User accounts';

SQL Server

-- Identity columns
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    guid UNIQUEIDENTIFIER DEFAULT NEWID()
);

-- NVARCHAR for Unicode
CREATE TABLE users (
    name NVARCHAR(255)
);

-- Extended properties for comments
EXEC sp_addextendedproperty 
    @name = N'MS_Description',
    @value = N'User accounts',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'users';

SQLite

-- Autoincrement
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT
);

-- Foreign keys (must be in CREATE TABLE)
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Helper Functions

getQuotedTableName

Quotes table names with schema:

const getQuotedTableName = (
    table: DBTable,
    isDBMLFlow: boolean = false
): string => {
    const quoteIfNeeded = (name: string) => {
        const needsQuoting = /[^a-zA-Z0-9_]/.test(name) || isDBMLFlow;
        return needsQuoting ? `"${name}"` : name;
    };
    
    if (table.schema) {
        return `${quoteIfNeeded(table.schema)}.${quoteIfNeeded(table.name)}`;
    }
    return quoteIfNeeded(table.name);
};

formatDefaultValue

Formats default values with proper quoting:

const formatDefaultValue = (value: string): string => {
    const trimmed = value.trim();
    
    // SQL keywords don't need quotes
    const keywords = [
        'TRUE', 'FALSE', 'NULL',
        'CURRENT_TIMESTAMP', 'CURRENT_DATE', 'CURRENT_TIME',
        'NOW', 'GETDATE', 'NEWID', 'UUID'
    ];
    if (keywords.includes(trimmed.toUpperCase())) {
        return trimmed;
    }
    
    // Function calls don't need quotes
    if (trimmed.includes('(') && trimmed.includes(')')) {
        return trimmed;
    }
    
    // Numbers don't need quotes
    if (/^-?\d+(\.\d+)?$/.test(trimmed)) {
        return trimmed;
    }
    
    // Already quoted strings
    if ((trimmed.startsWith("'") && trimmed.endsWith("'")) ||
        (trimmed.startsWith('"') && trimmed.endsWith('"'))) {
        return trimmed;
    }
    
    // Quote everything else
    return `'${trimmed.replace(/'/g, "''")}'`;
};

Cross-Dialect Export

ChartDB supports deterministic cross-dialect conversion for common scenarios:

PostgreSQL to MySQL

import { exportPostgreSQLToMySQL } from './cross-dialect';

const mysqlSQL = exportPostgreSQLToMySQL({ 
    diagram, 
    onlyRelationships: false 
});

Conversions:

SERIAL → INT AUTO_INCREMENT
TEXT → TEXT
BOOLEAN → TINYINT(1)
UUID → VARCHAR(36) or CHAR(36)
TIMESTAMP → DATETIME
Array types → JSON (with warning)
JSONB → JSON

PostgreSQL to SQL Server

import { exportPostgreSQLToMSSQL } from './cross-dialect';

const sqlServerSQL = exportPostgreSQLToMSSQL({ 
    diagram, 
    onlyRelationships: false 
});

Conversions:

SERIAL → INT IDENTITY(1,1)
TEXT → NVARCHAR(MAX)
BOOLEAN → BIT
UUID → UNIQUEIDENTIFIER
TIMESTAMP → DATETIME2
Array types → Not supported (warning)
JSONB → NVARCHAR(MAX) (with warning)

Export Options

Only Relationships

Export only ALTER TABLE statements for foreign keys:

const fkSQL = exportBaseSQL({
    diagram,
    targetDatabaseType: DatabaseType.POSTGRESQL,
    onlyRelationships: true
});

// Output:
// ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id ...
// ALTER TABLE order_items ADD CONSTRAINT fk_order_items_order_id ...

Skip Foreign Keys

Export tables without foreign key constraints:

const tablesOnly = exportBaseSQL({
    diagram,
    targetDatabaseType: DatabaseType.POSTGRESQL,
    skipFKGeneration: true
});

// Useful for:
// - DBML export (uses Ref syntax instead)
// - Circular dependencies
// - Performance during initial import

Complete Example

import { exportBaseSQL, exportSQL } from './sql-export';
import { DatabaseType } from './database-type';

// Basic export (same database type)
const postgresSQL = exportBaseSQL({
    diagram,
    targetDatabaseType: DatabaseType.POSTGRESQL
});

console.log(postgresSQL);
/*
CREATE SCHEMA IF NOT EXISTS "public";

CREATE TABLE "public"."users" (
    "id" uuid NOT NULL DEFAULT gen_random_uuid(),
    "email" varchar(255) NOT NULL UNIQUE,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."orders" (
    "id" bigserial PRIMARY KEY,
    "user_id" uuid NOT NULL
);

ALTER TABLE "public"."orders" 
ADD CONSTRAINT "fk_orders_user_id" 
FOREIGN KEY ("user_id") 
REFERENCES "public"."users" ("id");
*/

// Cross-dialect export with AI (requires OpenAI API key)
const mysqlSQL = await exportSQL(
    diagram,
    DatabaseType.MYSQL,
    {
        stream: false,
        onResultStream: (text) => console.log(text)
    }
);

Core Types

Import & Export

Overview

Export Functions

exportBaseSQL

exportSQL

Generated SQL Structure

1. Schema Creation

2. Custom Types (PostgreSQL)

3. Sequences (PostgreSQL)

4. Table Definitions

5. Table Comments

6. Indexes

7. Foreign Keys

Database-Specific Features

PostgreSQL

MySQL / MariaDB

SQL Server

SQLite

Helper Functions

getQuotedTableName

formatDefaultValue

Cross-Dialect Export

PostgreSQL to MySQL

PostgreSQL to SQL Server

Export Options

Only Relationships

Skip Foreign Keys

Complete Example

Core Types

Import & Export

​Overview

​Export Functions

​exportBaseSQL

​exportSQL

​Generated SQL Structure

​1. Schema Creation

​2. Custom Types (PostgreSQL)

​3. Sequences (PostgreSQL)

​4. Table Definitions

​5. Table Comments

​6. Indexes

​7. Foreign Keys

​Database-Specific Features

​PostgreSQL

​MySQL / MariaDB

​SQL Server

​SQLite

​Helper Functions

​getQuotedTableName

​formatDefaultValue

​Cross-Dialect Export

​PostgreSQL to MySQL

​PostgreSQL to SQL Server

​Export Options

​Only Relationships

​Skip Foreign Keys

​Complete Example

​Related

Overview

Export Functions

exportBaseSQL

exportSQL

Generated SQL Structure

1. Schema Creation

2. Custom Types (PostgreSQL)

3. Sequences (PostgreSQL)

4. Table Definitions

5. Table Comments

6. Indexes

7. Foreign Keys

Database-Specific Features

PostgreSQL

MySQL / MariaDB

SQL Server

SQLite

Helper Functions

getQuotedTableName

formatDefaultValue

Cross-Dialect Export

PostgreSQL to MySQL

PostgreSQL to SQL Server

Export Options

Only Relationships

Skip Foreign Keys

Complete Example

Related