sqlx

This is Vinovest's fork of https://github.com/jmoiron/sqlx.git for ongoing support. Sqlx hasn't been updated in a while and we use it extensively, so we're going to maintain this fork with some additional features and bug fixes. You're welcome to use and contribute to this fork!

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
• Bulk insert SQL builder
• Generic functions One and List
• Range function AllRows, or for prepared statements use All

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

Changes compared to the original sqlx

1.7.0

• Better scanning in the case of outer joins. If a struct contains a nested struct pointer, it will no longer be a scan error.

• Made complex joins easier to scan by using the position of the field to help map duplicate column names into structs. See the joins example.

• Improved "unsafe" mode at the DB level. "Unsafe" isn't all that unsafe, it really is just an additional check when scanning rows. If there's a column in the row result that can't be mapped to the struct, sqlx will return a "missing destination name" error. A new WithUnsafe option for Connect and Open allows you to turn this off.

1.6.0:

• Set Go version to 1.23.

• Added AllRows package function and All on statement types to range over rows and automatically close the result set.

• Added functions One and List for querying with generics. Modifications to types Stmt and NamedStmt to support generics may cause some small compatibility changes that should be quick to resolve with a search and replace. Both are now type aliases to attempt to minimize the impact of this change.

• fix for sql in-list parsing that will properly ignore question marks in comments and strings. This frequently caused a confusing error ("number of bindVars exceeds arguments").

1.5.0:

• VALUES bulk insertions were performed by a regex previously, now sqlx will use a tokenizer to parse the query and reliably expand the query for additional bound parameters. This fixes several bugs around named parameters (e.g. :name). Where previously a colon had to be escaped using an additional colon (resulting in ::name), now the query will work as expected.

• Rebind parsing uses the tokenizer now as well, fixing double quoting and unexpected behavior with colons in sql strings.

• Get and GetContext will now error if the query returns more than one row. Previously, it fetched the first row and ignored the rest. Using Get on multiple rows is typically a mistake, and this change makes the behavior more consistent and avoids potential bugs.

• Fix for multiple sql statements ("select a from test; select b from test2;") in a single call. Previously, sqlx cached the columns of the first query and used them for all subsequent queries. This could lead to incorrect results if the queries returned different columns. Now, sqlx will reset the columns for each query on NextResultSet.

• Adds Queryable interface to unify DB and Tx.

Backwards Compatibility

Compatibility with the most recent two versions of Go is a requirement for any new changes. Compatibility beyond that is not guaranteed.

Versioning is done with Go modules. Breaking changes (eg. removing deprecated API) will get major version number bumps.

Install

go get github.com/vinovest/sqlx

Usage

There are several standalone examples in the examples directory that demonstrate various features of sqlx.

• Generics - query with generics
• Bulk inserts - efficient bulk inserts
• Named queries - named parameter queries
• SQL in-lists - using In to generate SQL IN clauses
• Prepared statements - prepared statements offer better performance
• Complex joins - mapping fields from joins

The simplified form of querying using sqlx looks like the below. Given a struct Person, querying all of the rows in the person table is as simple as:

package main
import (
"fmt"
_ "github.com/lib/pq""github.com/vinovest/sqlx"
)
typePersonstruct {
FirstNamestring`db:"first_name"`LastNamestring`db:"last_name"`Emailstring
}
funcmain() {
db, _:=sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
people, _:=sqlx.List[Person](db, "SELECT * FROM person")
fmt.Printf("People %#v\n", people)
// alternatively, range over rowsrows, _:=db.Queryx("select * from person")
forperson, err:=range sqlx.AllRows[Person](rows) {
iferr!=nil {
panic(err)
}
fmt.Printf("%#v\n", person)
}
}

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.