Round-tripping SQLite schemas: what a diagram lost and what was fixed
Schemity, a desktop ERD tool, read two real SQLite schemas, Campfire on Rails 8 and Jellyfin on EF Core 10, and rebuilt database files from the SQL it wrote. Before the fixes, declared column types, partial and expression indexes and AUTOINCREMENT settings were lost. The post is a useful case study for anyone who edits or migrates SQLite schemas with a tool.
Key points
- Declared column types such as varchar(16), datetime(6) and bigint are kept with their length or precision and written back.
- Partial indexes keep their WHERE clause, and expression indexes keep their key expressions on SQLite.
- AUTOINCREMENT, WITHOUT ROWID and STRICT are recorded per table, so id reuse matches the original.
- Both Import SQL and the live connection now skip FTS5 virtual tables and their five data tables.
- Foreign keys keep the names declared in the DDL, and CREATE TABLE keeps the declared column order.
- The fixed build ships in the next release; the currently released app still has the type and index losses.
A diagram that reads a SQLite schema and writes it back should produce the same file. Schemity's team tested this on Campfire (Rails 8) and Jellyfin (EF Core 10). The earlier build dropped declared types like varchar and datetime(6), so Rails read timestamps as decimals and Campfire could not create accounts, and Jellyfin lost its partial and expression indexes.
The fixes keep declared types with their lengths, preserve index WHERE clauses and key expressions, and store AUTOINCREMENT, WITHOUT ROWID and STRICT per table. Jellyfin now matches its original exactly. Campfire differs only in its FTS5 search table, which a diagram cannot represent. The corrected build ships in the next release.
Real-world use
- Reviewing an inherited SQLite database from an open-source app before writing migrations against it.
- Generating a diagram from a Rails or EF Core SQLite file and recreating a test copy for QA.
- Checking that a schema tool does not change declared column types or AUTOINCREMENT in a desktop app's local database.
Why it matters
SQLite accepts these declarations silently, so check the declared types, indexes and AUTOINCREMENT settings in any schema tool's output. Rails and EF Core read those declared types at runtime, so a wrong one can break the application.