Round-Tripping Campfire and Jellyfin Through a Database Diagram: What SQLite Lost and What We Fixed

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: We put two real SQLite schemas, Campfire (Rails 8) and Jellyfin (Entity Framework Core 10), through Schemity and rebuilt a database file from the SQL it wrote. Before this round of fixes Campfire's columns came back as an affinity name instead of their declared type, so Rails read created_at as a decimal and Campfire could not create an account; Jellyfin's partial and expression indexes were lost. After the fixes Jellyfin comes back with no difference, Campfire differs only in its FTS5 search table, Campfire's own code works on the copies, and all 18 behaviour probes across both schemas match the originals.

A database diagram that reads a SQLite schema and writes it back should give you the same database file, and before this round of fixes Schemity did not. We took two real open-source schemas, Campfire v1.5.2 (Rails 8, 23 tables) and Jellyfin 12.2 (Entity Framework Core 10, 33 tables), put each through Schemity from a .schema script and from the live database file, and rebuilt a new file from the SQL Schemity wrote. SQLite stored the same values in the copies, so most row-level checks passed, but Campfire's columns came back as an affinity name instead of their declared type, and Campfire's own code could not create an account on the copy. After the fixes, Jellyfin comes back with no catalog difference on either path, Campfire differs only in its FTS5 search table, and both behave like their originals.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. This is the fourth post in a series that tests the round trip on each database it supports, after Mastodon and Saleor on PostgreSQL, Invoice Ninja and Sylius on MySQL and Bitwarden and Smartstore on SQL Server. Reading a schema you did not write is how you understand your schema before changing it, and SQLite tested something the other three never did: a database that accepts any type name and keeps it, while enforcing almost none of it.

Which two schemas, and which two paths?

Two schemas built by two frameworks that treat SQLite differently. Campfire, 37signals' self-hosted chat app, comes from Rails migrations, which declare types the way Rails reads them back: datetime(6), varchar, bigint, json. Jellyfin, the media server, comes from Entity Framework Core migrations, which declare only SQLite's own names (INTEGER, TEXT, REAL) but use partial and expression indexes.

Each schema went through Schemity on two paths:

• Import SQL: the sqlite3 shell's .schema output, read by Schemity's SQL parser, with no connection.
• Live connection: Schemity's SQLite reader opened the original database file and read SQLite's PRAGMA functions and sqlite_master.

Both paths end with the diagram's SQL applied to an empty file with the sqlite3 shell. The runs used Schemity's own code from a development build, called from scripts rather than through the app's window. The fixes described below are in that build and ship in the next release; until then, the released app has the type and index losses in the "before" column.

How do you prove a rebuilt SQLite schema is the same?

Three checks, one more than the earlier posts, because on SQLite the database itself barely notices the difference that mattered most.

The catalog diff. A script lists one line per fact for the original file and for the copy: every table with WITHOUT ROWID, STRICT and AUTOINCREMENT; every column with its declared type, nullability, default and key position; every foreign key with its actions; every index with its keys, WHERE and expressions. The lists are diffed line by line. A declared type is compared without regard to case, as SQLite and Rails read it.

The behaviour probes. A probe is a few statements in one transaction that is rolled back, run with PRAGMA foreign_keys = ON as both frameworks open their connections: delete an account and insert another to see which id it gets, store '12' in the json column and ask SQLite what type it kept, ask SQLite how it plans a lookup by lower("Name"). The outcome is "accepted" with what the SELECT read back, or "refused at step N" with SQLite's message.

The framework itself. Campfire's Docker image ran bin/rails runner against each copy: print the type Rails reads for three columns of accounts (created_at, settings, join_code), then create an account through Campfire's own Account model and read it back.

The originals hold a few rows the frameworks wrote (Campfire's schema_migrations and ar_internal_metadata, Jellyfin's __EFMigrationsHistory and 4 rows of library items); every probe uses rows of its own.

What failed before the fixes?

Everything below is from Schemity's code just before these fixes (commit cb4b7b28), on both paths.

Cascades, unique indexes, defaults, NULL handling in unique columns, composite keys and foreign keys all survived. The failures:

Why does a declared type matter if SQLite ignores it?

Because SQLite does not ignore it; it keeps it and reads only part of it. A column's declared type sets its affinity by five substring rules: a name containing INT gets INTEGER, one containing CHAR, CLOB or TEXT gets TEXT, and so on, down to NUMERIC for anything else, including datetime and json. The length in varchar(16) is ignored. Schemity's SQLite support modelled only the five affinity names, so both paths turned varchar into TEXT, datetime(6) into NUMERIC and bigint into INTEGER. Each pair has the same affinity, so SQLite stores the same values in the copy, and every SQL probe passed.

