DEV Community: Son Tran

Column Comments in PostgreSQL and MySQL: How to Document Columns Without a Migration

Son Tran — Fri, 21 Aug 2026 03:21:45 +0000

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

TL;DR: The database has a built-in place to document a column - COMMENT ON COLUMN in PostgreSQL, the COMMENT attribute in MySQL - and almost nobody fills it in, because a sentence of prose has to travel the same path as a schema change: a migration file, a review, a deploy. Schemity keeps field descriptions in the diagram instead, where editing one generates no SQL, reads existing database comments in on import, and exports the result as a data dictionary.

You can document a database column without touching the database: write the description in the model rather than in the schema. That sounds like a dodge until you price the alternative. The database's own mechanism for column documentation, COMMENT ON COLUMN in PostgreSQL and the COMMENT attribute in MySQL, sends a sentence of prose down exactly the same path as a change to how data is stored - a migration file, a code review, an approval, a deploy window - and on MySQL it does something worse than that. Schemity keeps field descriptions in the diagram, where editing one produces no SQL at all.

This is why so many production schemas have thousands of columns and almost no comments. Not because nobody wanted to write them. Because writing one costs a deploy.

How do I document a database column without running a migration?

Keep the description in the model rather than in the storage engine. A field description is a fact about what the column means to your team; it changes no type, no constraint, no index, and nothing about what the database will accept. When it lives in the diagram, editing it is like editing a comment in a code file: you change it, review it in the same pull request as everything else, and nothing has to run against production for it to take effect.

The moment that description is a column comment, it stops being prose and becomes DDL. Now it needs a migration file, and the migration needs a reviewer, and the reviewer is looking at an ALTER TABLE against a live table. Everyone in that chain is correct to be careful, which is the problem: the care is proportionate to a schema change, and this is not one.

Why a column comment is priced like a schema change

PostgreSQL gets this closest to right. COMMENT ON COLUMN invoices.voided_at IS 'set when finance reverses an invoice' is a standalone statement that touches only the catalog. It is still a migration in every workflow where migrations own schema changes, but it is a cheap and safe one.

MySQL has no equivalent. The comment is an attribute inside the column definition, so changing it means ALTER TABLE ... MODIFY, and MySQL's own manual states the trap plainly: "Attributes present in the original definition but not specified for the new definition are not carried forward." The manual's example is a column defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column', modified with the intention of changing only the type:

ALTER TABLE t1 MODIFY col1 BIGINT;

Attribute	In the original definition	After that statement
Data type	`INT`	`BIGINT`
`UNSIGNED`	present	dropped
`DEFAULT 1`	present	dropped
`COMMENT 'my column'`	present	dropped

Three attributes vanish and the statement is perfectly valid. There is no error, no warning, and no comment-only syntax to reach for. To attach one sentence of documentation to a MySQL column, the migration has to restate the column's entire definition correctly, which means the documentation change is now capable of altering how data is stored. That is the reverse of what anyone wanted.

Why ORMs keep declining to support column comments

The obvious escape is to let the ORM manage comments alongside everything else, and the ORMs have been declining for years. Drizzle has three separate open requests for it - issues 886, 1840 and 5203, the last opened on 1 January 2026 - and the newest one argues from an angle that did not exist when the first was filed: "In modern projects, database comments are no longer only for humans - they are increasingly important machine-readable context for AI-powered tooling." node-db-migrate's request, issue 558, has been open since March 2018 carrying the labels "Nice to have" and "Not Planned". Doctrine's migrations have their own long-running report of comments disappearing from generated migrations.

None of these maintainers are wrong. From inside a migration tool, column comments genuinely are niche: they are the only part of a column definition that no query result depends on. The pattern that emerges from eight years of open issues is not neglect, it is a category error. Documentation keeps being filed as a schema feature, gets ranked against schema features, and loses every time.

Where a field description belongs instead

In Schemity, a field carries a description of its own, the same way entities and legends already carry markdown descriptions. It is a text box in the field editor, next to the type and the default, and the sentence it holds is the one a MySQL migration would have had to restate a whole column definition to attach.

That description is the kind nobody ever writes into a column comment, because it is the reasoning behind a type choice rather than a definition of the column, and it would need a deploy. It also answers the question a reader of content TEXT actually has.

A documented field is then visible without opening anything: a bar on the leading edge of the row marks any field that has one, so which parts of a table are documented is a glance down a column rather than an audit, and the bar is drawn into SVG exports too.

One marked row out of nine is the honest picture of most schemas, and it is readable at a glance precisely because the mark is absence-shaped: you are looking for the fields that have no bar.

The important property is what does not happen. Descriptions live in the diagram's JSON file and never reach the database, so writing one produces no migration to review and no statement to run. This was a deliberate reversal on our side rather than a design we got right first time. Schemity used to write descriptions back as column comments, and that produced exactly the migration described above - on MySQL, the worst-shaped statement in the whole product, restating an entire column definition from the diagram just to attach a sentence. It also lost work: because comments came back from the database on every re-sync, a description could be silently overwritten, invisibly on PostgreSQL and MySQL and permanently on SQL Server and SQLite.

Reading still goes one way. Importing an already documented schema arrives documented, because comments that exist in the database are read in. They are simply never written back.

When the comment really does belong in the database

There is a real case on the other side, and the Drizzle issue names it: if the reader is a program that introspects the live database - an agent connecting to a schema it has never seen, a catalog crawler, a BI tool reading the information schema - then the comment has to be in the database, because that is the only place the reader looks. A description in a diagram file it cannot open is worth nothing to it.

If that is your goal, write the comments as migrations and treat them as schema changes, deliberately. What you should not do is adopt that cost by accident for documentation that only people will ever read, which is the situation almost every team is actually in.

	Comment in the database	Description in the model
Read by	Anything that introspects the catalog	People, and any export you generate
Cost of an edit	Migration, review, deploy	Save the file
MySQL cost of an edit	Restating the full column definition	The same as any other engine
Survives a schema refresh	Yes, it is the source	Yes, it is not overwritten
Reviewed in the pull request	As DDL	As a diff in the diagram JSON

Getting the documentation to the people who need it

A description nobody can reach is not documentation, which is the fair objection to keeping it out of the database. The answer is export rather than storage: the diagram exports as a data dictionary in HTML, Markdown and Excel, covering every column with its type, key role, nullability, default and description alongside the constraints and relationships. The export follows the active view, and a context view is a saved, focused subset of the schema showing only some entities and the relationships between them, so documenting one context produces a document about that context rather than the whole database.

It also ends by counting what is not written down yet: how many entities and fields carry a description, and the names of those that do not. It states the numbers and stops there, which is the only honest way to report on documentation coverage.

That closing count is the part that changes behaviour, because the reason columns go undocumented was never that people did not care. It was that the cheapest place to write it down had a deploy attached, so the note went into a wiki page instead and drifted. Take the deploy off the description and the note goes where the schema is - and then the data dictionary is a document you generate rather than one you maintain.

Circular Foreign Keys: Why the First Row Cannot Be Inserted

Son Tran — Wed, 19 Aug 2026 01:48:01 +0000

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

TL;DR: When foreign keys form a cycle and every column in it is NOT NULL, each insert needs a row that does not exist yet, so an empty database can never take its first row. The cycle is a property of the whole graph rather than of any one relationship, so nobody spots it by looking at the diagram - it surfaces at seed time on a fresh environment. Schemity's fk-cycle-all-not-null lint rule computes it from the open ERD offline and marks the entities involved in the margin, and the fix is a modelling decision: make one side nullable, defer the check on an engine that can, or move the reference into a third table.

A cycle of foreign keys is legal to create and impossible to populate. If departments.manager_id is NOT NULL and references employees, and employees.department_id is NOT NULL and references departments, the schema is valid, the diagram is tidy, the migration applies cleanly, and the database will never accept a single row. Each insert needs a row that does not exist yet.

That failure has a specific arrival time, and it is not review. It arrives the first time somebody points the seed script at an empty database: a new staging environment, a contributor's local machine, a fresh tenant, the disaster-recovery rehearsal. Production is fine, because production was populated years ago by whoever fought through it once. The defect sat in the schema the whole time and only the empty case exposes it.

It is also old. On 22 June 2000, Eric Du asked the PostgreSQL mailing list why he could not create two tables that were foreign keys for each other - his INITIALLY DEFERRED attempt failed at CREATE TABLE with ERROR: Relation 't2' does not exist, because deferral postpones checking data, not the existence of a table that has not been created yet. Twenty-six years later the modelling version of the same knot is still being written up: a clear description of it puts the problem in one sentence, that running two independent inserts will not work because you cannot insert into department without a manager_id and cannot insert into employee without a department_id.

Can two tables have foreign keys to each other?

Yes, and this is worth separating from the insert problem, because the two get conflated constantly.

Creating the pair is a DDL ordering question. The first CREATE TABLE cannot reference a table that does not exist, so the second constraint is added afterwards with ALTER TABLE ... ADD CONSTRAINT. That is a mechanical detail, it works on every engine, and once both constraints exist the catalog is perfectly happy. Nothing about the shape is rejected.

Inserting into the pair is a different question with a different answer, and the answer depends entirely on one thing: whether every foreign key column in the cycle is NOT NULL.

If one of them is nullable, there is no problem at all. Insert the department with manager_id NULL, insert the employee pointing at it, update the department. Two statements and an update, done once, at seed time.

If all of them are NOT NULL, there is no ordering that works, because ordering is not the difficulty. The set of rows the schema demands is self-referential, and no sequence of statements produces a set that contains itself.

Which databases let you defer the foreign key check?

The escape hatch is deferral: tell the engine to check the constraint at COMMIT rather than after each statement, insert both rows inside one transaction, and let the two halves validate each other at the end. Whether you have that hatch is decided by the engine, not by the model.

Engine	Deferrable foreign keys	What a NOT NULL cycle means here
PostgreSQL	Yes, `DEFERRABLE INITIALLY DEFERRED`	Both inserts in one transaction, if you can supply the key values yourself
Oracle	Yes, for constraints other than NOT NULL	Same
SQLite	Yes, with `PRAGMA foreign_keys = ON` and an explicit transaction	Same, and outside an explicit transaction deferred constraints behave as immediate
MySQL and MariaDB	No	No transactional escape hatch at all
SQL Server	No	No transactional escape hatch at all

MySQL's own manual is blunt about it, and states in the foreign key constraints page that because MySQL does not support deferred constraint checking, NO ACTION is treated as RESTRICT. SQL Server has no deferrable constraints either. On those engines a cycle of NOT NULL foreign keys is not a puzzle to solve in the transaction, it is a schema you cannot use.

And deferral is narrower than it first appears even where it exists. The PostgreSQL CREATE TABLE documentation is explicit that only UNIQUE, PRIMARY KEY, EXCLUDE and REFERENCES constraints accept the clause, and that NOT NULL and CHECK constraints are not deferrable. So the NOT NULL on department.manager_id is still checked immediately. You are not allowed to insert the department with no manager and fix it at commit - you have to insert it pointing at an employee id that does not exist yet, which means generating the key yourself from a sequence or as a client-side UUID before either row is written. Deferral does not remove the chicken and egg. It relocates it into your insert code, where it becomes a requirement that the application knows both keys in advance.

The three shapes a foreign key cycle takes

Only one of these is visible by eye, which is the whole difficulty.

One table. A self-reference: categories.parent_id NOT NULL referencing categories.id. The root category has no parent and cannot be written. This one is obvious in hindsight and still ships regularly, usually because parent_id was made NOT NULL for the honest reason that most rows do have a parent.

Two tables. The mutual pair - departments and employees, organizations and owners, carts and checkouts. Visible on a diagram if the two entities happen to sit next to each other, invisible if they are eighty tables apart on a large canvas.

Three or more. A ring: A references B, B references C, C references A. Nobody drew this and nobody can see it. Each of the three relationships is individually reasonable, each was added in a different quarter, and the cycle exists only in the graph they form together. No amount of staring at the ERD finds it, because there is no place on the diagram where the defect is located - it is a property of the whole model, in exactly the sense that a foreign key whose type does not match the key it references is a fact about two tables at once rather than about either one.

