Database
Import SQLite
Import SQLite database schema into ChartDB
ChartDB never stores or accesses your data - the import query only retrieves schema metadata.
Quick Start
1
Create New Diagram
- Click File > New in the ChartDB editor
- Select SQlite from the database options
2
Run Import Query
Execute the provided query using either:
- Your database client (e.g., DBeaver, CLI command)
- Direct SQlite connection
3
Import Results
- Copy the JSON output from the query
- Paste into ChartDB’s import field
- Click Import to generate your diagram
Import Methods
Copy
-- 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
Was this page helpful?
On this page
Assistant
Responses are generated using AI and may contain mistakes.