sqlx

package module
v0.0.0-...-3f954fd Latest Latest
Warning

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

 Go to latest Published: Dec 15, 2020 License: MIT Imports: 11 Imported by: 0

README

#sqlx

Build Status

sqlx is a set of extensions upon go's basic database/sql module. They are all optional and SQLX versions of sql.DB, sql.Tx, sql.Stmt, etc all leave the underlying interfaces untouched, so sqlx.DB.Query will behave exactly the same as sql.DB.Query, etc.

Major additional concepts are:

  • Named parameter support with NamedQuery, NamedExec, et al.
  • Scan() into a struct with StructScan
  • Get and Select, which will QueryRow or Query directly into a provided struct or slice
  • Common error handling mnemonics Execf, Execp (also MustExec), and Execl
  • LoadFile for easy execution of SQL in files

usage

Read the documentation for detailed API docs, and check sqlx_test.go for more usage.

Below is an example which shows some common use cases for sqlx.


package main

import (
    _ "github.com/lib/pq"
    "database/sql"
    "github.com/jmoiron/sqlx"
)

var schema = `
CREATE TABLE person (
    first_name text,
    last_name text,
    email text
);

CREATE TABLE place (
    country text,
    city text NULL,
    telcode integer
)`

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}

type Place struct {
    Country string
    City    sql.NullString
    TelCode int
}

func main() {
    // this connects & tries a simple 'SELECT 1', panics on error
    // use sqlx.Open() for sql.Open() semantics
    db := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")

    // exec the schema or fail; multi-statement Exec behavior varies between
    // database drivers;  pq will exec them all, sqlite3 won't, ymmv
    db.Execf(schema)
    
    tx := db.MustBegin()
    tx.Execl("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")
    tx.Execl("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net")
    tx.Execl("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "1")
    tx.Execl("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Hong Kong", "852")
    tx.Execl("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "65")
    tx.Commit()

    // Query the database, storing results in a []Person (wrapped in []interface{})
    people := []Person{}
    db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")
    jason, john := people[0], people[1]

    fmt.Printf("%#v\n%#v", jason, john)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}
    // Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@gmail.net"}

    // You can also get a single result, a la QueryRow
    jason = Person{}
    err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")
    fmt.Printf("%#v\n", jason)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}

    // if you have null fields and use SELECT *, you must use sql.Null* in your struct
    places := []Place{}
    err := db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
    if err != nil {
        fmt.Printf(err)
        return
    }
    usa, singsing, honkers = places[0], places[1], places[2]
    
    fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers)
    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}
    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}

    // Loop through rows using only one struct
    place := Place{}
    rows, err := db.Queryx("SELECT * FROM place")
    for rows.Next() {
        rows.StructScan(&place)
        fmt.Printf("%#v\n", place)
    }
    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}
    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}

    // Named queries, using `:name` as the bindvar.  Automatic bindvar support
    // which takes into account the dbtype based on the driverName on sqlx.Open/Connect
    _, err = db.NamedExecMap(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, 
        map[string]interface{}{
            "first": "Bin",
            "last": "Smuth",
            "email": "bensmith@allblacks.nz",
    })

    // Selects Mr. Smith from the database
    rows, err := db.NamedQueryMap(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "Bin"})

    // Named queries can also use structs.  Their bind names follow the same rules
    // as the name -> db mapping, so struct fields are lowercased and the `db` tag
    // is taken into consideration.
    rows, err := db.NamedQuery(`SELECT * FROM person WHERE first_name=:first_name`, jason)

}

Documentation

Overview

General purpose extensions to database/sql

sqlx is intended to seamlessly wrap database/sql and provide convenience methods which are useful in the development of database driven applications. None of the underlying database/sql methods are changed, instead all extended behavior is implemented through new methods defined on wrapper types.

sqlx adds struct scanning, named queries, query rebinding between drivers, convenient shorthand for common error handling, from-file query execution, and more.

Index

Constants

View Source 
const (
	UNKNOWN = iota
	QUESTION
	DOLLAR
)

Bindvar types supported by sqlx's Rebind & BindMap/Struct functions.

Variables

This section is empty.

