pgm

package module
v0.1.5 Latest Latest
Warning

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

 Go to latest Published: Aug 11, 2025 License: MIT Imports: 12 Imported by: 0

README

pgm - PostgreSQL Query Mapper

A lightweight ORM built on top of jackc/pgx database connection pool.

ORMs I Like in the Go Ecosystem

Why Not Use ent?

ent is a feature-rich ORM with schema definition, automatic migrations, integration with gqlgen (GraphQL server), and more. It provides nearly everything you could want in an ORM.

However, it can be overkill. The generated code supports a wide range of features, many of which you may not use, significantly increasing the compiled binary size.

Why Not Use sqlc?

sqlc is a great tool, but it often feels like the database layer introduces its own models. This forces you to either map your application’s models to these database models or use the database models directly, which may not align with your application’s design.

Issues with Existing ORMs

Here are some common pain points with ORMs:

  • Auto Migrations: Many ORMs either lack robust migration support or implement complex methods for simple schema changes. This can obscure the database schema, making it harder to understand and maintain. A database schema should be defined in clear SQL statements that can be tested in a SQL query editor. Tools like dbmate provide a mature solution for managing migrations, usable via CLI or in code.

  • Excessive Code Generation: ORMs often generate excessive code for various conditions and scenarios, much of which goes unused.

  • Generated Models for Queries: Auto-generated models for SELECT queries force you to either adopt them or map them to your application’s models, adding complexity.

A Hybrid Approach: Plain SQL Queries with pgm

Plain SQL queries are not inherently bad but come with challenges:

  • Schema Change Detection: Changes in the database schema are not easily detected.
  • SQL Injection Risks: Without parameterized queries, SQL injection becomes a concern.

pgm addresses these issues by providing a lightweight CLI tool that generates Go files for your database schema. These files help you write SQL queries while keeping track of schema changes, avoiding hardcoded table and column names.

Generating pgm Schema Files

Run the following command to generate schema files:

go run code.partial.tech/go/pgm/cmd -o ./db ./schema.sql

once you have the schama files created you can use pgm as

package main

import (
    "code.partial.tech/go/pgm"
    "myapp/db/user" // scham  create by pgm/cmd
)

type MyModel struct {
    ID    string
    Email string
}

func main() {
    println("Initializing pgx connection pool")
    pgm.InitPool(pgm.Config{
        ConnString: url,
    })

    // Select query to fetch the first record
    // Assumes the schema is defined in the "db" package with a User table
    var v MyModel
    err := db.User.Select(user.ID, user.Email).
        Where(user.Email.Like("anki%")).
        First(context.TODO(), &v.ID, &v.Email)
    if err != nil {
        println("Error:", err.Error())
        return
    }

    println("User email:", v.Email)
}

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrConnStringMissing = errors.New("connection string is empty")
	ErrInitTX            = errors.New("failed to init db.tx")
	ErrCommitTX          = errors.New("failed to commit db.tx")
	ErrNoRows            = errors.New("no data found")
)

Functions

func BeginTx 

func BeginTx(ctx context.Context) (pgx.Tx, error)

BeginTx begins a pgx poll transaction

func GetPool 

func GetPool() *pgxpool.Pool

GetPool instance

func InitPool added in v0.0.2 

func InitPool(conf Config)

InitPool will create new pgxpool.Pool and will keep it for its working

func IsNotFound 

func IsNotFound(err error) bool

IsNotFound error check

func PgTime 

func PgTime(t time.Time) pgtype.Timestamptz

PgTime as in UTC

func PgTimeNow 

func PgTimeNow() pgtype.Timestamptz

Types

type AfterGroupBy 

type AfterGroupBy interface {
	HavinClause
	OrderByClause
	LimitClause
	OffsetClause
	Query
}

type AfterHaving 

type AfterHaving interface {
	OrderByClause
	LimitClause
	OffsetClause
	Query
}

type AfterLimit 

