pgxgen

pgxgen

Code generation tool for PostgreSQL, MySQL, and SQLite. Generates CRUD SQL, Go models, and sqlc query code from a single config file.

Features

• One config — pgxgen.yaml replaces both pgxgen.yaml and sqlc.yaml
• CRUD generation — template-based SQL with soft delete, batch insert support
• Go models — structs with json/db tags, custom tags, type overrides, enum generation
• sqlc integration — auto-generates sqlc.yaml and runs sqlc
• Multi-engine — PostgreSQL + MySQL + SQLite in one project
• Per-table repos or single repo layout
• Watch mode, dry-run, validation, interactive init

AI Agent Skills

This repository includes AI agent skills with documentation and usage examples for all packages. Install them with the skills CLI:

go install github.com/sxwebdev/skills/cmd/skills@latest
skills init
skills repo add tkcrm/pgxgen

Install

go install github.com/tkcrm/pgxgen/cmd/pgxgen@latest

Or from source:

git clone https://github.com/tkcrm/pgxgen.git
cd pgxgen && go build -o pgxgen ./cmd/pgxgen

Quick start

# Create config interactively
pgxgen init

# Or generate an example config
pgxgen example > pgxgen.yaml

# Generate everything
pgxgen generate

# Preview without writing
pgxgen generate --dry-run

Configuration

version: "2"

schemas:
  - name: main
    engine: postgresql
    schema_dir: sql/migrations

    models:
      output_dir: internal/models
      output_file_name: models_gen.go
      package_name: models
      package_path: github.com/your-org/project/internal/models
      emit_json_tags: true
      emit_db_tags: true
      # include_struct_comments: true  # Add // @name StructName for Swagger

    sqlc:
      defaults:
        sql_package: pgx/v5
        emit_interface: true
        emit_json_tags: true
        emit_db_tags: true
        emit_empty_slices: true
        emit_result_struct_pointers: true
        emit_enum_valid_method: true
        emit_all_enum_values: true
      overrides:
        rename: { d: Params }
        types:
          - db_type: uuid
            go_type: "github.com/google/uuid.UUID"
          - db_type: uuid
            nullable: true
            go_type: "github.com/google/uuid.NullUUID"
        columns:
          - column: users.email
            go_struct_tag: 'validate:"required,email"'

    defaults:
      queries_dir_prefix: sql/queries
      output_dir_prefix: internal/store/repos
      crud:
        auto_clean: true
        exclude_table_name: true
        methods:
          create:
            skip_columns: [id, updated_at]
            returning: "*"
            column_values: { created_at: "now()" }
      constants:
        include_column_names: true

    tables:
      users:
        primary_column: id
        # soft_delete:
        #   column: deleted_at
        crud:
          methods:
            create:
              skip_columns: [id, updated_at]
              column_values: { created_at: "now()" }
              returning: "*"
            update:
              skip_columns: [id, created_at]
              column_values: { updated_at: "now()" }
              returning: "*"
            get: { name: GetByID }
            delete: {}
            find:
              order: { by: created_at, direction: DESC }
              limit: true
            total: {}
            exists:
              where: { email: {} }

Path patterns

Per-table repos (each table gets its own directory):

defaults:
  queries_dir_prefix: sql/queries # → sql/queries/{table}
  output_dir_prefix: internal/store/repos # → internal/store/repos/repo_{table}

Single repo (all tables in one directory):

defaults:
  queries_dir: sql/queries
  output_dir: internal/store

CRUD methods

Soft delete

tables:
  posts:
    soft_delete:
      column: deleted_at
    crud:
      methods:
        delete: {} # → UPDATE SET deleted_at = now() WHERE id = $1
        get: {} # → auto adds WHERE deleted_at IS NULL
        find: {} # → auto adds WHERE deleted_at IS NULL

Commands

Migration from v1

pgxgen migrate --in-place --sqlc-config sqlc.yaml
rm sqlc.yaml
pgxgen generate

JSON Schema

IDE autocompletion is available via schemas/pgxgen-schema.json:

# yaml-language-server: $schema=https://raw.githubusercontent.com/tkcrm/pgxgen/master/schemas/pgxgen-schema.json
version: "2"

License

MIT