sqx

package module
v0.6.0 Latest Latest
Warning

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

 Go to latest Published: Aug 2, 2024 License: MIT Imports: 8 Imported by: 0

README

Squirrel Xtended

Go Reference

Squirrel Xtended (sqx) is a convenient library for db interactions in go. It provides nice bindings around:

sqx is not an ORM or a migration tool. sqx just wants to run some SQL!

Links

Quick Start

Teach sqx where your DB handle and logger are. sqx can then be used to create, update, and delete data.

See Widget Test for an example of a complete data layer built with sqx.

package main

import (
	"context"
	"github.com/stytchauth/sqx"
)

func init() {
	db := getDatabase()
	log := getLogger()
	sqx.SetDefaultQueryable(db)
	sqx.SetDefaultLogger(log)
}

type User struct {
	ID          string `db:"id"`
	Email       string `db:"email"`
	PhoneNumber string `db:"phone_number"`
	Status      string `db:"status"`
}

func InsertUser(ctx context.Context, user *User) error {
	return sqx.Write(ctx).
		Insert("users").
		SetMap(sqx.ToSetMap(user)).
		Do()
}

type GetUserFilter struct {
	ID          *string `db:"id"`
	Email       *string `db:"email"`
	PhoneNumber *string `db:"phone_number"`
	Status      *string `db:"status"`
}

func GetUsers(ctx context.Context, filter GetUserFilter) ([]User, error) {
	return sqx.Read[User](ctx).
		Select("*").
		From("users").
		Where(sqx.ToClause(filter)).
		All()
}

func DeleteUser(ctx context.Context, userID string) error {
	return sqx.Write(ctx).
		Delete("users").
		Where(sqx.Eq{"ID": userID}).
		Do()
}
Core Concepts
Query building

sqx is a superset of Masterminds/squirrel - refer to their docs for information on what query methods are available. We will try to add more examples over time - if there is an example you'd love to see, feel free to open an issue or a PR!

Reading data

Call sqx.Read[T](ctx).Select(columNames...) to start building a read transaction. When the read transaction is ran, sqx will provision an object of type T and scan the results into the object. Scanning is accomplished using db tags defined on T. All scanning is handled by blockloop/scan's RowsStrict method. Read transactions can be ran in several ways:

  • func (b SelectBuilder[T]) One() (*T, error) - reads a single struct of type T. If no response is found, returns a sql.ErrNoRows. If more than one row is returned from the underlying query, an error will be logged to the provided logger.
  • func (b SelectBuilder[T]) OneStrict() (*T, error) - like One() but returns an error if more than one row is returned
  • func (b SelectBuilder[T]) OneScalar() (T, error) - like One() but can be used to read simple values like int32 or string
  • func (b SelectBuilder[T]) First() (*T, error) - line One() but does not care if the underlying query has more than one result and will just take the first row. NOTE: if you don't supply an OrderBy clause, the first result is not guaranteed to be the same each time you run the query.
  • func (b SelectBuilder[T]) FirstScalar() (T, error) - line First() but can be used to read simple values like int32 or string
  • func (b SelectBuilder[T]) All() ([]T, error) - returns a slice of structs of type T

You'll often want to filter the data that you read - for example, finding all Users with a certain status, or finding a User with a specific ID. sqx.ToClause is helpful for converting flexible structs into Where-compatible filters. nil-valued fields are ignored, and only present fields are preserved.

For example, the following struct definition can be used to find users with a specific ID, a specific Email, a specific PhoneNumber, or any combination thereof.

type GetUserFilter struct {
	ID          *string `db:"id"`
	Email       *string `db:"email"`
	PhoneNumber *string `db:"phone_number"`
}
Clause Output
sqx.ToClause(GetUserFilter{ID: sqx.Ptr("123")}) sqx.Eq{"id": "123"}
sqx.ToClause(GetUserFilter{Email: sqx.Ptr("joe@example.com")}) sqx.Eq{"email": "joe@example.com"}
sqx.ToClause(GetUserFilter{ID: sqx.Ptr("123"), Email: sqx.Ptr("joe@example.com")}) sqx.Eq{"id": "123", "email": "joe@example.com"} 
func GetUsers(ctx context.Context, filter GetUserFilter) ([]User, error) {
	return sqx.Read[User](ctx).
		Select("*").
		From("users").
		Where(sqx.ToClause(filter)).
		All()
}

If you are joining tables together and aliasing them along the way, sqx.ToClauseAlias can help with that.

func GetUsersAndProfileData(ctx context.Context, filter GetUserFilter) ([]User, error) {
	return sqx.Read[UserWithPets](ctx).
		Select("*").
		From("users u").
		Join("pets p ON users.id = pets.user_id")
		Where(sqx.ToClauseAlias("u", filter)).
		All()
}

You can also define the alias directly in the struct tag

type GetUserWithPetFilter struct {
	UserID *string `db:"u.id"`
	PetID  *string `db:"p.id"`
}
Writing data

Call sqx.Write(ctx) to start building a write transaction. Write transactions can be used for Create, Update, or Delete operations. All write transactions are ran by calling .Do() after being built.

Create and Update transactions require fields to be set. Fields may be set one at a time via calls to .Set(fieldName string, fieldValue any) but the preferred way is via .SetMap(map[string]any). The method sqx.ToSetMap is useful for converting flexible structs into maps. As with ToClause, nil-valued fields are ignored, and only present fields are passed through.

