DEV Community: Son Tran

How to Track Schema Changes in PostgreSQL: DDL Logging, an Event Trigger or a Schema Snapshot

Son Tran — Fri, 23 Oct 2026 15:15:10 +0000

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

TL;DR: Pick by the question you need answered. log_statement = 'ddl' records what was attempted, rolled-back statements included, and misses DDL run inside a DO block or function. An event trigger records committed changes with the login that made them, but only a superuser can install it, and a superuser can switch it off without leaving a trace in it. A schema snapshot compared with the live database shows what is different now, however it got there, but not who did it.

There are three ways to track schema changes in PostgreSQL, and they answer three different questions. The server log with log_statement = 'ddl' records what someone tried to run. An event trigger records what was committed, and by whom. A schema snapshot compared with the live database shows what is different now, whoever made the change and however they made it.

Each one has a gap the others cover. The log keeps statements that were rolled back and misses DDL run inside a function. The event trigger needs a superuser to install, and a superuser can switch it off for one session. The snapshot cannot tell you who. Every result below was measured on PostgreSQL 18.3 in a throwaway container, against this schema:

CREATE TABLE customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL
);

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers,
    total numeric(12,2) NOT NULL
);

How to track schema changes in PostgreSQL

The schema change you need to find is rarely the one in your migrations folder. It is the index someone added by hand during an incident, the column a support script created, the ALTER an AI agent ran against a dev database that was later promoted. Migration tools record which of their files ran; they do not notice a change made around them. Hence the three approaches below, compared on what they catch:

	Server log (DDL)	Event trigger	Schema snapshot
Answers	What was attempted	What was committed, by whom	What is different now
Rolled-back DDL	Logged, with no sign it was undone	Not recorded	Not visible (nothing changed)
DDL inside `DO` or a function	Missed	Recorded	Visible
Who made the change	Only with `%u` added to `log_line_prefix`	`session_user` in the audit row	No
Roles, databases, tablespaces	Logged	Never fires	Only with `pg_dumpall --globals-only`
Needs	Superuser to set it	Superuser to create it	`SELECT` on the tables it dumps
Can be switched off by	A superuser	A superuser, per session or for everyone, unrecorded	Nobody, but it sees only the state at each snapshot

For most teams the answer is a snapshot for what changed, plus an event trigger for who did it. The rest of this post is the evidence for each row.

Does log_statement = 'ddl' catch every schema change?

It catches every DDL statement a client sends, which is not the same thing. With log_statement = 'ddl' set, PostgreSQL wrote these lines for this session:

ALTER TABLE orders ADD COLUMN note text;
BEGIN; ALTER TABLE orders ADD COLUMN rolled_back int; ROLLBACK;
DO $$ BEGIN EXECUTE 'ALTER TABLE orders ADD COLUMN via_do int'; END $$;

The first two appeared as LOG: statement: ALTER TABLE orders ADD COLUMN .... The second was rolled back, and the log says nothing about the rollback: anyone reading the log afterwards sees a rolled_back column that orders does not have. The third added a real column, via_do, and was not logged at all, because the statement the client sent was a DO block, not DDL. A function that runs EXECUTE 'ALTER TABLE ...' behaves the same way: the CREATE FUNCTION is logged, the call that changes the table is not.

One more limit comes from the PostgreSQL logging documentation: the default log_line_prefix is '%m [%p] ', a time and a process ID, so the line does not say which user ran it until you add %u.

The log is still worth turning on. It is one setting, it costs nothing, and for "what happened at 02:14 last night" it is often the only record there is.

How do you log DDL changes with an event trigger?

An event trigger runs a function after every DDL command, inside the same transaction. This one writes each change to an audit table, and a second trigger catches drops, which pg_event_trigger_ddl_commands() does not report:

CREATE TABLE ddl_audit (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    happened_at  timestamptz NOT NULL DEFAULT now(),
    db_user      text NOT NULL DEFAULT session_user,
    command_tag  text NOT NULL,
    object_type  text,
    object_name  text,
    statement    text
);

CREATE FUNCTION log_ddl() RETURNS event_trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
DECLARE r record;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        INSERT INTO ddl_audit (command_tag, object_type, object_name, statement)
        VALUES (r.command_tag, r.object_type, r.object_identity, current_query());
    END LOOP;
END $$;

CREATE FUNCTION log_drop() RETURNS event_trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
DECLARE r record;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
        INSERT INTO ddl_audit (command_tag, object_type, object_name, statement)
        VALUES (tg_tag, r.object_type, r.object_identity, current_query());
    END LOOP;
END $$;

CREATE EVENT TRIGGER audit_ddl ON ddl_command_end EXECUTE FUNCTION log_ddl();
CREATE EVENT TRIGGER audit_drop ON sql_drop EXECUTE FUNCTION log_drop();

Run against the same three statements, the audit table got one row for the committed ALTER, nothing for the rolled-back one, and a row for the column added inside the DO block, with the whole DO block as the statement. An ALTER TABLE orders DROP COLUMN produced two rows: the dropped column from the sql_drop trigger and the altered table from ddl_command_end. A COMMENT ON COLUMN was recorded too. A DROP TABLE IF EXISTS on a table that did not exist recorded nothing, which is right, since nothing changed.

Four things to know before you rely on it:

SECURITY DEFINER is not optional. Without it, the audit INSERT runs as whoever issued the DDL. In the test, a role with CREATE on the schema but no INSERT on ddl_audit ran CREATE TABLE and got permission denied for table ddl_audit: the audit trigger blocked the change it was meant to record. With SECURITY DEFINER, the same CREATE TABLE succeeded and the row named the user, because session_user is still the login role inside the function.
Only a superuser can create it. A non-superuser gets permission denied to create event trigger with the hint Must be superuser to create an event trigger, as the CREATE EVENT TRIGGER reference says.
Shared objects are never seen. The event trigger documentation excludes databases, roles, tablespaces, parameter privileges and ALTER SYSTEM. A CREATE ROLE left no row.
A superuser can step around it. SET event_triggers = off, a setting added in PostgreSQL 17, disables every event trigger for the session, and an ALTER TABLE run after it left no row. SET session_replication_role = replica did the same until both triggers were set to ENABLE ALWAYS (ALTER EVENT TRIGGER audit_ddl ENABLE ALWAYS, and the same for audit_drop), after which they fired anyway. ALTER EVENT TRIGGER audit_ddl DISABLE or a DROP EVENT TRIGGER turns it off for everyone, and leaves no row either, since event triggers never fire for commands on event triggers. The server log does record those, which is the best argument for running both.

So an event trigger is an honest record of changes made by people who are not trying to hide them. That is most changes, and it is the only one of the three that answers "who", with one caveat: session_user is the login role. A superuser who runs SET ROLE dev before a CREATE TABLE is recorded as the superuser, and a team that shares one migration login gets that login as the answer every time.

Why does a pg_dump diff show changes that did not happen?

The snapshot approach is the simplest to set up: dump the schema, commit the file, dump it again later and compare. pg_dump --schema-only needs no superuser, only SELECT on the tables it dumps (it takes an ACCESS SHARE lock on each), and the comparison sees everything that is in the database now, however it got there. Its blind spot is the time between snapshots: a change made and undone in between leaves no trace.

Since the August 2025 minor releases (17.6, 16.10 and their siblings), two dumps of an unchanged table are not identical. Each begins with a \restrict line and ends with an \unrestrict line carrying a random key, a defence against a malicious server injecting psql commands into a dump (CVE-2025-8714, released August 14, 2025). Diffing two dumps of the same orders table taken a second apart on 18.3 gave a four-line difference and no schema change.

The fix is pg_dump --schema-only --restrict-key=schema, which writes a fixed key. The pg_dump documentation names comparing dump files as a reason to use it, and warns against using a known key for a dump you will restore. With the fixed key, a real change was the only difference: total numeric(12,2) became total numeric(14,2). The -- Dumped from database version and -- Dumped by pg_dump version headers still change when the server or the client is upgraded, so expect one noisy diff then.

What the snapshot cannot do is say who changed the column or when. It says the column is different now. That is usually the question you have when you inherit a database, or come back to one after a month, and it is the one the logs answer worst: you would have to replay every line since the last time you looked.

Seeing what changed since you last saved the diagram

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. The saved diagram is a schema snapshot already, so opening it is the comparison. When a connected diagram that has been saved at least once opens, Schemity reads the live database, compares it with the file as you last saved it, and if anything differs, a Database changed button appears in the canvas's top-right corner. A change made through psql, an ORM's migration or an agent's session shows up there the same way.

The button opens the change preview, titled "Changed in the database since you last saved". Every table the database changed gets a border: green for added, red for removed, blue for altered, with removed tables drawn where they used to be, since the diagram itself no longer has them. A table's footer counts what changed in its keys, constraints and indexes, so a hand-made index reads idx: 1 (+1), with a bar on the column it covers. Each change is also listed in words, such as Index on orders (status) added. There is no SQL in it, because nothing is going to run. On a restricted licence tier the same changes come as a plain list instead of the drawing.

On the main diagram, tables the database added carry the dashed border of a table added since the last save.

What it compares is what the database owns: tables, columns with their type, nullability and default, primary keys, unique constraints, checks, indexes and declared foreign keys. Positions, colours, descriptions and virtual relations are the diagram's own and never count as a change. Views, triggers, functions and grants are not part of the comparison, so keep the event trigger for those.

Nothing is accepted until you save. The merge sits on the diagram as one unsaved change: Save accepts the database's version and the button goes, Undo puts the diagram back to the file. Commit the saved file and the diagram's JSON in Git becomes the dated record of what the schema was at each save, which is the part a snapshot needs and a log never gives you. The same check runs on MySQL, SQL Server and SQLite connections.

This is what it takes to understand your schema as it is rather than as the migrations say it should be. It does not replace the event trigger: the preview tells you orders gained an index since Tuesday, and the audit table tells you who added it and what they typed.

How to Auto-Update an updated_at Column: MySQL ON UPDATE vs a Database Trigger vs the ORM

Son Tran — Thu, 22 Oct 2026 02:53:40 +0000

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

TL;DR: A DEFAULT CURRENT_TIMESTAMP fills updated_at only once, at insert. On MySQL, add ON UPDATE CURRENT_TIMESTAMP to the column; on PostgreSQL, SQL Server and SQLite, write a trigger; or let the ORM set it, which works on every database but misses every write that does not go through the ORM. Pick the database side when scripts, admins or other services write to the table, and the ORM side when only one application does.

Whether updated_at should be set by the database or by the ORM comes down to one question: does anything besides your application write to the table? The database side means MySQL's ON UPDATE CURRENT_TIMESTAMP column clause or a trigger everywhere else; the application side means Rails timestamps, Django's auto_now, Eloquent or Prisma's @updatedAt. A column default alone never moves after the row is created.

The short version: if anything other than your application writes rows (a migration backfill, a support engineer in psql, a second service, a bulk update_all), keep it in the database. If one application owns every write, the ORM is simpler and portable. The rest of this post is what each option does on the edge cases that decide it, and the schema changes that break each one later without an error. Every database behaviour below was run on MySQL 8.4.11, PostgreSQL 18.3, SQL Server 2022 and SQLite 3.54.0 in throwaway containers; the ORM behaviour is quoted from each framework's documentation or source.

Why DEFAULT CURRENT_TIMESTAMP is not enough

Most schemas start the same way. On MySQL:

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);

Both columns get the insert time. After that, updated_at is an ordinary column, and an UPDATE orders SET status = 'paid' leaves it exactly where it was. A default is a value for a missing column at insert, on every database. Keeping a "last changed" time current is a separate job, and each database gives you a different tool for it.

How to auto update updated_at column: the three options

	MySQL `ON UPDATE`	PostgreSQL trigger	ORM (`auto_now`, `@updatedAt`, Rails timestamps)
Available on	MySQL `TIMESTAMP` and `DATETIME` only	Any database with triggers (SQL Server and SQLite below)	Any database
An `UPDATE` that changes nothing	Column unchanged	Column moves	Rails sends no `UPDATE`; Django's `save()` moves it
An `UPDATE` that sets `updated_at` itself	Your value is kept	Your value is overwritten	Rails and Prisma keep it, Django's `auto_now` overwrites it
Raw SQL and scripts	Covered	Covered	Not covered
Bulk updates	Covered	Covered	Eloquent sets it; Django `QuerySet.update()` and Rails `update_all` do not
Renaming the column	The clause follows the column	Next `UPDATE` fails	Change the model
Where it shows up	In the column definition	In a trigger, outside the table	In application code

The rows that decide it are the bulk and raw writes, and the rename, so each option gets its own section.

MySQL: ON UPDATE CURRENT_TIMESTAMP

MySQL is the only one of the four with a column clause for this:

updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)

It fires only when a row's values actually change. UPDATE orders SET status = 'new' on a row whose status is already new reports 0 rows changed and leaves updated_at alone, which matches the MySQL manual: an auto-updated column "remains unchanged if all other columns are set to their current values". An UPDATE that sets updated_at explicitly keeps the value it was given, which is how a data fix can backdate a row on purpose. The clause works on DATETIME as well as TIMESTAMP, and with fractional seconds the precision has to match on both sides: CURRENT_TIMESTAMP(6) for a TIMESTAMP(6) column.

The trap is in later migrations. ALTER TABLE orders MODIFY updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) replaces the whole column definition, and since it does not repeat the clause, the clause is gone. MySQL accepts the statement without a warning. Before it, information_schema.columns.EXTRA read DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6); after it, DEFAULT_GENERATED. The column keeps its old values and stops moving, and nothing notices until someone asks why a row edited yesterday says it was last changed in March. RENAME COLUMN, by contrast, keeps the clause.

PostgreSQL, SQL Server and SQLite: a trigger

PostgreSQL has no column clause, so the column is kept current by a BEFORE UPDATE trigger:

CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER orders_set_updated_at BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

PostgreSQL also ships the same thing as an extension, moddatetime, in contrib: CREATE EXTENSION moddatetime; then CREATE TRIGGER ... BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at);. It saves writing the function and accepts only timestamp or timestamptz columns.

Three behaviours differ from MySQL, and all three were reproduced on 18.3:

It fires on every UPDATE, changed or not. UPDATE orders SET status = status moves updated_at. Adding WHEN (OLD.* IS DISTINCT FROM NEW.*) to the CREATE TRIGGER gives MySQL's no-op behaviour back; an explicit value is still overwritten.
It overwrites an explicit value. UPDATE orders SET updated_at = '2020-01-01' stores the current time instead, with the plpgsql function and with moddatetime. A backfill that needs to set historic times has to disable the trigger first.
now() is the start of the transaction, not the moment of the update. Every row a long transaction touches gets the same time. Use clock_timestamp() in the function if you need the wall clock.

SQL Server and SQLite use an AFTER UPDATE trigger that runs a second UPDATE on the same row. That second update does not fire the trigger again because recursive triggers are off by default on both (is_recursive_triggers_on = 0, PRAGMA recursive_triggers returns 0). On SQL Server:

CREATE TRIGGER orders_set_updated_at ON orders AFTER UPDATE AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(updated_at) RETURN;
    UPDATE o SET updated_at = SYSUTCDATETIME()
    FROM orders o JOIN inserted i ON i.id = o.id;
END;

And on SQLite:

CREATE TRIGGER orders_set_updated_at AFTER UPDATE ON orders
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
    UPDATE orders SET updated_at = strftime('%Y-%m-%d %H:%M:%f', 'now') WHERE id = NEW.id;
END;

Both keep an explicit value, and both still move the column on a no-op UPDATE. The SQL Server guard has a catch: UPDATE(updated_at) is true whenever the column is in the SET list, even with its old value, so an ORM that writes every column on save makes this trigger return every time and the column never moves. Drop the guard if your ORM does that.

The weakness of a trigger is that it names the column in a place the column does not know about. Rename updated_at to modified_at and PostgreSQL accepts the rename, then fails the next UPDATE on the table with record "new" has no field "updated_at" from the plpgsql function, or "orders" has no attribute "updated_at" from moddatetime. Dropping the column does the same. The migration succeeds; the first write after the deploy is what fails. SQL Server fails the same way after sp_rename, which at least prints a caution. SQLite is the exception: it rewrites the trigger body on RENAME COLUMN, though a dropped column still breaks the next UPDATE with no such column: NEW.updated_at. A generated column is no way out here, since it cannot call now().

The ORM: auto_now, @updatedAt and Rails timestamps

Rails, Django, Laravel's Eloquent and Prisma all set updated_at themselves, in application code, so the same model works on every database and the behaviour is visible in the codebase. The cost is every write that does not go through that code path, and the frameworks document where that is:

Django: auto_now is "only automatically updated when calling Model.save()", not by QuerySet.update().
Rails: update_all "does not trigger Active Record callbacks or validations", so it does not touch updated_at either.
Prisma: @updatedAt is "implemented at Prisma ORM level", so a raw SQL UPDATE leaves it alone.
Eloquent is the exception for bulk writes: its query builder's update() adds updated_at to the values, so Order::where(...)->update([...]) sets it. A DB::table('orders')->update() does not.

Add a manual fix run in a SQL console, a nightly script, or a second service written in another language, and updated_at is right for some writes and stale for others. That is acceptable when the column is for display. It is not when an incremental export, a cache, or a sync job reads WHERE updated_at > :last_run, because a stale row is a row that sync never sends.

Which should you use: MySQL ON UPDATE, a trigger or the ORM?

One application owns every write, and updated_at is informational: let the ORM do it. It is portable and it lives next to the model.
Anything reads updated_at to decide what changed (sync, export, cache invalidation): put it in the database, because the database sees every write. On MySQL that is the column clause; elsewhere a trigger, with the WHEN clause if no-op updates should not count. Database-side fixes coverage, not ordering: now() is when a transaction started, so a long transaction that commits after a sync run can stamp rows earlier than that run's :last_run. Query with an overlap, such as updated_at > :last_run - interval '5 minutes', and make the sync idempotent.
Both at once is harmless when the column is for display: MySQL and the SQLite trigger keep the value the ORM wrote, and a PostgreSQL trigger replaces it with its own now(). For sync, pick one clock, the database's, since the application servers' clocks and time zones are not the database's.

Whichever you choose, the column's behaviour now depends on something the CREATE TABLE may not show, which is what makes it easy to lose in a later migration.

How Schemity keeps updated_at in the schema

To design the next one without losing this, the schema, not the application, has to carry the decision. 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.

On MySQL, the clause is part of the column in the diagram. Import SQL and the live reader both read ON UPDATE CURRENT_TIMESTAMP, and the field dialog has an "On update, set to the current time" tick on TIMESTAMP and DATETIME columns. Because the diagram holds it, every MODIFY in the migration SQL Schemity writes restates it, so changing the column's type or default does not quietly drop it the way a hand-written MODIFY does. Converting a MySQL diagram to another database leaves the clause out and says so in the conversion notes (ON UPDATE CURRENT_TIMESTAMP(6) "is left out: PostgreSQL has no column that sets itself on update"), rather than writing a column that looks the same and stops moving.

With the default removed and the tick left on, the planned migration still carries the clause:

On PostgreSQL, the mechanism lives outside the table, and the diagram is honest about that. Import SQL keeps CREATE EXTENSION moddatetime and writes it back as CREATE EXTENSION IF NOT EXISTS moddatetime; setup SQL that runs before the tables, but the trigger and its function are not part of a table in the diagram: they are counted among the skipped statements, and updated_at reads as a timestamptz column with its now() default.

When you rename or drop updated_at in a diagram connected to the database, impact analysis reads the table's triggers from the catalogue and names each one, with the warning that it fails the next time it runs if it uses what changed. That is the error above, shown before the migration rather than after the deploy.

When you design the next table, the template every new entity starts from is the place to settle the columns once: created_at and updated_at with their types and defaults, so the question of how updated_at stays current is asked for every table rather than remembered for some.

