pgtalk

package module
v1.8.1 Latest Latest
Warning

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

 Go to latest Published: Jan 23, 2025 License: MIT Imports: 14 Imported by: 0

README

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)

or by collecting columns first

cols := NewColumns()
cols.Add(products.Code.Set("testme"))
if categoryWhasChanged {
	cols.Add(products.CategoryID.Set(changedValue))
}
m := products.Update(cols...).Where(products.ID.Equals(10)).
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.

custom datatype mappping

If your datatype can be aliased (use) to one of the supported types then you can define such mapping in a configuration file.

pgtalk-gen -mapping your-mapping.json

An example of such as mapping file your-mapping.json:

{
	"character(26)": {
		"use": "character varying"
	}
}

If your datatype cannot be aliased then you can write the missing logic for a datatype and an accessor type. See example test/types/real.go for such an implementation. The configuration for this mapping is:

{
	"real":{
		"nullableFieldType":"types.Real",
		"newAccessFuncName":"types.NewRealAccess",
		"imports": ["github.com/emicklei/pgtalk/test/types"]
	}
}

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.

Documentation

Overview

Package pgtalk provides functionality to create and execute type-safe Postgres Queries and Mutations using generated struct-per-table.

Index

Constants

View Source 
const (
	IsPrimary  = true
	NotPrimary = false
	NotNull    = true
	Nullable   = false
)
View Source 
const (
	MutationDelete = iota
	MutationInsert
	MutationUpdate
)
View Source 
const HideNilValues = true

Variables

View Source 
var EmptyColumnAccessor = []ColumnAccessor{}

Functions

func IndentedSQL added in v1.0.1 

func IndentedSQL(some SQLWriter) string

IndentedSQL returns source with tabs and lines trying to have a formatted view.

func IsNotNull 

func IsNotNull(e SQLExpression) nullCheck

IsNotNull returns an expression with the IS NOT NULL condition

func IsNull 

func IsNull(e SQLExpression) nullCheck

IsNull returns an expression with the IS NULL condition

func NewBooleanAccess 

func NewBooleanAccess(info ColumnInfo, writer fieldAccessFunc) booleanAccess

func NewBytesAccess 

func NewBytesAccess(info ColumnInfo,
	valueWriter func(dest any) *string) bytesAccess

func NewFloat64Access 

func NewFloat64Access(info ColumnInfo, writer fieldAccessFunc) float64Access

func NewInt32Access added in v0.29.2 

func NewInt32Access(
	info ColumnInfo,
	valueWriter func(dest any) any) int32Access

func NewInt64Access 

func NewInt64Access(
	info ColumnInfo,
	valueWriter func(dest any) any) int64Access

func NewJSONAccess added in v1.0.0 

func NewJSONAccess(info ColumnInfo,
	valueWriter func(dest any) any) jsonAccess

func NewTextAccess 

func NewTextAccess(info ColumnInfo, writer fieldAccessFunc) textAccess

func NewTimeAccess 

func NewTimeAccess(info ColumnInfo,
	valueWriter fieldAccessFunc) timeAccess

func SQL 

func SQL(some SQLWriter) string

SQL returns source as a oneliner without tabs or line ends.

func SQLAs added in v0.23.1 

func SQLAs(sql, name string) *computedField
SQLAs returns a ColumnAccessor with a customer SQL expression.

The named result will be available using the GetExpressionResult method of the record type.

func StringWithFields 

func StringWithFields(v any, includePresent bool) string

Types

type ColumnAccessor 

type ColumnAccessor interface {
	SQLWriter
	Name() string
	ValueToInsert() any
	Column() ColumnInfo
	// FieldValueToScan returns the address of the value of the field in the entity
	FieldValueToScan(entity any) any
	// AppendScannable collects values for scanning by a result Row
	// Cannot use ValueToInsert because that looses type information such that the Scanner will use default mapping
	AppendScannable(list []any) []any
	// Get accesses the value from a map.
	// (unfortunately, Go methods cannot have additional type parameters:
	// Get[V](values map[string]any) V )
	Get(values map[string]any) any
}

type ColumnAccessorSlice added in v1.8.1 

type ColumnAccessorSlice []ColumnAccessor

ColumnAccessorSlice is a slice of ColumnAccessor

func NewColumns added in v1.8.1 

func NewColumns() (list ColumnAccessorSlice)

Returns a ColumnAccessorSlice which is an empty list of ColumnAccessor. This can be used to build a list of columns e.g when updating.

func (*ColumnAccessorSlice) Add added in v1.8.1 

