# DB Docs

Generate comprehensive and up-to-date documentation for your database schema.

Includes table structures, relationships, and visual ER (Entity-Relationship) diagrams. Validate designs and onboard team members with auto-generated reference materials.

## Best Practices

**Add comments to your database objects whenever making schema changes.**

This tool automatically generates documentation from your database schema, but the quality of that documentation depends on the comments you add to tables and columns. Well-commented schemas produce comprehensive, useful documentation.

### When making database changes:

1. **Add table comments** that explain the purpose and business context
   ```sql
   CREATE TABLE users (
     ...
   ) COMMENT='Stores user account information and authentication details';

   -- Or update existing tables
   ALTER TABLE users COMMENT='Stores user account information and authentication details';
   ```

2. **Add column comments** that clarify meaning, units, or constraints
   ```sql
   CREATE TABLE transactions (
     amount DECIMAL(10,2) COMMENT='Transaction amount in USD',
     status VARCHAR(20) COMMENT='Current status: pending, completed, failed',
     created_at TIMESTAMP COMMENT='UTC timestamp when transaction was initiated'
   );

   -- Or update existing columns
   ALTER TABLE transactions MODIFY amount DECIMAL(10,2) COMMENT='Transaction amount in USD';
   ```

3. **Run `make coverage`** to verify documentation completeness
   - Aim for 100% coverage on critical tables
   - Ideally, all tables should be documented

4. **Run `make doc`** to regenerate documentation

### Why this matters:

- Generated docs become your source of truth
- Team members can understand the schema without asking questions
- Business logic is preserved alongside the technical structure
- `make lint` will flag missing documentation

## Install

Requires [TBLS](https://github.com/k1LoW/tbls?tab=readme-ov-file#install).

```bash
brew install tbls
```

## Setup

Create your `.env` file:
```bash
cp .env.shadow .env
```

### MySQL

Fill in your connection details in `.env`.

### Snowflake

Uncomment the `Snowflake` section in `.env` and fill in your connection details.

### Other

A DSN (Data Source Name) is used to connect to databases, found in the `DB_DSN` environment variable.

To connect to other databases, a custom `DB_DSN` is required. The format is vendor-specific; see [Supported databases and examples DSNs](https://github.com/k1LoW/tbls?tab=readme-ov-file#dsn).

```yaml
# MySQL
dsn: mysql://username:password@localhost:3306/db_name

# Snowflake
dsn: snowflake://username:password@account/db_name/schema_name

# SQLite
dsn: sqlite:///path/to/db_name.db
```

## Usage

### make coverage

Checks documentation coverage to ensure all database objects are properly documented.

```bash
make coverage
```

This reports:
- Which tables lack descriptions
- Which columns lack comments
- Overall documentation coverage percentage
- Helps maintain comprehensive schema documentation

### make doc

Generates database documentation.

```bash
make doc
```

This command:
- Connects to your database
- Analyzes all tables, columns, indexes, and relationships
- Generates documentation in the `schema` directory
- Creates ER diagrams showing table relationships

After running, you'll find:
- `schema/README.md` - Main documentation entry point with database overview
- `schema/<table_name>.md` - Individual markdown files for each table with:
  - Table structure and column definitions
  - Indexes and constraints
  - Foreign key relationships
- `schema/<table_name>.svg` - ER diagrams in SVG format showing relationships

### make lint

Validates your database schema.

```bash
make lint
```

This checks for:
- Missing primary keys
- Missing indexes on foreign keys
- Tables without descriptions
- Custom rules defined in your `.tbls.yml`

### make to_json

Exports the complete database schema as a JSON file for programmatic access or integration with other tools.

```bash
make to_json
```

This creates:
- `schema.json` - A structured JSON representation of your entire database schema
- Useful for code generation, analysis, or tooling integration

### make to_yaml

Exports the complete database schema as a YAML file for programmatic access or integration with other tools.

```bash
make to_yaml
```

This creates:
- `schema.yaml` - A structured YAML representation of your entire database schema
- Useful for code generation, analysis, or tooling integration

## Troubleshooting

### Connection Errors

**Error: `dial tcp [::1]:3306: connect: connection refused`**

The database server is not running or not accessible.
- Verify the database is running. `MySQL`: `mysql -u root -p`
- Check if the port in `.env` is correct.
- If using Docker, ensure the container is running and ports are mapped correctly

**Error: `Access denied for user 'user'@'localhost'`**

Database credentials are incorrect or user lacks permissions.
- Double-check username and password in `.env`
- Verify the user exists. `MySQL`: `SELECT user, host FROM mysql.user;`
- Grant appropriate privileges. The user requires `SELECT` on all tables. `MySQL`:
  ```sql
  GRANT SELECT ON db_name.* TO 'user'@'host';
  GRANT SELECT ON information_schema.* TO 'user'@'host';
  FLUSH PRIVILEGES;
  ```

### Documentation Generation Issues

**No ER diagrams generated**

SVG generation may require additional system dependencies.
- Ensure graphviz is installed: `brew install graphviz` (macOS)
- Check `.tbls.yml` configuration for ER diagram settings

**Empty documentation generated**

The database may have no tables or `tbls` couldn't access them.
- Verify tables exist. `MySQL`: `SHOW TABLES;`
- Check user has `SELECT` privileges on all tables
- Review `.tbls.yml` for any table exclusion rules