FAQ

Contents

• How to execute jet statement in SQL transaction?
• How to construct dynamic projection list?
• How to construct dynamic condition?
• How to use jet in multi-tenant environment?
• How to work with exact decimal types?

How to execute jet statement in SQL transaction?

tx, err := db.Begin()
...
stmt := SELECT(...)

var dest Dest
err = stmt.QueryContext(ctx, tx, &dest) // or stmt.ExecContext(ctx, tx) 
...
tx.Commit()

More information about statement execution can be found here.

How to construct dynamic projection list?

var request struct {
	ColumnsToSelect []string
	ShowFullName    bool
}
// ...

projectionList := ProjectionList{}

for _, columnName := range request.ColumnsToSelect {
	switch columnName {
	case Customer.CustomerID.Name():
		projectionList = append(projectionList, Customer.CustomerID)
	case Customer.Email.Name():
		projectionList = append(projectionList, Customer.Email)
	case Customer.CreateDate.Name():
		projectionList = append(projectionList, Customer.CreateDate)
	}
}

if request.ShowFullName {
	projectionList = append(projectionList, Customer.FirstName.CONCAT(Customer.LastName))
}

stmt := SELECT(projectionList).
	FROM(Customer).
	LIMIT(3)

How to construct dynamic condition?

var request struct {
	CustomerID *int64
	Email      *string
	Active     *bool
}

// ....

condition := Bool(true)

if request.CustomerID != nil {
	condition = condition.AND(Customer.CustomerID.EQ(Int(*request.CustomerID)))
}
if request.Email != nil {
	condition = condition.AND(Customer.Email.EQ(String(*request.Email)))
}
if request.Active != nil {
	condition = condition.AND(Customer.Activebool.EQ(Bool(*request.Active)))
}

stmt := SELECT(Customer.AllColumns).
	FROM(Customer).
	WHERE(condition)

How to use jet in multi-tenant environment?

multiTenant1 :=SELECT(Artist.AllColumns).
	FROM(Artists)                    // default schema/database "chinook"
	ORDER_BY(Artist.ArtistId).
	LIMIT(10)

Artist2 := Artist.FromSchema("chinook2") // the same generated SQL builder type used for different schema/database

multiTenant2 := 
	SELECT(Artist2.AllColumns).
	FROM(Artist2).
	ORDER_BY(Artist2.ArtistId).
	LIMIT(10)

How to work with exact decimal types?

Generator will by default represents, exact decimal types (DECIMAL and NUMERIC) as float64 fields in the model types. This can lead to loss of precision during query result mapping.

To overcome this issue, we need to create a new type to store decimal values, and then instruct generator to use this new type instead of default float64.

New type has to implement sql.Serializer and sql.Valuer interface.

type MoneyType int64  // or some other representation 

func (m *MoneyType) Scan(value interface{}) error {   // value is string 
 ...  add implementation
}

func (m MoneyType) Value() (driver.Value, error) {
 ...  add implementation
}

Similar behavior can be achieved without customizing a generator. For instance, assuming table name is my_table, and my_table has a column money of the type NUMERIC.

We can create a new custom model type, by wrapping generated MyTable type.

type MyTable struct {
    model.MyTable         // MyTable.Money will contain float64 value
    Money MoneyType       // will contains exact decimal value
}

New MyTable type can be used in any place model.MyTable is used, as a QueryContext destination or as a model for INSERT or UPDATE statements.

It is also possible to use some of the existing third party decimal libraries:

import "github.com/shopspring/decimal"

type MyTable struct {
    model.MyTable
    Money decimal.Decimal  
}