SQL Server INT to BIGINT Primary Key: Four Ways to Change It and What Each One Costs

Son Tran — Wed, 21 Oct 2026 07:02:37 +0000

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

TL;DR: SQL Server refuses ALTER COLUMN on an INT primary key while the key, any foreign key or any index uses the column, so the in-place change is drop, alter, recreate. On two 1,000,000-row tables that took 20.5 seconds and about 1 GB of transaction log. Row compression stops the ALTER COLUMN from rewriting rows, new tables copied across avoid the long lock, and a negative reseed buys time when no window is available yet.

To change an INT primary key to BIGINT in SQL Server, you have to drop everything that uses the column first: every foreign key that references it, the primary key itself, and the indexes on the referencing columns. A plain ALTER COLUMN fails with Msg 5074 while any of them exists, and once they are gone the change rewrites every row of every table involved under a lock that blocks all reads and writes.

The question usually arrives as an outage. An IDENTITY(1,1) key reaches 2,147,483,647 and the next insert fails with Msg 8115, Level 16: "Arithmetic overflow error converting IDENTITY to data type int." Below are the four ways out, run on SQL Server 2022 (CU26, Developer edition) in a throwaway container. That container ran under x86 emulation on a laptop, so read the timings as ratios between methods, not as what your server will do.

How close are your INT keys to the limit?

Before choosing a method, find out how much time you have. sys.identity_columns keeps the last value each identity handed out:

SELECT OBJECT_SCHEMA_NAME(ic.object_id) AS schema_name,
       OBJECT_NAME(ic.object_id) AS table_name,
       ic.name AS column_name,
       CAST(ic.last_value AS bigint) AS last_value,
       CAST(100.0 * CAST(ic.last_value AS bigint) / 2147483647 AS decimal(5,2)) AS pct_used
FROM sys.identity_columns ic
WHERE TYPE_NAME(ic.system_type_id) = 'int'
  AND OBJECTPROPERTY(ic.object_id, 'IsUserTable') = 1
ORDER BY pct_used DESC;

A table at 80% with a steady insert rate gives you a date. A negative last_value and percentage mean someone already reseeded it, which is the last method below. The query covers the keys; the foreign key columns that point at them are just as INT, and every one of them has to change in the same migration, because SQL Server will not keep a foreign key across two types. Creating an INT foreign key against a BIGINT key failed with Msg 1778, "is not the same data type as referencing column", and even a VARCHAR(20) column against a VARCHAR(40) key failed with Msg 1753, "must be defined with the same length and scale".

Why does ALTER COLUMN fail on an INT primary key?

Because SQL Server checks every object that uses the column before it changes the type. With orders.user_id referencing users.id and an index on orders.user_id, the obvious statement:

ALTER TABLE users ALTER COLUMN id BIGINT NOT NULL;

returns:

Msg 5074, Level 16, State 1
The object 'PK_users' is dependent on column 'id'.
Msg 5074, Level 16, State 1
The object 'FK_orders_users' is dependent on column 'id'.
Msg 4922, Level 16, State 9
ALTER TABLE ALTER COLUMN id failed because one or more objects access this column.

Changing the referencing column first fails the same way, naming IX_orders_user_id and FK_orders_users. The ALTER TABLE documentation lists the objects that block a type change: a PRIMARY KEY or FOREIGN KEY constraint, a CHECK or UNIQUE constraint, a default, an index, statistics made with CREATE STATISTICS, and a computed column. In the same container, a CHECK (n > 0) on the column, an index that only INCLUDEs it, and a view created WITH SCHEMABINDING that selects it all blocked INT to BIGINT as well. On a real database, look for schema-bound views and functions before you plan the window: each one has to be dropped and created again around the change.

Two things do not get you past it. WITH (ONLINE = ON), available since SQL Server 2016 and on Enterprise edition only, returned the same Msg 5074 on a key column. And the one exception the documentation makes is narrower than it looks: an index, a CHECK or a UNIQUE constraint lets a VARCHAR, NVARCHAR or VARBINARY column grow to a longer length of the same type. In the container the same growth also passed under a primary key, but a foreign key still blocked it. INT to BIGINT gets no exception.

How do you change an INT primary key to BIGINT in SQL Server?

In place, it is eight statements for one key and one referencing table, inside one transaction so that a failure halfway does not leave users without its primary key. Foreign keys go first, because the primary key cannot be dropped while one references it:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
ALTER TABLE orders DROP CONSTRAINT FK_orders_users;
ALTER TABLE users DROP CONSTRAINT PK_users;
DROP INDEX IX_orders_user_id ON orders;
ALTER TABLE users ALTER COLUMN id BIGINT NOT NULL;
ALTER TABLE orders ALTER COLUMN user_id BIGINT NOT NULL;
CREATE INDEX IX_orders_user_id ON orders (user_id);
ALTER TABLE users ADD CONSTRAINT PK_users PRIMARY KEY (id);
ALTER TABLE orders ADD CONSTRAINT FK_orders_users
    FOREIGN KEY (user_id) REFERENCES users (id);
COMMIT;

The IDENTITY property survives the type change. After the script, sys.columns still showed is_identity = 1 on users.id, and after DBCC CHECKIDENT ('users', RESEED, 2147483647) the next insert got 2,147,483,648.

The cost is the rewrite. With 1,000,000 rows in users and 1,000,000 in orders, in a database whose full backup was 82 MB, the whole script took 20.5 seconds and grew the used transaction log from 6 MB to 1,015 MB. A single ALTER COLUMN from INT to BIGINT on a 1,000,000-row table with no key on the column took 1.6 seconds and 365 MB of log on its own. The documentation says ALTER TABLE holds a schema modification (Sch-M) lock "to ensure that no other connections reference even the metadata for the table during the change", so nothing reads or writes either table until it commits. Multiply by every table that references users, and by the real row counts, and size the log file before you start. PK_users is clustered, the default for a primary key, so dropping it leaves users as a heap and adding it back sorts the whole table into a clustered index again. If the original key was NONCLUSTERED, say so in the ADD CONSTRAINT, or it comes back clustered.

Restate NOT NULL and COLLATE on every ALTER COLUMN

The same documentation hides a second trap: "if not specified, the column is nullable", and "if you don't specify a collation, the column is assigned the default collation of the database". On a NOT NULL column created as NVARCHAR(255) COLLATE Latin1_General_CS_AS, this statement succeeded:

ALTER TABLE users ALTER COLUMN email NVARCHAR(320);

and left email nullable and on the database default collation, in that container the case-insensitive SQL_Latin1_General_CP1_CI_AS. No error, no warning. For a key widening the rule is simple: every ALTER COLUMN says NOT NULL, and every string column says its COLLATE again.

Which way should you widen it?

The in-place script is not the only option. Each of the four trades something different:

Method	What changes	Measured here	Use it when
In place: drop keys, `ALTER COLUMN`, recreate	The key and every referencing column, for good	20.5 s and ~1 GB of log for 2 x 1,000,000 rows, both tables locked throughout	The tables fit in a maintenance window
Row compression first, then in place	The same, but the `ALTER COLUMN` stops rewriting rows	`ALTER COLUMN` took 0 ms and 0 MB of log; re-adding the clustered primary key still rebuilt the table in 1.0 s	The table is already row- or page-compressed, or a compression rebuild fits now
New `BIGINT` tables, copy across, rename	New tables swapped in with `sp_rename`, the key's and each large referencing table's	Not measured: the copies run in batches while the old tables keep serving	The tables are too big to lock for the length of a rewrite
Reseed to -2,147,483,648	Nothing in the schema	Instant	You need weeks, not hours, and every consumer accepts negative ids

Row compression stores integers in "only the bytes that are needed", according to Microsoft's row compression documentation, so INT and BIGINT share one on-disk format and the type change becomes a catalog edit. On a row-compressed 1,000,000-row table, the same ALTER COLUMN that took 1.6 seconds uncompressed took 0 ms and wrote no log. It does not lift Msg 5074: on a compressed table with the key on the column, the ALTER COLUMN was still refused until the primary key was dropped, and adding it back took 1.0 second. Compressing an existing table is itself a rebuild (0.36 seconds for 1,000,000 narrow rows here). Data compression is in every edition of SQL Server 2022, Express included; doing that rebuild ONLINE is what needs Enterprise.

A new table is the method for a table you cannot lock: create users_new with id BIGINT IDENTITY, copy rows across in batches with SET IDENTITY_INSERT users_new ON, keep the two in step with a trigger or a final catch-up pass, then swap the names with sp_rename in a short transaction. The foreign keys cannot simply be recreated against the new table: every referencing column is still INT, and Msg 1778 refuses a key across two types, so each large child table needs the same copy, or row compression, of its own before the keys come back. It is the most work and the only one where the long part runs while the application keeps writing.

Reseeding changes no column. Once the identity has overflowed, DBCC CHECKIDENT with RESEED, -2147483648 makes the next insert -2,147,483,647, because, as Microsoft's documentation for the command puts it, on a table with rows the next one "uses new_reseed_value + the current increment value". That buys another 2.1 billion inserts in the negative half. It is a stopgap: every API, client and report that assumed positive ids sees negative ones, and the ids stop sorting by age.

How Schemity plans the change and shows what it costs

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. After you connect to SQL Server, every key is drawn with its real type, so an INT key and the INT foreign key columns that point at it are visible together on the canvas, with the relationships between them.

Change users.id to BIGINT in the field editor and lint reports each foreign key column still on INT as "Foreign key type does not match the column it references", filed under the findings that fail at runtime with the detail that SQL Server refuses a foreign key whose column and key differ in type. The referencing columns are found by the diagram rather than by Msg 1778 at deploy time.

Change them too, and the planned schema migration is the in-place script above: it drops the foreign keys, the primary key and the indexes that use the columns, restates NOT NULL on every column that has it, keeps a non-default COLLATE on string columns, and creates everything again in dependency order. A VARCHAR, NVARCHAR or VARBINARY that only grows keeps its index, because SQL Server allows that change in place. A column renamed and retyped in the same save becomes one ALTER COLUMN plus sp_rename, and an identity added, removed or given a new step is refused with an explanation, because SQL Server can only change that by copying the table.

Before anything runs, impact analysis lets you see the impact of every change in the plan: "Rewrites users to change id" and "Rewrites orders to change user_id", each with the table's row estimate and size on disk, the lock each type change takes ("holds back reads and writes to users while it reads ~1M rows"), the index rebuild and the key checks that hold back writes, "Drops and recreates a foreign key on orders", and the primary key it adds back, with the duplicate check any new key gets. Those row estimates are the numbers to set against the 20.5 seconds and 1 GB above before you book the window.

INT to BIGINT on SQL Server, in one list

Find the tables at risk with sys.identity_columns before an insert fails with Msg 8115.
ALTER COLUMN on a key column fails with Msg 5074 and 4922 until every primary key, foreign key, unique, check, index and schema-bound view on it is dropped. ONLINE = ON does not change that.
In place means drop, alter, recreate, with every table locked and its rows rewritten. Budget the transaction log: 1 GB for two 1,000,000-row tables here.
Always restate NOT NULL and the column's COLLATE in ALTER COLUMN.
Row compression stops the ALTER COLUMN from rewriting rows; new tables avoid the long lock; a negative reseed only buys time.

The PostgreSQL version of this migration has its own catch, a serial sequence that stays integer after the column is widened, covered in serial vs identity in Postgres. MySQL's version fails on the foreign keys too, with error 3780, as shown in MySQL UNSIGNED primary keys, and how Schemity reads two real SQL Server schemas end to end is in the SQL Server round trip.

Round-Tripping TPC-DS and a Snowflake Feature Schema Through a Database Diagram: What Snowflake Lost and What We Fixed

Son Tran — Tue, 20 Oct 2026 14:53:37 +0000

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 Snowflake schemas through Schemity, TPC-DS as Snowflake ships it and a hand-written schema using Snowflake's own features, and rebuilt each in a fresh schema from the SQL Schemity wrote. Before this round of fixes the second script stopped partway, because a table with an expression clustering key could not be read; every clustering key and TRANSIENT was dropped, an identity lost its start and step, and TPC-DS's 17 single-column surrogate keys came back as AUTOINCREMENT. After the fixes TPC-DS comes back with no catalog difference, the second schema differs only in two comments, and all 14 behaviour probes match the originals.

A database diagram that reads a Snowflake schema and writes it back should give you the same schema, and before this round of fixes Schemity did not. We took two schemas, TPC-DS as Snowflake ships it in its sample database (24 tables) and a hand-written schema built from the Snowflake features real projects use (6 tables), imported each from Snowflake's own GET_DDL, and rebuilt it in a fresh schema from the SQL Schemity wrote. Snowflake stopped the second script partway through, and the first came back with every clustering key gone and 17 keys numbering themselves. After the fixes, the SQL Schemity writes runs on Snowflake as it is, with no edit, TPC-DS comes back with no catalog difference, the feature schema differs only in two comments, and both behave like their originals in 14 behaviour probes.

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 fifth and last 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, Bitwarden and Smartstore on SQL Server and Campfire and Jellyfin on SQLite. Reading a schema you did not write is how you understand your schema before changing it, and Snowflake tested the part of a table the other four never needed: how the warehouse stores it, not only what its columns are.

Which two schemas, and which path?

There is no open-source application whose schema is built for Snowflake the way Mastodon's is built for PostgreSQL, so this post uses two kinds instead. TPC-DS is the TPC's decision-support benchmark, the schema analytics engines are measured on, and Snowflake ships it with its keys declared. The second is ours: a billing and events model written to use what Snowflake projects do and the other four databases never needed.

	TPC-DS	Feature schema
What it is	The TPC's decision-support schema, from `SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL`	Customers, products, orders, order items, raw events and a daily rollup
Tables / columns	24 / 425	6 / 37
Keys	24 primary keys, 105 foreign keys	5 primary keys and 2 unique keys, 2 of them composite, and 3 foreign keys
Clustering keys	24, on a column	2, one on an expression: `CLUSTER BY (TO_DATE(ORDERED_AT))`
Also has	nothing else	an identity with `START 100 INCREMENT 5`, a sequence default, `COLLATE 'en-ci'`, a `TRANSIENT` table, `OBJECT` / `VARIANT` / `ARRAY`, `TIMESTAMP_LTZ(9)` / `TIMESTAMP_TZ(3)` / `TIMESTAMP_NTZ(6)`, `TIME(0)`, `BINARY(16)`, two comments

Snowflake refuses GET_DDL on a shared database such as its sample data, so TPC-DS was copied into an empty schema with CREATE TABLE ... LIKE, which keeps the columns and the clustering keys, and the sample's keys were added from its own SHOW PRIMARY KEYS and SHOW IMPORTED KEYS. Everything ran on a Snowflake trial account, version 10.37, on an X-Small warehouse.

Unlike the other four databases, Schemity has no live connection to Snowflake: a Snowflake diagram is designed from a script and its SQL is run by you. So unlike the other four posts there is one path:

Import SQL: GET_DDL of each original schema, read by Schemity's SQL parser and mapped into a diagram the way the app maps it, with no connection.

The diagram's SQL, as View SQL writes it, then ran in a fresh schema on the same account. The runs used Schemity's own code from a development build, called from scripts rather than through the app's window, and include the step the app takes when it turns a parsed table into a diagram. The earlier posts' scripts skipped it, and that step reordered every table's columns until the fix described in the SQLite post. The fixes described below are in that build and ship in the next release; until then, the released app has the losses in the "before" column.

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

The same two checks as the earlier posts, with one change Snowflake forces on the second.

The catalog diff. A script lists one line per fact for the original schema and for the copy, from INFORMATION_SCHEMA and Snowflake's SHOW commands: every table with whether it is transient, its clustering key and its comment; every column with its position, type, length or precision, nullability, default, identity start, step and order, collation and comment; every primary, unique and foreign key by its columns; every sequence with its start and step. The two lists are diffed line by line.

The behaviour probes. A probe is a few statements in one transaction that is rolled back, ending in a SELECT of what was stored or in Snowflake's error. On standard tables Snowflake records primary, unique and foreign keys but does not enforce them; it enforces NOT NULL and CHECK. So a probe here cannot ask which duplicate a copy refuses, as the earlier posts did. It asks what a value becomes: the ids an identity hands out, whether a case-insensitive collation finds a match, what a NUMBER(12,2) stores for 10.005, what a TIMESTAMP_TZ(3) keeps of 10:00:00.123456 +02:00, and whether a row that leaves out a key is refused.

Check	What it compares	What it catches
Catalog diff	Tables, columns, keys and sequences as Snowflake reports them	Anything missing, added or changed in the schema
Probes	What each schema stores, numbers and refuses	An identity, default, collation or type that behaves differently

There are 14 probes: 12 for the feature schema and 2 for TPC-DS. An identity or a sequence hands out its numbers even inside a rolled-back transaction, so each copy of the feature schema was rebuilt fresh before its probes ran; TPC-DS has neither.

What failed before the fixes?

Everything below is from Schemity's code just before these fixes (commit 3360cb61).

Snowflake stopped the feature schema's script partway. It created the first three tables and stopped at ORDER_ITEMS with:

002003 (42S02): SQL compilation error: Table 'SPIKE_DB.FEATURES_BEFORE.ORDERS' does not exist or not authorized.

Import SQL had not read ORDERS at all, so ORDER_ITEMS's foreign key named a table the script never created. With that one key removed by hand the script ran, and the copy could be compared.

	TPC-DS	Feature schema
Script ran	yes	stopped at the fourth table, error 002003; ran once patched
Tables lost	0	1, `ORDERS`
Clustering keys lost	24 of 24	2 of 2
`TRANSIENT` dropped	-	1 of 1
Keys given an identity they do not have	17	0
Identity start and step lost	-	1
Collation dropped	-	1
Catalog lines that differ	82	22
Probes that match the original	0 of 2	5 of 12

The probes that failed are the ones that matter to the data:

Probe	Original	Before the fixes	After
Feature schema: the first two customer ids	`100, 105`	`1, 2`	`100, 105`
Feature schema: `'A@X.COM'` finds `'a@x.com'`	1 row	0 rows	1 row
Feature schema: the 5 probes on `ORDERS`	stored or refused as declared	refused, no table	stored or refused as declared
TPC-DS: a row in `CALL_CENTER` or `DATE_DIM` without its key	refused, error 100072	accepted, key 1	refused

In the tables that were read, NOT NULL, every type, length, precision and scale, defaults, VARIANT, OBJECT and ARRAY, TIMESTAMP_LTZ(9) and TIMESTAMP_NTZ(6) with their precision, TIME(0), BINARY(16), every primary, unique and foreign key and the column order all survived; ORDERS, with its TIMESTAMP_TZ(3), its keys and its sequence default, was lost whole. Each failure had its own cause.

Why did a table with a clustering key disappear?

Because the clause was in a place the parser did not expect it. GET_DDL writes a table's clustering key between its name and its column list, create or replace TABLE ORDERS cluster by (TO_DATE(ORDERED_AT))(, and Snowflake allows an expression there, not only column names. The SQL parser Schemity uses reads only column names in that clause, so it refused the statement, and Import SQL listed ORDERS as a table it could not read. TPC-DS's clustering keys are plain columns, so its tables were read, but the clause was then dropped with nothing to hold it. Import SQL now takes CLUSTER BY (...) out of the statement wherever it stands, keeps its text exactly as written, since Snowflake stores the key's text and spacing, and the writer puts it back after the column list.

Why did a staging table become permanent?

Because TRANSIENT was read and then dropped. A transient table has no Fail-safe period and at most one day of Time Travel, so it costs less to keep and cannot be recovered once its Time Travel period of at most a day has passed; it is what raw and staging data is usually loaded into. The copy's RAW_EVENTS came back as a permanent table, with Fail-safe storage the original never paid for. The diagram now keeps TRANSIENT with the table's clustering key and writes CREATE TRANSIENT TABLE.

