MySQL schema diagram automation: the FK graph lives in information_schema, unless your engine dropped it

The structure-only dump is the canonical artifact: every CREATE TABLE with its columns, keys, and engine clause, and with --routines and --triggers the stored logic too. Because the output is deterministic text, it diffs cleanly between releases, which turns the dump into the change record your regeneration reacts to, the same role a migrations directory plays for teams that keep DDL in the repo, a pattern covered in living diagrams from SQL DDL.

mysqldump --no-data --routines --triggers shop > schema.sql

Foreign keys live in information_schema.KEY_COLUMN_USAGE, where a non-null REFERENCED_TABLE_NAME marks a constraint edge. One query returns the full edge list with the columns on both ends; joining REFERENTIAL_CONSTRAINTS adds the ON DELETE and ON UPDATE rules, which are worth carrying onto the diagram as edge annotations, since a CASCADE is exactly the kind of fact a reviewer wants to see before approving a migration.

SELECT k.table_schema, k.table_name, k.column_name,
       k.referenced_table_schema, k.referenced_table_name,
       k.referenced_column_name, r.delete_rule
FROM information_schema.KEY_COLUMN_USAGE k
JOIN information_schema.REFERENTIAL_CONSTRAINTS r
  ON r.constraint_schema = k.constraint_schema
 AND r.constraint_name   = k.constraint_name
WHERE k.referenced_table_name IS NOT NULL;

Here is the failure mode unique to MySQL estates with history: a FOREIGN KEY clause on a MyISAM table is parsed and discarded. The server accepts the DDL without error, stores no constraint, enforces nothing, and KEY_COLUMN_USAGE returns no row. On a legacy database that predates the InnoDB default, whole regions of the ER diagram silently lose their arrows, and the diagram looks like a parts catalog instead of a model.

The fix starts with knowing where the gaps are: information_schema.TABLES carries an ENGINE column, so one query lists every table whose constraints the engine cannot hold. For those tables the edges must be inferred, from column naming conventions like customer_id or from the application's model layer, and an inferred edge should look different on the diagram than a declared one. An agent doing the redraw can apply both steps: query the declared edges, infer the missing ones, and style them apart.

SELECT table_name, engine
FROM information_schema.TABLES
WHERE table_schema = 'shop' AND engine <> 'InnoDB';

First diagram: paste the structure dump into the database schema diagram generator and edit the result. The loop that keeps it true runs in CI: a job filtered on the migrations directory applies the migrations to a scratch database, runs the dump and the two queries above, and asks an agent connected to Datadef's MCP server, registry name io.datadef/mcp, to update the existing diagram. Updating in place preserves the layout and the manual annotations; regeneration from scratch would throw them away each time.

The honest version of the automation claim: Datadef does not watch the repository or the database, and nothing detects your ALTER TABLE by itself. The migration merge is the detection, the CI job is the trigger, and the redraw is one agent call authenticated with an API key, available on paid plans.

Cross-database references complicate the zone story: KEY_COLUMN_USAGE reports edges across schemas on the same server, but if your platform splits across servers, no metadata query sees the seams, and those edges come from application config only.

And the standing limits: a live-embedded diagram requires the project shared public, so a private schema belongs in an exported image or behind your own wall. The loop maintains the diagram; the prose around it, column meanings and ownership notes, is a documentation problem the diagram does not solve, covered in keeping database schema docs in sync.

• Database schema diagram generator
• A living diagram from SQL DDL
• A living diagram from Postgres
• Keep database schema docs in sync