schemaextract

README

SQL Schema

A comprehensive Go package for database schema extraction, comparison, and migration planning for MySQL and PostgreSQL databases.

Status: Beta (v0.1.0) - Production-ready core functionality with active development. Feedback welcome!

Features

Schema Extraction

• Complete Schema Extraction: Extract all database objects including tables, views, functions, procedures, triggers, sequences, and more
• Plain Go Structs: No protobuf dependencies - uses idiomatic Go structs with JSON/YAML tags
• Database Support:
  • MySQL 5.7+ and 8.0+
  • PostgreSQL 10+
• Comprehensive Metadata: Captures all schema details including:
  • Tables with columns, indexes, foreign keys, check constraints, partitions
  • Views and materialized views (PostgreSQL)
  • Functions and stored procedures
  • Triggers
  • Sequences (PostgreSQL)
  • Extensions (PostgreSQL)
  • Enum types (PostgreSQL)
  • Events (MySQL)
  • Generated columns
  • Table statistics (row counts, sizes)
• System Object Filtering: Automatically filters out system objects and built-in functions
• Transaction-based Extraction: PostgreSQL extraction uses a single read-only transaction for consistency
• Type Normalization: MySQL type synonyms are canonicalized for consistency

Schema Comparison & Migration Planning

• Engine-Aware Comparison: Intelligent comparison with PostgreSQL and MySQL-specific logic
• Type Alias Recognition: Understands equivalent types (INTEGER=INT=INT4, VARCHAR=CHARACTER VARYING, etc.)
• 11+ Object Types: Compare tables, views, functions, procedures, sequences, enums, events, extensions, and more
• Semantic Expression Comparison: Normalizes and compares expressions semantically
• Risk Assessment: Automatic risk analysis (NONE, LOW, MEDIUM, HIGH) for migration operations
• Dependency Ordering: Topological sort ensures correct execution order
• Multiple Output Formats: Generate reports in Text, Markdown, or JSON
• Auto-Import: Engines register automatically - no manual imports needed

Schema Snapshots

• Point-in-Time Captures: Take snapshots of database schemas with rich metadata
• Multiple Formats: Save/load snapshots in JSON or YAML format
• Integrity Verification: Optional MD5 and SHA256 checksums for snapshot validation
• Metadata Tracking: Track timestamp, version, database engine, tags, and descriptions
• Snapshot Comparison: Compare snapshots to detect schema changes over time
• SQL Export: Export snapshots as SQL DDL statements (CREATE TABLE, etc.)
• Snapshot Management: List, load, validate, and organize snapshot files
• Use Cases:
  • Pre/post-migration snapshots for rollback capability
  • Environment comparison (production vs staging)
  • Schema versioning and change tracking
  • Documentation and audit trails

Installation

go get github.com/nsxbet/sql-schema

Quick Start

MySQL Example

package main

import (
    "context"
    "database/sql"
    "encoding/json"
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql"
    "github.com/nsxbet/sql-schema/mysql"
)

func main() {
    // Open database connection
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/mydb")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create extractor
    extractor := mysql.NewExtractor(db, "mydb")

    // List all databases
    databases, err := extractor.ListDatabases(context.Background())
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Found %d databases\n", len(databases))

    // Extract complete schema
    schema, err := extractor.ExtractSchema(context.Background())
    if err != nil {
        log.Fatal(err)
    }

    // Schema is ready to use
    fmt.Printf("Database: %s\n", schema.Name)
    fmt.Printf("Character Set: %s\n", schema.CharacterSet)
    fmt.Printf("Collation: %s\n", schema.Collation)

    for _, s := range schema.Schemas {
        fmt.Printf("Tables: %d, Views: %d, Functions: %d\n",
            len(s.Tables), len(s.Views), len(s.Functions))
    }

    // Export to JSON
    jsonData, _ := json.MarshalIndent(schema, "", "  ")
    fmt.Println(string(jsonData))
}

PostgreSQL Example

package main

import (
    "context"
    "database/sql"
    "encoding/json"
    "fmt"
    "log"

    _ "github.com/lib/pq"
    "github.com/nsxbet/sql-schema/postgres"
)

