pgm/CLAUDE.md

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

pgm is a lightweight, type-safe PostgreSQL query builder for Go, built on top of jackc/pgx. It generates Go code from SQL schema files, enabling compile-time safety for database queries without the overhead of traditional ORMs.

Core Philosophy:

• Schema defined in SQL (not Go)
• Minimal code generation (only table/column definitions)
• Users provide their own models (no forced abstractions)
• Type-safe query building with fluent API
• Zero reflection for maximum performance

Development Commands

Building

# Build CLI with version from git tags
make build

# Build with specific version
make build VERSION=v1.2.3

# Install to GOPATH/bin
make install

# Check current version
make version
pgm -version

Testing

# Run all tests in playground
make test

# Run benchmarks for SELECT queries
make bench-select

# Run code generator on playground schema
make run

Code Generation

# Generate Go code from SQL schema
pgm -o ./db ./schema.sql

# The generator expects a SQL schema file and outputs:
# - One package per table in the output directory
# - Each package contains table and column definitions
# Example output: db/users/users.go, db/posts/posts.go

Architecture

Core Components

1. Query Builder System (qry_*.go files)

The query builder uses a mutable, stateful design for conditional query building:

• qry_select.go: SELECT queries with joins, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET
• qry_insert.go: INSERT queries with RETURNING and UPSERT (ON CONFLICT) support
• qry_update.go: UPDATE queries with conditional WHERE clauses
• qry_delete.go: DELETE queries with WHERE conditions

Important: Query builders accumulate state with each method call. They are designed for conditional building within a single query but should NOT be reused across multiple separate queries.

// ✅ CORRECT - Conditional building
query := users.User.Select(users.ID, users.Email)
if nameFilter != "" {
    query = query.Where(users.Name.Like("%" + nameFilter + "%"))
}
err := query.First(ctx, &id, &email)

// ❌ WRONG - Reusing builder across separate queries
baseQuery := users.User.Select(users.ID)
baseQuery.Where(users.ID.Eq(1)).First(ctx, &id1)  // Adds ID=1
baseQuery.Where(users.Status.Eq(2)).First(ctx, &id2)  // Has BOTH conditions!

Query builders are NOT thread-safe. Each goroutine must create its own query instance.

2. Field System (pgm_field.go)

Fields are type-safe column references with:

• Comparison operators: Eq(), NotEq(), Gt(), Lt(), Gte(), Lte()
• Pattern matching: Like(), ILike(), LikeFold(), EqFold()
• NULL checks: IsNull(), IsNotNull()
• Array operations: Any(), NotAny()
• Aggregate functions: Count(), Sum(), Avg(), Min(), Max()
• String functions: Lower(), Upper(), Trim(), StringEscape()
• Special functions: ConcatWs(), StringAgg(), DateTrunc(), RowNumber()

Security: Functions like ConcatWs(), StringAgg(), and DateTrunc() validate inputs and escape SQL strings. They use allowlists for parameters like date truncation levels.

3. Connection Pool (pgm.go)

Global connection pool using pgxpool with atomic pointer for thread safety:

• InitPool(Config): Initialize once at startup (panics if called multiple times or with invalid config)
• GetPool(): Retrieve pool instance (panics if not initialized)
• ClosePool(): Graceful shutdown
• BeginTx(ctx): Start transactions

The pool is stored in an atomic.Pointer[pgxpool.Pool] for lock-free concurrent access.

4. Code Generator (cmd/)

Parses SQL schema files and generates Go code:

• cmd/main.go: CLI entry point with version flag support
• cmd/parse.go: Regex-based SQL parser (has known limitations)
• cmd/generate.go: Code generation with go/format for proper formatting
• cmd/version.go: Version string generation from build-time ldflags

Parser Limitations:

• No multi-line comments (/* */)
• Limited support for complex data types (arrays, JSON, JSONB)
• No advanced PostgreSQL features (PARTITION BY, INHERITS)
• Some constraints (CHECK, EXCLUDE) not parsed

Generated files include a header comment with version and timestamp:

// Code generated by code.patial.tech/go/pgm/cmd v1.2.3 on 2025-11-16 04:05:43 DO NOT EDIT.

5. Table System (pgm_table.go)

Minimal table metadata:

type Table struct {
    Name         string    // Table name
    FieldCount   int       // Number of columns
    PK           []string  // Primary key columns
    DerivedTable Query     // For subqueries/CTEs
}

Provides factory methods: Select(), Insert(), Update(), Delete()

String Builder Pool

Uses sync.Pool for efficient string building (qry.go):

