71 Generating Schema Documentation Automatically

Documentation is often treated as a boring “side task,” yet almost every senior engineer agrees on one thing: documentation is an investment. One of the most important kinds of documentation is database or API schema documentation, which tends to go stale quickly when maintained by hand. Fortunately, modern tooling already offers automatic and efficient ways to write schema documentation. In this article, I’ll share tips, techniques, and real examples for automating schema documentation—particularly for databases and APIs—using an approach you can apply directly to your day-to-day projects.

Why Is Schema Documentation Important?

Have you ever inherited a project whose database had no documentation at all? Or integrated against an API that only had an outdated Swagger file with no clear descriptions? Here are some of the real problems that arise:

• It makes onboarding new engineers harder
• It’s prone to miscommunication between teams
• It makes debugging take longer
• It slows down the development of new features

With good schema documentation, all of these can be minimized. However, the need for fast updates combined with engineers’ limited time often causes documentation to be neglected.

Options for Automating Schema Documentation

There’s a wide range of tooling that lets us generate schema documentation automatically. I’ll zoom in on two main use cases: database schemas (e.g., PostgreSQL) and API schemas (e.g., a RESTful API with OpenAPI).

1. Database Schema

Modern databases like PostgreSQL store all of their schema metadata in the information schema. Tools such as dbdocs.io, SchemaSpy, or PostgreSQL Autodoc can read that metadata and turn it into visual and narrative documentation.

Example: Using SchemaSpy for PostgreSQL

Suppose we have the following schema:

 1CREATE TABLE users (
 2    id SERIAL PRIMARY KEY,
 3    name VARCHAR(100) NOT NULL,
 4    email VARCHAR(255) UNIQUE NOT NULL,
 5    created_at TIMESTAMP DEFAULT NOW()
 6);
 7
 8CREATE TABLE posts (
 9    id SERIAL PRIMARY KEY,
10    user_id INTEGER REFERENCES users(id),
11    title VARCHAR(255),
12    body TEXT,
13    published_at TIMESTAMP
14);

Using SchemaSpy is very simple:

1docker run --rm -v $PWD:/output schemaspy/schemaspy \
2  -t pgsql \
3  -dp /drivers/postgresql-*.jar \
4  -db mydb -host db.example.com -u dbuser -p secret \
5  -o /output

The result is HTML documentation that includes:

• Table relationships
• Column descriptions (if comments are present)
• An interactive ER diagram

Extracted Result Table

Automation Process Flow Diagram

flowchart LR
    A["Perbarui Schema Database"] --> B["Jalankan SchemaSpy"]
    B --> C["Read Metadata"]
    C --> D["Generate HTML Documentation"]
    D --> E["Team Review"]

flowchart LR
    A["Perbarui Schema Database"] --> B["Jalankan SchemaSpy"]
    B --> C["Read Metadata"]
    C --> D["Generate HTML Documentation"]
    D --> E["Team Review"]

2. API Schema Documentation

If you’re developing an API (REST, GraphQL), it’s better to write the API specification declaratively using a format such as OpenAPI/Swagger or GraphQL SDL. From this specification, the documentation is generated automatically.

Example: Using Swagger/OpenAPI Autogeneration

Suppose you’re using Node.js with Express & TypeScript:

 1import express from 'express';
 2import swaggerUi from 'swagger-ui-express';
 3import swaggerJsdoc from 'swagger-jsdoc';
 4
 5const app = express();
 6
 7const options = {
 8  definition: {
 9    openapi: '3.0.0',
10    info: { title: 'My API', version: '1.0.0' },
11  },
12  apis: ['./routes/*.ts'],
13};
14
15const swaggerSpec = swaggerJsdoc(options);
16app.use('/docs', swaggerUi.serve, swaggerUi.setup(swaggerSpec));

Then in the route file:

 1/**
 2 * @openapi
 3 * /users:
 4 *   get:
 5 *     summary: Get all users
 6 *     tags:
 7 *       - Users
 8 *     responses:
 9 *       200:
10 *         description: List of users
11 */
12app.get('/users', getAllUsersHandler);

Visit /docs – voilà! Interactive documentation is ready for other developers to use.

Advantages

• Always up to date: Every change in the code is reflected in the documentation.
• Automated testing: Many tools can automatically match documentation against the implementation.

How to Keep Documentation Always Up to Date

Automation isn’t a magic bullet if the process isn’t integrated. Here are some tips to keep your schema documentation relevant and prevent it from “rotting”:

a. Integration in CI/CD

On every push to the main branch, add a job to generate the latest documentation, for example:

 1# .github/workflows/build-docs.yml
 2name: Build & Deploy Docs
 3on: [push]
 4jobs:
 5  build:
 6    runs-on: ubuntu-latest
 7    steps:
 8      - uses: actions/checkout@v2
 9      - name: Generate DB Docs
10        run: docker run ... schemaspy ...
11      - name: Publish Docs
12        uses: peaceiris/actions-gh-pages@v3
13        with:
14          publish_dir: ./output

b. A “Docs as Code” Culture

Always treat documentation as part of the codebase: a pull request that changes the schema must update the documentation—and that’s far easier when there’s an automated pipeline.

c. Validating Schema & Documentation Consistency

There are tools like spectral for OpenAPI that can enforce that the documentation matches the code implementation.

Simulation: A Mini Case Study

Suppose you have a small blog application and want to document its schema—both the database and the API—automatically.

1. Database: Use SchemaSpy as in the example above. Commit /output/docs to your repo.
2. API: Apply JSDoc annotations to every endpoint.
3. CI Pipeline: Combine the processes above so that every merge request always triggers a documentation update.
4. Review: The QA/PM team can simply access the /docs folder in the repo or on some hosting platform.

Benefits:

• QA and PMs can see what fields exist before asking the backend engineer.
• Frontend engineers immediately know the response schema without having to guess (or stress out because fields change frequently).
• Documentation becomes part of the SDLC rather than an afterthought.

Conclusion

Generating schema documentation automatically doesn’t eliminate the engineer’s role in explaining the ambiguous parts—it frees up time from the processes that are easy to automate. Once you start using tools like these, you’ll realize that documentation is no longer a burden, but rather a catalyst for collaboration.

Starting today, pick an automation tool that fits your stack. Integrate it into your workflow gradually. Over time, you’ll never have a mysterious “legacy schema” again.

Have questions or additional tips? Feel free to share your experiences automating documentation in the comments below ✨