pgm

pgm - PostgreSQL Query Mapper

A lightweight, type-safe PostgreSQL query builder for Go, built on top of jackc/pgx. pgm generates Go code from your SQL schema, enabling you to write SQL queries with compile-time safety and autocompletion support.

Features

• Type-safe queries - Column and table names are validated at compile time
• Zero reflection - Fast performance with no runtime reflection overhead
• SQL schema-based - Generate Go code directly from your SQL schema files
• Fluent API - Intuitive query builder with method chaining
• Transaction support - First-class support for pgx transactions
• Full-text search - Built-in PostgreSQL full-text search helpers
• Connection pooling - Leverages pgx connection pool for optimal performance
• Minimal code generation - Only generates what you need, no bloat

Table of Contents

• Why pgm?
• Installation
• Quick Start
• Usage Examples
  • SELECT Queries
  • INSERT Queries
  • UPDATE Queries
  • DELETE Queries
  • Joins
  • Transactions
  • Full-Text Search
• CLI Tool
• API Documentation
• Contributing
• License

Why pgm?

The Problem with Existing ORMs

While Go has excellent ORMs like ent and sqlc, they come with tradeoffs:

ent - Feature-rich but heavy:

• Generates extensive code for features you may never use
• Significantly increases binary size
• Complex schema definition in Go instead of SQL
• Auto-migrations can obscure actual database schema

sqlc - Great tool, but:

• Creates separate database models, forcing model mapping
• Query results require their own generated types
• Less flexibility in dynamic query building

The pgm Approach

pgm takes a hybrid approach:

✅ Schema as SQL - Define your database schema in pure SQL, where it belongs
✅ Minimal generation - Only generates table and column definitions
✅ Your models - Use your own application models, no forced abstractions
✅ Type safety - Catch schema changes at compile time
✅ SQL power - Full control over your queries with a fluent API
✅ Migration-friendly - Use mature tools like dbmate for migrations

Installation

go get code.patial.tech/go/pgm

Install the CLI tool for schema code generation:

go install code.patial.tech/go/pgm/cmd@latest

Building from Source

Build with automatic version detection (uses git tags):

# Build with version from git tags
make build

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

# Install to GOPATH/bin
make install

# Or build manually with version
go build -ldflags "-X main.version=v1.2.3" -o pgm ./cmd

Check the version:

pgm -version

Quick Start

1. Create Your Schema

Create a SQL schema file schema.sql or use the one created by dbmate:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    title VARCHAR(500) NOT NULL,
    content TEXT,
    published BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2. Generate Go Code

Run the pgm CLI tool:

pgm -o ./db ./schema.sql

This generates Go files for each table in ./db/:

• db/users/users.go - Table and column definitions for users
• db/posts/posts.go - Table and column definitions for posts

3. Use in Your Code

package main

import (
    "context"
    "log"
    
    "code.patial.tech/go/pgm"
    "yourapp/db/users"
    "yourapp/db/posts"
)

func main() {
    // Initialize connection pool
    pgm.InitPool(pgm.Config{
        ConnString: "postgres://user:pass@localhost:5432/dbname",
        MaxConns:   25,
        MinConns:   5,
    })
    defer pgm.ClosePool() // Ensure graceful shutdown
    
    ctx := context.Background()
    
    // Query a user
    var email string
    err := users.User.Select(users.Email).
        Where(users.ID.Eq("some-uuid")).
        First(ctx, &email)
    if err != nil {
        log.Fatal(err)
    }
    
    log.Printf("User email: %s", email)
}

Important: Query Builder Lifecycle

✅ Conditional Building (CORRECT)

Query builders are mutable by design to support conditional query building:

// ✅ CORRECT - Conditional building pattern
query := users.User.Select(users.ID, users.Email, users.Name)

// Add conditions based on filters
if nameFilter != "" {
    query = query.Where(users.Name.Like("%" + nameFilter + "%"))
}

if statusFilter > 0 {
    query = query.Where(users.Status.Eq(statusFilter))
}

if sortByName {
    query = query.OrderBy(users.Name.Asc())
}

// Execute the final query with all accumulated conditions
err := query.First(ctx, &id, &email, &name)

This is the intended use! The builder accumulates your conditions, which is powerful and flexible.

❌ Unintentional Reuse (INCORRECT)

Don't try to create a "base query" and reuse it for multiple different queries:

// ❌ WRONG - Trying to reuse for multiple separate queries
baseQuery := users.User.Select(users.ID, users.Email)

// First query - adds ID condition
baseQuery.Where(users.ID.Eq(1)).First(ctx, &id1, &email1)

