README

zdb exposes some database helpers; all of this is built on top of sqlx, but makes certain things a bit easier.

Right now only PostgreSQL and SQLite are supported. Adding MariaDB etc. wouldn't be hard, but I don't use it myself so didn't bother adding (and testing!) it. Just need someone to write a patch 😅

Note: compile with CGO_ENABLED=0 if you're not using cgo, otherwise it will depend on the go-sqlite3 (which uses cgo).


It exposes a DB interface which can be used for both database connections and transactions; with the function arguments and return values omitted it looks like:

type DB interface {
    ExecContext()
    GetContext()
    QueryRowxContext()
    QueryxContext()
    SelectContext()

    Rebind()
    DriverName()
}

It doesn't expose everything sqlx has because I find that for >95% of the use cases, just this is enough. I'm not against adding more methods though, just report a use case that's hard to solve otherwise.

Use zdb.Connect() to connect to a database; It's not required to use this (sqlx.Connect() will work fine as well), but it has some handy stuff like automatic schema creation and migrations. See godoc for the full details on that.


To run queries in a transaction you can use zdb.TX():

func Example(ctx context.Context) {
    err := zdb.TX(ctx, func(ctx context.Context, tx zdb.DB) error {
        _, err := db.ExecContext(..)
        if err != nil {
            return err
        }

        // ... more queries
    })
    if err != nil {
        return fmt.Errorf("zdb.Example: %w", err)
    }
}

The transaction will be rolled back if an error is returned, or commited if it doesn't.

It's assumed that the context has a database value:

db, err := zdb.Connect(...)
ctx = zdb.With(ctx, db)

// And get it again with db := zdb.MustGet(ctx)

I know some people are against storing the database on the context like this, but I don't really see the problem. You don't need to store it on the context; you'll just have to add a call to zdb.With():

func Example(db zdb.DB) {  // or *sqlx.DB
    err := zdb.TX(zdb.With(ctx, db), func(...) {
        ...
    })
}

You can also start a transaction with zdb.Begin(), but I find the TX() wrapper more useful in most cases:

txctx, tx, err := zdb.Begin(ctx)
if err != nil {
    return err
}
defer tx.Rollback()

// Do stuff with tx...

err := tx.Commit()
if err != nil {
    return err
}

Because it just passes around zdb.DB you can pass this to functions that accept zdb.DB, so they will operate on the transaction.

Because zdb.DB satisfies both the sqlx.DB and sqlx.Tx structs, you can pass this around to your functions if they accept zdb.DB instead of *sqlx.DB.


zdb.Dump() and zdb.DumpString() are two rather useful helper functions: they get the output of any SQL query in a table, similar to what you get on the sqlite3 or psql commandline. This is rather useful for debugging and tests:

want := "" +
    `site    day                            browser  version  count   count_unique  event`+
    `1       2019-08-31 00:00:00 +0000 UTC  Firefox  68.0     1       0             0`

out := zdb.DumpString(ctx, `select * from table`)

if d := ztest.Diff(out, want); d != "" {
    t.Error(d)
}

This will panic() on errors. Again, it's only intended for debugging and tests, and omitting error returns makes it a bit smoother to use.

The zdb.ApplyPlaceholders() function will replace ? and $1 with the actual values. This is intended to make copying long-ish queries to the psql CLI for additional debugging/testing easier. This is not intended for any serious use and is not safe against malicious input.

fmt.Println(zdb.ApplyPlaceholders(
    `select * from users where site=$1 and state=$2`,
    1, "active"))

// Output: select * from users where site=1 and state='active'

The zdb/bulk package makes it easier to bulk insert values:

ins := bulk.NewInsert(ctx, "table", []string{"col1", "col2", "col3"})
for _, v := range listOfValues {
    ins.Values(v.Col1, v.Col2, v.Col3)
}

err := ins.Finish()

This won't naïvely group everything in one query; after more than 998 parameters it will construct an SQL query and send it to the server. 998 was chosen because that's the default SQLite limit. You get the error(s) back with Finish().

Note this isn't run in a transaction by default; start a transaction yourself if you want it.


There's a few types as well:

Bool to store text such as "true", "on", "1" as boolean true. This is always stored as an int for best SQL compatibility.

Ints, Floats, and Strings all store a slice as a comma-separated varchar. If you use just a single database engine which supports arrays or JSON (like PostgreSQL) then that's probably a better option, but for simpler cases this makes some things easier.

Note Strings does not escape commas in existing strings; don't use it for arbitrary text.

Expand ▾ Collapse ▴

Documentation

Index

Constants

const Date = "2006-01-02 15:04:05"

Date format for SQL.


Variables

This section is empty.

Functions

func ApplyPlaceholders

func ApplyPlaceholders(query string, args ...interface{}) string

ApplyPlaceholders replaces parameter placeholders in query with the values.

