squint

package module
Version: v0.5.0 Latest Latest
Warning

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

Go to latest
Published: Jun 10, 2021 License: MIT Imports: 7 Imported by: 0

README

Squint - An interpolating SQL builder

Inspired by Perl's SQL::Interp

Overview

The database/sql package is very capable but somewhat tedious to use. You must hand-write full SQL queries with bind placeholders and provide a matching ordered list of variables. It's familiar, but inconvenient and repetitive. Squint makes things easier by allowing SQL and bind variables to be intermixed in their natural order. It also interpolates the variables into the proper bind placeholders and values, including complex types like structs and maps. Squint is not an ORM, though. It's merely a pleasant query building assistant.

Builder

The Builder is the heart of Squint. It accepts a list of SQL fragments and variables and returns the resulting SQL statement and bind variables.

import "github.com/mwblythe/squint"

// a simple example

idList := []int{10, 20, 30}
b := squint.NewBuilder()
sql, binds := b.Build("select * from users where id in", idList, "and active = 1")
rows, err := db.Query(sql, binds...)

The sql and binds returned are ready to pass into the standard database/sql query functions, such as Exec, Query and QueryRow. (See Bridge for an easier way)

Basic Variables

A string by default is treated as SQL. Other variables of the basic types will transform into a SQL bind placeholder (?) and an accompanying bind value. To have a string treated as a bind variable, you can pass a reference (&myString) or cast it as squint.Bind(myString).

// treated as binds
b.Build("select * from users where id =", 10) // good
b.Build("select * from users where is_active =", true) // good

// strings are sql without special handling
name := "Frank"
b.Build("select * from users where name =", name) // bad
b.Build("select * from users where name =", &name) // good
b.Build("select * from users where name =", squint.Bind(name)) // good
Arrays and Slices

By default, arrays and slices will be flattened and treated as if their contents had been passed inline. However, as part of a SQL IN clause, they will be expanded into appropriate placeholders and binds (even strings).

bits := []string{"select *", "from", "crew"}
b.Build(bits, "where id =", 10) // slice is flattened

names := []string{"jim", "spock", "uhura"}
b.Build("select * from crew where name in", names) // magic

Note that only arrays of basic types are supported for IN clause.

Structs and Maps

By default, these will be expanded in the style of a WHERE clause (column = ?) and joined with AND.

// easily build where clauses from structs
type Query struct {
  Origin     string
  Completed  bool
}

q := Query{Origin: "online", Completed: false}
b.Build("select * from orders where", q)

// or maps
type M map[string]interface{}
q := M{"origin": "online", "completed": false }
b.Build("select * from orders where", q)

There is special handling for INSERT statements:

// simple user struct
type User struct {
  Id   int
  Name string
}

// fill structure, likely from user input
newUser := User{10, "Frank"}

// build our query
b.Build("insert into users", newUser)

// multi-row insert
users := []User{
  { 10, "Frank"},
  { 20, "Hank"},
}

b.Build("insert into users", users)

// also handles maps
type Item map[string]interface{}
b.Build("insert into users", Item{"id": 10, "name": "Frank"})

Also for UPDATE statements:

// use a structure
type Updates struct {
  Role       string
  Department string
}

updates := Updates{"captain", "housewares"}
b.Build("UPDATE user SET", updates, "WHERE id =", id)

// or a map
type Updates map[string]interface{}
updates := Updates{"role": "captain", "department": "housewares"}
b.Build("UPDATE user SET", updates, "WHERE id =", id)
Pointers

Generally, pointers are dereferenced and their values used as if they were passed directly. If the pointer is nil, it will map to a NULL value. Pointers can be useful in a struct as discussed below with the NilValues option.

Conditions

When crafting a complex query, you sometimes need to build it up in bits while checking various conditions. Was an ID specified? Was extra information requested? While you can do this by carefully filling an array that you then pass to Build(), Squint has another option.

b.Build(
  "SELECT u.* FROM users u",
  b.If(boolCondition, "JOIN employees e ON u.id = e.id"),
  "WHERE u.id IN", ids,
)

You can include any number of arguments in If(), and they will only be processed by Build() if the condition is true. This can also be called as squint.If()

Field Mapping

When mapping struct fields into database columns, by default the names are used verbatim. You can change the mapping by using the db struct field.

type User struct {
  Id        int                            // column is Id
  FirstName string `db:"first_name"`       // column is first_name
  Username  string `db:"-"`                // skip this one
  ManagerId int    `db:"mgr_id,omitempty"` // skip if empty (0)
}

A custom mapping function may be a future enhancement

Options

The Builder uses functional options to control behavior. They and their defaults are:

Tag(string)       // tag name for field mapping ("db")
NilValues(bool)   // keep nil struct/map field values? (false)
EmptyValues(bool) // keep empty string struct/map field values? (false)
LogQuery(bool)    // log queries (false)
LogBinds(bool)    // log bind values from queries (false)
Log(bool)         // shorthand to log both queries AND binds (false)

These can all be set via NewBuilder():

b := squint.NewBuilder(
  squint.NilValues(true),
  squint.Log(true),
)

They can also be set via Build(), and will only be in effect for that query:

b.Build(
  squint.LogBinds(false),
  "update users set password =", &myPass,
  "where id =", id,
)

A bit more about NilValues and EmptyValues:

When a struct or map is processed, any string values that are empty ("") will be skipped if EmptyValues is false. Any values that are nil will be skipped if NilValues is false. This is the default behavior. Why?

It's common to have a struct type that represents a full set of possible columns to use. It's also common that only some of those values are supplied in a given scenario. For example:

// the columns we allow to be updated
type Updates struct {
  FirstName  string
  LastName   string
  Department string
}

// update a user record
func updateUser(id int, updates *Updates) error {
  b := squint.NewBuilder()
  sql, binds := b.Build("update users set", updates, "where id =", id)
  _, err := db.Exec(sql, binds...)
  return err
}

What if only updates.Department is set? The zero value for a string is the empty string (""). If EmptyValues is true, the user record would be updated with an empty FirstName and LastName. This is generally not what you want. If EmptyValues is false, then only Department will be included in the update. This safer behavior is the default.

Note that EmptyValues only applies to strings. This is because the zero values of other basic types are more common as real values, zero (0) in particular. So you can't really tell if one of these was explicltly set or not. How to handle this?

If you're sure the zero value is not a value you want saved, you can set omitempty in a particular field's db tag.

type Updates struct {
  Name string
  Age  int    `db:"omitempty"`
}

Otherwise, you can leverage the NilValues option. It is essentially the same as EmptyValues, but applies to pointers. So, you can do:

type Updates struct {
  FirstName  string
  LastName   string
  Department string
  Balance    *float64
}

Now you can tell the difference between setting Balance to zero or not setting it at all. With NilValues set to false, an empty Balance would be skipped; otherwise it would be included as a SQL NULL.

NOTE: Neither EmptyValues or NilValues apply for multi-row inserts, since the columns must be consistent across rows.

Bridge

Because of the way variadic functions work, you cannot literally pass the return values of Build() directly into something like Exec().

// NOPE: you can't do this
err := db.Exec(b.Build("insert into user", newUser))

// instead, you have to do this
sql, binds := b.Build("insert into user", newUser)
err := db.Exec(sql, binds...)

To make this more convenient, you can use a Squint database Bridge.

// open database and build a bridge
con, err := sqlx.Open("mysql", dsn)
db := squint.BridgeDB(con, squint.NewBuilder())

// now queries are easier via the Squint extension
err := db.Squint.Exec("insert into user", newUser)

// but you can still do things the old way too
err := db.Exec("update users set balance = ? where id = ?", 0.00, 10)

The bridge has wrapper functions for the standard Exec, Query and QueryRow, as well as the sqlx extensions MustExec, Queryx, QueryRowx, Get and Select. Wherever those original functions expect sql and binds, the Squint versions expect Squint Build() parameters.

The bridged database also returns a bridged transaction if you call Begin, Beginx, or MustBegin:

// using the above bridged database
tx := db.Begin()

// use the same Squint extensions
if err := tx.Squint.Exec("insert into user", newUser); err == nil {
  db.Commit()
} else {
  db.Rollback()
}

Documentation

Index

Constants

View Source
const (
	BASE sqlContext = iota
	INSERT
	SET
	IN
)

supported contexts

Variables

This section is empty.

Functions

This section is empty.

Types

type Bind

type Bind string

Bind treats a string as a bind rather than SQL fragment

type Bridge

type Bridge struct {
	*Builder
	// contains filtered or unexported fields
}

Bridge provides a connection between a Builder and a Target. It essentially wraps a Builder interface around standard query functions.

So something like this:

sql, binds := builder.Build("SELECT * FROM table WHERE", conditions) rows, err := db.Query(sql, binds)

Becomes:

rows, err := bridge.Query("SELECT * FROM table WHERE", conditions)

func (*Bridge) Exec

func (b *Bridge) Exec(bits ...interface{}) (sql.Result, error)

Exec executes a query that doesn't return rows

func (*Bridge) Get

func (b *Bridge) Get(dest interface{}, bits ...interface{}) error