func main() {
    // Open database connection
    db, err := sql.Open("postgres", "postgres://user:password@localhost/mydb?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create extractor
    extractor := postgres.NewExtractor(db, "mydb")

    // List all databases (excludes templates and system databases)
    databases, err := extractor.ListDatabases(context.Background())
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Found %d databases\n", len(databases))

    // Extract complete schema
    schema, err := extractor.ExtractSchema(context.Background())
    if err != nil {
        log.Fatal(err)
    }

    // Schema is ready to use
    fmt.Printf("Database: %s\n", schema.Name)
    fmt.Printf("Search Path: %v\n", schema.SearchPath)

    for _, s := range schema.Schemas {
        fmt.Printf("Schema: %s\n", s.Name)
        fmt.Printf("  Tables: %d\n", len(s.Tables))
        fmt.Printf("  Views: %d\n", len(s.Views))
        fmt.Printf("  Materialized Views: %d\n", len(s.MaterializedViews))
        fmt.Printf("  Functions: %d\n", len(s.Functions))
        fmt.Printf("  Procedures: %d\n", len(s.Procedures))
        fmt.Printf("  Sequences: %d\n", len(s.Sequences))
        fmt.Printf("  Extensions: %d\n", len(s.Extensions))
        fmt.Printf("  Enum Types: %d\n", len(s.EnumTypes))
    }

    // Export to JSON
    jsonData, _ := json.MarshalIndent(schema, "", "  ")
    fmt.Println(string(jsonData))
}

API Reference

Core Types

DatabaseSchema

The root structure representing a complete database schema.

type DatabaseSchema struct {
    Name         string    // Database name
    CharacterSet string    // MySQL only: default character set
    Collation    string    // MySQL only: default collation
    SearchPath   []string  // PostgreSQL only: search path
    Schemas      []*Schema // List of schemas (always 1 for MySQL, multiple for PostgreSQL)
}

Schema

Represents a schema (namespace) within a database.

type Schema struct {
    Name               string
    Tables             []*Table
    Views              []*View
    MaterializedViews  []*MaterializedView  // PostgreSQL only
    Functions          []*Function
    Procedures         []*Procedure
    Triggers           []*Trigger
    Sequences          []*Sequence          // PostgreSQL only
    Extensions         []*Extension         // PostgreSQL only
    EnumTypes          []*EnumType          // PostgreSQL only
    Events             []*Event             // MySQL only
}

Table

Complete table metadata including columns, indexes, constraints, and statistics.

type Table struct {
    Name             string
    Columns          []*Column
    Indexes          []*Index
    ForeignKeys      []*ForeignKey
    CheckConstraints []*CheckConstraint
    Triggers         []*Trigger
    Partitions       []*Partition
    Comment          string
    Engine           string  // MySQL only
    Collation        string  // MySQL only
    RowCount         int64
    DataSize         int64
    IndexSize        int64
    DataFree         int64   // MySQL only
}

See types.go for complete type definitions.

MySQL API

package mysql

// NewExtractor creates a new MySQL schema extractor
func NewExtractor(db *sql.DB, databaseName string) *Extractor

// ListDatabases returns a list of all databases
func (e *Extractor) ListDatabases(ctx context.Context) ([]string, error)

// ExtractSchema extracts the complete schema for the configured database
func (e *Extractor) ExtractSchema(ctx context.Context) (*schemaextract.DatabaseSchema, error)

PostgreSQL API

package postgres

// NewExtractor creates a new PostgreSQL schema extractor
func NewExtractor(db *sql.DB, databaseName string) *Extractor

// ListDatabases returns a list of all user databases (excludes templates and system databases)
func (e *Extractor) ListDatabases(ctx context.Context) ([]string, error)

// ExtractSchema extracts the complete schema for the configured database
// Uses a single read-only transaction for consistency
func (e *Extractor) ExtractSchema(ctx context.Context) (*schemaextract.DatabaseSchema, error)

Schema Comparison API

The util package provides comprehensive schema comparison with automatic engine registration:

package util

import (
    "github.com/nsxbet/sql-schema/util"
    "github.com/nsxbet/sql-schema/util/engine"
)

// Note: Engine comparers (PostgreSQL, MySQL, etc.) are automatically registered
// when you import the util package. No need for blank imports!

// CompareOptions configures schema comparison behavior
type CompareOptions struct {
    Engine          engine.Engine  // Database engine (PostgreSQL, MySQL, etc.)
    IgnoreComments  bool           // Ignore comment differences
    IgnoreCharset   bool           // Ignore charset differences
    IgnoreCollation bool           // Ignore collation differences
}

// CompareSchemasDetailed compares two schemas and returns detailed differences
func CompareSchemasDetailed(
    oldSchema *DatabaseSchema,
    newSchema *DatabaseSchema,
    opts *CompareOptions,
) (*diff.MetadataDiff, error)

Example Usage:

import (
    "github.com/nsxbet/sql-schema/util"
    "github.com/nsxbet/sql-schema/util/engine"
    "github.com/nsxbet/sql-schema/util/migration"
    "github.com/nsxbet/sql-schema/util/format"
)

// Compare schemas (engines auto-registered)
opts := &util.CompareOptions{
    Engine: engine.PostgreSQL,
}

diff, err := util.CompareSchemasDetailed(oldSchema, newSchema, opts)
if err != nil {
    log.Fatal(err)
}

// Analyze migration strategy
strategy, _ := migration.AnalyzeStrategy(diff, engine.PostgreSQL)

// Generate outputs in multiple formats
textReport := format.FormatDiffAsText(diff)
mdReport := format.FormatStrategyAsMarkdown(strategy)
jsonSummary, _ := format.FormatStrategyAsSummaryJSON(strategy, true)

Supported Features:

• ✅ All database object types (11+)
• ✅ PostgreSQL and MySQL with type alias recognition
• ✅ Semantic expression comparison
• ✅ Migration risk assessment
• ✅ Dependency ordering
• ✅ Multiple output formats (Text, Markdown, JSON)

See examples/complete_workflow_example.go for a complete example.

Database Support

MySQL

Supported Versions:

• MySQL 5.7+
• MySQL 8.0+
• MariaDB 10.3+

Extracted Objects:

• Tables with all metadata
• Views
• Functions
• Stored procedures
• Triggers
• Events
• Partitions (all types: RANGE, LIST, HASH, KEY)
• Generated columns (VIRTUAL/STORED)

Features:

• Type synonym canonicalization (e.g., BOOLEAN → TINYINT(1))
• Character set and collation detection
• Storage engine information
• Table statistics (row count, data size, index size, data free)

PostgreSQL

Supported Versions:

• PostgreSQL 10+
• PostgreSQL 11+ (for procedures)

Extracted Objects:

• Multiple schemas per database
• Tables with all metadata
• Views
• Materialized views
• Functions
• Procedures (PostgreSQL 11+)
• Triggers
• Sequences
• Extensions
• Enum types
• Partitions (PostgreSQL 10+)
• Generated columns

Features:

• System object filtering (900+ built-in functions filtered)
• Cloud provider object filtering (AWS RDS, GCP CloudSQL, AlloyDB)
• Extension dependency tracking
• Transaction-based extraction for consistency
• Search path support

Configuration

Connection Management

This package accepts *sql.DB connections, giving you full control over:

• Connection pooling
• Timeouts
• SSL/TLS configuration
• Connection string parameters

Example with custom configuration:

import "database/sql"

// MySQL with custom settings
db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/mydb?timeout=30s&readTimeout=60s")
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(time.Hour)

// PostgreSQL with custom settings
db, err := sql.Open("postgres", "postgres://user:password@localhost/mydb?sslmode=require&connect_timeout=30")
db.SetMaxOpenConns(10)

Context Support

All extraction methods accept context.Context for cancellation and timeout control:

ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute)
defer cancel()

