dbw package - github.com/hashicorp/go-dbw - Go Packages (original) (raw)

Package dbw is a database wrapper that supports connecting and using any database with a gorm driver. It's intent is to completely encapsulate an application's access to it's database with the exception of migrations.

dbw is intentionally not an ORM and it removes typical ORM abstractions like "advanced query building", associations and migrations.

This is not to say you can't easily use dbw for complicated queries, it's just that dbw doesn't try to reinvent sql by providing some sort of pattern for building them with functions. Of course, dbw also provides lookup/search functions when you simply need to read resources from the database.

dbw strives to make CRUD for database resources fairly trivial. Even supporting "on conflict" for its create function. dbw also allows you to opt out of its CRUD functions and use exec, query and scan rows directly. You may want to carefully weigh when it's appropriate to use exec and query directly, since it's likely that each time you use them you're leaking a bit of your database schema into your application's domain.

For more information see README.md

BuildUpdatePaths takes a map of field names to field values, field masks, fields allowed to be zero value, and returns both a list of field names to update and a list of field names that should be set to null.

Clear sets fields in the value pointed to by i to their zero value. Clear descends i to depth clearing fields at each level. i must be a pointer to a struct. Cycles in i are not detected.

A depth of 2 will change i and i's children. A depth of 1 will change i but no children of i. A depth of 0 will return with no changes to i.

func InitNonCreatableFields(fields []string)

InitNonCreatableFields sets the fields which are not setable using via RW.Create(...)

func InitNonUpdatableFields(fields []string)

InitNonUpdatableFields sets the fields which are not updatable using via RW.Update(...)

Intersection is a case-insensitive search for intersecting values. Returns []string of the Intersection with values in lowercase, and map[string]string of the original av and bv, with the key set to uppercase and value set to the original

NewId creates a new random base62 ID with the provided prefix with an underscore delimiter

func NonCreatableFields() []string

NonCreatableFields returns the current set of fields which are not setable using via RW.Create(...)

func NonUpdatableFields() []string

NonUpdatableFields returns the current set of fields which are not updatable using via RW.Update(...)

func TestCreateTables(t *testing.T, conn *DB)

TestCreateTables will create the test tables for the dbw pkg

UpdateFields will create a map[string]interface of the update values to be sent to the db. The map keys will be the field names for the fields to be updated. The caller provided fieldMaskPaths and setToNullPaths must not intersect. fieldMaskPaths and setToNullPaths cannot both be zero len.

Backoff defines an interface for providing a back off for retrying transactions. See DoTx(...)

type Column

Column represents a table Column

type ColumnValue