Get retrieves a single row and scans into dest

func (*Bridge) MustExec

func (b *Bridge) MustExec(bits ...interface{}) sql.Result

MustExec executes a query and panics on error

func (*Bridge) Query

func (b *Bridge) Query(bits ...interface{}) (*sql.Rows, error)

Query executes a query that returns rows, typically a SELECT

func (*Bridge) QueryRow

func (b *Bridge) QueryRow(bits ...interface{}) *sql.Row

QueryRow executes a query that is expected to return at most one row

func (*Bridge) QueryRowx

func (b *Bridge) QueryRowx(bits ...interface{}) *sqlx.Row

QueryRowx is the same as QueryRow but returns a *sqlx.Row

func (*Bridge) Queryx

func (b *Bridge) Queryx(bits ...interface{}) (*sqlx.Rows, error)

Queryx is the same as Query but returns a *sqlx.Rows

func (*Bridge) Select

func (b *Bridge) Select(dest interface{}, bits ...interface{}) error

Select executes a query and scans the into dest (a slice)

type Builder

type Builder struct {
	Options
}

Builder is the core of public squint interactions. It's responsible for processing inputs into SQL and binds

func NewBuilder

func NewBuilder(options ...Option) *Builder

NewBuilder returns a new Builder with the supplied options

func (*Builder) Build

func (b *Builder) Build(bits ...interface{}) (string, []interface{})

Build accepts a list of SQL fragments and Go variables and interpolates them into a query and a set of binds. These are appropriate to pass into a variety of execution methods of the sql (or sqlx) package.

sql, binds := b.Build("INSERT INTO users", &User)

func (*Builder) HasValues

func (b *Builder) HasValues(src interface{}) bool

HasValues evaluates whether a struct or map has values that would be used according to the Builder's "Keep*"" options.

If you have a situation where one might be considered empty, you can use this as a pre-check to avoid generating invalid SQL

func (*Builder) If

func (b *Builder) If(condition bool, bits ...interface{}) Condition

If allows for conditionally including a list of arguments in a query. This is a convenience to allow a bit of inline logic when calling Build:

sql, binds := b.Build(

"SELECT u.* FROM users u",
b.If(
  EmployeesOnly,
  "JOIN employees e ON u.id = e.id"
),
"WHERE id IN", ids

)

type Condition

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

Condition will conditionally process a list of arguments

func If

func If(condition bool, bits ...interface{}) Condition

If : package level version, in case Builder instance isn't handy

type DB

type DB struct {
	*sqlx.DB
	Squint Bridge
}

DB is a bridged db connection; Create one with squint.BridgeDB

func BridgeDB

func BridgeDB(db *sqlx.DB, builder *Builder) *DB

BridgeDB creates a bridge between a database handle and a Builder

func (*DB) Begin

func (db *DB) Begin() (*Tx, error)

Begin starts and bridges a transaction

func (*DB) Beginx

func (db *DB) Beginx() (*Tx, error)

Beginx starts and bridges a transaction

func (*DB) MustBegin

func (db *DB) MustBegin() *Tx

MustBegin starts and bridges a transaction, but panics on error

type Option added in v0.3.0

type Option func(*Options)

Option is a functional option

func EmptyValues added in v0.3.0

func EmptyValues(b bool) Option

EmptyValues : keep empty string struct/map field values

func Log added in v0.3.0

func Log(b bool) Option

Log : log queries and binds

func LogBinds added in v0.3.0

func LogBinds(b bool) Option

LogBinds : log binds

func LogQuery added in v0.3.0

func LogQuery(b bool) Option

LogQuery : log queries

func NilValues added in v0.3.0

func NilValues(b bool) Option

NilValues : keep nil struct/map field values?

func Tag added in v0.3.0

func Tag(tag string) Option

Tag is the field tag to use

type Options

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

Options for the squint Builder

func (*Options) SetOption added in v0.4.0

func (o *Options) SetOption(options ...Option)

SetOption applies the given options

type Target

type Target interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row

	MustExec(query string, args ...interface{}) sql.Result
	Queryx(query string, args ...interface{}) (*sqlx.Rows, error)
	QueryRowx(query string, args ...interface{}) *sqlx.Row
	Get(dest interface{}, query string, args ...interface{}) error
	Select(dest interface{}, query string, args ...interface{}) error
}

Target is the destination of a Bridge. It is typically a database or transaction handle.

type Tx

type Tx struct {
	*sqlx.Tx
	Squint Bridge
}

Tx is a bridged transaction; Create one with DB.Begin(x)

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
t or T : Toggle theme light dark auto
y or Y : Canonical URL