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 custom column types?
• How to call custom or currently unsupported SQL function?

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 custom column 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  
}

How to call custom or currently unsupported SQL function?

Lets say our database contains this SQL function:

create function get_film_count(len_from int, len_to int)
...

Developer can define utility function:

func GET_FILM_COUNT(lenFrom, lenTo IntegerExpression) IntegerExpression { //or (lenFrom, lenTo int) if there is no need to pass a column as a parameter
	return IntExp(Func("dvds.get_film_count", lenFrom, lenTo))
}

And now, this new function can be called directly from the SQL query:

stmt := SELECT(
	GET_FILM_COUNT(Int(100), Int(120)).AS("film_count"),
)

The same affect can be achieved using Raw expression method:

stmt2 := SELECT(
	Raw("dvds.get_film_count(#1, #2)", RawArgs{"#1": 100, "#2": 120}).AS("film_count"),
)

With the loss of all jet benefits, the entire statement can also be written as a raw query, using RawStatement method:

stmt3 := RawStatement(`
	SELECT dvds.get_film_count(#1, #2) AS "film_count";`, RawArgs{"#1": 100, "#2": 120},
)