Import SQLite

ChartDB never stores or accesses your data - the import query only retrieves schema metadata.

Quick Start

Create New Diagram

Click File > New in the ChartDB editor
Select SQlite from the database options

Run Import Query

Execute the provided query using either:

Your database client (e.g., DBeaver, CLI command)
Direct SQlite connection

Import Results

Copy the JSON output from the query
Paste into ChartDB’s import field
Click Import to generate your diagram

Import Methods

SQLite

-- Regular SQlite Database Import Query
WITH fk_info AS (
SELECT
  json_group_array(
      json_object(
          'schema', '',  -- SQLite does not have schemas
          'table', m.name,
          'column', fk."from",
          'foreign_key_name',
              'fk_' || m.name || '_' || fk."from" || '_' || fk."table" || '_' || fk."to",  -- Generated foreign key name
          'reference_schema', '', -- SQLite does not have schemas
          'reference_table', fk."table",
          'reference_column', fk."to",
          'fk_def',
              'FOREIGN KEY (' || fk."from" || ') REFERENCES ' || fk."table" || '(' || fk."to" || ')' ||
              ' ON UPDATE ' || fk.on_update || ' ON DELETE ' || fk.on_delete
      )
  ) AS fk_metadata
FROM
  sqlite_master m
JOIN
  pragma_foreign_key_list(m.name) fk
ON
  m.type = 'table'
), pk_info AS (
SELECT
  json_group_array(
      json_object(
          'schema', '',  -- SQLite does not have schemas
          'table', pk.table_name,
          'field_count', pk.field_count,
          'column', pk.pk_column,
          'pk_def', 'PRIMARY KEY (' || pk.pk_column || ')'
      )
  ) AS pk_metadata
FROM
(
  SELECT
      m.name AS table_name,
      COUNT(p.name) AS field_count,  -- Count of primary key columns
      GROUP_CONCAT(p.name) AS pk_column  -- Concatenated list of primary key columns
  FROM
      sqlite_master m
  JOIN
      pragma_table_info(m.name) p
  ON
      m.type = 'table' AND p.pk > 0
  GROUP BY
      m.name
) pk
), indexes_metadata AS (
SELECT
  json_group_array(
      json_object(
          'schema', '',  -- SQLite does not have schemas
          'table', m.name,
          'name', idx.name,
          'column', ic.name,
          'index_type', 'B-TREE',  -- SQLite uses B-Trees for indexing
          'cardinality', '',  -- SQLite does not provide cardinality
          'size', '',  -- SQLite does not provide index size
          'unique', (CASE WHEN idx."unique" = 1 THEN 'true' ELSE 'false' END),
          'direction', '',  -- SQLite does not provide direction info
          'column_position', ic.seqno + 1  -- Adding 1 to convert from zero-based to one-based index
      )
  ) AS indexes_metadata
FROM
  sqlite_master m
JOIN
  pragma_index_list(m.name) idx
ON
  m.type = 'table'
JOIN
  pragma_index_info(idx.name) ic
), cols AS (
SELECT
  json_group_array(
      json_object(
          'schema', '',  -- SQLite does not have schemas
          'table', m.name,
          'name', p.name,
          'type',
              CASE
                  WHEN INSTR(LOWER(p.type), '(') > 0 THEN
                      SUBSTR(LOWER(p.type), 1, INSTR(LOWER(p.type), '(') - 1)
                  ELSE LOWER(p.type)
              END,
          'ordinal_position', p.cid,
          'nullable', (CASE WHEN p."notnull" = 0 THEN 'true' ELSE 'false' END),
          'collation', '',
          'character_maximum_length',
              CASE
                  WHEN LOWER(p.type) LIKE 'char%' OR LOWER(p.type) LIKE 'varchar%' THEN
                      CASE
                          WHEN INSTR(p.type, '(') > 0 THEN
                              REPLACE(SUBSTR(p.type, INSTR(p.type, '(') + 1, LENGTH(p.type) - INSTR(p.type, '(') - 1), ')', '')
                          ELSE 'null'
                      END
                  ELSE 'null'
              END,
          'precision',
          CASE
              WHEN LOWER(p.type) LIKE 'decimal%' OR LOWER(p.type) LIKE 'numeric%' THEN
                  CASE
                      WHEN instr(p.type, '(') > 0 THEN
                          json_object(
                              'precision', substr(p.type, instr(p.type, '(') + 1, instr(p.type, ',') - instr(p.type, '(') - 1),
                              'scale', substr(p.type, instr(p.type, ',') + 1, instr(p.type, ')') - instr(p.type, ',') - 1)
                          )
                      ELSE 'null'
                  END
              ELSE 'null'
          END,
          'default', COALESCE(REPLACE(p.dflt_value, '"', '\"'), '')
      )
  ) AS cols_metadata
FROM
  sqlite_master m
JOIN
  pragma_table_info(m.name) p
ON
  m.type in ('table', 'view')
), tbls AS (
SELECT
  json_group_array(
      json_object(
          'schema', '',  -- SQLite does not have schemas
          'table', m.name,
          'rows', -1,
          'type', 'table',
          'engine', '',  -- SQLite does not use storage engines
          'collation', ''  -- Collation information is not available
      )
  ) AS tbls_metadata
FROM
  sqlite_master m
WHERE
  m.type in ('table', 'view')
), views AS (
SELECT
  json_group_array(
      json_object(
          'schema', '',
          'view_name', m.name
      )
  ) AS views_metadata
FROM
  sqlite_master m
WHERE
  m.type = 'view'
)
SELECT
replace(replace(replace(
  json_object(
      'fk_info', (SELECT fk_metadata FROM fk_info),
      'pk_info', (SELECT pk_metadata FROM pk_info),
      'columns', (SELECT cols_metadata FROM cols),
      'indexes', (SELECT indexes_metadata FROM indexes_metadata),
      'tables', (SELECT tbls_metadata FROM tbls),
      'views', (SELECT views_metadata FROM views),
      'database_name', 'sqlite',
      'version', sqlite_version()
  ),
  '\"', '"'),'"[', '['), ']"', ']'
) AS metadata_json_to_import;

Troubleshooting

Common Issues Guide

Find solutions for frequently encountered import problems and their resolutions

Get Started

Import

Diagram

Export

Troubleshoot

Quick Start

Import Methods

Troubleshooting

Common Issues Guide

Get Started

Import

Diagram

Export

Troubleshoot

​Quick Start

​Import Methods

​Troubleshooting

Common Issues Guide

Quick Start

Import Methods

Troubleshooting