Skip to content

FAQ

Jump to bottom
go-jet edited this page Nov 3, 2024 · 37 revisions

Contents

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
}
// ...

var 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?

SQL Builder default targeted schema can be changed using FromSchema method:

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)

Alternatively, each table and view default schema can be changed using global UseSchema method:

table.UseSchema("chinook2")
view.UseSchema("chinook2")

How to change model field type?

Generator customization

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.Scanner 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
}

Wrap generated types

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 Use Custom(or Currently Unsupported) Functions and Operators?

Although most database functions and operators are supported, some are still missing. The internal Jet functionalities are exposed, allowing developers to implement any missing features as needed.

Scalar Functions

Scalar functions return a single value of a specific data type (e.g., INTEGER, VARCHAR, DATE).

Let’s say our database contains following scalar returning function:

CREATE FUNCTION get_film_count(len_from int, len_to int) RETURNS int

Developer can define a utility function:

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

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"),
)

Set-Returning Functions

Set-returning functions return a set of rows rather than a single scalar value.

Let’s say our database contains following set-returning function:

CREATE FUNCTION dvds.film_in_stock(p_film_id integer, p_store_id integer, OUT p_film_count integer) RETURNS SETOF integer

To use this function as a table source in the FROM clause, developers must use raw statement wrapped in a CTE (Common Table Expression):

inventoryID := IntegerColumn("inventoryID")
filmsInStock := CTE("film_in_stock", inventoryID)

stmt := WITH(
	filmsInStock.AS(
		RawStatement("SELECT * FROM dvds.film_in_stock(#filmID, #storeID)",
			RawArgs{
				"#filmID":  1,
				"#storeID": 2,
			}),
	),
)(
	SELECT(
		Inventory.AllColumns,
	).FROM(Inventory.
		INNER_JOIN(filmsInStock, Inventory.InventoryID.EQ(inventoryID)),
	),
)

Operators

Similar to custom or unsupported functions, any operators can be defined as utility functions using CustomExpression and Token. For example, if our DBMS has a case-insensitive LIKE operator called ILIKE, we can define a utility function as follows:

func ILIKE(lhs, rhs StringExpression) BoolExpression {
	return BoolExp(CustomExpression(lhs, Token("ILIKE"), rhs))
}

And use it like so:

stmt := SELECT(
	ILIKE(String("FOOFoo"), String("foo%")).AS("foo_like"),
)

Raw Expression Fallback

If, for any reason, the above approaches do not work for you, the same effect can be achieved using the Raw expression:

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

Raw Statement Fallback

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

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

How to use IN/NOT_IN with dynamic list of values?

import (
	. "myapp/table"
	. "github.com/go-jet/jet/v2/sqlite"
)

func demo() {
	var userIDs = []int64{1, 2, 3}         // dynamic list, could be user provided
	var sqlIDs []Expression                // !!! sqlite.Expression list !!! 

	for _, userID := range userIDs {
		sqlIDs = append(sqlIDs, Int(userID))
	}

	SELECT(
		Users.AllColumns,
	).FROM(
		Users,
	).WHERE(
		Users.UserID.IN(sqlIDs...),
	)

	...
}

For tuple comparison use ROW method.

ROW(Users.UserID, Users.Name).IN(
    ROW(Int(1), String("John")),
    ROW(Int(2), String("Mike")),
)

Note that sqlIDs must be of type []Expression or the compilation will fail.
This is a bug, and it will be fixed in version V3. IN/NOT_IN right argument should match the type of the left argument.

Scan stopped working after naming a destination type

Usually developers start with a query that looks something like this:

stmt := SELECT(
	Payment.PaymentID,                    // no need to alias, "payment.payment_id" alias is added automatically
	MAX(Payment.Amount).AS("max_amount"), // alias equivalent to ".max_amount"
).FROM(
	Payment,
).WHERE(
	Payment.CustomerID.EQ(Int(101)),
).GROUP_BY(
	Payment.PaymentID,
)

var dest struct {
	model.Payment
	MaxAmount int
}

err := stmt.QueryContext(ctx, db, &dest)

This scan will work for MaxAmount field, because there is a valid mapping between alias and destination struct field.
Alias(.max_amount) => Field(.MaxAmount). Note that destination is anonymous type.

After naming the destination type, this mapping is broken.

type MyStruct struct {
	model.Payment
	MaxAmount int
}

var dest MyStruct

Alias(.max_amount) =| Field(MyStruct.MaxAmount)

Now, the scan will not work. To fix it we need to update query alias:

SELECT(
	Payment.PaymentID,                               // still no need to alias
	MAX(Payment.Amount).AS("my_struct.max_amount"),  // ".max_amount" -> "my_struct.max_amount"
).FROM(
 	Payment,
...

The same logic would apply if dest is slice of anonymous types.

Clone this wiki locally