var sbPool = sync.Pool{
    New: func() any { return new(strings.Builder) }
}

All query builders use getSB() / putSB() to reduce allocations.

Error Handling

Custom errors in pgm.go:

• ErrConnStringMissing: Connection string validation
• ErrInitTX: Transaction initialization failure
• ErrCommitTX: Transaction commit failure
• ErrNoRows: Wrapper for pgx.ErrNoRows

Use pgm.IsNotFound(err) to check for no rows errors.

Generated Code Structure

For a schema with users and posts tables:

db/
├── schema.go              # Table definitions and DerivedTable helper
├── user/
│   └── users.go           # User table columns as constants
├── post/
│   └── posts.go           # Post table columns as constants
└── ...

Each table file exports constants like:

const (
    All   pgm.Field = "users.*"
    ID    pgm.Field = "users.id"
    Email pgm.Field = "users.email"
    // ... all columns
)

Naming Conventions

• Table pluralization: pluralToSingular() in cmd/generate.go handles irregular plurals (people→person, children→child) and common patterns (-ies→-y, -ves→-fe, -es→-e, -s→"")
• Field naming: Snake_case columns converted to PascalCase (first_name → FirstName)
• ID suffix: Fields ending in _id become ID not Id (user_id → UserID)

Key Implementation Details

Query Building Strategy

1. Pre-allocation: Queries estimate final string length via averageLen() methods to reduce allocations
2. Conditional accumulation: All clauses stored in slices, built on demand
3. Parameterized queries: Uses PostgreSQL numbered parameters ($1, $2, etc.)
4. Builder methods return interfaces: Enforces correct method call sequences at compile time

Example type progression:

SelectClause → WhereClause → AfterWhere → OrderByClause → Query → First/All

Transaction Handling

Pattern used throughout:

tx, err := pgm.BeginTx(ctx)
if err != nil { return err }
defer tx.Rollback(ctx)  // Safe to call after commit

// ... operations using ExecTx, FirstTx, AllTx methods ...

return tx.Commit(ctx)

All query execution methods have Tx variants that accept pgx.Tx.

Full-Text Search Helpers

Helper functions for PostgreSQL tsvector queries:

• TsAndQuery(): AND operator between terms
• TsPrefixAndQuery(): AND with prefix matching :*
• TsOrQuery(): OR operator
• TsPrefixOrQuery(): OR with prefix matching

Used with tsvector columns via Field.TsQuery().

Testing

Tests are in the playground/ directory:

• playground/schema.sql: Test database schema
• playground/db/: Generated code from schema
• playground/*_test.go: Integration tests for SELECT, INSERT, UPDATE, DELETE
• playground/local_select_test.go: Additional SELECT test cases

Tests require a running PostgreSQL instance. The playground uses the generated code to verify the query builder works correctly.

Version Management

Version is injected at build time via ldflags:

go build -ldflags "-X main.version=v1.2.3" ./cmd

The Makefile automatically extracts version from git tags:

VERSION ?= $(shell git describe --tags --always --dirty 2>/dev/null || echo "dev")

This version appears in:

• CLI pgm -version output
• Generated file headers
• Version string formatting in cmd/version.go

Common Patterns

Connection Pool Initialization

Always initialize once at startup and defer cleanup:

func main() {
    pgm.InitPool(pgm.Config{
        ConnString: os.Getenv("DATABASE_URL"),
        MaxConns:   25,
        MinConns:   5,
    })
    defer pgm.ClosePool()
    // ...
}

Safe Query Execution

Check for no rows using the helper:

err := users.User.Select(users.Email).Where(users.ID.Eq(id)).First(ctx, &email)
if pgm.IsNotFound(err) {
    // Handle not found case
}
if err != nil {
    // Handle other errors
}

Validation Requirements

• UPDATE requires at least one Set() call
• INSERT requires at least one Set() call
• DELETE without Where() deletes ALL rows (dangerous!)

All execution methods validate these requirements and return descriptive errors.

Performance Considerations

• sync.Pool: Reuses string builders across queries
• Pre-allocation: Queries pre-calculate buffer sizes
• Zero reflection: Direct field access, no runtime type inspection
• pgxpool: Leverages jackc/pgx's efficient connection pooling
• Direct scanning: Users scan into their own types, no intermediate mapping

Security Notes

• All queries use parameterized statements (PostgreSQL numbered parameters)
• Field validation in functions like DateTrunc() uses allowlists
• SQL string escaping in escapeSQLString() for literal values
• Identifier validation via regex in validateSQLIdentifier()
• Connection pool configuration validates against negative values and invalid ranges