Database Analysis – Analyze SQLite Databases

Indexly provides a powerful pipeline for inspecting SQLite databases, allowing you to extract meaningful insights and visualize table relationships efficiently. Whether you’re exploring your own datasets or working with sample DBs such as Chinook, analyze-db gives you the ability to summarize, profile, and export your data.

Key Features

• Analyze SQLite databases (single or multi-table)
• Summarize table structure, numeric/non-numeric stats, nulls, and top values
• Detect explicit foreign keys and infer relationships heuristically
• Build Mermaid ER diagrams and adjacency graphs
• Export results in JSON, Markdown, HTML, or diagram formats
• Persist analysis at multiple levels: none, summary, detailed, raw

Using indexly analyze-db

Basic Syntax

indexly analyze-db <db_path> [--show-summary] [--table <table_name>] [--all-tables]
                     [--sample-size N]
                     [--persist-level {minimal,full,none}]
                     [--no-persist]
                     [--export json,md,html]
                     [--max-preview ROWS]

Example: Analyze a Single Table and Export Markdown

indexly analyze-db Chinook.db --show-summary --table customers --export md --diagram mermaid
✔ Persisted summary to Chinook.db.analysis.json

Read the persisted JSON summary later:

indexly read-json Chinook.db.analysis.json --show-summary
indexly read-json Chinook.db.analysis.json --treeview --preview 5

read-json is a reader for persisted Indexly summary JSON. It displays stored summary blocks from *.analysis.json files and does not re-analyze the original database.

──────────────────── Dataset Summary Preview ──────────────────── DB Tables Overview

🔗 Detected Relations

📊 Sample Rows (see Sample Size Considerations)

Profile: customers

Exported Markdown → Chinook.db.analysis.md

Other Examples

# Analyze all tables, export JSON + Markdown
indexly analyze-db Chinook.db --all-tables --persist-level full --export json,md

# Generate relationships diagram for all tables in Markdown
indexly analyze-db Chinook.db --all-tables --export md --diagram mermaid

# Limit row sample to 20 for faster profiling of a specific table
indexly analyze-db Chinook.db --table tracks --sample-size 20 --show-summary

Analyze-DB Subcommand

Arguments

Sampling Controls

Output Controls

Sample Size Considerations

When performing table profiling with Indexly, sample size selection plays a crucial role in balancing accuracy and performance. For instance, smaller sample sizes speed up profiling and reduce memory usage, but they may underrepresent rare values or subtle numeric patterns. Conversely, larger samples improve statistical stability, capturing both frequent and uncommon categories more reliably.

Key considerations:

1. Central Limit Theorem (CLT) – For numeric columns, a minimum of 30–100 rows per column generally suffices to stabilize the sampling distribution of means, medians, and standard deviations.
2. Margin of Error – For proportion estimates, the number of rows required depends mainly on the desired confidence level and precision, not the total table size. Typical guidance:

Even for very large tables, accurate summary statistics can often be derived from a well-chosen subset.

1. Rare-value detection – If a category appears in 0.1% of rows, a sample of 10,000 ensures near certainty of capturing it. Smaller samples may miss these rare occurrences entirely.

Conclusion: Indexly applies adaptive sampling to balance speed and accuracy. Users can override defaults with --sample-size for a bounded profile or --all-data when full-table completeness is more important than speed and memory use.

When To Use analyze-db vs analyze-file

For an example analysis into Chinook’s ecosystem, see Chinook DB Analysis.

Related pages:

• Data Analysis Overview
• Analyze AutoDoctor Artifacts