Rails reads the declared text. On the copy, bin/rails runner printed created_at: NUMERIC -> decimal, settings: NUMERIC -> decimal and join_code: TEXT -> text, where the original says datetime(6) -> datetime, precision 6, json -> json and varchar -> string. Campfire's Account model keeps its settings in that json column, and creating an account failed with NoMethodError: undefined method 'reverse_merge!' for nil. Campfire deployed against a schema rebuilt by the diagram would have stopped at its first account, and any Rails app would have read its times as decimals.

The diagram now keeps each column's declared type on both paths, with its length or precision, and writes it back. The field editor offers the declared types frameworks write (VARCHAR, CHAR, BIGINT, INT, SMALLINT, BOOLEAN, DECIMAL, DOUBLE, FLOAT, DATE, TIME, DATETIME, JSON) beside the five affinity names. A diagram drawn before this change holds TEXT where the database says varchar; saving it does not rebuild the table for that, since the two have the same affinity, and a rebuild for another reason keeps the database's declared type. Lint compares foreign key types by affinity on SQLite, so a bigint column pointing at an integer key is not reported as a mismatch.

What happened to Jellyfin's partial and expression indexes?

They lost what made them different. Jellyfin has four partial indexes, such as IX_BaseItems_OwnerId ON BaseItems (OwnerId) WHERE OwnerId IS NOT NULL, and one on an expression, IX_Peoples_NameLower ON Peoples (lower("Name")). Both paths wrote the partial ones without WHERE and left the expression index out; the live reader had kept them only to replay during a table rebuild.

None of the five is unique, so no row is refused either way. The query plan shows the difference. On the copy without the expression index, SQLite planned a lookup by lower(Name) as a full scan of Peoples; on the original it searches the index. And IX_BaseItems_OwnerId, written as a plain index, holds every item instead of only those with an owner, so SQLite keeps it up to date on every write to an ownerless item and plans WHERE OwnerId IS NULL through it, where the original scans the table. The diagram now keeps a SQLite index's key expressions and WHERE, as it already did for PostgreSQL, and reads them through the same parser on both paths.

Why did a lock table start skipping ids?

Because AUTOINCREMENT changes which ids a table hands out, and the writer added it to every table. SQLite's documentation puts it plainly: a plain INTEGER PRIMARY KEY gives a new row the largest id plus one, so a deleted last id comes back, while AUTOINCREMENT never reuses one and records the highest in sqlite_sequence. Jellyfin declares it on 12 tables and leaves it off __EFMigrationsLock. The copy's lock table gave a new row id 2 after id 1 was deleted, where the original gives 1 again. Schemity now records AUTOINCREMENT, WITHOUT ROWID and STRICT for each table on both paths and writes them as declared; neither schema has a table of its own with the last two, so only unit tests cover them.

What is an FTS5 virtual table to a diagram?

Not a table it can hold. Campfire's message search is CREATE VIRTUAL TABLE message_search_index USING fts5 (body, tokenize=porter), and SQLite creates five tables of its own to keep the index in: message_search_index_data, _idx, _content, _docsize and _config. Import SQL could not parse the virtual table and imported the five as if Campfire had written them; the live reader read all six as plain tables, so the copy got a message_search_index table with a body column and no search. Import SQL now names the virtual table in its summary and leaves it out with its five data tables, and the live reader skips both kinds.

Which bugs did only a refresh find?

Import a script, connect to the file it came from, and Refresh & merge should find nothing. Through the app's own merge code, it planned 152 statements for Campfire and 503 for Jellyfin, mostly table rebuilds. Foreign key names differed on 9 of Campfire's tables and 22 of Jellyfin's. SQLite keeps a foreign key's name only in the table's DDL: Import SQL read fk_rails_c3b3935057 and FK_Devices_Users_UserId from the script, while the live reader named each key after its columns. The live reader now takes a name the DDL declares, the writer writes it as CONSTRAINT "name" FOREIGN KEY, and both refreshes list nothing, as do the six schemas from the earlier posts.