schema, err := extractor.ExtractSchema(ctx)

Use Cases

Schema Documentation

Generate comprehensive documentation of your database schema:

schema, _ := extractor.ExtractSchema(ctx)

for _, s := range schema.Schemas {
    for _, table := range s.Tables {
        fmt.Printf("## Table: %s\n", table.Name)
        if table.Comment != "" {
            fmt.Printf("%s\n\n", table.Comment)
        }

        fmt.Println("### Columns")
        for _, col := range table.Columns {
            nullable := "NOT NULL"
            if col.Nullable {
                nullable = "NULL"
            }
            fmt.Printf("- **%s** (%s) %s", col.Name, col.Type, nullable)
            if col.Comment != "" {
                fmt.Printf(" - %s", col.Comment)
            }
            fmt.Println()
        }
        fmt.Println()
    }
}

Schema Comparison

Compare schemas between environments:

prodSchema, _ := prodExtractor.ExtractSchema(ctx)
devSchema, _ := devExtractor.ExtractSchema(ctx)

// Compare table counts
prodTables := len(prodSchema.Schemas[0].Tables)
devTables := len(devSchema.Schemas[0].Tables)

if prodTables != devTables {
    fmt.Printf("Table count mismatch: prod=%d, dev=%d\n", prodTables, devTables)
}

Migration Planning

Identify schema changes for migration planning:

schema, _ := extractor.ExtractSchema(ctx)

// Find tables without primary keys
for _, s := range schema.Schemas {
    for _, table := range s.Tables {
        hasPK := false
        for _, idx := range table.Indexes {
            if idx.Primary {
                hasPK = true
                break
            }
        }
        if !hasPK {
            fmt.Printf("Table %s has no primary key\n", table.Name)
        }
    }
}

Schema Backup

Export complete schema as JSON/YAML for backup or version control:

import (
    "encoding/json"
    "os"
    "gopkg.in/yaml.v3"
)

schema, _ := extractor.ExtractSchema(ctx)

// Export as JSON
jsonFile, _ := os.Create("schema-backup.json")
encoder := json.NewEncoder(jsonFile)
encoder.SetIndent("", "  ")
encoder.Encode(schema)