Why did TPC-DS's keys start numbering themselves?

Because a Snowflake diagram did not record identity, and the writer guessed. It gave AUTOINCREMENT to every single-column whole-number primary key, which is right for a table drawn in the editor and wrong for 17 of TPC-DS's 24 tables, whose surrogate keys come with the data being loaded; the other 7, the sales, returns and inventory tables, have composite keys. A row that leaves CC_CALL_CENTER_SK out is refused by the original:

100072 (22000): DML operation to table CALL_CENTER failed on column CC_CALL_CENTER_SK with error: NULL result in a non-nullable column

The copy accepted it and gave it key 1, which the next load's real key 1 would then duplicate, and Snowflake would not refuse that either. The same missing record lost the feature schema's ID NUMBER(38,0) autoincrement start 100 increment 5 noorder: written back as plain AUTOINCREMENT, which starts at 1 and steps by 1, it numbered the first customers 1 and 2 where the original numbered them 100 and 105 (a NOORDER identity does not promise consecutive values, but it starts where it is told). The diagram now records a Snowflake column's identity as declared, its start, step and ORDER or NOORDER, and records that a whole-number column has none, as it already did for PostgreSQL and SQL Server. Editing such a field in the editor keeps it.

Why did a case-insensitive email stop matching?

Because the column's collation was read and not written. EMAIL VARCHAR(255) COLLATE 'en-ci' compares without regard to case, so a lookup for 'A@X.COM' finds 'a@x.com'; the copy's plain VARCHAR(255) compared case and found nothing, which is the difference between a login that works and one that does not. The writer now writes a Snowflake column's COLLATE as declared.

What happened to the sequence?

It was skipped, and the default that drew on it had two more problems. ORDERS.ID defaults to ORDER_SEQ.NEXTVAL, a sequence that starts at 1000 and steps by 10, and GET_DDL writes that default with the original's database and schema, SPIKE_DB.FEATURES_ORIG.ORDER_SEQ.NEXTVAL, so a copy elsewhere would draw on the original's sequence. And the writer, which ORDERS never reached here, wrote such a default in quotes, as the text 'ORDER_SEQ.NEXTVAL'; the core now has a test for it. Import SQL now keeps each sequence the script creates as setup SQL, which the diagram's SQL runs first as CREATE SEQUENCE IF NOT EXISTS with the script's own start and step; a default reads as the sequence alone; and the writer leaves a NEXTVAL call unquoted. The copy's first order is 1000, as the original's.

What does the round trip give you now?

The same schema, for both, apart from two comments, from SQL that ran on Snowflake as Schemity wrote it. Neither script needed an edit or raised an error: TPC-DS's 24 CREATE TABLE statements built all 24 tables, 425 columns, 24 primary keys and 105 foreign keys, and the feature schema's one CREATE SEQUENCE and six CREATE TABLE statements built its sequence, all 6 tables and every key.

	TPC-DS	Feature schema
Catalog differences	none	the table's comment and a column's comment
Probes that match the original	2 of 2	12 of 12

All 24 of TPC-DS's clustering keys and its 17 surrogate keys without an identity come back as they were, as do the feature schema's expression clustering key, its transient table, its identity starting at 100 by 5, its case-insensitive collation and its sequence starting at 1000 by 10. The catalog includes each column's position, and every table comes back in its declared column order.

How do you see relationships in a Snowflake schema with no foreign keys?

By reading them from the column names. Snowflake does not enforce a FOREIGN KEY on a standard table, as the probes above rely on, so nothing stops a row that points nowhere, and plenty of Snowflake schemas declare no foreign keys at all. Raw landing tables and models built by ELT tools are the usual cases. Imported as written, such a schema is a grid of tables with no lines between them.

The feature schema has one: RAW_EVENTS.CUSTOMER_ID holds a customer's id, but the transient landing table declares no key for it. With Infer virtual relations from column names ticked in Import SQL, Schemity matched CUSTOMER_ID to CUSTOMERS and its primary key ID, and drew a dashed relation, zero-or-one at the customer end because the column allows NULL. A virtual relation lives only in the diagram. The SQL Schemity writes leaves it out, so the round trip above is unchanged, and the diagram still shows where the data joins.

What still differs, and why?

Comments. The feature schema's table comment and its column comment on LIFETIME_VALUE are read into the diagram as descriptions, and Schemity does not write descriptions to any database: they are the diagram's own notes, on Snowflake as on the other four. A copy carries the columns and keys, not the documentation, which stays in the diagram file.

What this benchmark does not show

The numbers through the app's window. The catalog diffs and probes came from scripts calling Schemity's import, mapping and writer code on a development build. The screenshots are that build's app, importing the same GET_DDL scripts, and the SQL file it exported for each diagram also ran by hand in a Snowflake worksheet with no error, building all 24 TPC-DS tables and all 6 of the feature schema's.
A live connection. Schemity has none to Snowflake; a schema comes in through Import SQL of GET_DDL, and the diagram's SQL is run by you.
Data. The originals are empty apart from the probes' rolled-back rows. A schema that matches is the precondition for loading data, not proof that a load is safe.
CHECK constraints, dynamic tables, views and stages. Neither schema has a CHECK; Snowflake enforces them, so they deserve their own test. Dynamic tables and views are not tables a diagram designs.
Other databases. The other four have their own posts, linked above, and this is the last in the series.

How to rerun it

Build each original in an empty schema, take SELECT GET_DDL('schema', '<db>.<schema>') as the script, put it through Schemity's Import SQL, take the diagram's SQL from View SQL and run it in another empty schema. Then list both catalogs with this query, a shorter form of the benchmark's, and diff the output. It leaves out keys, sequences and comments, which the benchmark reads from SHOW PRIMARY KEYS, SHOW IMPORTED KEYS and SHOW SEQUENCES:

SELECT line FROM (
  SELECT 'table | ' || table_name || ' | ' || is_transient || ' | ' || coalesce(clustering_key, 'no clustering key') AS line
    FROM information_schema.tables
   WHERE table_schema = CURRENT_SCHEMA() AND table_type = 'BASE TABLE'
  UNION ALL
  SELECT 'column | ' || table_name || ' | ' || ordinal_position || ' | ' || column_name || ' | ' || data_type
         || ' | ' || coalesce(to_varchar(character_maximum_length), to_varchar(numeric_precision) || ',' || to_varchar(numeric_scale), to_varchar(datetime_precision), '')
         || ' | ' || is_nullable
         -- A default drawing on a sequence names the schema it is in.
         || ' | ' || coalesce(replace(column_default, current_database() || '.' || current_schema() || '.', ''), 'no default')
         || ' | identity ' || coalesce(identity_start || ',' || identity_increment || ',' || identity_ordered, 'none')
         || ' | ' || coalesce(collation_name, 'no collation')
    FROM information_schema.columns
   WHERE table_schema = CURRENT_SCHEMA()
) ORDER BY line;

For behaviour, run each probe between BEGIN and ROLLBACK, end it with a SELECT of what was stored, and rebuild the schema before running the probes again: an identity and a sequence keep the numbers a rolled-back transaction drew.

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

Son Tran — Tue, 20 Oct 2026 14:53:07 +0000

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.

	Campfire v1.5.2	Jellyfin 12.2
What it is	Group chat	Media server
Built by	Rails 8 migrations, `bin/rails db:prepare`	Entity Framework Core 10 migrations, on first start
Tables	23: 17 of its own, an FTS5 virtual table and the 5 tables SQLite keeps its data in	33
Foreign keys	10	28, 25 of them `ON DELETE CASCADE`
`AUTOINCREMENT` keys	15	12, and one plain `INTEGER PRIMARY KEY`
Declared types beyond SQLite's own	33 `varchar`, 1 `varchar(16)`, 31 `datetime(6)`, 6 `bigint`, 1 `json`	none
Partial / expression indexes	0 / 0	4 / 1

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.

Check	What it compares	What it catches
Catalog diff	Tables, columns, keys, indexes, as SQLite reports them	Anything missing, added or changed in the schema
Probes	What each file accepts, refuses, stores and how it plans a query	A key, index or affinity that behaves differently
Rails runner	What the application reads from the schema	A declared type the application reads differently

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.

	Campfire, Import SQL	Campfire, live	Jellyfin, Import SQL	Jellyfin, live
Catalog lines that differ	198	200	37	37
Declared types written as an affinity name	72	72	0	0
`INTEGER PRIMARY KEY` without its `NOT NULL` (spelling only)	15	15	13	13
FTS5 table and its data tables	data tables read as Campfire's	all 6 read as plain tables	-	-
Partial indexes that lost `WHERE`	-	-	4 of 4	4 of 4
Expression index	-	-	left out	left out
`AUTOINCREMENT` added where there was none	0	0	1	1
Probes that match the original	10 of 10	10 of 10	5 of 8	5 of 8
Campfire's `Account.create!`	fails	fails	-	-

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

Check	Original	Before the fixes	After
Campfire: Rails reads `accounts.created_at` as	`datetime`, precision 6	`decimal`	`datetime`, precision 6
Campfire: `Account.create!`	creates the account	`NoMethodError`	creates the account
Jellyfin: plan for `WHERE lower(Name) = 'x'`	`SEARCH Peoples USING INDEX IX_Peoples_NameLower`	`SCAN Peoples`	the original's
Jellyfin: plan for `WHERE OwnerId IS NULL`	`SCAN BaseItems`	`SEARCH ... USING INDEX IX_BaseItems_OwnerId`	the original's
Jellyfin: a deleted migration lock's id	handed out again: 1	not reused: 2	1

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.

	Import SQL	Live connection
Campfire: catalog differences	the FTS5 table and its 5 data tables	the same
Campfire: probes that match	10 of 10	10 of 10
Campfire: Rails types and `Account.create!`	as the original	as the original
Jellyfin: catalog differences	none	none
Jellyfin: probes that match	8 of 8	8 of 8

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.

Round-Tripping Bitwarden and Smartstore Through a Database Diagram: What SQL Server Lost and What We Fixed

Son Tran — Tue, 20 Oct 2026 14:52:31 +0000

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 SQL Server schemas, Bitwarden (a SQL Server database project) and Smartstore (Entity Framework Core), through Schemity and rebuilt a database from the SQL it wrote. Before this round of fixes Bitwarden's script failed, varchar came back as nvarchar, the live path turned tinyint, money and datetime into wider types, and filtered indexes lost their WHERE, so a copy refused rows the original accepts. After the fixes Smartstore comes back with no difference, Bitwarden differs only in two foreign keys created WITH NOCHECK, and 21 behaviour probes match.

A database diagram that reads a SQL Server schema and writes it back should give you the same database, and before this round of fixes Schemity did not. We took two real open-source schemas, Bitwarden server v2026.10.0 (a SQL Server database project, 64 tables) and Smartstore 6.4.0 (Entity Framework Core, 118 tables), put each through Schemity from a script and from the live database, and rebuilt a database from the SQL Schemity wrote. Bitwarden's script failed, every varchar came back as nvarchar, and the copies accepted and refused different rows from the originals. After the fixes, Smartstore comes back with no catalog difference at all on either path, Bitwarden differs only in two foreign keys it created WITH NOCHECK, and both behave exactly like their originals in 21 behaviour probes.

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 third post in a series that tests the round trip on each database it supports, after Mastodon and Saleor on PostgreSQL and Invoice Ninja and Sylius on MySQL. Reading a schema you did not write is how you understand your schema before changing it, and SQL Server tested the parts of the diagram the other two never reached: its own type names, and indexes that carry more than their key columns.

Which two schemas, and which two paths?

Two schemas built two different ways. Bitwarden's comes from a SQL Server database project (Sql.sqlproj) applied by its own migrator, so it is hand-written T-SQL with procedures and views. Smartstore's comes from its installer, which runs on Entity Framework Core with FluentMigrator.

	Bitwarden server v2026.10.0	Smartstore 6.4.0
What it is	Password manager	E-commerce platform
Built by	SQL Server database project, Bitwarden's migrator	Entity Framework Core and FluentMigrator, the web installer
Tables	64	118
Columns	705	1,225
Foreign keys	88: 43 `ON DELETE CASCADE`, 2 `ON DELETE SET NULL`, 2 `WITH NOCHECK`	122: 86 `ON DELETE CASCADE`, 20 `ON DELETE SET NULL`
Indexes besides primary keys	117	260
Filtered (`WHERE`)	22, 3 of them unique	5, all unique
Covering (`INCLUDE`)	19	4
With a `DESC` key	6	0
Keys	mostly `uniqueidentifier`, 4 `IDENTITY`	104 `IDENTITY`
`varchar` / `tinyint` / `money` / `datetime`	75 / 47 / 3 / 3	0 / 0 / 0 / 1
`CHECK` / defaults / unique constraints	1 / 40 / 5	none
Also has	61 views, 574 procedures, 3 functions, 5 table types	nothing

SQL Server has no pg_dump or mysqldump. The script for the Import SQL path came from sqlpackage, Microsoft's command-line tool, with /a:Extract /p:ExtractTarget=File, which writes the whole schema as one .sql file. Each schema then went through Schemity on two paths:

Import SQL: that script, read by Schemity's SQL parser, with no connection.
Live connection: Schemity's SQL Server reader connected to the original database and read its catalog views.

Both paths end with the diagram's SQL applied to an empty database on the same SQL Server 2022 server. 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 same type and index losses as the "before" column.

How do you prove a rebuilt SQL Server schema is the same?

The same two checks as the MySQL post.

The catalog diff. A query over SQL Server's catalog views (sys.columns, sys.indexes, sys.foreign_keys and the rest) lists one line per fact for the original and for the copy: every column with its type, length or precision, nullability, default, identity and collation; every primary key, unique constraint and index with its keys in order, sort direction, INCLUDE columns, filter and clustering; every foreign key with its actions and whether SQL Server trusts it; every CHECK. The two lists are diffed line by line.

The behaviour probes. A probe is a few statements in one transaction that is rolled back: insert 256 into a tinyint, insert two rows with no order into a filtered unique column, delete a group and count its members. Its outcome is "accepted" or "refused at step N" with SQL Server's message number, and where it ends with a SELECT, the value read back is part of the outcome. A foreign key a probe is not about is switched off with NOCHECK inside the transaction, which the rollback undoes.

Check	What it compares	What it catches
Catalog diff	Columns, keys, indexes, foreign keys, as SQL Server's catalog reports them	Anything missing, added or changed in the schema
Probes, accept or refuse	Which rows each database accepts	A type range, unique index or foreign key that behaves differently
Probes, read back	What each database stored	A value rounded or encoded differently

The 21 probes are 12 for Bitwarden and 9 for Smartstore. Smartstore's installer seeds rows in 35 tables and Bitwarden's migrator logs its scripts in one, which the copies do not have; every probe uses rows of its own.

What failed before the fixes?

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

Three of the four scripts failed under sqlcmd. Bitwarden's Import SQL script stopped at:

Msg 8166, Level 16, State 1, Line 983
Constraint name '#Cols_pkey' not permitted. Constraint names cannot begin with a number sign (#).

Both live-path scripts stopped at the first filtered index with Msg 1934, CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Smartstore's Import SQL script ran only because its filtered indexes had already lost their filters. With the phantom table removed by hand and the scripts run with QUOTED_IDENTIFIER ON, as the app's own connection has it, all four copies could be compared.

	Bitwarden, Import SQL	Bitwarden, live	Smartstore, Import SQL	Smartstore, live
Script ran	no, Msg 8166	no, Msg 1934	yes	no, Msg 1934
`varchar` written as `nvarchar`	75	75	0	0
`tinyint` / `money` / `datetime` widened	0	47 / 3 / 3	0	0 / 0 / 1
Filtered indexes changed or dropped	22 of 22	20 of 22	5 of 5	0 of 5
Covering indexes without `INCLUDE`	19 of 19	19 of 19	4 of 4	4 of 4
Indexes that lost a `DESC` key	6	6	0	0
Clustered index made nonclustered	0	0	1	1
Probes that match the original	10 of 12	7 of 12	8 of 9	8 of 9

The probes that failed are the ones that matter to the data:

Probe	Original	Before the fixes	After
Bitwarden: `gw-Ā` into a `varchar(50)`	stores `gw-A`	stores `gw-Ā`	stores `gw-A`
Bitwarden: 256 into a `tinyint`	refused, Msg 220	accepted (live)	refused
Bitwarden: an amount above `money`'s range	refused, Msg 8115	accepted (live)	refused
Bitwarden and Smartstore: `10:00:00.001` into a `datetime`	stores `.000`	stores `.0010000` (live)	stores `.000`
Bitwarden: two active tax rates for one postal code	refused, Msg 2601	accepted (Import SQL)	refused
Bitwarden: two inactive tax rates for one postal code	accepted	refused (live)	accepted
Smartstore: two reward point records with no order	accepted	refused (Import SQL)	accepted

Cascades on delete, the CHECK, defaults, IDENTITY, case-insensitive uniqueness and foreign keys all survived. Each failure had its own cause.

Why did Import SQL read a procedure as a table?

Because a script splits on more than GO. A sqlpackage or SSMS script separates batches with GO, and a procedure is one batch, but its body holds statements of its own, ended by semicolons. Bitwarden has 574 procedures, and one of them contains CREATE TABLE #Cols (...), a temporary table. Split at its semicolons, the body became separate statements, and that one was read as a table. Import SQL now keeps a CREATE PROCEDURE, FUNCTION, TRIGGER or VIEW batch whole and skips it, and names the 61 views it skipped. A diagram holds tables; a live connection reads the views as read-only tables.

Why did the script fail under sqlcmd?

Because a filtered index needs a session setting that sqlcmd leaves off. SQL Server refuses to create a filtered index unless QUOTED_IDENTIFIER is ON, and sqlcmd's own documentation says its default is OFF unless you pass -I. SSMS connects with it on and its Generate Scripts writes it before each table; the script sqlpackage extracted for this test carries no SET at all, so it fails under sqlcmd the same way. Schemity's SQL Server scripts now start with SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON, so View SQL's script runs the same in sqlcmd as in SSMS.

Why is varchar not nvarchar?

Because they store different things. A varchar holds one byte a character in the column's code page, here SQL_Latin1_General_CP1_CI_AS; an nvarchar holds Unicode at two bytes a character. The probe shows the difference: Ā is not in that code page, so the original's varchar(50) stored gw-Ā as gw-A, while the copy, with nvarchar(50), kept gw-Ā. The copy also doubled the bytes of 75 columns, and an index on one of them counts the doubled size against SQL Server's limit on an index key.

Schemity's SQL Server support used a reduced type list, and both paths folded a declared type into a wider one. The live reader read varchar and char as nvarchar and nchar, tinyint as smallint, money as decimal, and datetime and smalldatetime as datetime2. Each fold changes a value or a range:

tinyint holds 0 to 255, so the original refuses 256 with Msg 220; smallint takes it.
money stops at 922,337,203,685,477.5807; decimal(19,4) goes higher, so the copy accepted an amount the original refuses with Msg 8115.
datetime rounds to increments of .000, .003 or .007 seconds, so 10:00:00.001 is stored as 10:00:00.000; datetime2(7) keeps it.

The diagram now keeps each type as SQL Server declares it on both paths, and the field editor offers SQL Server's own types: varchar, char, varchar(max), tinyint, money, smallmoney, datetime, smalldatetime, real, binary, varbinary and xml beside the ones it had. A diagram drawn for another database still converts its text columns to nvarchar.

What happens to a filtered unique index?

It depends on its filter, and before the fixes both readings were wrong in a different way. On SQL Server a unique index treats NULL as a value, so it allows one row without a value. A unique index filtered to WHERE col IS NOT NULL is how SQL Server writes the standard rule, many NULLs allowed, and Smartstore has five of them, among them IX_RewardPointsHistory_UsedWithOrder_Id. Import SQL dropped the filter, so the copy refused the second reward point record with no order. It now reads such an index as a unique rule that ignores NULL, as the live reader already did, and writes it back as the same filtered index.

Bitwarden's IX_TaxRate_Country_PostalCode_Active_Uniqueness is the other kind: unique WHERE ([Active]=(1)), one active rate per postal code. Import SQL left it out entirely, so the copy accepted two active rates; the live reader dropped its filter, so the copy refused two inactive ones. The diagram now keeps a SQL Server index's filter as its WHERE, as it keeps a PostgreSQL partial index's.

What about INCLUDE, DESC and clustered indexes?

They change speed, not rows, and they were all lost. A covering index's INCLUDE columns, a key sorted DESC and a CLUSTERED index that is not the primary key are now kept on both paths and written back; a table whose clustered index is another index gets its primary key written NONCLUSTERED, since SQL Server allows one clustered index a table. Neither schema needed that last one: Smartstore's clustered unique index is on a table with no primary key. INCLUDE turned up a PostgreSQL bug on the way: its live reader read a covering index's INCLUDE columns as key columns. PostgreSQL indexes keep INCLUDE now too.

Which bugs did only a refresh find?

Import a script, connect to the database it came from, and Refresh & merge should find nothing to change. Measured through the app's own merge code, called from a script, it planned 113 statements for Bitwarden and 459 for Smartstore, all spelling: INTEGER from the script against INT from the reader, a CHECK that SQL Server stores in its own lowercase form, INCLUDE columns in another order, and names in brackets against names without. Both refreshes now list nothing, and so do the four MySQL and PostgreSQL schemas through the same check.

The same holds in the app's window the other way round: Import SQL of Bitwarden's script onto the diagram read from its live database finds nothing to change.

Two more turned up while this post was being checked, in schemas neither app has. A script's INT primary key without IDENTITY, the shape of a lookup table with fixed ids, came back as IDENTITY(1,1), so the copy refused the ids the application inserts; the diagram now records whether SQL Server numbers a column, on both paths. And Import SQL wrote a deprecated text column as nvarchar(max), the same widening as above; it now reads text, ntext and image as varchar(max), nvarchar(max) and varbinary(max), as the live reader does.

One more turned up later, while taking the SQLite post's screenshots, which the scripts here 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, so a copy built from the diagram met a SELECT * or an INSERT without a column list with its columns in another order. 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, 62 of Bitwarden's 64 tables and 96 of Smartstore's 118 came back in a different column order before this fix.

What does the round trip give you now?

The same database, on both paths, for both schemas.

	Import SQL	Live connection
Bitwarden: catalog differences	2 foreign keys `WITH NOCHECK`	the same
Bitwarden: probes that match the original	12 of 12	12 of 12
Smartstore: catalog differences	none	none
Smartstore: probes that match the original	9 of 9	9 of 9

Every one of the 75 varchar, 47 tinyint, 3 money and 4 datetime columns, the 27 filtered indexes, 23 covering indexes, 6 indexes with a DESC key and Smartstore's clustered unique index come back as they were, and the scripts run under sqlcmd without -I.

What still differs, and why?

Two foreign keys SQL Server does not trust. Bitwarden's FK_EmergencyAccess_GranteeId and FK_EmergencyAccess_GrantorId were created WITH NOCHECK, so SQL Server did not check the rows already in the table when they were added and marks them not trusted. Both copies create them as ordinary, trusted keys. Every new row is checked either way, and an empty copy has no old rows to skip, so the diagram does not record it.

Objects a diagram does not hold. Bitwarden's 574 procedures, 3 functions and 5 table types are skipped by Import SQL, and its 61 views are skipped by name; a live connection reads the views as read-only tables. None of them is written back. SQL Server's deprecated text, ntext and image types are written as varchar(max), nvarchar(max) and varbinary(max) on both paths; neither schema uses them. An IDENTITY column comes back with seed 1 and increment 1, which every identity in both schemas has.

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.
Data. The originals hold only their seed rows. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
Triggers. Neither schema has one.
Azure SQL and older servers. Everything ran on SQL Server 2022 CU26.
Other databases. SQLite has its own post, with Campfire and Jellyfin, and so has Snowflake, with TPC-DS.
Schemas this size are not a stress test. 64 and 118 tables are typical; a thousand-table schema is covered in a separate post.

How to rerun it

Create each schema with its own tooling against an empty SQL Server, take sqlpackage /a:Extract /SourceConnectionString:<connection> /TargetFile:<schema>.sql /p:ExtractTarget=File, put the script or the database through Schemity, export the SQL and apply it to an empty database. Then list both catalogs with a query like this one, a shorter form of the benchmark's, run with sqlcmd -d <database> -h -1 -y 8000, and diff the output:

SET NOCOUNT ON;
WITH cols AS (
  SELECT CONCAT('column | ', t.name, ' | ', c.name, ' | ', ty.name,
      CASE
        WHEN ty.name IN ('nvarchar', 'nchar') THEN IIF(c.max_length = -1, '(max)', CONCAT('(', c.max_length / 2, ')'))
        WHEN ty.name IN ('varchar', 'char', 'varbinary', 'binary') THEN IIF(c.max_length = -1, '(max)', CONCAT('(', c.max_length, ')'))
        WHEN ty.name IN ('decimal', 'numeric') THEN CONCAT('(', c.precision, ',', c.scale, ')')
        WHEN ty.name IN ('datetime2', 'datetimeoffset', 'time') THEN CONCAT('(', c.scale, ')')
        ELSE '' END,
      IIF(c.is_nullable = 1, ' | null', ' | not null'),
      ' | ', ISNULL('default ' + dc.definition, 'no default'),
      ' | ', IIF(ic.column_id IS NULL, '-', CONCAT('identity(', CONVERT(varchar(40), ic.seed_value), ',', CONVERT(varchar(40), ic.increment_value), ')')),
      ' | ', ISNULL(c.collation_name, '-')) AS line
  FROM sys.tables t
  JOIN sys.columns c ON c.object_id = t.object_id
  JOIN sys.types ty ON ty.user_type_id = c.user_type_id
  LEFT JOIN sys.default_constraints dc ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
  LEFT JOIN sys.identity_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
),
idx AS (
  SELECT CONCAT(IIF(i.is_primary_key = 1, 'pk', IIF(i.is_unique_constraint = 1, 'unique constraint', 'index')),
      ' | ', t.name, ' | ', i.name, ' | ', IIF(i.is_unique = 1, 'unique', 'non-unique'), ' | ', LOWER(i.type_desc), ' | ',
      (SELECT STRING_AGG(CONCAT(c.name, IIF(ic.is_descending_key = 1, ' desc', '')), ', ') WITHIN GROUP (ORDER BY ic.key_ordinal)
         FROM sys.index_columns ic JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
         WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0),
      ISNULL(' | include ' + (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY c.name)
         FROM sys.index_columns ic JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
         WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1), ''),
      ISNULL(' | where ' + i.filter_definition, '')) AS line
  FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id
  WHERE i.type > 0
),
fks AS (
  SELECT CONCAT('fk | ', t.name, ' | ', fk.name, ' | ',
      (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY fc.constraint_column_id)
         FROM sys.foreign_key_columns fc JOIN sys.columns c ON c.object_id = fc.parent_object_id AND c.column_id = fc.parent_column_id
         WHERE fc.constraint_object_id = fk.object_id),
      ' | ', OBJECT_NAME(fk.referenced_object_id),
      ' | on delete ', LOWER(fk.delete_referential_action_desc), ' | on update ', LOWER(fk.update_referential_action_desc),
      IIF(fk.is_not_trusted = 1, ' | not trusted', '')) AS line
  FROM sys.foreign_keys fk JOIN sys.tables t ON t.object_id = fk.parent_object_id
)
SELECT line FROM (
  SELECT line COLLATE Latin1_General_BIN2 AS line FROM cols
  UNION ALL SELECT line COLLATE Latin1_General_BIN2 FROM idx
  UNION ALL SELECT line COLLATE Latin1_General_BIN2 FROM fks
  UNION ALL SELECT CONCAT('check | ', t.name, ' | ', ck.name, ' | ', ck.definition) COLLATE Latin1_General_BIN2
    FROM sys.check_constraints ck JOIN sys.tables t ON t.object_id = ck.parent_object_id
) catalog ORDER BY line;

For behaviour, run a few statements per rule inside BEGIN TRANSACTION and ROLLBACK on the original and on the copy, end each with a SELECT of what was stored, and compare both the step SQL Server refuses at and the values it reads back. Connect with QUOTED_IDENTIFIER ON, or SQL Server refuses every write to a table with a filtered index before your probe gets to its point.

Round-Tripping Invoice Ninja and Sylius Through a Database Diagram: What MySQL Lost and What We Fixed

Son Tran — Tue, 20 Oct 2026 14:52:02 +0000

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 MySQL schemas, Invoice Ninja (Laravel) and Sylius (Symfony with Doctrine), through Schemity and rebuilt a database from the SQL it wrote. Before this round of fixes MySQL refused Invoice Ninja's script, and once patched the copies built from the dumps dropped microseconds, stopped rounding a double(8,2) and accepted duplicate keys the originals refuse. After the fixes Invoice Ninja comes back with no difference at all, Sylius differs only in 16 column comments Doctrine no longer uses, and both behave the same in 19 probes.

A database diagram that reads a MySQL schema and writes it back should give you the same database, and before this round of fixes Schemity did not. We took two real open-source schemas, Invoice Ninja v5.13.47 (Laravel, 80 tables) and Sylius v2.3.0 (Symfony with Doctrine, 115 tables), put each through Schemity from its mysqldump and from the live database, and rebuilt a database from the SQL Schemity wrote. MySQL refused Invoice Ninja's script outright, and once it was patched to run, the copies built from both dumps stored different values and accepted rows the originals refuse. After the fixes, Invoice Ninja comes back with no catalog difference at all on either path, Sylius differs only in 16 column comments, and both behave exactly like their originals in 19 behaviour probes.

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 second post in a series that tests the round trip on each database it supports; the first, round-tripping Mastodon and Saleor, did the same for PostgreSQL. Reading a schema you did not write is how you understand your schema before changing it, and MySQL turned out to lose different things from PostgreSQL: not keys and indexes, but the column details that decide what a value is.

Which two schemas, and which two paths?

Two schemas created by their own frameworks, chosen because both declare real foreign keys. Not every MySQL application does: WordPress's own CREATE TABLE statements have none, and a round trip of a schema without them would prove little.

	Invoice Ninja v5.13.47	Sylius v2.3.0
What it is	Invoicing and billing	E-commerce platform
Created by	Laravel, `php artisan migrate`	Symfony with Doctrine, `doctrine:migrations:migrate`
Tables	80	115
Columns	1,727	738
Foreign keys	138: 127 `ON DELETE CASCADE`, 114 `ON UPDATE CASCADE`	141: 71 `ON DELETE CASCADE`, 10 `ON DELETE SET NULL`
Unique keys	34	78
`UNSIGNED` columns	437	0
`timestamp(6)` columns	141	0
`tinyint(1)` flags	224	40
Indexes on a column prefix	3	0
`ENUM` / `JSON` columns	13 / 2	0 / 15
Collation	`utf8mb4_unicode_ci` throughout	`utf8mb4_unicode_ci`, one table `utf8mb4_0900_ai_ci`
Column comments	0	16
Views, triggers, routines, `CHECK`	none	none

Each schema was created by its framework in an empty MySQL 8.4.11 database, Invoice Ninja inside the official invoiceninja/invoiceninja-debian:5.13.47 image and Sylius from sylius/sylius-standard v2.3.0, and then went through Schemity on two paths:

Import SQL: the mysqldump --no-data file read by Schemity's SQL parser, with no connection.
Live connection: Schemity's MySQL reader connected to the original database and read information_schema.

Both paths end the same way: the diagram's SQL is applied to an empty database on the same server. The runs used Schemity's own code from a development build, called from scripts rather than through the app's window, and the live path always read the original database, never a copy Schemity had built. The fixes described below are in that build and ship in the next release; until then, the released app behaves like the "before" column.

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

Two checks, as in the PostgreSQL post, with one addition MySQL needs.

The catalog diff. A query over information_schema lists one line per fact for the original and for the copy: every table with its engine and collation, every column with its full column_type, nullability, default, extra (auto_increment, on update), collation and comment, every index with its prefix lengths, and every foreign key with its actions. The two lists are diffed line by line.

The behaviour probes. A probe is a few statements in one transaction that is rolled back: insert a row with a microsecond timestamp, insert two company keys that differ only by a trailing space, delete an account and count its companies. Its outcome is "accepted" or "refused at step N" with MySQL's error code, and a copy passes when its outcome is the original's.

The addition is reading back. On MySQL a column that lost a detail usually does not refuse anything: it accepts the row and stores something else. So a probe can end with a SELECT, and the value it reads is part of the outcome.

Check	What it compares	What it catches
Catalog diff	Tables, columns, indexes and foreign keys, as `information_schema` reports them	Anything missing, added or changed in the schema
Probes, accept or refuse	Which rows each database accepts	A unique key or foreign key that behaves differently
Probes, read back	What each database stored	A value rounded or defaulted differently

The 19 probes are 11 for Invoice Ninja and 8 for Sylius, run under MySQL 8.4's default strict sql_mode, which is what makes an out-of-range or too-long value an error rather than a warning. Invoice Ninja's migrations seed a few reference tables, 21 currencies among them, which the copies do not have; every probe uses rows and ids of its own, so no seed row decides an outcome.

What failed before the fixes?

Everything below is from Schemity as of 15 October 2026, through Import SQL, the same scope as the PostgreSQL post's "before" column. The live path was already better: Schemity's MySQL reader kept every collation, and Sylius came back from it differing only in its comments. It refused Invoice Ninja's script at the same index.

MySQL refused Invoice Ninja's script. It stopped at line 492 with:

ERROR 1170 (42000): BLOB/TEXT column 'client_hash' used in key specification without a key length

With the three prefix indexes put back by hand, the script ran, and both copies could be compared with their originals.

	Invoice Ninja	Sylius
Script ran	no, error 1170, ran once patched	yes
Columns that lost their collation	593	258
Tables that lost their collation	all 80	114 of 115
`timestamp(6)` written as `timestamp`	141	0
`double(8,2)` written as `double`	2	0
Column comments lost	0	16
Probes that match the original	8 of 11	7 of 8

The four probes that failed are the four that matter to the data:

Probe	Original	Before the fixes	After
Invoice Ninja: a timestamp set to `10:00:00.123456`	stores `.123456`	stores `10:00:00`	stores `.123456`
Invoice Ninja: a discount of `1.239` in a `double(8,2)`	stores `1.24`	stores `1.239`	stores `1.24`
Invoice Ninja: two company keys differing only by a trailing space	refused, duplicate	accepted	refused
Sylius: two customer emails differing only by a trailing space	refused, duplicate	accepted	refused

Every other probe passed even before the fixes: cascades on delete and update, SET NULL, foreign keys, UNSIGNED, ENUM, JSON validity and column lengths all survived. Each failure had its own cause.

Why did MySQL refuse an index on a TEXT column?

Because MySQL can only index a TEXT column by a prefix of it. Invoice Ninja declares KEY (client_hash(20)) on a TEXT column, and two more on contact_key. The diagram held an index as a list of column names, so the 20 was dropped on both paths and the script said CREATE INDEX ... (client_hash), which MySQL refuses. The diagram now keeps a column prefix as the index's key list, `client_hash`(20), written back as it was, and the table's index list shows it. Converting such a diagram to another database writes a non-unique prefix index as a plain index on the whole column, and leaves a unique one out, named in the script's notes, because uniqueness on the first 20 characters is a different rule from uniqueness on all of them.

Why did keys that differ by a trailing space stop being duplicates?

Because the copies lost their collation, and MySQL 8's default compares trailing spaces differently. Every text column in Invoice Ninja, and all but one in Sylius, is utf8mb4_unicode_ci, which has the PAD SPACE attribute: trailing spaces are ignored, so 'probe-key' and 'probe-key ' are the same value and a unique key refuses the second. Import SQL read no character set or collation from a dump, so the copies took the server default, utf8mb4_0900_ai_ci, which is NO PAD: the two strings differ, and the duplicate goes in. MySQL's documentation on the pad attribute has the rule.

mysqldump makes this easy to miss, because it spells a collation four ways. A table's is DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. A column that takes its table's gets a bare COLLATE utf8mb4_unicode_ci, with no character set, which is how all 593 of Invoice Ninja's text columns appear. A column set on its own gets CHARACTER SET utf8mb4 COLLATE .... And a column whose collation is its character set's default, as on Sylius's one utf8mb4_0900_ai_ci table, gets nothing. Import SQL now reads all four, takes the character set from the collation's name, and gives a column with no clause its table's, which is how the live reader reports the same columns. The same mismatch is why a foreign key between two CHAR(36) columns can fail with error 3780, covered in signed vs unsigned MySQL keys.

Why did timestamp(6) lose its microseconds?

Because a diagram field had no place for fractional seconds. A MySQL TIMESTAMP, DATETIME or TIME with no precision keeps whole seconds, and MySQL rounds a fraction away without a warning. Invoice Ninja's 141 timestamp(6) columns came back as timestamp, so every created_at and updated_at (51 of each) and deleted_at (39) was rounded to the second: two rows written in the same second sort as equal, and a value compared with the application's own microsecond copy no longer matches.

The live reader knew the 6 but used it only when it restated an existing column, never in a CREATE TABLE. A time column's precision now lives in the field's precision, the slot DECIMAL already used, on every database that has one: MySQL's TIMESTAMP(6), SQL Server's DATETIME2(3), Snowflake's TIMESTAMP_NTZ(9). The field editor offers it up to each database's maximum. Writing TIMESTAMP(6) also needs the clock to match: MySQL refuses DATETIME(6) DEFAULT CURRENT_TIMESTAMP with error 1067, so the default and ON UPDATE are written as CURRENT_TIMESTAMP(6).

Checking this post turned up one more loss in the same columns, in a shape neither schema has: ON UPDATE CURRENT_TIMESTAMP(6), which sets updated_at to the current time whenever a row's values change, even when the UPDATE does not set it. Both paths read it, but the diagram had no place for it, so a table written from the diagram lost it and the copy's updated_at stopped moving. The diagram now keeps it on both paths, the field editor sets it on a TIMESTAMP or DATETIME column, and a rebuilt copy updates the column as the original does.

The fix changed the PostgreSQL post too. Mastodon's 92 timestamp(6) columns had been written back as timestamp, which PostgreSQL treats as the same type, since its default is 6; they now come back as declared, and that post was updated. Converting a diagram between databases had the opposite gap: a PostgreSQL timestamp keeps 6 digits but became MySQL's bare DATETIME, which keeps none. It now becomes DATETIME(6).

Why did double(8,2) stop rounding?

Because the precision and scale of a DOUBLE were never written. DOUBLE(8,2) rounds every value to two decimals as it is stored, so 1.239 becomes 1.24; a plain DOUBLE keeps 1.239. MySQL marks FLOAT(M,D) and DOUBLE(M,D) as deprecated, but a schema that has them still rounds. The field dialog already let you enter a precision and scale for DOUBLE, and Import SQL dropped them while the MySQL export wrote them only for DECIMAL. Both are fixed.

Which bugs did only the live path and a refresh find?

Four, none of which a catalog diff of the import path could show:

An empty-string default. Invoice Ninja's countries.decimal_separator is DEFAULT ''. MySQL reports that as an empty column_default, and the live reader read it as no default, so a copy built from the live path stored NULL where the original stores an empty string. It now reads ''.
A refresh straight after the import. Import a dump, connect to the database it came from, and Refresh & merge should find nothing to change. Measured through the app's own merge code, called from a script, it planned 1,210 statements for Invoice Ninja and 537 for Sylius, because the two paths spelled the same schema differently: a tinyint(1) default as '0' against FALSE, a column's character set as empty against utf8mb4, an index type as blank against BTREE, and CHAR against CHARACTER. Each is now read the same way on both paths, and both refreshes list nothing. Mastodon and Saleor, run through the same check, list nothing too.
Import SQL over the live diagram. Taking these screenshots, importing Invoice Ninja's dump onto the diagram read from its live database marked all 80 tables Updated, although a migration between the two is zero statements. The preview compared the two tables field by field, so the same spelling differences counted as changes. It now asks the planner Refresh & merge uses which tables a migration would touch, and marks the other tables Unchanged.
Column order, through the app. Found later, while taking the SQLite post's screenshots, and invisible to the scripts here 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, so a copy built from the diagram met a SELECT * or an INSERT without a column list with its columns in another order. 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, 74 of Invoice Ninja's 80 tables and 81 of Sylius's 115 came back in a different column order before this fix.

What does the round trip give you now?

The same database, on both paths, for both schemas.

	Import SQL	Live connection
Invoice Ninja: catalog differences	none	none
Invoice Ninja: probes that match the original	11 of 11	11 of 11
Sylius: catalog differences	16 column comments	16 column comments
Sylius: probes that match the original	8 of 8	8 of 8

A refresh from the original database straight after importing its dump lists no changes for either schema.

The other direction holds in the app's window: Import SQL of the dump onto the diagram read from the live database finds nothing to change.

All 437 UNSIGNED columns, 141 timestamp(6) columns, 3 prefix indexes, 13 ENUM value lists, 279 foreign keys with their actions and every table's and column's collation come back as they were, including the one Sylius table on utf8mb4_0900_ai_ci, whose NO PAD probe accepts the trailing-space pair in the original and in both copies.

What still differs, and why?

Only Sylius's column comments, and they are left out on purpose. Schemity treats a field's description as the diagram's own notes and does not write it to the database: on MySQL a comment can only be changed by restating the whole column with MODIFY COLUMN, and an edit to a note should never be the riskiest statement in a migration. The 16 comments are Doctrine type hints such as (DC2Type:datetime_immutable). Sylius 2.3 runs on Doctrine DBAL 4.5, which no longer writes or reads type comments; its upgrade notes say doctrine/migrations should generate a migration dropping them, so a copy without them is what Doctrine itself would arrive at.

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 development build, and the field dialog for ON UPDATE is on a small test table, since neither schema has that clause.
The live path before the fixes, in full. The "before" column covers Import SQL. On the live path, Invoice Ninja's script was refused at the same prefix index and Sylius differed only in its comments; Invoice Ninja's live copy was not patched and probed.
Data. The originals are empty. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
Views, triggers, routines and CHECK constraints. Neither schema has any, so nothing here tests them on MySQL.
MariaDB. Everything ran on MySQL 8.4.11; MariaDB's collations and defaults differ.
Other databases. SQL Server and SQLite have their own posts, with Bitwarden and Smartstore and Campfire and Jellyfin, and Snowflake with TPC-DS.
Schemas this size are not a stress test. 80 and 115 tables are typical; a thousand-table schema is covered in a separate post.

How to rerun it

Create each schema with its framework against an empty MySQL 8.4 database, take mysqldump --no-data --skip-comments --routines --triggers, put it through Schemity on either path, export the SQL and apply it to an empty database. Then list both catalogs with a query like this one, run with mysql -N -D <database>, and diff the output:

SELECT * FROM (
SELECT CONCAT_WS(' | ', 'table', table_name, engine, table_collation, table_comment)
  FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE'
UNION ALL
SELECT CONCAT_WS(' | ', 'column', table_name, column_name, column_type,
  IF(is_nullable = 'YES', 'null', 'not null'), IFNULL(CONCAT('default ', column_default), 'no default'),
  extra, IFNULL(collation_name, '-'), column_comment, IFNULL(generation_expression, ''))
  FROM information_schema.columns WHERE table_schema = DATABASE()
UNION ALL
SELECT CONCAT_WS(' | ', 'index', table_name, index_name, IF(non_unique = 0, 'unique', 'non-unique'), index_type,
  GROUP_CONCAT(CONCAT(IFNULL(column_name, expression), IFNULL(CONCAT('(', sub_part, ')'), ''), IF(collation = 'D', ' desc', ''))
    ORDER BY seq_in_index SEPARATOR ', '))
  FROM information_schema.statistics WHERE table_schema = DATABASE()
  GROUP BY table_name, index_name, non_unique, index_type
UNION ALL
SELECT CONCAT_WS(' | ', 'fk', k.table_name, k.constraint_name,
  GROUP_CONCAT(k.column_name ORDER BY k.ordinal_position SEPARATOR ', '),
  k.referenced_table_name, GROUP_CONCAT(k.referenced_column_name ORDER BY k.ordinal_position SEPARATOR ', '),
  CONCAT('on delete ', r.delete_rule), CONCAT('on update ', r.update_rule))
  FROM information_schema.key_column_usage k
  JOIN information_schema.referential_constraints r
    ON r.constraint_schema = k.constraint_schema AND r.constraint_name = k.constraint_name AND r.table_name = k.table_name
  WHERE k.table_schema = DATABASE() AND k.referenced_table_name IS NOT NULL
  GROUP BY k.table_name, k.constraint_name, k.referenced_table_name, r.delete_rule, r.update_rule
UNION ALL
SELECT CONCAT_WS(' | ', 'check', t.table_name, c.constraint_name, c.check_clause)
  FROM information_schema.check_constraints c
  JOIN information_schema.table_constraints t
    ON t.constraint_schema = c.constraint_schema AND t.constraint_name = c.constraint_name AND t.constraint_type = 'CHECK'
  WHERE c.constraint_schema = DATABASE()
) catalog ORDER BY 1;

For behaviour, run a few statements per rule inside START TRANSACTION and ROLLBACK on the original and on the copy, end each with a SELECT of what was stored, and compare both the step MySQL refuses at and the values it reads back. A database that matches on refusals alone can still be rounding every timestamp.

Round-Tripping Mastodon and Saleor Through a Database Diagram: What Broke and What We Fixed

Son Tran — Tue, 20 Oct 2026 14:51:21 +0000

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 PostgreSQL schemas, Mastodon (Rails) and Saleor (Django), through Schemity and rebuilt a database from the SQL it wrote. Before this round of fixes PostgreSQL refused that SQL for both, and once patched by hand the copies refused rows the originals accept. After the fixes the rebuilt copies match their originals in every table, key, constraint and index, and behave the same in 16 probes. What a diagram does not hold, Mastodon's own function and Saleor's four extensions, is no longer a hand-written step: Import SQL keeps it and Save runs it first.

A database diagram that reads a schema and writes it back has one job: give you the same database. To test that, we took two real open-source schemas, Mastodon v4.7.3 (Rails, 118 tables) and Saleor 3.23.41 (Django, 148 tables), put each through Schemity both from its pg_dump and from the live database, and rebuilt a database from the SQL Schemity wrote. Before this round of fixes, PostgreSQL refused the SQL Schemity wrote for both schemas. After them, the rebuilt copies match the originals in every table, column, key, constraint and index, apart from spelling, and accept and refuse exactly the same rows in 16 behaviour probes.

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. Reading a database you did not design is how most people start with it: you reverse engineer the PostgreSQL schema to an ERD to understand your schema, and later generate SQL from that diagram. Both directions are only as good as what survives the trip, so this post measures exactly that, including what broke.

Which two schemas, and which two paths?

Two production schemas, each created by its own framework rather than written for the test, and chosen because they use different parts of PostgreSQL.

	Mastodon v4.7.3	Saleor 3.23.41
What it is	Federated social network	E-commerce platform
Created by	Rails, `rails db:schema:load`	Django, `manage.py migrate`
Tables	118	148
Columns	1,018	1,348
Foreign keys	156	266, 265 of them `DEFERRABLE INITIALLY DEFERRED`
`CHECK` constraints	0	43
Unique constraints	0 (uniqueness is in indexes)	128
Indexes besides primary keys	217	616
Partial (`WHERE`)	44	5
On an expression	4	2
With an operator class	7	62
With `DESC`	7	0
Keys numbered by the database	106 serial	132 `GENERATED BY DEFAULT AS IDENTITY`
Also has	2 views, 1 function	4 extensions, 1 function

Rails leans on serial keys and on partial and sorted indexes; Django on identity keys, deferred foreign keys and operator classes for search. Between them they cover most of what a framework writes into a PostgreSQL schema.

Mastodon's 118 tables, imported from its dump and grouped into 17 legends by the models in Mastodon's own source (accounts, statuses, the social graph, moderation, federation and so on), look like this. Open the full-size diagram to read the table names.

The status bar of that diagram says 168 relations, not 156. The extra 12 are relationships Schemity inferred from column names that have no foreign key in the database. They are drawn dashed and are never written out as constraints, so the exported script holds exactly Mastodon's 156 foreign keys. A schema this size is not read by shrinking it: the minimap shows where every legend sits, and search finds a column across all 118 tables. Why the canvas works that way has its own post.

Each went through Schemity on two paths, which run different code:

Import SQL: the pg_dump --schema-only file read by Schemity's SQL parser, with no database connection.
Live connection: Schemity connected to the database and read PostgreSQL's catalog.

Both paths end the same way: the diagram's SQL exported and applied to an empty database. The four runs were done in the app, in a development build with the fixes described below. The originals ran on PostgreSQL 17.11 and the rebuilt copies on PostgreSQL 18.3.

Import SQL lists every table it found before anything is added, then what it did with the rest. Mastodon's tables call a function a diagram does not hold, and the summary says it kept the SQL that creates it:

Mastodon's ids come from its own timestamp_id(), which reads seven sequences no column owns. Import SQL keeps the dump's own text for the function and those sequences as setup SQL. It writes it before the tables in View SQL, and when Save migrates to a database that lacks the function, puts it at the top of the migration, with each sequence as CREATE SEQUENCE IF NOT EXISTS so one the database already has is left alone:

Saleor's tables call no function of their own, but its search indexes need four extensions, among them pg_trgm. Import SQL keeps them, and the diagram's SQL creates them first:

None of that existed when the benchmark started. The first runs needed a hand-written prelude on each empty database: the function and its seven sequences copied from Mastodon's dump, and four CREATE EXTENSION lines for Saleor. The comparisons below were measured that way. Once Import SQL kept the setup SQL itself, both dumps went through the app again, imported and migrated to an empty database with Save and nothing run by hand: all 118 of Mastodon's tables and 148 of Saleor's came up, and all 16 probes still match their originals.

How do you prove a rebuilt schema is the same?

Two ways, and they answer different questions: does the database describe itself the same way, and does it behave the same way.

The catalog diff. Each original was created by its own framework, not by us: Mastodon with rails db:schema:load in the official ghcr.io/mastodon/mastodon:v4.7.3 image, Saleor with manage.py migrate in ghcr.io/saleor/saleor:3.23.41, both on PostgreSQL 17.11. The schema then went into Schemity two ways, as a pg_dump --schema-only file through Import SQL and as a live connection, and Schemity's exported SQL was applied to an empty database. A query over PostgreSQL's own catalog then lists, for both databases, one line per fact:

every column with its type as format_type prints it, NOT NULL, its default and its identity
every primary key, foreign key, unique and CHECK constraint as pg_get_constraintdef prints it
every index as pg_indexes prints it, with its own name left out
every sequence with its type, its start and the column that owns it

The two lists are diffed line by line. A difference is either a real change or the same thing spelled two ways, such as DEFAULT now() and DEFAULT CURRENT_TIMESTAMP, which are the same time, and each one is classified by hand in the results.

The behaviour probes. A catalog can agree while a database still behaves differently, so each copy also gets 16 probes, eight per app, run against the original and every copy. A probe is a few statements in one transaction that is rolled back, so nothing stays: insert an account with its own id, insert two usernames that differ only in case, insert a tax rate before the tax class it points to. Its outcome is "accepted" or "refused at step N" with PostgreSQL's reason, and a copy passes when its outcome is the original's.

Check	What it compares	What it catches
Catalog diff	Columns, keys, constraints, indexes, as PostgreSQL reports them	Anything missing, added or changed in the schema
Behaviour probes	Which rows each database accepts and refuses	A difference the catalog reads past, or one that only matters to the data

Data is not part of this test. Both originals hold almost none: Saleor's migrations insert a default customer type, which one probe has to set aside first, and Mastodon's insert nothing the probes touch.

What failed before the fixes?

Everything below is from Schemity as of 14 October 2026, through Import SQL. Rebuilding the live path at that point would have meant a second full build of the desktop app, so the "before" column has the import path only.

Neither exported script ran. Schemity imported both schemas, but PostgreSQL refused the SQL it wrote for each at the first double precision column, which Schemity had written as DOUBLE, a MySQL name: 5 columns in Mastodon, 9 in Saleor. With that patched by hand, and Saleor's one empty array default, written as the text 'ARRAY[]', patched too, both scripts ran, and the probes showed what the database would have done.

Probe	Original	Before the fixes	After
Mastodon: a row with its own id in a serial table	accepted	refused	accepted
Mastodon: usernames that differ only in case	refused	accepted	refused
Mastodon: two statuses with the same URI	refused	accepted	refused
Saleor: any row that gives its own id	accepted	refused	accepted
Saleor: a rate inserted before its tax class, in one transaction	accepted	refused	accepted
Saleor: one country's rates under two tax classes	accepted	refused	accepted
Saleor: two customer types that are not the default	accepted	refused	accepted

Mastodon's other five probes passed even before the fixes. Saleor's first failure hid the rest, since every probe that gives a row its own id stopped at step one, so its last three rows come from a second run that lets the database pick the ids. Each failure had its own cause.

Why did serial ids come back as `GENERATED ALWAYS`?

Rails makes a key bigserial, and pg_dump writes that as a plain bigint column plus a separate ALTER TABLE ... ALTER COLUMN id SET DEFAULT nextval('accounts_id_seq'). Import SQL ignored that second statement, so the column arrived with no default, and the export followed its old rule for a lone integer key with none: GENERATED ALWAYS AS IDENTITY. 104 of Mastodon's 106 keys came back that way, and ALWAYS refuses a row that gives its own id, which is exactly what fixtures, seed scripts and copied data do. The other two keys lost their default altogether. Import SQL now reads the SET DEFAULT, and a column on its own sequence is written back as BIGSERIAL, which creates the same sequence, default and ownership. The trade-offs between the two are in PostgreSQL serial vs identity.

On the canvas, a key the database numbers reads auto, whether it is a serial or an identity, and a key the application assigns stays blank. A column that draws from a sequence of its own, such as an invoice number, shows nextval, because it is a default and not a key.

What does Django's `BY DEFAULT` identity need from a diagram?

To be recorded at all. Since Django 4.1, a key is GENERATED BY DEFAULT AS IDENTITY: the database numbers it, but a row may still bring its own id. pg_dump writes each one as ALTER TABLE ... ADD GENERATED BY DEFAULT AS IDENTITY (SEQUENCE NAME ...), and the SQL parser behind Import SQL cannot read the SEQUENCE NAME option, so all 132 of Saleor's statements went unread, and the export gave 133 keys GENERATED ALWAYS. A diagram now records each key's identity as ALWAYS, BY DEFAULT or none, from the live database and from a dump, writes it back as it was, and shows it in the field dialog as an Auto increment checkbox with the kind beside it.

Why does every Django foreign key say `DEFERRABLE INITIALLY DEFERRED`?

So a transaction can insert a child row before its parent, the way loaddata and Django fixtures load. 265 of Saleor's 266 foreign keys are declared that way, and before the fixes all 265 came back as ordinary keys, checked by each statement, so the rate inserted before its tax class was refused at once. The diagram now records when each key is checked, and the relationship dialog shows it.

What happens to a partial unique index?

It used to become a full one, which is the dangerous direction: the copy refuses rows the original accepts. Saleor allows one default tax rate per country with CREATE UNIQUE INDEX ... ON tax_taxclasscountryrate (country) WHERE (tax_class_id IS NULL). Without its condition that index makes country unique across the whole table, so a second rate for the same country, under a different tax class, was refused. Saleor's one-default customer type went the same way, and a UNIQUE NULLS NOT DISTINCT rule went the other, losing the clause that makes NULL count as a duplicate. How a unique index differs from a unique constraint in the first place is in unique constraint vs unique index.

How do you keep `lower(username)` and `text_pattern_ops`?

By keeping the index's key list as SQL when its columns alone do not describe it. Mastodon makes usernames unique regardless of case with UNIQUE (lower(username), COALESCE(lower(domain), '')), an index on expressions, and the old diagram held only column names, so the rule was dropped and the copy accepted Alice beside alice. Seven other Mastodon indexes use an operator class, text_pattern_ops, which the parser could not read either, so all seven were lost, three of them unique, which is why the copy accepted two statuses with the same URI. Saleor has 62 indexes with an operator class, among them 19 trigram GIN indexes for search. The diagram now keeps the expression, the operator class, a sort order such as id DESC, the WHERE condition and NULLS NOT DISTINCT, and the index dialog shows them.

The keys read lower((username)::text) rather than lower(username) because that is how PostgreSQL stores the expression, and the diagram keeps what the database reports.

Which bugs did only real dumps find?

The ones no hand-written test schema had:

Newer versions of pg_dump wrap the file in psql's \restrict command, which is not SQL and has no semicolon, so it joined the statement after it.
Saleor writes extension operator classes with their schema, public.gin_trgm_ops, which needed its own handling.
The live reader reported a nullable column with no default as DEFAULT NULL, so the export wrote DEFAULT NULL on 171 of Saleor's columns.
Import SQL turned every CREATE UNIQUE INDEX into a UNIQUE constraint, so 69 of Mastodon's unique indexes came back as constraints. It is the same rule, so no probe noticed, but not the same object: a later Rails migration that runs remove_index on one would fail, because PostgreSQL will not drop an index a constraint owns. A unique index now stays an index, as the live reader already kept it; on the canvas both still show as unique.
A diagram imported from Mastodon's dump and then refreshed from the database it built reported 70 columns as changed in the database, because the two paths spell false and FALSE, or DECIMAL and NUMERIC, differently. Comparing the meaning instead of the spelling took the planned migration from 71 statements to none.

None of these broke a test, because every test schema had been written by hand to cover a feature. A framework's real output was the first input that had all of them at once.

One more turned up later, while taking the screenshots for the SQLite post, which the scripts here 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, so a copy built from the diagram met a SELECT * or an INSERT without a column list with its columns in another order. 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, 102 of Mastodon's 118 tables and 125 of Saleor's 148 came back in a different column order before this fix.

What does the round trip give you now?

The same database, on both paths, for both schemas. Each line below is one run in the app: the schema went in through one path, Schemity wrote the SQL, and the rebuilt copy was compared with the original.

	Import SQL	Live connection
Mastodon: catalog differences	none	none
Mastodon: sequences	all 113 identical	all 113 identical
Mastodon: probes that match the original	8 of 8	8 of 8
Saleor: catalog differences	4, all spelling	5, all spelling
Saleor: sequences	111 identical, 24 renamed (below)	111 identical, 24 renamed (below)
Saleor: probes that match the original	8 of 8	8 of 8

A first version of this table gave Mastodon 92 differences, all timestamp(6) columns written back as timestamp. On PostgreSQL those are one type, because 6 digits is the default, so nothing was lost. The MySQL round trip showed the same gap is not harmless there: a bare MySQL timestamp keeps whole seconds, so timestamp(6) written back that way rounds every microsecond away. Schemity now keeps a time column's precision as it was declared, on every database, and Mastodon's 92 columns come back as timestamp(6). That rerun went through Schemity's core on the development build, not through the app.