How do you find a cycle you cannot see?

You compute it. Schema lint in Schemity ships seventeen rules, and fk-cycle-all-not-null is one of the three in the group that fails at runtime: a cycle of foreign keys in which every column is NOT NULL, with a table referencing itself as the simplest case. It walks the relationships in the open diagram, so the three-table ring is found on exactly the same terms as the self-reference - the number of hops makes no difference to a graph traversal and all the difference to a person.

table3 > table2 and table5 > table6, each reporting that every foreign key in the cycle is NOT NULL so no row can be inserted, beside a canvas where table1, table2 and table3 form a ring of three ordinary-looking relationships" width="800" height="556">

Both invisible shapes are sitting in that one panel. table5 > table6 is the mutual pair and table1 > table3 > table2 is the ring, and the rule reports them identically because to a traversal they are one defect at two lengths. Look at the canvas underneath and the ring is three unremarkable relationships, each drawn between a different pair of tables, none of which says cycle by itself. The finding names the hops in order - table1 → table3 → table2 → table1 - says what it costs, that each INSERT needs a row that does not exist yet, and states both fixes in the same breath: make one of these foreign keys nullable, or declare them DEFERRABLE on PostgreSQL and insert the rows in one transaction.

Two properties of how that finding is reported matter more than the check itself.

It runs against the model, not the server. Every input the rule needs - which relationships exist, which columns are nullable - is already written down in the diagram, so the check needs the schema rather than a connection, in exactly the way a rule reading which date columns are missing their time zone needs nothing but the types in front of it. You get the answer while designing, on a plane, before the migration exists, rather than at seed time on an environment that does not exist yet.

And it lands on the diagram. Every finding carries a Show on canvas link, and taking it draws a colored strip in the margin beside each entity in the cycle, at the exact field row concerned, rather than leaving you with a list to translate back into the picture - the orange strip visible on the entity at the top of that canvas is the same mechanism reporting the unrelated change_histories.action_type finding. Nullability is already legible there: a nullable field carries a green N badge on the entity, so once you break the cycle, the thing that broke it is visible on the canvas at a glance instead of being a fact you have to remember. Clicking the relationship highlights both ends, the foreign key field on one entity and the primary key it points at on the other, which is how you trace a ring back through its hops.

If the cycle is deliberate and you have solved it with deferral, ignore that single finding. The ignore is saved in the diagram's JSON file and travels with it, so the decision is recorded once for the team rather than re-dismissed by each person who opens the file.

One honest limit: Schemity has no DEFERRABLE toggle on a relationship. It models cardinality, ON DELETE and ON UPDATE, and its answer to a cycle is the modelling decision rather than a constraint flag - so a deferrable constraint is something you add in the migration and record in the ignore, not something the ERD holds for you.

Which side should be nullable?

Assuming you are not deferring, one column in the cycle has to accept NULL, and the choice is not arbitrary.

Pick the side where absence is a real state of the world, not the side that is easier to change. A department genuinely can exist before its manager is appointed, so departments.manager_id being nullable describes reality. An employee who belongs to no department is usually a data error, so making employees.department_id nullable to fix an insert ordering problem quietly legalises a row nobody wants. Both choices unblock the insert. Only one of them is still true a year later.

The reason this matters beyond taste is that NULL then means something, and every query has to handle it. A nullable foreign key is also a nullable column in every unique constraint it participates in, where NULLs compare as distinct and the constraint stops enforcing what it appears to enforce. Choosing the wrong side to relax buys one insert and pays for it in every read.

The third option is to remove the cycle rather than survive it. If both directions are genuinely required and neither absence is real, the reference that does not belong to the entity moves into its own table: a department_managers table holding (department_id, employee_id) with a unique constraint on department_id says one manager per department, in the same shape a junction table uses for the many-to-many case, and both original tables now point one way only. The cost is a join. The benefit is a schema with no cycle in it at all, which is also a schema whose rows can be inserted in any order, on any engine, forever.

What a cycle does to deletes

The insert is the loud failure. The delete is the quiet one.

Referential actions are evaluated in the same graph, so a cycle carrying ON DELETE CASCADE describes a delete that travels back to where it started. Engines guard against the infinite case, but the guard varies - SQL Server refuses to create cyclic cascade paths outright, MySQL and PostgreSQL accept them and resolve the traversal at runtime - and the practical result is that the blast radius of one DELETE in a cycle is genuinely hard to reason about from the SQL. Schemity draws a bold crow's foot at the child end of any relationship whose foreign key cascades, so the rows a parent delete takes with it are visible on the canvas without opening a dialog, and a cascade inside a cycle reads as a bold ring rather than as three unrelated decisions.

At the architecture scale the same question repeats between groups of tables rather than between tables. A context view is a focused subset of the main diagram - one domain's entities, arranged for reading, with the main view still holding the schema. On the Context Map, each context view becomes a node and arrows carry the count of foreign keys flowing in each direction, with a curved arrow rather than a straight one where two contexts depend on each other - so a mutual dependency between two bounded contexts is a shape you scan for rather than a thing you audit. Indirect cycles across three or more contexts are the same invisible case one level up, which is why the AI chat reads the map to answer that question directly instead of asking you to trace arrows.

The one-line version

A foreign key cycle where every column is NOT NULL is a schema that compiles and cannot run. Deferral helps on PostgreSQL, Oracle and SQLite and does not exist on MySQL or SQL Server, and even where it exists it hands the ordering problem to your insert code rather than removing it. The durable fixes are both modelling decisions: make the side where absence is real nullable, or move the reference into its own table.

What you should not rely on is seeing it. Two of the three shapes are invisible on a canvas, which is the general property of a schema defect that involves more than one object at a time - the same reason a link table with nothing enforcing uniqueness over its pair looks exactly like one that is correct, and why the ERD is the right place to check the model rather than the migration that only ever shows you the delta. Draw the relationships, then let something walk them.

PostgreSQL timestamp vs timestamptz: Which to Use and How to Find the Wrong Ones

Son Tran — Wed, 19 Aug 2026 01:47:12 +0000

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

TL;DR: Use timestamptz for anything that records when something happened, and timestamp only for a wall-clock time meant to read the same in every zone. Both types occupy 8 bytes, so the correct one is free, but Rails and Prisma both default to the naive one, which is how a schema ends up with hundreds of columns nobody chose. A migration linter cannot see them because the statements that created them were merged years ago; Schemity's schema lint reads the whole open ERD instead and marks each field row in the margin, with the conversion routed through a migration SQL diff you review first.

Use timestamptz for any column that records when something happened, and timestamp only for a wall-clock reading that is meant to mean the same thing in every zone. Both types take 8 bytes, so the correct one is free. The reason most schemas have the wrong one anyway is that nobody chose it: Rails' t.datetime and Prisma's DateTime both emit the naive type unless you go out of your way, and by the time it matters the migration that created the column was merged years ago.

That is what makes this different from a normal type mistake. A varchar(20) that should have been text announces itself the first time a value does not fit. A timestamp column behaves perfectly for years, then produces an hour of wrong answers at a daylight saving boundary, or shifts an entire reporting dashboard the day someone changes the server's time zone. Nothing in the schema looks wrong at any point, because nothing in the schema is invalid.

Should I use timestamp or timestamptz in PostgreSQL?

PostgreSQL's own wiki answers this in a page called Don't Do This, where don't use timestamp (without time zone) sits alongside its advice against char(n), money and serial. The description there is the clearest one available: timestamp stores "a date and time you give it", which the wiki compares to a picture of a calendar and a clock, while timestamptz "records a single moment in time" and does the right thing with arithmetic between values entered in different zones.

The practical difference:

	`timestamp` (without time zone)	`timestamptz` (with time zone)
What the value means	The reading on a clock, with no clock stated	An instant, the same one for every reader
On write	Stored exactly as given	Converted to UTC using the session zone or the stated offset
On read	Returned exactly as stored	Converted back to the reader's session zone
Arithmetic across a DST boundary	Wrong by an hour, silently	Correct
Two servers in different zones	Disagree about what the row says	Agree
Storage	8 bytes	8 bytes
Right for	A time that is local by definition	Everything that happened

The last row is the whole decision. created_at, deleted_at, published_at, last_seen_at, every audit column and every event time are instants, and they belong in timestamptz. A shop that opens at 09:00 in whatever zone it stands in, a birthday, a recurring alarm the user wants at 07:00 no matter which country they wake up in - those are wall-clock readings, and timestamp is genuinely the right type for them. The set of columns in the second category is much smaller than the number of timestamp columns in a typical database, which is the tell.

What does timestamptz actually store?

Not a time zone. This is worth stating plainly because the objection to using the type is usually built on the assumption that it does, and that the extra information costs something.

The PostgreSQL date/time types documentation is explicit on both halves. On what is kept: an input string with an explicit zone "will be converted to UTC using the appropriate offset for that time zone", and "in either case, the value is stored internally as UTC, and the originally stated or assumed time zone is not retained." On what it costs: the types table gives timestamp [ (p) ] [ without time zone ] and timestamp [ (p) ] with time zone a storage size of 8 bytes each, both spanning 4713 BC to 294276 AD at 1 microsecond resolution.

So timestamptz is not a richer type that remembers where a row came from. It is the same 8 bytes with a defined meaning attached, and a conversion applied at the edges. If you need to know that a booking was entered in Europe/Berlin - because a future rule about that booking depends on the zone rather than the instant - that is a second column holding the zone name, and it is a modelling decision rather than a type choice.

Why did my ORM create timestamp without time zone?

Because that is what it does by default, in the two frameworks most likely to have generated your schema.

Rails translates t.datetime to timestamp without time zone on PostgreSQL. The datetime_type setting that lets you change it to :timestamptz arrived in Rails 7.0, more than six years after the behaviour was reported: the issue standard migrations will generate a "timestamp without timezone" field was opened on 4 August 2015, with the complaint that AT TIME ZONE arithmetic then quietly uses the database server's zone rather than the one intended. The default itself has not changed, so a Rails application generated today still produces naive columns unless somebody adds the initializer.

Prisma does the same by a different route. Its schema reference maps the DateTime scalar to timestamp(3) on PostgreSQL, with @db.Timestamptz(x) available as a native type attribute you apply per field. The documented fix is to write @db.Timestamptz(6) on every date column you care about, and the recurring reports of time zone surprises are largely the gap between that annotation existing and anyone knowing to type it forty times.

This is the same shape as the ORM quietly deciding how a status column is constrained: the framework picks a physical representation, the choice never surfaces in a code review because there is nothing to review, and the schema ends up expressing a decision nobody made.

MySQL inverts the vocabulary, which is worth knowing if you move between engines. Its manual states that MySQL "converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval", and that this "does not occur for other types such as DATETIME". So MySQL's TIMESTAMP behaves like PostgreSQL's timestamptz, MySQL's DATETIME is the naive one, and the zone-aware type there is the one carrying a range limit: 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC, against DATETIME's year 9999.

How do you find the timestamp columns you already have?

The advice to use timestamptz is easy to accept and useless on its own, because the columns that need fixing were created before you accepted it. The real question is which of the two hundred date columns in the schema are wrong, and that is a question about the schema as it stands rather than about anything you are writing today.

A migration linter cannot answer it. Squawk carries a prefer-timestamptz rule, and it is a good rule, but it fires on the column being added in the file being checked - which is why checking migrations and checking the schema are two different jobs. A schema that already holds three hundred naive columns produces no findings at all, forever, because none of those statements are in front of it.

Schemity checks the model instead. timestamp-not-timestamptz is one of the seventeen rules in schema lint, and it reports the same value reading as a different instant depending on the session time zone. It runs against the diagram you have open, on your machine, with no connection required - so a schema reverse engineered from the live database is checked in its entirety, every column at once, including the ones created in 2019.

