zdb – zgo.at/zdb Index | Files | Directories

package zdb

import "zgo.at/zdb"

Package zdb provides a nice API to interact with SQL databases in Go.

All query functions (Exec, NumRows, InsertID Get, Select, Query) use named parameters (:name)used if params contains a map or struct; positional parameters (? or $1) are used if it 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" information to stderr for testing and debugging: 

DumpLocation   Show location of Dump call.
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.
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.

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

Index

Variables 

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. 

var TXRollback = errors.New("TXRollback")

TXRollback can be returned from TX to roll back the transaction without error. 

var TemplateFuncMap template.FuncMap

TemplateFuncMap are additional template functions for Template(). Existing functions may be overridden. 

var TestDrivers []string

TestDrivers is a list of driver names to run tests against. These drivers must be imported. The default is to run against all registered drivers, which is not always desired.

Functions

func ApplyParams 

func ApplyParams(query string, params ...any) 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 Create 

func Create(db DB, files fs.FS) error

Create tables based on db/schema.{sql,gotxt}

func DBSQL 

func DBSQL(ctx context.Context) (*sql.DB, *sql.Tx)

DBSQL gets the database and transaction. The sql.DB is always set, but sql.Tx is nil if this is not a transaction.

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 DriverConnection 

func DriverConnection(db DB) any

DriverConnection gets the driver connection; for example *pgx.Pool. May be nil.

func Dump 

func Dump(ctx context.Context, out io.Writer, query string, params ...any)

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.
DumpNul        Separate columns with NUL (0x00) bytes; useful to feed output to another printer.
DumpJSON       Show as an array of JSON objects.
DumpHTML       Show as a HTML table.

func DumpString 

func DumpString(ctx context.Context, query string, params ...any) string

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

func ErrMissingField 

func ErrMissingField(err error) bool

ErrMissingFields reports if this error is because not all columns could be scanned due to missing struct fields.

All other columns are scanned; you can choose to ignore this error safely.

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.

func Exec 

func Exec(ctx context.Context, query string, params ...any) error

Exec executes a query without returning the result.

func Get 

func Get(ctx context.Context, dest any, query string, params ...any) error

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

func InsertID 

func InsertID(ctx context.Context, idColumn, query string, params ...any) (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.

func Load 

func Load(db DB, name string) (string, bool, error)

Load a query from the filesystem or embeded files.

Queries are loaded from the "db/query/" directory, as "{name}-{driver}.sql" or "db/query/{name}.sql".

To allow identifying queries in logging and statistics such as pg_stat_statements every query will have the file name inserted in the first line; for example for "db/query/select-x.sql" the query sent to the database: 

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

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 ...any) (int64, error)

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

func RunTest 

func RunTest(t *testing.T, f func(*testing.T, context.Context), opts ...drivers.TestOptions)

RunTest runs tests against all registered zdb SQL drivers.

func Select 

func Select(ctx context.Context, dest any, query string, params ...any) error

Select zero or more rows; dest needs to be a pointer to a slice of:

Returns nil (and no error) if there are no rows.

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. The error is propegated up unless it's TXRollback.

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 Template 

func Template(dialect Dialect, tpl string, params ...any) ([]byte, error)

Template runs text/template on SQL to make writing compatible schemas a bit easier.

Available template functions: 

{{sqlite "str"}}         Include the string only for the given SQL dialect.
{{psql   "str"}}
{{maria  "str"}}

Column types: 