// Export as YAML
yamlFile, _ := os.Create("schema-backup.yaml")
yamlEncoder := yaml.NewEncoder(yamlFile)
yamlEncoder.Encode(schema)

Examples

See the examples directory for complete working examples:

• examples/mysql - MySQL schema extraction example
• examples/postgres - PostgreSQL schema extraction example
• examples/snapshot - Schema snapshot management example

Each example includes:

• Connection setup
• Database listing
• Schema extraction
• Summary output
• JSON export

Snapshot Example

package main

import (
    "database/sql"
    "github.com/nsxbet/sql-schema/comparer/engine"
    "github.com/nsxbet/sql-schema/snapshot"
)

func main() {
    db, _ := sql.Open("postgres", "postgresql://user:password@localhost:5432/mydb")
    defer db.Close()

    // Take a snapshot with metadata
    snap, _ := snapshot.TakeSnapshot(db, "mydb", engine.PostgreSQL, &snapshot.SnapshotOptions{
        Version:     "1.0",
        Description: "Production database snapshot before migration",
        Tags: map[string]string{
            "environment": "production",
            "purpose":     "pre-migration",
        },
        ComputeHash: true, // Verify integrity
    })

    // Save snapshot
    snapshot.SaveSnapshot(snap, "snapshots/production_before.json")

    // Load and compare snapshots
    mdiff, _ := snapshot.LoadAndCompare(
        "snapshots/production_before.json",
        "snapshots/production_after.json",
        nil, // Auto-detect engine
    )

    // Export as SQL DDL
    snapshot.ExportSnapshotSQL(snap, "snapshots/schema.sql")
}

See examples/snapshot for more comprehensive examples.

Testing

Run the integration tests (requires Docker):

# Run all tests
go test ./...

# Run MySQL tests only
go test ./mysql/...

# Run PostgreSQL tests only
go test ./postgres/...

# Run with verbose output
go test -v ./...

The tests use testcontainers-go to automatically spin up MySQL and PostgreSQL containers, create test schemas, and verify extraction functionality.

Performance

Optimization Tips

1. Use Connection Pooling: Configure sql.DB with appropriate pool settings
2. Set Timeouts: Use context with timeout for large databases
3. Filter Early: The package already filters system objects automatically
4. Transaction Size: PostgreSQL extraction runs in a single transaction - ensure your database can handle read-only transactions for the duration

Benchmarks

Typical extraction times (local environment):

Database Size	MySQL 8.0	PostgreSQL 15
Small (10 tables)	~50ms	~100ms
Medium (100 tables)	~200ms	~400ms
Large (1000 tables)	~2s	~4s

Note: Times vary based on database configuration, hardware, and number of objects.

Troubleshooting

MySQL: "You do not have the SUPER privilege"

When creating functions/procedures, you might encounter:

Error 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled

Solution: Set log_bin_trust_function_creators=1 in MySQL configuration or:

SET GLOBAL log_bin_trust_function_creators = 1;

PostgreSQL: "permission denied for table pg_proc"

The user needs read access to system catalogs.

Solution: Grant necessary permissions:

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO your_user;

Connection Timeout

For large databases, extraction may take longer than default timeout.

Solution: Increase timeout in context:

ctx, cancel := context.WithTimeout(context.Background(), 10*time.Minute)
defer cancel()

Memory Usage

For databases with thousands of tables, extraction may use significant memory.

Solution: Consider extracting specific schemas or tables rather than the entire database.

Dependencies

Core Dependencies

• database/sql - Standard library database interface
• context - Standard library context support

Database Drivers (peer dependencies)

• MySQL: github.com/go-sql-driver/mysql
• PostgreSQL: github.com/lib/pq

Utilities

• github.com/blang/semver/v4 - Version parsing
• golang.org/x/text - Character encoding

Contributing

This package is part of the Bytebase project.

Development Setup

1. Clone the repository:

git clone https://github.com/bytebase/bytebase.git
cd bytebase/sql-schema

1. Install dependencies:

go mod download

1. Run tests:

go test ./...

Code Style

• Follow Google Go Style Guide
• Run gofmt -w . before committing
• Ensure all tests pass
• Add tests for new functionality

License

This package is part of Bytebase and follows the same license.

Related Projects

• Bytebase - Database CI/CD for DevOps teams
• sqlparser - SQL parsers for multiple databases

Support

• GitHub Issues - Bug reports and feature requests
• Discord - Community chat
• Documentation - Bytebase documentation

Documentation

Overview

Package schemaextract provides comprehensive database schema extraction, comparison, and migration management for MySQL and PostgreSQL databases.

Overview

This library enables developers to:

• Extract complete database schemas including tables, views, functions, triggers, and more
• Compare schemas to detect differences and generate migration plans
• Export schemas to JSON, YAML, or SQL formats
• Create point-in-time snapshots for schema versioning
• Generate human-readable schema comparison reports