The finding lands in the margin beside the exact field row rather than in a list you have to translate back into the picture, which matters here more than for most rules: a table with created_at, updated_at and published_at where only one was fixed during some earlier cleanup shows you precisely which one is still naive. The type name is already on the canvas next to it, since entities render each field's type and default directly, so the before and after are both readable without opening anything.

Two honest limits. The rule is PostgreSQL-only - it and fk-array-column are the two of the seventeen that are, and the others run on every engine Schemity connects to - so a MySQL diagram is not asked this question at all, and the DATETIME version of it is not a rule today. And the field type icons deliberately will not help you: types are grouped by what the value is rather than by what the engine calls it, so DATE and TIMESTAMPTZ share one calendar glyph. The icon tells you it is a time, the type text and the lint strip tell you which one.

The rule sits in the group called Costs, next to money-as-float, and the grouping is the point. It is not a runtime failure like a foreign key cycle that no row can be inserted into. It works. It charges you an hour of wrong answers twice a year and a permanent inability to compare two rows written on different servers, which is the sort of bill that never arrives all at once.

When you do fix one, the conversion goes through the normal path: change the type in the ERD, read the generated migration SQL diff, then apply it. That review is not ceremony for this particular change, because converting a naive column means stating what its values meant - USING created_at AT TIME ZONE 'UTC' shifts every row by a fixed offset if the assumption is wrong, and the diff is where you see the clause before it runs rather than after.

If the column is deliberately naive, ignore the finding once. The ignore is stored in the diagram's JSON file and travels with it, so a store-opening-hours column reads as a decision the team made rather than a note each person re-dismisses.

Getting the next hundred columns right for free

The columns that do not exist yet are the cheap half of this problem, and the fix is not vigilance.

Every new entity in Schemity starts from the active template, so a baseline of id, created_at TIMESTAMPTZ and updated_at TIMESTAMPTZ means the convention is what happens when you do nothing, rather than something enforced afterwards by a linter or a reviewer. That is the same reasoning behind fixing the ORM default: an initializer setting datetime_type = :timestamptz, or a Prisma annotation on the field, moves the decision from every future migration to one line, once.

The remaining work is the audit of what is already there, and it is a finite list rather than an ongoing discipline. Open the diagram, read the margin, convert the columns that record instants, ignore the handful that genuinely record clock readings, and the question is closed for the life of the schema.

The one-line version

timestamp stores what a clock said, timestamptz stores when something happened, both cost 8 bytes, and the second one is what almost every column in your schema means. Your ORM picked the first, in Rails since forever and in Prisma by default, which is why the columns to fix outnumber the ones you would ever write by hand. A migration linter cannot find them because their statements are years behind you. Read them off the model instead: the schema is the thing that is wrong, so the schema is the thing to check.

Export a Database Data Dictionary: HTML, Markdown and Excel from Your ERD

Son Tran — Mon, 17 Aug 2026 01:55:03 +0000

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

TL;DR: The people who most need to read your schema - auditors, analysts, a new hire on day one - will never install your ERD tool, so the documentation gets retyped into a spreadsheet or replaced by a picture that answers nothing. Schemity exports the diagram as a data dictionary in HTML, Markdown and Excel, with every column's type, default, nullability and description plus the constraints and relationships, and ends by counting what is still undocumented.

A data dictionary is a document, not a diagram, and it should be generated from the model you already keep rather than retyped into a spreadsheet. Schemity exports the current diagram as a data dictionary in three formats, covering every entity and column with its type, key role, nullability, default and description, the unique, check and index constraints on each table, and the relationships with their cardinality and delete rules.

The reason that matters has nothing to do with file formats. It is about who is asking.

How do I generate a data dictionary from my database?

From the diagram, if the diagram is already an accurate model of the database. That is the whole trick, and it is why the answer is usually so unsatisfying: most teams do not have a diagram that is current, so they fall back to querying the catalog by hand and pasting the results into a spreadsheet.

The people who need the document are rarely the people who own the tooling. An auditor wants to know what personal data the schema holds and where. A business analyst wants to filter a column list and sort it. A client at the end of an engagement wants something they can keep. A new engineer on day one wants to read the schema before touching it. Not one of them is going to install a desktop ERD tool, connect to a database and learn a canvas to get an answer, and none of their questions are answered by a picture of boxes and lines.

Why a diagram is not a data dictionary

A diagram shows shape. It shows that invoices points at customers and that line_items hangs off invoices. It does not tell you that invoices.voided_at is a nullable timestamp with no default, that status is constrained to four values, or that the delete rule on that relationship is RESTRICT rather than CASCADE. Those facts are the document's entire job, and exporting the canvas as an image gives you none of them - which is a different failure from the image going blurry when someone zooms in, and a worse one.

So the document gets built by hand, and the size of the job is what kills it. A practitioner writing about re-oiling the gears for the data dictionary describes a warehouse holding "tens of thousands of columns across the various tables and views" and puts the failure plainly: "the size of the effort is always a barrier to doing it." He also notes that "opposition to providing documentation is always quite vocal", which is what happens when the ask is a manual one.

The free generators are the obvious escape, and they are genuinely useful when they work. They are also a project of their own: a Java runtime, the right JDBC driver, historically a Graphviz install, and a command line to get right before you see a single page. When it goes wrong it goes wrong quietly - one user pointed the standard generator at AdventureWorks2016 and got three tables back, reporting in January 2019 that the output was "Just some tables and columns. MIssing tables, relationships, constraints everting :(". A documentation pipeline that can silently emit a partial document is worse than no pipeline, because the partial document looks finished.

Three formats, three readers

The dictionary exports as HTML, Markdown or an Excel workbook, and the reason there are three is that the three readers want genuinely different things.

The export panel says the distinction out loud. The first three entries are images, the next three are code - .sql CREATE statements, .dbml table and ref definitions, .mmd erDiagram source - and the last three are documents: .html described as entities, fields, constraints and descriptions ready to print or share, .md as the same tables for committing next to the code, and .xlsx as a workbook of overview, entities, fields, constraints, relationships and notes. A picture, a definition of the schema, and a description of it are three different artifacts, and only the third one answers a question about a column.

Format	Reader	What it is for
HTML	Someone who will never open Schemity	Print it, attach it, hand it over at the end of an engagement
Markdown	The engineers	Commit it beside the code so the document is reviewed in the same pull request as the migration
Excel	Analysts and auditors	Filter, sort and pivot a column list; six sheets covering overview, entities, fields, constraints, relationships and notes

The Excel workbook always carries all six sheets, even when a sheet has no rows, so the shape of the file does not change with the diagram and a spreadsheet someone built on top of last quarter's export still works on this quarter's. Column counts are written as numbers rather than text, so they total - a small thing that decides whether the workbook is usable or merely present.

The export follows the active view. Exporting from a context view documents that context alone rather than the whole database. A context view is a saved, focused view of your schema that shows only a subset of entities and the relationships between them, so the billing context can be documented and handed to the payments team without shipping them seventy unrelated tables. The descriptions written on entities and legends travel into the document too, which is what turns a column list into something worth reading.

Why database views belong in the data dictionary

Schemity's SQL, DBML and Mermaid exports leave database views out, and that is correct: those formats describe tables you can create, and a view is not one of them. A document is a different job. Someone checking what exists in the database needs to see the reporting layer and the security boundary, and an export that silently omits half the schema answers nothing.

So views appear in the dictionary, labelled as views rather than dropped. If your reporting layer or your PostgREST API surface is built on views, they are frequently the objects an analyst cares about most, and leaving views out of the model entirely is how they end up as undocumented infrastructure that nobody dares change.

The dictionary tells you what is not documented yet

Every engine will hold a description for every object. PostgreSQL stores them in pg_description and hands them back through col_description; the feature has been there for decades. What no engine offers is the second question: how many objects actually have one.

That gap is why documentation drives get abandoned. The same practitioner above wrote test procedures to "highlight any tables or columns with blank descriptions" and found they produced "too many failed tests when plugged into a legacy system" to be usable. The mechanism was right and the framing was wrong - graded as failures, an inherited schema is nothing but failures, and the report gets muted on the first run.

Schemity's data dictionary closes with the same information stated as facts rather than verdicts: how many entities and fields carry a description, and the names of those that do not. It counts only what can actually be changed, so the columns of a database view, which are read-only, are never listed as missing anything. It states the numbers and stops there. On a legacy database the first export will say something bleak, and that is the point - the number is a work list, and it is the only measurement of documentation coverage most teams have ever had.

This is the same principle that makes schema lint report facts rather than grades: a deliberate choice reported as a mistake teaches people to ignore the report.

Generate it, do not maintain it

The reason a spreadsheet dictionary dies is that it is a second artifact requiring manual synchronization, and that arrangement fails eventually every time. The fix is not a better spreadsheet. It is to make the document a build output of a model that is already kept honest - one set of entities that the live database can be re-read into when it moves, with the document regenerated from it in one action.

That is the practical difference between documentation you write and documentation you produce. It is also why the meaning of the schema has to live in the diagram in the first place rather than in a wiki: descriptions attached to the model get carried into every export, and descriptions attached to a document get carried nowhere. For an inherited database, documenting it without diagramming all 800 tables and exporting one context at a time is the version of this that finishes.

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

Son Tran — Fri, 14 Aug 2026 07:31:37 +0000

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

	Migration linter	Schema linter
Input	The SQL statements in one migration	The whole schema as a model
Runs	In CI, per deploy	While designing, before the migration exists
Answers	Is it safe to run this	Is the schema correct
Sees	Locks, rewrites, backwards compatibility	Relationships between objects, constraint semantics
Cannot see	Anything already in the schema	Whether applying a change will take the database down

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.

Postgres Array Column vs Junction Table: What Each One Does to Your ERD

Son Tran — Thu, 13 Aug 2026 04:19:36 +0000

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

TL;DR: A PostgreSQL array column stores a list in one field and can be several times faster to query than a junction table, but the database cannot put a foreign key on array elements, so an array of IDs is a many-to-many relationship that no ERD can draw. Use an array when the list is a value the row owns, and a junction table when the elements are references to rows that exist on their own. Schemity supports PostgreSQL array types as first-class field types instead of rejecting them, creates the junction table with its foreign keys and composite primary key from one N:N gesture, and turns a later conversion into a migration SQL diff you read before it runs.

Use a PostgreSQL array column when the list is a value the row owns, and a junction table when the elements are references to rows that exist independently. The reason is not normalization theory. It is that PostgreSQL cannot put a foreign key on array elements, so an array of IDs is a many-to-many relationship with nothing to enforce it and nothing for a diagram to draw.

That second half is the part nobody mentions. Every discussion of int[] versus a join table is argued on query speed and disk size, both of which favour the array more often than the purists admit. Then a year later somebody opens the ERD to understand how the system fits together, and an entire relationship is simply not in the picture.

Why an array of IDs disappears from your ERD

An ERD draws a relationship line because there is a foreign key to draw it from. No foreign key, no line. And PostgreSQL will not give you one on an array column: foreign keys compare directly comparable things, and an array is not comparable to a scalar primary key. This is not an oversight waiting on a release. A patch adding array element foreign keys - an ELEMENT REFERENCES column constraint, so FOREIGN KEY (c1, ELEMENT c2) REFERENCES t1 (u1, u2) would validate every element - was proposed in 2011 and has never landed. The standing advice on the mailing lists is unchanged after fifteen years: write your own trigger on both tables, or use a details table.

So article.tag_ids int[] records a many-to-many relationship between articles and tags with no constraint behind it. Delete a tag row and the IDs stay in every array that mentioned it, pointing at nothing. There is no ON DELETE behaviour to configure, because there is no foreign key to configure it on - which puts array columns in the same blind spot as referential actions that never make it into the diagram, except worse, because here the reference itself does not exist as far as the engine is concerned.