Functions

func BaseSliceType 

func BaseSliceType(t reflect.Type) (reflect.Type, error)

Return the type for a slice, dereferencing it if it is a pointer. Returns an error if the destination is not a slice or a pointer to a slice.

func BaseStructType 

func BaseStructType(t reflect.Type) (reflect.Type, error)

Return the type of a struct, dereferencing it if it is a pointer. Returns an error if the destination is not a struct or a pointer to a struct.

func BindMap 

func BindMap(bindType int, query string, args map[string]interface{}) (string, []interface{}, error)

Bind a named parameter query with a map of arguments.

func BindStruct 

func BindStruct(bindType int, query string, arg interface{}) (string, []interface{}, error)

Bind a named parameter query with fields from a struct argument. The rules for binding field names to parameter names follow the same conventions as for StructScan, including obeying the `db` struct tags.

func BindType 

func BindType(driverName string) int

Return the bindtype for a given database given a drivername

func Execf 

func Execf(e Execer, query string, args ...interface{}) sql.Result

Execf (fatal) runs Exec on the query and args and uses log.Fatal to print the query, result, and error in the event of an error.

func Execl 

func Execl(e Execer, query string, args ...interface{}) sql.Result

Execl (log) runs Exec on the query and args and ses log.Println to print the query, result, and error in the event of an error. Unlike Execv, Execl does not return the error, and can be used in single-value contexts.

Do not abuse Execl; it is convenient for experimentation but generally not for production use.

func Execp 

func Execp(e Execer, query string, args ...interface{}) sql.Result

Execp (panic) runs Exec on the query and args and panics on error.

func Execv 

func Execv(e Execer, query string, args ...interface{}) (sql.Result, error)

Execv (verbose) Exec's the query using the Execer and uses log.Println to print the query, result, and error in the event of an error.

func Get 

func Get(q Queryer, dest interface{}, query string, args ...interface{}) error

QueryRow using the provided Queryer, and StructScan the resulting row into dest, which must be a pointer to a struct. If there was no row, Get will return sql.ErrNoRows.

func LoadFile 

func LoadFile(e Execer, path string) (*sql.Result, error)

LoadFile exec's every statement in a file (as a single call to Exec). LoadFile may return a nil *sql.Result if errors are encountered locating or reading the file at path. LoadFile reads the entire file into memory, so it is not suitable for loading large data dumps, but can be useful for initializing schemas or loading indexes. FIXME: this does not really work with multi-statement files for mattn/go-sqlite3 or the go-mysql-driver/mysql drivers; pq seems to be an exception here. Detecting this by requiring something with DriverName() and then attempting to split the queries will be difficult to get right, and its current driver-specific behavior is deemed at least not complex in its incorrectness.

func MustExec 

func MustExec(e Execer, query string, args ...interface{}) sql.Result

MustExec (panic) is an alias for Execp.

func NamedExec 

func NamedExec(e Ext, query string, arg interface{}) (sql.Result, error)

Like NamedQuery, but use Exec instead of Queryx.

func NamedExecMap 

func NamedExecMap(e Ext, query string, argmap map[string]interface{}) (sql.Result, error)

Like NamedQuery, but use Exec instead of Queryx.

func Rebind 

func Rebind(bindType int, query string) string

Rebind a query from the default bindtype (QUESTION) to the target bindtype

func Select 

func Select(q Queryer, dest interface{}, query string, args ...interface{}) error

Query using the provided Queryer, and StructScan each row into dest, which must be a slice of structs. The resulting *sql.Rows are closed automatically.

func Selectf 

func Selectf(q Queryer, dest interface{}, query string, args ...interface{})

Selectf (fatal) will Select using a Queryer and use log.Fatal to print the query and the error in the event of an error.

func Selectv 

func Selectv(q Queryer, dest interface{}, query string, args ...interface{}) error

Selectv (verbose) will Select using a Queryer and use log.Println to print the query and the error in the event of an error.

func StructScan 

func StructScan(rows *sql.Rows, dest interface{}) error