type ColumnValue struct {

Column [string](/builtin#string)

Value interface{}

}

ColumnValue defines a column and it's assigned value for a database operation. See: SetColumnValues(...)

func SetColumnValues

func SetColumnValues(columnValues map[string]interface{}) []ColumnValue

SetColumnValues defines a map from column names to values for database operations.

func SetColumns

SetColumns defines a list of column (names) to update using the set of proposed insert columns during an on conflict update.

type Columns

Columns defines a set of column names

ConstBackoff defines a constant backoff for retrying transactions. See DoTx(...)

Duration is the constant backoff duration based on the retry attempt

Constraint defines database constraint name

DB is a wrapper around whatever is providing the interface for database operations (typically an ORM). DB uses database/sql to maintain connection pool.

Open a database connection which is long-lived. The options of WithLogger, WithLogLevel and WithMaxOpenConnections are supported.

Note: Consider if you need to call Close() on the returned DB. Typically the answer is no, but there are occasions when it's necessary. See the sql.DB docs for more information.

func OpenWith(dialector Dialector, opt ...Option) (*DB, error)

OpenWith will open a database connection using a Dialector which is long-lived. The options of WithLogger, WithLogLevel and WithMaxOpenConnections are supported.

Note: Consider if you need to call Close() on the returned DB. Typically the answer is no, but there are occasions when it's necessary. See the sql.DB docs for more information.

TestSetup is typically called before starting a test and will setup the database for the test (initialize the database one-time). Do not close the returned db. Supported test options: WithDebug, WithTestDialect, WithTestDatabaseUrl, WithTestMigration and WithTestMigrationUsingDB.

TestSetupWithMock will return a test DB and an associated Sqlmock which can be used to mock out the db responses.

Close the database

Note: Consider if you need to call Close() on the returned DB. Typically the answer is no, but there are occasions when it's necessary. See the sql.DB docs for more information.

DbType will return the DbType and raw name of the connection type

func (db *DB) Debug(on bool)

Debug will enable/disable debug info for the connection

func (db *DB) LogLevel(l LogLevel)

LogLevel will set the logging level for the db

SqlDB returns the underlying sql.DB Note: this makes it possible to do things like set database/sql connection options like SetMaxIdleConns. If you're simply setting max/min connections then you should use the WithMinOpenConnections and WithMaxOpenConnections options when "opening" the database.

Care should be take when deciding to use this for basic database operations like Exec, Query, etc since these functions are already provided by dbw.RW which provides a layer of encapsulation of the underlying database.

DbType defines a database type. It's not an exhaustive list of database types which can be used by the dbw package, since you can always use OpenWith(...) to connect to KnownDB types.

const (

UnknownDB [DbType](#DbType) = 0


Postgres [DbType](#DbType) = 1


Sqlite [DbType](#DbType) = 2

)

StringToDbType provides a string to type conversion. If the type is known, then UnknownDB with and error is returned.

String provides a string rep of the DbType.

Dialector provides a set of functions the database dialect must satisfy to be used with OpenWith(...) It's a simple wrapper of the gorm.Dialector and provides the ability to open any support gorm dialect driver.

DoNothing defines an "on conflict" action of doing nothing

type ExpBackoff struct {

}

ExpBackoff defines an exponential backoff for retrying transactions. See DoTx(...)

Duration is the exponential backoff duration based on the retry attempt

type ExprValue struct { Sql string Vars []interface{} }

ExprValue encapsulates an expression value for a column assignment. See Expr(...) to create these values.

func Expr(expr string, args ...interface{}) ExprValue

Expr creates an expression value (ExprValue) which can be used when setting column values for database operations. See: Expr(...)

Set name column to null example:

SetColumnValues(map[string]interface{}{"name": Expr("NULL")})

Set exp_time column to N seconds from now:

SetColumnValues(map[string]interface{}{"exp_time": Expr("wt_add_seconds_to_now(?)", 10)})

LogLevel defines a log level

const (

Default [LogLevel](#LogLevel) = [iota](/builtin#iota)


Silent


Error


Warn


Info

)

type OnConflict struct {

Target interface{}


Action interface{}

}

OnConflict specifies how to handle alternative actions to take when an insert results in a unique constraint or exclusion constraint error.

OpType defines a set of database operation types

const (

UnknownOp [OpType](#OpType) = 0


CreateOp [OpType](#OpType) = 1


UpdateOp [OpType](#OpType) = 2


DeleteOp [OpType](#OpType) = 3


DefaultBatchSize = 1000

)

type Option func(*Options)

Option - how Options are passed as arguments.

func WithAfterWrite(fn func(i interface{}, rowsAffected int) error) Option

WithAfterWrite provides and option to provide a func to be called after a write operation. The i interface{} passed at runtime will be the resource(s) being written.

func WithBatchSize(size int) Option

WithBatchSize specifies an option for setting the batch size for bulk operations like CreateItems. If WithBatchSize == 0, the default batch size is used (see DefaultBatchSize const).

func WithBeforeWrite(fn func(i interface{}) error) Option

WithBeforeWrite provides and option to provide a func to be called before a write operation. The i interface{} passed at runtime will be the resource(s) being written.

func WithDebug(with bool) Option

WithDebug specifies the given operation should invoke debug mode for the database output

func WithFieldMaskPaths(paths []string) Option

WithFieldMaskPaths provides an option to provide field mask paths for update operations.

func WithLimit(limit int) Option

WithLimit provides an option to provide a limit. Intentionally allowing negative integers. If WithLimit < 0, then unlimited results are returned. If WithLimit == 0, then default limits are used for results (see DefaultLimit const).

func WithLogLevel(l LogLevel) Option

WithLogLevel specifies an option for setting the log level

WithLogger specifies an optional hclog to use for db operations. It's only valid for Open(..) and OpenWith(...)

func WithLookup(enable bool) Option

WithLookup enables a lookup after a write operation.

func WithMaxOpenConnections(max int) Option

WithMaxOpenConnections specifies and optional max open connections for the database. A value of zero equals unlimited connections

func WithMinOpenConnections(max int) Option

WithMinOpenConnections specifies and optional min open connections for the database. A value of zero means that there is no min.

func WithNullPaths(paths []string) Option

WithNullPaths provides an option to provide null paths for update operations.

func WithOnConflict(onConflict *OnConflict) Option

WithOnConflict specifies an optional on conflict criteria which specify alternative actions to take when an insert results in a unique constraint or exclusion constraint error

func WithOrder(withOrder string) Option

WithOrder provides an option to provide an order when searching and looking up.

func WithPrngValues(withPrngValues []string) Option

WithPrngValues provides an option to provide values to seed an PRNG when generating IDs

func WithReturnRowsAffected(rowsAffected *int64) Option

WithReturnRowsAffected specifies an option for returning the rows affected and typically used with "bulk" write operations.

func WithSkipVetForWrite(enable bool) Option

WithSkipVetForWrite provides an option to allow skipping vet checks to allow testing lower-level SQL triggers and constraints

WithTable specifies an option for setting a table name to use for the operation.

func WithVersion(version *uint32) Option

WithVersion provides an option version number for update operations. Using this option requires that your resource has a version column that's incremented for every successful update operation. Version provides an optimistic locking mechanism for write operations.

func WithWhere(whereClause string, args ...interface{}) Option

WithWhere provides an option to provide a where clause with arguments for an operation.

type Options struct {

WithBeforeWrite func(i interface{}) [error](/builtin#error)


WithAfterWrite func(i interface{}, rowsAffected [int](/builtin#int)) [error](/builtin#error)


WithLookup [bool](/builtin#bool)


WithLimit [int](/builtin#int)


WithFieldMaskPaths [][string](/builtin#string)


WithNullPaths [][string](/builtin#string)


WithVersion *[uint32](/builtin#uint32)

WithSkipVetForWrite [bool](/builtin#bool)


WithWhereClause [string](/builtin#string)


WithWhereClauseArgs []interface{}


WithOrder [string](/builtin#string)


WithPrngValues [][string](/builtin#string)


WithLogger [hclog](/github.com/hashicorp/go-hclog).[Logger](/github.com/hashicorp/go-hclog#Logger)


WithMaxOpenConnections [int](/builtin#int)


WithMinOpenConnections [int](/builtin#int)


WithDebug [bool](/builtin#bool)


WithOnConflict *[OnConflict](#OnConflict)


WithRowsAffected *[int64](/builtin#int64)


WithTable [string](/builtin#string)


WithBatchSize [int](/builtin#int)

}

Options - how Options are represented which have been set via an Option function. Use GetOpts(...) to populated this struct with the options that have been specified for an operation. All option fields are exported so they're available for use by other packages.

func GetOpts(opt ...Option) Options

GetOpts - iterate the inbound Options and return a struct.

RW uses a DB as a connection for it's read/write operations. This is basically the primary type for the package's operations.

func New(underlying *DB) *RW

New creates a new RW using an open DB. Note: there can by many RWs that share the same DB, since the DB manages the connection pool.

Begin will start a transaction

Commit will commit a transaction

Create a resource in the db with options: WithDebug, WithLookup, WithReturnRowsAffected, OnConflict, WithBeforeWrite, WithAfterWrite, WithVersion, WithTable, and WithWhere.

OnConflict specifies alternative actions to take when an insert results in a unique constraint or exclusion constraint error. If WithVersion is used with OnConflict, then the update for on conflict will include the version number, which basically makes the update use optimistic locking and the update will only succeed if the existing rows version matches the WithVersion option. Zero is not a valid value for the WithVersion option and will return an error. WithWhere allows specifying an additional constraint on the on conflict operation in addition to the on conflict target policy (columns or constraint).

CreateItems will create multiple items of the same type. Supported options: WithBatchSize, WithDebug, WithBeforeWrite, WithAfterWrite, WithReturnRowsAffected, OnConflict, WithVersion, WithTable, and WithWhere. WithLookup is not a supported option.

DB returns the underlying DB

Delete a resource in the db with options: WithWhere, WithDebug, WithTable, and WithVersion. WithWhere and WithVersion allows specifying a additional constraints on the operation in addition to the PKs. Delete returns the number of rows deleted and any errors.

DeleteItems will delete multiple items of the same type. Options supported: WithWhereClause, WithDebug, WithTable

DoTx will wrap the Handler func passed within a transaction with retries you should ensure that any objects written to the db in your TxHandler are retryable, which means that the object may be sent to the db several times (retried), so things like the primary key may need to be reset before retry.

Exec will execute the sql with the values as parameters. The int returned is the number of rows affected by the sql. The WithDebug option is supported.

func (rw *RW) IsTx() bool

IsTx returns true if there's an existing transaction in progress

LookupBy will lookup a resource by it's primary keys, which must be unique. If the resource implements either ResourcePublicIder or ResourcePrivateIder interface, then they are used as the resource's primary key for lookup. Otherwise, the resource tags are used to determine it's primary key(s) for lookup. The WithDebug and WithTable options are supported.

LookupByPublicId will lookup resource by its public_id, which must be unique. The WithTable option is supported.

LookupWhere will lookup the first resource using a where clause with parameters (it only returns the first one). Supports WithDebug, and WithTable options.

Query will run the raw query and return the *sql.Rows results. Query will operate within the context of any ongoing transaction for the Reader. The caller must close the returned *sql.Rows. Query can/should be used in combination with ScanRows. The WithDebug option is supported.

Rollback will rollback the current transaction

func (rw *RW) ScanRows(rows *sql.Rows, result interface{}) error

ScanRows will scan the rows into the interface

SearchWhere will search for all the resources it can find using a where clause with parameters. An error will be returned if args are provided without a where clause.

Supports WithTable and WithLimit options. If WithLimit < 0, then unlimited results are returned. If WithLimit == 0, then default limits are used for results. Supports the WithOrder, WithTable, and WithDebug options.

Update a resource in the db, a fieldMask is required and provides field_mask.proto paths for fields that should be updated. The i interface parameter is the type the caller wants to update in the db and its fields are set to the update values. setToNullPaths is optional and provides field_mask.proto paths for the fields that should be set to null. fieldMaskPaths and setToNullPaths must not intersect. The caller is responsible for the transaction life cycle of the writer and if an error is returned the caller must decide what to do with the transaction, which almost always should be to rollback. Update returns the number of rows updated.

Supported options: WithBeforeWrite, WithAfterWrite, WithWhere, WithDebug, WithTable and WithVersion. If WithVersion is used, then the update will include the version number in the update where clause, which basically makes the update use optimistic locking and the update will only succeed if the existing rows version matches the WithVersion option. Zero is not a valid value for the WithVersion option and will return an error. WithWhere allows specifying an additional constraint on the operation in addition to the PKs. WithDebug will turn on debugging for the update call.

type Reader interface {

LookupBy(ctx [context](/context).[Context](/context#Context), resource interface{}, opt ...[Option](#Option)) [error](/builtin#error)


LookupByPublicId(ctx [context](/context).[Context](/context#Context), resource [ResourcePublicIder](#ResourcePublicIder), opt ...[Option](#Option)) [error](/builtin#error)


LookupWhere(ctx [context](/context).[Context](/context#Context), resource interface{}, where [string](/builtin#string), args []interface{}, opt ...[Option](#Option)) [error](/builtin#error)


SearchWhere(ctx [context](/context).[Context](/context#Context), resources interface{}, where [string](/builtin#string), args []interface{}, opt ...[Option](#Option)) [error](/builtin#error)


Query(ctx [context](/context).[Context](/context#Context), sql [string](/builtin#string), values []interface{}, opt ...[Option](#Option)) (*[sql](/database/sql).[Rows](/database/sql#Rows), [error](/builtin#error))


ScanRows(rows *[sql](/database/sql).[Rows](/database/sql#Rows), result interface{}) [error](/builtin#error)


Dialect() (_ [DbType](#DbType), rawName [string](/builtin#string), _ [error](/builtin#error))

}

Reader interface defines lookups/searching for resources

type ResourcePrivateIder interface { GetPrivateId() string }

ResourcePrivateIder defines an interface that LookupBy() can use to get the resource's private id.

type ResourcePublicIder interface { GetPublicId() string }

ResourcePublicIder defines an interface that LookupByPublicId() and LookupBy() can use to get the resource's public id.

RetryInfo provides information on the retries of a transaction

type TestOption func(*testOptions)

TestOption - how Options are passed as arguments

func WithTestDatabaseUrl(url string) TestOption

WithTestDatabaseUrl provides a way to specify an existing database for tests

func WithTestDialect(dialect string) TestOption

WithTestDialect provides a way to specify the test database dialect

WithTestMigration provides a way to specify an option func which runs a required database migration to initialize the database

WithTestMigrationUsingDB provides a way to specify an option func which runs a required database migration to initialize the database using an existing open sql.DB

type TxHandler

TxHandler defines a handler for a func that writes a transaction for use with DoTx

UpdateAll defines an "on conflict" action of updating all columns using the proposed insert column values

VetForWriter provides an interface that Create and Update can use to vet the resource before before writing it to the db. For optType == UpdateOp, options WithFieldMaskPath and WithNullPaths are supported. For optType == CreateOp, no options are supported

type Writer interface {

DoTx(ctx [context](/context).[Context](/context#Context), retryErrorsMatchingFn func([error](/builtin#error)) [bool](/builtin#bool), retries [uint](/builtin#uint), backOff [Backoff](#Backoff), Handler [TxHandler](#TxHandler)) ([RetryInfo](#RetryInfo), [error](/builtin#error))


Update(ctx [context](/context).[Context](/context#Context), i interface{}, fieldMaskPaths [][string](/builtin#string), setToNullPaths [][string](/builtin#string), opt ...[Option](#Option)) ([int](/builtin#int), [error](/builtin#error))


Create(ctx [context](/context).[Context](/context#Context), i interface{}, opt ...[Option](#Option)) [error](/builtin#error)


CreateItems(ctx [context](/context).[Context](/context#Context), createItems interface{}, opt ...[Option](#Option)) [error](/builtin#error)


Delete(ctx [context](/context).[Context](/context#Context), i interface{}, opt ...[Option](#Option)) ([int](/builtin#int), [error](/builtin#error))


DeleteItems(ctx [context](/context).[Context](/context#Context), deleteItems interface{}, opt ...[Option](#Option)) ([int](/builtin#int), [error](/builtin#error))


Exec(ctx [context](/context).[Context](/context#Context), sql [string](/builtin#string), values []interface{}, opt ...[Option](#Option)) ([int](/builtin#int), [error](/builtin#error))


Query(ctx [context](/context).[Context](/context#Context), sql [string](/builtin#string), values []interface{}, opt ...[Option](#Option)) (*[sql](/database/sql).[Rows](/database/sql#Rows), [error](/builtin#error))


ScanRows(rows *[sql](/database/sql).[Rows](/database/sql#Rows), result interface{}) [error](/builtin#error)


Begin(ctx [context](/context).[Context](/context#Context)) (*[RW](#RW), [error](/builtin#error))


Rollback(ctx [context](/context).[Context](/context#Context)) [error](/builtin#error)


Commit(ctx [context](/context).[Context](/context#Context)) [error](/builtin#error)


Dialect() (_ [DbType](#DbType), rawName [string](/builtin#string), _ [error](/builtin#error))

}

Writer interface defines create, update and retryable transaction handlers