pgsql

package
v1.0.70 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 18, 2026 License: Apache-2.0 Imports: 16 Imported by: 0

README

PostgreSQL Writer

Generates PostgreSQL DDL (Data Definition Language) SQL scripts from database schema information.

Overview

The PostgreSQL Writer converts RelSpec's internal database model representation into PostgreSQL-compatible SQL DDL scripts, including CREATE TABLE statements, constraints, indexes, views, and sequences.

Features

  • Generates complete PostgreSQL DDL
  • Creates schemas, tables, columns
  • Defines constraints (PK, FK, unique, check)
  • Creates indexes
  • Generates views and sequences
  • Supports migration scripts
  • Includes audit triggers (optional)
  • Handles PostgreSQL-specific data types

Usage

Basic Example
package main

import (
    "git.warky.dev/wdevs/relspecgo/pkg/models"
    "git.warky.dev/wdevs/relspecgo/pkg/writers"
    "git.warky.dev/wdevs/relspecgo/pkg/writers/pgsql"
)

func main() {
    options := &writers.WriterOptions{
        OutputPath: "schema.sql",
    }

    writer := pgsql.NewWriter(options)
    err := writer.WriteDatabase(db)
    if err != nil {
        panic(err)
    }
}
CLI Examples
# Generate PostgreSQL DDL from JSON schema
relspec --input json \
  --in-file schema.json \
  --output pgsql \
  --out-file schema.sql

# Convert GORM models to PostgreSQL DDL
relspec --input gorm \
  --in-file models.go \
  --output pgsql \
  --out-file create_tables.sql

# Export live database schema to SQL
relspec --input pgsql \
  --conn "postgres://localhost/source_db" \
  --output pgsql \
  --out-file backup_schema.sql

Generated SQL Example

-- Schema: public

CREATE SCHEMA IF NOT EXISTS public;

-- Table: public.users

CREATE TABLE IF NOT EXISTS public.users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    bio TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Constraints for public.users

ALTER TABLE public.users
    ADD CONSTRAINT uq_users_username UNIQUE (username);

-- Indexes for public.users

CREATE INDEX idx_users_email ON public.users (email);

-- Table: public.posts

CREATE TABLE IF NOT EXISTS public.posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Foreign Keys for public.posts