Fully scan a sql.Rows result into the dest slice. StructScan destinations MUST have fields that map to every column in the result, and they MAY have fields in addition to those. Fields are mapped to column names by lowercasing the field names by default: use the struct tag `db` to specify exact column names for each field.

StructScan will scan in the entire rows result, so if you need do not want to allocate structs for the entire result, use Queryx and see sqlx.Rows.StructScan.

Types

type Binder 

type Binder interface {
	DriverName() string
	Rebind(string) string
	BindMap(string, map[string]interface{}) (string, []interface{}, error)
	BindStruct(string, interface{}) (string, []interface{}, error)
}

An interface for something which can bind queries (Tx, DB)

type DB 

type DB struct {
	sql.DB
	// contains filtered or unexported fields
}

An wrapper around sql.DB which keeps track of the driverName upon Open, used mostly to automatically bind named queries using the right bindvars.

func Connect 

func Connect(driverName, dataSourceName string) (*DB, error)

Connect to a database and verify with a ping.

func MustConnect 

func MustConnect(driverName, dataSourceName string) *DB

Connect, but panic on error.

func NewDb 

func NewDb(db *sql.DB, driverName string) *DB

func Open 

func Open(driverName, dataSourceName string) (*DB, error)

Same as database/sql's Open, but returns an *sqlx.DB instead.

func (*DB) Beginx 

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

Same as Begin, but returns an *sqlx.Tx instead of an *sql.Tx.

func (*DB) BindMap 

func (db *DB) BindMap(query string, argmap map[string]interface{}) (string, []interface{}, error)

BindMap's a query using the DB driver's bindvar type.

func (*DB) BindStruct 

func (db *DB) BindStruct(query string, arg interface{}) (string, []interface{}, error)

BindStruct's a query using the DB driver's bindvar type.

func (*DB) DriverName 

func (db *DB) DriverName() string

Returns the driverName passed to the Open function for this DB.

func (*DB) Execf 

func (db *DB) Execf(query string, args ...interface{}) sql.Result

Execf (fatal) runs Execf using this database.

func (*DB) Execl 

func (db *DB) Execl(query string, args ...interface{}) sql.Result

Execl (log) runs Execl using this database.

func (*DB) Execp 

func (db *DB) Execp(query string, args ...interface{}) sql.Result

Execp (panic) runs Execp using this database.

func (*DB) Execv 

func (db *DB) Execv(query string, args ...interface{}) (sql.Result, error)

Execv (verbose) runs Execv using this database.

func (*DB) Get 

func (db *DB) Get(dest interface{}, query string, args ...interface{}) error

Get using this DB.

func (*DB) LoadFile 

func (db *DB) LoadFile(path string) (*sql.Result, error)

LoadFile using this DB.

func (*DB) MustBegin 

func (db *DB) MustBegin() *Tx

Begin starts a transaction, and panics on error. Returns an *sqlx.Tx instead of an *sql.Tx.

func (*DB) MustExec 

func (db *DB) MustExec(query string, args ...interface{}) sql.Result

MustExec (panic) runs MustExec using this database.

func (*DB) NamedExec 

func (db *DB) NamedExec(query string, arg interface{}) (sql.Result, error)

NamedExec using this DB.

func (*DB) NamedExecMap 

func (db *DB) NamedExecMap(query string, argmap map[string]interface{}) (sql.Result, error)

NamedExecMap using this DB.

func (*DB) NamedQuery 

func (db *DB) NamedQuery(query string, arg interface{}) (*Rows, error)

NamedQuery using this DB.

func (*DB) NamedQueryMap 

func (db *DB) NamedQueryMap(query string, argmap map[string]interface{}) (*Rows, error)

NamedQueryMap using this DB.

func (*DB) Preparex 

func (db *DB) Preparex(query string) (*Stmt, error)

Preparex returns an sqlx.Stmt instead of a sql.Stmt

func (*DB) QueryRowx 

func (db *DB) QueryRowx(query string, args ...interface{}) *Row

Same as QueryRow, but returns an *sqlx.Row instead of *sql.Row.

func (*DB) Queryx 

func (db *DB) Queryx(query string, args ...interface{}) (*Rows, error)

Same as Query, but returns an *sqlx.Rows instead of *sql.Rows.