Supported Databases

• MySQL 5.7+, 8.0+
• PostgreSQL 10+

Quick Start

Extract a MySQL schema:

import (
    "database/sql"
    "github.com/NSXBet/sql-schema/extractor/mysql"
    _ "github.com/go-sql-driver/mysql"
)

db, _ := sql.Open("mysql", "user:pass@tcp(localhost:3306)/mydb")
schema, err := mysql.Extract(db)
if err != nil {
    log.Fatal(err)
}

Compare two schemas:

import "github.com/NSXBet/sql-schema/comparer"

diffs, err := comparer.CompareSchemas(oldSchema, newSchema, nil)
if err != nil {
    log.Fatal(err)
}

Export schema to JSON:

import "github.com/NSXBet/sql-schema/exporter"

err := exporter.ExportJSON(schema, "schema.json")
if err != nil {
    log.Fatal(err)
}

Architecture

The library is organized into several key packages:

• extractor/mysql, extractor/postgres: Database-specific schema extraction
• comparer: Schema comparison and difference detection
• planner: Migration planning and strategy generation
• formatter: Human-readable report generation (text, markdown, JSON)
• exporter: Schema export to various formats
• snapshot: Point-in-time schema snapshots with versioning

Security Considerations

All database queries use parameterized statements to prevent SQL injection. Database credentials should be managed securely using environment variables or secret management systems. Schema information should be treated as sensitive data.

Thread Safety

All exported functions are safe for concurrent use unless otherwise documented.

Beta Status

This library is currently in beta (v0.1.x). API breaking changes may occur between minor versions. Production use is supported but extensive testing is recommended.

Package schemaextract provides database schema extraction for MySQL and PostgreSQL.

Index

• type CheckConstraint
• type Column
• type DatabaseSchema
• type EnumType
• type Event
• type Extension
• type ForeignKey
• type Function
• type Generation
• type GenerationType
• type Identity
• type Index
• type MaterializedView
• type Partition
• type PartitionType
• type Procedure
• type Rule
• type Schema
• type Sequence
• type Snapshot
• type SnapshotMetadata
• type SpatialConfig
• type Table
• type Trigger
• type View

Types

type CheckConstraint

type CheckConstraint struct {
	Name       string `json:"name"       yaml:"name"`
	Expression string `json:"expression" yaml:"expression"`
}

CheckConstraint represents a CHECK constraint.

type Column

type Column struct {
	Name         string      `json:"name"                   yaml:"name"`
	Position     int32       `json:"position"               yaml:"position"`
	Type         string      `json:"type"                   yaml:"type"`
	Nullable     bool        `json:"nullable"               yaml:"nullable"`
	Default      string      `json:"default,omitempty"      yaml:"default,omitempty"`
	Comment      string      `json:"comment,omitempty"      yaml:"comment,omitempty"`
	CharacterSet string      `json:"characterSet,omitempty" yaml:"characterSet,omitempty"`
	Collation    string      `json:"collation,omitempty"    yaml:"collation,omitempty"`
	OnUpdate     string      `json:"onUpdate,omitempty"     yaml:"onUpdate,omitempty"` // MySQL: ON UPDATE CURRENT_TIMESTAMP
	Generation   *Generation `json:"generation,omitempty"   yaml:"generation,omitempty"`
	Identity     *Identity   `json:"identity,omitempty"     yaml:"identity,omitempty"` // PostgreSQL: IDENTITY columns
}

Column represents a table column.

type DatabaseSchema

type DatabaseSchema struct {
	Name         string    `json:"name"                   yaml:"name"`
	CharacterSet string    `json:"characterSet,omitempty" yaml:"characterSet,omitempty"` // MySQL only
	Collation    string    `json:"collation,omitempty"    yaml:"collation,omitempty"`
	SearchPath   []string  `json:"searchPath,omitempty"   yaml:"searchPath,omitempty"` // PostgreSQL only
	Schemas      []*Schema `json:"schemas"                yaml:"schemas"`
}

DatabaseSchema represents the complete schema metadata for a database.

type EnumType

type EnumType struct {
	Name   string   `json:"name"   yaml:"name"`
	Values []string `json:"values" yaml:"values"`
	Schema string   `json:"schema" yaml:"schema"`
}

EnumType represents a PostgreSQL enum type.

type Event

type Event struct {
	Name                string `json:"name"                          yaml:"name"`
	Definition          string `json:"definition"                    yaml:"definition"`
	TimeZone            string `json:"timeZone,omitempty"            yaml:"timeZone,omitempty"`
	SQLMode             string `json:"sqlMode,omitempty"             yaml:"sqlMode,omitempty"`
	CharacterSetClient  string `json:"characterSetClient,omitempty"  yaml:"characterSetClient,omitempty"`
	CollationConnection string `json:"collationConnection,omitempty" yaml:"collationConnection,omitempty"`
	Comment             string `json:"comment,omitempty"             yaml:"comment,omitempty"`
}