Both paths start from the original database Rails created. A first version of this post read Mastodon's live path from the Import SQL copy instead, which made its number a repeat of the import's rather than its own; the line above is from the original.

All 132 identity keys come back GENERATED BY DEFAULT AS IDENTITY, all 106 serial keys BIGSERIAL, all 265 deferred foreign keys DEFERRABLE INITIALLY DEFERRED, and every partial, expression and sorted index with its condition, key list and operator class.

What still differs, and why?

Two kinds of difference are left: the same thing spelled another way, and objects the diagram does not hold.

Spelled differently, behaving the same:

In the original	In the rebuilt copy	Where
`DEFAULT now()`	`DEFAULT CURRENT_TIMESTAMP`	2 Saleor columns; both are the transaction's start time
`DEFAULT ARRAY[]::character varying[]`	`DEFAULT '{}'::character varying[]`	1 Saleor column; both are an empty array
`UNIQUE NULLS NOT DISTINCT` constraint	Unique index with `NULLS NOT DISTINCT`	1 Saleor rule, the same index under it
`DEFAULT NULL::character varying`	no default	1 Saleor column, live path only; a column with no default is `NULL` anyway
An identity sequence named after a table's old name, `account_serviceaccount_id_seq` on `app_app.id`	The name PostgreSQL gives it now, `app_app_id_seq`	24 Saleor keys, same type, start and column; this matters only to code that names the sequence itself

Not held by the diagram as objects:

Functions. Mastodon numbers seven tables with its own timestamp_id() function, and the column defaults that call it are kept. Without the function PostgreSQL stops at the first such table with function public.timestamp_id(text) does not exist. From a dump, Import SQL now keeps the function and the seven sequences it reads as setup SQL and runs them first, as shown above. A function read from a live database is only named: View SQL warns that the script calls a function it does not create.
Views. A CREATE VIEW statement names its columns' types nowhere, so Import SQL skips Mastodon's two views and lists them as skipped. A live connection reads them, as read-only tables in the diagram, and they are not exported as SQL.
A sequence's options. A sequence a column default draws on, or one a column owns with OWNED BY, comes back bare: bigint, starting at 1. Saleor's three such sequences, among them order_order_number_seq on order_order.number, match their originals because those were bare too.

The import summary counts what it skips: 12 statements for Mastodon (seven sequences timestamp_id() reads, two views, the function and two indexes on the materialized view), all but the indexes named, and 1 for Saleor, a function no table calls. Saleor's four extensions and their comments are kept, not skipped; before this round they were 8 of 9 skipped statements.

One gap was found only on the way to this post: Saleor's three owned sequences have no column default, nothing in the schema reads them, and the first version of the catalog diff did not list sequences, so they were missing from every rebuilt copy without a line of difference. Reading the skipped statements caught it. The diagram now keeps a sequence a column owns, and the catalog diff lists sequences.

What this benchmark does not show

Other databases. Everything here is PostgreSQL. MySQL, SQL Server, SQLite and Snowflake each get their own schema and their own post. Converting a diagram from PostgreSQL to one of them leaves identity kinds, DEFERRABLE and partial and expression indexes out, and the converted script's notes name each one.
Data. The originals are empty, and no rows were copied. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
Prisma, and other frameworks. Rails and Django are covered; a schema from Prisma, Laravel or a hand-written monolith may use features neither of these uses.
The live path before the fixes. The "before" column covers Import SQL only.
Triggers. Neither schema has one, so nothing here tests them. A trigger is not a table either, and one left behind fails nothing: the rebuilt database simply behaves differently, which is the case this benchmark exists to catch. A schema that leans on triggers, such as an audit table or an updated_at trigger, is the next one to test.
Schemas this size are not a stress test. 118 and 148 tables are typical, not large; how Schemity handles a schema of a thousand tables is in a separate post.

How to rerun it

The method needs nothing from us. Create each schema with its own framework in its official Docker image, take pg_dump --schema-only --no-owner --no-privileges, put it through Schemity on either path, export the SQL, and apply it to an empty database. Through the app, Save does that and runs the setup SQL first; by hand, View SQL's script already starts with it. Then list both catalogs with a query like this one and diff the output:

SELECT line FROM (
  SELECT format('column %s.%s %s%s%s', c.table_name, c.column_name,
         format_type(a.atttypid, a.atttypmod),
         CASE WHEN c.is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
         COALESCE(' DEFAULT ' || c.column_default, '') ||
         CASE WHEN c.is_identity = 'YES' THEN ' IDENTITY ' || c.identity_generation ELSE '' END) AS line
  FROM information_schema.columns c
  JOIN pg_attribute a ON a.attrelid = format('public.%I', c.table_name)::regclass AND a.attname = c.column_name
  JOIN information_schema.tables t ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND t.table_type = 'BASE TABLE'
  WHERE c.table_schema = 'public'
  UNION ALL
  SELECT format('constraint %s %s %s', conrelid::regclass, contype, pg_get_constraintdef(oid))
  FROM pg_constraint WHERE connamespace = 'public'::regnamespace AND contype IN ('p', 'u', 'f', 'c')
  UNION ALL
  SELECT format('index %s %s', tablename, regexp_replace(indexdef, '^CREATE (UNIQUE )?INDEX \S+ ON ', 'CREATE \1INDEX ON '))
  FROM pg_indexes WHERE schemaname = 'public'
  UNION ALL
  SELECT format('sequence %s %s start %s%s', s.relname, format_type(q.seqtypid, NULL), q.seqstart,
         COALESCE((SELECT ' owned by ' || t.relname || '.' || a.attname FROM pg_depend d
                   JOIN pg_class t ON t.oid = d.refobjid
                   JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
                   WHERE d.objid = s.oid AND d.deptype IN ('a', 'i')), ''))
  FROM pg_class s JOIN pg_sequence q ON q.seqrelid = s.oid
  WHERE s.relnamespace = 'public'::regnamespace
) lines ORDER BY 1;

For behaviour, write a few statements per rule you care about, run each inside BEGIN and ROLLBACK on the original and on the copy, and compare which ones PostgreSQL accepts and at which step it refuses the rest.

MySQL UNSIGNED Primary Keys: Signed or Unsigned, and How to Fix Error 3780 When They Mix

Son Tran — Mon, 19 Oct 2026 04:34:51 +0000

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

TL;DR: Use the sign your schema already uses, because MySQL refuses a foreign key whose sign differs from the key it references. Laravel and WordPress create BIGINT UNSIGNED keys, while Rails and Django create signed BIGINT. For a new schema with nothing deciding for you, signed BIGINT is simpler, and changing the sign later means dropping every foreign key that references the key and copying the key's table and each referencing table.

A MySQL primary key can be signed or UNSIGNED, and both cost the same storage: 4 bytes for INT, 8 for BIGINT. What decides the choice is not the range but the foreign keys. MySQL refuses any foreign key whose sign differs from the column it references, so the sign of the first key decides the sign of every key column that will ever point at it.

That is why the question usually arrives as an error rather than a design discussion. A migration adds an orders table to a Laravel database, declares user_id as a plain BIGINT, and MySQL answers with error 3780. Below is what each choice gets you, measured on MySQL 8.4.11 in a throwaway container, how to pick, and what changing your mind later costs.

What UNSIGNED changes in a MySQL integer column

Only the range. The MySQL manual's integer table gives each type one storage size, signed or not:

Type	Bytes	Signed maximum	`UNSIGNED` maximum
`INT`	4	2,147,483,647	4,294,967,295
`BIGINT`	8	9,223,372,036,854,775,807	18,446,744,073,709,551,615

On 8.4, inserting 2,147,483,648 into a signed INT and -1 into an INT UNSIGNED both failed with ERROR 1264: Out of range value. The doubled range is real for INT, where 2.1 billion is a number a busy events table can reach. For BIGINT it is academic: inserting a million rows a second, a signed key lasts about 292,000 years.

Two things UNSIGNED is not. It is not deprecated on integers: MySQL 8.0.17 deprecated it for DECIMAL, FLOAT and DOUBLE, and 8.4 still warns UNSIGNED for decimal and floating point data types is deprecated on those, but not on INT or BIGINT. And the (20) in BIGINT(20) UNSIGNED is a display width, deprecated in the same release. 8.4 warns Integer display width is deprecated and SHOW CREATE TABLE drops it.

Which frameworks create signed and unsigned keys

Most schemas did not choose. The framework that created the first table did:

Framework	Primary key it creates on MySQL	Source
Laravel	`BIGINT UNSIGNED` from `$table->id()`, and `foreignId()` matches it	Laravel 13 migrations docs: `id` is "an alias of the `bigIncrements` method", which "creates an auto-incrementing `UNSIGNED BIGINT`"
WordPress	`bigint(20) unsigned` on every core table: `ID`, `post_author`, `comment_ID`	wp-admin/includes/schema.php
Rails	signed `bigint`	`primary_key: "bigint auto_increment PRIMARY KEY"` in the MySQL adapter
Django	signed `bigint` for `BigAutoField`, `integer` for `AutoField`	`"BigAutoField": "bigint AUTO_INCREMENT"` in the MySQL backend

Laravel's current helpers stay consistent with each other, but its default changed once. Apps created before Laravel 5.8 made users.id with $table->increments('id'), which is INT UNSIGNED, so a new foreignId('user_id') pointing at it fails, and the column needs $table->unsignedInteger('user_id') instead. WordPress core declares no foreign keys at all, so there the error comes from a plugin or a hand-written table. Otherwise the problem appears where two conventions meet: a hand-written $table->bigInteger('user_id') (Laravel's bigInteger is signed) next to $table->id(), a Laravel app added on top of a database a Rails or raw-SQL app created first, or a reporting table written by hand in a WordPress database.

To see which convention a database already uses, count its integer primary keys by type:

SELECT c.COLUMN_TYPE, COUNT(*) AS primary_keys
FROM information_schema.COLUMNS c
JOIN information_schema.KEY_COLUMN_USAGE k
  ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME
 AND k.COLUMN_NAME = c.COLUMN_NAME AND k.CONSTRAINT_NAME = 'PRIMARY'
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.DATA_TYPE IN ('tinyint', 'smallint', 'mediumint', 'int', 'bigint')
GROUP BY c.COLUMN_TYPE;

One row back means one convention. Two rows means the next foreign key has to check which table it points at.

Should a MySQL primary key be UNSIGNED?

Only when the schema around it already is. The sign is not a per-table preference, it is a convention the whole schema has to share, and the cheapest choice is almost always the one already in the database:

Situation	Choose	Why
Laravel or WordPress schema	`BIGINT UNSIGNED`, on keys and every foreign key column	Every existing key is unsigned, and a signed column cannot reference one
Rails or Django schema	Signed `BIGINT`	Same rule, the other way round
New schema, no framework deciding	Signed `BIGINT`	The extra range is unreachable, and signed avoids the problems below
A 4-byte `INT` key that may pass 2.1 billion	`BIGINT`, unless key size really matters	`INT UNSIGNED` doubles the headroom once, `BIGINT` multiplies it by four billion

The case for signed in a new schema is everything outside the table. Subtraction is one: on 8.4, SELECT a.id - b.id between two BIGINT UNSIGNED ids, 1 minus 2, failed with ERROR 1690: BIGINT UNSIGNED value is out of range, and CAST(a.id - b.id AS SIGNED) failed the same way, because the subtraction runs first. Only CAST(a.id AS SIGNED) - CAST(b.id AS SIGNED), or the NO_UNSIGNED_SUBTRACTION SQL mode, returned -1. Porting is another: PostgreSQL, SQL Server and SQLite have no unsigned 64-bit integer, so a BIGINT UNSIGNED key either moves to BIGINT with a theoretical upper half it can no longer hold, or to NUMERIC. Java's long, Go's int64 and PHP's int are signed 64-bit as well, so a BIGINT UNSIGNED value above 2^63-1 has no native type in them.

Whichever you pick, the rule that is not optional is the same: a foreign key column gets exactly the type of the key it references, UNSIGNED included.

Why does MySQL return error 3780 for a foreign key?

Because the MySQL manual requires it: "The size and sign of fixed precision types such as INTEGER and DECIMAL must be the same." This is the Laravel case, reproduced on 8.4.11:

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    CONSTRAINT posts_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id)
);

ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'posts_user_id_foreign' are incompatible.

Three details from the same test session:

SET foreign_key_checks = 0 does not get it through. The same CREATE TABLE failed with the same error. The manual lists "creating a foreign key constraint where a column references a nonmatching column type" among the things disabled checks still refuse.
Same sign, different size fails too. An INT UNSIGNED column referencing BIGINT UNSIGNED returned 3780 as well.
The direction does not matter. A BIGINT UNSIGNED column referencing a signed BIGINT key failed the same way as the reverse. So did a Laravel foreignId('account_id'), which differs in both size and sign, pointing at a legacy signed INT key. The fix there is $table->integer('account_id') followed by $table->foreign('account_id')->references('id')->on('accounts'), matching the key rather than the framework's default.

The other cause of error 3780: string keys with different collations

Integer keys are not the only ones. The same manual passage goes on: "For nonbinary (character) string columns, the character set and collation must be the same." A CHAR(36) UUID key or a country_code hits this whenever the key's table and the new table were created under different defaults, and the error text is identical, so it reads like a type problem.

It usually comes from a table made years ago. A database created on MySQL 8.0 or later defaults to utf8mb4_0900_ai_ci, the 8.0 server default. Older tables often carry utf8mb3_general_ci, and Laravel's default config writes utf8mb4_unicode_ci onto every table its migrations create. Any table made without a COLLATE clause, by raw SQL, a GUI or another framework, gets the database default instead. On 8.4.11:

CREATE TABLE tenants (
    id CHAR(36) NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE projects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    CONSTRAINT projects_tenant_id_foreign FOREIGN KEY (tenant_id) REFERENCES tenants (id)
);

ERROR 3780 (HY000): Referencing column 'tenant_id' and referenced column 'id' in foreign key constraint 'projects_tenant_id_foreign' are incompatible.

Both columns are utf8mb4 and both are CHAR(36). Only the collation differs, and foreign_key_checks = 0 did not get it through either. Declaring the column as tenant_id CHAR(36) COLLATE utf8mb4_unicode_ci NOT NULL did. To see which keys a new table will fail against before you write one, list the primary key columns whose collation differs from the database default:

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.COLLATION_NAME,
       s.DEFAULT_COLLATION_NAME AS new_tables_get
FROM information_schema.COLUMNS c
JOIN information_schema.KEY_COLUMN_USAGE k
  ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME
 AND k.COLUMN_NAME = c.COLUMN_NAME AND k.CONSTRAINT_NAME = 'PRIMARY'
JOIN information_schema.SCHEMATA s ON s.SCHEMA_NAME = c.TABLE_SCHEMA
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.COLLATION_NAME <> s.DEFAULT_COLLATION_NAME;

Every row is a key whose foreign key columns need an explicit COLLATE. Integer keys never appear, because their COLLATION_NAME is NULL.

What does it cost to change a key from UNSIGNED to signed?

More than one ALTER. With orders.user_id referencing users.id, both BIGINT UNSIGNED, the obvious migration fails on its first statement, with or without foreign key checks:

ALTER TABLE users MODIFY COLUMN id BIGINT AUTO_INCREMENT;

ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'orders_user_id_foreign' are incompatible.

Changing the child column first fails the same way. The foreign key has to go, both columns change, and the key comes back:

ALTER TABLE orders DROP FOREIGN KEY orders_user_id_foreign;
ALTER TABLE users MODIFY COLUMN id BIGINT AUTO_INCREMENT;
ALTER TABLE orders MODIFY COLUMN user_id BIGINT NOT NULL;
ALTER TABLE orders ADD CONSTRAINT orders_user_id_foreign
    FOREIGN KEY (user_id) REFERENCES users (id);

On two tables of 1,000,000 rows each, that took 7.3 seconds, and every statement after the drop reported 1,000,000 rows affected:

Statement	Time	What MySQL did
`DROP FOREIGN KEY`	0.01 s	Metadata only
`MODIFY COLUMN` on `users.id`	1.53 s	Copied the table: a type change is `ALGORITHM=COPY` only
`MODIFY COLUMN` on `orders.user_id`	2.49 s	Copied the table
`ADD CONSTRAINT ... FOREIGN KEY`	3.27 s	Copied `orders` again: with checks on, `ALGORITHM=INPLACE` is refused with `Adding foreign keys needs foreign_key_checks=OFF`

The last step is the one you can make cheaper. With foreign_key_checks = 0, the same ADD CONSTRAINT on a 1,000,000-row table affected 0 rows and took 0.87 s, because MySQL skips checking the existing rows. That is safe here, where only the type changed and no value did, and unsafe anywhere rows might not match.

The online DDL table lists a column type change as "Permits Concurrent DML: No", so writes to each table wait while it is copied. Multiply by every table that references users and the cost of switching is clear: decide the sign once, at the first table, because changing it later is a schema migration across the whole graph of foreign keys.

How Schemity handles UNSIGNED keys

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. When you connect to MySQL, it reads each column's sign with its type, and the canvas shows an unsigned column as uBIGINT, so a schema's convention, and any table that breaks it, is visible at a glance across the diagram.

To design the next one, the field editor offers an Unsigned checkbox on MySQL integer types. Drawing a relationship copies the key's full type into the new foreign key column, sign included, so a new table pointing at a Laravel users.id gets user_id BIGINT UNSIGNED and its migration applies instead of failing with 3780. A string key's character set and collation are copied the same way, so a column pointing at tenants.id is created with COLLATE utf8mb4_unicode_ci whatever the database default is. Ticking or clearing Unsigned on a key changes every foreign key column that references it in the same edit, one undo step.

A mismatch that already exists, or one you type yourself, is caught by lint. Its fk-type-mismatch rule reports "Foreign key type does not match the column it references" on the foreign key's field row, with the detail that the two columns differ in UNSIGNED and that MySQL refuses a foreign key whose column and key differ in sign. On MySQL it also names both collations when a string key and its foreign key column differ in character set or collation.

Changing the sign of a key that is already referenced plans the drop, modify, re-add sequence from the section above, and impact analysis reports each MODIFY before it runs as "Rewrites users to change id" with the row count, and a sign change that can lose values, such as BIGINT UNSIGNED to BIGINT, as "values may not survive the cast". The foreign key it adds back is listed too, as "Checking the new key holds back writes to orders", since that was the slowest step in the test above. If you later convert the diagram's SQL to PostgreSQL, SQL Server or SQLite, unsigned integers are widened one size so every value still fits, and a BIGINT UNSIGNED stays a signed 64-bit integer (BIGINT, or INTEGER on SQLite) with a note that values above 2^63-1 would not.

Signed or unsigned MySQL keys, in one list

Existing schema: use whatever sign its primary keys already have. The information_schema query above tells you.
Laravel or WordPress: BIGINT UNSIGNED everywhere, including hand-written foreign key columns.
Rails, Django, or a new schema with no framework: signed BIGINT.
Foreign key column: exactly the key's type, sign and size included. Error 3780 means it is not.
Changing the sign later: drop the foreign keys, modify both sides, add the keys back, and expect each table to be copied.

The same choice on PostgreSQL, where there is no unsigned option but the key can be a UUID, is in UUID vs bigint primary keys in Postgres. Naming the constraints those foreign keys create, so orders_user_id_foreign still means something in five years, is in the PostgreSQL constraint naming convention.

Foreign Keys Between Modules in a Modular Monolith: Keep, Restrict, or Drop?

Son Tran — Mon, 19 Oct 2026 04:34:16 +0000

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

TL;DR: Keep a foreign key between two modules while they share one database and ship together: it costs an index and a row lock, and the coupling it creates is the coupling your code already has. Change its delete action to RESTRICT so no module deletes another module's rows, and drop it only when a module moves to its own database or needs to rebuild its tables alone, keeping the column documented and checked for orphans.