func (s *ColumnAccessorSlice) Add(c ColumnAccessor)

Add appends a non-nil column accessor to the list

type ColumnInfo 

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

func MakeColumnInfo 

func MakeColumnInfo(tableInfo TableInfo, columnName string, isPrimary bool, isNotNull bool, _ uint16) ColumnInfo

MakeColumnInfo creates a ColumnInfo describing a column in a table. The last argument is now ignored (used to be table attribute number, field ordinal).

func (ColumnInfo) Name 

func (c ColumnInfo) Name() string

func (ColumnInfo) SQLOn 

func (c ColumnInfo) SQLOn(w WriteContext)

func (ColumnInfo) TableAlias added in v0.14.0 

func (c ColumnInfo) TableAlias(alias string) ColumnInfo

TableAlias changes the table alias for this column info.

type FieldAccess 

type FieldAccess[T any] struct {
	ColumnInfo
	// contains filtered or unexported fields
}

func NewFieldAccess 

func NewFieldAccess[T any](
	info ColumnInfo,
	writer func(dest any) any) FieldAccess[T]

func (FieldAccess) And added in v0.21.0 

func (FieldAccess) And(e SQLExpression) SQLExpression

func (FieldAccess[T]) AppendScannable added in v0.16.0 

func (a FieldAccess[T]) AppendScannable(list []any) []any

AppendScannable is part of ColumnAccessor

func (FieldAccess[T]) Column 

func (a FieldAccess[T]) Column() ColumnInfo

func (FieldAccess[T]) Compare added in v0.14.0 

func (a FieldAccess[T]) Compare(operator string, operand any) binaryExpression

func (FieldAccess[T]) Concat added in v0.15.0 

func (a FieldAccess[T]) Concat(resultName string, ex SQLExpression) ColumnAccessor

func (FieldAccess[T]) Equals 

func (a FieldAccess[T]) Equals(operand any) binaryExpression

Equals returns a SQLExpression

func (FieldAccess[T]) FieldValueToScan added in v0.15.2 

func (a FieldAccess[T]) FieldValueToScan(entity any) any

func (FieldAccess[T]) Get added in v0.16.0 

func (a FieldAccess[T]) Get(values map[string]any) any

Get returns the value for its columnName from a map (row).

func (FieldAccess[T]) GreaterThan added in v1.3.0 

func (a FieldAccess[T]) GreaterThan(operand any) binaryExpression

GreaterThan returns a SQLExpression

func (FieldAccess[T]) In added in v0.14.0 

func (a FieldAccess[T]) In(values ...T) SQLExpression

In returns a binary expression to check that the value of the fieldAccess is in the values collection.

func (FieldAccess[T]) IsNotNull added in v1.0.0 

func (a FieldAccess[T]) IsNotNull() binaryExpression

IsNotNull creates a SQL Expresion with IS NOT NULL.

func (FieldAccess[T]) IsNull added in v1.0.0 

func (a FieldAccess[T]) IsNull() binaryExpression

IsNull creates a SQL Expresion with IS NULL.

func (FieldAccess[T]) LessThan 

func (a FieldAccess[T]) LessThan(operand any) binaryExpression

LessThan returns a SQLExpression

func (FieldAccess) Or added in v0.21.0 

func (FieldAccess) Or(e SQLExpression) SQLExpression

func (FieldAccess[T]) Set 

func (a FieldAccess[T]) Set(v T) FieldAccess[T]

Set returns a new FieldAccess[T] with a value to set on a T.

func (FieldAccess[T]) TableAlias added in v0.14.0 

func (a FieldAccess[T]) TableAlias(alias string) FieldAccess[T]

TableAlias changes the table alias for this column accessor.

func (FieldAccess[T]) ValueToInsert 

func (a FieldAccess[T]) ValueToInsert() any

type MutationSet 

type MutationSet[T any] struct {
	// contains filtered or unexported fields
}

func MakeMutationSet 

func MakeMutationSet[T any](tableInfo TableInfo, selectors []ColumnAccessor, operationType int) MutationSet[T]

func (MutationSet[T]) Exec 

func (m MutationSet[T]) Exec(ctx context.Context, conn querier, parameters ...*QueryParameter) ResultIterator[T]

Pre: must be run inside transaction. The iterator is closed unless it has data to return (set via Returning). The iterator must be closed on error or after consuming all data.

func (MutationSet[T]) On 

func (m MutationSet[T]) On() MutationSet[T]

todo

func (MutationSet[T]) Returning 

func (m MutationSet[T]) Returning(columns ...ColumnAccessor) MutationSet[T]