For example, the following structs define a user that can be created once, then updated any number of times. The UserUpdate struct can be used to update a user's email, phone number, status, or multiple at once.

type User struct {
	ID          string `db:"id"`
	Email       string `db:"email"`
	PhoneNumber string `db:"phone_number"`
	Status      string `db:"status"`
}
type UserUpdate struct {
	Email       *string `db:"email"`
	PhoneNumber *string `db:"phone_number"`
	Status      *string `db:"status"`
}
Input Output
sqx.ToSetMap(User{ID:"123", Email:"joe@example.com"}) map[string]any{"id":"123", "email":"joe@example.com", "phone_number": "", "status":""}
sqx.ToSetMap(UserUpdate{ID:sqx.Ptr("123"), Email:sqx.Email("joe@example.com")}) map[string]any{"id":"123", "email":"joe@example.com"} 
func InsertUser(ctx context.Context, user *User) error {
	return sqx.Write(ctx).
		Insert("users").
		SetMap(sqx.ToSetMap(user)).
		Do()
}

func UpdateUser(ctx context.Context, userID string, update *UserUpdate) error {
	return sqx.Write(ctx).
		Update("users").
		Where(sqx.Eq{"id": userID}).
		SetMap(sqx.ToSetMap(update)).
		Do()
}

--

FAQ
What SQL dialects are supported?

sqx is actively tested against mysql. Since sqx is built on top of squirrel, it should support all SQL dialects squirrel supports. squirrel is tested against mysql, postgres, and sqlite.

Examples
Reading a single struct row
func GetUser(ctx context.Context, userID string) (*User, error) {
	return sqx.Read[User](ctx).
		Select("*").
		From("users").
		Where(sqx.Eq{"ID": userID}).
		One()
}
Reading a simple value (string, int, bool, etc)
func CountUsers(ctx context.Context, userID string) (int32, error) {
	return sqx.Read[int32](ctx).
		Select("COUNT(*)").
		From("users").
		OneScalar()
}
Reading a slice of structs
func GetAllUsers(ctx context.Context) ([]User, error) {
	return sqx.Read[User](ctx).
		Select("*").
		From("users").
		All()
}
Debugging generated SQL

Call .Debug() at any time to print out the internal state of the query builder

sqx.Read[UserWithPets](ctx).
	Select("*").
	From("users u").
	Debug().
	Join("pets p ON users.id = pets.user_id").
	Where(sqx.ToClauseAlias("u", filter)).
	Debug().
	All()
// outputs
// map[args:[] error:<nil> sql:SELECT * FROM users u]
// map[args:[poodle] error:<nil> sql:SELECT * FROM users u JOIN pets p ON users.id = pets.user_id WHERE u.breed = ?]
Setting a field to null using an Update

Use the sqx.Nullable[T] type and its helper methods - sqx.NewNullable and sqx.NewNull.

Given the update request:

type PetUpdate {
	UserID sqx.Nullable[string] `db:"user_id"`
}
func UpdatePets(ctx context.Context, petID string, petUpdate *PetUpdate) error {
	return sqx.Write(ctx).
		Update("pets").
		Where(sqx.Eq{"id": petID}).
		SetMap(sqx.ToClause(petUpdate)).
		Do()
}

This update will set the user_id field to the provided value

UpdatePets(ctx, &PetUpdate{
	UserID: sqx.NewNullable("some-user-id")
})

and this update will set the user_id field to NULL/nil

UpdatePets(ctx, &PetUpdate{
	UserID: sqx.NewNull[string]()
})
Validating data before inserting

InsertBuilder.SetMap() can take in an optional error. If an error occurs, the insert operation will short-circuit.

type Pet struct {
	Name string `db:"name"`
}
func (p *Pet) ToSetMap() (map[string]any, error) {
	if p.name == "" {
		return nil, fmt.Errorf("pet was missing name")		
	}
	return sqx.ToSetMap(p), nil
}

func CreatePet(ctx context.Context, pet *Pet) error {
	return sqx.Write(ctx).
		Insert("pets").
		SetMap(pet.ToSetMap()).
		Do()
}
Managing Transactions

sqx does not manage transactions itself. Create transactions within your application when needed, and then pass to WithQueryable to let the request builder know to use that transaction object. Both sql.DB and sql.Tx satisfy the sqx.Queryable interface.

func MyOperationThatNeedsATransaction(ctx context.Context) error {
	// Get a Tx for making transaction requests.
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	// Defer a rollback in case anything fails.
	defer tx.Rollback()
	
	err = OperationThatNeedsAQueryable(ctx, tx)
	if err != nil {
		return err
	}

	err = OperationThatNeedsAQueryable(ctx, tx)
	if err != nil {
		return err
	}
  
	return tx.Commit()
}

func OperationThatNeedsAQueryable(ctx context.Context, tx sqx.Queryable) error {
	return sqx.Write(ctx).
		WithQueryable(tx).
		Update("table").
		Set("key", "value").
		Do()
}
Customizing Handles & Loggers

Have multiple database handles or a per-request logger? You can override them using WithQueryable or WithLogger.

func GetUsersFromReadReplica(ctx context.Context, filter GetUserFilter) ([]User, error) {
	return sqx.Read[User](ctx).
		WithQueryable(replicaDB).
		WithLogger(logging.FromCtx(ctx))
		Select("*").
		From("users").
		Where(sqx.ToClause(filter)).
		All()
}

If you always want to pass in a custom handle or logger, consider aliasing the Read and Write entrypoints within your project.