// Second query - ALSO has ID=1 from above PLUS Status=2!
baseQuery.Where(users.Status.Eq(2)).First(ctx, &id2, &email2)
// This executes: WHERE users.id = 1 AND users.status = 2 (WRONG!)

// ✅ CORRECT - Each separate query gets its own builder
users.User.Select(users.ID, users.Email).Where(users.ID.Eq(1)).First(ctx, &id1, &email1)
users.User.Select(users.ID, users.Email).Where(users.Status.Eq(2)).First(ctx, &id2, &email2)

Why? Query builders are mutable and accumulate state. Each method call modifies the builder, so reusing the same builder causes conditions to stack up.

Thread Safety

⚠️ Query builders are NOT thread-safe and must not be shared across goroutines:

// ✅ CORRECT - Each goroutine creates its own query
for i := 0; i < 10; i++ {
    go func(id int) {
        var email string
        err := users.User.Select(users.Email).
            Where(users.ID.Eq(id)).
            First(ctx, &email)
        // Process result...
    }(i)
}

// ❌ WRONG - Sharing query builder across goroutines
baseQuery := users.User.Select(users.Email)
for i := 0; i < 10; i++ {
    go func(id int) {
        var email string
        baseQuery.Where(users.ID.Eq(id)).First(ctx, &email)
        // RACE CONDITION! Multiple goroutines modifying shared state
    }(i)
}

Thread-Safe Components:

• ✅ Connection Pool - Safe for concurrent use
• ✅ Table objects - Safe to share
• ❌ Query builders - Create new instance per goroutine

Usage Examples

SELECT Queries

Basic Select

var user struct {
    ID    string
    Email string
    Name  string
}

err := users.User.Select(users.ID, users.Email, users.Name).
    Where(users.Email.Eq("john@example.com")).
    First(ctx, &user.ID, &user.Email, &user.Name)

Select with Multiple Conditions

err := users.User.Select(users.ID, users.Email).
    Where(
        users.Email.Like("john%"),
        users.CreatedAt.Gt(time.Now().AddDate(0, -1, 0)),
    ).
    OrderBy(users.CreatedAt.Desc()).
    Limit(10).
    First(ctx, &user.ID, &user.Email)

Select All with Callback

var userList []User

err := users.User.Select(users.ID, users.Email, users.Name).
    Where(users.Name.Like("J%")).
    OrderBy(users.Name.Asc()).
    All(ctx, func(row pgm.RowScanner) error {
        var u User
        if err := row.Scan(&u.ID, &u.Email, &u.Name); err != nil {
            return err
        }
        userList = append(userList, u)
        return nil
    })

Pagination

page := 2
pageSize := 20

err := users.User.Select(users.ID, users.Email).
    OrderBy(users.CreatedAt.Desc()).
    Limit(pageSize).
    Offset((page - 1) * pageSize).
    All(ctx, func(row pgm.RowScanner) error {
        // Process rows
    })

Grouping and Having

err := posts.Post.Select(posts.UserID, pgm.Count(posts.ID)).
    GroupBy(posts.UserID).
    Having(pgm.Count(posts.ID).Gt(5)).
    All(ctx, func(row pgm.RowScanner) error {
        var userID string
        var postCount int
        return row.Scan(&userID, &postCount)
    })

INSERT Queries

Simple Insert

err := users.User.Insert().
    Set(users.Email, "jane@example.com").
    Set(users.Name, "Jane Doe").
    Set(users.CreatedAt, pgm.PgTimeNow()).
    Exec(ctx)

Insert with Map

data := map[pgm.Field]any{
    users.Email:     "jane@example.com",
    users.Name:      "Jane Doe",
    users.CreatedAt: pgm.PgTimeNow(),
}

err := users.User.Insert().
    SetMap(data).
    Exec(ctx)

Insert with RETURNING

var newID string

err := users.User.Insert().
    Set(users.Email, "jane@example.com").
    Set(users.Name, "Jane Doe").
    Returning(users.ID).
    First(ctx, &newID)

Upsert (INSERT ... ON CONFLICT)

// Do nothing on conflict
err := users.User.Insert().
    Set(users.Email, "jane@example.com").
    Set(users.Name, "Jane Doe").
    OnConflict(users.Email).
    DoNothing().
    Exec(ctx)

// Update on conflict
err := users.User.Insert().
    Set(users.Email, "jane@example.com").
    Set(users.Name, "Jane Doe Updated").
    OnConflict(users.Email).
    DoUpdate(users.Name).
    Exec(ctx)

UPDATE Queries

Simple Update

