pgsql

PostgreSQL Reader

Reads schema information directly from a live PostgreSQL database.

Overview

The PostgreSQL Reader connects to a PostgreSQL database and introspects its schema, extracting complete information about tables, columns, constraints, indexes, views, and sequences.

Features

• Direct database introspection
• Extracts complete schema information including:
  • Tables and columns
  • Primary keys, foreign keys, unique constraints, check constraints
  • Indexes
  • Views
  • Sequences
• Supports multiple schemas
• Captures constraint actions (ON DELETE, ON UPDATE)
• Derives relationships from foreign keys

Usage

Basic Example

package main

import (
    "fmt"
    "git.warky.dev/wdevs/relspecgo/pkg/readers"
    "git.warky.dev/wdevs/relspecgo/pkg/readers/pgsql"
)

func main() {
    options := &readers.ReaderOptions{
        ConnectionString: "postgres://user:password@localhost:5432/mydb?sslmode=disable",
    }

    reader := pgsql.NewReader(options)
    db, err := reader.ReadDatabase()
    if err != nil {
        panic(err)
    }

    fmt.Printf("Database: %s\n", db.Name)
    fmt.Printf("Schemas: %d\n", len(db.Schemas))
    for _, schema := range db.Schemas {
        fmt.Printf("  Schema: %s, Tables: %d\n", schema.Name, len(schema.Tables))
    }
}

CLI Example

# Inspect PostgreSQL database and export to JSON
relspec --input pgsql \
  --conn "postgres://user:password@localhost:5432/mydb" \
  --output json \
  --out-file schema.json

# Generate GORM models from PostgreSQL database
relspec --input pgsql \
  --conn "postgres://user:password@localhost:5432/mydb" \
  --output gorm \
  --out-file models.go

# Export database structure to YAML
relspec --input pgsql \
  --conn "postgres://localhost/mydb?sslmode=disable" \
  --output yaml \
  --out-file schema.yaml

Connection String Format

The reader uses PostgreSQL connection strings in the format:

postgres://username:password@hostname:port/database?parameters

Examples:

postgres://localhost/mydb
postgres://user:pass@localhost:5432/mydb
postgres://user@localhost/mydb?sslmode=disable
postgres://user:pass@db.example.com:5432/production?sslmode=require

By default, relspec sets application_name to relspecgo/<version> for PostgreSQL sessions so they are identifiable in pg_stat_activity. If you provide application_name in the connection string, your explicit value is preserved.

Extracted Information

Tables

• Table name and schema
• Comments/descriptions
• All columns with data types, nullable, defaults
• Sequences

Columns

• Column name, data type, length/precision
• NULL/NOT NULL constraints
• Default values
• Auto-increment information
• Primary key designation

Constraints

• Primary keys
• Foreign keys (with ON DELETE/UPDATE actions)
• Unique constraints
• Check constraints

Indexes

• Index name and type (btree, hash, gist, gin, etc.)
• Columns in index
• Unique/non-unique
• Partial indexes

Views

• View definitions
• Column information

Sequences

• Sequence properties
• Associated tables

Extension Types (PostGIS, pgvector)

• Extension column types keep their catalog-formatted form: geometry(Point,4326), geography(Point), vector(1536), halfvec(768), citext, arrays included.

• Built-in types are canonicalized and their dimensions moved to Column.Length / Precision / Scale; extension modifiers stay in Column.Type.

• Index access methods are read from the definition as-is: gist, spgist, brin, hnsw, ivfflat, vchordrq, vchordg, bm25.

• Operator class and WITH (...) parameters have no model field, so they are stored in Index.Comment in the form the PostgreSQL writer reads back:

  opclass=vector_cosine_ops; with (m=16, ef_construction=64)
  

  Ordering modifiers (DESC, NULLS LAST, COLLATE) are not treated as operator classes. Numeric parameter values are unquoted (lists='100' -> lists=100); string values keep their quotes (key_field='id'), and dollar-quoted values are preserved whole.

• Installed extensions are read from pg_extension into schema.Metadata["extensions"] (only extensions RelSpec recognizes), so a read/write round-trip re-creates them.

Notes

• Requires PostgreSQL connection permissions
• Reads all non-system schemas (excludes pg_catalog, information_schema, pg_toast)
• Captures PostgreSQL-specific data types
• Automatically maps PostgreSQL types to canonical types
• Preserves relationship metadata for downstream conversion

Requirements

• Go library: github.com/jackc/pgx/v5
• Database user must have SELECT permissions on system catalogs