func Read[T any](ctx context.Context, db sqx.Queryable) interface {
	Select(columns ...string) sqx.SelectBuilder[T]
} {
	return sqx.Read[T](ctx).WithQueryable(db).WithLogger(logging.FromContext(ctx))
}

func Write(ctx context.Context, db sqx.Queryable) interface {
	Insert(tblName string) sqx.InsertBuilder
	Update(tblName string) sqx.UpdateBuilder
	Delete(tblName string) sqx.DeleteBuilder
} {
	return sqx.Write(ctx).WithQueryable(db).WithLogger(logging.FromContext(ctx))
}
Why sqx?

sqx is made to operate in a sweet spot just slightly past "query builder", but well before "ORM". The closest analog for sqx is knex.js - a Node query builder with wonderful DX. sqx wants to eliminate boilerplate commonly found in DB IO operations based on Rob Pike's Errors are values essay.

Returning to our quick-start example, we see that sqx lets us create reusable DB query patterns with a minimal amount of boilerplate, while also not obscuring the SQL query that is generated. The following snippet shows a single function that can be ran in several different ways - to list all users in the table, to filter users by ID, or to filter by a number of other fields.

func GetUsers(ctx context.Context, filter GetUserFilter) ([]User, error) {
	return sqx.Read[User](ctx).
		Select("*").
		From("users").
		Where(sqx.ToClause(filter)).
		All()
}
sqx vs database/sql

Here's some sample code showing how someone might write the GetUsers function defined above using the stdlib. We want to avoid the manual management of errors + rows.Close + scanning boilerplate.

db, _ := sql.Open("mysql", "user:password...")
query := "SELECT id, email, phone_number, status FROM users"

rows, err := db.Query(query)
if err != nil {
	log.Fatal(err)
}
defer rows.Close()

// Loop through the rows and populate User structs
var users []User
for rows.Next() {
	var user User
	err := rows.Scan(&user.ID, &user.Email, &user.PhoneNumber, &user.Status)
	if err != nil {
		log.Fatal(err)
		continue
	}
	users = append(users, user)
}
sqx vs sqlx

sqlx builds on database/sql to reduce scanning boilerplate. However, SQL generation is still nontrivial. How would you modify the code below to support flexible filters? e.g.

  • find all users with a status of active
  • find all users with a specific phone number and a specific email address

We wanted the nice scanning attributes with the power and flexibility of a query builder.

db, _ := sqlx.Open("mysql", "user:password...")
// Define your SQL query
query := "SELECT id, email, phone_number, status FROM users"
// Execute the query and retrieve users
var users []User
err = db.Select(&users, query)
if err != nil {
	log.Fatal(err)
}
sqx vs gorm

gorm is a full-featured ORM library. That's great for some people and some projects! gorm certainly does a great job of removing boilerplate around common DB IO. That being said - some people are ORM people and some people aren't. If you value full control over what SQL is being run, a query builder based approach is going to be friendlier than an ORM based approach.

var users []User
if err := db.Table("users").Find(&users).Error; err != nil {
	log.Fatal(err)
}
Contributing

sqx uses mysql@8.1.0 in a docker file for development and testing. It is hardcoded to run on port 4306

Start it with

make services

and kill it with

make services-stop

Run all tests with

make tests

Documentation

Overview

Package sqx is a convenient library for db interactions in go. It provides nice bindings around fluent SQL query building, scanning, and generics

Index

Examples

Constants

View Source 
const VERSION = "0.6.0"

Variables

View Source 
var NoDBTagsError = errors.New("No db tags detected")

Functions

func ContainsUpdates 

func ContainsUpdates(v any, excluded ...string) bool

ContainsUpdates returns true if an update filter is nonempty. This function panics if v is not a pointer to a struct.

Example 
package main

import (
	"fmt"

	"github.com/stytchauth/sqx"
)

func main() {
	type filter struct {
		Value  *string   `db:"first_col"`
		Values *[]string `db:"second_col"`
	}
	first := sqx.ContainsUpdates(&filter{
		Value:  sqx.Ptr("example"),
		Values: &[]string{"a", "b"},
	})
	second := sqx.ContainsUpdates(&filter{ /* Empty! */ })
	fmt.Printf("first: %t second: %t", first, second)

}

Output:

first: true second: false

func Ptr 

func Ptr[T any](t T) *T

Ptr is a convenience method for converting inline constants into pointers for use with ToClause and ToSetMap

Example 
type dogFilter struct {
	Breed      *string `db:"breed"`
	PlaysFetch *bool   `db:"plays_fetch"`
}
clause := ToClause(&dogFilter{
	Breed: Ptr("husky"),
})
sql, args, _ := clause.ToSql()
fmt.Printf("%s, %s", sql, args)

Output:

breed = ?, [husky]

func Read 

func Read[T any](ctx context.Context) typedRunCtx[T]

Read is the entrypoint for creating generic Select builders

func SetDefaultLogger 

func SetDefaultLogger(logger Logger)

SetDefaultLogger sets the logger that should be used to log information. If you need to change the logger for a specific request, use WithLogger

func SetDefaultQueryable 

func SetDefaultQueryable(queryable Queryable)

SetDefaultQueryable sets the DB query handler that should be used to run requests. If you need to change the DB query handler for a specific request, use WithQueryable

func ToSetMap 

func ToSetMap(v any, excluded ...string) (map[string]any, error)