A foreign key from one module's table to another's is worth keeping while both modules live in one database and ship as one application. It costs an index and a row lock per write, it refuses orphan rows, and on the day a module leaves, dropping it is one ALTER TABLE. What you should not keep is a cross-module ON DELETE CASCADE, and what you cannot afford is not knowing where those keys are.

The common advice runs the other way. Milan Jovanović's modular monolith data isolation guide states that "Joins are only allowed between tables of the same module" and that "Removing the foreign keys when extracting tables is not a problem." Both are true. Neither is an argument for removing the keys today, years before any extraction, and giving up the one integrity check that no module can forget to call. The question worth answering is narrower: which cross-module keys to keep, which delete action they should have, and when one should go.

Every statement below was run on PostgreSQL 18.3 in a throwaway container. The example is a Django-style monolith in one schema, where the table name prefix is the module: customers_customer, ordering_order, billing_invoice.

Foreign keys between modules in a modular monolith: what do they cost?

Here is the schema most monoliths end up with:

CREATE TABLE customers_customer (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE
);

CREATE TABLE ordering_order (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers_customer (id) ON DELETE CASCADE,
    total numeric(12, 2) NOT NULL
);

CREATE TABLE billing_invoice (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES ordering_order (id),
    amount numeric(12, 2) NOT NULL
);

Two keys cross a boundary, and each one ties the modules together in five concrete ways.

A delete in one module reaches into another. Deleting a customer whose orders were never invoiced silently deletes those orders, which belong to the ordering module. Deleting a customer whose order was invoiced fails, and the error names a table the customers module has never heard of: ERROR: update or delete on table "ordering_order" violates foreign key constraint "billing_invoice_order_id_fkey" on table "billing_invoice". The customers module's delete was stopped by billing, two modules away.

A module cannot reset or rebuild its own tables. TRUNCATE customers_customer fails with cannot truncate a table referenced in a foreign key constraint, and DROP TABLE customers_customer fails with cannot drop table customers_customer because other objects depend on it. A migration that rebuilds one of them needs the other module's cooperation. Tests have an easier way out: the error's own hint is to truncate both tables in one statement, or a test can roll back its transaction instead.

Every write reaches into the other module. Inserting an order takes a FOR KEY SHARE lock on the customer row it points at, which pgrowlocks shows while the transaction is open, and deleting a customer looks up its orders by customer_id. PostgreSQL does not index a foreign key column for you, so without CREATE INDEX ON ordering_order (customer_id) that lookup scans the whole orders table.

Schema changes lock the other module's table. The ALTER TABLE documentation says ADD FOREIGN KEY "also acquires a SHARE ROW EXCLUSIVE lock on the referenced table". Dropping one is heavier: in the same container, ALTER TABLE ... DROP CONSTRAINT on a cross-module key held ACCESS EXCLUSIVE on both the child table and the referenced table until it committed. On a busy table, run it with SET lock_timeout so it gives up instead of queueing every other query behind it.

It cannot survive a move to another database. PostgreSQL has no cross-database foreign keys: REFERENCES orders_db.public.ordering_order (id) is refused with cross-database references are not implemented. The key has to be dropped on the day the module moves out, whatever you decide now.

None of these is a reason to drop a key in a monolith that is staying a monolith. They are the reasons to choose its delete action deliberately and to know exactly which keys you have.

Which cross-module foreign keys to keep, restrict, or drop

Situation	Decision	Why
Modules share one database and deploy together	Keep the key, index its column	Integrity costs an index and a row lock, and dropping it later is one statement
The key is `ON DELETE CASCADE` across a boundary	Keep it, change the action to `RESTRICT`	No module should delete another module's rows as a side effect
The module is moving to its own database	Drop it before the move	PostgreSQL cannot enforce it across databases
The referenced module must rebuild its tables on its own in production	Drop it	Otherwise every rebuild needs the other module
The referenced rows are archived or deleted on their own schedule	Drop it, or copy the values you need	The reference is history, not a live link
Two modules reference each other	Remove one direction first	A loop means extracting either module means changing the other too

The RESTRICT row is the one most schemas need today. A module that wants to delete a customer should be told that orders still point at it, and decide what that means for its own data: deactivate the customer, or ask the ordering module to deal with its orders first. The change is one statement, and afterwards the delete fails with violates RESTRICT setting of foreign key constraint "ordering_order_customer_id_fkey":

ALTER TABLE ordering_order DROP CONSTRAINT ordering_order_customer_id_fkey,
    ADD CONSTRAINT ordering_order_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers_customer (id) ON DELETE RESTRICT;

Picking the action for a key inside a module is a different question, answered in ON DELETE SET NULL vs CASCADE vs RESTRICT. Across a module boundary the answer is almost always RESTRICT or NO ACTION, because the child row belongs to someone else. The two refuse the same deletes; NO ACTION, the default and what billing_invoice already has, differs only in that its check can be deferred to the end of the transaction.

A key also tends to point at a module's identity table rather than at its internals. ordering_order.customer_id referencing customers_customer is borrowing one concept, the customer, which is the shape a boundary can live with. A key from billing into a customers module's customers_address table is reaching into an implementation detail, and it is the first one to question.

How to list every foreign key that crosses a module boundary

The catalogue already knows. When modules share a schema and the table prefix is the module, compare the prefix of the child table with the prefix of the parent:

SELECT c.conrelid::regclass  AS child_table,
       c.confrelid::regclass AS parent_table,
       c.conname,
       c.confdeltype         AS on_delete
FROM pg_constraint c
JOIN pg_class ch ON ch.oid = c.conrelid
JOIN pg_class pa ON pa.oid = c.confrelid
WHERE c.contype = 'f'
  AND split_part(ch.relname, '_', 1) <> split_part(pa.relname, '_', 1)
ORDER BY 1, 2;

On the example it returns the two keys, with on_delete reading c for ordering_order_customer_id_fkey and a for billing_invoice_order_id_fkey. The pg_constraint documentation defines the codes: a is no action, r restrict, c cascade, n set null, d set default, so every c in that column is a cascade across a boundary. The prefix test assumes module names contain no underscore; a Django app called user_profiles needs a list of module prefixes instead. When each module has its own schema, replace the last condition with ch.relnamespace <> pa.relnamespace.

The query has two blind spots. It only sees declared keys, so a customer_id column that was never given a constraint does not appear, though it is just as much a dependency; and it lists edges, not loops. Add last_order_id bigint REFERENCES ordering_order (id) to customers_customer and the result gains a third row, but nothing in it says that customers and ordering now depend on each other. Finding a loop through three modules from a flat list of rows is a graph walk you do by hand.

When you drop one, what replaces it?

The column stays. What leaves is the guarantee, and three things should take its place.

An orphan check. Without the key, nothing stops billing_invoice from holding an order_id of 999 that no order has. A scheduled query finds them:

SELECT i.id, i.order_id
FROM billing_invoice i
LEFT JOIN ordering_order o ON o.id = i.order_id
WHERE o.id IS NULL;

A rule in the owning module. If ordering may now hard-delete orders, billing has to learn about it, usually through an event, and decide whether an invoice for a deleted order is an error or a record to keep.

Documentation that the dependency still exists. A dropped key leaves a column that looks like any other bigint. The next engineer, and the next AI agent, reads the schema and sees no relationship at all, which is how skipping foreign keys turns into a schema nobody can read.

Going back is possible once the orphan check returns no rows, since any orphan makes validation fail. ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID commits without scanning the table, though it still takes SHARE ROW EXCLUSIVE on both tables for that moment, and a later VALIDATE CONSTRAINT checks the existing rows; the ALTER TABLE documentation notes that validation takes only a SHARE UPDATE EXCLUSIVE lock on the table, plus a ROW SHARE lock on the referenced one.

Seeing module dependencies as a map instead of a query result

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. For this decision its job is to help you understand your schema at the scale of modules: connect it to the database, group the tables into one context view per module, and open the Context Map. Schemity reads one PostgreSQL schema per connection, so this works directly on the single-schema, prefix-per-module layout above; a schema-per-module database is one diagram per schema, and the map cannot join them.

A context view is a saved view of a subset of the schema's entities, and the Context Map is the view above it, where each context view becomes one box and each arrow is counted from the relations that cross between two of them. The pg_constraint query becomes a picture, with four things it could not show:

Declared and undeclared dependencies, side by side. An arrow is solid when every relation behind it is a declared foreign key, dashed when every one is a virtual relation, and carries a ringed count badge when the two mix. When an arrow has any virtual relations, the dependency drawer behind it says how many are declared and how many are virtual, and tags each virtual row.
Loops. A Cycles button lists the dependency loops between context views, shortest first and up to 50, including loops through three or more modules where no single arrow looks wrong. Picking one selects its modules and lights only the loop's arrows.
Direction for one module at a time. Selecting a module highlights what it depends on, dims what depends on it, and hides every other arrow; Shift+click (Cmd+click on macOS) adds more modules.
The boundary from inside a module. In a context view, the boundary indicator on an entity lists what crosses it: the keys it holds pointing out, and the entities outside that reference it. Picking a row lands on that entity in the main diagram.

That last view is where the drop decision gets made. When a key comes out of the database, draw it back as a virtual relation on the column that already carries it, and the dependency stays on the diagram and on the map, dashed: the table above told you to keep documenting it, and this is the documentation. An AI agent sees the same split over the database MCP server, where get_context_views returns each dependency with its relations, which of them are virtual, and the cycles.

The arrows count a dependency whether or not the database enforces it, because architecturally it is the same dependency. A dashed arrow is not a smaller coupling than a solid one; it is the same coupling with no check behind it. Each arrow's relations, the keys you would have to drop to separate two modules, are one click away, and the DDD context map you get from your foreign keys is the same picture read as an architecture rather than a list.

The decision itself is still yours. Keep the key while the modules share a database, give it RESTRICT instead of CASCADE, and drop it when a module leaves or needs its tables to itself, with the dependency still drawn where everyone can see it. A loop between two tables inside one module is a different problem, covered in circular foreign keys and the first row.

PostgreSQL: Changing an Integer Column to Boolean - In Place or a New Column, and the Four Errors on the Way

Son Tran — Mon, 19 Oct 2026 04:33:37 +0000

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

TL;DR: Change it in place with ALTER COLUMN ... TYPE boolean USING is_active <> 0, after dropping the integer default and anything that compares the column with a number: a CHECK, a partial index, a view. The in-place change rewrites the table under a lock that blocks reads, and any query that still binds the integer 1 fails the moment it commits. On a large or busy table, add a new boolean column, keep it in sync while you backfill it, and switch over to it in a transaction that takes milliseconds.

To change an integer flag to boolean in PostgreSQL, run ALTER TABLE users ALTER COLUMN is_active TYPE boolean USING is_active <> 0, but only after removing what ties the column to integers, such as its default, a CHECK (is_active IN (0, 1)), or a partial index or view with WHERE is_active = 1. Each one stops the statement with its own error, and the CHECK and the partial index fail with the same error, which names neither of them.

That covers the statement. The decision is whether to run it in place at all. The type change rewrites the whole table under an ACCESS EXCLUSIVE lock, and the moment it commits, a query that still sends the integer 1 fails. On a table with millions of rows, or an application deployed separately from its migrations, adding a new boolean column and swapping it in is the safer route. Everything below was run on PostgreSQL 18.3 in a throwaway container.

Why integer flags end up needing this migration

Integer flags usually come from somewhere else: a schema ported from MySQL, where BOOLEAN is an alias for TINYINT(1), a Rails app that started on MySQL, or a column someone added as smallint years ago "in case we need a third state". It works, and it stays that way until someone wants the column to read like what it is. Here is the table used in every example:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    is_active integer NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))
);
CREATE INDEX users_active_email_idx ON users (email) WHERE is_active = 1;
CREATE VIEW active_users AS SELECT id, email FROM users WHERE is_active = 1;

How do I change a column from integer to boolean in Postgres?

Try the obvious statement and PostgreSQL refuses four times, once for the missing cast and then once per object in the way:

Statement	Error
`ALTER COLUMN is_active TYPE boolean`	`column "is_active" cannot be cast automatically to type boolean`
`... TYPE boolean USING is_active::boolean`	`default for column "is_active" cannot be cast automatically to type boolean`
The same, after `DROP DEFAULT`	`cannot alter type of a column used by a view or rule`
The same, after `DROP VIEW active_users`	`operator does not exist: boolean = integer`

The first error is the missing USING clause: there is no automatic conversion from integer to boolean, so you have to say how. The second is the one that surprises people, because the USING clause is right. PostgreSQL converts the column's default separately and does not apply your USING expression to it, so DEFAULT 1 has no way to become a boolean. The third is the view, covered in detail in changing a column type used by a view.

The fourth comes from both the CHECK constraint and the partial index. PostgreSQL rebuilds each of them against the new type, and is_active IN (0, 1) and WHERE is_active = 1 now compare a boolean with an integer. The error does not name the constraint or the index, and dropping only the CHECK produces the same message from the index.

The migration that works drops every one of them, changes the type, and puts back boolean versions:

BEGIN;
DROP VIEW active_users;
DROP INDEX users_active_email_idx;
ALTER TABLE users DROP CONSTRAINT users_is_active_check;
ALTER TABLE users ALTER COLUMN is_active DROP DEFAULT;
ALTER TABLE users ALTER COLUMN is_active TYPE boolean USING is_active <> 0;
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;
CREATE INDEX users_active_email_idx ON users (email) WHERE is_active;
CREATE VIEW active_users AS SELECT id, email FROM users WHERE is_active;
COMMIT;

The CHECK does not come back: a boolean column already holds only two values, which is the reason for the migration. A view also loses its grants when dropped, so restore those after recreating it.

USING is_active::boolean or is_active <> 0?

Use is_active <> 0. PostgreSQL has a cast from integer to boolean, but none from smallint or bigint, so a smallint column such as is_admin cannot use the cast form:

SELECT 1::smallint::boolean;
-- ERROR:  cannot cast type smallint to boolean

So USING is_admin::boolean works on an integer column and fails on a smallint one. Comparing with zero works on every width, and it gives the same answer as the integer cast: 0 becomes false and any other value, including 2 and -1, becomes true. If your data has values other than 0 and 1, decide what they mean before the migration, because after it they are indistinguishable from 1.

ORMs write the cast form. Django's PostgreSQL backend appends USING "is_active"::boolean whenever a field's database type changes, so turning an IntegerField into a BooleanField works, and turning a SmallIntegerField into one fails with the error above; write that migration as RunSQL. Rails' change_column :users, :is_active, :boolean emits no USING at all unless you pass using: "is_active <> 0", or cast_as: :boolean, which writes USING CAST(... AS boolean) and has the same smallint problem. Passing default: true in the same call does not help: Rails adds SET DEFAULT true to the same ALTER TABLE, and PostgreSQL 18.3 still failed on the old default. default: nil does work: the DROP DEFAULT it adds to the same statement runs before the type change, and change_column_default can set true afterwards.

How to find everything that depends on the column

The error messages arrive one at a time, so list the dependents first. The objects PostgreSQL tracks are recorded in pg_depend against the column's attribute number, and pg_describe_object names each one:

SELECT DISTINCT pg_describe_object(d.classid, d.objid, d.objsubid) AS dependent
FROM pg_depend d
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE d.refclassid = 'pg_class'::regclass
  AND d.refobjid = 'users'::regclass
  AND a.attname = 'is_active'
ORDER BY 1;

On the example table it returned the partial index, the column default, users_is_active_check, the view, and on PostgreSQL 18 also users_is_active_not_null, the constraint 18 now records for NOT NULL. That one survives the type change untouched. Everything else on the list needs a boolean rewrite or a drop, and so would a row-level security policy or extended statistics on the column, which show up here too. Views built on top of active_users do not appear, since they depend on the view rather than the column; walk them with the recursive query in the view post linked above. Two kinds of code are not tracked at all: a PL/pgSQL function body with WHERE is_active = 1, which lets the type change through and fails the next time it runs, and your application's queries.

Does converting the column lock the table?

Yes, for the whole rewrite. integer and boolean are stored differently, so PostgreSQL writes a new copy of the table and rebuilds every index on it, holding ACCESS EXCLUSIVE the entire time: no reads, no writes. On 5,000,000 rows on a laptop, with emails like user123@example.com, the TYPE change took 15.4 seconds and recreating the partial index another 4.4. Nothing could read users for those 20 seconds.

It does not buy much space either. The table itself, without its indexes, went from 365 MB to 357 MB, because a 4-byte integer and a 1-byte boolean often land in the same aligned row slot. The reason to migrate is a column that says what it means, not storage.

The other cost arrives at commit. Once the column is boolean, the old application code fails on every query that sends a number:

SELECT count(*) FROM users WHERE is_active = 1;
-- ERROR:  operator does not exist: boolean = integer

INSERT INTO users (email, is_active) VALUES ('a@example.com', 1);
-- ERROR:  column "is_active" is of type boolean but expression is of type integer

A literal '1' in quotes still works, because PostgreSQL reads an untyped string as a boolean. A parameter typed as an integer does not: PREPARE q(int) AS SELECT count(*) FROM users WHERE is_active = $1 fails the same way. Whether your application hits this depends on the driver, since some send parameters as typed integers and some as untyped text, so test it rather than assume. Either way, the in-place change and the application change have to ship as one.

In place, new column, or keep the integer?

When the table is large, or the application deploys separately from its migrations, add the boolean as a new column and swap it in at the end:

ALTER TABLE users ADD COLUMN active boolean;
ALTER TABLE users ALTER COLUMN active SET DEFAULT true;

-- keep the new column in step with whatever the old code writes
CREATE FUNCTION users_sync_active() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.active := NEW.is_active <> 0;
    RETURN NEW;
END $$;
CREATE TRIGGER users_sync_active BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION users_sync_active();

-- backfill in batches, with VACUUM users between them
UPDATE users SET active = (is_active <> 0) WHERE id BETWEEN 1 AND 100000;
CREATE INDEX CONCURRENTLY users_active_email_idx_new ON users (email) WHERE active;
ALTER TABLE users ADD CONSTRAINT users_active_not_null NOT NULL active NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_active_not_null;

SET lock_timeout = '2s';
BEGIN;
DROP TRIGGER users_sync_active ON users;
DROP FUNCTION users_sync_active();
DROP VIEW active_users;
ALTER TABLE users DROP COLUMN is_active;
ALTER TABLE users RENAME COLUMN active TO is_active;
ALTER TABLE users RENAME CONSTRAINT users_active_not_null TO users_is_active_not_null;
ALTER INDEX users_active_email_idx_new RENAME TO users_active_email_idx;
CREATE VIEW active_users AS SELECT id, email FROM users WHERE is_active;
COMMIT;

The trigger matters: without it, old code inserting is_active = 0 would get active = true from the default, and rows it updates after their batch would go stale. The slow parts, the backfill and the index build, run without blocking readers, but each batch writes a new version of every row it touches, so the table grows until VACUUM catches up, and the dropped column's space is only reclaimed by a later rewrite. The final transaction took about 60 ms on the same 5,000,000 rows, since dropping a column and renaming are catalogue changes, and the old column's CHECK and partial index go with it. The NOT NULL ... NOT VALID form is PostgreSQL 18; on 12 to 17, prove it with a CHECK constraint as described in adding a NOT NULL column to a large table. What this route shortens is the lock, not the cut-over. After the swap, is_active is boolean, so code that still sends 1 fails exactly as it would in place, and code already moved to active loses that name. The simplest version keeps the new name: move the application to active, then drop is_active and skip the renames.

	In place	New column and swap	Keep the integer
Lock	`ACCESS EXCLUSIVE` for the rewrite, 20 s on 5M rows here	About 60 ms at the swap	None
Old application code	Fails at commit	Keeps working until the cut-over	Unaffected
Steps	One transaction	Backfill, dual writes, swap	None
Fits	Small tables, one deploy for app and schema	Large or busy tables	A column that may need a third state