Event represents a MySQL scheduled event.

type Extension

type Extension struct {
	Name    string `json:"name"              yaml:"name"`
	Schema  string `json:"schema"            yaml:"schema"`
	Version string `json:"version"           yaml:"version"`
	Comment string `json:"comment,omitempty" yaml:"comment,omitempty"`
}

Extension represents a PostgreSQL extension.

type ForeignKey

type ForeignKey struct {
	Name              string   `json:"name"                yaml:"name"`
	Columns           []string `json:"columns"             yaml:"columns"`
	ReferencedTable   string   `json:"referencedTable"     yaml:"referencedTable"`
	ReferencedColumns []string `json:"referencedColumns"   yaml:"referencedColumns"`
	OnDelete          string   `json:"onDelete,omitempty"  yaml:"onDelete,omitempty"` // CASCADE, SET NULL, RESTRICT, NO ACTION
	OnUpdate          string   `json:"onUpdate,omitempty"  yaml:"onUpdate,omitempty"`
	MatchType         string   `json:"matchType,omitempty" yaml:"matchType,omitempty"` // MySQL: SIMPLE, PARTIAL, FULL
}

ForeignKey represents a foreign key constraint.

type Function

type Function struct {
	Name       string `json:"name"                          yaml:"name"`
	Definition string `json:"definition"                    yaml:"definition"`
	Comment    string `json:"comment,omitempty"             yaml:"comment,omitempty"`
	// MySQL-specific
	SQLMode             string `json:"sqlMode,omitempty"             yaml:"sqlMode,omitempty"`
	CharacterSetClient  string `json:"characterSetClient,omitempty"  yaml:"characterSetClient,omitempty"`
	CollationConnection string `json:"collationConnection,omitempty" yaml:"collationConnection,omitempty"`
	DatabaseCollation   string `json:"databaseCollation,omitempty"   yaml:"databaseCollation,omitempty"`
}

Function represents a database function.

type Generation

type Generation struct {
	Type       GenerationType `json:"type"       yaml:"type"` // VIRTUAL or STORED
	Expression string         `json:"expression" yaml:"expression"`
}

Generation represents a generated column.

type GenerationType

type GenerationType string

GenerationType indicates how a generated column is stored.

const (
	GenerationTypeVirtual GenerationType = "VIRTUAL"
	GenerationTypeStored  GenerationType = "STORED"
)

type Identity

type Identity struct {
	Seed      int64 `json:"seed"               yaml:"seed"`      // Starting value
	Increment int64 `json:"increment"          yaml:"increment"` // Increment value
	Always    bool  `json:"always"             yaml:"always"`    // ALWAYS vs BY DEFAULT
	Cycle     bool  `json:"cycle"              yaml:"cycle"`     // Cycle when reaching max/min
	Cache     int64 `json:"cache,omitempty"    yaml:"cache,omitempty"`
	MinValue  int64 `json:"minValue,omitempty" yaml:"minValue,omitempty"`
	MaxValue  int64 `json:"maxValue,omitempty" yaml:"maxValue,omitempty"`
}

Identity represents a PostgreSQL IDENTITY column.

type Index

type Index struct {
	Name        string         `json:"name"                  yaml:"name"`
	Type        string         `json:"type"                  yaml:"type"` // BTREE, HASH, FULLTEXT, SPATIAL, GIN, GIST, etc.
	Expressions []string       `json:"expressions"           yaml:"expressions"`
	Primary     bool           `json:"primary"               yaml:"primary"`
	Unique      bool           `json:"unique"                yaml:"unique"`
	Visible     bool           `json:"visible"               yaml:"visible"`
	Comment     string         `json:"comment,omitempty"     yaml:"comment,omitempty"`
	KeyLength   []int64        `json:"keyLength,omitempty"   yaml:"keyLength,omitempty"` // MySQL: prefix length, -1 means full column
	Descending  []bool         `json:"descending,omitempty"  yaml:"descending,omitempty"`
	WhereClause string         `json:"whereClause,omitempty" yaml:"whereClause,omitempty"` // Partial index condition
	Spatial     *SpatialConfig `json:"spatial,omitempty"     yaml:"spatial,omitempty"`     // Spatial index configuration
}

Index represents a table index.

type MaterializedView

type MaterializedView struct {
	Name       string    `json:"name"              yaml:"name"`
	Definition string    `json:"definition"        yaml:"definition"`
	Comment    string    `json:"comment,omitempty" yaml:"comment,omitempty"`
	Columns    []*Column `json:"columns,omitempty" yaml:"columns,omitempty"`
}

