pgm/README.md

# pgm - PostgreSQL Query Mapper

[![Go Reference](https://pkg.go.dev/badge/code.patial.tech/go/pgm.svg)](https://pkg.go.dev/code.patial.tech/go/pgm)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

A lightweight, type-safe PostgreSQL query builder for Go, built on top of [jackc/pgx](https://github.com/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?](#why-pgm)
- [Installation](#installation)
- [Quick Start](#quick-start)
- [Usage Examples](#usage-examples)
  - [SELECT Queries](#select-queries)
  - [INSERT Queries](#insert-queries)
  - [UPDATE Queries](#update-queries)
  - [DELETE Queries](#delete-queries)
  - [Joins](#joins)
  - [Transactions](#transactions)
  - [Full-Text Search](#full-text-search)
- [CLI Tool](#cli-tool)
- [API Documentation](#api-documentation)
- [Contributing](#contributing)
- [License](#license)

## Why pgm?

### The Problem with Existing ORMs

While Go has excellent ORMs like [ent](https://github.com/ent/ent) and [sqlc](https://github.com/sqlc-dev/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](https://github.com/amacneil/dbmate) for migrations

## Installation

```bash
go get code.patial.tech/go/pgm
```

Install the CLI tool for schema code generation:

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

### Building from Source

Build with automatic version detection (uses git tags):

```bash
# 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:

```bash
pgm -version
```

## Quick Start

### 1. Create Your Schema

Create a SQL schema file `schema.sql` or use the one created by [dbmate](https://github.com/amacneil/dbmate):

```sql
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:

```bash
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

```go
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:

```go
// ✅ 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**:

```go
// ❌ 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:

```go
// ✅ 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

```go
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

```go
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

```go
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

```go
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

```go
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

```go
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

```go
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

```go
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)

```go
// 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

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

#### Update Multiple Fields

```go
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

```go
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

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

#### Conditional Delete

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

### Joins

#### Inner Join

```go
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

```go
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

```go
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

```go
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:

```go
// 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

```bash
pgm -o <output_directory> <schema.sql>
```

### Options

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

### Examples

```bash
# 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:**
```go
// 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):

```go
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):

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

#### GetPool

Get the underlying pgx pool:

```go
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:

```go
// ❌ 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:

```go
// ❌ 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:

```go
// ✅ 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](LICENSE) file for details.

## Author


**Ankit Patial** - [Patial Tech](https://code.patial.tech)

## Acknowledgments

Built on top of the excellent [jackc/pgx](https://github.com/jackc/pgx) library.

---

**Made with ❤️ for the Go community**