yesql

package module
v0.2.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Feb 20, 2023 License: MIT Imports: 8 Imported by: 0

README

yesql

yesql is a Go library that adds convenience features to the standard library package database/sql. It's specifically designed for writing raw SQL queries, with a focus on providing greater efficiency and ease-of-use.

yesql leans on the standard library, keeping the same API, making it an ideal drop-in replacement for database/sql.

It's a WIP and currently provides the following features:

  • Named arguments (e.g. SELECT * FROM foo WHERE id = @ID)
  • Templates for query building (cached and thread-safe)
  • Statement logging
  • Same API as database/sql
The Elevator Pitch™

yesql is like a child born from the union of database/sql and text/templates.

Quick start

Start by opening a connection to a database and initializing a database driver:

package foo

import (
    "github.com/izolate/yesql"
    _ "github.com/lib/pq"
)

func main() {
    db, err := yesql.Open("postgres", "host=localhost user=foo sslmode=disable")
    if err != nil {
        panic(err)
    }
}
Exec / ExecContext

You can use the Exec function to execute a query without returning data. Named parameters (@Foo) allow you to bind arguments to map (or struct) fields without the risk of SQL injection.

type Book struct {
    ID     string
    Title  string
    Author string
}

func InsertBook(b Book) error {
    q := `INSERT INTO users (id, title, author) VALUES (@ID, @Title, @Author)`
    _, err := db.Exec(q, b)
    return err
}

🐸 NOTE: Named parameters only work with exported fields.

Query / QueryContext

Use Query to execute a query and return data. Templates allow you to perform complex logic without string concatenation or query building.

The templates alter the final SQL statement based on the input provided.

type BookSearch struct {
    Author string    
    Title  string
    Genre  string
}

const sqlSearchBooks = `
SELECT * FROM books
WHERE author = @Author
{{if .Title}}AND title ILIKE @Title{{end}}
{{if .Genre}}AND genre = @Genre{{end}}
`

func SearchBooks(s BookSearch) ([]Book, error) {
    rows, err := db.Query(sqlSearchBooks, s)
    if err != nil {
        return nil, err
    }
    books := []Book{}
    for rows.Next() {
        var b Book
        if err := rows.Scan(&b.ID, &b.Title, &b.Author, &b.Genre); err != nil {
            return nil, err
        }
        books = append(books, b)
    }
    return books, nil
}

Positional scanning is inflexible. Instead, use db struct tags and rows.ScanStruct() to scan into a struct:

type Book struct {
    ID     string `db:"id"`
    Title  string `db:"title"`
    Author string `db:"author"`
}

func SearchBooks(s BookSearch) ([]Book, error) {
    rows, err := db.Query(sqlSearchBooks, s)
    if err != nil {
        return nil, err
    }
    books := []Book{}
    for rows.Next() {
        var b Book
        if err := rows.ScanStruct(&b); err != nil {
            return nil, err
        }
        books = append(books, b)
    }
    return books, nil
}

Configuration

You can easily configure yesql using a list of functional config setters, which are accepted by all entry points. For instance, to disable statement logging, you can pass in the OptQuiet setter as follows:

db, err := yesql.Open("postgres", "host=localhost user=foo sslmode=disable", yesql.OptQuiet())

This will ensure that statement logs are not printed during execution.

Feature checklist

  • Templated SQL statements
  • Named arguments (bindvars)
  • Statement logging
  • Query tracing
  • Struct scanning
  • Unicode support
  • Postgres support
  • Prepared statements

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func ExecContext 

func ExecContext(
	db Execer,
	ctx context.Context,
	query string,
	data any,
	cfg *Config,
) (sql.Result, error)

ExecContext executes a query without returning any rows, e.g. an INSERT. The data object is a map/struct for any placeholder parameters in the query.

func OptBindvar added in v0.2.0 

func OptBindvar(p bindvar.Parser) func(c *Config)

OptBindvar sets the bindvar parser.

func OptDriver added in v0.2.0 

func OptDriver(s string) func(c *Config)

OptDriver sets the driver name.

func OptQuiet added in v0.2.0 

func OptQuiet() func(c *Config)

OptQuiet disables logging.

func OptTemplate added in v0.2.0 

func OptTemplate(e template.Executer) func(c *Config)

OptTemplate sets the template executer.

Types

type Config added in v0.2.0 

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

Config stores runtime config for yesql.

func NewConfig added in v0.2.0 

func NewConfig(opts ...func(*Config)) *Config

NewConfig initializes a config with supplied options, or defaults.

type DB 

type DB struct {
	*sql.DB
	// contains filtered or unexported fields
}

func MustOpen 

func MustOpen(driver, dsn string, opts ...func(*Config)) *DB

MustOpen opens a database specified by its database driver name and a driver-specific data source name, and panics on any errors.

func New 

func New(db *sql.DB, opts ...func(*Config)) (*DB, error)

New instantiates yesql with an existing database connection.

func Open 

func Open(driver, dsn string, opts ...func(*Config)) (*DB, error)

Open opens a database specified by its database driver name and a driver-specific data source name, usually consisting of at least a database name and connection information.

Most users will open a database via a driver-specific connection helper function that returns a *DB. No database drivers are included in the Go standard library. See https://golang.org/s/sqldrivers for a list of third-party drivers.