MaterializedView represents a PostgreSQL materialized view.

type Partition

type Partition struct {
	Name          string        `json:"name"                    yaml:"name"`
	Type          PartitionType `json:"type"                    yaml:"type"`
	Expression    string        `json:"expression,omitempty"    yaml:"expression,omitempty"`
	Value         string        `json:"value,omitempty"         yaml:"value,omitempty"`
	Subpartitions []*Partition  `json:"subpartitions,omitempty" yaml:"subpartitions,omitempty"`
	UseDefault    string        `json:"useDefault,omitempty"    yaml:"useDefault,omitempty"` // Default partition count
}

Partition represents a table partition.

type PartitionType

type PartitionType string

PartitionType represents the partition type.

const (
	PartitionTypeRange        PartitionType = "RANGE"
	PartitionTypeRangeColumns PartitionType = "RANGE_COLUMNS"
	PartitionTypeList         PartitionType = "LIST"
	PartitionTypeListColumns  PartitionType = "LIST_COLUMNS"
	PartitionTypeHash         PartitionType = "HASH"
	PartitionTypeKey          PartitionType = "KEY"
	PartitionTypeLinearHash   PartitionType = "LINEAR_HASH"
	PartitionTypeLinearKey    PartitionType = "LINEAR_KEY"
)

type Procedure

type Procedure struct {
	Name       string `json:"name"                          yaml:"name"`
	Definition string `json:"definition"                    yaml:"definition"`
	Comment    string `json:"comment,omitempty"             yaml:"comment,omitempty"`
	// MySQL-specific
	SQLMode             string `json:"sqlMode,omitempty"             yaml:"sqlMode,omitempty"`
	CharacterSetClient  string `json:"characterSetClient,omitempty"  yaml:"characterSetClient,omitempty"`
	CollationConnection string `json:"collationConnection,omitempty" yaml:"collationConnection,omitempty"`
	DatabaseCollation   string `json:"databaseCollation,omitempty"   yaml:"databaseCollation,omitempty"`
}

Procedure represents a database stored procedure.

type Rule

type Rule struct {
	Name       string `json:"name"              yaml:"name"`
	Event      string `json:"event"             yaml:"event"`  // SELECT, INSERT, UPDATE, DELETE
	Timing     string `json:"timing"            yaml:"timing"` // INSTEAD, ALSO
	Definition string `json:"definition"        yaml:"definition"`
	Comment    string `json:"comment,omitempty" yaml:"comment,omitempty"`
}

Rule represents a PostgreSQL rule.

type Schema

type Schema struct {
	Name       string       `json:"name"                        yaml:"name"`
	Tables     []*Table     `json:"tables,omitempty"            yaml:"tables,omitempty"`
	Views      []*View      `json:"views,omitempty"             yaml:"views,omitempty"`
	Functions  []*Function  `json:"functions,omitempty"         yaml:"functions,omitempty"`
	Procedures []*Procedure `json:"procedures,omitempty"        yaml:"procedures,omitempty"`
	Events     []*Event     `json:"events,omitempty"            yaml:"events,omitempty"` // MySQL only
	// PostgreSQL-specific
	MaterializedViews []*MaterializedView `json:"materializedViews,omitempty" yaml:"materializedViews,omitempty"`
	Sequences         []*Sequence         `json:"sequences,omitempty"         yaml:"sequences,omitempty"`
	Extensions        []*Extension        `json:"extensions,omitempty"        yaml:"extensions,omitempty"`
	EnumTypes         []*EnumType         `json:"enumTypes,omitempty"         yaml:"enumTypes,omitempty"`
	Rules             []*Rule             `json:"rules,omitempty"             yaml:"rules,omitempty"` // PostgreSQL rules (schema-level)
}

Schema represents a database schema (namespace). MySQL has a single unnamed schema, PostgreSQL can have multiple named schemas.

type Sequence

type Sequence struct {
	Name      string `json:"name"              yaml:"name"`
	DataType  string `json:"dataType"          yaml:"dataType"`
	Start     int64  `json:"start"             yaml:"start"`
	Increment int64  `json:"increment"         yaml:"increment"`
	Min       int64  `json:"min"               yaml:"min"`
	Max       int64  `json:"max"               yaml:"max"`
	Cache     int64  `json:"cache"             yaml:"cache"`
	Cycle     bool   `json:"cycle"             yaml:"cycle"`
	Comment   string `json:"comment,omitempty" yaml:"comment,omitempty"`
}

Sequence represents a PostgreSQL sequence.

type Snapshot

type Snapshot struct {
	Metadata SnapshotMetadata `json:"metadata" yaml:"metadata"`
	Schema   *DatabaseSchema  `json:"schema"   yaml:"schema"`
}