Keeping the integer is a real option. With CHECK (is_active IN (0, 1)) the database enforces the same two values, and if a third state is plausible, an enum or a lookup table is the better target than boolean anyway.

How Schemity plans and reviews the change

The point of the table above is to see the impact of every change before it runs, and none of it is visible in a one-line migration. 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.

Change is_active from INTEGER to BOOLEAN in a diagram connected to the database, and the field dialog clears the 1 default, because it is not a valid boolean, and offers TRUE and FALSE in its place.

Saving the field also removes users_is_active_check, because 0 and 1 are not values a boolean can hold. The migration SQL it plans drops that constraint and the old default first, converts with USING "is_active" <> 0, which works on SMALLINT and BIGINT columns too, and sets the new default last.

0 and sets DEFAULT TRUE; planned changes list the altered column and the dropped check constraint, lint has no findings, and impact has 4"/>

Impact analysis then reports the cost before anything runs: a type change whose values may not survive the cast, a rewrite of users with its row estimate and size on disk, the lock that holds back reads and writes while it happens, and active_users as a view the database will refuse the change for.

Two limits are worth knowing. Impact analysis lists views, materialized views, triggers, functions and generated columns that read the column, but not a partial index whose predicate compares it with a number, so run the pg_depend query above as well. And Schemity's own migration is the in-place route without dropping the view or the index, so on this table the planned SQL still stops at both. For a large table, use the findings as the cue to write the new-column version in your migration tool, then paste it into the SQL migration drawer to have it analysed against the connected database without running it.

Which migration to write

Small table, application and migration deployed together: in place, in one transaction, with the default dropped first and USING col <> 0.
Large or busy table, or separate deploys: a new boolean column kept in sync by a trigger, batched backfill, an index built concurrently, and a swap of about 60 ms.
A flag that might grow a third value: keep the integer with its CHECK, or move to an enum, not to boolean.

What Breaks When a Database Diagram Reaches 1,000 Tables, and What We Redesigned

Son Tran — Mon, 19 Oct 2026 04:30:41 +0000

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

TL;DR: Around 1,000 tables a database diagram stops being a drawing problem and becomes a scaling problem. In Schemity, drawing every table at once, comparing every line with every other, redrawing a 624-relation table on every move and painting a 134-megapixel export all broke. After the redesign a 1,035-table diagram opens in about 10 seconds and idles at 1.1 to 1.7 GB on a 16 GB M1 Pro. The next limit found is the line-jump pass, which grows much faster than the diagram.

Going from a few hundred tables to a thousand is where a database diagram stops being a drawing and becomes a performance problem. On a 1,035-table, 1,822-relation PostgreSQL diagram, four things broke in Schemity: drawing every table at once, comparing every line with every other, redrawing a 624-relation table on every move, and painting the PNG export as one 134-megapixel canvas. Each one needed a redesign, and each redesign has a cost, measured below.

The result opens in about 10 seconds and sits at 1.1 to 1.7 GB with no CPU use while idle, on a 16 GB laptop. That is the user-facing answer. The rest of this post is the engineering behind it: what failed, what replaced it, what the replacement gave up, the export that needed 10.4 GB, and where the design breaks next, because it does.

The test diagram and how it was measured

The diagram is a generated ERP-style PostgreSQL schema, built through Schemity's MCP server for stress testing rather than taken from a customer: 33 business domains from identity and billing to payroll and logistics, laid out in one diagram. It is the same model the app's stress test loads, and its limits as a benchmark are spelled out near the end.

What	Count
Tables	1,035
Columns	9,407
Relations (foreign keys)	1,822
Most relations on one table	624, on `iam_users`
Check and unique constraints	1,124
Legends (domain boxes)	33
Diagram size	15,760 by 14,178 px
Diagram file on disk	13.2 MB of JSON

The machine is an Apple M1 Pro with 8 cores and 16 GB of memory, on macOS 27, running Schemity 2.12.1. Memory is the physical footprint macOS reports, the number Activity Monitor shows, summed over the app's own process and the WebKit processes that draw its window. It was sampled at least once a second while each step ran, with a pause of about 20 seconds between steps so each one shows up on its own in the log.

How much memory does an ERD for a large database need?

About 1.1 GB once it is open, rising to 1.6 to 1.7 GB after you have moved around it, with short peaks above that:

Step	Time	Peak memory	Memory after	CPU
App open, home screen	-	183 MB	183 MB	none
Open the 1,035-table diagram	about 10 s to usable	1.9 GB	1.1 GB	12 s, about one core
Move across it with the minimap and search	smooth	3.1 GB	about 1.6 GB	44 s over the whole step
Drag the 624-relation table and drop it	works, with visible lag	2.4 GB	1.6 GB	11 s over the whole step
Open Lint on the whole schema	instant	1.9 GB	1.7 GB	11 s over the whole step
Idle with the diagram open	-	-	-	none

Almost all of the memory sits in WebKit's content process, which holds the canvas and the diagram's data. The app's own process stayed between 33 and 109 MB throughout, and the GPU process rose to about 290 MB while the view was moving and fell back below 30 MB when it stopped. With the diagram open and nothing happening, the CPU counter did not move for a minute: no CPU while idle. Each row of that table is the product of one of the redesigns below.

How do you open a 1,000-table diagram without drawing every table?

You draw only what is on screen. Every table in a Schemity diagram is cached as a bitmap, so panning moves pictures instead of redrawing text. Before 2.12.1, opening a diagram rasterized every table into that cache whether it was near the screen or not, twice: once when it mounted and again when the diagram font finished loading. On 1,035 tables that was 2,070 bitmap renders and 13.7 seconds of frozen canvas after the diagram had already appeared.

Now a table outside the viewport is not drawn at all. It keeps a note of what it still owes, the text layout the font change asks for and its bitmap, and pays it in the frame where it first scrolls into view. Opening costs only the tables you can see, which is how the same diagram became usable in about 10 seconds.

The cost is memory that grows as you explore. A table that has been drawn keeps its bitmap, so panning back over it is cheap, but the cache only grows. In this run, moving around the diagram took it from 1.1 GB to about 1.6 GB, and the 3.1 GB peak in the table came during that first trip across.

Why does comparing every line with every other stop working?

Because the number of pairs grows with the square of the lines. Where two relation lines cross, Schemity draws a small hop so you can tell which line goes where, and finding the crossings means testing segments against each other. The 1,822 relations here are 21,254 straight segments, which is 226 million pairs, and testing them all took 1.4 seconds, after every single drop.

The replacement sorts the segments by their left edge and sweeps across them, so a segment is only tested against the segments whose horizontal range overlaps its own. It finds exactly the same crossings, in about 0.2 seconds. The pass was also moved out of the frame that shows the drop: the table lands and the snap guide clears at once, and the hops follow a moment later. Until they do, a line that just moved is drawn without hops rather than with hops at its old crossings.

That sweep has a known weakness, and it is the first limit in the scaling run further down: it only prunes by horizontal position. Segments stacked in the same vertical band are still tested against each other, and a bigger diagram stacks more of them.

How do you drag a table with 624 relations?

By not drawing the real lines while it moves. A high-degree table is the worst case for an interactive diagram, and iam_users, with 624 relations, made every part of the drag path visible:

Every move re-rendered, committed and redrew all 624 routed relations.
Starting and ending a drag re-rendered every one of the 1,035 tables, twice, because of one diagram-wide dragging flag, at over a second of React work each time.
Every table carried its selection ring, resize handles and other controls whether selected or not, about 5,000 components, and two of them loaded an icon before drawing nothing, so every drop set off about 2,000 icon loads.

Now the dragging state is shared by reference instead of passed to every table, the controls exist only on the selected table, and a table with 30 or more relations is dragged through a lightweight preview. Its real lines are hidden, and one shape draws a straight line from its centre to the centre of each related table, re-aimed directly on each move with no routing and no React render. Aiming all 624 lines costs under 0.1 ms per frame, and on drop the 624 real routes are recomputed in about 7 ms and drawn once.

This is a deliberate trade of looks for usability. For the length of the drag the lines run straight through whatever lies between, instead of routed around the other tables with clean right angles, so the diagram looks rougher than it really is. The properly routed lines come back the moment you drop. Even so, the drag is the one step that does not feel smooth: the table follows the pointer with a visible lag instead of stalling as it did before, but it is not the fluid drag a small table gets.

Why did exporting the diagram to PNG need 10 GB?

Because the image was drawn the way a browser draws anything: into one canvas. Schemity's window is a web view, and a 1,035-table diagram exported at the largest scale the pixel budget allows, about 0.77 of its size on screen, is 12,208 by 10,994 pixels, 134 million pixels in one canvas. Before 2.12.1 the export always asked for its usual 2x scale, a 909-million-pixel canvas the browser refused, and silently wrote no file. 2.12.1 fits the scale to the pixel budget, and the export works, but the whole app went from 1.7 GB to a 10.4 GB peak, more than an 8 GB laptop has. An instrumented build showed where most of the jump came from: drawing the diagram into the canvas took 3.2 seconds and added about 3.3 GB, and WebKit's PNG encoder added about another 3.2 GB. JPEG, which goes through the same canvas, cost about the same.

We confirmed it was the canvas and not the diagram by exporting the same diagram as SVG, which writes the drawing out as text and never creates a canvas. That export added about 240 MB. So the next release no longer asks the browser to paint the image. On desktop, PNG and JPEG export now take the SVG export and render it natively with resvg, an SVG renderer written in Rust, encoding the PNG row by row so the pixels are never copied whole:

Export of the 1,035-table diagram	2.12.1	Next release (test build)
PNG peak, whole app	10.4 GB	2.9 GB
JPEG peak, whole app	about the same as PNG	2.8 GB
SVG, added while exporting	about 240 MB	unchanged
Image size	12,208 by 10,994 px	the same

The next-release figures come from a test build with the export code optimized as it will ship. The renderer alone, timed outside the app, peaks at about 925 MB and takes 8.7 seconds for this diagram, against about 7 seconds for the whole old export. One detail cost a factor of three: with every installed font available for fallback, a copy of the diagram font installed on the test machine competed with the bundled one and was mapped from disk again for each of the 27,811 text elements, which took the render from 8.5 to 29 seconds until the bundled copy was made the only one. The price is 2.3 MB more app binary, for the renderer and that bundled font. Until the release reaches you, export a diagram this size as SVG: it is the cheap path today.

How long does it take an AI agent to route 1,822 relations?

It used to be never. Schemity's MCP server lets an AI agent sort a large schema into domains with one call, group_entities, which places the legends and then routes every relation around the tables. On 1,035 tables and 1,822 relations that call never finished. Each re-routing pass rebuilt its record of where every other route already ran, once per relation, and re-totalled the whole layout twice for every attempt.

The router now takes one route out of a running count and puts it back, and prices only the line that changed. Its A* search keeps its best costs in flat arrays reused across searches instead of hash maps. Routes came out byte-identical on 162- and 400-table diagrams, and 400 tables went from 26.8 seconds to 3.6. Past 500 relations the re-routing stops after 2,000 searches in total, a count rather than a clock, so the same input still gives the same routes. When that change landed, 1,035 tables in 33 domains went from over 3 minutes to about 85 seconds end to end. A later change compiled the routing engine for speed instead of size, which cut a full 1,822-relation routing pass from 33.2 to 22.0 seconds for 0.4 MB of app binary; the end-to-end call was not re-timed after it.

Does opening on the Context Map use less memory?

In the next release, yes: a diagram can open on its Context Map instead of the full canvas. A context view is a saved view of the diagram that shows only the tables you picked and the relations between them, and the Context Map shows every context view as one box with its table count, with arrows between boxes that come from the foreign keys. The canvas behind the main diagram is now built only the first time something other than the map is shown, so a diagram set to open on the map draws none of its 1,035 tables on the way in.

To measure it, this diagram's 33 domains became 33 context views, one per legend, which grew the file from 13.2 to 15.3 MB. The diagram was then set to open on the map, with Open on in its connection settings, and opened in a local release build of the next version, sampled the same way:

Step	Time	Peak memory	Memory after
Open the diagram on its Context Map	about 4 s	0.74 GB	0.46 GB
Enter the Sales context view, 36 tables	instant	0.69 GB	0.47 GB
Switch to the main diagram, all 1,035 tables	about 3 s	1.5 GB	1.2 GB

Against opening straight onto the main diagram in 2.12.1 (1.9 GB peak, 1.1 GB after, about 10 s), opening on the map in the next version's build settles at about 40% of the memory in less than half the time. Someone who works in one domain settles under half a gigabyte, which matters more on an 8 GB laptop than any single peak. The full diagram is still one step away, and with the data already loaded it takes about 3 s.

Where does the architecture break next?

To find out without a bigger real schema, the engine was run on copies of this diagram: the same 1,035 tables tiled 2, 4 and 8 times across a larger canvas, each copy renamed so nothing collides, keeping the real routed lines and one 624-relation hub per copy. These are engine timings on the same M1 Pro, run in Node outside the app, so they exclude drawing:

Tables	Relations	Line jumps, after every drop	Lint, whole schema	Recompute the hub's 624 relations on drop	Drag preview, per frame
1,035	1,822	0.19 s	0.04 s	6.9 ms	0.06 ms
2,070	3,644	0.48 s	0.07 s	5.4 ms	0.04 ms
4,140	7,288	2.1 s	0.13 s	5.6 ms	0.07 ms
8,280	14,576	5.5 s	0.28 s	8.2 ms	0.04 ms

Three of the four scale well. Lint grows in step with the schema. The drag of a 624-relation table costs the same at 8,280 tables as at 1,035, because the drag only touches that table's own relations: its cost follows the table's degree, not the diagram's size, which is the property the preview was built for.

The line-jump pass does not. Eight times the relations cost 29 times the time, and it grows faster than the diagram from the first doubling: 2.6 times the time for twice the relations, then 4.3 times for the next doubling. The cause is the horizontal sweep from earlier, which still tests every pair of segments that share a vertical band, and a larger diagram stacks more of them. How much depends on layout: the copies here are tiled in a grid, so they share columns, and a wider or taller arrangement would give a different curve. The pass runs on the main thread after any change to the relations, a drop, an edit or an undo, and once synchronously while the diagram opens, so at 8,280 tables it would hold the window for about 5.5 seconds each time in Node. WebKit was not measured at that size. A grid that also prunes by vertical position would remove that weakness.

What this benchmark does not show

Publishing the numbers is only useful with their limits next to them:

One synthetic schema. The model is generated, and its topology is cleaner than a real legacy database: every table sits in exactly one domain, and Schemity's own router laid out every line. A real schema can have tables that relate to everything, inferred relations with no foreign key behind them, and a layout nobody planned.
Modest density. 1,822 relations over 1,035 tables is about 1.8 per table. The single 624-relation hub is the deliberate worst case, but a schema that is dense everywhere would stress the line-jump pass sooner.
One machine. Every number comes from a 16 GB M1 Pro. There are no measurements here for an 8 GB laptop, Windows or Linux, where the web view is a different engine.
No full-app curve past 1,035 tables. The scaling table is engine-only, and it repeats one topology rather than growing a new one. Memory, drawing and export were not measured on the larger copies.
Test builds for what is unreleased. The native export and Open on figures come from builds of the next release, not from a shipped version.

Is it worth keeping a 1,000-table schema in one diagram?

Yes, in one file, and no, not on one screen. The point of loading the whole schema is that nothing is missing when you go looking: every table and every foreign key between domains is in the diagram, and on a real database Schemity reads them from the live schema, so you can understand your schema without first deciding which half to ignore. Reading it is a different job, done one domain at a time.

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. A payroll engineer can open the payroll context view without the other 1,000 tables around it. Context views and legends are how this diagram's 33 domains stay readable, and Lint checks all 1,035 tables at once whichever view you are in. The diagram file itself is plain JSON, 13.2 MB for this schema, so it lives in Git like the code it describes.

If the large schema is one you inherited, grouping a legacy database by domain covers how to get from a wall of tables to named domains, with an AI agent doing the first pass. And for what a diagram of a live database should and should not let an agent touch, see what a database MCP server should let an agent do.

DEV Community: Son Tran

How to Track Schema Changes in PostgreSQL: DDL Logging, an Event Trigger or a Schema Snapshot

How to track schema changes in PostgreSQL

Does log_statement = 'ddl' catch every schema change?

How do you log DDL changes with an event trigger?

Why does a pg_dump diff show changes that did not happen?

Seeing what changed since you last saved the diagram

Related reading

How to Auto-Update an updated_at Column: MySQL ON UPDATE vs a Database Trigger vs the ORM

Why DEFAULT CURRENT_TIMESTAMP is not enough

How to auto update updated_at column: the three options

MySQL: ON UPDATE CURRENT_TIMESTAMP

PostgreSQL, SQL Server and SQLite: a trigger

The ORM: auto_now, @updatedAt and Rails timestamps

Which should you use: MySQL ON UPDATE, a trigger or the ORM?

How Schemity keeps updated_at in the schema

Related reading

SQL Server INT to BIGINT Primary Key: Four Ways to Change It and What Each One Costs

How close are your INT keys to the limit?

Why does ALTER COLUMN fail on an INT primary key?

How do you change an INT primary key to BIGINT in SQL Server?

Restate NOT NULL and COLLATE on every ALTER COLUMN

Which way should you widen it?

How Schemity plans the change and shows what it costs

INT to BIGINT on SQL Server, in one list

Round-Tripping TPC-DS and a Snowflake Feature Schema Through a Database Diagram: What Snowflake Lost and What We Fixed

Which two schemas, and which path?

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

What failed before the fixes?

Why did a table with a clustering key disappear?

Why did a staging table become permanent?

Why did TPC-DS's keys start numbering themselves?

Why did a case-insensitive email stop matching?

What happened to the sequence?

What does the round trip give you now?

How do you see relationships in a Snowflake schema with no foreign keys?

What still differs, and why?

What this benchmark does not show

How to rerun it

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

Which two schemas, and which two paths?

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

What failed before the fixes?

Why does a declared type matter if SQLite ignores it?

What happened to Jellyfin's partial and expression indexes?

Why did a lock table start skipping ids?

What is an FTS5 virtual table to a diagram?

Which bugs did only a refresh find?

What does the round trip give you now?

What still differs, and why?

What this benchmark does not show

How to rerun it

Round-Tripping Bitwarden and Smartstore Through a Database Diagram: What SQL Server Lost and What We Fixed

Which two schemas, and which two paths?

How do you prove a rebuilt SQL Server schema is the same?

What failed before the fixes?

Why did Import SQL read a procedure as a table?

Why did the script fail under sqlcmd?

Why is varchar not nvarchar?

What happens to a filtered unique index?

What about INCLUDE, DESC and clustered indexes?

Which bugs did only a refresh find?

What does the round trip give you now?

What still differs, and why?

What this benchmark does not show

How to rerun it

Round-Tripping Invoice Ninja and Sylius Through a Database Diagram: What MySQL Lost and What We Fixed

Which two schemas, and which two paths?

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

What failed before the fixes?

Why did MySQL refuse an index on a TEXT column?

Why did keys that differ by a trailing space stop being duplicates?

Why did timestamp(6) lose its microseconds?

Why did double(8,2) stop rounding?

Which bugs did only the live path and a refresh find?

What does the round trip give you now?

What still differs, and why?

What this benchmark does not show

How to rerun it

Round-Tripping Mastodon and Saleor Through a Database Diagram: What Broke and What We Fixed

Why did serial ids come back as `GENERATED ALWAYS`?

What does Django's `BY DEFAULT` identity need from a diagram?

Why does every Django foreign key say `DEFERRABLE INITIALLY DEFERRED`?

How do you keep `lower(username)` and `text_pattern_ops`?