# RelSpec

[![Release](https://img.shields.io/gitea/v/release/wdevs/relspecgo?gitea_url=https://git.warky.dev&label=release)](https://git.warky.dev/wdevs/relspecgo/releases/latest)
[![CI](https://git.warky.dev/wdevs/relspecgo/actions/workflows/ci.yml/badge.svg)](https://git.warky.dev/wdevs/relspecgo/actions/workflows/ci.yml)
[![Integration Tests](https://git.warky.dev/wdevs/relspecgo/actions/workflows/integration-tests.yml/badge.svg)](https://git.warky.dev/wdevs/relspecgo/actions/workflows/integration-tests.yml)
[![Go Version](https://img.shields.io/badge/go-1.24.0-blue.svg)](https://go.dev/dl/)
[![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](LICENSE)

> Database Relations Specification Tool for Go

RelSpec is a comprehensive database relations management tool that reads, transforms, and writes database table specifications across multiple formats and ORMs.

## Overview

RelSpec provides bidirectional conversion, comparison, and validation of database specification formats, allowing you to:
- Inspect live databases and extract their structure
- Validate schemas against configurable rules and naming conventions
- Convert between different ORM models (GORM, Bun, etc.)
- Transform legacy schema definitions (Clarion DCTX, XML, JSON, etc.)
- Generate standardized specification files (JSON, YAML, etc.)
- Compare database schemas and track changes

![1.00](./assets/image/relspec1_512.jpg)

## Features

### Readers (Input Formats)

RelSpec can read database schemas from multiple sources:

#### ORM Models
- [GORM](pkg/readers/gorm/README.md) - Go GORM model definitions
- [Bun](pkg/readers/bun/README.md) - Go Bun model definitions
- [Drizzle](pkg/readers/drizzle/README.md) - TypeScript Drizzle ORM schemas
- [Prisma](pkg/readers/prisma/README.md) - Prisma schema language
- [TypeORM](pkg/readers/typeorm/README.md) - TypeScript TypeORM entities

#### Database Inspection
- [PostgreSQL](pkg/readers/pgsql/README.md) - Direct PostgreSQL database introspection

#### Schema Formats
- [DBML](pkg/readers/dbml/README.md) - Database Markup Language (dbdiagram.io)
- [DCTX](pkg/readers/dctx/README.md) - Clarion database dictionary format
- [DrawDB](pkg/readers/drawdb/README.md) - DrawDB JSON format
- [GraphQL](pkg/readers/graphql/README.md) - GraphQL Schema Definition Language (SDL)
- [JSON](pkg/readers/json/README.md) - RelSpec canonical JSON format
- [YAML](pkg/readers/yaml/README.md) - RelSpec canonical YAML format

### Writers (Output Formats)

RelSpec can write database schemas to multiple formats:

#### ORM Models
- [GORM](pkg/writers/gorm/README.md) - Generate GORM-compatible Go structs
- [Bun](pkg/writers/bun/README.md) - Generate Bun-compatible Go structs
- [Drizzle](pkg/writers/drizzle/README.md) - Generate Drizzle ORM TypeScript schemas
- [Prisma](pkg/writers/prisma/README.md) - Generate Prisma schema files
- [TypeORM](pkg/writers/typeorm/README.md) - Generate TypeORM TypeScript entities

#### Database DDL
- [PostgreSQL](pkg/writers/pgsql/README.md) - PostgreSQL DDL (CREATE TABLE, etc.)

#### Schema Formats
- [DBML](pkg/writers/dbml/README.md) - Database Markup Language
- [DCTX](pkg/writers/dctx/README.md) - Clarion database dictionary format
- [DrawDB](pkg/writers/drawdb/README.md) - DrawDB JSON format
- [GraphQL](pkg/writers/graphql/README.md) - GraphQL Schema Definition Language (SDL)
- [JSON](pkg/writers/json/README.md) - RelSpec canonical JSON format
- [YAML](pkg/writers/yaml/README.md) - RelSpec canonical YAML format

### Inspector (Schema Validation)

RelSpec includes a powerful schema validation and linting tool:

- [Inspector](pkg/inspector/README.md) - Validate database schemas against configurable rules
  - Enforce naming conventions (snake_case, camelCase, custom patterns)
  - Check primary key and foreign key standards
  - Detect missing indexes on foreign keys
  - Prevent use of SQL reserved keywords
  - Ensure schema integrity (missing PKs, orphaned FKs, circular dependencies)
  - Support for custom validation rules
  - Multiple output formats (Markdown with colors, JSON)
  - CI/CD integration ready

## Use of AI
[Rules and use of AI](./AI_USE.md)

## User Interface

RelSpec provides an interactive terminal-based user interface for managing and editing database schemas. The UI allows you to:

- **Browse Databases** - Navigate through your database structure with an intuitive menu system
- **Edit Schemas** - Create, modify, and organize database schemas
- **Manage Tables** - Add, update, or delete tables with full control over structure
- **Configure Columns** - Define column properties, data types, constraints, and relationships
- **Interactive Editing** - Real-time validation and feedback as you make changes

The interface supports multiple input formats, making it easy to load, edit, and save your database definitions in various formats.

<p align="center" width="100%">
<img  src="./assets/image/screenshots/main_screen.jpg"> 
</p>
<p align="center" width="100%">
<img  src="./assets/image/screenshots/table_view.jpg"> 
</p>
<p align="center" width="100%">
<img src="./assets/image/screenshots/edit_column.jpg"> 
</p>


## Installation

```bash
go get github.com/wdevs/relspecgo

go install -v git.warky.dev/wdevs/relspecgo/cmd/relspec@latest
```

## Usage

### Interactive Schema Editor

```bash
# Launch interactive editor with a DBML schema
relspec edit --from dbml --from-path schema.dbml --to dbml --to-path schema.dbml

# Edit PostgreSQL database in place
relspec edit --from pgsql --from-conn "postgres://user:pass@localhost/mydb" \
             --to pgsql --to-conn "postgres://user:pass@localhost/mydb"

# Edit JSON schema and save as GORM models
relspec edit --from json --from-path db.json --to gorm --to-path models/
```

The `edit` command launches an interactive terminal user interface where you can:
- Browse and navigate your database structure
- Create, modify, and delete schemas, tables, and columns
- Configure column properties, constraints, and relationships
- Save changes to various formats
- Import and merge schemas from other databases

### Schema Merging

```bash
# Merge two JSON schemas (additive merge - adds missing items only)
relspec merge --target json --target-path base.json \
              --source json --source-path additions.json \
              --output json --output-path merged.json

# Merge PostgreSQL database into JSON, skipping specific tables
relspec merge --target json --target-path current.json \
              --source pgsql --source-conn "postgres://user:pass@localhost/source_db" \
              --output json --output-path updated.json \
              --skip-tables "audit_log,temp_tables"

# Cross-format merge (DBML + YAML → JSON)
relspec merge --target dbml --target-path base.dbml \
              --source yaml --source-path additions.yaml \
              --output json --output-path result.json \
              --skip-relations --skip-views
```

The `merge` command combines two database schemas additively:
- Adds missing schemas, tables, columns, and other objects
- Never modifies or deletes existing items (safe operation)
- Supports selective merging with skip options (domains, relations, enums, views, sequences, specific tables)
- Works across any combination of supported formats
- Perfect for integrating multiple schema definitions or applying patches

### Schema Conversion

```bash
# Convert PostgreSQL database to GORM models
relspec convert --from pgsql --from-conn "postgres://user:pass@localhost/mydb" \
                --to gorm --to-path models/ --package models

# Convert GORM models to Bun
relspec convert --from gorm --from-path models.go \
                --to bun --to-path bun_models.go --package models

# Export database schema to JSON
relspec convert --from pgsql --from-conn "postgres://..." \
                --to json --to-path schema.json

# Convert DBML to PostgreSQL SQL
relspec convert --from dbml --from-path schema.dbml \
                --to pgsql --to-path schema.sql
```

### Schema Validation

```bash
# Validate a PostgreSQL database with default rules
relspec inspect --from pgsql --from-conn "postgres://user:pass@localhost/mydb"

# Validate DBML file with custom rules
relspec inspect --from dbml --from-path schema.dbml --rules .relspec-rules.yaml

# Generate JSON validation report
relspec inspect --from json --from-path db.json \
                --output-format json --output report.json

# Validate specific schema only
relspec inspect --from pgsql --from-conn "..." --schema public
```

### Schema Comparison

```bash
# Compare two database schemas
relspec diff --from pgsql --from-conn "postgres://localhost/db1" \
             --to pgsql --to-conn "postgres://localhost/db2"
```

## Project Structure

```
relspecgo/
├── cmd/
│   └── relspec/      # CLI application (convert, inspect, diff, scripts)
├── pkg/
│   ├── readers/      # Input format readers (DBML, GORM, PostgreSQL, etc.)
│   ├── writers/      # Output format writers (GORM, Bun, SQL, etc.)
│   ├── inspector/    # Schema validation and linting
│   ├── diff/         # Schema comparison
│   ├── models/       # Internal data models
│   ├── transform/    # Transformation logic
│   └── pgsql/        # PostgreSQL utilities (keywords, data types)
├── examples/         # Usage examples
└── tests/            # Test files
```

## Todo

[Todo List of Features](./TODO.md)

## Development

### Prerequisites
- Go 1.21 or higher
- Access to test databases (optional)

### Building

```bash
go build -o relspec ./cmd/relspec
```

### Testing

```bash
go test ./...
```

## License

Apache License 2.0 - See [LICENSE](LICENSE) for details.

Copyright 2025 Warky Devs

## Contributing

Contributions welcome. Please open an issue or submit a pull request.