Snapshot wraps a DatabaseSchema with metadata for tracking and management. It provides a point-in-time capture of a database schema with additional context.

type SnapshotMetadata

type SnapshotMetadata struct {
	Timestamp      time.Time         `json:"timestamp"                yaml:"timestamp"`
	Version        string            `json:"version"                  yaml:"version"`                  // Snapshot format version
	DatabaseName   string            `json:"databaseName"             yaml:"databaseName"`             // Name of the database
	DatabaseEngine string            `json:"databaseEngine"           yaml:"databaseEngine"`           // "mysql" or "postgres"
	HostInfo       string            `json:"hostInfo,omitempty"       yaml:"hostInfo,omitempty"`       // Optional host connection info
	Tags           map[string]string `json:"tags,omitempty"           yaml:"tags,omitempty"`           // User-defined tags (e.g., "environment": "production")
	Description    string            `json:"description,omitempty"    yaml:"description,omitempty"`    // User-provided description
	ChecksumMD5    string            `json:"checksumMd5,omitempty"    yaml:"checksumMd5,omitempty"`    // MD5 checksum of schema
	ChecksumSHA256 string            `json:"checksumSha256,omitempty" yaml:"checksumSha256,omitempty"` // SHA256 checksum of schema
}

SnapshotMetadata contains tracking information for a schema snapshot.

type SpatialConfig

type SpatialConfig struct {
	BoundingBox  string `json:"boundingBox,omitempty"  yaml:"boundingBox,omitempty"`  // MySQL: bounding box specification
	Tessellation string `json:"tessellation,omitempty" yaml:"tessellation,omitempty"` // MySQL: tessellation scheme
	Storage      string `json:"storage,omitempty"      yaml:"storage,omitempty"`      // Storage parameters
}

SpatialConfig represents spatial index configuration.

type Table

type Table struct {
	Name             string             `json:"name"                       yaml:"name"`
	Columns          []*Column          `json:"columns"                    yaml:"columns"`
	Indexes          []*Index           `json:"indexes,omitempty"          yaml:"indexes,omitempty"`
	ForeignKeys      []*ForeignKey      `json:"foreignKeys,omitempty"      yaml:"foreignKeys,omitempty"`
	CheckConstraints []*CheckConstraint `json:"checkConstraints,omitempty" yaml:"checkConstraints,omitempty"`
	Triggers         []*Trigger         `json:"triggers,omitempty"         yaml:"triggers,omitempty"`
	Partitions       []*Partition       `json:"partitions,omitempty"       yaml:"partitions,omitempty"`
	Rules            []*Rule            `json:"rules,omitempty"            yaml:"rules,omitempty"` // PostgreSQL table-level rules
	Comment          string             `json:"comment,omitempty"          yaml:"comment,omitempty"`
	// MySQL-specific
	Engine        string `json:"engine,omitempty"           yaml:"engine,omitempty"`
	Collation     string `json:"collation,omitempty"        yaml:"collation,omitempty"`
	RowCount      int64  `json:"rowCount,omitempty"         yaml:"rowCount,omitempty"`
	DataSize      int64  `json:"dataSize,omitempty"         yaml:"dataSize,omitempty"`
	IndexSize     int64  `json:"indexSize,omitempty"        yaml:"indexSize,omitempty"`
	DataFree      int64  `json:"dataFree,omitempty"         yaml:"dataFree,omitempty"`
	CreateOptions string `json:"createOptions,omitempty"    yaml:"createOptions,omitempty"`
	Charset       string `json:"charset,omitempty"          yaml:"charset,omitempty"`
}

Table represents a database table.

type Trigger

type Trigger struct {
	Name   string `json:"name"                          yaml:"name"`
	Event  string `json:"event"                         yaml:"event"`  // INSERT, UPDATE, DELETE
	Timing string `json:"timing"                        yaml:"timing"` // BEFORE, AFTER
	Body   string `json:"body"                          yaml:"body"`
	// MySQL-specific
	SQLMode             string `json:"sqlMode,omitempty"             yaml:"sqlMode,omitempty"`
	CharacterSetClient  string `json:"characterSetClient,omitempty"  yaml:"characterSetClient,omitempty"`
	CollationConnection string `json:"collationConnection,omitempty" yaml:"collationConnection,omitempty"`
	Comment             string `json:"comment,omitempty"             yaml:"comment,omitempty"`
}

Trigger represents a database trigger.

type View

type View struct {
	Name       string    `json:"name"              yaml:"name"`
	Definition string    `json:"definition"        yaml:"definition"`
	Comment    string    `json:"comment,omitempty" yaml:"comment,omitempty"`
	Columns    []*Column `json:"columns,omitempty" yaml:"columns,omitempty"`
}

View represents a database view.