ToSetMap converts a struct into a map[string]any based on the presence of "db" struct tags Nil values are skipped over automatically Add fields to the "excluded" arg to exclude them from the row

Example 
package main

import (
	"encoding/json"
	"fmt"

	"github.com/stytchauth/sqx"
)

func main() {
	type filter struct {
		Value  *string   `db:"first_col"`
		Values *[]string `db:"second_col"`
	}
	setMap, _ := sqx.ToSetMap(&filter{
		Value:  sqx.Ptr("example"),
		Values: &[]string{"a", "b"},
	})

	out, _ := json.Marshal(setMap)
	fmt.Print(string(out))

}

Output:

{"first_col":"example","second_col":["a","b"]}

func ToSetMapAlias 

func ToSetMapAlias(tableName string, v any, excluded ...string) (map[string]any, error)

ToSetMapAlias is like ToSetMap, but takes in a table alias

func TypedWrite added in v0.5.0 

func TypedWrite[T any](ctx context.Context) typedRunCtx[T]

func Write 

func Write(ctx context.Context) runCtx

Write is the entrypoint for creating sql-extra builders that call ExecCtx and its variants - it does not have a generic b/c Exec cannot return arbitrary data

Types

type And 

type And = sq.And

And represents a SQL AND expression. It is a re-export of squirrel.And.

type Clause 

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

Clause stores an Eq result, but also holds an error if one occurred during the conversion. You may think of this struct as a (Eq, error) tuple that implements the Sqlizer interface.

func ToClause 

func ToClause(v any, excluded ...string) *Clause

ToClause converts a filter interface to a SQL Where clause by introspecting its db tags

Example 
type filter struct {
	Value  *string   `db:"first_col"`
	Values *[]string `db:"second_col"`
}
clause := ToClause(&filter{
	Value:  Ptr("example"),
	Values: &[]string{"a", "b"},
})
sql, args, _ := clause.ToSql()
fmt.Printf("%s, %s", sql, args)

Output:

first_col = ? AND second_col IN (?,?), [example a b]

func ToClauseAlias 

func ToClauseAlias(tableName string, v any, excluded ...string) *Clause

ToClauseAlias is like ToClause, but takes in a table alias

func (*Clause) ToSql 

func (c *Clause) ToSql() (string, []interface{}, error)

ToSql calls the underlying Eq's ToSql method, but returns the error if one occurred when the Clause was constructed.

type DeleteBuilder 

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

DeleteBuilder wraps squirrel.DeleteBuilder and adds syntactic sugar for common usage patterns.

func (DeleteBuilder) Debug 

func (b DeleteBuilder) Debug() DeleteBuilder

Debug prints the DeleteBuilder state out to the provided logger

func (DeleteBuilder) Do 

func (b DeleteBuilder) Do() error

Do executes the DeleteBuilder

func (DeleteBuilder) DoResult added in v0.2.0 

func (b DeleteBuilder) DoResult() (sql.Result, error)

DoResult executes the DeleteBuilder and also returns the sql.Result for a successful query. This is useful if you wish to check the value of the LastInsertId() or RowsAffected() methods since Do() will discard this information.

func (DeleteBuilder) From 

func (b DeleteBuilder) From(from string) DeleteBuilder

From sets the table to be deleted from.

func (DeleteBuilder) Limit 

func (b DeleteBuilder) Limit(limit uint64) DeleteBuilder

Limit sets a LIMIT clause on the query.

func (DeleteBuilder) Offset 

func (b DeleteBuilder) Offset(offset uint64) DeleteBuilder

Offset sets a OFFSET clause on the query.

func (DeleteBuilder) OrderBy 

func (b DeleteBuilder) OrderBy(orderBys ...string) DeleteBuilder

OrderBy adds ORDER BY expressions to the query.

func (DeleteBuilder) Prefix 

func (b DeleteBuilder) Prefix(sql string, args ...interface{}) DeleteBuilder

Prefix adds an expression to the beginning of the query

func (DeleteBuilder) PrefixExpr 

func (b DeleteBuilder) PrefixExpr(expr Sqlizer) DeleteBuilder

PrefixExpr adds an expression to the very beginning of the query

func (DeleteBuilder) Suffix 

func (b DeleteBuilder) Suffix(sql string, args ...interface{}) DeleteBuilder

Suffix adds an expression to the end of the query

func (DeleteBuilder) Where 

func (b DeleteBuilder) Where(pred interface{}, rest ...interface{}) DeleteBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

func (DeleteBuilder) WithLogger 

func (b DeleteBuilder) WithLogger(logger Logger) DeleteBuilder

WithLogger configures a Queryable for this DeleteBuilder instance

func (DeleteBuilder) WithQueryable 

func (b DeleteBuilder) WithQueryable(queryable Queryable) DeleteBuilder

WithQueryable configures a Queryable for this DeleteBuilder instance

type EmptyResult added in v0.2.0 

type EmptyResult struct{}

EmptyResult represents a result with no rows affected. This is used for an UpdateBuilder that has no pending changes since the query would be a noop.

func (EmptyResult) LastInsertId added in v0.2.0 

func (e EmptyResult) LastInsertId() (int64, error)

func (EmptyResult) RowsAffected added in v0.2.0 

func (e EmptyResult) RowsAffected() (int64, error)

type Eq 

type Eq = sq.Eq

Eq represents a SQL equality = expression. It is a re-export of squirrel.Eq.

type ErrTooManyRows 

type ErrTooManyRows struct {
	Expected int
	Actual   int
}

