sqlc-schema-doc

sqlc-schema-doc

A sqlc process plugin that generates a clean, readable SCHEMA.md from your PostgreSQL migration files. Designed for both humans and AI assistants to quickly understand your database schema without reading dozens of migration files.

Why?

When using sqlc with migrations, your schema is spread across many .up.sql files containing noise like partition functions, seed data, DO $$ blocks, and triggers. This plugin extracts just the essential information — tables, columns, types, constraints, indexes, and enums — into a single markdown file.

Installation

go install github.com/sonereker/sqlc-schema-doc@latest

Configuration

Add the plugin to your sqlc.yaml:

version: "2"
plugins:
  - name: schema-doc
    process:
      cmd: sqlc-schema-doc

sql:
  # Your existing query blocks stay unchanged...

  # Add this block for schema documentation
  - engine: "postgresql"
    queries: "queries/schema_doc.sql"
    schema: "queries/schema_doc.sql"
    codegen:
      - plugin: schema-doc
        out: "."
        options:
          migrations_dir: "../db/migrations"
    gen:
      go:
        sql_package: "pgx/v5"
        package: "schemadoc"
        out: "generated/schemadoc/"

Create a dummy query file (queries/schema_doc.sql):

-- name: SchemaDocDummy :exec
SELECT 1;

Usage

sqlc generate

This generates SCHEMA.md alongside your normal sqlc output. Every time you run sqlc generate, the schema doc is regenerated automatically.

Plugin Options

Custom excludes

options:
  migrations_dir: "../db/migrations"
  exclude:
    - "river_"
    - "pg_"
    - "temp_"

How It Works

1. The plugin reads all *.up.sql files from the specified migrations directory
2. Parses them using PostgreSQL's actual SQL parser (pg_query_go)
3. Extracts CREATE TABLE, CREATE TYPE ... AS ENUM, ALTER TABLE, CREATE INDEX, DROP TABLE, and DROP TYPE statements
4. Removes tables and enums dropped by later migrations so the output reflects the current schema (set include_deleted_objects: true to keep them)
5. Skips everything else (functions, triggers, inserts, DO blocks)
6. Generates clean markdown with tables, columns, types, nullability, primary keys, unique constraints, defaults, foreign key references, indexes, and check constraints

Example Output

# Database Schema

> Auto-generated by sqlc-schema-doc. Do not edit.

## Enums

### user_role
`admin` | `member` | `viewer`

## Tables

### users

| Column | Type | Nullable | PK | Unique | Default | References |
|--------|------|----------|----|--------|---------|------------|
| id | uuid | NO | YES |  | gen_random_uuid() |  |
| email | citext | NO |  | YES |  |  |
| name | text | YES |  |  |  |  |
| role | user_role | NO |  |  |  |  |
| created_at | timestamptz | YES |  |  | now() |  |

### posts

| Column | Type | Nullable | PK | Unique | Default | References |
|--------|------|----------|----|--------|---------|------------|
| id | uuid | NO | YES |  | gen_random_uuid() |  |
| user_id | uuid | NO |  |  |  | users(id) |
| title | text | NO |  |  |  |  |
| tags | text[] | YES |  |  |  |  |
| published_at | timestamptz | YES |  |  |  |  |

**Check constraints:**

- `positive_price`: price > 0

**Indexes:**

- `idx_posts_user_id` INDEX on (user_id)

License

MIT