err := users.User.Update().
    Set(users.Name, "John Smith").
    Where(users.ID.Eq("some-uuid")).
    Exec(ctx)

Update Multiple Fields

updates := map[pgm.Field]any{
    users.Name:  "John Smith",
    users.Email: "john.smith@example.com",
}

err := users.User.Update().
    SetMap(updates).
    Where(users.ID.Eq("some-uuid")).
    Exec(ctx)

Conditional Update

err := users.User.Update().
    Set(users.Name, "Updated Name").
    Where(
        users.Email.Like("%@example.com"),
        users.CreatedAt.Lt(time.Now().AddDate(-1, 0, 0)),
    ).
    Exec(ctx)

DELETE Queries

Simple Delete

err := users.User.Delete().
    Where(users.ID.Eq("some-uuid")).
    Exec(ctx)

Conditional Delete

err := posts.Post.Delete().
    Where(
        posts.Published.Eq(false),
        posts.CreatedAt.Lt(time.Now().AddDate(0, 0, -30)),
    ).
    Exec(ctx)

Joins

Inner Join

err := posts.Post.Select(posts.Title, users.Name).
    Join(users.User, posts.UserID, users.ID).
    Where(users.Email.Eq("john@example.com")).
    All(ctx, func(row pgm.RowScanner) error {
        var title, userName string
        return row.Scan(&title, &userName)
    })

Left Join

err := users.User.Select(users.Name, posts.Title).
    LeftJoin(posts.Post, users.ID, posts.UserID).
    All(ctx, func(row pgm.RowScanner) error {
        var userName, postTitle string
        return row.Scan(&userName, &postTitle)
    })

Join with Additional Conditions

err := posts.Post.Select(posts.Title, users.Name).
    Join(users.User, posts.UserID, users.ID, users.Email.Like("%@example.com")).
    Where(posts.Published.Eq(true)).
    All(ctx, func(row pgm.RowScanner) error {
        // Process rows
    })

Transactions

Basic Transaction

tx, err := pgm.BeginTx(ctx)
if err != nil {
    log.Fatal(err)
}
defer tx.Rollback(ctx)

// Insert user
var userID string
err = users.User.Insert().
    Set(users.Email, "jane@example.com").
    Set(users.Name, "Jane Doe").
    Returning(users.ID).
    FirstTx(ctx, tx, &userID)
if err != nil {
    return err
}

// Insert post
err = posts.Post.Insert().
    Set(posts.UserID, userID).
    Set(posts.Title, "My First Post").
    Set(posts.Content, "Hello, World!").
    ExecTx(ctx, tx)
if err != nil {
    return err
}

// Commit transaction
if err := tx.Commit(ctx); err != nil {
    return err
}

Full-Text Search

PostgreSQL full-text search helpers:

// Search with AND operator (all terms must match)
searchQuery := pgm.TsAndQuery("golang database")
// Result: "golang & database"

// Search with prefix matching
searchQuery := pgm.TsPrefixAndQuery("gol data")
// Result: "gol:* & data:*"

// Search with OR operator (any term matches)
searchQuery := pgm.TsOrQuery("golang rust")
// Result: "golang | rust"

// Prefix OR search
searchQuery := pgm.TsPrefixOrQuery("go ru")
// Result: "go:* | ru:*"

// Use in query (assuming you have a tsvector column)
err := posts.Post.Select(posts.Title, posts.Content).
    Where(posts.SearchVector.Match(pgm.TsPrefixAndQuery(searchTerm))).
    OrderBy(posts.CreatedAt.Desc()).
    All(ctx, func(row pgm.RowScanner) error {
        // Process results
    })

CLI Tool

Usage

pgm -o <output_directory> <schema.sql>

Options

-o string      Output directory path (required)
-version       Show version information

Examples

# Generate from a single schema file
pgm -o ./db ./schema.sql

# Generate from concatenated migrations
cat migrations/*.sql > /tmp/schema.sql && pgm -o ./db /tmp/schema.sql

# Check version
pgm -version

Known Limitations

The CLI tool uses a regex-based SQL parser with the following limitations:

• ❌ Multi-line comments /* */ are not supported
• ❌ Complex data types (arrays, JSON, JSONB) may not parse correctly
• ❌ Quoted identifiers with special characters may fail
• ❌ Advanced PostgreSQL features (PARTITION BY, INHERITS) not supported
• ❌ Some constraints (CHECK, EXCLUDE) are not parsed

Workarounds:

• Use simple CREATE TABLE statements
• Avoid complex PostgreSQL-specific syntax in schema files
• Split complex schemas into multiple simple statements
• Remove comments before running the generator