ErrTooManyRows indicates that a query returned more rows than expected. This is used in calls to OneStrict() which expects a single row to be returned. In Strict mode, this error is raised if the number of rows returned is not equal to the expected number. If you received this error in your code and didn't expect it, check out the One() or First() methods instead.

func (ErrTooManyRows) Error 

func (e ErrTooManyRows) Error() string

type Gt added in v0.3.0 

type Gt = sq.Gt

Gt represents a SQL > expression. It is a re-export of squirrel.Gt.

type GtOrEq added in v0.3.0 

type GtOrEq = sq.GtOrEq

GtOrEq represents a SQL >= expression. It is a re-export of squirrel.GtOrEq.

type ILike added in v0.6.0 

type ILike = sq.ILike

ILike represents a SQL ILIKE expression. It is a re-export of squirrel.ILike.

type InsertBuilder 

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

InsertBuilder wraps squirrel.InsertBuilder and adds syntactic sugar for common usage patterns.

func (InsertBuilder) Columns 

func (b InsertBuilder) Columns(columns ...string) InsertBuilder

Columns adds insert columns to the query.

func (InsertBuilder) Debug 

func (b InsertBuilder) Debug() InsertBuilder

Debug prints the InsertBuilder state out to the provided logger

func (InsertBuilder) Do 

func (b InsertBuilder) Do() error

Do executes the InsertBuilder

func (InsertBuilder) DoResult added in v0.2.0 

func (b InsertBuilder) DoResult() (sql.Result, error)

DoResult executes the InsertBuilder and also returns the sql.Result for a successful query. This is useful if you wish to check the value of the LastInsertId() or RowsAffected() methods since Do() will discard this information.

func (InsertBuilder) Options 

func (b InsertBuilder) Options(options ...string) InsertBuilder

Options adds keyword options before the INTO clause of the query.

func (InsertBuilder) Prefix 

func (b InsertBuilder) Prefix(sql string, args ...interface{}) InsertBuilder

Prefix adds an expression to the beginning of the query

func (InsertBuilder) PrefixExpr 

func (b InsertBuilder) PrefixExpr(expr Sqlizer) InsertBuilder

PrefixExpr adds an expression to the very beginning of the query

func (InsertBuilder) SetMap 

func (b InsertBuilder) SetMap(clauses map[string]interface{}, errors ...error) InsertBuilder

SetMap set columns and values for insert builder from a map of column name and value note that it will reset all previous columns and values was set if any

func (InsertBuilder) Suffix 

func (b InsertBuilder) Suffix(sql string, args ...interface{}) InsertBuilder

Suffix adds an expression to the end of the query

func (InsertBuilder) SuffixExpr 

func (b InsertBuilder) SuffixExpr(expr Sqlizer) InsertBuilder

SuffixExpr adds an expression to the end of the query

func (InsertBuilder) Values 

func (b InsertBuilder) Values(values ...any) InsertBuilder

Values adds a single row's values to the query.

func (InsertBuilder) WithLogger 

func (b InsertBuilder) WithLogger(logger Logger) InsertBuilder

WithLogger configures a Queryable for this InsertBuilder instance

func (InsertBuilder) WithQueryable 

func (b InsertBuilder) WithQueryable(queryable Queryable) InsertBuilder

WithQueryable configures a Queryable for this InsertBuilder instance

type InsertManyBuilder added in v0.5.0 

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

InsertManyBuilder wraps squirrel.InsertBuilder and adds syntactic sugar for common usage patterns. This is a special case that includes a type constraint since generic methods are not supported in Go. In order to implement FromItems, we need a type constraint, but this is only possible if the struct itself is also generic. As such, the InsertManyBuilder is more constrained than InsertBuilder, but this is by design since *most* use cases should prefer the InsertBuilder unless they explicitly want to use FromItems.

func (InsertManyBuilder[T]) Columns added in v0.5.0 

func (b InsertManyBuilder[T]) Columns(columns ...string) InsertManyBuilder[T]

Columns adds insert columns to the query.

func (InsertManyBuilder[T]) Debug added in v0.5.0 

func (b InsertManyBuilder[T]) Debug() InsertManyBuilder[T]

Debug prints the InsertManyBuilder state out to the provided logger

func (InsertManyBuilder[T]) Do added in v0.5.0 

func (b InsertManyBuilder[T]) Do() error

Do executes the InsertManyBuilder

func (InsertManyBuilder[T]) DoResult added in v0.5.0 

func (b InsertManyBuilder[T]) DoResult() (sql.Result, error)

DoResult executes the InsertManyBuilder and also returns the sql.Result for a successful query. This is useful if you wish to check the value of the LastInsertId() or RowsAffected() methods since Do() will discard this information.

func (InsertManyBuilder[T]) FromItems added in v0.5.0 

func (b InsertManyBuilder[T]) FromItems(items []T, excluded ...string) InsertManyBuilder[T]

FromItems generates an InsertManyBuilder from a slice of items. The first item in the slice is used to determine the columns for the insert statement. If excluded columns are provided, they will be removed from the list of columns. All items should be of the same type.

func (InsertManyBuilder[T]) Values added in v0.5.0 

func (b InsertManyBuilder[T]) Values(values ...any) InsertManyBuilder[T]

Values adds a single row's values to the query.

func (InsertManyBuilder[T]) WithLogger added in v0.5.0 

func (b InsertManyBuilder[T]) WithLogger(logger Logger) InsertManyBuilder[T]

