package module
Version: v0.0.0-...-a6be15a Latest Latest

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

Go to latest
Published: May 12, 2021 License: MIT Imports: 34 Imported by: 11


zdb allows interacting with SQL databases.

Much of this is built on top of sqlx, but that's mostly an implementation detail; in regular usage you shouldn't really have to deal with the sqlx (or database/sql) package interfaces at all. The API/interface of zdb is quite different.

This requires Go 1.16 or newer. It uses the new fs package to load files.

Right now only PostgreSQL and SQLite are supported. Adding MariaDB or other engines 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 😅

Full reference documentation: https://pkg.go.dev/zgo.at/zdb#pkg-index




This section is empty.


View Source
var ErrTransactionStarted = errors.New("transaction already started")

ErrTransactionStarted is returned when a transaction is already started; this can often be treated as a non-fatal error.

View Source
var SchemaFuncMap template.FuncMap

SchemaFuncMap are additional template functions for SchemaTemplate(). Existing functions may be overridden.


func ApplyParams

func ApplyParams(query string, params ...interface{}) string

ApplyParams 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 BeginIsolation

func BeginIsolation(level sql.IsolationLevel) beginOpt

func BeginReadOnly

func BeginReadOnly() beginOpt

func Diff

func Diff(out, want string) string

Diff two strings, ignoring whitespace at the start of a line.

This is useful in tests in combination with zdb.Dump():

got := DumpString(ctx, `select * from factions`)
want := `
    faction_id  name
    1           Peacekeepers
    2           Moya`
