sqlz

README

sqlz

sqlz is an extremely simple alternative to sqlx, convenient helper for working with database/sql.

Usage

Stmt

The common use cases of Stmt are as follows:

package main

import (
        "context"
        "database/sql"
        "fmt"

        _ "github.com/go-sql-driver/mysql"
        "github.com/go-tk/sqlz"
)

const createTable = `
DROP TABLE IF EXISTS person;
CREATE TABLE person (
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    age INT NOT NULL
);
`

type Person struct {
        FirstName string
        LastName  string
        Age       int
}

func main() {
        db, err := sql.Open("mysql", "root:password@tcp(127.0.0.1:3306)/test?multiStatements=true")
        if err != nil {
                panic(err)
        }
        _, err = db.Exec(createTable)
        if err != nil {
                panic(err)
        }

        // 1. Insert Persons in bulk
        {
                stmt := sqlz.NewStmt("INSERT INTO person ( first_name, last_name, age ) VALUES")
                for _, person := range []Person{
                        {"Jason", "Moiron", 12},
                        {"John", "Doe", 9},
                        {"Peter", "Pan", 13},
                } {
                        stmt.Append("( ?, ?, ? ),").Format(person.FirstName, person.LastName, person.Age)
                }
                stmt.Trim(",") // Remove the trailing ','

                if _, err := stmt.Exec(context.Background(), db); err != nil {
                        panic(err)
                }
        }

        // Define a helper function
        selectPerson := func(person *Person) *sqlz.Stmt {
                return sqlz.NewStmt("SELECT").
                        Append("first_name,").Scan(&person.FirstName).
                        Append("last_name,").Scan(&person.LastName).
                        Append("age,").Scan(&person.Age).
                        Trim(","). // Remove the trailing ','
                        Append("FROM person")
        }

        // 2. Get a single Person
        {
                var person Person
                stmt := selectPerson(&person).
                        Append("WHERE").
                        Append("age BETWEEN ? AND ?").Format(12, 13).
                        Append("AND").
                        Append("last_name = ?").Format("Pan").
                        Append("LIMIT 1")

                if err := stmt.QueryRow(context.Background(), db); err != nil {
                        panic(err)
                }
                fmt.Printf("%v\n", person)
                // Output: {Peter Pan 13}
        }

        // 3. Get all Persons
        {
                var temp Person
                stmt := selectPerson(&temp).Append("LIMIT 100")

                var persons []Person
                if err := stmt.Query(context.Background(), db, func() bool {
                        // Be called back for each row
                        persons = append(persons, temp)
                        return true
                }); err != nil {
                        panic(err)
                }
                fmt.Printf("%v\n", persons)
                // Output: [{Jason Moiron 12} {John Doe 9} {Peter Pan 13}]
        }
}

BeginTx

See the comment in the code below:

func DoSomething(ctx context.Context, db *sql.DB) (returnedErr error) {
        tx, closeTx, err := sqlz.BeginTx(ctx, db, nil, &returnedErr)
        if err != nil {
                return err
        }
        defer closeTx() // Automatically call tx.Commit() or tx.Rollback() according to returnedErr
        ...
        if err != nil {
                return err
        }
        ...
        return nil
}

Documentation

Index

• func BeginTx(ctx context.Context, db *sql.DB, txOptions *sql.TxOptions, returnedErr *error) (*sql.Tx, func(), error)
• type Execer
• type Queryer
• type Stmt
  • func NewStmt(sqlFrag string) *Stmt
  • func (s *Stmt) Append(sqlFrag string) *Stmt
  • func (s *Stmt) Exec(ctx context.Context, execer Execer) (sql.Result, error)
  • func (s *Stmt) Format(args ...interface{}) *Stmt
  • func (s *Stmt) Query(ctx context.Context, queryer Queryer, callback func() bool) error
  • func (s *Stmt) QueryRow(ctx context.Context, queryer Queryer) error
  • func (s *Stmt) SQL() string
  • func (s *Stmt) Scan(values ...interface{}) *Stmt
  • func (s *Stmt) Trim(sqlFrag string) *Stmt

Functions

func BeginTx

func BeginTx(ctx context.Context, db *sql.DB, txOptions *sql.TxOptions, returnedErr *error) (*sql.Tx, func(), error)

BeginTx is the convenient version of `DB.BeginTx()`.

Types

type Execer

type Execer interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (result sql.Result, err error)
}

Execer is an interface implemented by `sql.DB` and `sql.Tx`.

type Queryer

type Queryer interface {
	QueryRowContext(ctx context.Context, query string, args ...interface{}) (row *sql.Row)
	QueryContext(ctx context.Context, query string, args ...interface{}) (rows *sql.Rows, err error)
}

Queryer is an interface implemented by `sql.DB` and `sql.Tx`.

type Stmt

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

Stmt represents a SQL statement.

func NewStmt

func NewStmt(sqlFrag string) *Stmt

NewStmt returns a Stmt with the given SQL fragment.

func (*Stmt) Append

func (s *Stmt) Append(sqlFrag string) *Stmt

Append adds the given SQL fragment to the end of the Stmt.

func (*Stmt) Exec

func (s *Stmt) Exec(ctx context.Context, execer Execer) (sql.Result, error)

Exec executes the Stmt.

func (*Stmt) Format

func (s *Stmt) Format(args ...interface{}) *Stmt

Format adds arguments as the input of the Stmt.

func (*Stmt) Query

func (s *Stmt) Query(ctx context.Context, queryer Queryer, callback func() bool) error

Query executes the Stmt as a query to retrieve rows. The given callback will be called for each row retrieved. If the callback returns false, the iteration will be stopped.

func (*Stmt) QueryRow

func (s *Stmt) QueryRow(ctx context.Context, queryer Queryer) error

QueryRow executes the Stmt as a query to retrieve a single row.

func (*Stmt) SQL

func (s *Stmt) SQL() string

SQL returns the underlying SQL to be executed.

func (*Stmt) Scan

func (s *Stmt) Scan(values ...interface{}) *Stmt

Scan adds values as the output of the Stmt.

func (*Stmt) Trim

func (s *Stmt) Trim(sqlFrag string) *Stmt

Trim removes the given SQL fragment from the end of the Stmt.