WithLogger configures a Queryable for this InsertManyBuilder instance

func (InsertManyBuilder[T]) WithQueryable added in v0.5.0 

func (b InsertManyBuilder[T]) WithQueryable(queryable Queryable) InsertManyBuilder[T]

WithQueryable configures a Queryable for this InsertManyBuilder instance

type Like added in v0.6.0 

type Like = sq.Like

Like represents a SQL LIKE expression. It is a re-export of squirrel.Like.

type Logger 

type Logger interface {
	// Printf prints output using the provided logger
	// Arguments are passed in the style of fmt.Printf.
	Printf(format string, v ...any)
}

Logger is a simple interface that can be used to log events in the sqx package. It contains a single Printf function that takes a format string and arguments, much like fmt.Printf.

func MakeLogger 

func MakeLogger(printf func(format string, v ...any)) Logger

MakeLogger creates a new Logger interface by using the provided function as the logger's printf function.

type Lt added in v0.3.0 

type Lt = sq.Lt

Lt represents a SQL < expression. It is a re-export of squirrel.Lt.

type LtOrEq added in v0.3.0 

type LtOrEq = sq.LtOrEq

LtOrEq represents a SQL <= expression. It is a re-export of squirrel.LtOrEq.

type NotEq 

type NotEq = sq.NotEq

NotEq represents a SQL inequality <> expression. It is a re-export of squirrel.NotEq.

type NotILike added in v0.6.0 

type NotILike = sq.NotILike

NotILike represents a SQL NOT ILIKE expression. It is a re-export of squirrel.NotILike.

type NotLike added in v0.6.0 

type NotLike = sq.NotLike

NotLike represents a SQL NOT LIKE expression. It is a re-export of squirrel.NotLike.

type Nullable 

type Nullable[T any] **T

func NewNull 

func NewNull[T any]() Nullable[T]

NewNull creates a Nullable[T] from a provided value use it to set nullable fields in Update calls to a null value

Example 
type updateFilter struct {
	Field2 Nullable[string] `db:"field_2"`
}
sm, _ := ToSetMap(&updateFilter{
	Field2: NewNull[string](),
})

fmt.Printf("setting field_2 to %v", reflect.ValueOf(sm["field_2"]).Elem().Interface())

Output:

setting field_2 to <nil>

func NewNullable 

func NewNullable[T any](t T) Nullable[T]

NewNullable creates a Nullable[T] from a provided value use it to set nullable fields in Update calls to a concrete value

Example 
type updateFilter struct {
	Field1 Nullable[int] `db:"field_1"`
}
sm, _ := ToSetMap(&updateFilter{
	Field1: NewNullable(1),
})

fmt.Printf("setting field_1 to %v", reflect.ValueOf(sm["field_1"]).Elem().Elem().Interface())

Output:

setting field_1 to 1

type Or 

type Or = sq.Or

Or represents a SQL OR expression. It is a re-export of squirrel.Or.

type Queryable 

type Queryable interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Queryable is an interface wrapping common database access methods.

This is useful in cases where it doesn't matter whether the database handle is the root handle (*sql.DB) or an already-open transaction (*sql.Tx).

type SelectBuilder 

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

SelectBuilder wraps squirrel.SelectBuilder and adds syntactic sugar for common usage patterns.

func (SelectBuilder[T]) All 

func (b SelectBuilder[T]) All() ([]T, error)

All returns all results from the query as a slice of T.

func (SelectBuilder[T]) Column 

func (b SelectBuilder[T]) Column(column any, args ...any) SelectBuilder[T]

Column adds a result column to the query. Unlike Columns, Column accepts args which will be bound to placeholders in the columns string, for example: 

Column("IF(col IN ("+squirrel.Placeholders(3)+"), 1, 0) as col", 1, 2, 3)

func (SelectBuilder[T]) Columns 

func (b SelectBuilder[T]) Columns(columns ...string) SelectBuilder[T]

Columns adds result columns to the query.

func (SelectBuilder[T]) CrossJoin 

func (b SelectBuilder[T]) CrossJoin(join string, rest ...interface{}) SelectBuilder[T]

CrossJoin adds a CROSS JOIN clause to the query.

func (SelectBuilder[T]) Debug 

func (b SelectBuilder[T]) Debug() SelectBuilder[T]

Debug prints the SQL query using the builder's logger and then returns b, unmodified. If the builder has no logger set (and SetDefaultLogger has not been called), then log.Printf is used instead.

func (SelectBuilder[T]) Distinct 

func (b SelectBuilder[T]) Distinct() SelectBuilder[T]

Distinct adds a DISTINCT clause to the query.

func (SelectBuilder[T]) First 

func (b SelectBuilder[T]) First() (*T, error)

First returns the first result from the query, or an error if there was a problem. This is useful for queries that are expected to return more than one result, but you only care about the first one. Note that if you haven't added an ORDER BY clause to your query, the first result is not guaranteed to be the same each time you run the query.

func (SelectBuilder[T]) FirstScalar 

func (b SelectBuilder[T]) FirstScalar() (T, error)

FirstScalar is like First but dereferences the result into a scalar value. If an error is raised, the scalar value will be the zero value of the type.

func (SelectBuilder[T]) From 

func (b SelectBuilder[T]) From(from string) SelectBuilder[T]

From sets the FROM clause of the query.

func (SelectBuilder[T]) FromSelect 

func (b SelectBuilder[T]) FromSelect(from SelectBuilder[T], alias string) SelectBuilder[T]