if d := Diff(got, want); d != "" {

It normalizes the leading whitespace in want, making "does my database match with what's expected?" fairly easy to test.

func Dump

func Dump(ctx context.Context, out io.Writer, query string, params ...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 params to control the output (they're not sent as parameters to the DB):

DumpLocation   Show location of the Dump() cal.
DumpQuery      Show the query with placeholders substituted.
DumpExplain    Show the results of EXPLAIN (or EXPLAIN ANALYZE for PostgreSQL).
DumpResult     Show the query result (
DumpVertical   Show vertical output instead of horizontal columns.
DumpCSV        Show as CSV.
DumpJSON       Show as an array of JSON objects.
DumpHTML       Show as a HTML table.

func DumpString

func DumpString(ctx context.Context, query string, params ...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 Exec

func Exec(ctx context.Context, query string, params ...interface{}) error

Exec executes a query without returning the result.

This uses Prepare(), and all the documentation from there applies here too.

func Get

func Get(ctx context.Context, dest interface{}, query string, params ...interface{}) error

Get one row, returning sql.ErrNoRows if there are no rows.

This uses Prepare(), and all the documentation from there applies here too.

func InsertID

func InsertID(ctx context.Context, idColumn, query string, params ...interface{}) (int64, error)

InsertID runs a INSERT query and returns the ID column idColumn.

If multiple rows are inserted it will return the ID of the last inserted row. This works for both PostgreSQL and SQLite.

This uses Prepare(), and all the documentation from there applies here too.

func Load

func Load(ctx context.Context, name string) (string, error)

Load a query from the filesystem or embeded files.

Queries are loaded from "db/query/{name}-{driver}.sql" or "db/query/{name}.sql". Every query will have the file name inserted in the first line; for example with "db/query/select-x.sql":

select x from y;

Then the actual query will be:

/* select-x */
select x from y;

This allows identifying queries in logging and statistics such as pg_stat_statements.

Typical usage with Query() is to use "load:name", instead of calling this directly:

zdb.QueryGet(ctx, "load:select-x", &foo, zdb.P{
    "param": "foo",

func NumRows

func NumRows(ctx context.Context, query string, params ...interface{}) (int64, error)

NumRows executes a query and returns the number of affected rows.

This uses Prepare(), and all the documentation from there applies here too.

func Prepare

func Prepare(ctx context.Context, query string, params ...interface{}) (string, []interface{}, error)

Prepare a query for sendoff to the database.

Named parameters (:name) are used if params contains a map or struct; positional parameters (? or $1) are used if it's doesn't. You can add multiple structs or maps, but mixing named and positional parameters is not allowed.

Everything between {{:name ..}} is parsed as a conditional; for example {{:foo query}} will only be added if "foo" from params is true or not a zero type. Conditionals only work with named parameters.

If the query starts with "load:" then it's loaded from the filesystem or embedded files; see Load() for details.

Additional DumpArgs can be added to dump the results of the query to stderr for testing and debugging:

DumpQuery      Show the query
DumpExplain    Show query plain (WILL RUN QUERY TWICE!)
DumpResult     Show the query result (WILL RUN QUERY TWICE!)
DumpVertical   Show results in vertical format.

Running the query twice for a select is usually safe (just slower), but running insert, update, or delete twice may cause problems.

func SchemaTemplate

func SchemaTemplate(driver DriverType, tpl string) ([]byte, error)

SchemaTemplate runs text/template on the database schema to make writing compatible schemas a bit easier.

func Select

func Select(ctx context.Context, dest interface{}, query string, params ...interface{}) error

Select one or more rows; dest needs to be a pointer to a slice.

Returns nil if there are no rows.

This uses Prepare(), and all the documentation from there applies here too.

func StartTest

func StartTest(t *testing.T, opt ...ConnectOptions) context.Context

StartTest starts a new test.

There are three versions of this function: for SQLite, PostgreSQL, and MariaDB. By default the SQLite version is used, which uses a :memory: database.

The PostgreSQL version is compiled with the 'testpg' tag; this will use the "zdb_test" database (will be created if it doesn't exist) and will run every test in a new schema. The standard PG* environment variables (PGHOST, PGPORT) can be used to specify the connection to a PostgreSQL database; see psql(1) for details.

The table (or schema) will be removed when the test ends, but the PostgreSQL zdb_test database is kept for re-use.

The MariaDB version is used with the testmaria tag.

func TX

func TX(ctx context.Context, fn func(context.Context) 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 committed if the fn returns nil, or will be rolled back if it's not.

Multiple TX() calls can be nested, but they all run the same transaction and are comitted only if the outermost transaction returns true.

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

func TestQueries

func TestQueries(t *testing.T, files fs.FS)

TestQueries tests queries in the db/query directory.

for every .sql file you can create a _test.sql file, similar to how Go's testing works; the following special comments are recognized:

-- params     Parameters for the query.
-- want       Expected result.

Everything before the first special comment is run as a "setup". The "-- params" and "-- want" comments can be repeated for multiple tests.


   select * from sites where site_id = :site and created_at > :start

  insert into sites () values (...)

  -- params
  site_id:    1
  created_at: 2020-01-01

  -- want

  -- params

  -- want

func WithDB

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

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


type BulkInsert

type BulkInsert struct {
	Limit uint16
	// contains filtered or unexported fields

BulkInsert inserts as many rows as possible per query we send to the server.

func NewBulkInsert

func NewBulkInsert(ctx context.Context, table string, columns []string) BulkInsert

NewBulkInsert makes a new BulkInsert builder.

func (*BulkInsert) Finish

func (m *BulkInsert) Finish() error

Finish the operation, returning any errors.

func (*BulkInsert) OnConflict

func (m *BulkInsert) OnConflict(c string)

OnConflict sets the "on conflict [..]" part of the query. This needs to include the "on conflict" itself.

func (*BulkInsert) Values

func (m *BulkInsert) Values(values ...interface{})

Values adds a set of values.

type ConnectOptions

type ConnectOptions struct {
	Connect string   // Connect string.
	Create  bool     // Create database if it doesn't exist yet.
	Migrate []string // Migrations to run; nil for none, "all" for all, or a migration name.

	// Will be called for every migration that gets run.
	MigrateLog func(name string)

	// Database files; the following layout is assumed:
	//   Schema       schema-{driver}.sql, schema.sql, or schema.gotxt
	//   Migrations   migrate/{name}-{driver}.sql, migrate/{name}.sql, or migrate/{name}.gotxt
	//   Queries      query/{name}-{driver}.sql, query/{name}.sql, or query/{name}.gotxt
	// It's okay if files are missing; e.g. no migrate directory simply means
	// that it won't attempt to run migrations.
	Files fs.FS

	// In addition to migrations from .sql files, you can run migrations from Go
	// functions. See the documentation on Migrate for details.
	GoMigrations map[string]func(context.Context) error

	// ConnectHook for sqlite3.SQLiteDriver; mainly useful to add your own
	// functions:
	//    opt.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_[addr]"
	// driver; note that DriverName() will now return "sqlite3_zdb_[addr]"
	// instead of "sqlite3"; use zdb.SQLite() to test if a connection is a
	// SQLite one.
	SQLiteHook func(*sqlite3.SQLiteConn) error

type DB

type DB interface {
	DBSQL() *sql.DB
	Driver() DriverType
	Ping(context.Context) error
	Version(context.Context) (Version, error)

	Prepare(ctx context.Context, query string, params ...interface{}) (string, []interface{}, error)
	Load(ctx context.Context, name string) (string, error)

	Exec(ctx context.Context, query string, params ...interface{}) error
	NumRows(ctx context.Context, query string, params ...interface{}) (int64, error)
	InsertID(ctx context.Context, idColumn, query string, params ...interface{}) (int64, error)
	Get(ctx context.Context, dest interface{}, query string, params ...interface{}) error
	Select(ctx context.Context, dest interface{}, query string, params ...interface{}) error
	Query(ctx context.Context, query string, params ...interface{}) (*Rows, error)

	BindNamed(query string, param interface{}) (newquery string, params []interface{}, err error)
	Rebind(query string) string
	DriverName() string
	Close() error

	TX(context.Context, func(context.Context) error) error
	Begin(context.Context, ...beginOpt) (context.Context, DB, error)
	Rollback() error
	Commit() error

DB is an interface to the database; this can be a regular connection, a transaction, or a wrapped connection to add features such as logging.

If this is not a transaction, then Commit() and Rollback() will always return an error. If this is a transaction, then Begin() is a no-op, and Close() will rollback the transaction and close the database connection.

See documentation on the top-level functions for more details on the methods.

func Begin

func Begin(ctx context.Context, opts ...beginOpt) (context.Context, DB, 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.

Nested transactions return the original transaction together with ErrTransactionStarted (which is not a fatal error).

func Connect

func Connect(opt ConnectOptions) (DB, error)

Connect to a database.

The database will be created automatically if the database doesn't exist and Schema is in ConnectOptions. It looks for the following files, in this order:

schema.gotxt           Run zdb.SchemaTemplate first.
schema-{driver}.sql    Driver-specific schema.

This will set the maximum number of open and idle connections to 25 each for PostgreSQL, and 16 and 4 for SQLite, instead of Go's default of 0 and 2.

To change this, you can use:


Several connection parameters are set to different defaults in SQLite:

_journal_mode=wal          Almost always faster with better concurrency,
                           with little drawbacks for most use cases.

_foreign_keys=on           Check FK constraints; by default they're not
                           enforced, which is probably not what you want.

_defer_foreign_keys=on     Delay FK checks until the transaction commit; by
                           default they're checked immediately (if

_case_sensitive_like=on    LIKE is case-sensitive, like PostgreSQL.

_cache_size=-20000         20M cache size, instead of 2M. Can be a
                           significant performance improvement.

You can still use "?_journal_mode=something_else" in the connection string to set something different.

For details on the connection string, see the documentation for go-sqlite3 and pq: https://github.com/mattn/go-sqlite3/ https://github.com/lib/pq

func GetDB

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

GetDB gets the DB from the context.

func MustGetDB

func MustGetDB(ctx context.Context) DB

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

func NewLogDB

func NewLogDB(db DB, out io.Writer, logWhat DumpArg, filter string) DB

NewLogDB returns a DB wrapper to log queries, query plans, and query results.

If Filter is not an empty string then only queries containing the text are logged. Use an empty string to log everything.

If LogQuery is enabled then the query text will be logged, if LogExplain is enabled then the query plan will be logged, and if LogResult is enabled then the query result will be logged.

Only LogQuery will be set if opts is nil,

WARNING: printing the result means the query will be run twice, which is a significant performance impact and DATA MODIFICATION STATEMENTS ARE ALSO RUN TWICE. Some SQL engines may also run the query on EXPLAIN (e.g. PostgreSQL does).

func Unwrap

func Unwrap(db DB) DB

Unwrap this database, removing any of the zdb wrappers and returning the underlying sqlx.DB or sqlx.Tx.

To wrap a zdb.DB object embed the zdb.DB interface, which contains the parent DB connection.

The Unwrap() method is expected to return the parent DB.

Then implement override whatever you want; usually you will want to implement the dbImpl interface, which contains the methods that actually interact with the database. All the DB methods call this under the hood. This way you don't have to wrap all the methods on DB, but just five.

In Begin() you will want to return a new wrapped DB instance with the transaction attached.

type DriverType

type DriverType uint8

DriverType is the SQL driver.

var (
	DriverUnknown    DriverType = 0
	DriverSQLite     DriverType = 1
	DriverPostgreSQL DriverType = 2
	DriverMariaDB    DriverType = 3

func Driver

func Driver(ctx context.Context) DriverType

Driver gets the SQL driver.

func (DriverType) String

func (d DriverType) String() string

type DumpArg

type DumpArg int32
const (
	DumpLocation DumpArg // Show location of Dump call.
	DumpQuery            // Show the query with placeholders substituted.
	DumpExplain          // Show the results of EXPLAIN (or EXPLAIN ANALYZE for PostgreSQL).
	DumpResult           // Show the query result.
	DumpVertical         // Print query result in vertical columns instead of horizontal.
	DumpCSV              // Print query result as CSV.
	DumpJSON             // Print query result as JSON.
	DumpHTML             // Print query result as a HTML table.
	DumpAll              // Dump all we can.

type L

type L []interface{}

L ("list") is an alias for []interface.

type Migrate

type Migrate struct {
	// contains filtered or unexported fields

Migrate allows running database migrations.

func NewMigrate

func NewMigrate(db DB, files fs.FS, gomig map[string]func(context.Context) error) (*Migrate, error)

NewMigrate creates a new migration instance.

Migrations are loaded from the filesystem, as described in ConnectOptions.

You can optionally pass a list of Go functions to run as a "migration".

Every migration is automatically run in a transaction; and an entry in the version table is inserted.

func (Migrate) Check

func (m Migrate) Check() error

Check if there are pending migrations; will return the (non-fatal) PendingMigrationsError if there are.

func (Migrate) List

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

List all migrations we know about, and all migrations that have already been run.

func (*Migrate) Log

func (m *Migrate) Log(f func(name string))

Log sets a log function for migrations; this gets called for every migration that gets run.

This only gets called if the migration was run successfully.

func (Migrate) Run

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

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

func (Migrate) Schema

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

Schema of a migration by name.

func (*Migrate) Test

func (m *Migrate) Test(t bool)

Test sets the "test" flag: it won't commit any transactions.

This will work correctly for SQLite and PostgreSQL, but not MariaDB as most ALTER and CREATE commands will automatically imply COMMIT. See: https://mariadb.com/kb/en/sql-statements-that-cause-an-implicit-commit/

type NotExistError

type NotExistError struct {
	Driver  string // Driver name
	DB      string // Database name
	Connect string // Full connect string

NotExistError is returned when a database doesn't exist and Create is false in the connection arguments.

func (NotExistError) Error

func (err NotExistError) Error() string

type P

type P map[string]interface{}

P ("params") is an alias for map[string]interface{}, just because it's less typing and looks less noisy 🙃

type PendingMigrationsError

type PendingMigrationsError struct{ Pending []string }

PendingMigrationsError is a non-fatal error used to indicate there are migrations that have not yet been run.

func (PendingMigrationsError) Error

func (err PendingMigrationsError) Error() string

type Rows

type Rows struct {
	// contains filtered or unexported fields

func Query

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

Query the database without immediately loading the result.

This gives more flexibility over Select(), and won't load the entire result in memory and allows fetching the result one row at a time.

This won't return an error if there are no rows.

This uses Prepare(), and all the documentation from there applies here too.

func (*Rows) Close

func (r *Rows) Close() error

func (*Rows) ColumnTypes

func (r *Rows) ColumnTypes() ([]*sql.ColumnType, error)

func (*Rows) Columns

func (r *Rows) Columns() ([]string, error)

func (*Rows) Err

func (r *Rows) Err() error

func (*Rows) Next

func (r *Rows) Next() bool

func (*Rows) Scan

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

type SQL

type SQL string

SQL represents a safe SQL string that will be directly inserted in the query without any modification, rather than passed as a parameter.

Use with wisdom! Careless use of this can open you to SQL injections. Generally speaking you rarely want to use this, except in some rare cases where 1) parameters won't work, and 2) you're really really sure this value is safe.

type Version

type Version string

func (Version) AtLeast

func (v Version) AtLeast(want Version) bool

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
t or T : Toggle theme light dark auto