README
¶
sqlz
sqlz is a lightweight, dependency-free Go library that extends the standard database/sql package, adding support for named queries, struct scanning, and batch operations, while having a clean, minimal API.
It's designed to feel familiar to anyone using database/sql, while removing repetitive boilerplate code. It can scan directly into structs, maps, or slices, and run named queries with full UTF-8/multilingual support.
Documentation: https://rfberaldo.github.io/sqlz/.
Features
- Named queries for structs and maps.
- Automatic scanning into primitives, structs, maps and slices.
- Automatic expanding "IN" clauses.
- Automatic expanding batch inserts.
- Automatic prepared statement caching.
Getting started
Install
go get github.com/rfberaldo/sqlz
Setup
There are two ways to use it:
// 1. using [sqlz.Connect]
db, err := sqlz.Connect("sqlite3", ":memory:")
// 2. using [sqlz.New] with a current connection
pool, err := sql.Open("sqlite3", ":memory:")
db := sqlz.New("sqlite3", pool, nil)
Examples
[!NOTE] For brevity of the examples, error handling is omitted.
Standard query
var users []User
db.Query(ctx, "SELECT * FROM user WHERE active = ?", true).Scan(&users)
// users variable now contains data from query
Named query
loc := Location{Country: "Brazil"}
var users []User
db.Query(ctx, "SELECT * FROM user WHERE country = :country", loc).Scan(&users)
// users variable now contains data from query
Exec
user := User{Name: "Alice", Email: "[email protected]"}
db.Exec(ctx, "INSERT INTO user (name, email) VALUES (:name, :email)", user)
Batch insert
users := []User{
{Name: "Alice", Email: "[email protected]"},
{Name: "Rob", Email: "[email protected]"},
{Name: "John", Email: "[email protected]"},
}
db.Exec(ctx, "INSERT INTO user (name, email) VALUES (:name, :email)", users)
// executed as "INSERT INTO user (name, email) VALUES (?, ?), (?, ?), (?, ?)"
Dependencies
sqlz has no dependencies, only testing/dev deps.
Comparison with sqlx
- It was designed with a simpler API for everyday use, with fewer concepts and less verbose.
- It has full support for UTF-8/multilingual named queries.
- It's more performant in most cases, take a look at the benchmarks for comparison.
Documentation
¶
Overview ¶
Package sqlz is an extension for the standard database/sql package. It adds named queries, scanning, and batch operations, while prividing a simple API.
Index ¶
- Constants
- func IsNotFound(err error) bool
- func ToSnakeCase(s string) string
- type DB
- func (db *DB) Begin(ctx context.Context) (*Tx, error)
- func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)
- func (db *DB) ClearStmtCache()
- func (db *DB) Exec(ctx context.Context, query string, args ...any) (sql.Result, error)
- func (db *DB) Pool() *sql.DB
- func (db *DB) Query(ctx context.Context, query string, args ...any) *Scanner
- func (db *DB) QueryRow(ctx context.Context, query string, args ...any) *Scanner
- type Options
- type Scanner
- type Tx
- func (tx *Tx) Commit() error
- func (tx *Tx) Conn() *sql.Tx
- func (tx *Tx) Exec(ctx context.Context, query string, args ...any) (sql.Result, error)
- func (tx *Tx) Query(ctx context.Context, query string, args ...any) *Scanner
- func (tx *Tx) QueryRow(ctx context.Context, query string, args ...any) *Scanner
- func (tx *Tx) Rollback() error
Examples ¶
Constants ¶
const ( BindAt = parser.BindAt // Syntax: '@p1' BindColon = parser.BindColon // Syntax: ':param' BindDollar = parser.BindDollar // Syntax: '$1' BindQuestion = parser.BindQuestion // Syntax: '?' )
Variables ¶
This section is empty.
Functions ¶
func IsNotFound ¶
IsNotFound is a helper to check if err contains sql.ErrNoRows.
func ToSnakeCase ¶ added in v0.3.0
ToSnakeCase transforms a string to snake case.
Types ¶
type DB ¶
type DB struct {
// contains filtered or unexported fields
}
DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.
func Connect ¶
Connect opens a database specified by its database driver name and a driver-specific data source name, then verify the connection with a ping.
No database drivers are included in the Go standard library. See https://golang.org/s/sqldrivers for a list of third-party drivers.
The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Connect function should be called just once.
func MustConnect ¶
MustConnect is like Connect, but panics on error.
func New ¶
New returns a DB instance using an existing sql.DB. The opts parameter can be nil for defaults.
Example:
pool, err := sql.Open("sqlite3", ":memory:")
db := sqlz.New("sqlite3", pool, nil)
Example ¶
package main
import (
"context"
"database/sql"
"log"
"github.com/rfberaldo/sqlz"
)
var ctx = context.Background()
func main() {
pool, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
db := sqlz.New("sqlite3", pool, nil)
_, err = db.Exec(ctx, "CREATE TABLE user (id INT PRIMARY KEY, name TEXT")
if err != nil {
log.Fatal(err)
}
}
Example (Options) ¶
package main
import (
"context"
"database/sql"
"log"
"strings"
"github.com/rfberaldo/sqlz"
)
var ctx = context.Background()
func main() {
pool, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
// use sqlz.Options as third parameter
db := sqlz.New("sqlite3", pool, &sqlz.Options{
Bind: sqlz.BindDollar,
StructTag: "json",
FieldNameTransformer: strings.ToLower,
IgnoreMissingFields: true,
})
_, err = db.Exec(ctx, "CREATE TABLE user (id INT PRIMARY KEY, name TEXT")
if err != nil {
log.Fatal(err)
}
}
func (*DB) Begin ¶
Begin starts a transaction. The default isolation level is dependent on the driver.
The provided context is used until the transaction is committed or rolled back. If the context is canceled, the transaction will roll back. Tx.Commit will return an error if the context provided to BeginTx is canceled.
Begin uses default options; to specify custom options, use DB.BeginTx
Example ¶
package main
import (
"context"
"log"
"github.com/rfberaldo/sqlz"
)
var (
db *sqlz.DB
ctx = context.Background()
)
func main() {
tx, err := db.Begin(ctx)
if err != nil {
log.Fatal(err)
}
// Rollback will be ignored if tx has been committed later in the function,
// remember to return early if there is an error.
defer tx.Rollback()
args := map[string]any{"status": "paid", "id": 37}
_, err = tx.Exec(ctx, "UPDATE user SET status = :status WHERE id = :id", args)
if err != nil {
log.Fatal(err)
return
}
if err := tx.Commit(); err != nil {
log.Fatalf("unable to commit: %v", err)
}
}
func (*DB) BeginTx ¶
BeginTx starts a transaction.
The provided context is used until the transaction is committed or rolled back. If the context is canceled, the transaction will roll back. Tx.Commit will return an error if the context provided to BeginTx is canceled.
The provided [TxOptions] is optional and may be nil if defaults should be used. If a non-default isolation level is used that the driver doesn't support, an error will be returned.
Example ¶
package main
import (
"context"
"database/sql"
"log"
"github.com/rfberaldo/sqlz"
)
var (
db *sqlz.DB
ctx = context.Background()
)
func main() {
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
if err != nil {
log.Fatal(err)
}
// Rollback will be ignored if tx has been committed later in the function,
// remember to return early if there is an error.
defer tx.Rollback()
args := map[string]any{"status": "paid", "id": 37}
_, err = tx.Exec(ctx, "UPDATE user SET status = :status WHERE id = :id", args)
if err != nil {
log.Fatal(err)
return
}
if err := tx.Commit(); err != nil {
log.Fatalf("unable to commit: %v", err)
}
}
func (*DB) ClearStmtCache ¶ added in v0.3.2
func (db *DB) ClearStmtCache()
ClearStmtCache clears the prepared statement cache. This is useful when the database schema has changed and cached statements may no longer be valid.
func (*DB) Exec ¶
Exec executes a query without returning any rows.
The args are for any placeholder parameters in the query, the default placeholder depends on the driver.
Named queries works for all drivers, allowing the use of struct field names or map keys as placeholders (e.g. :id, :name), rather than having to refer to parameters positionally.
Example ¶
package main
import (
"context"
"log"
"github.com/rfberaldo/sqlz"
)
var (
db *sqlz.DB
ctx = context.Background()
)
func main() {
id := 42
result, err := db.Exec(ctx, "UPDATE balances SET balance = balance + 10 WHERE user_id = ?", id)
if err != nil {
log.Fatal(err)
}
rows, err := result.RowsAffected()
if err != nil {
log.Fatal(err)
}
if rows != 1 {
log.Fatalf("expected to affect 1 row, affected %d", rows)
}
}
Example (Batch_insert) ¶
package main
import (
"context"
"log"
"time"
"github.com/rfberaldo/sqlz"
)
var (
db *sqlz.DB
ctx = context.Background()
)
func main() {
type User struct {
Username string
CreatedAt time.Time
}
users := []User{
{"john", time.Now()},
{"alice", time.Now()},
{"rob", time.Now()},
{"brian", time.Now()},
}
_, err := db.Exec(ctx, "INSERT INTO user (username, created_at) VALUES (:username, :created_at)", users)
if err != nil {
log.Fatal(err)
}
}
func (*DB) Pool ¶
Pool return the underlying sql.DB.
Example ¶
package main
import (
"context"
"github.com/rfberaldo/sqlz"
)
var db *sqlz.DB
func main() {
db.Pool().SetMaxOpenConns(10)
db.Pool().SetMaxIdleConns(4)
}
func (*DB) Query ¶
Query executes a query that can return multiple rows. Any errors are deferred until Scanner.Err or Scanner.Scan is called.
The args are for any placeholder parameters in the query, the default placeholder depends on the driver.
Named queries works for all drivers, allowing the use of struct field names or map keys as placeholders (e.g. :id, :name), rather than having to refer to parameters positionally.
Example ¶
package main
import (
"context"
"fmt"
"log"
"github.com/rfberaldo/sqlz"
)
var (
db *sqlz.DB
ctx = context.Background()
)
func main() {
var names []string
err := db.Query(ctx, "SELECT name FROM user WHERE age > ?", 27).Scan(&names)
if err != nil {
log.Fatal(err)
}
fmt.Printf("%+v", names)
}
Example (In_clause) ¶
package main
import (
"context"
"fmt"
"log"
"github.com/rfberaldo/sqlz"
)
var (
db *sqlz.DB
ctx = context.Background()
)
func main() {
var names []string
ages := []int{27, 28, 29}
err := db.
Query(ctx, "SELECT name FROM user WHERE age IN (?)", ages).
Scan(&names)
if err != nil {
log.Fatal(err)
}
fmt.Printf("%+v", names)
}
Example (Named) ¶
package main
import (
"context"
"fmt"
"log"
"github.com/rfberaldo/sqlz"
)
var (
db *sqlz.DB
ctx = context.Background()
)
func main() {
type Params struct {
Age int
}
var names []string
params := Params{Age: 27} // or map[string]any{"age": 27}
err := db.
Query(ctx, "SELECT name FROM user WHERE age > :age", params).
Scan(&names)
if err != nil {
log.Fatal(err)
}
fmt.Printf("%+v", names)
}
Example (Named_in_clause) ¶
package main
import (
"context"
"fmt"
"log"
"github.com/rfberaldo/sqlz"
)
var (
db *sqlz.DB
ctx = context.Background()
)
func main() {
type Params struct {
Ages []int
}
var names []string
params := Params{Ages: []int{27, 28, 29}}
err := db.
Query(ctx, "SELECT name FROM user WHERE age IN (:ages)", params).
Scan(&names)
if err != nil {
log.Fatal(err)
}
fmt.Printf("%+v", names)
}
func (*DB) QueryRow ¶
QueryRow executes a query that is expected to return at most one row. Any errors are deferred until Scanner.Err or Scanner.Scan is called, if the query selects no rows, it returns sql.ErrNoRows.
The args are for any placeholder parameters in the query, the default placeholder depends on the driver.
Named queries works for all drivers, allowing the use of struct field names or map keys as placeholders (e.g. :id, :name), rather than having to refer to parameters positionally.
Example ¶
package main
import (
"context"
"log"
"time"
"github.com/rfberaldo/sqlz"
)
var (
db *sqlz.DB
ctx = context.Background()
)
func main() {
type User struct {
Username string
CreatedAt time.Time
}
id := 42
var user User
err := db.
QueryRow(ctx, "SELECT username, created_at FROM user WHERE id = ?", id).
Scan(&user)
switch {
case sqlz.IsNotFound(err):
log.Printf("no user with id %d\n", id)
case err != nil:
log.Fatalf("query error: %v\n", err)
default:
log.Printf("username is %q, account created on %s\n", user.Username, user.CreatedAt)
}
}
type Options ¶
type Options struct {
// Bind is the placeholder the database driver uses, this should be blank for most users.
// Default is based on driver.
Bind parser.Bind
// StructTag is the reflection tag that will be used to map struct fields.
// Default is "db".
StructTag string
// FieldNameTransformer transforms a struct field name when the struct tag is not found.
// Default is [ToSnakeCase].
FieldNameTransformer func(string) string
// IgnoreMissingFields causes the scanner to ignore missing struct fields
// rather than returning an error.
// Default is false.
IgnoreMissingFields bool
// StatementCacheCapacity sets the maximum number of cached statements,
// if it's zero, prepared statement caching is completely disabled.
// Note that each statement may be prepared on each connection in the pool.
// Default is 16.
StatementCacheCapacity int
}
Options are optional configs for sqlz.
type Scanner ¶ added in v0.3.0
type Scanner struct {
// contains filtered or unexported fields
}
Scanner is the result of calling DB.Query or DB.QueryRow.
func (*Scanner) Close ¶ added in v0.3.0
Close closes Scanner, preventing further enumeration, and returning the connection to the pool. Close is idempotent and does not affect the result of Scanner.Err.
func (*Scanner) Err ¶ added in v0.3.0
Err returns the error, if any, that was encountered while running the query or during iteration. Err may be called after an explicit or implicit Scanner.Close.
func (*Scanner) NextRow ¶ added in v0.3.0
NextRow prepares the next result row for reading with Scanner.ScanRow. It returns true on success, or false if there is no next result row or an error happened while preparing it. Scanner.Err should be consulted to distinguish between the two cases.
Every call to Scanner.ScanRow, even the first one, must be preceded by a NextRow.
type Tx ¶
type Tx struct {
// contains filtered or unexported fields
}
Tx is an in-progress database transaction, representing a single connection.
A transaction must end with a call to Tx.Commit or Tx.Rollback, or else the connection will be locked.
After a call to Tx.Commit or Tx.Rollback, all operations on the transaction fail with sql.ErrTxDone.
func (*Tx) Commit ¶
Commit commits the transaction.
If Commit fails, then all queries on the Tx should be discarded as invalid.
func (*Tx) Exec ¶
Exec executes a query without returning any rows.
The args are for any placeholder parameters in the query, the default placeholder depends on the driver.
Named queries works for all drivers, allowing the use of struct field names or map keys as placeholders (e.g. :id, :name), rather than having to refer to parameters positionally.
func (*Tx) Query ¶
Query executes a query that can return multiple rows. Any errors are deferred until Scanner.Err or Scanner.Scan is called.
The args are for any placeholder parameters in the query, the default placeholder depends on the driver.
Named queries works for all drivers, allowing the use of struct field names or map keys as placeholders (e.g. :id, :name), rather than having to refer to parameters positionally.
func (*Tx) QueryRow ¶
QueryRow executes a query that is expected to return at most one row. Any errors are deferred until Scanner.Err or Scanner.Scan is called, if the query selects no rows, it returns sql.ErrNoRows.
The args are for any placeholder parameters in the query, the default placeholder depends on the driver.
Named queries works for all drivers, allowing the use of struct field names or map keys as placeholders (e.g. :id, :name), rather than having to refer to parameters positionally.
Source Files
Directories