Taking the screenshots for this post turned up one more, which the scripts could not see because they skip a step the app takes when it reads a table: the app lists a table's fields key first, foreign keys next and timestamps last, and wrote CREATE TABLE in that order. A copy of Campfire's sessions built from the diagram put user_id second instead of last, so a SELECT * or an INSERT without a column list would read and write its columns in a different order, and a SQLite table rebuild writes the same CREATE TABLE. The diagram now remembers each column's position as the table declares it and writes that order, while the canvas keeps its own; a column added in the diagram goes last, where ADD COLUMN puts it. Read and written back the way the app does it, 14 of Campfire's 17 tables and 20 of Jellyfin's 33 came back in a different column order before this fix, and none do now.

What does the round trip give you now?

The same file, on both paths, for both schemas, apart from the search table.

All 72 of Campfire's declared types, the 28 NOT NULL rowid keys, Jellyfin's 4 partial indexes and its expression index, and every table's AUTOINCREMENT come back as they were.

In the app's window, Import SQL of Campfire's script onto the diagram read from its live file finds nothing to change.

What still differs, and why?

Virtual tables. FTS3, FTS4, FTS5 and R*Tree tables belong to a SQLite module, not to columns a diagram can hold, so the diagram's SQL does not create Campfire's message_search_index. Run its CREATE VIRTUAL TABLE from the script after the diagram's SQL and SQLite creates the five data tables itself; with that one statement added, Campfire's copy matches the original line for line.

Spelling. A declared type is written in upper case (DATETIME(6) for datetime(6)), which SQLite and Rails read the same way. An INTEGER PRIMARY KEY declared without NOT NULL gets it, which a rowid key is anyway; both schemas already declare it.

What this benchmark does not show

• The app's window. These runs called Schemity's import, reader, export and comparison code from scripts on a development build. The screenshots are from the app's window on the same build, and the column-order check above went through the app's own read path.
• Data. The originals hold only bookkeeping rows. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
• Triggers, views and CHECK constraints. Neither schema has one.
• Snowflake. The last post in the series puts TPC-DS and a Snowflake feature schema through the same round trip.
• Other frameworks. Django and Prisma declare SQLite types their own way; this tested Rails and EF Core.

How to rerun it

Create each schema with its framework against an empty file, take sqlite3 <file> .schema for the script, put the script or the file through Schemity, export the SQL and apply it to an empty file. Then list both catalogs with this query, a shorter form of the benchmark's, and diff the output. It lists a primary key or UNIQUE constraint's index by origin only and leaves collations out, which the benchmark's script compares:

SELECT line FROM (
  SELECT 'table | ' || l.name || ' | ' || l.type
         || CASE WHEN l.wr THEN ' | without rowid' ELSE '' END
         || CASE WHEN l.strict THEN ' | strict' ELSE '' END
         || CASE WHEN m.sql LIKE '%AUTOINCREMENT%' THEN ' | autoincrement' ELSE '' END AS line
    FROM pragma_table_list l JOIN sqlite_schema m ON m.name = l.name
   WHERE l.schema = 'main' AND l.name NOT LIKE 'sqlite_%'
  UNION ALL
  SELECT 'column | ' || m.name || ' | ' || c.name || ' | ' || lower(c.type)
         || ' | ' || CASE c."notnull" WHEN 1 THEN 'not null' ELSE 'null' END
         || ' | ' || ifnull('default ' || c.dflt_value, 'no default') || ' | pk ' || c.pk
    FROM sqlite_schema m, pragma_table_xinfo(m.name) c
   WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
  UNION ALL
  SELECT 'fk | ' || m.name || ' | ' || f."from" || ' | ' || f."table" || ' | ' || f."to"
         || ' | on delete ' || f.on_delete || ' | on update ' || f.on_update
    FROM sqlite_schema m, pragma_foreign_key_list(m.name) f
   WHERE m.type = 'table'
  UNION ALL
  SELECT 'index | ' || m.name || ' | ' || i.name || ' | unique ' || i."unique" || ' | '
         || ifnull(replace(x.sql, '"', ''), i.origin)
    FROM sqlite_schema m, pragma_index_list(m.name) i
    LEFT JOIN sqlite_schema x ON x.name = i.name
   WHERE m.type = 'table'
) ORDER BY line;

It needs SQLite 3.37 or later for pragma_table_list. For behaviour, run each probe between BEGIN and ROLLBACK with PRAGMA foreign_keys = ON, end it with a SELECT of what was stored or an EXPLAIN QUERY PLAN, and compare the outputs. And if a framework reads your schema, ask it: on SQLite the declared type is for the application more than for the database.