{{"auto_increment [large]}}     Auto-incrementing column; the optional
                                [large] boolean can be used to make a
                                bigserial/bigint.
{{json}}                        JSON column type (jsonb, json, varchar)
{{blob}}                        Binary column type (bytea, blob, binary)

These only produce output for SQLite: 

{{check_timestamp "colname"}}   Check constraint for timestamp
{{check_date                    Check constraint for date

You can set additional functions (or override any of the above) by adding functions to TemplateFuncMap.

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) Errors 

func (m BulkInsert) Errors() error

Errors returns all errors that have been encountered.

func (*BulkInsert) Finish 

func (m *BulkInsert) Finish() error

Finish the operation, returning any errors.

This can be called more than once, in cases where you want to have some fine-grained control over when actual SQL is sent to the server.

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) Returned 

func (m *BulkInsert) Returned() [][]any

Returned returns any rows that were returned; only useful of [Returning] was set.

This will only return values once, for example: 

Values(...)    // Inserts 3 rows
...
Returned()     // Return the 3 rows
Values(..)     // Inserts 1 row
Returned()     // Returns the 1 row

func (*BulkInsert) Returning 

func (m *BulkInsert) Returning(columns ...string)

Returning sets a column name in the "returning" part of the query.

The values can be fetched with [Returned].

func (*BulkInsert) Values 

func (m *BulkInsert) Values(values ...any)

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.
	MigrateLog func(name string) // Called for every migration that gets run.

	// Set the maximum number of open and idle connections.
	//
	// The default for MaxOpenConns is 16, and the default for MaxIdleConns is
	// 4, instead of Go's default of 0 and 2. Use a value <0 to skip the
	// default.
	//
	// This can also be changed at runtime with:
	//
	//    d, _ := db.DBSQL()
	//    d.SetMaxOpenConns(100)
	MaxOpenConns int
	MaxIdleConns int

	// 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

	// Database files; the following layout is assumed:
	//
	//   Schema       schema-{dialect}.sql, schema.sql, or schema.gotxt
	//   Migrations   migrate/{name}-{dialect}.sql, migrate/{name}.sql, or migrate/{name}.gotxt
	//   Queries      query/{name}-{dialect}.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
}

ConnectOptions are options for Connect().

type DB 

type DB interface {
	DBSQL() (*sql.DB, *sql.Tx)
	SQLDialect() Dialect
	Info(ctx context.Context) (ServerInfo, error)
	Close() error

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

	TX(ctx context.Context, fb func(context.Context) error) error
	Begin(ctx context.Context, opts ...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(ctx context.Context, opt ConnectOptions) (DB, error)

Connect to a database.

To connect to a database you need to register a driver for it first. While zdb uses database/sql, it needs a zdb-specific driver which contains additional information. Several drivers are included in the zgo.at/zdb/drivers package. To register a driver simply import it: 

import _ "zgo.at/zdb/drivers/pq"

The connect string has the following layout (minus spaces): 

dialect                           Use default connection parameters for this driver.
dialect              + connect    Pass driver-specific connection string.
driverName           + connect    Use a SQL driver name, instead of SQL dialect.
dialect / driverName + connect    Specify both.

The connectString is driver-specific; see the documentation of the driver for details. The dialect is the "SQL dialect"; currently recognized dialects are: 

postgresql    aliases: postgres psql pgsql
sqlite        aliases: sqlite3
mysql         aliases: mariadb

For example, "postgresql+dbname=mydb", "pq+dbname=mydb", and "postgresql/pq+dbname=mydb" are all identical, assuming pq is the registered driver.

If multiple drivers are registered for the same dialect then it will use the first one.

If Create is set it will try to automatically create a database if it doesn't exist yet. If Files is given it will also look for the following files to set up the database if it doesn't exist or is empty: 

schema.gotxt           Run zdb.Template first.
schema-{dialect}.sql   Schema for this SQL dialect.
schema.sql

Migrate and GoMigrate are migrations to run, see the documentation of Migrate for details.

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 NewMetricsDB 

func NewMetricsDB(db DB, recorder MetricRecorder) DB

NewMetricsDB returns a DB wrapper which records query performance metrics.

For every query recorder.Record is called.

func Unwrap 

func Unwrap(db DB) DB

Unwrap this database, removing all zdb wrappers and returning the underlying database (which may be a transaction).

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

See logDB and metricDB in log.go and metric.go for examples.

TODO: document wrapping a bit better.

type Dialect 

type Dialect uint8

Dialect is an SQL dialect. This can be represented by multiple drivers; for example for PostgreSQL "pq" and "pgx" are both DialectPostgreSQL. 

const (
	DialectUnknown Dialect = iota
	DialectSQLite
	DialectPostgreSQL
	DialectMariaDB
)

SQL dialects.

func SQLDialect 

func SQLDialect(ctx context.Context) Dialect

SQLDialect gets the SQL dialect.

func (Dialect) String 

func (d Dialect) 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.
	DumpNul              // Print query result with columns separated by NUL (0x00) bytes.
	DumpJSON             // Print query result as JSON.
	DumpHTML             // Print query result as a HTML table.
	DumpAll              // Dump all we can.
)

type L 

type L []any

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

Deprecated: []any{..} is now a lot shorter and less ugly than []interface{}{..}

type MetricRecorder 

type MetricRecorder interface {
	Record(d time.Duration, query string, params []any)
}

type MetricsMemory 

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

MetricsMemory records metrics in memory.

func NewMetricsMemory 

func NewMetricsMemory(max int) *MetricsMemory

NewMetricsMemory creates a new MetricsMemory, up to "max" metrics per query.

func (*MetricsMemory) Queries 

func (m *MetricsMemory) Queries() []struct {
	Query string
	Times ztime.Durations
}

Queries gets a list of queries sorted by the total run time.

func (*MetricsMemory) Record 

func (m *MetricsMemory) Record(d time.Duration, query string, params []any)

Record this query.

func (*MetricsMemory) Reset 

func (m *MetricsMemory) Reset()

Reset the contents.

func (*MetricsMemory) String 

func (m *MetricsMemory) String() string

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) Show 

func (m *Migrate) Show(v bool)

Show sets the "show" flag; it won't run anything, just print the queries it would run to stdout.

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 P 

type P map[string]any

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

Deprecated: map[string]any{..} is now a lot shorter and less ugly than map[string]interface{}{...}

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
}

TODO: document.

func Query 

func Query(ctx context.Context, query string, params ...any) (*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 to allow fetching the result one row at a time.

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

TODO: will it return nil or Rows which just does nothing? Make sure this is tested and documented.

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 ...any) 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 sure this value is safe.

type ServerInfo 

type ServerInfo struct {
	Version    ServerVersion
	DriverName string
	Dialect    Dialect
	Connect    string
}

ServerInfo contains information about the SQL server.

func Info 

func Info(ctx context.Context) (ServerInfo, error)

Info gets information about the SQL server.

type ServerVersion 

type ServerVersion string

ServerVersion represents a database version.

func (ServerVersion) AtLeast 

func (v ServerVersion) AtLeast(want ServerVersion) bool

AtLeast reports if this version is at least version want.

Source Files

bulk.go connect.go log.go metric.go migrate.go prepare.go test.go tpl.go zdb.go zdb_impl.go

Directories

PathSynopsis
drivers
drivers/go-sqlite3Package sqlite3 provides a zdb driver for SQLite.
drivers/mariadbPackage mariadb provides a zdb driver for MariaDB.
drivers/pgxPackage pgx provides a zdb driver for PostgreSQL.
drivers/pqPackage pq provides a zdb driver for PostgreSQL.
drivers/test
internal
Version
v0.0.0-20241030193854-e38684ed772c (latest)
Published
Oct 30, 2024
Platform
linux/amd64
Imports
34 packages
Last checked
1 week ago –

Tools for package owners.