Then the diagram tool makes it invisible a second way, by refusing to render the type at all. A dbdiagram.io user wrote on November 14 2021 that PostgreSQL array types are marked as errors, calling them "critical to my schemas (and to my continued use of dbdiagram.io)". The official reply three days later was a workaround: quote the type as a string, name "text []", so the parser stops complaining. The export side has the same trouble - Postgres export fails on array has been open in the DBML repository since 2019, because a column declared as variants array exports as "variants" array, which PostgreSQL rejects. A column type that has to be smuggled past the parser as a quoted string is not a modelled type.

The net effect is a diagram that is wrong twice about the same column. The relationship is missing, and the field lies about what it holds.

Should I use a Postgres array column or a junction table?

Decide by asking what the elements are, not by asking which one is faster:

	Array column (`int[]`, `text[]`)	Junction table
Referential integrity	None available; a trigger you write and maintain	Foreign key per parent, enforced by the engine
Visible in an ERD	No line, and often not even a correct type	Two 1:N relationships, drawn
`ON DELETE` behaviour	Nothing to attach it to	Configurable per foreign key
Attributes on the pairing	Impossible - the element is a bare value	Any columns you want on the junction row
Duplicate entries	Allowed unless you add a check	Blocked by the composite primary key
Read a whole row with its list	One row, no join	One join, or an aggregate
Filter by list contents	Fast with a GIN index	Join plus index lookup
Shared lookup of all values	No natural place for it	The parent table is the lookup
Best for	A list the row owns: free-form tags, a checklist, a set of flags	A list of references to rows that exist on their own

The speed column is where arrays earn their reputation, and it deserves an honest number rather than a hand-wave. Crunchy Data benchmarked exactly this on a tagging schema and found the three-tag lookup ran in roughly 120ms on an integer array against roughly 950ms on the relational model - "about seven times faster than the same query on the relational model". Their conclusion is not that arrays win, though. It is that the array models are "faster to query, smaller to store, and simpler to query" while giving up two specific things: "there's no general place to lookup all tags" and "there's no way to create a simple constraint that guarantees integers exist in the tags table".

Those two sentences are the whole decision. If you need a place to look up all the values, and a guarantee that every stored value is one of them, you have described a table with a foreign key pointing at it.

One row of that table deserves a footnote: "blocked by the composite primary key" is true of the junction table you get from an N:N gesture, and not automatically true of one that arrived by reverse-engineering a database somebody else designed. A link table with two foreign keys and no key over the pair accepts duplicates exactly as freely as the array does - and if its key is there but includes a nullable column, it covers only the rows where every key column has a value, which may be the design or may be an accident.

The test: is the element a value or a reference?

The reliable way to tell the two cases apart is to ask whether an element can exist on its own, before and after any row that mentions it.

A free-form tag typed into a text field is a value. It has no row anywhere, no ID, no attributes, no lifecycle. tags text[] on the article is an accurate model of that, and normalizing it into tags plus articles_tags buys you nothing but two joins and a table that is a list of strings with surrogate keys stapled on.

A tag that appears in an autocomplete, has a slug, has a colour, has a count, or can be renamed everywhere at once is a reference. It is a row. The moment it is a row, tag_ids int[] is storing a foreign key in a place a foreign key cannot go, and every fact about the relationship - that it exists, which direction the dependency runs, what happens on delete - drops out of the schema and lives only in whichever service happens to write that array.

The same test settles the cases that look ambiguous. A permissions text[] column on a role is a reference in disguise, because permissions are generated from the routing table and must be looked up as a set - which is why permissions in a multi-tenant RBAC schema get their own table and junction rather than an array of strings on the role. A preferred_contact_methods text[] on a user is a value, because the elements are vocabulary, not rows. And a list of file paths, a set of coordinates, a fixed-length matrix of scores: values, all of them, because you use the array as a whole.

There is a third option that people reach for arrays instead of, and it is worth naming: when the list is short, closed, and drawn from a fixed vocabulary, the question is really about a value set, and the choice between a Postgres enum, a check constraint and a lookup table is the one you are actually making. An array of strings with no constraint on the elements is the least legible member of that family, because nothing anywhere records what an element is allowed to be.

How Schemity renders array columns and junction tables

Schemity supports PostgreSQL array types as ordinary field types, so a text[] column is a text[] column on the canvas rather than a parser error or a quoted string workaround. When you reverse engineer a PostgreSQL or Supabase schema, the array columns come across as what they are. That matters most for the honest case: when the list genuinely is a value, you want the diagram to say so plainly, not to force you into a junction table just so the tool has something to draw.

The array is a toggle on the type rather than a type of its own: pick TEXT, tick [ ], and the field reads TEXT[] on the entity with its {} default intact, alongside the PK, Unique and Nullable flags any other column gets. The screenshot has both models in one frame. allowed_domains text[] on sso_configs is a value the row owns - a list of domain strings that exist nowhere else and are read as a whole - while member_roles and roles_pems are drawn as tables, because members, roles and permissions are rows with their own lifecycle. Nothing about the picture forces the first case into the shape of the second.

For the reference case, the junction table is generated rather than assembled. Drag between the two entities, pick N:N in the relationship dialog, and the junction table is created with one foreign key per parent, a composite primary key over the two so a pairing cannot repeat, and a name that follows the naming convention set on the connection. It is a real table you can rename and extend, not a notation shortcut - which is the point when the pairing turns out to need a column of its own, an added_at or an added_by, the kind of attribute an array element can never carry. The mechanics of that, and the rule for when a junction wants a composite primary key versus its own id, are covered in why many-to-many shouldn't mean hand-building the junction table.

The practical difference on the canvas is that one of these two models is legible from across the room and the other is not:

	`article.tag_ids int[]`	`articles_tags` junction
On the canvas	One field on one entity	Two crow's foot relationships between three entities
What a reader learns	This column holds some integers	Articles and tags are many-to-many, and the delete behaviour
What breaks silently	A deleted tag leaves dangling IDs	Nothing; the foreign key refuses the delete or cascades deliberately

Converting an array column to a junction table later

Most of these decisions get revisited, usually when the list grows an attribute or somebody finds orphaned IDs. The conversion is mechanical, and it is worth planning as three deploys rather than one.

Create the junction table with a foreign key to each parent. Backfill it by expanding the array with unnest, inserting one row per element. Then count the elements that failed to match a parent row, because that number is the thing the array was hiding - it is the size of the integrity gap you have been carrying, and it is almost never zero. Only after the application reads from the junction do you drop the array column, since that is the single irreversible step.

Schemity handles the schema half of that the way it handles any structural change: you edit the ERD, and it generates the migration SQL diff for you to read before anything runs against the database. Dropping a column that is still the only copy of a relationship is exactly the statement that deserves a human reading it first. Because the diagram is a plain JSON file in the workspace folder, the change is also a line in a pull request, so a reviewer sees a relationship appear in the model at the same time the migration appears in the diff - the same loop as not hand-translating between SQL and your ERD.

Store the list where the model can show it

An ERD is a single source of truth when every structural fact the database enforces is readable from the diagram and nowhere else is needed to understand it. An array of IDs breaks that quietly, because the fact it encodes - these two things are related, many to many - is enforced by nothing and drawn by nothing. The knowledge moves into application code, and from there into whoever happens to remember.

None of which makes array columns a mistake. They are a good fit for a list of values, they are genuinely faster on the query shapes they suit, and a tool that cannot even render text[] is failing you rather than protecting you. The mistake is only ever the substitution: using an array to store references because the junction table felt like too much ceremony to build. It is also why an array of foreign keys is one of the things a schema linter reports on the diagram: nothing checks that the referenced rows exist, so the finding states that consequence rather than calling the column wrong. When the junction table costs one gesture, that trade stops being tempting, and the schema goes back to carrying its own domain model instead of outsourcing half of it to the code that writes the arrays.

DrawSQL Alternative: An Offline Desktop ERD Tool With No Table Limits

Son Tran — Thu, 06 Aug 2026 13:31:08 +0000

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

TL;DR: DrawSQL is a cloud ERD editor with no connection to your database - schema arrives by pasting CREATE TABLE statements - and every plan meters how many tables you may model, from 15 on the public-only free tier to 100 on the $59/month Growth plan. Schemity is the offline desktop alternative: it connects to PostgreSQL, Supabase, MySQL, MariaDB, SQL Server or SQLite directly, re-syncs the schema every time you open the diagram, and stores each diagram as a plain JSON file in your Git repo for $129 one time, with no cap on tables, diagrams, or workspaces.

If you like the way DrawSQL works but want it without a browser tab, a subscription, or a counter on how many tables you are allowed to draw, the alternative is a desktop ERD tool that opens a real connection to the database you are diagramming. That is what Schemity is: connect, reverse engineer, model, review the migration, and keep the file in Git.

DrawSQL is a genuinely nice product. It is developer-shaped rather than a general diagramming canvas, the editor is fast, and the public schema gallery is a good piece of community work. But three structural facts decide whether it fits your work, and none of them is a matter of taste: it has no live database connection at all, the size of the model you are allowed to build is a pricing tier, and the canvas will happily accept a diagram that no database could ever accept.

The Honest Summary

DrawSQL is a browser-based database diagram editor for developers and teams. Schema goes in by pasting SQL, diagrams live in DrawSQL's cloud, and the collaboration layer - multiplayer editing with live cursors, canvas discussions, version history - is the reason to be there. It supports MySQL, PostgreSQL, SQL Server, and MariaDB, and it exports to SQL, JSON, Laravel migrations, and images.

That screenshot is DrawSQL at its best, and it is worth looking at before any criticism lands: table groups as colored regions, sticky notes carrying open design questions, and two teammates' cursors moving on the canvas while you work. Those are real strengths and none of them is on the next screenshot.

Schemity is an offline ERD tool for software engineers: a native desktop app that connects to PostgreSQL, Supabase, MySQL, MariaDB, SQL Server, and SQLite, reverse engineers the live schema into a diagram, generates a SQL migration diff when you change the model, and writes every diagram as plain JSON into a folder you choose. One $129 purchase, no account, no seats, no table limit.

Same job, different premise. Every type, default, unique constraint, and nullable flag in that diagram was read from a live database rather than typed in, the two italicised entities are database views the tool knows are read-only, and the footer reports the size of the model as information rather than as a quota. The small triangles in the top-right corner of the policy legend and of entities like pwd_rules and segments are the equivalent of those sticky notes, with one difference this post comes back to below: they are attached to the objects rather than to the canvas.

The difference is not cloud versus desktop. It is that one tool is fed by copy-paste and metered by plan, and the other is fed by a connection and bounded only by your disk.

Does DrawSQL Connect to Your Database?

No. The only documented way in is text. DrawSQL's own feature list offers "Import from SQL" - you paste CREATE TABLE statements, or run its SQL-to-ERD tool - and there is no connection, no credential store, and no reverse-engineering step anywhere in the product. Nothing on the page tests, saves, or re-reads a database.

Read that as a workflow and the consequence is not the paste itself, which takes a minute. It is that the diagram is a photograph. The moment a migration lands, the picture and the database disagree, and the only repair is to dump the schema again, paste it again, and re-create the arrangement work you did the first time. This is exactly the loop that makes people stop updating diagrams: keeping the ERD current becomes a second job with no payoff between updates.

Schemity starts from the other end. You create a connection - directly or through an SSH tunnel - test it before saving, and Schemity reverse engineers the schema into an ERD: tables, fields, types, defaults, check constraints, unique constraints, and the foreign keys as real crow's foot relationships with their ON DELETE and ON UPDATE behavior attached. Passwords go to the operating system keychain, never a config file, and SSH connections store a reference to your private key rather than its contents.

None of which means a database is required. When you create a connection in Schemity, Conn method has a None option: no host, no credentials, no database - you pick a dialect (PostgreSQL, MySQL, SQLite, or SQL Server) and design on an empty canvas. The dialect is not decoration; it decides which types you can choose from and what the generated SQL looks like, so a schema designed against SQL Server is a SQL Server schema rather than a generic drawing you translate later. Greenfield design, an interview whiteboard, a schema for a database that does not exist yet - all of it works with nothing to connect to, and starting an empty offline diagram this way is free; the license unlocks the live-database half.