FromSelect sets a subquery into the FROM clause of the query.

func (SelectBuilder[T]) GroupBy 

func (b SelectBuilder[T]) GroupBy(groupBys ...string) SelectBuilder[T]

GroupBy adds GROUP BY expressions to the query.

func (SelectBuilder[T]) Having 

func (b SelectBuilder[T]) Having(pred interface{}, rest ...interface{}) SelectBuilder[T]

Having adds an expression to the HAVING clause of the query.

See Where.

func (SelectBuilder[T]) InnerJoin 

func (b SelectBuilder[T]) InnerJoin(join string, rest ...interface{}) SelectBuilder[T]

InnerJoin adds a INNER JOIN clause to the query.

func (SelectBuilder[T]) Join 

func (b SelectBuilder[T]) Join(join string, rest ...interface{}) SelectBuilder[T]

Join adds a JOIN clause to the query.

func (SelectBuilder[T]) JoinClause 

func (b SelectBuilder[T]) JoinClause(pred interface{}, args ...interface{}) SelectBuilder[T]

JoinClause adds a join clause to the query.

func (SelectBuilder[T]) LeftJoin 

func (b SelectBuilder[T]) LeftJoin(join string, rest ...interface{}) SelectBuilder[T]

LeftJoin adds a LEFT JOIN clause to the query.

func (SelectBuilder[T]) Limit 

func (b SelectBuilder[T]) Limit(limit uint64) SelectBuilder[T]

Limit sets a LIMIT clause on the query.

func (SelectBuilder[T]) Offset 

func (b SelectBuilder[T]) Offset(offset uint64) SelectBuilder[T]

Offset sets a OFFSET clause on the query.

func (SelectBuilder[T]) One 

func (b SelectBuilder[T]) One() (*T, error)

One returns a single result from the query, or an error if there was a problem. This runs in "non-strict" mode which means that if the underlying query returns more than one row, a warning is logged but no error is raised. If you want to raise an error if the underlying query returns more than one result, use OneStrict. If you instead expect that more than one result may be returned and this is not cause for concern, use First.

func (SelectBuilder[T]) OneScalar 

func (b SelectBuilder[T]) OneScalar() (T, error)

OneScalar is like One but dereferences the result into a scalar value. If an error is raised, the scalar value will be the zero value of the type.

func (SelectBuilder[T]) OneScalarStrict 

func (b SelectBuilder[T]) OneScalarStrict() (T, error)

OneScalarStrict is like OneStrict but dereferences the result into a scalar value. If an error is raised, the scalar value will be the zero value of the type.

func (SelectBuilder[T]) OneStrict 

func (b SelectBuilder[T]) OneStrict() (*T, error)

OneStrict returns a single result from the query, or an error if there was a problem. This runs in "strict" mode which means that if the underlying query returns more than one row, an error is raised. You may instead use One to downgrade this error into a warning from the saved logger, or First to return the first result for cases where you expect more than one result can be returned from the underlying query and this is not cause for concern.

func (SelectBuilder[T]) Options 

func (b SelectBuilder[T]) Options(options ...string) SelectBuilder[T]

Options adds select option to the query

func (SelectBuilder[T]) OrderBy 

func (b SelectBuilder[T]) OrderBy(orderBys ...string) SelectBuilder[T]

OrderBy adds ORDER BY expressions to the query.

func (SelectBuilder[T]) OrderByClause 

func (b SelectBuilder[T]) OrderByClause(pred interface{}, args ...interface{}) SelectBuilder[T]

OrderByClause adds ORDER BY clause to the query.

func (SelectBuilder[T]) Prefix 

func (b SelectBuilder[T]) Prefix(sql string, args ...interface{}) SelectBuilder[T]

Prefix adds an expression to the beginning of the query

func (SelectBuilder[T]) PrefixExpr 

func (b SelectBuilder[T]) PrefixExpr(expr Sqlizer) SelectBuilder[T]

PrefixExpr adds an expression to the very beginning of the query

func (SelectBuilder[T]) RemoveColumns 

func (b SelectBuilder[T]) RemoveColumns() SelectBuilder[T]

RemoveColumns remove all columns from query. Must add a new column with Column or Columns methods, otherwise return a error.

func (SelectBuilder[T]) RemoveLimit 

func (b SelectBuilder[T]) RemoveLimit() SelectBuilder[T]

RemoveLimit removes LIMIT clause

func (SelectBuilder[T]) RemoveOffset 

func (b SelectBuilder[T]) RemoveOffset() SelectBuilder[T]

RemoveOffset removes OFFSET clause.

func (SelectBuilder[T]) RightJoin 

func (b SelectBuilder[T]) RightJoin(join string, rest ...interface{}) SelectBuilder[T]

RightJoin adds a RIGHT JOIN clause to the query.

func (SelectBuilder[T]) Suffix 

func (b SelectBuilder[T]) Suffix(sql string, rest ...interface{}) SelectBuilder[T]

Suffix adds an expression to the end of the query

func (SelectBuilder[T]) SuffixExpr 

func (b SelectBuilder[T]) SuffixExpr(expr Sqlizer) SelectBuilder[T]

SuffixExpr adds an expression to the end of the query

func (SelectBuilder[T]) UnionAll 

func (b SelectBuilder[T]) UnionAll(other SelectBuilder[T]) SelectBuilder[T]

UnionAll adds a UNION ALL clause to the query from another SelectBuilder of the same type.

func (SelectBuilder[T]) Where 

