SchemaSpy with SQLite

Intro

SchemaSpy is my preferred tool for generating clear, browsable HTML documentation and ER diagrams for SQL databases. It works seamlessly with engines like MySQL and PostgreSQL, but SQLite has historically been difficult to configure and poorly supported. After considerable trial and error, I’ve identified a reliable combination of components and the exact command needed to make SchemaSpy work consistently with SQLite databases.

The Pieces You Need

SchemaSpy itself is “just a JAR,” but to actually generate diagrams you need a few companion pieces: a JDBC driver, a working Java runtime, and (optionally) GraphViz. Getting it to work with SQLite has historically been finicky, if not downright impossible, but the following setup finally did the trick for me

Quick Download Commands

If you prefer the CLI to clicking links, you can grab the JARs like this:

# Download SchemaSpy 6.2.4
curl -L https://github.com/schemaspy/schemaspy/releases/download/v6.2.4/schemaspy-6.2.4.jar \
     -o schemaspy-6.2.4.jar

# Download SQLite JDBC driver 3.45.1.0
curl -L https://repo1.maven.org/maven2/org/xerial/sqlite-jdbc/3.45.1.0/sqlite-jdbc-3.45.1.0.jar \
     -o sqlite-jdbc-3.45.1.0.jar

Example Database: Chinook

If you don’t want to run SchemaSpy against your own data right away, the Chinook sample database is a great test case.

• Download the SQL script: Chinook_Sqlite.sql
• Create a SQLite database file from it:

sqlite3 chinook.db < Chinook_Sqlite.sql

This will generate chinook.db populated with a realistic music store schema (artists, albums, tracks, invoices) that works perfectly for testing SchemaSpy.

The Command

The following example assumes that the SchemaSpy jar file, the sqlite-xerial jdbc driver and the db file (chinook.db) are in the current working directory. Otherwise simply provide the correct path to the required files.

java -jar schemaspy-6.2.4.jar \
  -dp sqlite-jdbc-3.45.1.0.jar \
  -t sqlite-xerial -cat % -s main -sso \
  -db ./chinook.db \
  -o ./schemaspy_output \
  -vizjs

Breakdown:

• -dp → path to the SQLite JDBC driver jar (Xerial).
• -t sqlite-xerial → use the modern Xerial driver profile.
• -cat % → wildcard catalog (SQLite doesn’t really have catalogs).
• -s main → SQLite’s default schema is always main.
• -sso → skip username/password (SQLite doesn’t use them).
• -db → path to your .db file.
• -o → output folder for the HTML docs.
• -vizjs → generate diagrams in-browser without GraphViz.

Why These Flags Matter

• Without -dp, SchemaSpy won’t find the correct driver.
• Without -t sqlite-xerial, it tries the old/dead SQLite profile.
• Without -cat % -s main, you’ll get schema ’null’ or catalog not provided errors.
• -sso avoids pointless login prompts which do not apply to Sqlite.
• -vizjs keeps the setup lightweight.

Results

SchemaSpy will generate a lovely, full HTML site in ./schemaspy_output. Open index.html to browse:

• Overview
• Table list
• Columns and constraints
• Relationship diagrams (ERD)

DEMO View live Chinook database demo

If you haven’t used it before, the generated site is static HTML ready to share with your team and easy to refer to while working with the database!

SQLite in the Wild

One reason it’s worth solving these setup issues is that SQLite is everywhere. Because it’s lightweight, reliable, and requires no separate server process, many applications ship with embedded SQLite databases by default. A few well-known examples:

• Apple macOS and iOS

  • Messages: ~/Library/Messages/chat.db holds iMessage and SMS history.
  • Safari: History.db, Downloads.db, and WebpageIcons.db store browsing data.
  • Photos: Photos.sqlite contains library metadata.
  • Mail: databases in ~/Library/Mail/.

• Web Browsers

  • Chromium/Chrome: uses SQLite for History, Cookies, Login Data, and more.
  • Firefox: places.sqlite for bookmarks and history, cookies.sqlite for cookie storage.

• Messaging and Communication

  • Skype: main.db contains chat logs.
  • Signal and WhatsApp Desktop: local SQLite databases maintain encrypted state.

• Other Tools

  • Thunderbird: mail indexing.
  • Slack desktop client: caches workspace data.
  • VS Code extensions: sometimes use SQLite for project state.

But SQLite isn’t just hidden inside big applications — it’s also ideal for your own projects. If you need a lightweight, file-based database with zero setup, SQLite is often the easiest choice. It’s great for prototypes, local tools, logging, side projects, or anything where you want structured storage without spinning up MySQL or Postgres. Pairing SQLite with SchemaSpy gives you a way to instantly visualize and document your schema, even on small personal databases.

Links & Stuff

• SchemaSpy
  GitHub Releases – schemaspy-6.2.4.jar
  SchemaSpy documentation

• SQLite JDBC Driver (Xerial)
  GitHub Releases – sqlite-jdbc
  Maven Central – org.xerial:sqlite-jdbc

• Java Runtime
  Debian/Ubuntu: sudo apt install openjdk-11-jre
  macOS (Homebrew): brew install openjdk
  Adoptium.net – Java binaries

• GraphViz (optional)
  Download from graphviz.org
  Debian/Ubuntu: sudo apt install graphviz
  macOS: brew install graphviz

• Chinook Sample Database
  Chinook_Sqlite.sql
  Chinook DataSources repo

Conclusion

SchemaSpy remains one of the most effective tools for documenting database schemas and generating ER diagrams. With the correct JDBC driver and options, it now works reliably with SQLite — a database engine that powers not only major applications like Safari, Chrome, and Apple Messages but also countless personal projects.

The working recipe is straightforward once you know it:

-t sqlite-xerial  -dp <sqlite-jdbc.jar>  -cat %  -s main  [-vizjs]

This guide demonstrated that configuration with both Apple’s chat.db and the Chinook sample database. The same approach can be applied to almost any SQLite file you encounter, whether it comes from a commercial application or your own side projects.

If you found this guide useful or ran into issues following it, I’d love to hear your feedback. Send comments, corrections, or ideas to feedback@adminjitsu.com.