func (MutationSet[T]) SQLOn 

func (m MutationSet[T]) SQLOn(w WriteContext)

SQLOn returns the full SQL mutation query

func (MutationSet[T]) Where 

func (m MutationSet[T]) Where(condition SQLExpression) MutationSet[T]

type NullJSON added in v1.0.0 

type NullJSON struct {
	Any   any
	Valid bool
}

NullJSON is a value that can scan a Nullable value to an empty interface (any)

func (*NullJSON) Scan added in v1.0.0 

func (na *NullJSON) Scan(src any) error

Scan implements the database/sql Scanner interface.

type QueryParameter added in v0.23.0 

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

QueryParameter captures any value as a parameter to use in a SQL query or mutation.

func NewParameter added in v0.23.0 

func NewParameter(value any) *QueryParameter

TODO can we use type parameterization here? func NewParameter[T any](value T) *QueryParameter[T] { return &QueryParameter{value: value} }

func (QueryParameter) And added in v0.23.0 

func (QueryParameter) And(e SQLExpression) SQLExpression

func (QueryParameter) Or added in v0.23.0 

func (QueryParameter) Or(e SQLExpression) SQLExpression

func (QueryParameter) SQLOn added in v0.23.0 

func (a QueryParameter) SQLOn(w WriteContext)

SQLOn is part of SQLWriter

type QuerySet 

type QuerySet[T any] struct {
	// contains filtered or unexported fields
}

func MakeQuerySet 

func MakeQuerySet[T any](tableInfo TableInfo, selectors []ColumnAccessor) QuerySet[T]

func (QuerySet) And added in v0.21.0 

func (QuerySet) And(e SQLExpression) SQLExpression

func (QuerySet[T]) Ascending 

func (q QuerySet[T]) Ascending() QuerySet[T]

Ascending is a SQL instruction for ASC sort option

func (QuerySet[T]) Descending 

func (q QuerySet[T]) Descending() QuerySet[T]

Descending is a SQL instruction for DESC sort option

func (QuerySet[T]) Distinct 

func (q QuerySet[T]) Distinct() QuerySet[T]

Distinct is a SQL instruction

func (QuerySet[T]) Exec 

func (d QuerySet[T]) Exec(ctx context.Context, conn querier, parameters ...*QueryParameter) (list []*T, err error)

func (QuerySet[T]) ExecIntoMaps added in v0.15.2 

func (d QuerySet[T]) ExecIntoMaps(ctx context.Context, conn querier, parameters ...*QueryParameter) (list []map[string]any, err error)

ExecIntoMaps executes the query and returns a list of generic maps (column->value). This can be used if you do not want to get full records types or have multiple custom values.

func (QuerySet[T]) Exists 

func (q QuerySet[T]) Exists() unaryExpression

func (QuerySet[T]) For added in v1.5.0 

func (d QuerySet[T]) For(f SQL_FOR) QuerySet[T]

func (QuerySet[T]) FullJoin 

func (d QuerySet[T]) FullJoin(otherQuerySet querySet) join

func (QuerySet[T]) GroupBy 

func (q QuerySet[T]) GroupBy(cas ...ColumnAccessor) QuerySet[T]

GroupBy is a SQL instruction

func (QuerySet[T]) Having 

func (q QuerySet[T]) Having(condition SQLExpression) QuerySet[T]

func (QuerySet[T]) Iterate 

func (d QuerySet[T]) Iterate(ctx context.Context, conn querier, parameters ...*QueryParameter) (*resultIterator[T], error)

func (QuerySet[T]) Join 

func (d QuerySet[T]) Join(otherQuerySet querySet) join

func (QuerySet[T]) LeftOuterJoin 

func (d QuerySet[T]) LeftOuterJoin(otherQuerySet querySet) join

func (QuerySet[T]) Limit 

func (q QuerySet[T]) Limit(limit int) QuerySet[T]

Limit is a SQL instruction

func (QuerySet[T]) Named 

func (q QuerySet[T]) Named(preparedName string) QuerySet[T]

Named sets the name for preparing the statement

func (QuerySet[T]) Offset added in v0.12.0 

func (q QuerySet[T]) Offset(offset int) QuerySet[T]

Offset is a SQL instruction

func (QuerySet) Or added in v0.21.0 

func (QuerySet) Or(e SQLExpression) SQLExpression

func (QuerySet[T]) OrderBy 

func (q QuerySet[T]) OrderBy(cas ...SQLWriter) QuerySet[T]

func (QuerySet[T]) RightJoin deprecated 