func (*DB) Rebind 

func (db *DB) Rebind(query string) string

Rebinds a query from QUESTION to the DB driver's bindvar type.

func (*DB) Select 

func (db *DB) Select(dest interface{}, query string, args ...interface{}) error

Select using this DB.

type Execer 

type Execer interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
}

An interface for something which can Execute sql commands (Tx, DB, Stmt)

type Ext 

type Ext interface {
	Binder
	Queryer
	Execer
}

A union interface which can bind, query, and exec (Tx, DB), used for NamedQuery and NamedExec, which requires exec/query and BindMap/Struct

type Preparer 

type Preparer interface {
	Prepare(query string) (*sql.Stmt, error)
}

An interface for something which can Prepare sql statements (Tx, DB)

type Queryer 

type Queryer interface {
	Query(query string, args ...interface{}) (*sql.Rows, error)
	Queryx(query string, args ...interface{}) (*Rows, error)
	QueryRowx(query string, args ...interface{}) *Row
}

An interface for something which can Execute sql queries (Tx, DB, Stmt)

type Row 

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

A reimplementation of sql.Row in order to gain access to the underlying sql.Rows.Columns() data, necessary for StructScan.

func (*Row) Columns 

func (r *Row) Columns() ([]string, error)

Return the underlying sql.Rows.Columns(), or the deferred error usually returned by Row.Scan()

func (*Row) Scan 

func (r *Row) Scan(dest ...interface{}) error

Same implementation as sql.Row.Scan

func (*Row) StructScan 

func (r *Row) StructScan(dest interface{}) error

StructScan's a single Row (result of QueryRowx) into dest

type Rows 

type Rows struct {
	sql.Rows
	// contains filtered or unexported fields
}

A wrapper around sql.Rows which caches costly reflect operations during a looped StructScan

func NamedQuery 

func NamedQuery(e Ext, query string, arg interface{}) (*Rows, error)

Issue a named query using BindStruct to get a query executable by the driver and then run Queryx on the result. May return an error from the binding or from the execution itself.

func NamedQueryMap 

func NamedQueryMap(e Ext, query string, argmap map[string]interface{}) (*Rows, error)

Issue a named query using BindMap to get a query executable by the driver and then run Queryx on the result. May return an error from the binding or from the query execution itself.

func (*Rows) StructScan 

func (r *Rows) StructScan(dest interface{}) error

Like sql.Rows.Scan, but scans a single Row into a single Struct. Use this and iterate over Rows manually when the memory load of Select() might be prohibitive. *Rows.StructScan caches the reflect work of matching up column positions to fields to avoid that overhead per scan, which means it is not safe to run StructScan on the same Rows instance with different struct types.

type Stmt 

type Stmt struct{ sql.Stmt }

An sqlx wrapper around database/sql's Stmt with extra functionality Although a Stmt's interface differs from Tx and DB's, internally, a wrapper is used to satisfy the Queryer & Execer interfaces.

func Preparex 

func Preparex(p Preparer, query string) (*Stmt, error)

Preparex prepares a statement given a Preparer (Tx, DB), returning an *sqlx.Stmt.

func (*Stmt) Execf 

func (s *Stmt) Execf(args ...interface{}) sql.Result

Execf (fatal) using this statement. Note that the query portion of the error output will be blank, as Stmt does not expose its query.

func (*Stmt) Execl 

func (s *Stmt) Execl(args ...interface{}) sql.Result

Execl (log) using this statement. Note that the query portion of the error output will be blank, as Stmt does not expose its query.

func (*Stmt) Execp 

func (s *Stmt) Execp(args ...interface{}) sql.Result

Execf (panic) using this statement. Note that the query portion of the error output will be blank, as Stmt does not expose its query.

func (*Stmt) Execv 

func (s *Stmt) Execv(args ...interface{}) (sql.Result, error)

Execv (verbose) runs Execv using this statement. Note that the query portion of the error output will be blank, as Stmt does not expose its query.

func (*Stmt) Get 

func (s *Stmt) Get(dest interface{}, query string, args ...interface{}) error

Get using the prepared statement.

func (*Stmt) MustExec 

