gopg

gopg is a lightweight PostgreSQL helper 简体中文文档

Features

• Simple Insert/Update/Delete/Select/Find
• Chain query builder in helper.Query
• Condition builder in condition package
• Context/timeout helpers
• Native PostgreSQL placeholders ($1/$2/...)
• PostgreSQL types:
  • Vector for pgvector
  • JSONB for jsonb
  • StringArray / Int64Array / Float64Array for arrays

Install

go get github.com/grpc-boot/gopg

Initialize

Choose a PostgreSQL driver in your application (example with pgx):

import (
    _ "github.com/jackc/pgx/v5/stdlib"
)

opts := gopg.DefaultPostgresOptions()
opts.Host = "127.0.0.1"
opts.Port = 5432
opts.DBName = "demo"
opts.UserName = "postgres"
opts.Password = "postgres"
opts.DriverName = "pgx"

db, err := gopg.NewDb(opts)
if err != nil {
    panic(err)
}
defer db.Close()

CRUD

err := gopg.Insert(db.Executor(), "users", helper.Columns{"name", "age"}, helper.Row{"alice", 18})

n, err := gopg.Update(
    db.Executor(),
    "users",
    helper.Columns{"age"},
    helper.Row{20},
    condition.Equal("name", "alice"),
)

records, err := gopg.Select(
    db.Executor(),
    "SELECT id,name FROM users WHERE age > $1 ORDER BY id DESC LIMIT $2",
    10,
    20,
)

Query Builder

sql, args := helper.NewQuery().
    Select("id", "name").
    From("users").
    Where(condition.And(
        condition.Ge("age", 18),
        condition.Like("name", "a%"),
    )).
    OrderBy("id DESC").
    Limit(20).
    Offset(0).
    Sql()

rows, err := gopg.Select(db.Executor(), sql, args...)

pgvector / jsonb / array

type Doc struct {
    ID        int64
    Embedding gopg.Vector
    Meta      gopg.JSONB
    Tags      gopg.StringArray
}

meta, _ := gopg.NewJSONB(map[string]any{"lang": "zh", "author": "codex"})
vec := gopg.Vector{0.12, 0.98, -0.5}

gopg.Insert(db.Executor(), "docs",
    helper.Columns{"embedding", "meta", "tags"},
    helper.Row{vec, meta, gopg.StringArray{"go", "postgres"}},
)

sql, args := helper.NewQuery().
    Select("id", "embedding", "meta", "tags").
    From("docs").
    Where(condition.PgVectorCosine("embedding", vec, 0.3)).
    OrderBy("embedding <=> $1 ASC").
    Limit(5).
    Sql()

rows, err := gopg.SelectBytes(db.Executor(), sql, args...)
_ = rows
_ = err

Logging

gopg.SetSqlLogger(func(query string, args ...any) {
    fmt.Println(query, args)
})
gopg.SetErrLogger(func(err error, query string, args ...any) {
    fmt.Println("sql err", err, query, args)
})