So the difference is not that Schemity demands a connection and DrawSQL forbids one. It is that Schemity treats the connection as available and DrawSQL has no concept of one - which decides what happens on day 200, not day one.

Because the connection persists, so does the diagram's accuracy. Re-opening it pulls the current schema: entities you have already positioned keep their layout, dropped tables and columns disappear, and new tables arrive as dashed drafts ready to place - visible in orange on the minimap so you can see exactly where the drift landed. The pasted-dump workflow is still available when you want it, since importing a SQL dump or a set of CREATE TABLE statements produces the same entities and relationships, and DBML import brings schemas over from dbdiagram.io. It is just not the only door.

Can You Draw a Schema DrawSQL Cannot Export?

Yes, and it takes about three gestures. We spent an afternoon in the DrawSQL editor on 4 August 2026 building an ordinary tasks-and-comments schema, and the canvas accepted three things that no relational database would.

A foreign key between two arbitrary columns. Drag from users.name to tenants.name - two plain varchar columns, neither of them a key - and DrawSQL draws the relationship without a murmur.

In PostgreSQL, MySQL, and SQL Server alike, a foreign key must reference a primary key or a unique constraint, and tenants.name is neither. So the line reads as a foreign key, is labelled 1:N like a foreign key, and describes a constraint the database would reject outright. The same held for every pair we tried, including comments.body pointing at members.role. Nothing about the shape of the line tells you it is fiction.

A relationship that is a label, not a constraint. The popover in that screenshot is the whole of what a DrawSQL relationship offers: the two endpoints, three cardinality buttons, and a delete icon. There is no ON DELETE or ON UPDATE referential action, no constraint name, and no control over how the connector is routed - the only lever on where a line goes is dragging the tables at either end and hoping. Cardinality here is a label you pick rather than something derived from the schema: 1:N is selected because you clicked 1:N, not because tenants.name is non-unique. DrawSQL's own documentation index matches that thinness - articles for Tables, Columns, Indexes, Table groups, and Sticky notes, and not one for relationships.

Count those three buttons again, because the set is odd in both directions. 1:N and N:1 are the same relationship read from opposite ends, so one of the three is a duplicate of another - and the missing fourth is N:N, the only cardinality that actually needs help from a tool, because it is the one a relational database cannot store without a junction table.

Two tables with the same name. Copy an entity, paste it, and the copy lands as a peer rather than as users_copy or a name collision warning.

Two tables called users, each with its own id, email, and name, and each running its own relationship into tenants.name. CREATE TABLE users twice in one schema is invalid in every engine DrawSQL supports, so the diagram now describes something that cannot be built - and it describes it twice, with two conflicting foreign keys into the same non-key column. The canvas raises nothing, because nothing on the canvas is checking.

These are the same fact wearing three costumes, and it connects directly to the section above: a tool that never opens a connection never has to produce a schema a database would accept, so nothing in it is under pressure to enforce the rules. To be fair, DrawSQL is partly a sketching tool with a public gallery attached, and permissiveness in a sketch pad is a defensible design choice - you should be able to draw a half-formed idea. But "SQL" is in the name and DDL export is a listed feature, which sets a different expectation: that what you draw is a schema, not a picture that resembles one. We could not test what the export actually emits for that name to name line, because export is gated behind a paid plan; the observation is what the editor accepts.

Schemity treats a relationship as an object with rules, which removes all three cases by construction. You do not connect two arbitrary fields - you drag between two entities and Schemity creates the foreign key field on the child pointing at the parent's primary key, so name to name is not expressible in the first place. Every relationship then opens: cardinality is derived rather than chosen, following the foreign key's uniqueness, which is why a 1:1 does not silently import as a 1:N; a CASCADE set in that dialog is then drawn with a bold crow's foot, so its blast radius is visible on the canvas without opening anything. Clicking a relationship highlights the whole line together with both fields it connects, and dragging it adds a waypoint so you never have to guess which line goes where. Duplicate entity names are rejected rather than drawn.

Put the two dialogs side by side and the difference is legible in a glance.

Note what is editable and what is not. The left side reads pwd_rules.id→pwd_policies. and cannot be changed - the parent's primary key and the child table are settled by the drag that created the relationship. The one text field is the name of the foreign key column on the child, pwd_rule_id, because that is the only part of a foreign key that is genuinely a naming decision. Below it sit the two dropdowns DrawSQL has no equivalent for - On delete and On update, defaulting to NO ACTION - which decide how far a DELETE travels through the schema and are otherwise invisible in most diagrams.

The cardinality menu differs the same way. Schemity offers 1:1, 1:N, and N:N, and there is no N:1 on purpose: direction is carried by the gesture, so you drag from the parent to the child and the foreign key field is created on the child. That is not a shortcut, it is where the answer already lives - in a real schema the direction of a relationship is not a preference, it is a fact about which table holds the foreign key column. Offering 1:N and N:1 as separate settings splits one fact into two representations and leaves room for them to disagree; drag the other way in Schemity and you get the other relationship, because you have built a different schema. Meanwhile the N:N that DrawSQL's popover has no button for is the one Schemity builds the junction table for - both foreign keys and a composite primary key already in place - rather than leaving you to assemble it by hand in six steps.

And when the diagram is connected to a database, the migration diff is the final check - the model has to be something the engine will actually accept before it becomes real.

What Does DrawSQL Cost in 2026?

Verified from DrawSQL's pricing page on 4 August 2026, at the default monthly billing:

Plan	Price	Private diagrams	Tables per diagram	Users
Free	$0	None - all diagrams public	15	1
Starter	$19/mo or $171/yr	10	50	1
Growth	$59/mo or $531/yr	Unlimited	100	5
Large	$179/mo or $1,790/yr	Unlimited	Unlimited	25
Enterprise	Custom	Unlimited	Unlimited	Unlimited

Two numbers in that table do the most work. The first is 15 - the free tier's table cap, which arrives long before a real schema does. The second is $179 per month, the price at which the cap disappears entirely. Between them, the plan you are on decides how much of your database you are permitted to model, which is a strange thing to negotiate with a drawing tool. Modeling under a quota bends the model: you merge entities you would have split and skip the junction table you actually needed, and the diagram quietly stops being an honest description of the system.

Look back at the Schemity status bar in the screenshot above: Entities: 59, Relations: 71. That is an unremarkable application schema - not a warehouse, not an enterprise ERP - and it is already nearly four free tiers' worth of tables, nine past the Starter cap, and squarely in $59-per-month Growth territory. The status bar reports it the way a text editor reports a line count, next to the PostgreSQL version, because the number is information rather than a threshold.

Schemity is $129 once, for unlimited workspaces, unlimited diagrams, unlimited entities, and unlimited context views. That is less than one year of the cheapest paid DrawSQL plan at its annual rate, and about 2.2 months of Growth. There is no per-seat multiplier, because there are no seats - the files are on your disk and your teammates read them out of the repository.

AI is priced differently too. DrawSQL includes 500 AI credits per month on Starter and Growth, so the assistant is a metered feature of the subscription. Schemity's built-in chatbot is bring-your-own-key: you point it at OpenAI, Claude, Gemini, Grok, or DeepSeek with your own key and pay that provider directly, or you add Ollama and run a local model with no cloud in the loop at all. Either way the credits are not ours to sell you.

What Happens to Your Schema on the Free Tier?

It becomes a public URL. DrawSQL's free plan is unlimited public diagrams - privacy is what the $19/month Starter plan buys. That is a legitimate business model, and for the open-source schemas in DrawSQL's gallery it is the whole point. It is also a hard stop for a large class of professional work.

If you consult, the schema on your canvas belongs to a client and is very likely covered by an NDA. If you work somewhere with an IT policy, "the ERD is on a public web page" is not a sentence that survives review. The free tier is therefore not a free tier for you - it is a paywall with a publication clause, and the first real decision you make in the tool is which client's data model you are willing to publish. That is the same problem as keeping every client's schema in one shared cloud account, with the sharing default turned all the way up.

An offline ERD tool inverts the question, and it inverts it for free diagrams too - designing with Conn method None costs nothing and still writes to your disk rather than to a URL. A Schemity workspace is a folder of JSON files on your machine; a diagram is not published, shared, or hosted unless you put it somewhere yourself. Per-client isolation is physical - one folder per engagement - and handover at the end of a contract is a folder copy rather than a permissions audit. Nothing needs to be public for you to have the free use of your own model.

Where Does the Diagram Live?

In DrawSQL, a diagram is a record in DrawSQL's database, reachable through DrawSQL's interface, for as long as your subscription is current. Exports exist, but on Starter the export is JSON only - so the cheapest paid plan is also the one where getting your work back out is most constrained. When your model lives inside a vendor's storage, switching tools later means re-doing the modeling, and the diagram itself is something that can go missing without you deleting anything.

In Schemity, a diagram is a file. Creating a workspace creates a matching folder, every diagram inside it is plain, readable JSON, and you open that folder in Finder or Explorer with one shortcut. Put the folder in a repository and the schema gets the same treatment as the code: the ERD is reviewed in a pull request as a diff, blamed, branched, and reverted with the tools you already use. Workspaces that sit inside a Git repository even show a branch icon in the workspace list, so which diagrams are actually under version control is visible rather than assumed.

That distinction is worth testing against the unhappy case, because a tool's real character shows when you stop paying it. When Schemity's two-week trial expires, context views and the Context Map are locked - the pricing page says so, and Schemity Lite does not include them either. What does not happen is your work becoming unreachable. The context views you already built are still in the workspace JSON on your disk, still in your Git history, still readable in any text editor, and still there the moment a license unlocks the panel again. Expiry withdraws a feature from the application; it does not withdraw the file from you. That is a different kind of event from a lapsed subscription in a cloud tool, where what ends is access to the diagram itself.

Export is not a rescue operation either. A diagram or a single context view goes out as JPG, PNG, or SVG; the SQL of the main view, any context view, or one entity is a right-click away; and the whole schema exports as DBML or as a Mermaid erDiagram that GitHub, GitLab, Notion, and Obsidian render natively from text.

DrawSQL vs Schemity

	DrawSQL	Schemity
What it is	Cloud database diagram editor for teams	Focused offline ERD tool for relational schemas
Platform	Browser, account required	Native desktop app (macOS, Windows, Linux)
Getting schema in	Paste `CREATE TABLE` statements	Live connection or SSH tunnel; SQL dump and DBML import too
Designing with no database	The only mode there is	Conn method None, with a chosen dialect driving types and SQL
Databases	MySQL, PostgreSQL, SQL Server, MariaDB	PostgreSQL, Supabase, MySQL, MariaDB, SQL Server, SQLite
Keeping it current	Re-paste the schema	Re-sync on open; layout preserved, drift applied
Relationships	Endpoints, three cardinality buttons, delete	Cardinality derived from uniqueness, `ON DELETE`/`ON UPDATE`, waypoints, click-to-highlight
Cardinalities offered	`1:1`, `1:N`, `N:1` - no `N:N`	`1:1`, `1:N`, `N:N` - direction comes from the drag, so no `N:1`
Many-to-many	Build the junction table yourself	Junction table generated with both FKs and a composite primary key
What the canvas allows	Foreign keys between arbitrary columns; duplicate table names	Foreign keys target the primary key; duplicate entity names rejected
Notes on the diagram	Sticky notes, anchored to a canvas position	Markdown descriptions, anchored to an entity, legend, or context view
Applying changes	Export SQL and run it yourself	Reviewed migration SQL diff, applied only on your approval
Table limit	15 free, 50 Starter, 100 Growth, unlimited at $179/mo	None
Privacy of a free diagram	Public, by design	Private - free offline design still writes to your disk, not a server
Where diagrams live	Records in DrawSQL's cloud	Plain JSON files in a folder you choose
Version history	Built in, on paid plans	Git, on files you own
AI	500 credits/month on paid plans	BYOK (OpenAI, Claude, Gemini, Grok, DeepSeek) or local Ollama
Collaboration	Real-time multiplayer, canvas discussions	Single-designer by design; review happens in Git
Pricing	$19 to $179/month, per plan and per seat	$129 one-time, unlimited diagrams

