dbx

README

dbx

A library that provides database extensions for Go. This library requires sqlx, but is compatible with both sql and sqlx.

Overview

Installation

Make sure you have a working Go environment. The core library has an external dependency on sqlx. To run the unit tests, however, the testify library is required.

To install, run:

go get github.com/dakiva/dbx

Tests

To run the tests, you'll need to connect to a Postgres database:

POSTGRES_DSN="name= dbname= host= port= sslmode=" go test

The pq Postgres driver and Testify are also required.

Getting Started

This library has support for externalizing queries into JSON files.

const (
      QueryA QueryIdentifier = "QueryA"
      ...
)

queryMap := LoadNamedQueries("path/to/querfile.json", "path/to/queryfile2.json")
db *DB = ...
db.Exec(queryMap.Q(QueryA), ...)

Additionally, the schema_support file contains useful Postgres specific functions for managing schemas.

About

This library is written by Daniel Akiva (dakiva) and is licensed under the apache-2.0 license. Pull requests are welcome.

Documentation

Index

• Constants
• func BuildDsn(dsnMap map[string]string) string
• func CreateDsnForRole(existingDsn, role, password string) string
• func DropSchema(schema string, db *sqlx.DB) error
• func EnsureSchema(schema, password string, db *sqlx.DB) error
• func GenerateDefaultDsn() string
• func GenerateSchemaName(prefix string) string
• func GetCurrentSchemaVersion(schema string, db *sqlx.DB) (int64, error)
• func GetDsn() string
• func InitializeDB(pgdsn, schema, schemaPassword, migrationsDir string) (*sqlx.DB, error)
• func InitializeTestDB(dsn, schemaName, migrationsDir string) (*sqlx.DB, error)
• func InstallExtensions(schema, migrationsDir string, db *sqlx.DB) error
• func MigrateSchema(pgdsn, schema, migrationsDir string) error
• func MustCheckSchemaVersion(schema string, expectedSchemaVersion int64, db *sqlx.DB)
• func MustInitializeDB(pgdsn, schema, schemaPassword, migrationsDir string) *sqlx.DB
• func MustInitializeTestDB(dsn, schemaName, migrationsDir string) *sqlx.DB
• func ParseDsn(dsn string) map[string]string
• func RemoveExtensions(migrationsDir string, db *sqlx.DB) error
• func TearDownTestDB(dsn, schemaName string) error
• type DBContext
• type DBContextProvider
• type DBTxContext
• type QueryMap
  • func LoadNamedQueries(fileLocations ...string) (QueryMap, error)
  • func MustLoadNamedQueries(fileLocations ...string) QueryMap
  • func (q QueryMap) Q(name string) string
• type QueryValue

Constants

const (
	// PostgresType is the internal type used to initialize a sql or sqlx DB object for postgres databases.
	PostgresType = "postgres"
	// PostgresDsnEnv is the environment variable name holding the Postgres Dsn
	PostgresDsnEnv = "POSTGRES_DSN"
)

Functions

func BuildDsn

func BuildDsn(dsnMap map[string]string) string

BuildDsn builds a dsn from a map.

func CreateDsnForRole

func CreateDsnForRole(existingDsn, role, password string) string

CreateDsnForRole takes an existing, valid dsn and replaces the user name with the specified role name. If the password is non-empty, sets the password.

func DropSchema

func DropSchema(schema string, db *sqlx.DB) error

DropSchema drops a Postgres schema along with the specific role owner. Exercise caution when using this method, as its impact is irreversible.

func EnsureSchema

func EnsureSchema(schema, password string, db *sqlx.DB) error

EnsureSchema creates a new Postgres schema along with a specific role as the owner if neither exist. Returns an error if schema creation fails. This call is idempotent. If the schema and/or role already exists, this function ignores creation and continues configuring the schema and role.

func GenerateDefaultDsn

func GenerateDefaultDsn() string

GenerateDefaultDsn generates a DSN using suitable local values: localhost, port 5432 and using the system username as the role and database name.

func GenerateSchemaName

func GenerateSchemaName(prefix string) string

GenerateSchemaName generates a unique schema name suitable for use during testing.

func GetCurrentSchemaVersion

func GetCurrentSchemaVersion(schema string, db *sqlx.DB) (int64, error)

GetCurrentSchemaVersion returns the current schema version, or an error if the version could not be determined. This function will create the migrations versions table if the migrations table does not exist.

func GetDsn

func GetDsn() string

GetDsn returns a datasource name suitable for use during testing by first looking for a dsn in an environment variable POSTGRES_DSN. If the environment variable is not set, generates a DSN using suitable local values.

func InitializeDB

func InitializeDB(pgdsn, schema, schemaPassword, migrationsDir string) (*sqlx.DB, error)

