Skip to content

generics package and code generator to access PostgreSQL tables and views on top of pgx

License

Notifications You must be signed in to change notification settings

emicklei/pgtalk

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

pgtalk

GoDoc

More type safe SQL query building and execution using Go code generated (pgtalk-gen) from PostgreSQL table definitions. After code generation, you get a Go type for each table or view with functions to create a QuerySet or MutationSet value. Except for query exectution, all operations on a QuerySet or MutationSet will return a copy of that value. This package requires Go SDK version 1.18+ because it uses type parameterization.

status

This package is used in production https://ag5.com and https://go-toad.com and its programming API is stable since v1.0.0.

install

go install github.com/emicklei/pgtalk/cmd/pgtalk-gen@latest

examples

These examples are from the test package in which a few database tables files (categories,products,things) are generated.

Insert

m := products.Insert(
	products.ID.Set(10),
	products.Code.Set("testit"),
	products.CategoryID.Set(1))

it := m.Exec(aConnection)
if err := it.Err(); err != nil {
	....
}

or by example:

p := new(products.Product)
p.SetID(10).SetCode("testit").SetCategoryID(1)		
m := products.Insert(p.Setters()...)

Update

m := products.Update(
		products.Code.Set("testme"),
		products.CategoryID.Set(1)).
	Where(products.ID.Equals(10)).
	Returning(products.Code)

it := m.Exec(aConnection)	
for it.HasNext() {
	p, err := products.Next(p) // p is a *product.Product
	t.Logf("%s,%s", *p.Code)
}		

or by example

p := new(products.Product)
p.SetID(10).SetCode("testme").SetCategoryID(1)		
m := products.Update(p.Setters()...).
		Where(products.ID.Equals(p.ID)).
		Returning(products.Code)

Delete

m := products.Delete().Where(products.ID.Equals(10))

_ = m.Exec(aConnection)

Select

q := products.Select(products.Code).Where(products.Code.Equals("F42"))

products, err := q.Exec(aConnection) // products is a []*product.Product

Arbitrary SQL expressions

q := products.Select(products.ID, pgtalk. SQLAs("UPPER(p1.Code)", "upper"))

// SELECT p1.id,UPPER(p1.Code) AS upper FROM public.products p1

list, _ := q.Exec(context.Background(),aConnection)
for _, each := range list {
	upper := each.GetExpressionResult("upper").(string)
	...
}

SQL query records as maps

q := products.Select(products.ID, pgtalk. SQLAs("UPPER(p1.Code)", "upper"))

// SELECT p1.id,UPPER(p1.Code) AS upper FROM public.products p1

listOfMaps, _ := q.ExecIntoMaps(context.Background(),aConnection)
for _, each := range listOfMaps {
	id := products.ID.Get(each).(pgtype.UUID)
	upper := each["upper"].(string)
	...
}

Using Query parameter

p := NewParameter("F42")
q := products.Select(products.Code).Where(products.Code.Equals(p))

// SELECT p1.code FROM public.products p1 WHERE (p1.code = $1)
// with $1 = "F42"

Joins

Left Outer Join

q :=products.Select(products.Code).Where(products.Code.Equals("F42")).
    LeftOuterJoin(categories.Select(categories.Title)).
    On(products.ID.Equals(categories.ID))

it, _ := q.Exec(aConnection)
for it.HasNext() {
	p := new(products.Product)
	c := new(categories.Category)
	_ = it.Next(p, c)
	t.Logf("%s,%s", *p.Code, *c.Title)
}

Multi Join

q := offer.Select(offer.Columns()...).
	Where(offer.ID.In("01F674G8MDRAPBWA6SB1HWE2VC").And(offer.IsActive.Equals(true))).
	LeftOuterJoin(offer_buyer_permission_rel.Select(offer_buyer_permission_rel.BuyerPermission)).
	On(offer_buyer_permission_rel.OfferId.Equals(offer.ID)).
	LeftOuterJoin(offer_market_rel.Select(offer_market_rel.MarketId)).
	On(offer_market_rel.OfferId.Equals(offer.ID)).
	LeftOuterJoin(buyer_market_rel.Select().Where(buyer_market_rel.BuyerId.Equals("X1010_0100002"))).
	On(offer_market_rel.MarketId.Equals(offer_market_rel.MarketId))

t.Log(pgtalk.PrettySQL(q))

it, err := q.Exec(context.Background(), testConnect)
if err != nil {
	t.Fatal(err)
}
for it.HasNext() {
	offer := new(offer.Offer)
	permission := new(offer_buyer_permission_rel.OfferBuyerPermissionRel)
	market := new(offer_market_rel.OfferMarketRel)
	// The order and types of the entities must match the order of the non-empty Select functions used in the query
	err := it.Next(offer, permission, market)
	if err != nil {
		t.Fatal(err)
	}
	t.Log(offer)
	t.Log(permission)
	t.Log(market)
}

supported Column Types

  • bigint
  • integer
  • jsonb
  • json
  • uuid
  • point
  • interval
  • timestamp with time zone
  • date
  • text
  • character varying
  • numeric
  • boolean
  • timestamp without time zone
  • daterange
  • bytea
  • text[]
  • citext
  • double precision
  • decimal

Send me a PR for a missing type available from https://www.postgresql.org/docs/9.5/datatype.html by modifying mapping.go in the cmd/pgtalk-gen package.

how to run the generator

The user in the connection string must have the right privileges to read schema information.

PGTALK_CONN=postgresql://usr:pwd@host:5432/database pgtalk-gen -o yourpackage
go fmt ./...

If you want to include and/or exclude table names, use additional flags such as:

pgtalk-gen -o yourpackage -include "address.*,employee.*" -exclude "org.*"

or views

pgtalk-gen -views -o yourpackage -include "skills.*"

(c) 2023, https://ernestmicklei.com. MIT License.

About

generics package and code generator to access PostgreSQL tables and views on top of pgx

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published