Schema Linting vs Migration Linting: Which Database Problems Each One Can See

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

TL;DR: A migration linter parses one SQL statement and answers whether running it is safe, which is why it cannot see a foreign key whose type does not match the key it references, or a unique constraint that enforces nothing because a nullable column sits in it. Those are facts about the whole schema, so the only checks available today are a human reviewer with a checklist and production. Schemity lints the ERD instead: seventeen rules, run offline against the model, grouped by what each finding costs, and marked in the margin next to the exact entity and field row concerned.

A migration linter reads the statement you are about to run. A schema linter reads the model you already have. Both are useful, and only one of them can tell you that the int foreign key in orders points at a bigint primary key in customers - because that is not a fact about any single statement, it is a fact about two tables at once.

This distinction is easy to miss, because the tooling that exists is almost entirely on one side of it. The mature, well-adopted tools in this space check migrations: they parse the SQL in your migration file and warn about locks, rewrites, and changes that break clients still running the old code. That is genuinely valuable, and it is a different question from the one that keeps costing teams years of cleanup work.

Is there a linter for a database schema, not just for migrations?

Not in the same shape, and the reason is structural rather than an oversight. Look at what a migration linter is given: one file, containing statements, arriving one deploy at a time.

Squawk, the most established of them, ships 43 rules whose stated focus is "ensuring safe migrations and warn about statements that could block reads / writes or break existing clients." The rule names tell you the shape of the job: require-concurrent-index-creation, adding-field-with-default, ban-drop-column. A few of them do reach into design - prefer-bigint-over-int and prefer-timestamptz are both there - but they fire on the column being added, in the statement being read. A schema that already has three hundred timestamp columns produces no findings at all, because none of them are in the file.

That is the boundary. A migration linter sees the delta. It cannot see the schema, because the schema was never handed to it.

So what checks the schema? In practice, two things. A human, and production.

The human version is more formal than most teams admit. GitLab's database review guidelines staff it as two named roles: a Database Reviewer does the first pass and relabels the merge request, then a Database Maintainer does the final review and approves it. The checklist those roles work through includes items like "Check indexes are present for foreign keys" and "Add foreign keys to any columns pointing to data in other tables, including an index." Those are eyeball checks, run per merge request, by two people, forever. It works - and it works because GitLab pays for it with a standing process most teams do not have.

The production version is the one everybody has by default. On MySQL it announces itself as errno 150, Cannot add foreign key constraint, at the moment you try to add a constraint between an int column and a bigint key. On PostgreSQL nothing fails - the constraint is created happily, and you find out later, when the join that should have used an index does not, or when the parent's id sequence passes 2,147,483,647 and the child column cannot hold the values any more. GitLab's own issue tracker carries a long line of tickets titled Prepare foreign key constraints for bigint - one per column, each a separate piece of work, because converting an int foreign key on a large table is not an afternoon.

Why a foreign key type mismatch survives code review

Because the defect has two halves and the review has one.

The migration under review says ADD COLUMN customer_id int. There is nothing wrong with that line. It becomes wrong only in the presence of customers.id bigint, which lives in a different migration file, written by a different person, possibly three years earlier. To catch it, the reviewer has to already know the other type, or stop and go find it - for every foreign key, in every migration, forever.

The same structure repeats across the problems that actually cost money:

• A unique constraint containing a nullable column reads as correct in any schema dump. UNIQUE (tenant_id, email, deleted_at) looks like a rule. It enforces nothing for live rows, because NULLs do not compare equal and deleted_at is NULL for every row that is not deleted. Reading the constraint tells you nothing; you have to read the constraint and the nullability of each of its columns together.
• A link table with nothing covering its foreign key pair looks like a table. The foreign keys are present and correct, and they check that the referenced rows exist - they have no opinion about how many times the same pairing appears.
• A default that its own column's check constraint forbids is two objects created in two statements, and every insert relying on the default fails.

None of these are subtle. They are all mechanical to detect. They survive because the place where a schema is normally read - a migration diff, a dump, a review comment - shows one object at a time, and each of these is a statement about two.

What a schema linter checks, and what a migration linter checks

The two are complementary, not rivals, and the honest version of the comparison says so plainly: nothing in a schema linter tells you that your ALTER TABLE will hold an ACCESS EXCLUSIVE lock for eleven minutes on a table with forty million rows. That is exactly what Squawk and Eugene are for, and a team shipping migrations to a busy database wants one of them in CI regardless.

What Schemity's seventeen rules check

Schema lint in Schemity runs against the diagram you have open, on your machine, with no connection required - every check it makes is a comparison between things already written down, so it needs the schema rather than the server. Seventeen rules ship today, each with a permanent id, and they are grouped by what the finding costs you:

• Fails at runtime - fk-type-mismatch (the int to bigint case, and varchar(36) to uuid), default-contradicts-check, and fk-cycle-all-not-null, where a cycle of NOT NULL foreign keys - a table referencing itself is the simplest case - leaves no row that can be inserted first.
• Not enforced - no-primary-key, unique-includes-nullable, enum-like-no-constraint for a status or type column with nothing restricting its values, fk-array-column for a PostgreSQL array holding foreign keys, and two rules for link tables.
• Costs - redundant-index for an index whose columns are a leading prefix of a wider one, money-as-float, and timestamp-not-timestamptz.
• Convention - id-not-primary-key, composite-pk-with-inbound-fks, nullable-boolean, created-at-nullable-or-no-default, and nullable-string-no-uniqueness, where absence has two spellings because a blank form field submits an empty string rather than NULL.

Every rule is a row with a switch, and the id is the name you see - there is no severity column to argue with and no numeric score. Two of the seventeen are PostgreSQL-only, and rules that do not apply to your engine are hidden from this tab rather than shown switched off, which is why all seventeen appear above on a PostgreSQL 17.5 connection. What is not in the list matters too: there is no rule about whether a foreign key is indexed, so GitLab's reviewers still have that particular checklist item. Seventeen mechanical checks is not a replacement for a database reviewer. It is the part of their job that a machine should have been doing all along.

The link-table rules are worth singling out, because a naive version of this check is worse than none. Both correct junction shapes are accepted: a composite primary key over the foreign key pair, and a surrogate id plus a unique constraint over that pair. Uniqueness is read from wherever it actually comes from - a unique constraint, a unique index, a single-field flag, or the primary key itself, the same sources behind the U marker on the canvas. That leaves the two ways a link table genuinely goes wrong: nothing covers the pair, or something covers the wrong columns.

Why findings are grouped by consequence instead of severity

Because a severity scale asks you to accept somebody else's ranking of your schema, and the first time it ranks a deliberate design as HIGH, you stop reading it.

Grouping by consequence says what happens instead and lets you decide. A nullable boolean is reported as three states where two were intended, not as a mistake. A unique constraint containing a nullable column is reported as the rows it exempts, which is the sentence you check your intent against - because the same constraint shape is correct for an optional unique phone number and catastrophic for a soft-delete timestamp, and no linter can tell which one you meant.

Here is what that looks like on a real schema - 59 entities, 71 relations, PostgreSQL 17.5, seventeen findings:

Read the three findings in order and none of them says "wrong". The self-referencing foreign key finding states the mechanical consequence - there is no row for the first one to point at - and then offers the fix that makes the design work, which is a nullable column giving the root row somewhere to start. The link table one names the pair nothing covers. The unique one names the exact column whose NULLs open the gap. Eleven of the seventeen findings here sit under "Not enforced", which is the group worth dwelling on: every one of those constraints looks correct in a schema dump.

That is also why every finding carries an Ignore beside its Show on canvas, and why whole rules can be switched off per diagram. Both are saved in the diagram's JSON file, so the decision travels with the schema and shows up in a pull request like any other change - the team records it once, in the place the schema lives, instead of every new reader rediscovering the constraint and re-arguing it. Ignores are keyed by name rather than by internal id, so renaming an entity brings its ignored findings back: the thing you decided about is not quite the thing you have now.

Where a finding appears

In the margin of the diagram, next to the thing concerned. A thin colored strip sits a few pixels to the left of the flagged entity - spanning the full height for a finding about the entity, marking one field's row exactly for a finding about a field, so orders.total being a float points at total and not at orders. An entity carrying more than one finding gets a count beside the strip. The strip is colored by category and each category also has its own fill, solid, hollow or dotted, so the marking survives greyscale printing and reads the same for a color-blind reviewer.

This is the part that a list in a terminal cannot do. A linter that prints orders.total: money-as-float hands you a string you then have to translate back into the picture you were just looking at.

The count badge is the other half of it, and it works with the drawer shut:

Closed, the diagram is just the diagram - no strips, no highlighting, nothing competing with the schema for attention. The badge still reads 9+ and takes its color from the most serious category present, so a diagram carrying only naming conventions stays grey and quiet while one that will fail at runtime turns red. A new problem announces itself instead of waiting to be looked for, and looking costs one click.

Lint runs where the schema is edited, which means the main view. A context view is a focused subset of the main diagram, read-only by design so that arranging a perspective can never change the schema - so a context view is not linted, and neither are database views or read-only diagrams opened without their connection.

From a finding to a migration

Lint fixes nothing by itself, and it should not. It reports, you decide, and the change goes through the normal path: edit the ERD, review the generated migration SQL diff, apply it. On a populated table, that diff is also where you find out whether the duplicates a missing constraint allowed are already in the data.

Which puts the check in a different place in time than every other tool here. A migration linter runs in CI, after the design decision has been made and written into a file. A database reviewer runs at merge request time, after the same thing. Reading seventeen rules against the model means the int that should have been a bigint gets caught while it is still a column in a diagram, before it is a column in a table with four hundred million rows in it.

Each rule reports at most 200 findings and says so when it stops, rather than truncating silently - which, on a schema reverse-engineered from a database nobody has linted before, is a number you will meet.

If you want the neighbouring cases: the difference between a schema that looks the same in two environments and one that is the same is the other question a dump cannot answer, referential actions are invisible in most diagrams for exactly the same reason a type mismatch is, and the choice between a composite primary key and a surrogate id on a junction table is the decision the link-table rules are careful not to make for you.