InitializeDB initializes a connection pool, establishing a connection to the database and migrates a schema, returning a DB object that has the proper search path set to the schema. Accepts a dsn "user= password= dbname= host= port= sslmode=[disable|require|verify-ca|verify-full] connect-timeout=" The role must have privileges to create a new database schema and install extensions. Schema must be set to a valid schema migrationsDir is the path to the migration scripts. This function uses goose to migrate the schema

func InitializeTestDB

func InitializeTestDB(dsn, schemaName, migrationsDir string) (*sqlx.DB, error)

InitializeTestDB initializes and migrates a test schema, returning a DB object that has the proper search path set to the initialized schema. Requires a dsn in the form "user= password= dbname= host= port= sslmode=[disable|require|verify-ca|verify-full] connect-timeout=

func InstallExtensions

func InstallExtensions(schema, migrationsDir string, db *sqlx.DB) error

InstallExtensions looks for an _extensions file in the migrations dir, loads and attempts to create the extensions with the objects of the extension stored within the newly created schema. This function is a noop if the file does not exist.

func MigrateSchema

func MigrateSchema(pgdsn, schema, migrationsDir string) error

MigrateSchema migrates a Postgres schema to the latest versioned schema script. Returns an error if migration fails.

func MustCheckSchemaVersion

func MustCheckSchemaVersion(schema string, expectedSchemaVersion int64, db *sqlx.DB)

MustCheckSchemaVersion verifies the schema is of the expected version, panicing if the version is not a match or if a match could not be determined.

func MustInitializeDB

func MustInitializeDB(pgdsn, schema, schemaPassword, migrationsDir string) *sqlx.DB

MustInitializeDB calls InitializeDB returning a DB object that has the proper search path set to the initialized schema. This function will panic on an error.

func MustInitializeTestDB

func MustInitializeTestDB(dsn, schemaName, migrationsDir string) *sqlx.DB

MustInitializeTestDB calls InitializeTestDB panics on an error.

func ParseDsn

func ParseDsn(dsn string) map[string]string

ParseDsn parses a dsn into a map.

func RemoveExtensions

func RemoveExtensions(migrationsDir string, db *sqlx.DB) error

RemoveExtensions attempts to remove all extensions. This function will not halt on an error and will iterate through all extensions. Removal does not remove the extension if it is in use. The first error found is returned.

func TearDownTestDB

func TearDownTestDB(dsn, schemaName string) error

TearDownTestDB drops the test schema, returning an error if dropping the schema fails.

Types

type DBContext

type DBContext interface {
	// NamedExec executes a query that contains named query parameters, returning result metadata or an error.
	NamedExec(query string, arg interface{}) (sql.Result, error)
	// NamedQuery executes a query that contains named parameters. Retuns rows returned by the database or an error.
	NamedQuery(query string, arg interface{}) (*sqlx.Rows, error)
	// PrepareNamed prepares a query with named parameters. Returns a prepared statement or an error.
	PrepareNamed(query string) (*sqlx.NamedStmt, error)
}

DBContext represents a protocol for providing data from an underlying database. The lack of exposure of transaction semantics here is deliberate as transactions can be adapted to this protocol. In other words, a DB object or Tx object can conform to this interface, provided that sqlx is used for named query support.

type DBContextProvider

type DBContextProvider interface {
	// GetTxContext returns a transaction context, or an error
	GetTxContext() (DBTxContext, error)
	// GetContext returns a database context
	GetContext() (DBContext, error)
}

DBContextProvider provides database connections suitable for reading or writing.

type DBTxContext

type DBTxContext interface {
	DBContext
	// Commit commits a transaction or returns an error
	Commit() error
	// Rollback rollbacks a transaction or returns an error
	Rollback() error
}

DBTxContext represents a database transaction context

type QueryMap

type QueryMap map[string]QueryValue

QueryMap holds the entire structure representing the unmarshalled set of names queries.

func LoadNamedQueries

func LoadNamedQueries(fileLocations ...string) (QueryMap, error)

LoadNamedQueries loads named queries from explicit file locations, returning an error if a file could not be loaded or parsed as JSON. The JSON format is simply { "queryName", { "query" : "SELECT * FROM...", "description": "A select statement" }. If two queries have the same name either in the same file, or in disparate files, the last query loaded wins, overwriting the previously loaded query.

func MustLoadNamedQueries

func MustLoadNamedQueries(fileLocations ...string) QueryMap

MustLoadNamedQueries calls LoadNamedQueries and panics if an error occurs while loading the queries.

func (QueryMap) Q

func (q QueryMap) Q(name string) string

Q finds and returns the query string for the given identifier, panic'ing if a query was not found.

type QueryValue

type QueryValue struct {
	Query       string `json:"query"`
	Description string `json:"description"`
}

QueryValue is a structure representing the unmarshalled json query object.