func (d QuerySet[T]) RightJoin(otherQuerySet querySet) join

Deprecated: use RightOuterJoin

func (QuerySet[T]) RightOuterJoin added in v1.4.2 

func (d QuerySet[T]) RightOuterJoin(otherQuerySet querySet) join

func (QuerySet[T]) SQLOn 

func (q QuerySet[T]) SQLOn(w WriteContext)

func (QuerySet[T]) SkipLocked added in v1.5.0 

func (d QuerySet[T]) SkipLocked() QuerySet[T]

func (QuerySet[T]) TableAlias added in v0.14.0 

func (q QuerySet[T]) TableAlias(alias string) QuerySet[T]

TableAlias will override the default table or view alias

func (QuerySet[T]) Where 

func (q QuerySet[T]) Where(condition SQLExpression) QuerySet[T]

Where is a SQL instruction

type ResultIterator 

type ResultIterator[T any] interface {
	// Close closes the rows of the iterator, making the connection ready for use again. It is safe
	// to call Close after rows is already closed.
	// Close is called implicitly when no return results are expected.
	Close()
	// Err returns the Query error if any
	Err() error
	// HasNext returns true if a more results are available. If not then Close is called implicitly.
	HasNext() bool
	// Next returns the next row populated in a T.
	Next() (*T, error)
	// GetParams returns all the parameters used in the query. Can be used for debugging or logging
	GetParams() map[int]any
	// CommandTag is valid if the query is an Exec query, i.e. not returning rows.
	CommandTag() pgconn.CommandTag
}

ResultIterator is returned from executing a Query (or Mutation).

type SQLExpression 

type SQLExpression interface {
	SQLWriter
	And(expr SQLExpression) SQLExpression
	Or(expr SQLExpression) SQLExpression
}
var EmptyCondition SQLExpression = noCondition{}

func NewSQLConstant added in v1.2.0 

func NewSQLConstant(value any) SQLExpression

type SQLFunction added in v0.24.0 

type SQLFunction struct {
	Name      string
	Arguments []SQLExpression
}

SQLFunction is for calling any Postgres standard function with zero or more arguments as part of your query. Typically used together with pgtalk.SQLAs.

func NewSQLFunction added in v0.24.0 

func NewSQLFunction(name string, arguments ...SQLExpression) SQLFunction

NewSQLFunction creates a new SQLFunction value.

func (SQLFunction) SQLOn added in v0.24.0 

func (f SQLFunction) SQLOn(w WriteContext)

SQLOn is part of SQLWriter

type SQLLiteral added in v1.0.0 

type SQLLiteral struct {
	Literal string
}

func (SQLLiteral) SQLOn added in v1.0.0 

func (l SQLLiteral) SQLOn(w WriteContext)

type SQLWriter 

type SQLWriter interface {
	// SQLOn writes a valid SQL on a Writer in a context
	SQLOn(w WriteContext)
}

type SQL_FOR added in v1.5.0 

type SQL_FOR string
const (
	FOR_UPDATE        SQL_FOR = "UPDATE"
	FOR_NO_KEY_UPDATE SQL_FOR = "NO KEY UPDATE"
	FOR_SHARE         SQL_FOR = "SHARE"
	FOR_KEY_SHARE     SQL_FOR = "KEY SHARE"
)

type TableInfo 

type TableInfo struct {
	Name   string
	Schema string // e.g. public
	Alias  string
	// Columns are all known columns for this table ;initialized by the generated package
	Columns []ColumnAccessor
}

TableInfo describes a table in the database.

func (TableInfo) Equals 

func (t TableInfo) Equals(o TableInfo) bool

func (TableInfo) FullName added in v1.5.0 

func (t TableInfo) FullName() string

FullName returns the fully qualified name of the table.

func (TableInfo) SQLOn 

func (t TableInfo) SQLOn(w io.Writer)

func (TableInfo) String 

func (t TableInfo) String() string

func (TableInfo) WithAlias added in v0.14.0 

func (t TableInfo) WithAlias(aliasName string) TableInfo

type WriteContext added in v0.14.0 

type WriteContext interface {
	Write(p []byte) (n int, err error)
	WithAlias(tableName, alias string) WriteContext
	TableAlias(tableName, defaultAlias string) string
}

func NewWriteContext added in v0.14.0 

func NewWriteContext(w io.Writer) WriteContext

NewWriteContext returns a new WriteContext to produce SQL

Source Files

View all Source files

Directories

Path Synopsis
cmd
pgtalk-gen
test
tables/categories
tables/products
tables/things
types

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.