ALTER TABLE public.posts
    ADD CONSTRAINT fk_posts_user_id
    FOREIGN KEY (user_id)
    REFERENCES public.users (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

-- Indexes for public.posts

CREATE INDEX idx_posts_user_id ON public.posts (user_id);

Writer Options

Metadata Options
options := &writers.WriterOptions{
    OutputPath: "schema.sql",
    Metadata: map[string]interface{}{
        "include_drop":    true,  // Include DROP statements
        "include_audit":   true,  // Include audit triggers
        "if_not_exists":   true,  // Use IF NOT EXISTS
        "migration_mode":  false, // Generate migration script
    },
}

Features

Full DDL Generation

Generates complete database structure:

  • CREATE SCHEMA statements
  • CREATE TABLE with all columns and types
  • PRIMARY KEY constraints
  • FOREIGN KEY constraints with actions
  • UNIQUE constraints
  • CHECK constraints
  • CREATE INDEX statements
  • CREATE VIEW statements
  • CREATE SEQUENCE statements
Migration Mode

When migration_mode is enabled, generates migration scripts with:

  • Version tracking
  • Up/down migrations
  • Transactional DDL
  • Rollback support
Audit Triggers

When include_audit is enabled, adds:

  • Created/updated timestamp triggers
  • Audit logging functionality
  • Change tracking

PostgreSQL-Specific Features

  • Serial types (SERIAL, BIGSERIAL)
  • Advanced types (UUID, JSONB, ARRAY)
  • Schema-qualified names
  • Constraint actions (CASCADE, RESTRICT, SET NULL)
  • Partial indexes
  • Function-based indexes
  • Check constraints with expressions

Data Types

Supports all PostgreSQL data types:

  • Integer types: SMALLINT, INTEGER, BIGINT, SERIAL, BIGSERIAL
  • Numeric types: NUMERIC, DECIMAL, REAL, DOUBLE PRECISION
  • String types: VARCHAR, CHAR, TEXT
  • Date/Time: DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL
  • Boolean: BOOLEAN
  • Binary: BYTEA
  • JSON: JSON, JSONB
  • UUID: UUID
  • Network: INET, CIDR, MACADDR
  • Special: ARRAY, HSTORE

Notes

  • Generated SQL is formatted and readable
  • Comments are preserved from source schema
  • Schema names are fully qualified
  • Default values are properly quoted
  • Constraint names follow PostgreSQL conventions
  • Compatible with PostgreSQL 12+

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func TemplateFunctions 

func TemplateFunctions() map[string]interface{}

TemplateFunctions returns a map of custom template functions

Types

type AddColumnData 

type AddColumnData struct {
	SchemaName string
	TableName  string
	ColumnName string
	ColumnType string
	Default    string
	NotNull    bool
}

AddColumnData contains data for add column template

type AddColumnWithCheckData added in v1.0.26 

type AddColumnWithCheckData struct {
	SchemaName       string
	TableName        string
	ColumnName       string
	ColumnDefinition string
}

AddColumnWithCheckData contains data for add column with existence check template

type AlterColumnDefaultWithCheckData added in v1.0.68 

type AlterColumnDefaultWithCheckData struct {
	SchemaName   string
	TableName    string
	ColumnName   string
	SetDefault   bool
	DefaultValue string
}

AlterColumnDefaultWithCheckData contains data for the guarded alter column default template, which only alters existing columns whose live default differs from the desired one.

type AlterColumnNullabilityWithCheckData added in v1.0.68 

type AlterColumnNullabilityWithCheckData struct {
	SchemaName string
	TableName  string
	ColumnName string
	NotNull    bool
}

AlterColumnNullabilityWithCheckData contains data for the guarded alter column nullability template, which only alters existing columns whose live NOT NULL state differs from the desired one.

type AlterColumnTypeWithCheckData added in v1.0.56 

type AlterColumnTypeWithCheckData struct {
	SchemaName      string
	TableName       string
	ColumnName      string
	NewType         string
	EquivalentTypes string
	UsingExpr       string
}

AlterColumnTypeWithCheckData contains data for the guarded alter column type template, which only alters existing columns whose live type differs from the desired one, and falls back to renaming the old column and adding a fresh one when the in-place conversion is not possible.

type AuditColumnData 

type AuditColumnData struct {
	Name     string
	OldValue string
	NewValue string
}

AuditColumnData represents a column in audit template

type AuditConfig 

type AuditConfig struct {
	// EnabledTables maps table names (schema.table or just table) to audit settings
	EnabledTables map[string]*TableAuditConfig
	// AuditSchema is where audit tables are created (default: same as table schema)
	AuditSchema string
	// UserFunction is the function to get current user (default: current_user)
	UserFunction string
}

AuditConfig defines audit configuration for tables

func NewAuditConfig 

func NewAuditConfig() *AuditConfig

NewAuditConfig creates a default audit configuration

func (*AuditConfig) EnableTableAudit 

func (ac *AuditConfig) EnableTableAudit(schemaName, tableName string) *TableAuditConfig

EnableTableAudit enables audit for a specific table

func (*AuditConfig) GetTableConfig 

func (ac *AuditConfig) GetTableConfig(schemaName, tableName string) *TableAuditConfig

GetTableConfig returns the audit config for a specific table

func (*AuditConfig) IsTableAudited 

func (ac *AuditConfig) IsTableAudited(schemaName, tableName string) bool

IsTableAudited checks if a table is configured for auditing

type AuditFunctionData 

type AuditFunctionData struct {
	SchemaName      string
	FunctionName    string
	TableName       string
	TablePrefix     string
	PrimaryKey      string
	AuditSchema     string
	UserFunction    string
	AuditInsert     bool
	AuditUpdate     bool
	AuditDelete     bool
	UpdateCondition string
	UpdateColumns   []AuditColumnData
	DeleteColumns   []AuditColumnData
}

AuditFunctionData contains data for audit function template

func BuildAuditFunctionData 

func BuildAuditFunctionData(
	schemaName string,
	table *models.Table,
	pk *models.Column,
	config *TableAuditConfig,
	auditSchema string,
	userFunction string,
) AuditFunctionData

BuildAuditFunctionData builds AuditFunctionData from table and config

type AuditTablesData 

type AuditTablesData struct {
	AuditSchema string
}

AuditTablesData contains data for audit tables template

type AuditTriggerData 

type AuditTriggerData struct {
	SchemaName   string
	TableName    string
	TriggerName  string
	FunctionName string
	Events       string
}

AuditTriggerData contains data for audit trigger template

type ColumnData 

type ColumnData struct {
	Name    string
	Type    string
	Default string
	NotNull bool
}

ColumnData represents column information

type CommentColumnData 

type CommentColumnData struct {
	SchemaName string
	TableName  string
	ColumnName string
	Comment    string
}

CommentColumnData contains data for column comment template

type CommentTableData 

type CommentTableData struct {
	SchemaName string
	TableName  string
	Comment    string
}

CommentTableData contains data for table comment template

type CreateCheckConstraintData added in v1.0.26 

type CreateCheckConstraintData struct {
	SchemaName     string
	TableName      string
	ConstraintName string
	Expression     string
}

CreateCheckConstraintData contains data for create check constraint template

type CreateForeignKeyData 

type CreateForeignKeyData struct {
	SchemaName     string
	TableName      string
	ConstraintName string
	SourceColumns  string
	TargetSchema   string
	TargetTable    string
	TargetColumns  string
	OnDelete       string
	OnUpdate       string
}

CreateForeignKeyData contains data for create foreign key template

type CreateForeignKeyWithCheckData added in v1.0.26 

type CreateForeignKeyWithCheckData struct {
	SchemaName     string
	TableName      string
	ConstraintName string
	SourceColumns  string
	TargetSchema   string
	TargetTable    string
	TargetColumns  string
	OnDelete       string
	OnUpdate       string
	Deferrable     bool
}

CreateForeignKeyWithCheckData contains data for create foreign key with existence check template

type CreateIndexData 

type CreateIndexData struct {
	SchemaName string
	TableName  string
	IndexName  string
	IndexType  string
	Columns    string
	Unique     bool
}

CreateIndexData contains data for create index template

type CreatePrimaryKeyData 

type CreatePrimaryKeyData struct {
	SchemaName     string
	TableName      string
	ConstraintName string
	Columns        string
}

CreatePrimaryKeyData contains data for create primary key template

type CreatePrimaryKeyWithAutoGenCheckData added in v1.0.26 

type CreatePrimaryKeyWithAutoGenCheckData struct {
	SchemaName     string
	TableName      string
	ConstraintName string
	AutoGenNames   string // Comma-separated list of names like "'name1', 'name2'"
	Columns        string
	ColumnNames    string // Comma-separated list of quoted column names like "'id', 'tenant_id'"
}

CreatePrimaryKeyWithAutoGenCheckData contains data for primary key with auto-generated key check template

type CreateSequenceData added in v1.0.26 

type CreateSequenceData struct {
	SchemaName   string
	SequenceName string
	Increment    int
	MinValue     int64
	MaxValue     int64
	StartValue   int64
	CacheSize    int
}

CreateSequenceData contains data for create sequence template

type CreateTableData 

type CreateTableData struct {
	SchemaName string
	TableName  string
	Columns    []ColumnData
}

CreateTableData contains data for create table template

func BuildCreateTableData 

func BuildCreateTableData(schemaName string, table *models.Table) CreateTableData

BuildCreateTableData builds CreateTableData from a models.Table

type CreateUniqueConstraintData added in v1.0.26 

type CreateUniqueConstraintData struct {
	SchemaName     string
	TableName      string
	ConstraintName string
	Columns        string
}

CreateUniqueConstraintData contains data for create unique constraint template

type DropConstraintData 

type DropConstraintData struct {
	SchemaName     string
	TableName      string
	ConstraintName string
}

DropConstraintData contains data for drop constraint template

type DropIndexData 

type DropIndexData struct {
	SchemaName string
	IndexName  string
}

DropIndexData contains data for drop index template

type ExecutionError added in v1.0.15 

type ExecutionError struct {
	StatementNumber int    `json:"statement_number"`
	Statement       string `json:"statement"`
	Error           string `json:"error"`
}

ExecutionError represents a failed statement

type ExecutionReport added in v1.0.15 

type ExecutionReport struct {
	TotalStatements    int              `json:"total_statements"`
	ExecutedStatements int              `json:"executed_statements"`
	FailedStatements   int              `json:"failed_statements"`
	Schemas            []SchemaReport   `json:"schemas"`
	Errors             []ExecutionError `json:"errors,omitempty"`
	StartTime          string           `json:"start_time"`
	EndTime            string           `json:"end_time"`
}

ExecutionReport tracks the execution status of SQL statements

type MigrationScript 

type MigrationScript struct {
	ObjectName string
	ObjectType string
	Schema     string
	Priority   int
	Sequence   int
	Body       string
}

MigrationScript represents a single migration script with priority

type MigrationWriter 

type MigrationWriter struct {
	// contains filtered or unexported fields
}

MigrationWriter generates differential migration SQL scripts using templates

func NewMigrationWriter 

func NewMigrationWriter(options *writers.WriterOptions) (*MigrationWriter, error)

NewMigrationWriter creates a new templated migration writer

func (*MigrationWriter) WriteMigration 

func (w *MigrationWriter) WriteMigration(model *models.Database, current *models.Database) error

WriteMigration generates migration scripts using templates

type SchemaReport added in v1.0.15 

type SchemaReport struct {
	Name   string        `json:"name"`
	Tables []TableReport `json:"tables"`
}

SchemaReport tracks execution per schema

type SetSequenceValueData added in v1.0.26 

type SetSequenceValueData struct {
	SchemaName   string
	TableName    string
	SequenceName string
	ColumnName   string
}

SetSequenceValueData contains data for set sequence value template

type TableAuditConfig 

type TableAuditConfig struct {
	// TableName is the name of the table to audit
	TableName string
	// SchemaName is the schema of the table
	SchemaName string
	// TablePrefix for compatibility with old audit system
	TablePrefix string
	// AuditInsert tracks INSERT operations
	AuditInsert bool
	// AuditUpdate tracks UPDATE operations
	AuditUpdate bool
	// AuditDelete tracks DELETE operations
	AuditDelete bool
	// ExcludedColumns are columns to skip from audit
	ExcludedColumns []string
	// EncryptedColumns are columns to hide in audit (show as ***)
	EncryptedColumns []string
}

TableAuditConfig defines audit settings for a specific table

type TableReport added in v1.0.15 

type TableReport struct {
	Name    string `json:"name"`
	Created bool   `json:"created"`
	Error   string `json:"error,omitempty"`
}

TableReport tracks execution per table

type TemplateExecutor 

type TemplateExecutor struct {
	// contains filtered or unexported fields
}

TemplateExecutor manages and executes SQL templates

func NewTemplateExecutor 

func NewTemplateExecutor(flattenSchema bool) (*TemplateExecutor, error)

NewTemplateExecutor creates a new template executor. flattenSchema controls whether schema.table identifiers use dot or underscore separation.

func (*TemplateExecutor) ExecuteAddColumn 

func (te *TemplateExecutor) ExecuteAddColumn(data AddColumnData) (string, error)

ExecuteAddColumn executes the add column template

func (*TemplateExecutor) ExecuteAddColumnWithCheck added in v1.0.26 

func (te *TemplateExecutor) ExecuteAddColumnWithCheck(data AddColumnWithCheckData) (string, error)

ExecuteAddColumnWithCheck executes the add column with check template

func (*TemplateExecutor) ExecuteAlterColumnDefaultWithCheck added in v1.0.68 

func (te *TemplateExecutor) ExecuteAlterColumnDefaultWithCheck(data AlterColumnDefaultWithCheckData) (string, error)

ExecuteAlterColumnDefaultWithCheck executes the guarded alter column default template shared by the full-schema writer and the diff-based migration writer.

func (*TemplateExecutor) ExecuteAlterColumnNullabilityWithCheck added in v1.0.68 

func (te *TemplateExecutor) ExecuteAlterColumnNullabilityWithCheck(data AlterColumnNullabilityWithCheckData) (string, error)

ExecuteAlterColumnNullabilityWithCheck executes the guarded alter column nullability template.

func (*TemplateExecutor) ExecuteAlterColumnTypeWithCheck added in v1.0.56 

func (te *TemplateExecutor) ExecuteAlterColumnTypeWithCheck(data AlterColumnTypeWithCheckData) (string, error)

ExecuteAlterColumnTypeWithCheck executes the guarded alter column type template shared by the full-schema writer and the diff-based migration writer.

func (*TemplateExecutor) ExecuteAuditFunction 

func (te *TemplateExecutor) ExecuteAuditFunction(data AuditFunctionData) (string, error)

ExecuteAuditFunction executes the audit function template

func (*TemplateExecutor) ExecuteAuditTables 

func (te *TemplateExecutor) ExecuteAuditTables(data AuditTablesData) (string, error)

ExecuteAuditTables executes the audit tables template

func (*TemplateExecutor) ExecuteAuditTrigger 

func (te *TemplateExecutor) ExecuteAuditTrigger(data AuditTriggerData) (string, error)

ExecuteAuditTrigger executes the audit trigger template

func (*TemplateExecutor) ExecuteCommentColumn 

func (te *TemplateExecutor) ExecuteCommentColumn(data CommentColumnData) (string, error)

ExecuteCommentColumn executes the column comment template

func (*TemplateExecutor) ExecuteCommentTable 

func (te *TemplateExecutor) ExecuteCommentTable(data CommentTableData) (string, error)

ExecuteCommentTable executes the table comment template

func (*TemplateExecutor) ExecuteCreateCheckConstraint added in v1.0.26 

func (te *TemplateExecutor) ExecuteCreateCheckConstraint(data CreateCheckConstraintData) (string, error)

ExecuteCreateCheckConstraint executes the create check constraint template

func (*TemplateExecutor) ExecuteCreateForeignKey 

func (te *TemplateExecutor) ExecuteCreateForeignKey(data CreateForeignKeyData) (string, error)

ExecuteCreateForeignKey executes the create foreign key template

func (*TemplateExecutor) ExecuteCreateForeignKeyWithCheck added in v1.0.26 

func (te *TemplateExecutor) ExecuteCreateForeignKeyWithCheck(data CreateForeignKeyWithCheckData) (string, error)

ExecuteCreateForeignKeyWithCheck executes the create foreign key with check template

func (*TemplateExecutor) ExecuteCreateIndex 

func (te *TemplateExecutor) ExecuteCreateIndex(data CreateIndexData) (string, error)

ExecuteCreateIndex executes the create index template

func (*TemplateExecutor) ExecuteCreatePrimaryKey 

func (te *TemplateExecutor) ExecuteCreatePrimaryKey(data CreatePrimaryKeyData) (string, error)

ExecuteCreatePrimaryKey executes the create primary key template

func (*TemplateExecutor) ExecuteCreatePrimaryKeyWithAutoGenCheck added in v1.0.26 

func (te *TemplateExecutor) ExecuteCreatePrimaryKeyWithAutoGenCheck(data CreatePrimaryKeyWithAutoGenCheckData) (string, error)

ExecuteCreatePrimaryKeyWithAutoGenCheck executes the create primary key with auto-generated key check template

func (*TemplateExecutor) ExecuteCreateSequence added in v1.0.26 

func (te *TemplateExecutor) ExecuteCreateSequence(data CreateSequenceData) (string, error)

ExecuteCreateSequence executes the create sequence template

func (*TemplateExecutor) ExecuteCreateTable 

func (te *TemplateExecutor) ExecuteCreateTable(data CreateTableData) (string, error)

ExecuteCreateTable executes the create table template

func (*TemplateExecutor) ExecuteCreateUniqueConstraint added in v1.0.26 

func (te *TemplateExecutor) ExecuteCreateUniqueConstraint(data CreateUniqueConstraintData) (string, error)

ExecuteCreateUniqueConstraint executes the create unique constraint template

func (*TemplateExecutor) ExecuteDropConstraint 

func (te *TemplateExecutor) ExecuteDropConstraint(data DropConstraintData) (string, error)

ExecuteDropConstraint executes the drop constraint template

func (*TemplateExecutor) ExecuteDropIndex 

func (te *TemplateExecutor) ExecuteDropIndex(data DropIndexData) (string, error)

ExecuteDropIndex executes the drop index template

func (*TemplateExecutor) ExecuteSetSequenceValue added in v1.0.26 

func (te *TemplateExecutor) ExecuteSetSequenceValue(data SetSequenceValueData) (string, error)

ExecuteSetSequenceValue executes the set sequence value template

type Writer 

type Writer struct {
	// contains filtered or unexported fields
}

Writer implements the Writer interface for PostgreSQL SQL output

func NewWriter 

func NewWriter(options *writers.WriterOptions) *Writer

NewWriter creates a new PostgreSQL SQL writer

func (*Writer) GenerateAddColumnStatements added in v1.0.19 

func (w *Writer) GenerateAddColumnStatements(schema *models.Schema) ([]string, error)

GenerateAddColumnStatements generates ALTER TABLE ADD COLUMN statements for existing tables This is useful for schema evolution when new columns are added to existing tables

func (*Writer) GenerateAddColumnsForDatabase added in v1.0.19 

func (w *Writer) GenerateAddColumnsForDatabase(db *models.Database) ([]string, error)

GenerateAddColumnsForDatabase generates ALTER TABLE ADD COLUMN statements for the entire database

func (*Writer) GenerateAlterColumnDefaultStatements added in v1.0.68 

func (w *Writer) GenerateAlterColumnDefaultStatements(schema *models.Schema) ([]string, error)

GenerateAlterColumnDefaultStatements generates guarded ALTER TABLE statements to bring existing columns' DEFAULT clause in line with the model, safe to run against a database that already has the columns.

func (*Writer) GenerateAlterColumnNullabilityStatements added in v1.0.68 

func (w *Writer) GenerateAlterColumnNullabilityStatements(schema *models.Schema) ([]string, error)

GenerateAlterColumnNullabilityStatements generates guarded ALTER TABLE statements to bring existing columns' NOT NULL state in line with the model, safe to run against a database that already has the columns.

func (*Writer) GenerateAlterColumnTypeStatements added in v1.0.56 

func (w *Writer) GenerateAlterColumnTypeStatements(schema *models.Schema) ([]string, error)

func (*Writer) GenerateDatabaseStatements 

func (w *Writer) GenerateDatabaseStatements(db *models.Database) ([]string, error)

GenerateDatabaseStatements generates SQL statements as a list for the entire database Returns a slice of SQL statements that can be executed independently

func (*Writer) GenerateSchemaStatements 

func (w *Writer) GenerateSchemaStatements(schema *models.Schema) ([]string, error)

GenerateSchemaStatements generates SQL statements as a list for a single schema

func (*Writer) WriteAddColumnStatements added in v1.0.19 

func (w *Writer) WriteAddColumnStatements(db *models.Database) error

WriteAddColumnStatements writes ALTER TABLE ADD COLUMN statements for a database This is used for schema evolution/migration when new columns are added

func (*Writer) WriteDatabase 

func (w *Writer) WriteDatabase(db *models.Database) error

WriteDatabase writes the entire database schema as SQL

func (*Writer) WriteSchema 

func (w *Writer) WriteSchema(schema *models.Schema) error

WriteSchema writes a single schema and all its tables

func (*Writer) WriteTable 

func (w *Writer) WriteTable(table *models.Table) error

WriteTable writes a single table with all its elements

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.