README

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

Documentation

Index

Constants

This section is empty.

Variables

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.

      Functions

      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 != "" {
             t.Error(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):

            DumpAll
            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.

                                        Example:

                                        db/query/select-sites.sql:
                                           select * from sites where site_id = :site and created_at > :start
                                        
                                        db/query/select-sites_test.sql
                                          insert into sites () values (...)
                                        
                                          -- params
                                          site_id:    1
                                          created_at: 2020-01-01
                                        
                                          -- want
                                          1
                                        
                                          -- params
                                        
                                          -- want
                                        

                                        func WithDB

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

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

                                          Types

                                          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.
                                                          schema.sql
                                                          

                                                          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:

                                                          db.DBSQL().SetMaxOpenConns(100)
                                                          

                                                          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.
                                                                                     https://www.sqlite.org/wal.html
                                                          
                                                          _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
                                                                                     enabled).
                                                          
                                                          _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