type AfterLimit interface {
	OffsetClause
	Query
}

type AfterOffset 

type AfterOffset interface {
	LimitClause
	Query
}

type AfterOrderBy 

type AfterOrderBy interface {
	LimitClause
	OffsetClause
	Query
}

type AfterWhere 

type AfterWhere interface {
	WhereClause
	GroupByClause
	OrderByClause
	LimitClause
	OffsetClause
	Query
}

type All 

type All interface {
	// Query rows
	//
	// don't forget to close() rows
	All(ctx context.Context, rows RowsCb) error
	// Query rows
	//
	// don't forget to close() rows
	AllTx(ctx context.Context, tx pgx.Tx, rows RowsCb) error
}

type Clause 

type Clause interface {
	Select(fields ...Field) SelectClause
}

type Cond 

type Cond struct {
	Val any

	Field string
	// contains filtered or unexported fields
}

func (*Cond) Condition 

func (cv *Cond) Condition(args *[]any, argIdx int) string

type CondAction 

type CondAction uint8
const (
	CondActionNothing CondAction = iota
	CondActionNeedToClose
	CondActionSubQuery
)

Contdition actions

type CondGroup 

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

func (*CondGroup) Condition 

func (c *CondGroup) Condition(args *[]any, argIdx int) string

type Conditioner 

type Conditioner interface {
	Condition(args *[]any, idx int) string
}

func And 

func And(cond ...Conditioner) Conditioner

func Or 

func Or(cond ...Conditioner) Conditioner

type Config 

type Config struct {
	ConnString      string
	MaxConns        int32
	MinConns        int32
	MaxConnLifetime time.Duration
	MaxConnIdleTime time.Duration
}

type Do 

type Do interface {
	DoNothing() Execute
	DoUpdate(fields ...Field) Execute
}

type Execute 

type Execute interface {
	Exec(ctx context.Context) error
	ExecTx(ctx context.Context, tx pgx.Tx) error
	Stringer
}

type Field 

type Field string

Field related to a table

func ConcatWs 

func ConcatWs(sep string, fields ...Field) Field

func StringAgg 

func StringAgg(exp, sep string) Field

func StringAggCast 

func StringAggCast(exp, sep string) Field

func (Field) Avg 

func (f Field) Avg() Field

Avg fn wrapping of field

func (Field) BooleanEscape added in v0.0.7 

func (f Field) BooleanEscape() Field

BooleanEscape will return a false for null value

func (Field) Count 

func (f Field) Count() Field

Count fn wrapping of field

func (Field) Eq 

func (f Field) Eq(val any) Conditioner

Eq is equal

func (Field) EqFold 

func (f Field) EqFold(val string) Conditioner

EqualFold will use LOWER(column_name) = LOWER(val) for comparision

func (Field) Gt 

func (f Field) Gt(val any) Conditioner

func (Field) Gte 

func (f Field) Gte(val any) Conditioner

func (Field) ILike 

func (f Field) ILike(val string) Conditioner

ILIKE is case-insensitive

func (Field) In added in v0.1.3 

func (f Field) In(val ...any) Conditioner

In using ANY

func (Field) IsNotNull added in v0.0.6 

func (f Field) IsNotNull() Conditioner

func (Field) IsNull added in v0.0.6 

func (f Field) IsNull() Conditioner

func (Field) Like 

func (f Field) Like(val string) Conditioner

func (Field) LikeFold 

func (f Field) LikeFold(val string) Conditioner

func (Field) Lower added in v0.0.5 

func (f Field) Lower() Field

func (Field) Lt added in v0.0.5 

func (f Field) Lt(val any) Conditioner

func (Field) Lte added in v0.0.5 

func (f Field) Lte(val any) Conditioner

func (Field) Max added in v0.0.5 

func (f Field) Max() Field

func (Field) Min added in v0.0.5 

func (f Field) Min() Field

func (Field) Name 

func (f Field) Name() string

func (Field) NotEq added in v0.1.3 