For complex schemas, consider contributing a more robust parser or using a proper SQL parser library.

Generated Code Structure

For a table named users, pgm generates:

db/
└── users/
    └── users.go

The generated file contains:

• Generated code header with version and timestamp
• Table definition (User)
• Column field definitions (ID, Email, Name, etc.)
• Type-safe query builders (Select(), Insert(), Update(), Delete())

Example header:

// Code generated by code.patial.tech/go/pgm/cmd v1.2.3 on 2025-01-27 15:04:05 DO NOT EDIT.

The version in generated files helps track which version of the CLI tool was used, making it easier to identify when regeneration is needed after upgrades.

API Documentation

Connection Pool

InitPool

Initialize the connection pool (must be called once at startup):

pgm.InitPool(pgm.Config{
    ConnString:      "postgres://...",
    MaxConns:        25,
    MinConns:        5,
    MaxConnLifetime: time.Hour,
    MaxConnIdleTime: time.Minute * 30,
})

Configuration Validation:

• MinConns cannot be greater than MaxConns
• Connection counts cannot be negative
• Connection string is required

ClosePool

Close the connection pool gracefully (call during application shutdown):

func main() {
    pgm.InitPool(pgm.Config{
        ConnString: "postgres://...",
    })
    defer pgm.ClosePool() // Ensures proper cleanup
    
    // Your application code
}

GetPool

Get the underlying pgx pool:

pool := pgm.GetPool()

Query Conditions

Available condition methods on fields:

• Eq(value) - Equal to
• NotEq(value) - Not equal to
• Gt(value) - Greater than
• Gte(value) - Greater than or equal to
• Lt(value) - Less than
• Lte(value) - Less than or equal to
• Like(pattern) - LIKE pattern match
• ILike(pattern) - Case-insensitive LIKE
• In(values...) - IN list
• NotIn(values...) - NOT IN list
• IsNull() - IS NULL
• IsNotNull() - IS NOT NULL
• Between(start, end) - BETWEEN range

Field Methods

• Asc() - Sort ascending
• Desc() - Sort descending
• Name() - Get column name
• String() - Get fully qualified name (table.column)

Utilities

• pgm.PgTime(t time.Time) - Convert Go time to PostgreSQL timestamptz
• pgm.PgTimeNow() - Current time as PostgreSQL timestamptz
• pgm.IsNotFound(err) - Check if error is "no rows found"

Best Practices

1. Use transactions for related operations - Ensure data consistency
2. Define schema in SQL - Use migration tools like dbmate for schema management
3. Regenerate after schema changes - Run the CLI tool after any schema modifications
4. Use your own models - Don't let the database dictate your domain models
5. Handle pgx.ErrNoRows - Use pgm.IsNotFound(err) for cleaner error checking
6. Always use context with timeouts - Prevent queries from running indefinitely
7. Validate UPDATE queries - Ensure Set() is called before Exec()
8. Be careful with DELETE - Always use Where() unless you want to delete all rows

Important Safety Notes

⚠️ DELETE Operations

DELETE without WHERE clause will delete ALL rows in the table:

// ❌ DANGEROUS - Deletes ALL rows!
users.User.Delete().Exec(ctx)

// ✅ SAFE - Deletes specific rows
users.User.Delete().Where(users.ID.Eq("user-id")).Exec(ctx)

⚠️ UPDATE Operations

UPDATE requires at least one Set() call:

// ❌ ERROR - No columns to update
users.User.Update().Where(users.ID.Eq(1)).Exec(ctx)
// Returns: "update query has no columns to update"

// ✅ CORRECT
users.User.Update().
    Set(users.Name, "New Name").
    Where(users.ID.Eq(1)).
    Exec(ctx)

⚠️ Query Timeouts

Always use context with timeout to prevent hanging queries:

// ✅ RECOMMENDED
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

err := users.User.Select(users.Email).
    Where(users.ID.Eq("some-id")).
    First(ctx, &email)

⚠️ Connection String Security

Never log or expose database connection strings as they contain credentials. The library does not sanitize connection strings in error messages.

Performance

pgm is designed for performance:

• Zero reflection overhead
• Efficient string building with sync.Pool
• Leverages pgx's high-performance connection pooling
• Minimal allocations in query building
• Direct scanning into your types

Requirements

• Go 1.20 or higher
• PostgreSQL 12 or higher

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This project is licensed under the MIT License - see the LICENSE file for details.

Author

Ankit Patial - Patial Tech

Acknowledgments

Built on top of the excellent jackc/pgx library.

---

Made with ❤️ for the Go community