How to Auto-Generate Documentation from a CREATE TABLE Statement

Most teams have a schema documentation page somewhere — a Confluence page, a wiki, a README — and most of those pages are at least a little wrong, because updating them is a separate manual step from actually running a migration. The migration ships; the doc doesn't, until someone notices during onboarding or debugging that they don't match.

Why hand-written schema docs drift

The failure isn't laziness, it's structure. A migration is enforced: it has to run or the deploy fails. A documentation update is optional: nothing breaks if it's skipped, so under deadline pressure it is the first thing dropped. Over a year of schema changes the wiki accumulates dropped columns that are still listed, renamed columns under their old names, and new tables that were never added.

The cost shows up later and in other people's time: a new hire builds a query against a column that no longer exists, or an analyst trusts a nullable-vs-required note that stopped being true two migrations ago.

The actual source of truth is already the SQL

A CREATE TABLE statement already contains everything a documentation page is trying to describe: column names and types, which columns are nullable, default values, primary keys, and foreign key relationships to other tables. Generating docs directly from that statement — instead of maintaining a separate prose description of it — means the documentation can't drift from reality between updates, because there's nothing to keep in sync by hand.

What good generated documentation should include

• Every column, with its type, nullability, and default value — the same questions someone new to the table would otherwise have to ask in a chat thread.
• Primary and foreign keys, so relationships between tables are explicit rather than something you have to infer from column naming conventions.
• Uniqueness and check constraints, since UNIQUE and CHECK rules encode business rules (one account per email, a status limited to known values) that are easy to violate if nobody can see them.
• An ER diagram, since a visual map of how tables connect is usually faster to orient with than reading several CREATE TABLE statements in sequence — especially useful for onboarding someone new to a schema with a dozen-plus tables.

A worked example

Take two tables from a typical small app — users and the orders they place:

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  total DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Pasting that into Schema Docs produces three views of the same schema. A data dictionary lists each table's columns with type and constraints. An ER diagram draws orders connected to users through user_id. And a Markdown export gives you a document you can commit straight into a repo. For the orders table the Markdown looks like this:

## Table: `orders`

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `INT` | PRIMARY KEY, AUTO_INCREMENT | — |
| `user_id` | `INT` | FK → users.id, NOT NULL | — |
| `status` | `VARCHAR(20)` | NOT NULL, DEFAULT 'pending' | — |
| `total` | `DECIMAL(10,2)` | NOT NULL | — |

**Relationships:**
- `user_id` → references `users.id`

Because it's plain Markdown, it renders in GitHub, GitLab, Notion and most wikis, and it diffs cleanly in a pull request — a changed column shows up as a changed line.

What DDL can't tell you

Generated docs are accurate about structure but silent about meaning. A column called status with a default of 'pending' doesn't tell a reader which other values are valid or what each one triggers. If that knowledge matters, put it where the database can carry it:

• An ENUM type or a CHECK constraint makes the allowed values part of the schema itself. Schema Docs shows CHECK expressions (column-level and table-level) and the values of a MySQL inline ENUM(...). A PostgreSQL CREATE TYPE ... AS ENUM is a separate statement that Schema Docs does not read, so a column of that type shows only the type name.
• PostgreSQL's COMMENT ON COLUMN and MySQL's inline COMMENT '...' store descriptions inside the database, which is the right place for a note that should travel with the column. Schema Docs picks up descriptions from COMMENT ON TABLE and COMMENT ON COLUMN statements and from inline COMMENT '...' clauses in the text you paste, so include them. If you only paste CREATE TABLE from a tool that leaves the comments out, the Description column stays empty.

Keeping it from going stale again

Auto-generating docs from schema solves the "nobody updated the wiki" problem, but it introduces a smaller version of the same question: when do you regenerate? A workable routine:

1. Commit the generated Markdown to the repo, next to the migrations, as docs/schema.md.
2. Make "regenerate schema.md" a checklist item on any pull request that includes a migration, so the doc changes in the same diff as the schema.
3. Review the Markdown diff as part of code review — it makes the effect of a migration visible to reviewers who don't read DDL fluently.

Keep it a five-minute manual step rather than trying to fully automate it into CI immediately — a lightweight habit that actually happens beats an automated pipeline that never gets built. Teams that outgrow that can later script the same step; the output format doesn't change.

Common questions

Does it work for any SQL dialect?

It reads CREATE TABLE, ALTER TABLE ... ADD CONSTRAINT, CREATE INDEX and COMMENT ON in PostgreSQL, MySQL, SQL Server, Oracle and SQLite styles, including SQL Server scripts that separate batches with GO and Oracle scripts that end blocks with a lone /. ALTER TABLE changes (adding, dropping, renaming and retyping columns, NOT NULL and default changes, adding and dropping constraints) are applied in the order written, so a migration history ends up documenting the final shape. Descriptions stored with SQL Server's sp_addextendedproperty are not read. An ALTER action it does not understand is named in a warning, and the other kinds of statement it skips (GRANT, INSERT, CREATE VIEW ...) are listed in a note under the output instead of being silently skipped, and a statement it cannot parse is reported as an error.

What if my strings contain backslashes?

In MySQL a backslash escapes the quote after it ('it\'s'); in PostgreSQL, SQLite and SQL Server it is an ordinary character. When your text has a backslash right before a quote and the two readings would give different tables, the tool stops with an error instead of guessing, and tells you to mark the dialect (for example with ENGINE=InnoDB or backtick quoting). The check is heuristic. It is skipped for scripts that contain $$-quoted text, so hand-written MySQL with noENGINE or backticks and a $$ somewhere can still be read as PostgreSQL, and SQL Server names in square brackets next to such a string are refused. MySQL \n and \t escapes read as PostgreSQL are kept literally without a warning. If a table or default looks wrong, check the dialect in the result and the text around any backslash.

Is my schema uploaded anywhere?

Not to our servers. Parsing and generation run in your browser, which matters when the schema describes a production system you can't paste into a third-party service. The one exception is the optional "Open in Mermaid Live" button, which sends your diagram to mermaid.live when you click it.

What about schema changes, not just the current shape?

A generated doc describes the schema as it is now. To check whether an upcoming change is safe to run on a live table, see adding a NOT NULL column in PostgreSQL and the risks of dropping a column in production, or run the statements through the Migration Checker.

Generate your docs

Schema Docs takes your CREATE TABLE statements and generates a data dictionary, an ER diagram, and a Markdown export directly from the SQL, without sending your schema to our servers.