func (s *Stmt) MustExec(args ...interface{}) sql.Result

MustExec (panic) using this statement. Note that the query portion of the error output will be blank, as Stmt does not expose its query.

func (*Stmt) Select 

func (s *Stmt) Select(dest interface{}, args ...interface{}) error

Select using the prepared statement.

func (*Stmt) Selectf 

func (s *Stmt) Selectf(dest interface{}, args ...interface{})

Selectf (fatal) using the prepared statement.

func (*Stmt) Selectv 

func (s *Stmt) Selectv(dest interface{}, args ...interface{}) error

Selectv (verbose) using the prepared statement.

type Tx 

type Tx struct {
	sql.Tx
	// contains filtered or unexported fields
}

An sqlx wrapper around database/sql's Tx with extra functionality

func (*Tx) BindMap 

func (tx *Tx) BindMap(query string, argmap map[string]interface{}) (string, []interface{}, error)

BindMap's a query within a transaction's bindvar type.

func (*Tx) BindStruct 

func (tx *Tx) BindStruct(query string, arg interface{}) (string, []interface{}, error)

BindStruct's a query within a transaction's bindvar type.

func (*Tx) DriverName 

func (tx *Tx) DriverName() string

Return the driverName used by the DB which began a transaction.

func (*Tx) Execf 

func (tx *Tx) Execf(query string, args ...interface{}) sql.Result

Execf (fatal) runs Execf within a transaction.

func (*Tx) Execl 

func (tx *Tx) Execl(query string, args ...interface{}) sql.Result

Execl (log) runs Execl within a transaction.

func (*Tx) Execp 

func (tx *Tx) Execp(query string, args ...interface{}) sql.Result

Execp (panic) runs Execp within a transaction.

func (*Tx) Execv 

func (tx *Tx) Execv(query string, args ...interface{}) (sql.Result, error)

Execv (verbose) runs Execv within a transaction.

func (*Tx) Get 

func (tx *Tx) Get(dest interface{}, query string, args ...interface{}) error

Get within a transaction.

func (*Tx) LoadFile 

func (tx *Tx) LoadFile(path string) (*sql.Result, error)

LoadFile within a transaction.

func (*Tx) MustExec 

func (tx *Tx) MustExec(query string, args ...interface{}) sql.Result

MustExec (panic) runs MustExec within a transaction.

func (*Tx) NamedExec 

func (tx *Tx) NamedExec(query string, argmap map[string]interface{}) (sql.Result, error)

Exec a named query within a transaction.

func (*Tx) NamedQuery 

func (tx *Tx) NamedQuery(query string, argmap map[string]interface{}) (*Rows, error)

NamedQuery within a transaction.

func (*Tx) Preparex 

func (tx *Tx) Preparex(query string) (*Stmt, error)

Prepare's a statement within a transaction, returning a *sqlx.Stmt instead of an *sql.Stmt.

func (*Tx) QueryRowx 

func (tx *Tx) QueryRowx(query string, args ...interface{}) *Row

QueryRow within a transaction, returning *sqlx.Row instead of *sql.Row.

func (*Tx) Queryx 

func (tx *Tx) Queryx(query string, args ...interface{}) (*Rows, error)

Query within a transaction, returning *sqlx.Rows instead of *sql.Rows.

func (*Tx) Rebind 

func (tx *Tx) Rebind(query string) string

Rebind a query within a transaction's bindvar type.

func (*Tx) Select 

func (tx *Tx) Select(dest interface{}, query string, args ...interface{}) error

Select within a transaction.

func (*Tx) Selectf 

func (tx *Tx) Selectf(dest interface{}, query string, args ...interface{})

Selectf (fatal) within a transaction.

func (*Tx) Selectv 

func (tx *Tx) Selectv(dest interface{}, query string, args ...interface{}) error

Selectv (verbose) within a transaction.

func (*Tx) Stmtx 

func (tx *Tx) Stmtx(stmt interface{}) *Stmt

Returns a version of the prepared statement which runs within a transaction. Provided stmt can be either *sql.Stmt or *sqlx.Stmt, and the return value is always *sqlx.Stmt.

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.