Back to all posts

How to Document a MySQL Database Schema in 2026

For the person who owns a MySQL schema and has to explain it to colleagues who never open a database client.

by Roxana Haidiner · 28-Oct-2024 · Updated 11-Nov-2026 · Mysql

On this page

At the end of this guide, you have one HTML5 file that anyone can open in a browser. The file shows a diagram of your MySQL schema and a short description of every table and column.

You store the descriptions in MySQL itself, as comments. Then every database client reads the same text. DbSchema reads the schema with its comments, draws the diagrams and exports the file. Follow these steps:

Write a comment on each table and column in MySQL.
Reverse-engineer the schema into DbSchema.
In DbSchema, add the descriptions that are still missing.
Split the schema into one diagram per topic.
Export the HTML5 documentation.
After each schema change, refresh the model and export again.

Write a comment on each table and column

MySQL stores the description of a table or a column in a COMMENT clause. In MySQL 8.4, a table comment holds up to 2,048 characters and a column comment up to 1,024 (CREATE TABLE). Here are two tables with comments:

CREATE TABLE customer (
  customer_id int PRIMARY KEY,
  email       varchar(120) NOT NULL COMMENT 'Login and invoice address'
) COMMENT = 'Everyone who signed up, paying or not';

CREATE TABLE subscription (
  subscription_id int PRIMARY KEY,
  customer_id     int NOT NULL,
  status          tinyint NOT NULL DEFAULT 1 COMMENT '1 active, 2 paused, 3 cancelled',
  end_date        date COMMENT 'Last paid day. NULL while the plan renews',
  FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
) COMMENT = 'One row per paid plan. A customer can have several';

Where the name of a table or column says too little, write a comment. The comment on status answers a question that a colleague would otherwise ask you: what does 2 mean? The comment on end_date says what NULL stands for. The name customer_id explains itself, so that column needs no comment.

SHOW CREATE TABLE subscription shows the comments, and SHOW FULL COLUMNS FROM subscription lists them in a Comment column. To find the columns that still have no comment, query information_schema, the built-in database that describes every table and column:

SELECT c.table_name, c.column_name
FROM information_schema.columns c
JOIN information_schema.tables t
  ON t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE c.table_schema = DATABASE()
  AND t.table_type = 'BASE TABLE'
  AND COALESCE(c.column_comment, '') = ''
ORDER BY c.table_name, c.ordinal_position;

The query lists every column of the current database that has no comment yet, table by table. The join to information_schema.tables keeps the columns of tables and skips views.

On a table that already exists, ALTER TABLE sets the table comment:

ALTER TABLE subscription COMMENT = 'One row per paid plan. Cancelled plans stay';

To set a column comment, use ALTER TABLE with MODIFY, and repeat the type, NOT NULL and the default. MODIFY replaces the whole definition of the column. MySQL drops every attribute that the new definition leaves out (ALTER TABLE):

ALTER TABLE subscription
  MODIFY status tinyint NOT NULL DEFAULT 1
  COMMENT '1 active, 2 paused, 3 cancelled, 4 refunded';

Before you write the ALTER TABLE, copy the column's definition from SHOW CREATE TABLE. Views take no comment, because CREATE VIEW has no COMMENT clause (CREATE VIEW). A stored procedure or function does take a comment: add COMMENT 'text' to its CREATE statement (CREATE PROCEDURE).

Reverse-engineer the schema into DbSchema

When DbSchema reverse-engineers a schema, it reads the tables, the foreign keys and the comments from MySQL. Then it draws the tables as a diagram:

Choose File → New Model.
Keep Connect to Database selected and click MySQL.
Choose New Connection, and press Connect to MySQL.
Choose Standard as the Method. For a server on another computer, choose Remote computer or custom port and type the Server Host.
Type the Database User and Password, choose the Database, and press Connect.
In Reverse Engineer the Database, tick the schema and press Import.

The tables customer and subscription that DbSchema reverse-engineered, joined by their foreign key, with a comment icon on each described column

DbSchema changes nothing in MySQL when it connects and imports (Connect to Database). DbSchema stores what it read in a model: a copy of the schema, with the diagrams and their layout. For the setup notes on the server, see the DbSchema MySQL page.

Add the missing descriptions in DbSchema

DbSchema can also set the comments in its dialogs:

Double-click the table header on the diagram.
Type the table's description in the Comment box above the tabs, and press OK.
Double-click a column on the diagram, type its description in the Comment box, and press OK.

The DbSchema Edit Table dialog of subscription, with the table comment in the Comment box and the comments of status and end_date in the Columns tab

When you're connected, pressing OK writes the comment to MySQL at once (Tables, Columns and Indexes). For a column comment, DbSchema writes an ALTER TABLE with MODIFY. The MODIFY repeats the type, NOT NULL, the default and AUTO_INCREMENT from DbSchema's model. If someone changed the column outside DbSchema, refresh the model first (see "After each schema change" below).

Split the schema into one diagram per topic

Create one diagram for each part of the application, such as billing or the product catalog. A schema of a few hundred tables doesn't fit on one readable page. Each diagram shows only the tables that belong together (Diagrams):

Choose Diagram → New Diagram, or press Ctrl+L.
Type a Name, such as Billing.
Pick a table in the list on the left and press Include >>.
Press Ok.

The DbSchema New Diagram dialog with the name Orders and the table shop.orders in the Current Diagram list

A table can appear on several diagrams. Each diagram becomes one section of the documentation.

Some MySQL schemas link two tables without declaring a foreign key. The diagram then shows no line between the two tables. Add a virtual foreign key: a foreign key that DbSchema draws in another color and never creates in the database. Drag the child column onto the parent column, tick Virtual in the dialog, and press OK (Foreign Keys).

Where each part of the MySQL documentation lives: the comments in the MySQL database, the diagrams and virtual foreign keys in the DbSchema model, and the HTML5 file exported from the model

Export the HTML5 documentation

Open the diagram you want to document.
Choose File → Export As... → HTML5/PDF Documentation, or press Ctrl+D.
Type a Title, and choose HTML5 under Format.
Under File Path, type the path of the new file. The folder must already exist.
Press Generate.

The DbSchema Schema Documentation dialog with the title Shop Database, the format HTML5, Table of Contents ticked and the file path C:/docs/shop/index.html

To document several diagrams at once, open each diagram in a tab and choose Open Diagrams in the first list under Content. Tick View Query to print the query of each view. The query is the only description of a MySQL view. Tick Procs/Triggers Text to add the SQL source of the procedures and triggers (Schema Documentation).

The free Community Edition documents a diagram of up to 12 tables, with "DbSchema Trial" written across the diagram image. DbSchema Pro documents any diagram. The same dialog also exports PDF and Markdown. Choose PDF to print the documentation or attach it to an email. Choose Markdown to keep it in a Git repository or a wiki.

After each schema change, refresh the model and export again

When a migration adds a column, the documentation is out of date from that moment. Before you export again, bring the change into the model:

Press Refresh in the toolbar, or choose Database → Refresh Model from Database.
If nothing differs, DbSchema says "Schemas are identical".
Otherwise, choose Review Differences to decide on each difference, or Refresh Model to take every difference into the model.

The DbSchema Schema Synchronization dialog reporting three differences between the model and the database, with the Review Differences and Refresh Model buttons

Refreshing the model needs DbSchema Pro. Refresh Model changes only the model, not the database. Any change that exists only in the model is lost (Schema Synchronization). After the refresh, export the documentation again. To export it every night, generate it from a Groovy script with Automation Scripts.

What the HTML5 documentation shows

The export is a single .html file. Any browser opens it from disk, without a web server or an internet connection. You can also put the file on an internal web server or attach it to an email.

The top bar shows the title, the number of tables and the date of the export. Its search box finds tables, columns and comments. Below the diagram, each table has a card with its comment, its columns, their types and constraints, its indexes and its foreign keys. If you ticked Image Mouse-Over Tooltips in the export dialog, point at a column on the diagram to read its comment. Click the column to jump to that column in the table's card.

A table in the HTML5 documentation that DbSchema exports: its comment, its columns with icons, data types and constraints, its indexes and the foreign keys that reference it

Download DbSchema at https://dbschema.com/download.html, connect it to your MySQL server, and export the documentation of one diagram. The free Community Edition covers connecting, reverse-engineering, the diagrams, and the HTML5 documentation of a diagram of up to 12 tables. DbSchema Pro saves the model to a file, documents larger diagrams and synchronizes the model with the database.