Github url

sqlx

by jmoiron

jmoiron /sqlx

general purpose extensions to golang's database/sql

8.6K Stars 670 Forks Last release: Not found MIT License 411 Commits 3 Releases

Available items

No Items, yet!

The developer of this repository has not created any items for sale yet. Need a bug fixed? Help with integration? A different license? Create a request here:

sqlx

Build Status Coverage Status Godoc license

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 user documentation that explains how to use

database/sql

along with sqlx.

Recent Changes

sql.ColumnType

sets the required minimum Go version to 1.8.

  • 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.

  • Using Go 1.6 and below with
    types.JSONText
    and
    types.GzippedText
    can be potentially unsafe, especially when used with common auto-scan sqlx idioms like
    Select
    and
    Get
    . See golang bug #13905.

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:

SELECT a.id, a.name, b.id, b.name FROM foos AS a JOIN foos AS b ON a.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 ( "database/sql" "fmt" "log" \_ "github.com/lib/pq" "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 Pings the database trying to connect, panics on error // use sqlx.Open() for sql.Open() semantics db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable") if err != 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, ymmv db.MustExec(schema) tx := db.MustBegin() tx.MustExec("INSERT INTO person (first\_name, last\_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "[email protected]") tx.MustExec("INSERT INTO person (first\_name, last\_name, email) VALUES ($1, $2, $3)", "John", "Doe", "[email protected]") 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 &person tx.NamedExec("INSERT INTO person (first\_name, last\_name, email) VALUES (:first\_name, :last\_name, :email)", &Person{"Jane", "Citizen", "[email protected]"}) 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:"[email protected]"} // Person{FirstName:"John", LastName:"Doe", Email:"[email protected]"} // 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:"[email protected]"} // 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.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 struct place := Place{} rows, err := db.Queryx("SELECT \* FROM place") for rows.Next() { err := rows.StructScan(&place) if err != 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": "[email protected]", }) // Selects Mr. Smith from the database rows, 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) }

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.