func (b SelectBuilder[T]) Where(pred interface{}, rest ...interface{}) SelectBuilder[T]

Where adds an expression to the WHERE clause of the query.

Expressions are ANDed together in the generated SQL.

Where accepts several types for its pred argument:

nil OR "" - ignored.

string - SQL expression. If the expression has SQL placeholders then a set of arguments must be passed as well, one for each placeholder.

map[string]interface{} OR Eq - map of SQL expressions to values. Each key is transformed into an expression like "<key> = ?", with the corresponding value bound to the placeholder. If the value is nil, the expression will be "<key> IS NULL". If the value is an array or slice, the expression will be "<key> IN (?,?,...)", with one placeholder for each item in the value. These expressions are ANDed together.

Where will panic if pred isn't any of the above types.

func (SelectBuilder[T]) WithLogger 

func (b SelectBuilder[T]) WithLogger(logger Logger) SelectBuilder[T]

WithLogger configures a Queryable for this SelectBuilder instance

func (SelectBuilder[T]) WithQueryable 

func (b SelectBuilder[T]) WithQueryable(queryable Queryable) SelectBuilder[T]

WithQueryable configures a Queryable for this SelectBuilder instance

type Sqlizer 

type Sqlizer = sq.Sqlizer

Sqlizer is an interface containing the ToSql method. It is a re-export of squirrel.Sqlizer.

type UpdateBuilder 

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

UpdateBuilder wraps squirrel.UpdateBuilder and adds syntactic sugar for common usage patterns.

func (UpdateBuilder) CrossJoin 

func (b UpdateBuilder) CrossJoin(join string, rest ...interface{}) UpdateBuilder

CrossJoin adds a CROSS JOIN clause to the query.

func (UpdateBuilder) Debug 

func (b UpdateBuilder) Debug() UpdateBuilder

Debug prints the UpdateBuilder state out to the provided logger

func (UpdateBuilder) Do 

func (b UpdateBuilder) Do() error

Do executes the UpdateBuilder

func (UpdateBuilder) DoResult added in v0.2.0 

func (b UpdateBuilder) DoResult() (sql.Result, error)

DoResult executes the InsertBuilder and also returns the sql.Result for a successful query. This is useful if you wish to check the value of the LastInsertId() or RowsAffected() methods since Do() will discard this information.

func (UpdateBuilder) InnerJoin 

func (b UpdateBuilder) InnerJoin(join string, rest ...interface{}) UpdateBuilder

InnerJoin adds a INNER JOIN clause to the query.

func (UpdateBuilder) Join 

func (b UpdateBuilder) Join(join string, rest ...interface{}) UpdateBuilder

Join adds a JOIN clause to the query.

func (UpdateBuilder) JoinClause 

func (b UpdateBuilder) JoinClause(pred interface{}, args ...interface{}) UpdateBuilder

JoinClause adds a join clause to the query.

func (UpdateBuilder) LeftJoin 

func (b UpdateBuilder) LeftJoin(join string, rest ...interface{}) UpdateBuilder

LeftJoin adds a LEFT JOIN clause to the query.

func (UpdateBuilder) Limit 

func (b UpdateBuilder) Limit(limit uint64) UpdateBuilder

Limit sets a LIMIT clause on the query.

func (UpdateBuilder) Offset 

func (b UpdateBuilder) Offset(offset uint64) UpdateBuilder

Offset sets a OFFSET clause on the query.

func (UpdateBuilder) OrderBy 

func (b UpdateBuilder) OrderBy(orderBys ...string) UpdateBuilder

OrderBy adds ORDER BY expressions to the query.

func (UpdateBuilder) Prefix 

func (b UpdateBuilder) Prefix(sql string, args ...interface{}) UpdateBuilder

Prefix adds an expression to the beginning of the query

func (UpdateBuilder) PrefixExpr 

func (b UpdateBuilder) PrefixExpr(expr Sqlizer) UpdateBuilder

PrefixExpr adds an expression to the very beginning of the query

func (UpdateBuilder) RightJoin 

func (b UpdateBuilder) RightJoin(join string, rest ...interface{}) UpdateBuilder

RightJoin adds a RIGHT JOIN clause to the query.

func (UpdateBuilder) Set 

func (b UpdateBuilder) Set(column string, value any) UpdateBuilder

Set adds SET clauses to the query.

func (UpdateBuilder) SetMap 

func (b UpdateBuilder) SetMap(clauses map[string]any, errors ...error) UpdateBuilder

SetMap is a convenience method which calls Set for each key/value pair in clauses.

func (UpdateBuilder) Suffix 

func (b UpdateBuilder) Suffix(sql string, args ...interface{}) UpdateBuilder

Suffix adds an expression to the end of the query

func (UpdateBuilder) SuffixExpr 

func (b UpdateBuilder) SuffixExpr(expr Sqlizer) UpdateBuilder

SuffixExpr adds an expression to the end of the query

func (UpdateBuilder) Where 

func (b UpdateBuilder) Where(pred interface{}, rest ...interface{}) UpdateBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

func (UpdateBuilder) WithLogger 

func (b UpdateBuilder) WithLogger(logger Logger) UpdateBuilder

WithLogger configures a Queryable for this UpdateBuilder instance

func (UpdateBuilder) WithQueryable 

func (b UpdateBuilder) WithQueryable(queryable Queryable) UpdateBuilder

WithQueryable configures a Queryable for this UpdateBuilder instance

Source Files

View all Source files

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.