pgxutil
A collection of helpers to deal with pgx toolkit.
Example:
package main
import (
"context"
"fmt"
"log"
"github.com/allisson/pgxutil"
"github.com/jackc/pgx/v4"
)
type Player struct {
ID int `db:"id"`
Name string `db:"name" fieldtag:"insert,update"`
Age int `db:"age" fieldtag:"insert,update"`
}
func main() {
ctx := context.Background()
// Run a database with docker: docker run --name test --restart unless-stopped -e POSTGRES_USER=user -e POSTGRES_PASSWORD=password -e POSTGRES_DB=pgxutil -p 5432:5432 -d postgres:14-alpine
// Connect to database
conn, err := pgx.Connect(ctx, "postgres://user:password@localhost/pgxutil?sslmode=disable")
if err != nil {
log.Fatal(err)
}
defer conn.Close(ctx)
// Create table
_, err = conn.Exec(ctx, `
CREATE TABLE IF NOT EXISTS players(
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
age INTEGER NOT NULL
)
`)
if err != nil {
log.Fatal(err)
}
// Insert players
r9 := Player{
Name: "Ronaldo Fenômeno",
Age: 44,
}
r10 := Player{
Name: "Ronaldinho Gaúcho",
Age: 41,
}
tag := "insert" // will use fields with fieldtag:"insert"
if err := pgxutil.Insert(ctx, conn, tag, "players", &r9); err != nil {
log.Fatal(err)
}
if err := pgxutil.Insert(ctx, conn, tag, "players", &r10); err != nil {
log.Fatal(err)
}
// Get player
findOptions := pgxutil.NewFindOptions().WithFilter("name", r10.Name)
if err := pgxutil.Get(ctx, conn, "players", findOptions, &r10); err != nil {
log.Fatal(err)
}
findOptions = pgxutil.NewFindOptions().WithFilter("name", r9.Name)
if err := pgxutil.Get(ctx, conn, "players", findOptions, &r9); err != nil {
log.Fatal(err)
}
// Select players
players := []*Player{}
findAllOptions := pgxutil.NewFindAllOptions().WithLimit(10).WithOffset(0).WithOrderBy("name asc")
if err := pgxutil.Select(ctx, conn, "players", findAllOptions, &players); err != nil {
log.Fatal(err)
}
for _, p := range players {
fmt.Printf("%#v\n", p)
}
// Update player
tag = "update" // will use fields with fieldtag:"update"
r10.Name = "Ronaldinho Bruxo"
if err := pgxutil.Update(ctx, conn, tag, "players", r10.ID, &r10); err != nil {
log.Fatal(err)
}
// Delete player
if err := pgxutil.Delete(ctx, conn, "players", r9.ID); err != nil {
log.Fatal(err)
}
}
Options for FindOptions and FindAllOptions:
package main
import (
"github.com/allisson/pgxutil"
)
func main() {
findOptions := pgxutil.NewFindOptions().
WithFields([]string{"id", "name"}). // Return only id and name fields
WithFilter("id", 1). // WHERE id = 1
WithFilter("id", nil). // WHERE id IS NULL
WithFilter("id.in", "1,2,3"). // WHERE id IN (1, 2, 3)
WithFilter("id.notin", "1,2,3"). // WHERE id NOT IN ($1, $2, $3)
WithFilter("id.not", 1). // WHERE id <> 1
WithFilter("id.gt", 1). // WHERE id > 1
WithFilter("id.gte", 1). // WHERE id >= 1
WithFilter("id.lt", 1). // WHERE id < 1
WithFilter("id.lte", 1). // WHERE id <= 1
WithFilter("id.like", 1). // WHERE id LIKE 1
WithFilter("id.null", true). // WHERE id.null IS NULL
WithFilter("id.null", false) // WHERE id.null IS NOT NULL
findAllOptions := pgxutil.NewFindAllOptions().
WithFields([]string{"id", "name"}). // Return only id and name fields
WithFilter("id", 1). // WHERE id = 1
WithFilter("id", nil). // WHERE id IS NULL
WithFilter("id.in", "1,2,3"). // WHERE id IN (1, 2, 3)
WithFilter("id.notin", "1,2,3"). // WHERE id NOT IN ($1, $2, $3)
WithFilter("id.not", 1). // WHERE id <> 1
WithFilter("id.gt", 1). // WHERE id > 1
WithFilter("id.gte", 1). // WHERE id >= 1
WithFilter("id.lt", 1). // WHERE id < 1
WithFilter("id.lte", 1). // WHERE id <= 1
WithFilter("id.like", 1). // WHERE id LIKE 1
WithFilter("id.null", true). // WHERE id.null IS NULL
WithFilter("id.null", false). // WHERE id.null IS NOT NULL
WithLimit(10). // LIMIT 10
WithOffset(0). // OFFSET 0
WithOrderBy("name asc") // ORDER BY name asc
}