Skip to main content
Smart Query is ChartDB’s most powerful import method, allowing you to extract complete database schema information directly from your database’s metadata tables. This method provides the most accurate and comprehensive import results.

​
Overview

The Smart Query method imports database schema by querying the database’s internal metadata tables (such as information_schema in PostgreSQL and MySQL). This approach captures:
  • All tables and views
  • Complete field definitions with data types
  • Primary and foreign keys
  • Indexes (including types like GIN, BTREE, HASH)
  • Check constraints
  • Default values
  • Comments and descriptions
  • Custom types (enums, composite types)
  • Array fields (PostgreSQL)

​
Database Metadata Structure

The imported JSON follows this structure: 
interface DatabaseMetadata {
    database_name?: string;
    tables: TableInfo[];
    views?: ViewInfo[];
    fk_info: ForeignKeyInfo[];
    pk_info?: PrimaryKeyInfo[];
    indexes?: IndexInfo[];
    custom_types?: CustomTypeInfo[];
    check_constraints?: CheckConstraintInfo[];
}

​
Metadata Import Scripts

ChartDB provides pre-built SQL scripts for extracting metadata from various databases:
Location: src/lib/data/import-metadata/scripts/postgresql-script.tsThe PostgreSQL script extracts:
  • Tables from information_schema.tables
  • Columns from information_schema.columns
  • Constraints from pg_constraint
  • Indexes from pg_indexes
  • Custom types from pg_type
  • Array types and dimensions
  • Check constraints
  • Comments from pg_description

​
Import Process

The metadata import process follows these steps:
1

Parse Metadata JSON

ChartDB validates and parses the JSON structure containing database metadata.
2

Create Tables

Tables are created from metadata with all field information:
const tables = createTablesFromMetadata({
    databaseMetadata,
    databaseType,
});
3

Create Relationships

Foreign key relationships are established:
const relationships = createRelationshipsFromMetadata({
    foreignKeys,
    tables,
});
4

Create Dependencies

View dependencies are analyzed:
const dependencies = await createDependenciesFromMetadata({
    views,
    tables,
    databaseType,
});
5

Process Custom Types

Enums and custom types are imported:
const dbCustomTypes = customTypes
    ? createCustomTypesFromMetadata({ customTypes })
    : [];
6

Position Tables

Tables are automatically positioned on the canvas:
const adjustedTables = adjustTablePositions({
    tables,
    relationships,
    mode: 'perSchema',
});

​
Table Information

​
Column Metadata

Each column includes comprehensive information: 
interface ColumnInfo {
    table_name: string;
    column_name: string;
    data_type: string;
    is_nullable: 'YES' | 'NO';
    column_default?: string;
    character_maximum_length?: number;
    numeric_precision?: number;
    numeric_scale?: number;
    is_identity?: boolean;
    collation_name?: string;
}

​
Example Column Import

{
  "table_name": "users",
  "column_name": "email",
  "data_type": "character varying",
  "is_nullable": "NO",
  "character_maximum_length": 255,
  "column_default": null
}

​
Foreign Key Information

Foreign keys are imported with full relationship details: 
interface ForeignKeyInfo {
    constraint_name: string;
    table_name: string;
    column_name: string;
    foreign_table_name: string;
    foreign_column_name: string;
    table_schema?: string;
    foreign_table_schema?: string;
}

​
Example Foreign Key

{
  "constraint_name": "fk_posts_user_id",
  "table_name": "posts",
  "column_name": "user_id",
  "foreign_table_name": "users",
  "foreign_column_name": "id",
  "table_schema": "public",
  "foreign_table_schema": "public"
}

​
Index Information

Indexes are imported with type and uniqueness information: 
interface IndexInfo {
    table_name: string;
    index_name: string;
    column_name: string;
    is_unique: boolean;
    index_type?: 'btree' | 'hash' | 'gin' | 'gist' | 'spgist' | 'brin';
}

​
Supported Index Types

BTREE

Default B-tree index for most queries

HASH

Hash index for equality comparisons

GIN

Generalized Inverted Index for arrays and full-text

GIST

Generalized Search Tree for spatial data

SP-GIST

Space-partitioned GIST for non-balanced structures

BRIN

Block Range Index for very large tables

​
Custom Types

​
Enum Types

PostgreSQL enums are fully supported: 
interface CustomTypeInfo {
    schema_name?: string;
    type_name: string;
    kind: 'enum' | 'composite';
    values?: string[];
}
Example enum import: 
{
  "type_name": "order_status",
  "kind": "enum",
  "values": ["pending", "processing", "shipped", "delivered"]
}

​
Check Constraints

Check constraints are imported with their expressions: 
interface CheckConstraintInfo {
    table_name: string;
    constraint_name: string;
    check_clause: string;
}
Example: 
{
  "table_name": "products",
  "constraint_name": "price_positive",
  "check_clause": "price > 0"
}

​
Complete Import Example

{
  "database_name": "ecommerce",
  "tables": [
    {
      "table_schema": "public",
      "table_name": "users",
      "table_type": "BASE TABLE"
    },
    {
      "table_schema": "public",
      "table_name": "orders",
      "table_type": "BASE TABLE"
    }
  ],
  "fk_info": [
    {
      "constraint_name": "fk_orders_user_id",
      "table_name": "orders",
      "column_name": "user_id",
      "foreign_table_name": "users",
      "foreign_column_name": "id",
      "table_schema": "public",
      "foreign_table_schema": "public"
    }
  ],
  "indexes": [
    {
      "table_name": "users",
      "index_name": "users_email_idx",
      "column_name": "email",
      "is_unique": true,
      "index_type": "btree"
    }
  ],
  "custom_types": [
    {
      "type_name": "order_status",
      "kind": "enum",
      "values": ["pending", "confirmed", "shipped", "delivered"]
    }
  ]
}

​
Schema Organization

Tables are automatically organized by schema: 
const adjustedTables = adjustTablePositions({
    tables,
    relationships,
    mode: 'perSchema', // Groups tables by schema
});
This creates a clean layout where tables from the same schema are grouped together.

​
View Support

Database views are imported as special table objects: 
interface ViewInfo {
    view_schema?: string;
    view_name: string;
    view_definition?: string;
}
Views are marked with isView: true and positioned after regular tables.

​
Advanced Features

​
Array Fields (PostgreSQL)

PostgreSQL array types are fully supported: 
{
  "column_name": "tags",
  "data_type": "ARRAY",
  "udt_name": "text"
}
This creates a field with isArray: true.

​
Comments and Descriptions

Database comments are imported and displayed: 
{
  "table_name": "users",
  "comment": "Application user accounts"
}

​
Identity Columns

Auto-increment and identity columns are recognized: 
{
  "column_name": "id",
  "is_identity": true,
  "identity_generation": "ALWAYS"
}

​
Performance Optimization

The metadata import is optimized for large schemas:
Tables are processed in a single pass with O(n) complexity.
Foreign keys are mapped using hash tables for O(1) lookups.
View dependencies are calculated asynchronously to avoid blocking.
Tables are sorted alphabetically with views at the end for consistent layout.

​
Troubleshooting

Ensure your metadata JSON is valid and contains at least a tables array. Missing foreign key information will result in diagrams without relationships.
For the most complete import, include all metadata fields: tables, foreign keys, indexes, custom types, and check constraints.

​
Next Steps

DDL Import

Import from SQL DDL scripts

Diagram Editor

Edit your imported diagram