Modeling a Schema That Does Not Fit in One Diagram

The table cap and the wall-poster problem are the same problem seen from two ends. A 200-table database will not fit on any tier below $179 per month, and even when it fits, one canvas holding 200 tables is not something anyone reads.

Schemity's answer is not a bigger canvas but more perspectives on one model. Legends group related entities into named, colored regions on the main view. Context views then carve the schema into focused sub-diagrams - a context view is a focused subset of the main diagram that you arrange freely while the schema itself stays untouched, so the main view remains the single source of truth. Right-click a legend and every entity inside it becomes a context view in one step. An entity with a relationship to something outside the view is marked with an orange dot, so a focused view still tells you what it is hiding.

Above those, the Context Map renders each context view as a single node and draws arrows for the foreign keys flowing between them, with a count on every arrow and a curved arrow wherever two contexts depend on each other. Fifteen readable diagrams and one architecture map beat a single sheet nobody opens - and the reason you do not need a diagram of all 800 tables is that comprehension never scaled with the canvas.

Where Should a Note About a Table Live?

Attached to the table, is Schemity's answer - which is why it has entity, legend, and context view descriptions but deliberately no sticky notes.

The difference is what the note is anchored to. A sticky note is anchored to a coordinate on the canvas: it sits at x, y, and the canvas is the only thing that knows it exists. A description is anchored to an object. Give pwd_rules a description and a triangle appears in its top-right corner; clicking it opens the rendered markdown. Legends carry them too, so the note can describe a whole domain rather than one table, and so can a context view.

That distinction is not aesthetic, because the two behave differently as the schema moves underneath them:

	Sticky note on the canvas	Description on the object
You move the table	Note stays where it was	Travels with the table
The table is dropped and you re-sync	Note survives, pointing at nothing	Removed with the table
The table is imported into a context view	Does not come along	Comes with it
Someone reads the diff	A note moved, somewhere	The note changed on the entity it describes

Six months on, an orphaned note floating near where a table used to be is worse than no note, because it reads as documentation and it is actually a fossil. A description cannot outlive its subject: it is a property of the entity, stored in the entity's JSON, so the meaning of the schema and the schema itself move as one thing - which is the entire point of putting the data dictionary inside the diagram rather than in a wiki beside it.

The honest cost is the case DrawSQL's own screenshot demonstrates. That sticky note asks whether cancellation_policies should be a separate table or a JSON column on listings - a question about a table that does not exist yet, and therefore has nothing to attach a description to. Anchoring to objects means you cannot pin a thought to a decision you have not made. Schemity's partial answer is the legend: draw one around the area under discussion and its description holds the open question for that domain. The full answer is that an unresolved design question is not documentation, and probably belongs in the pull request or the ticket where it will be resolved and closed - which is the same reasoning behind Schemity having no zoom. Some absences are decisions.

If your team thinks out loud on the canvas, that is a real reason to prefer DrawSQL, and no amount of philosophy changes it.

Where DrawSQL Wins

The concessions are real, and for some teams they settle it.

Real-time multiplayer editing. Live cursors, simultaneous edits, and discussion threads pinned to the canvas. Schemity does not do this and does not plan to - it takes a deliberate single-designer position where one person holds the pen and feedback arrives through review. If your modeling sessions are three people on one canvas, DrawSQL is built for that and Schemity is not.

Sticky notes on the canvas. As the section above argues, Schemity's descriptions are the more durable form - but durability is not what a half-formed thought needs. If your team parks open questions on the diagram as you go, DrawSQL supports that directly and Schemity asks you to put them somewhere else.

A link, not an install. Anyone with the URL can open the diagram - no download, no license, no operating system to match. Schemity's read-only mode lets someone explore a diagram without database access, but it is still an app they have to install.

Laravel migration export. Exporting the model straight to Laravel migrations is a genuinely useful piece of framework integration that Schemity has no equivalent for; Schemity emits SQL DDL and a migration diff instead.

A free way in. Fifteen tables and a public URL is a real starting point if the schema is not confidential - and for the open-source schemas in DrawSQL's gallery, publishing is the feature. Schemity's free paths are a two-week full trial on the desktop app, offline design with Conn method None after it, and Schemity Lite in the browser for designing and sharing by link. But they are genuinely narrower: the live-database half this post spends most of its time on is licensed, and so are context views and the Context Map. There is no permanent free tier that includes them.

What Is a Good DrawSQL Alternative That Works Offline?

If what you valued in DrawSQL was the shared canvas - people editing together, discussions on the diagram - then no offline tool will replace it, and you should keep using it for that.

If what you valued was the ERD itself, the useful alternative is a tool whose primitives are tables, foreign keys, and migrations, and whose storage is a file. Schemity is that in desktop form: it connects to the database and reads the constraints instead of waiting for a paste, it re-syncs on open so the diagram stays true without a maintenance ritual, it generates a migration SQL diff you approve before anything runs, and it keeps every diagram as a JSON file that Git can version alongside the code it describes.

And the arithmetic is not close. A subscription is a permanent line item that also decides how large your model may be; a $129 one-time purchase ERD tool with unlimited local workspaces has neither property.

Who Should Use DrawSQL

Diagramming is a live team activity and multiplayer editing is the point
Sharing means sending a URL to people who will not install anything
Your schemas are open source, or public visibility is genuinely fine
Laravel migration export fits your stack directly

Who Should Use Schemity

You are diagramming a database that exists and want the tool to read it directly
Or the database does not exist yet, and you want to design it against a real dialect before it does
The schema is under NDA, an IT policy, or simply none of the internet's business
The ERD should live in Git, be reviewed in pull requests, and outlive any vendor
Your model is bigger than a free tier and you refuse to negotiate its size
One purchase, no seats, no meter

The Bottom Line

DrawSQL is a well-made cloud ERD editor, and both of its limits follow from that description rather than from any lack of craft: a browser app with no connection to your database has to be fed by hand, and a subscription product has to meter something, so it meters tables.

If your diagram is a conversation, that trade is fine. If your diagram is a picture of a database you have to keep accurate for years, the fix is an offline desktop ERD tool that opens the connection, reads the constraints, preserves your layout on every re-sync, and writes the result to a file you own.

Comparing the other database-native tools instead?

The cloud-generation schema visualizer: see ChartDB alternative.
The DSL-based sketch pad: see dbdiagram.io alternative.
The full desktop database IDE: see DbSchema alternative.
The general diagramming suite: see Lucidchart ERD alternative.

You Cannot Tell Which of Your ERD Files Are Under Version Control

Son Tran — Tue, 04 Aug 2026 17:55:53 +0000

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

TL;DR: A diagram stored as a file is only version controlled if its folder happens to sit inside a Git repository, and nothing in the tool tells you whether it does - so the safest-feeling diagrams are often the untracked ones. Schemity now shows a Git branch icon beside every workspace that lives inside a repository, detected by walking up the directory tree so a workspace nested deep inside a project counts too, and marks imported workspaces with their own icon so the ones you deliberately placed inside a repo are distinguishable from the ones the app created for you.

A diagram is not under version control because you meant it to be. It is under version control if, and only if, the folder holding it sits inside a Git repository - and until now nothing in Schemity told you which of your workspaces did. Now every workspace that lives inside a repo carries a Git branch icon in the workspace list.

That sounds like a small thing. It is small the way a fuel gauge is small.

The assumption you never actually check

Storing diagrams as plain files is the whole argument for a Git-native ERD tool: text on disk, diffed in pull requests, restored from history. The ERD belongs in your Git repo rather than as a PNG rotting in a wiki. But that argument quietly depends on a fact nobody verifies: that this particular diagram, the one open right now, is inside a repository.

Six months in, that is genuinely hard to know. You created a workspace for the client project and put it in their repo. You created another for a spike and let it default to ~/schemity. You imported a third from a folder you no longer remember choosing. Every one of them looks identical in the list. They open the same, save the same, and produce the same JSON. The only difference is that three of them are recoverable and one is a file on a laptop.

This is not a niche confusion, and the proof is that developers have industrialized it. An entire category of command line tools exists purely to answer "which of my folders are Git repositories, and what state are they in" - gita, at 1.9k GitHub stars, exists to "manage many git repos with sanity" by printing their status side by side, and mgitstatus scans a whole directory tree looking for .git dirs "up to DEPTH directories deep" to report uncommitted, untracked and unpushed changes. Nobody writes and maintains a tool for a problem they do not have. Developers lose track of which folders are tracked, routinely enough to build instruments for it.

The failure mode is specific and quiet. Nothing breaks. No error appears. The diagram saves perfectly every time. You find out the day you need the version from before the refactor, and there is no history to go back to.

How do I know which of my database diagrams are under version control?

Look at the workspace list. Any workspace whose folder resolves to somewhere inside a Git repository shows a branch icon beside its name.

Five workspaces, four different situations, readable in one glance. demo, default and work sit inside repositories. erd was imported from elsewhere on the machine but is not in one, so it is a single copy on this laptop. suberd was imported and is inside a repository, which is the arrangement you want for a diagram that belongs to a project.

There is no configuration and no connection step. Schemity checks the workspace folder's location on disk and reports what it finds. A workspace under version control looks different from one that is not, at the moment you are choosing which to open, which is the only moment the information is useful. The check is about the folder, not about your commits - the icon tells you the diagram can be tracked, not that you have remembered to commit it.

This closes a real gap in the version control workflow. The JSON storage format made diagrams diffable, and creating a workspace put them in a folder you control. What was missing was any way to confirm the arrangement had actually happened for a given workspace, rather than being something you intended in March.

What the two workspace icons mean

Workspaces now carry up to two independent markers, and reading them together tells you where a diagram lives and whether it is safe:

Icons shown	What it means	Recoverable from history?
None	Created by Schemity in the default `~/schemity` folder, not inside any repository	No
Git branch	The folder sits inside a Git repository	Yes
Imported	Stored somewhere else on your machine, outside `~/schemity`	Not by itself
Imported + Git branch	Deliberately placed inside a project's own repository	Yes

The last row is the one worth aiming for. It is the arrangement where the ERD travels with the code it describes: same repo, same branch, same pull request, same history. A schema change and the diagram change land in one commit, and a reviewer sees both.

The first row is the one worth auditing. A workspace with no icons is not wrong - a scratch diagram belongs nowhere near a repo - but it should be a choice rather than a discovery.

Why detection has to walk up the directory tree

The obvious implementation checks whether the workspace folder is a repository root. That would be wrong most of the time, because almost nobody puts an ERD at the top level of a project.

A diagram lives in docs/schema/, or db/erd/, or design/. It sits several levels down inside a repository whose root is somewhere above it. So Schemity walks up the directory tree from the workspace folder and reports a match wherever it finds the repository, however deep the workspace is nested. A workspace at ~/work/acme-api/docs/database/ is correctly marked as tracked because ~/work/acme-api/ is the repo.

This matters more than it sounds, because the root-only version of this feature would mislabel exactly the workspaces that are set up correctly. The engineer who did the right thing - filed the diagram in a sensible subdirectory of the project - would see no icon, and the feature would be worse than not having it. A signal that is wrong in the well-organized case teaches you to ignore the signal.

The point is the audit, not the icon

Open the workspace list and read it once. Every workspace with a branch icon is one you can recover, review, and blame. Every workspace without one is a single copy of a design that can simply disappear, and you now know which ones those are rather than assuming the answer.

For the ones that should be tracked and are not, the fix is the same as it has always been: keep the workspace inside a Git repository by moving its folder into the repo where it belongs, or import a fresh workspace from a folder inside the project. What changes is that you can now tell there is something to fix.

That is the whole feature. An ERD tool built for software engineers should not ask you to take version control on faith, any more than your editor asks you to guess which branch you are on.