This is ONLY for printf-debugging, and NOT for actual usage. Security was NOT a consideration when writing this. Parameters in SQL are sent separately over the write and are not interpolated, so it's very different.

This supports ? placeholders and $1 placeholders *in order* ($\d is simply replace with ?).

func Begin

func Begin(ctx context.Context) (context.Context, *sqlx.Tx, error)

Begin a new transaction.

The returned context is a copy of the original with the DB replaced with a transaction. The same transaction is also returned directly.

func Connect

func Connect(opts ConnectOptions) (*sqlx.DB, error)

Connect to database.

func Dump

func Dump(ctx context.Context, out io.Writer, query string, args ...interface{})

Dump the results of a query to a writer in an aligned table. This is a convenience function intended mostly for testing/debugging.

Combined with ztest.Diff() it can be an easy way to test the database state.

You can add some special sentinel values in the args to control the output (they're not sent as parameters to the DB):

DumpVertical   Show vertical output instead of horizontal columns.
DumpQuery      Show the query with placeholders substituted.
DumpExplain    Show the results of EXPLAIN (or EXPLAIN ANALYZE for PostgreSQL).

func DumpString

func DumpString(ctx context.Context, query string, args ...interface{}) string

DumpString is like Dump(), but returns the result as a string.

func ErrNoRows

func ErrNoRows(err error) bool

ErrNoRows reports if this error is sql.ErrNoRows.

func ErrUnique

func ErrUnique(err error) bool

ErrUnique reports if this error reports a UNIQUE constraint violation.

This is the cgo version which works for PostgreSQL and SQLite.

func ListTables

func ListTables(ctx context.Context) ([]string, error)

ListTables lists all tables

func PgSQL

func PgSQL(db DB) bool

PgSQL reports if this database connection is to PostgreSQL.

func Query

func Query(ctx context.Context, query string, arg interface{}, conds ...bool) (string, []interface{}, error)

Query creates a new query.

Everything between {{..}} is parsed as a conditional; for example {{query}} will only be added if the nth conds parameter is true.

SQL parameters can be added as :name; sqlx's BindNamed is used.

func QueryExec

func QueryExec(ctx context.Context, dest interface{}, query string, arg interface{}, conds ...bool) (sql.Result, error)

QueryExec is like Query(), but will also run ExecContext().

func QueryGet

func QueryGet(ctx context.Context, dest interface{}, query string, arg interface{}, conds ...bool) error

QueryGet is like Query(), but will also run GetContext() and scan in to desc.

func QuerySelect

func QuerySelect(ctx context.Context, dest interface{}, query string, arg interface{}, conds ...bool) error

QuerySelect is like Query(), but will also run SelectContext() and scan in to desc.

func TX

func TX(ctx context.Context, fn func(context.Context, DB) error) error

TX runs the given function in a transaction.

The context passed to the callback has the DB replaced with a transaction.

The transaction is comitted if the fn returns nil, or will be rolled back if it's not.

This is just a more convenient wrapper for Begin().

func With

func With(ctx context.Context, db DB) context.Context

With returns a copy of the context with the DB instance.

Types

type Bool

type Bool bool

Bool converts various types to a boolean.

It's always stored as an integer in the database (the only cross-platform way in SQL).

Supported types:

bool
int* and float*     0 or 1
[]byte and string   "1", "true", "on", "0", "false", "off"
nil                 defaults to false

func (Bool) MarshalJSON

func (b Bool) MarshalJSON() ([]byte, error)

MarshalJSON converts the data to JSON.

func (Bool) MarshalText

func (b Bool) MarshalText() ([]byte, error)

MarshalText converts the data to a human readable representation.

func (*Bool) Scan

func (b *Bool) Scan(src interface{}) error

Scan converts the data from the DB.

func (*Bool) UnmarshalJSON

func (b *Bool) UnmarshalJSON(text []byte) error

UnmarshalJSON converts the data from JSON.

func (*Bool) UnmarshalText

func (b *Bool) UnmarshalText(text []byte) error

UnmarshalText parses text in to the Go data structure.

func (Bool) Value

func (b Bool) Value() (driver.Value, error)

Value converts a bool type into a number to persist it in the database.

type ConnectOptions

type ConnectOptions struct {
	Connect string // Connect string.
	Schema  []byte // Database schema to create on startup.
	Migrate *Migrate

	// ConnectHook for sqlite3.SQLiteDriver; mainly useful to add your own
	// functions:
	//
	//    opts.SQLiteHook = func(c *sqlite3.SQLiteConn) error {
	//        return c.RegisterFunc("percent_diff", func(start, final float64) float64 {
	//            return (final - start) / start * 100
	//        }, true)
	//    }
	//
	// It'll automatically register and connect to a new "sqlite3_zdb" driver.
	SQLiteHook func(*sqlite3.SQLiteConn) error
}

type DB

type DB interface {
	// Execute a query without returning any result.
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

	// Get a simple single-column value. dest needs to be a pointer to a
	// primitive.
	//
	// Returns sql.ErrNoRows if there are no rows.
	GetContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error

	// Select multiple rows, dest needs to be a pointer to a slice.
	//
	// Returns nil if there are no rows.
	SelectContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error

	// Query one row.
	//
	// Returning row is never nil; use .Err() to check for errors. Row.Scan()
	// will return sql.ErrNoRows if there are no rows.
	QueryRowxContext(ctx context.Context, query string, args ...interface{}) *sqlx.Row

	// Query one or more rows.
	//
	// Returning rows is never nil; use .Err() to check for errors. Row
	QueryxContext(ctx context.Context, query string, args ...interface{}) (*sqlx.Rows, error)

	// Rebind :named to placeholders appropriate for this SQL connection.
	BindNamed(query string, arg interface{}) (newquery string, args []interface{}, err error)

	// Rebind ? to placeholders appropriate for this SQL connection.
	Rebind(query string) string

	// SQL driver name for this connection.
	DriverName() string
}

DB wraps sqlx.DB so we can add transactions.

func Get

func Get(ctx context.Context) (DB, bool)

Get the DB from the context.

func MustGet

func MustGet(ctx context.Context) DB

MustGet gets the DB from the context, panicking if there is none.

func NewExplainDB

func NewExplainDB(db DB, out io.Writer, filter string) DB

NewExplainDB returns a wrapper DB connection that will print all queries with their EXPLAINs to out.

Only queries which contain the string in filter are shown. Use an empty string to show everything.

Because EXPLAIN will actually run the queries this is quite a significant performance impact. Note that data modification statements are *also* run twice!

type DumpArg

type DumpArg int
const (
	DumpVertical DumpArg = iota
	DumpQuery
	DumpExplain
)

type Floats

type Floats []float64

Floats stores a slice of []float64 as a comma-separated string.

func (Floats) MarshalText

func (l Floats) MarshalText() ([]byte, error)

MarshalText converts the data to a human readable representation.

func (*Floats) Scan

func (l *Floats) Scan(v interface{}) error

Scan converts the data from the DB.

func (Floats) String

func (l Floats) String() string

func (*Floats) UnmarshalText

func (l *Floats) UnmarshalText(v []byte) error

UnmarshalText parses text in to the Go data structure.

func (Floats) Value

func (l Floats) Value() (driver.Value, error)

Value determines what to store in the DB.

type Ints

type Ints []int64

Ints stores a slice of []int64 as a comma-separated string.

func (Ints) MarshalText

func (l Ints) MarshalText() ([]byte, error)

MarshalText converts the data to a human readable representation.

func (*Ints) Scan

func (l *Ints) Scan(v interface{}) error

Scan converts the data from the DB.

func (Ints) String

func (l Ints) String() string

func (*Ints) UnmarshalText

func (l *Ints) UnmarshalText(v []byte) error

UnmarshalText parses text in to the Go data structure.

func (Ints) Value

func (l Ints) Value() (driver.Value, error)

Value determines what to store in the DB.

type Migrate

type Migrate struct {
	DB           DB
	Which        []string                  // List of migrations to run.
	Migrations   map[string][]byte         // List of all migrations, for production.
	GoMigrations map[string]func(DB) error // Go migration functions.
	MigratePath  string                    // Path to migrations, for dev.
}

func NewMigrate

func NewMigrate(db DB, which []string, mig map[string][]byte, gomig map[string]func(DB) error, path string) *Migrate

func (Migrate) Check

func (m Migrate) Check() error

Check if there are pending migrations and zlog.Error() if there are.

func (Migrate) List

func (m Migrate) List() (haveMig, ranMig []string, err error)

Get a list of all migrations we know about, and all migrations that have already been run.

func (Migrate) Run

func (m Migrate) Run(which ...string) error

Run a migration, or all of then if which is "all" or "auto".

func (Migrate) Schema

func (m Migrate) Schema(n string) (string, error)

Schema of a migration by name.

type Strings

type Strings []string

Strings stores a slice of []string as a comma-separated string.

Note this only works for simple strings (e.g. enums), it DOES NOT ESCAPE COMMAS, and you will run in to problems if you use it for arbitrary text.

You're probably better off using e.g. arrays in PostgreSQL or JSON in SQLite, if you can. This is intended just for simple cross-SQL-engine use cases.

func (Strings) MarshalText

func (l Strings) MarshalText() ([]byte, error)

MarshalText converts the data to a human readable representation.

func (*Strings) Scan

func (l *Strings) Scan(v interface{}) error

Scan converts the data from the DB.

func (Strings) String

func (l Strings) String() string

func (*Strings) UnmarshalText

func (l *Strings) UnmarshalText(v []byte) error

UnmarshalText parses text in to the Go data structure.

func (Strings) Value

func (l Strings) Value() (driver.Value, error)

Value determines what to store in the DB.

Directories

Path Synopsis
bulk Package bulk provides helpers for bulk SQL operations.