Why Parsing SQL Is Not Enough to Draw Your Database Schema

A tool that draws your schema by parsing migration files has to reimplement the database it pretends to be. Engines disagree on syntax, a large share of migrations are Python, C# or Ruby rather than SQL, and the final schema is the sum of every file ever applied. Running the migrations against a real engine and reading the catalog back avoids all three problems at once.

One parser, six grammars#

There is no such thing as a SQL parser. There are PostgreSQL, MySQL, MariaDB, SQL Server, Oracle and SQLite parsers, and they accept different languages that happen to share keywords. The same three-column table is written differently in each of them:

-- PostgreSQL
CREATE TABLE owners (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- MySQL
CREATE TABLE `owners` (
    `id`         INT NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(50) NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ix_owners_name` (`first_name`)
) ENGINE=InnoDB;

-- SQL Server
CREATE TABLE [dbo].[owners] (
    [id]         INT IDENTITY(1,1) PRIMARY KEY,
    [first_name] NVARCHAR(50) NOT NULL,
    [created_at] DATETIME2 DEFAULT SYSUTCDATETIME()
);

Quoting alone comes in three flavors: double quotes, backticks and square brackets. MySQL lets you declare an index inside CREATE TABLE with the bare word KEY. SQL Server has IDENTITY, PostgreSQL has identity columns and the older SERIAL, Oracle has sequences and, since 12c, identity columns of its own. Then come the types: TIMESTAMPTZ, DATETIME2, NUMBER, VARCHAR2, NVARCHAR(MAX), arrays, enums, domains and JSON. A parser that wants to be correct for all of them has to track every version of every grammar, and it has to know what each engine does with a type it accepts, because INT becomes int4 in PostgreSQL, int in MySQL and NUMBER in Oracle.

Migrations are programs#

Real migration files rarely stay within the declarative subset a parser handles well. Teams write conditional logic so that a script can run twice, guard changes behind existence checks, or loop over tables:

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_name = 'pets' AND column_name = 'microchip') THEN
        ALTER TABLE pets ADD COLUMN microchip VARCHAR(15);
    END IF;
END $$;

IF NOT EXISTS (SELECT 1 FROM sys.columns
               WHERE object_id = OBJECT_ID('dbo.pets') AND name = 'microchip')
    ALTER TABLE dbo.pets ADD microchip NVARCHAR(15);

To know whether microchip exists after these run, a parser would have to evaluate a PL/pgSQL block or a T-SQL batch against a simulated catalog. Add Flyway placeholders such as ${schema}, Liquibase preconditions and contexts, Sqitch verify scripts and stored procedures called from migrations, and the "parser" has quietly become a database engine with worse test coverage than the real one.

Half the ecosystem never writes SQL#

Django, Entity Framework Core, ActiveRecord, Alembic, Knex, Laravel, Doctrine, TypeORM and MikroORM express migrations in the host language. The SQL is generated at run time by the tool, for the specific engine it is connected to, and it is never checked in:

class Migration(migrations.Migration):
    dependencies = [("petclinic", "0001_owners_types_pets")]

    operations = [
        migrations.CreateModel(
            name="Visit",
            fields=[
                ("id", models.AutoField(primary_key=True)),
                ("visit_date", models.DateField(null=True)),
                ("description", models.CharField(max_length=255, null=True)),
                ("pet", models.ForeignKey(on_delete=models.deletion.PROTECT, to="petclinic.pet")),
            ],
            options={"db_table": "visits"},
        ),
    ]

The table name comes from db_table, the foreign key column is called pet_id because Django appends _id, and AutoField maps to integer on PostgreSQL but to int AUTO_INCREMENT on MySQL. None of that is in the file. To draw it you need Django's schema editor, which means you need Django, and the same argument holds for migrationBuilder.CreateTable in EF Core or create_table in a Rails migration. See how Django migrations are run to get a real schema out of them.

The schema is the sum of its history#

Even for pure SQL, a single file is not the schema. Migration number 2 creates pet_types, number 9 renames it to types, number 14 drops a column, number 21 changes a type from INT to BIGINT and number 30 drops a table with CASCADE, which silently removes foreign keys declared on other tables. A parser has to replay every statement in order, apply the engine's rules for each of them, and handle repeatable migrations, out-of-order runs and baseline scripts. Every rule it gets wrong is a table on the diagram that does not exist, or a relation that is missing.

The engine already does this replay, perfectly, every time the migrations run. Asking it afterwards is cheaper than imitating it.

What a real engine gives you#

After the migrations have run, the catalog is the truth. Whether you read information_schema, pg_catalog, sys.tables or JDBC's DatabaseMetaData, you get the same things in the same shape for every engine: tables, columns in order, the type as the engine stores it, nullability, primary keys and foreign keys with the columns they connect. There is nothing to guess.

The last row is the honest trade-off. Execution needs a throw-away database of the right engine and version, which today means Docker, and it takes seconds rather than milliseconds. For a diagram that is refreshed on every pull request, seconds are fine.

What introspection does not see#

Reading the catalog answers structural questions and nothing else. Views, indexes, triggers, check constraints, column comments and, of course, data are either not read or not drawn by most ER tools, including the ones built on execution. A relationship enforced only in application code has no foreign key and therefore no arrow, which is a useful thing to discover, and one reason to declare foreign keys in the first place. Names come back the way the engine stores them, so an Oracle schema written in lower case is drawn in upper case.

The migrations also have to be laid out the way their tool expects. Flyway wants V1__name.sql, Liquibase wants a master changelog, Django wants an app directory with a migrations package. Execution is only as forgiving as the tool.

Where SchemaDrawer fits#

SchemaDrawer is built on the execution side of this argument. You upload the migration files of any of 22 tools, from Flyway and Liquibase to Prisma, Django, EF Core and Diesel. A throw-away PostgreSQL, MySQL, MariaDB, SQL Server, Oracle or SQLite of the version you pick starts in Docker, the real migration tool runs your files against it, the schema is read back over JDBC and rendered as an SVG diagram with tables, columns, types, primary keys and foreign keys. If the migration fails, you get the tool's own error message instead of a guess. It is free, it works over an HTTP API and a GitHub Action, and how it works is described in detail.

Key takeaways#

• SQL is a family of dialects; a diagram tool that parses it has to be correct for every engine and version it claims to support.
• Procedural blocks, placeholders and preconditions cannot be understood without executing them.
• Django, EF Core, Rails, Alembic and the Node ORMs generate SQL at run time; there is nothing to parse.
• The schema is the result of the whole migration history, which the engine replays exactly.
• Executing migrations costs seconds and a container; in exchange the diagram shows what production will actually get.