Why Schemity Has No Zoom: Fuzzy Search and a Minimap Instead

Son Tran — Tue, 04 Aug 2026 17:54:57 +0000

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

TL;DR: Zoom looks free but is not: each zoom changes the diagram's geography so you have to rebuild your sense of where things are, it fights the mouse wheel you need for scrolling, and it leaves you managing a zoom percentage you now have to reset. Schemity has no zoom control at all. The jobs zoom was doing - finding a distant entity, spotting tables a migration added outside the app, seeing where imported entities landed, and getting a first overview of a reverse-engineered schema - are handled by realtime fuzzy search and a minimap that shows where things are without pretending to be a smaller copy of the diagram.

Schemity has no zoom control, and that is a decision rather than an omission. Zoom is one of those features that looks free because every canvas app ships it, but it charges the designer on every single use, and once the jobs it was doing are handled properly there is nothing left for it to pay for.

The question we asked was not "should we add zoom?" It was: what problems does zoom solve, and what problems does it introduce?

What zoom actually costs

It resets your spatial memory. A large ERD is something you navigate by remembering where things are. Orders live down and to the right, identity is up near the top left, the audit tables hang off the bottom. That memory is the thing that makes a big diagram workable at all. Every zoom changes the geography: distances between entities change, what is on screen changes, the shape you had memorized is replaced by a different one. You then spend a few seconds re-establishing where you are. Individually trivial, and paid every single time.

It fights the input it needs. The default binding for zoom on a canvas is the mouse wheel, and the mouse wheel is what you want for scrolling. So a tool either takes the wheel away from scrolling, or it puts zoom behind a modifier - space, control, command plus wheel. Panning around a schema is a constant, high-frequency action. Putting a modifier on either half of that pair taxes something you do hundreds of times an hour.

It creates state you have to manage. Once zoom exists, the diagram has a zoom percentage, and the percentage becomes something you track. Am I at 80% or 60%? Why does this entity look smaller than it did yesterday? How do I get back to 100%? That is a new category of question that had nothing to do with designing a database.

None of this is peculiar to ERDs. It is a known cost of infinite canvases, and even the tools built entirely around zoom run into its edges. A Figma user asked the forum why they could no longer zoom out to see all their content - "is there any reason why i now can not zoom out to see all the content?" - because they wanted to overview the file before zooming into sections. The thread, 105 views and 3 replies, ended with the answer that the file had grown to 100,000px wide and hit Figma's zoom limit. The overview they wanted was not available at any zoom level. Muse's 2020 memo on the infinite canvas made the sharper version of the point: arbitrary zooming lets you pull out to see the landscape and pull back in for detail work, "but this adds another layer of confusion because there is no consistent sense of scale."

What zoom was supposed to solve

The costs only matter if the benefits can be delivered another way, so here are the real jobs, stated fairly. On a large ERD, designers zoom out because they need to:

Reach an area far outside the viewport. The entity you want is somewhere off screen and panning to it is slow.
Spot tables a migration added outside the app. You ran an ORM migration, new tables now exist, and after a re-sync they are placed somewhere on a large canvas. You do not know where.
Find entities you just imported into a context view. They landed somewhere and now need arranging.
Get a first overview of a reverse-engineered database. A freshly imported schema arrives in a default layout, and before arranging anything you need to see the whole shape to reason about it.
Keep track of new entities you created in several places. You have been working across the diagram and want to see what you added.

Every one of those is a genuine need. Notice what they have in common: not one of them is a request to make the diagram smaller. They are all requests to locate something. Zoom is a way of answering a location question by shrinking everything until the answer happens to be on screen, which is a very indirect way to answer it.

How do I navigate a large ER diagram without zooming out?

Two features, split by whether you know what you are looking for.

If you know the name, search. Realtime fuzzy search across entity and field names takes whatever fragment you remember - not necessarily the start, not necessarily contiguous - and updates as you type.

Typing cndtio into a 59-entity schema returns the filter_conditions entity and every filter_condition_id field that references it, across three different tables - even though not one of those names contains the literal string cndtio. Arrow down, hit enter, and the viewport is on it. That is the "reach a distant area" job, done in a keystroke instead of a zoom-pan-zoom cycle.

If you do not know the name, use the minimap. A toggleable minimap sits at the bottom right of the canvas showing every legend and entity in miniature, with a rectangle marking the part the viewport currently covers. Click anywhere on it to jump there, or drag the rectangle to pan continuously. The main view and each context view carry their own minimap, so switching views switches the map with it.

And for the three jobs that are really "where did the new things land?", the minimap answers directly. An entity the database has not confirmed yet is drawn with a dashed border on the canvas, and on the minimap it is drawn in orange - the one case where the minimap uses color at all. In the schema above, the entity count has gone from 59 to 60 and the single orange block tells you exactly where the new table went. So when a re-sync pulls new tables in from the live database after an out-of-app migration, or when entities are imported into a context view, you can see where they landed without hunting for them.

The job	What zoom does	What Schemity does
Reach a distant entity you can name	Zoom out, find it, zoom back in	Fuzzy search, one keystroke to the entity
Find tables a migration added	Zoom out and scan for unfamiliar shapes	Dashed entities visible on the minimap
Locate entities imported into a context view	Zoom out and scan	Dashed on the minimap, exactly where they landed
Get a first overview of a reverse-engineered schema	Zoom out until it fits, if it fits	Minimap shows the whole shape at a fixed size
Move to another part of the diagram	Zoom out, pan, zoom in	Click the minimap, or drag its viewport rectangle

Why the minimap deliberately shows less

The obvious way to build a minimap is to render the diagram smaller. That reproduces the exact problem zoom has, because a shrunken copy of a dense ERD is an unreadable smudge.

So the minimap is not a small diagram. It draws only legends and entities, never relationships, and it ignores the custom colors you assigned to legends and entities on the canvas. It is a map of where things are, not a second copy of the diagram, which is why it stays readable no matter how dense or how colorful the ERD gets. The orange draft marker is the deliberate exception: the map spends its one color on the single question you cannot answer by looking, which is where the new thing went. Dropping the relationship lines is what keeps it legible at minimap size, and the information you lose is information you were not going to be able to read anyway.

That is the whole trade. Zoom offers a continuous range of scales, all of which change the geography and none of which is guaranteed to answer your question. A fixed-size map plus a name search answers the question directly and leaves the diagram exactly where you left it.

The general principle

Every feature that ships has to justify what it costs the people using it, and interaction costs are the easiest kind to overlook because each instance is small. A feature used a hundred times a day at two seconds of re-orientation is not a small feature.

The useful discipline is to write down the jobs a feature is really being asked to do, then ask whether something more direct can do them. For zoom on an ERD, the jobs were all location questions, and location questions have better answers than scale changes. That is also why you do not need a diagram of all 800 tables in the first place: the instinct to see everything at once is usually a navigation problem wearing a layout costume.

Database Views in Your ERD: Read-Only Entities, Not Fake Tables

Son Tran — Sun, 02 Aug 2026 03:02:00 +0000

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

TL;DR: Database views carry real responsibilities - reporting layers, security boundaries, API surfaces - but ERD tools either leave them out entirely (DBML has no view support despite requests since 2022) or draw them as if they were ordinary tables. Schemity displays views and materialized views as read-only entities with italic names and a bold view or mview token in the entity footer, so derived relations are distinguishable from base tables at a glance, and they can be imported into context views like any other entity.

A database view belongs in your ERD, but not disguised as a table: it is a derived, read-only relation, and the diagram should say so at a glance. Schemity draws views and materialized views as read-only entities with italic names - present on the canvas, visually distinct from the base tables they are built on.

That sentence would be unremarkable if the rest of the tooling world agreed with it. Mostly, it does not. In most ERD tools your views are simply absent, and in the rest they are dressed up as something they are not.

The reporting layer your diagram pretends does not exist

Views are not decoration. They are where schemas put their public face: the reporting layer that joins five tables into one readable relation, the security boundary that exposes a subset of columns to an application role, the compatibility shim that survives a refactor. On Supabase, views are how you shape what PostgREST exposes as an API. A materialized view may be the single most performance-critical object in an analytics schema. Whoever reads your diagram to understand the system needs to see them.

Yet the diagram usually cannot show them. DBML - the schema language behind dbdiagram.io - has no syntax for views at all: a user proposed designing views with join definitions in December 2022, others were still upvoting the request in July 2024, and there has been no official reply. SSMS database diagrams accept tables only. And ChartDB, the most active cloud schema visualizer, shipped view support only in v1.15 in August 2025 - a year into the product's life - having earlier demonstrated the opposite failure mode: before views were modeled properly, you could draw a foreign key pointing into a view, which is exactly the kind of nonsense a diagram produces when it cannot tell a derived relation from a base table.

So the designer documenting a real schema gets a choice: leave the views out and hand readers a diagram that hides the reporting layer, or smuggle them in as fake tables and hand readers a diagram that lies about what is editable.

Tool	Database views in the diagram
dbdiagram.io (DBML)	Not supported - requested since Dec 2022, no official reply
SSMS database diagrams	Tables only
ChartDB	Added Aug 2025 (v1.15); earlier versions allowed a foreign key into a view
MySQL Workbench	Supported in EER models
Schemity	Read-only entities with italic names; importable into context views

A view drawn like a table is a small lie

The absence is bad; the disguise is worse. A view rendered as an ordinary table invites every wrong conclusion a reader can draw. It implies you could add a column to it the way you would to a table. It implies a foreign key could land on it. It implies its rows live somewhere, when they are a query executed on demand. Materialized views sharpen the trap: they do hold physical rows and can carry indexes, so they look even more like tables - but their columns are still derived, and the way to change one is to change its defining query, not to edit fields in a dialog.

The diagram should encode that difference, not rely on a naming convention (v_ prefixes are a workaround, not a design) or on the reader's memory of which boxes are real. Schemity uses three signals: views and materialized views are read-only on the canvas, so there is no field editing to be misled by; their names render in italics; and the entity footer says it outright, opening with a bold view or mview token ahead of the usual field and index counts, so nobody is left inferring the kind of relation from a naming convention. The distinction survives at any zoom level and in any export.

The same philosophy as the bold crow's foot for ON DELETE CASCADE: a structural fact about the schema deserves a visual channel of its own, one that cannot go stale.

How do I show database views in an ERD?

Open a diagram backed by your database in Schemity - version 2.9.0 or later - and the views are on the canvas alongside the tables - a desktop ERD tool reading the schema from the live database rather than from a hand-maintained model, so the derived layer is documented from the same source of truth as everything else. You arrange them, color them, and group them under legends exactly like base tables; you just cannot edit what is not editable.

The naming collision is worth untangling here, because Schemity also has a feature called context views, and the two are unrelated the way database view and point of view are unrelated. A context view is a focused sub-diagram that displays a chosen subset of the main diagram while the main view remains the single source of truth. Database views - the SQL kind - are entities inside your diagrams, and they can be imported into a context view like any other entity.

That combination is where the feature earns its keep. The reporting layer is usually its own bounded concern: a handful of views over a dozen base tables, consumed by dashboards and exports. Pull those views and their source tables into one context view and you have the reporting layer as a diagram - what feeds what, which base tables are load-bearing, where a refactor will ripple. The same works for a security-boundary story: one context view showing exactly which relations an application role can see, readable by the auditor who asked without a single query.

Views exist because base tables alone do not tell the whole story of a schema. A diagram that omits them - or forges them - tells even less of it. Put them on the canvas, in italics, where they belong.

SSMS Database Diagrams: Your ERD Is Trapped Inside the Database It Documents

Son Tran — Sun, 02 Aug 2026 03:01:21 +0000

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

TL;DR: SSMS keeps every database diagram as a binary blob in the sysdiagrams system table of the database it describes, so it cannot be saved to a file, versioned in Git, or opened without a server connection - and the feature itself was removed in SSMS 18.0 and restored in 18.1 only after user outcry. Schemity, a desktop ERD tool with SQL Server support, reverse engineers the same schema into a plain local JSON file that diffs in pull requests, exports to SVG and Mermaid, and re-syncs from the live database without losing your layout.

