Golang data mapping library for postgres and pgx.
go get -u github.com/wcamarao/pmx
- Simple data mapping with struct tags
- Explicit by design, no magic or conventions
- Select database records into an annotated struct or slice
- Insert and update database records from an annotated struct
- Compatible with pgx Exec/Query interface i.e.
pgxpool.Pool
,pgx.Conn
,pgx.Tx
- Support transient fields and auto generated values
Given the following table:
create table events (
id uuid primary key,
recorded_at timestamptz
);
Annotate a data model with struct tags:
type Event struct {
ID string `db:"id" table:"events"`
RecordedAt time.Time `db:"recorded_at"`
Transient string // ignored by pmx
transient string // ignored by pmx
}
- The first struct field must be annotated with
table
for insert and update operations - Struct fields annotated with
db
must be exported - Transient fields can be optionally exported
Always provide a struct pointer.
type Event struct {
ID string `db:"id" table:"events"`
RecordedAt time.Time `db:"recorded_at"`
}
func main() {
ctx := context.Background()
conn, err := pgx.Connect(ctx, "postgresql://...")
if err != nil {
panic(err)
}
defer conn.Close(ctx)
event := Event{
ID: "a1eff19b-4624-46c6-9e09-5910e7b2938d",
RecordedAt: time.Now(),
}
// Generated query: insert into events (id, recorded_at) values ($1, $2)
_, err = pmx.Insert(ctx, conn, &event)
if err != nil {
panic(err)
}
fmt.Printf("%+v\n", event)
}
Always provide a struct pointer.
Selecting into a struct will error with pgx.ErrNoRows
if an empty dataset is returned from the query.
type Event struct {
ID string `db:"id" table:"events"`
RecordedAt time.Time `db:"recorded_at"`
}
func main() {
ctx := context.Background()
conn, err := pgx.Connect(ctx, "postgresql://...")
if err != nil {
panic(err)
}
defer conn.Close(ctx)
var event Event
err = pmx.Select(ctx, conn, &event, "select * from events where id = $1", "a1eff19b-4624-46c6-9e09-5910e7b2938d")
if errors.Is(err, pgx.ErrNoRows) {
panic("event not found")
}
if err != nil {
panic(err)
}
fmt.Printf("%+v\n", event)
}
Always provide a slice pointer. The underlying slice type must be a struct pointer.
Selecting into a slice won't error if an empty dataset is returned from the query.
type Event struct {
ID string `db:"id" table:"events"`
RecordedAt time.Time `db:"recorded_at"`
}
func main() {
ctx := context.Background()
conn, err := pgx.Connect(ctx, "postgresql://...")
if err != nil {
panic(err)
}
defer conn.Close(ctx)
var events []*Event
err = pmx.Select(ctx, conn, &events, "select * from events limit 3")
if err != nil {
panic(err)
}
if len(events) == 0 {
panic("no events found")
}
fmt.Printf("%+v\n", events)
}
Always provide a struct pointer.
The last argument UpdateOptions
specifies:
Set
: explicit struct fields to be updatedBy
: explicit struct fields to be matched by equality in thewhere
clause
type Event struct {
ID string `db:"id" table:"events"`
RecordedAt time.Time `db:"recorded_at"`
}
func main() {
ctx := context.Background()
conn, err := pgx.Connect(ctx, "postgresql://...")
if err != nil {
panic(err)
}
defer conn.Close(ctx)
event := Event{
ID: "a1eff19b-4624-46c6-9e09-5910e7b2938d",
RecordedAt: time.Now(),
}
// Generated query: update events set recorded_at = $1 where id = $2
_, err = pmx.Update(ctx, conn, &event, &pmx.UpdateOptions{
Set: []string{"RecordedAt"},
By: []string{"ID"},
})
if err != nil {
panic(err)
}
fmt.Printf("%+v\n", event)
}
Given the following table with an auto generated value, e.g. serial/sequence:
create table events (
id bigserial primary key,
recorded_at timestamptz
);
Annotate the ID
struct field with a generated:"auto"
struct tag:
type Event struct {
ID string `db:"id" generated:"auto" table:"events"`
RecordedAt time.Time `db:"recorded_at"`
}
The id
column will be excluded from insert values
and update set
statements.
The error pmx.ErrInvalidRef
("invalid ref") means you provided an invalid pointer reference.