#sqlx

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Major additional concepts are:

• Marshal rows into structs (with embedded struct support), maps, and slices
• Named parameter support including prepared statements
• Get and Select to go quickly from query to struct/slice

In addition to the godoc API documentation, there is also some standard documentation that explains how to use database/sql along with sqlx.

Recent Changes

• sqlx/types.JsonText has been renamed to JSONText to follow Go naming conventions.

This breaks backwards compatibility, but it's in a way that is trivially fixable (s/JsonText/JSONText/g). The types package is both experimental and not in active development currently.

More importantly, golang bug #13905 makes types.JSONText and types.GzippedTextpotentially unsafe, especially when used with common auto-scan sqlx idioms like Select and Get.

Backwards Compatibility

There is no Go1-like promise of absolute stability, but I take the issue seriously and will maintain the library in a compatible state unless vital bugs prevent me from doing so. Since #59 and #60 necessitated breaking behavior, a wider API cleanup was done at the time of fixing. It's possible this will happen in future; if it does, a git tag will be provided for users requiring the old behavior to continue to use it until such a time as they can migrate.

install

go get github.com/jmoiron/sqlx

issues

Row headers can be ambiguous (SELECT 1 AS a, 2 AS a), and the result of Columns() does not fully qualify column names in queries like:

SELECTa.id, a.name, b.id, b.nameFROM foos AS a JOIN foos AS b ONa.parent=b.id;

making a struct or map destination ambiguous. Use AS in your queries to give columns distinct names, rows.Scan to scan them manually, or SliceScan to get a slice of results.

usage

Below is an example which shows some common use cases for sqlx. Check sqlx_test.go for more usage.

package main
import (
_ "github.com/lib/pq""database/sql""github.com/jmoiron/sqlx""log"
)
varschema=`CREATE TABLE person ( first_name text, last_name text, email text);CREATE TABLE place ( country text, city text NULL, telcode integer)`typePersonstruct {
FirstNamestring`db:"first_name"`LastNamestring`db:"last_name"`Emailstring
}
typePlacestruct {
CountrystringCity sql.NullStringTelCodeint
}
funcmain() {
// this Pings the database trying to connect, panics on error// use sqlx.Open() for sql.Open() semanticsdb, err:=sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
iferr!=nil {
log.Fatalln(err)
}
// exec the schema or fail; multi-statement Exec behavior varies between// database drivers; pq will exec them all, sqlite3 won't, ymmvdb.MustExec(schema)
tx:=db.MustBegin()
tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")
tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net")
tx.MustExec("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "1")
tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Hong Kong", "852")
tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "65")
// Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &persontx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})
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 QueryRowjason=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 structplaces:= []Place{}
err=db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
iferr!=nil {
fmt.Println(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 structplace:=Place{}
rows, err:=db.Queryx("SELECT * FROM place")
forrows.Next() {
err:=rows.StructScan(&place)
iferr!=nil {
log.Fatalln(err)
} 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.NamedExec(`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 databaserows, err=db.NamedQuery(`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)
}