The database diagram you built in SSMS is not a file - it is a row of binary data in a system table called sysdiagrams, inside the very database it documents. That is why you cannot attach it to a design doc, review it in a pull request, or open it after the server is gone. Getting a SQL Server ERD you can actually keep means reverse engineering the schema into a tool that treats the diagram as a document you own.

Most people discover this the day they try to share the diagram. You spent an hour dragging tables into place, the picture finally explains the schema, and a teammate asks for a copy. There is no Save As. One user asked Microsoft directly how to save an SSMS ER diagram to a file so the team could open it, and the accepted workarounds were copy it to the clipboard as an image, or print it to PDF - a screenshot with extra steps. The diagram itself stays where it was born: inside the database, invisible to Git, unreachable without a connection.

A diagram stored as a binary row cannot be shared, diffed, or kept

sysdiagrams has a definition column of type varbinary(max), and your entire diagram - every table position, every annotation - is one opaque value in it. Every consequence follows from that storage decision. Copy the database with a script and the diagram is gone, because schema scripts do not carry system-table contents. Restore an old backup and you get the old diagram, with no way to see what changed. Two DBAs cannot work on the picture without overwriting each other. And nothing about the diagram can be reviewed, because there is nothing readable to review.

The feature's own history says how much weight it can bear. Microsoft removed database diagrams outright in SSMS 18.0 in 2019, brought them back in 18.1 after users pushed back, and only fixed the lingering breakage in 18.6. Even then the designer stayed coupled to the tooling version: connect SSMS 18.12.1 to SQL Server 2022 and it refuses with "This backend version is not supported to design database diagrams or tables" until you upgrade SSMS again. A diagram you cannot export, produced by a designer that has been removed once and version-locked ever since, is not documentation. It is a screensaver for one machine.

There is one more trap worth naming: the SSMS diagram designer doubles as a table designer. Saving a diagram can save table changes with it, so an absent-minded edit inside what you thought was a picture becomes a real ALTER against the database. A diagram should never have that power by accident.

How do I export a database diagram from SSMS to a file?

The honest answer is that you cannot - not as a diagram. The clipboard-image and print-to-PDF workarounds produce pictures that no tool can reopen, and the deeper trick of copying sysdiagrams rows between databases just relocates the blob without ever making it readable. Whatever you produce is frozen at export time and dead to version control.

So the useful move is to stop trying to get the picture out and instead get the schema out - which SQL Server hands to any client that asks the catalog. A desktop ERD tool that speaks SQL Server can connect to the database and reverse engineer the whole schema into a diagram in one step: tables, foreign keys, unique constraints, the lot. Schemity is built for exactly this - an offline, local ERD tool where SQL Server sits alongside PostgreSQL, MySQL, MariaDB, and SQLite as a first-class connection, over a direct connection or an SSH tunnel.

The difference is not the drawing. It is where the drawing lives.

	SSMS database diagram	Schemity diagram
Stored as	Binary row in `sysdiagrams`, inside the database	Plain JSON file in a local folder you choose
Version control	No - binary blob, nothing to diff	Yes - line-by-line diffs in Git and pull requests
Open without the server	No - requires a live connection	Yes - it is a local file
Survives tooling upgrades	Designer version-locked to the engine	File format independent of any server version
Export	Clipboard image or print to PDF	SVG, PNG, JPG, Mermaid, SQL DDL
Schema changes	Saving the diagram can silently alter tables	Nothing applies without a reviewed migration SQL diff

The diagram becomes a file: local JSON, versioned in Git

When Schemity reverse engineers your SQL Server database, the result is a plain JSON file in a workspace folder on your machine - ERD tool JSON storage in the most literal sense. Put that folder in a repository and you have a Git-native ERD: the afternoon you spend arranging tables into domains is committed, branched, and recoverable, and a schema change shows up in review as readable lines instead of an unexplained binary. This is how you version control a database diagram in Git without fighting the format.

Staying current stops being a rewrite, too. Re-sync pulls the latest schema from the live database every time you reopen the diagram - the live database stays the source of truth, new tables arrive ready to place, dropped ones vanish, and every entity you already arranged keeps its position. The layout work survives, which is precisely the work dashboard-style visualizers throw away.

And sharing finally works the way documents work. A teammate opens the diagram file in read-only mode with no access to the connection behind it. The picture leaves as a sharp SVG instead of a blurry clipboard bitmap, or as a Mermaid file that GitHub and Notion render natively, or as the SQL DDL itself when that is the real question. None of it requires the reader to have SSMS, credentials, or a matching tooling version.

Documentation should not live inside the thing it documents

The sysdiagrams design gets one thing exactly backwards: it makes the documentation depend on the system being documented. If the server is down, migrated, restored, or retired, the explanation of the schema goes with it - which is the moment you need the explanation most. The same failure wears other costumes: cloud tools that can lose a diagram outright because it is a row in someone else's database, and dashboards that regenerate the picture from scratch on every visit. The cure is the same each time - make the diagram a local file that outlives every server and every tool version, sitting in your repo next to the code that uses the schema.

Your SQL Server schema deserves a diagram with a path, not a diagram with a connection string. Take it out of the database.

Supabase Schema Diagram: Get an ERD You Can Keep, Not a Dashboard View

Son Tran — Sat, 01 Aug 2026 11:32:04 +0000

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

TL;DR: The Schema Visualizer built into Supabase Studio is a viewer, not a document - it renders one schema at a time, saves your dragged layout only to that browser's localStorage, and exports at most a flat image. The durable way to get a Supabase schema diagram is to connect an ERD tool to the underlying Postgres over the session-mode connection string (port 5432) and reverse engineer it. Schemity treats Supabase as a first-class connection, pulls the schema with every foreign key and constraint intact, keeps your layout across re-syncs, and stores the diagram as a plain JSON file in Git.

To get a Supabase schema diagram you can keep, connect an ERD tool to the project's underlying Postgres database - session mode, port 5432 - and reverse engineer it. The Schema Visualizer inside Supabase Studio will not get you there: it is a viewer bolted onto the dashboard - the layout you make lives in one browser, and the most it can hand you is a flat image.

That distinction takes about a week of real use to feel. The visualizer demos beautifully - open the dashboard, click Database, click Schema Visualizer, and your tables appear with lines between them. Then you try to use it as documentation and the seams show.

What the built-in Schema Visualizer actually gives you

The Schema Visualizer arrived in Supabase Studio 3.0 as an answer to one of the platform's oldest feature requests, and it is genuinely useful for a first glance. But three limits surface quickly.

It shows one schema at a time. A Supabase project is never just public - there are auth, storage, and graphql_public at minimum, and the visualizer renders each in isolation. The picture of how your application tables relate to the platform's is a picture nobody can draw in the dashboard. The gap is real enough that a community member built a separate tool to generate an ERD across all schemas - the "because Visualizer can't" is in the project's own tagline.

The layout lives in your browser, not in the project. Early versions opened vertically stretched and dropped your repositioning entirely; Supabase has since fixed the persistence half, so a dragged table now keeps its position across refreshes. But it keeps it in exactly one place: that browser's localStorage, keyed to the project and schema. Nothing syncs to the project itself - a teammate opening the same visualizer gets the auto-layout, a new laptop starts from zero, clearing site data erases the arrangement, and the relationship lines cannot be reshaped at all. An arrangement only one browser can see is a preference, not documentation.

What leaves is a picture, not a document. The visualizer can download the current view as an SVG or PNG, and each table's three-dot menu will copy that single table's definition as SQL or Markdown - genuinely handy for a quick paste. What it cannot produce is a diagram document: a file another tool can reopen, a layout a teammate inherits, anything Git can diff in a pull request. Even the SVG is not what the extension promises - it is the dashboard's HTML wrapped in a single foreignObject tag with the browser's computed styles inlined on every element. Exporting the same database both ways: 6.4 MB from the visualizer, with no SVG text elements and blank output in vector editors, against 99 KB of true vector from Schemity. The export is a flat image of one schema, frozen at the moment you clicked.

None of this is a flaw in Supabase - the dashboard's job is administering the project, not holding your data model. The mistake is asking a dashboard widget to be the schema diagram your team keeps, a job it was never built for.

How do I get an ER diagram of my Supabase database?

Underneath every Supabase project is a plain PostgreSQL database, and that is the door to walk through. Any tool that can reverse engineer Postgres can diagram a Supabase project - the only Supabase-specific part is picking the right connection string.

Open Connect in the dashboard and you will find three strings. They are not interchangeable for a schema tool:

Connection	Port	Built for	Works for an ERD tool?
Direct connection	5432	Long-lived servers	Yes, but resolves to IPv6 unless you have the IPv4 add-on
Session pooler (Supavisor)	5432	Long-lived connections over IPv4	Yes - the safe default
Transaction pooler	6543	Serverless / edge functions	No - connections are reclaimed after each query and session features are restricted

The session-mode string on port 5432 is the one to use: a schema tool wants exactly one ordinary, long-lived Postgres connection, and session mode provides it over IPv4 from anywhere. The transaction pooler on 6543 exists for a different problem - thousands of short-lived serverless invocations - and its restrictions are noise you do not need.

In Schemity, Supabase is a first-class connection type alongside PostgreSQL, MySQL, MariaDB, SQL Server and SQLite: paste the session string, test the connection before saving it, and the password goes into your operating system's keychain rather than any config file. Tag the connection with its environment - Local, Staging, Production - so the list stays scannable as projects accumulate. Then reverse engineer the schema into an ERD: every table arrives with its foreign keys drawn as crow's foot relationships, and the detail the dashboard never shows is on the entity itself - unique constraints as underlined badges, IN check constraints marking enum-like fields, defaults and nullability printed next to each column.

Because this is a desktop ERD tool reading your database directly, the workflow also survives the situations the dashboard cannot: the diagram opens offline on a flight, and the schema of a client's project never transits any third-party diagram cloud on the way to being documented.

The layout you arrange is the part you own

Reverse engineering gives you the truth; arranging it gives you the understanding. Drag the auth-adjacent tables together, group each domain under a colored legend, route the lines until the picture reads top to bottom - this is analysis work, and in the Studio visualizer it stays locked inside one browser's localStorage.

Schemity persists it structurally. The diagram is a plain JSON file in a folder on your machine, which means the ERD can live in your Git repository and the layout is versioned along with the model. When the schema changes - and on a Supabase project it changes every time you run a migration or click through the Table Editor - re-sync pulls the fresh schema on reopen while every entity you arranged keeps its position. New tables appear ready to drag into place; dropped ones vanish. The live database stays the source of truth for structure, and you stay the source of truth for the picture.

One diagram also does not have to carry everything. A context view is a focused sub-diagram that displays a chosen subset of the main diagram while the main view remains the single source of truth. Carve one per domain - orders, billing, content - and an orange dot on any entity signals a relationship pointing outside the view, so the focused picture never silently lies about coupling. For a project whose public schema has grown past what one canvas can say, that is the difference between a diagram people open and a wall poster people avoid.

Getting the diagram back out

The last thing a dashboard viewer cannot do is hand the picture to someone else, and it is where an owned file pays off repeatedly. Schemity exports the main view or any context view as SVG - sharp at every zoom level - as well as PNG and JPG for chat threads, and full SQL DDL when the question is really about the statements. It also exports a Mermaid erDiagram, which GitHub, GitLab, Notion and Obsidian render natively: the Supabase schema diagram in your README becomes text that renders as a diagram, reviewable in the same pull request as the migration that changed it.

The dashboard visualizer answers "what does the schema look like right now, while I am logged in." A reverse-engineered, re-synced, Git-versioned ERD answers the better question - "what is our data model, and how has it changed" - and answers it for the whole team, indefinitely. Point the tool at the database once, arrange the picture once, and the diagram stops being a second job without ever going stale.