Open may just validate its arguments without creating a connection to the database. To verify that the data source name is valid, call Ping.

The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.

func (*DB) Begin 

func (db *DB) Begin() (*Tx, error)

Begin starts a transaction. The default isolation level is dependent on the driver.

func (*DB) BeginTx 

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

BeginTx starts a transaction.

The provided context is used until the transaction is committed or rolled back. If the context is canceled, the sql package will roll back the transaction. 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.

func (*DB) Exec 

func (db *DB) Exec(query string, data interface{}) (sql.Result, error)

Exec executes a query without returning any rows, e.g. an INSERT. The data object is a map/struct for any placeholder parameters in the query.

func (*DB) ExecContext 

func (db *DB) ExecContext(ctx context.Context, query string, data interface{}) (sql.Result, error)

ExecContext executes a query without returning any rows, e.g. an INSERT. The data object is a map/struct for any placeholder parameters in the query.

func (*DB) Query 

func (db *DB) Query(query string, data interface{}) (*Rows, error)

Query executes a query that returns rows, typically a SELECT. The data object is a map/struct for any placeholder parameters in the query.

func (*DB) QueryContext 

func (db *DB) QueryContext(ctx context.Context, query string, data interface{}) (*Rows, error)

QueryContext executes a query that returns rows, typically a SELECT. The data object is a map/struct for any placeholder parameters in the query.

func (*DB) QueryRow 

func (db *DB) QueryRow(query string, data interface{}) *Row

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

QueryRow uses context.Background internally; to specify the context, use QueryRowContext.

func (*DB) QueryRowContext 

func (db *DB) QueryRowContext(ctx context.Context, query string, data interface{}) *Row

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

type Execer 

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

type ExecerQueryer 

type ExecerQueryer interface {
	Execer
	Queryer
}

ExecerQueryer is a union interface comprising Execer and Queryer.

type Queryer 

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

type Row 

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

Row is the result of calling QueryRow to select a single row. It's a re-implementation of *sql.Row because the standard type is inaccessible.

func QueryRowContext 

func QueryRowContext(
	db Queryer,
	ctx context.Context,
	query string,
	data any,
	cfg *Config,
) *Row

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*Row) Err 

func (r *Row) Err() error

Err provides a way for wrapping packages to check for query errors without calling Scan. Err returns the error, if any, that was encountered while running the query. If this error is not nil, this error will also be returned from Scan.

func (*Row) Scan 

func (r *Row) Scan(dest ...interface{}) error

Scan copies the columns from the matched row into the values pointed at by dest. See the documentation on Rows.Scan for details. If more than one row matches the query, Scan uses the first row and discards the rest. If no row matches the query, Scan returns ErrNoRows.

func (*Row) ScanStruct 

func (r *Row) ScanStruct(dest interface{}) error

ScanStruct copies the columns in the current row into the values pointed at by the dest struct.

ScanStruct is like Rows.Scan, but doesn't rely on positional scanning, and instead scans into a struct based on the column names and the db struct tags, e.g. Foo string `db:"foo"`.

type Rows 

type Rows struct {
	*sql.Rows
}

Rows is the result of a query. Its cursor starts before the first row of the result set. Use Next to advance from row to row.

func QueryContext 

func QueryContext(
	db Queryer,
	ctx context.Context,
	query string,
	data any,
	cfg *Config,
) (*Rows, error)

QueryContext executes a query that returns rows, typically a SELECT. The data object is a map/struct for any placeholder parameters in the query.

func (*Rows) ScanStruct 

func (rs *Rows) ScanStruct(dest interface{}) error

ScanStruct copies the columns in the current row into the values pointed at by the dest struct.

ScanStruct is like Rows.Scan, but doesn't rely on positional scanning, and instead scans into a struct based on the column names and the db struct tags, e.g. Foo string `db:"foo"`.

type Tx 

type Tx struct {
	*sql.Tx
	// contains filtered or unexported fields
}

Tx is an in-progress database transaction.

A transaction must end with a call to Commit or Rollback.

After a call to Commit or Rollback, all operations on the transaction fail with ErrTxDone.

The statements prepared for a transaction by calling the transaction's Prepare or Stmt methods are closed by the call to Commit or Rollback.

func (*Tx) Exec 

func (tx *Tx) Exec(query string, data interface{}) (sql.Result, error)

Exec executes a query without returning any rows. The data object is a map/struct for any placeholder parameters in the query.

func (*Tx) ExecContext 

func (tx *Tx) ExecContext(ctx context.Context, query string, data interface{}) (sql.Result, error)

ExecContext executes a query that doesn't return rows. The data object is a map/struct for any placeholder parameters in the query.

func (*Tx) Query 

func (tx *Tx) Query(ctx context.Context, query string, data interface{}) (*Rows, error)

Query executes a query that returns rows, typically a SELECT. The data object is a map/struct for any placeholder parameters in the query.

func (*Tx) QueryContext 

func (tx *Tx) QueryContext(ctx context.Context, query string, data interface{}) (*Rows, error)

QueryContext executes a query that returns rows, typically a SELECT. The data object is a map/struct for any placeholder parameters in the query.

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.