func (f Field) NotEq(val any) Conditioner

func (Field) NotIn 

func (f Field) NotIn(val ...any) Conditioner

NotIn using ANY

func (Field) NotInSubQuery 

func (f Field) NotInSubQuery(qry WhereClause) Conditioner

NotInSubQuery using ANY

func (Field) NumberEscape added in v0.0.7 

func (f Field) NumberEscape() Field

NumberEscape will return a zero string for null value

func (Field) String 

func (f Field) String() string

func (Field) StringEscape added in v0.0.7 

func (f Field) StringEscape() Field

StringEscape will return a empty string for null value

func (Field) Sum added in v0.0.5 

func (f Field) Sum() Field

func (Field) Trim added in v0.0.5 

func (f Field) Trim() Field

func (Field) Upper added in v0.0.5 

func (f Field) Upper() Field

type First 

type First interface {
	First(ctx context.Context, dest ...any) error
	FirstTx(ctx context.Context, tx pgx.Tx, dest ...any) error
	Stringer
}

type GroupByClause 

type GroupByClause interface {
	GroupBy(fields ...Field) AfterGroupBy
}

type HavinClause 

type HavinClause interface {
	Having(cond ...Conditioner) AfterHaving
}

type Insert 

type Insert interface {
	Set(field Field, val any) InsertClause
	SetMap(fields map[Field]any) InsertClause
}

type InsertClause 

type InsertClause interface {
	Insert
	Returning(field Field) First
	OnConflict(fields ...Field) Do
	Execute
	Stringer
}

type LimitClause 

type LimitClause interface {
	Limit(v int) AfterLimit
}

type OffsetClause 

type OffsetClause interface {
	Offset(v int) AfterOffset
}

type OrderByClause 

type OrderByClause interface {
	OrderBy(fields ...Field) AfterOrderBy
}

type Query 

type Query interface {
	First
	All
	Stringer
}

type RowScanner 

type RowScanner interface {
	Scan(dest ...any) error
}

type RowsCb 

type RowsCb func(row RowScanner) error

type SelectClause 

type SelectClause interface {
	// Join and Inner Join are same
	Join(m Table, t1Field, t2Field Field, cond ...Conditioner) SelectClause
	LeftJoin(m Table, t1Field, t2Field Field, cond ...Conditioner) SelectClause
	RightJoin(m Table, t1Field, t2Field Field, cond ...Conditioner) SelectClause
	FullJoin(m Table, t1Field, t2Field Field, cond ...Conditioner) SelectClause
	CrossJoin(m Table) SelectClause
	WhereClause
	OrderByClause
	GroupByClause
	LimitClause
	OffsetClause
	Query
	// contains filtered or unexported methods
}

type Stringer 

type Stringer interface {
	String() string
}

type Table 

type Table struct {
	Name       string
	PK         []string
	FieldCount uint16
	// contains filtered or unexported fields
}

Table in database

func (Table) Debug 

func (t Table) Debug() Clause

Debug when set true will print generated query string in stdout

func (*Table) Delete 

func (t *Table) Delete() WhereOrExec

func (*Table) Insert 

func (t *Table) Insert() Insert

func (Table) Select 

func (t Table) Select(field ...Field) SelectClause

Select clause

func (*Table) Update 

func (t *Table) Update() Update

type Update 

type Update interface {
	Set(field Field, val any) UpdateClause
	SetMap(fields map[Field]any) UpdateClause
}

type UpdateClause 

type UpdateClause interface {
	Update
	Where(cond ...Conditioner) WhereOrExec
}

type WhereClause 

type WhereClause interface {
	Where(cond ...Conditioner) AfterWhere
}

type WhereOrExec 

type WhereOrExec interface {
	Where(cond ...Conditioner) WhereOrExec
	Execute
}

Source Files

View all Source files

Directories

Path Synopsis
db
db/branchuser
db/comment
db/employee
db/post
db/user
db/usersession

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.