# LLMSchema

[![Go Version](https://img.shields.io/github/go-mod/go-version/tordrt/llmschema)](go.mod)
[![Release](https://img.shields.io/github/v/release/tordrt/llmschema)](https://github.com/tordrt/llmschema/releases)
[![License](https://img.shields.io/github/license/tordrt/llmschema)](LICENSE)
[![Go Reference](https://pkg.go.dev/badge/github.com/tordrt/llmschema.svg)](https://pkg.go.dev/github.com/tordrt/llmschema)

**Simple database schema docs for LLMs and AI agents.**

LLMSchema extracts database schemas from PostgreSQL, MySQL, and SQLite into
simple, concise Markdown documentation. [See example output](#output-format).

## Why?

AI coding agents need an accurate understanding of your database schema to work effectively on your application. Without dedicated schema documentation, they often have to reconstruct it from application code and migration histories, which is a slower and less reliable use of context.

LLMSchema extracts tables, columns, types, indexes, constraints, and relationships into concise Markdown. It produces a single portable document by default, with optional per-table files.

This gives AI agents a clear and concise understanding of your data model without overwhelming their context window with irrelevant details.

> **Note:** This tool is intended for development databases to aid AI-assisted coding. Do not rely on it for production-critical documentation.

## Installation

### CLI Tool

**With Go installed:**
```bash
# Run the latest or a pinned version directly
go run github.com/tordrt/llmschema/cmd/llmschema@latest --version

# Or install the CLI
go install github.com/tordrt/llmschema/cmd/llmschema@latest
```

For reproducible automation, replace `@latest` with a version such as `@v1.4.1`.

**Quick install (macOS/Linux):**
```bash
curl -fsSL https://raw.githubusercontent.com/tordrt/llmschema/main/install.sh | sh
```

You can also just give your AI coding agent this repository's URL and ask it to
install LLMSchema and set it up for your project.

### Go Library

```bash
go get github.com/tordrt/llmschema
```

## CLI Usage

### Quick Start

Set `DATABASE_URL`:

```bash
export DATABASE_URL="postgres://user:pass@localhost:5432/mydb"
llmschema -o schema.md
```

Or pass the connection string directly:

```bash
llmschema --db-url "postgres://user:pass@localhost:5432/mydb" -o schema.md
```

This writes the complete schema to one Markdown file, beginning with a linked
index of its tables. Without `--output`, LLMSchema writes the same document to
stdout. An explicit `--db-url` takes precedence over `DATABASE_URL`.

### Connection Strings

| Database | Format |
|----------|--------|
| **PostgreSQL** | `postgres://username:password@host:port/db-name` |
| **MySQL** | `mysql://username:password@host:port/db-name` |
| **SQLite** | `sqlite://path/to/db-name.db` |

Replace `db-name` with the name of the database you want to document. For
SQLite, use the path to that database's file. MySQL also accepts the Go driver
form, `mysql://username:password@tcp(host:port)/db-name`.

### Common Examples

These examples assume `DATABASE_URL` is set as shown in the quick start.

**Filter Specific Tables**
```bash
llmschema -o schema.md -t "users,posts,comments"
```

**Exclude Tables**
```bash
llmschema -o schema.md -e "migrations,audit_logs"
```

**Print to stdout**
```bash
llmschema
```

**Omit the Table Index**
```bash
llmschema -o schema.md --no-table-index
```

**Create Focused Single-file Documents**
```bash
llmschema -o schema-core.md -e "audit_logs,analytics_events"
llmschema -o schema-analytics.md -t "analytics_events,dashboards,reports"
```

Table filters let you maintain multiple purpose-specific schema documents when
the complete schema would add unnecessary context.

**Generate One File per Table**
```bash
llmschema -d docs/db-schema
```

Multi-file output creates an overview and one file per table so an agent can
load only the tables relevant to its task. For complex schemas, it can be useful
to generate both formats: keep the single-file schema for general context and
use the per-table files for targeted, in-depth work.

**Automated CI/Migration Integration**
Add to your `Makefile` or migration script to keep docs up-to-date:
```makefile
.PHONY: migrate schema

migrate:
	# Example using Goose; replace with your project's migration command.
	goose postgres "$(DATABASE_URL)" up
	$(MAKE) schema

schema:
	go run github.com/tordrt/llmschema/cmd/llmschema@latest -o schema.md
```

### Command Line Flags

| Flag | Short | Description | Default |
|------|-------|-------------|---------|
| `--db-url` | | Database connection string | `$DATABASE_URL` |
| `--output` | `-o` | Output file for the single-file schema | stdout |
| `--output-dir` | `-d` | Output directory for optional multi-file output | - |
| `--tables` | `-t` | Comma-separated list of tables to extract | All tables |
| `--exclude-tables` | `-e` | Comma-separated list of tables to exclude | - |
| `--schema` | `-s` | Database schema name (PostgreSQL/MySQL) | `public` (PG) / Auto (MySQL) |
| `--no-database-info` | | Exclude database type, version, name, and schema from the output | `false` |
| `--no-table-index` | | Exclude the table index from single-file output | `false` |
| `--version` | | Print the LLMSchema version | - |
| `--preserve-stale-files` | | Keep table files generated by previous runs | `false` |

## AI Agent Integration

Tell your coding agent where the schema documentation is located in
`AGENTS.md`, `CLAUDE.md`, or an equivalent instruction file:

```markdown
## Database schema

Database schema documentation is in `schema.md`.
```

For multi-file output:

```markdown
Database schema documentation is in `docs/db-schema/`. The directory contains
a schema overview and a separate file for each table.
```

## Library Usage

Use `LLMSchema` programmatically in your Go applications.

```go
package main

import (
    "context"
    "log"
    "os"

    "github.com/tordrt/llmschema"
)

func main() {
    schemaFile, err := os.Create("schema.md")
    if err != nil {
        log.Fatal(err)
    }
    defer schemaFile.Close()

    err = llmschema.ExtractAndFormat(
        context.Background(),
        "postgres://user:pass@localhost:5432/mydb",
        &llmschema.Options{
            ExcludeTables: []string{"migrations"},
        },
        &llmschema.OutputOptions{
            Writer: schemaFile,
        },
    )
    if err != nil {
        log.Fatal(err)
    }
}
```

## Output Format

This single-file example was generated from the checked-in PostgreSQL integration
fixture:

```markdown
# Database Schema

**Database:** PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1)
**Name:** `testdb`

**Conventions:** `PK` and `UNIQUE` identify unique keys; their backing indexes are omitted from Additional indexes.

**Tables:**

- [users](#users)
- [orders](#orders)

## users

| Column | Type |
|--------|------|
| id | PK integer NOT NULL DEFAULT nextval('users_id_seq'::regclass) |
| username | varchar(50) NOT NULL UNIQUE |
| email | varchar(100) NOT NULL |
| status | user_status (active, inactive, banned) DEFAULT 'active'::user_status |
| created_at | timestamp DEFAULT CURRENT_TIMESTAMP |

## orders

| Column | Type |
|--------|------|
| id | PK integer NOT NULL DEFAULT nextval('orders_id_seq'::regclass) |
| user_id | integer NOT NULL |
| total_amount | numeric(10,2) NOT NULL |
| order_date | timestamp DEFAULT CURRENT_TIMESTAMP |
| status | order_status (pending, processing, shipped, delivered, cancelled) DEFAULT 'pending'::order_status |

### Additional indexes

- idx_status on (status)
- idx_user_date on (user_id, order_date)

### References

- user_id → users.id (many orders to one users; ON DELETE CASCADE)
```

Sections such as `Additional indexes` and `References` appear only when the
table has that metadata. Primary and unique keys are represented by `PK`,
`UNIQUE`, and explicit composite-key lines, so their backing indexes are not
repeated under `Additional indexes`. Columns the database fills itself are
marked with `AUTO_INCREMENT`, `GENERATED ... AS IDENTITY`, or
`GENERATED ALWAYS AS (expression)`; SQLite generated columns show `GENERATED`
without the expression.

For schemas with many tables, or tables that are individually complex,
`--output-dir docs/db-schema` instead creates an overview plus one Markdown
file per table:

```text
docs/db-schema/
├── .llmschema-manifest.json
├── _overview.md
├── order_items.md
├── orders.md
├── products.md
└── users.md
```

The hidden manifest tracks files generated by LLMSchema so stale table files
can be removed without touching supplemental files in the same directory.

The overview is intentionally small, so an AI agent can discover the available
tables and load only the table files relevant to its task.

### `docs/db-schema/_overview.md`

```markdown
# Schema Overview

**Database:** PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1)
**Name:** `testdb`

**Conventions:** `PK` and `UNIQUE` identify unique keys; their backing indexes are omitted from Additional indexes.

Each table has its own documentation file listed below.

## Tables

- **order_items** (file: `order_items.md`) (references: orders, products)
- **orders** (file: `orders.md`) (references: users)
- **products** (file: `products.md`)
- **users** (file: `users.md`)
```

Each table file contains its columns and, when present, indexes and both
outgoing and incoming relationships.

### `docs/db-schema/orders.md`

```markdown
## orders

| Column | Type |
|--------|------|
| id | PK integer NOT NULL DEFAULT nextval('orders_id_seq'::regclass) |
| user_id | integer NOT NULL |
| total_amount | numeric(10,2) NOT NULL |
| order_date | timestamp DEFAULT CURRENT_TIMESTAMP |
| status | order_status (pending, processing, shipped, delivered, cancelled) DEFAULT 'pending'::order_status |

### Additional indexes

- idx_status on (status)
- idx_user_date on (user_id, order_date)

### References

- user_id → users.id (many orders to one users; ON DELETE CASCADE)

### Referenced by

- order_items.order_id → id (many order_items to one orders)
```

## Contributing

Contributions are welcome! Feel free to open issues or submit pull requests for
new features, database support, or bug fixes.

Some useful areas to explore:

- Views and materialized views across the supported databases
- PostgreSQL triggers and their associated functions
- Table and column comments in generated documentation

For larger features, consider opening an issue first.

## License

MIT License - see [LICENSE](LICENSE) file for details.