db

package module
v0.0.0-...-037e02a Latest Latest
Warning

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

 Go to latest Published: Mar 25, 2024 License: MIT Imports: 14 Imported by: 0

README

db

The db package encourages the use of a prepared statement workflow that matches most EO codebases at Return Path. It also abstracts some database/sql package annoyances while remaining fully database/sql compatible:

  • cursor-interation boilerplate
  • named binds (for drivers that support them)
  • @out parameter handling
  • transaction support using BeginTx if you need to execute multiple prepared statements
  • performance metric reporting via NewRelic

Notes

When using statement.Next(), always check for statement.LastErr() after since Next() only returns a boolean.

  • An error can occur while moving to the next row of data, or while scanning and writing the results to destination variables. Err() only handles the first case, while LastErr() covers both. LastErr() should be used.

Quick Start

All database/sql compatible database drivers are supported. Here are some common connection examples.

Postgres
package main

import (
	"fmt"
	"os"
	"github.com/bdlm/log"
	"github.com/bdlm/db"
	"github.com/lib/pq"
)

func main() {
	ctx, cancel := context.WithCancel(context.Background())

	// Always sanitize your secrets from your logs.
	log.AddSecret(os.Getenv("POSTGRES_PASS"))

	// Connect! The package understands how to construct a Postgres conneciton string.
	DB := db.New(&db.Conf{
		Ctx:          ctx,
		DatabaseName: "MyService-Postgres",
		DriverName:   "postgres",
		DriverType:   "postgres",
		Driver:       pq.Driver{},
		DSNData: map[string]string{
			"host": os.Getenv("POSTGRES_HOST"), // db host address
			"name": os.Getenv("POSTGRES_NAME"), // db name
			"pass": os.Getenv("POSTGRES_PASS"), // password
			"user": os.Getenv("POSTGRES_USER"), // user name
		},
	})
}
Oracle with tnsnames.ora
package main

import (
	"fmt"
	"os"
	"github.com/bdlm/log"
	"github.com/bdlm/db"
	"github.com/godror/godror"
)

func main() {
	ctx, cancel := context.WithCancel(context.Background())

	// Always sanitize your secrets from your logs.
	log.AddSecret(os.Getenv("ORACLE_PASS"))

	// Connect! The package understands how to construct an Oracle conneciton string.
	conf := &db.Conf{
		Ctx:          ctx,
		DatabaseName: "MyService-Oracle",
		DriverName:   "godror",
		DriverType:   "oracle",
		DSNData: map[string]string{
			"host": os.Getenv("ORACLE_HOST"), // db host
			"pass": os.Getenv("ORACLE_PASS"), // password
			"user": os.Getenv("ORACLE_USER"), // user name
		},
	}

	// A database Connector or Driver instance is required but the Oracle driver
	// isn't exposed publicly. Instead, initialize a new connector instance.
	dbConfig.Connector, err = godror.NewConnector(dbConfig.DSN(), nil)
	if nil != err {
		log.WithError(err).WithField("dsn", dbConfig.DSN()).Fatal("could not initialize database connector")
	}

	DB := db.New(conf)
}
Oracle without tnsnames.ora
package main

import (
	"fmt"
	"os"
	"github.com/bdlm/log"
	"github.com/bdlm/db"
	"github.com/godror/godror"
)

func main() {
	ctx, cancel := context.WithCancel(context.Background())

	// Always sanitize your secrets from your logs.
	log.AddSecret(os.Getenv("ORACLE_PASS"))

	// Connect!
	conf := &db.Conf{
		Ctx:          ctx,
		DatabaseName: "MyService-Oracle",
		DriverName:   "godror",
		DriverType:   "oracle",
		DSNData: map[string]string{
			"host": fmt.Sprintf(`(DESCRIPTION =
					(SDU=32767)
					(ENABLE=BROKEN)
					(ADDRESS_LIST =
						(ADDRESS =
							(PROTOCOL = TCP)
							(HOST = %s)
							(PORT = %s)
						)
					)
					(CONNECT_DATA =
						(SERVICE_NAME = %s)
						(SERVER = DEDICATED)
					)
				)`,
				os.Getenv("ORACLE_HOST"),
				os.Getenv("ORACLE_PORT"),
				os.Getenv("ORACLE_NAME"),
			),
			"pass": os.Getenv("ORACLE_PASS"), // password
			"user": os.Getenv("ORACLE_USER"), // user name
		},
	}

	// A database Connector or Driver instance is required but the Oracle driver
	// isn't exposed publicly. Instead, initialize a new connector instance.
	dbConfig.Connector, err = godror.NewConnector(dbConfig.DSN(), nil)
	if nil != err {
		log.WithError(err).WithField("dsn", dbConfig.DSN()).Fatal("could not initialize database connector")
	}

	DB := db.New(conf)
}
Snowflake
package main

import (
	"fmt"
	"os"
	"github.com/bdlm/v2/log"
	"github.com/bdlm/db"
	"github.com/snowflakedb/gosnowflake"
)

func main() {
	ctx, cancel := context.WithCancel(context.Background())

	// Always sanitize your secrets from your logs.
	log.AddSecret(os.Getenv("SNOWFLAKE_PASS"))

	// Connect! The package understands how to construct a Snowflake conneciton string.
	DB := db.New(&db.Conf{
		Ctx:          ctx,
		DatabaseName: "MyService-Snowflake",
		DriverName:   "snowflake",
		DriverType:   "snowflake",
		Driver:       gosnowflake.SnowflakeDriver{},
		DSNData: map[string]string{
			"user":      os.Getenv["SNOWFLAKE_USER"],      // user name
			"pass":      os.Getenv["SNOWFLAKE_PASS"],      // password
			"account":   os.Getenv["SNOWFLAKE_ACCOUNT"],   // account
			"db":        os.Getenv["SNOWFLAKE_DB"],        // database
			"schema":    os.Getenv["SNOWFLAKE_SCHEMA"],    // schema
			"warehouse": os.Getenv["SNOWFLAKE_WAREHOUSE"], // warehouse
			"role":      os.Getenv["SNOWFLAKE_ROLE"],      // role
		},
	}
}

Documentation

Overview

Package db defines a simple Statement interface for querying Oracle databases, abstracting some boilerplate (beginning transactions, wraping bind values in data structures, etc.).

Usage: 

// Creating a database connection is straightforward.
db, err := New(&Config{
	User: "user",
	Pass: "pass",
	Host: "host",
})

// You can also ping the connection, useful for building auto-reconnect
// functionality.
err := db.Ping()

// Begin a new transaction and return a Statement type. Statements use named
// query parameters.
stmt, err := db.Prepare("...")

// Bind values to the query. This wraps creating NamedArg data structures
// via sql.Named() calls.
stmt.Bind("foo", 1)
stmt.Bind("bar", "baz")

// When a result cursor is required, use Query() to execute the statement.
// This wraps the sql.Stmt.Query() call to compile the bound data (and any
// additional data passed in the exec call). Query generates a cursor that
// can be used to iterate through the results.
stmt.Query()

// To iterate through the cursor, a Next() method is provided. This wraps
// the sql.Rows.Next() and sql.Rows.Scan() methods into a single call. The
// input arguments are destination targets for the query results.
// See https://golang.org/pkg/database/sql/#Rows.Scan for details.
var foo int
var bar string
for stmt.Next(&foo, &bar) {
	doThings(foo, bar)
}

// When a result cursor is not required, use Exec() to execute the statement.
// This wraps the sql.Stmt.Exec() call to compile the bound data (and any
// additional data passed in the exec call). Exec returns a sql.Result
// interface to summarize the executed SQL command.
result, err := stmt.Exec()
fmt.Println(result.RowsAffected())

// All statements are transactions, commit the transaction to save data
// changes and close the transaction.
err = stmt.Commit()

// Close the database connection. This returns an error if no connection
// exists.
err := db.Close()

Index

Constants

This section is empty.

Variables

View Source 
var (
	// ErrDSNStringEmpty defines the empty DSN string error.
	ErrDSNStringEmpty = fmt.Errorf("DSNString is empty")
	// ErrInvalidTLSConfig defines the invalid TLS config error.
	ErrInvalidTLSConfig = fmt.Errorf("invalid value / unknown config name")
)
View Source 
var (
	// RFC3339Milli is RFC3339 with miliseconds
	RFC3339Milli = "2006-01-02T15:04:05.000Z07:00"
)

Functions

func InstrumentSQLConnector 

func InstrumentSQLConnector(cfg *Config) driver.Connector

InstrumentSQLConnector returns a wrapped driver.Driver send statistics to NewRelic agent. The returned driver must be registered and used when opening a connection.

func InstrumentSQLDriver 

func InstrumentSQLDriver(cfg *Config) driver.Driver

InstrumentSQLDriver returns a wrapped driver.Driver send statistics to NewRelic agent. The returned driver must be registered and used when opening a connection.

Types

type Config 

type Config struct {
	// Recommended, a database connector or driver instance is required to instrument
	// database queries with NewRelic.
	Connector driver.Connector

	// cancel provides the context cancellation function used internally to manage graceful shutdown.
	Cancel context.CancelFunc

	// Recommended, application context.
	Ctx context.Context

	// Required, DatabaseName is name of database instance being connected to. Used for tracking
	// metrics. i.e. "CPROD1"
	DatabaseName string

	// Recommended, a database connector or driver instance is required to instrument
	// database queries with NewRelic.
	Driver driver.Driver // database/sql/driver.Driver instance

	// Required, the driver name used when initiating a database connection.
	DriverName string // i.e. "goracle", "godror", "postgres", "snowflake"

	// Optional, used when generating a DSN string if a DSNString or DSNFn are not provided.
	// Automatic DSN generation using DSNData is supported for several database drivers
	DriverType string // i.e. "oracle", "postgres", "snowflake"

	// Optional, any data needed to generate the DSN string.
	DSNData map[string]string

	// Optional, function to generate the DSN string. *Config.DSNData will be passed in.
	//
	// Something like:
	//	func(cfg &db.Config) string {
	//		return fmt.Sprintf("%s/%s@%s", cfg.DSNData["user"], cfg.DSNData["pass"], cfg.DSNData["host"])
	//	}
	DSNFn func(*Config) string

	// Optional, function to parse the DSNString and populate database configuration
	// properties.
	DSNParser func(*Config) error

	// Optional, DSN string used to connect to the database.
	DSNString string

	// Location data storage for DSNParser or DSNFn.
	Loc *time.Location

	// NewRelic application instance
	NewRelic *nr.Application

	// Additional connection parameter storage for DSNParser or DSNFn.
	Params map[string]string

	// TLS configuration value storage for DSNParser or DSNFn.
	TLS *tls.Config
}

Config represents a database client configuration, used to create DSN strings or store values parsed out of a DSN string.

func (*Config) DSN 

func (cfg *Config) DSN() string

DSN returns a DSN string based on configuration values.

func (*Config) ParseDSN 

func (cfg *Config) ParseDSN() error

ParseDSN will parse a Data Source Name (DSN) string and return the database configuration values.

func (*Config) String 

func (cfg *Config) String() string

String implements Stringer. Prevent leaking credentials.

type DB 

type DB struct {
	// Database configuration
	Cfg *Config

	// Database connection
	Conn *sql.DB

	Ctx context.Context
}

DB defines an Oracle database connection structure.

func New 

func New(cfg *Config) (*DB, error)

New returns a new database connection instance.

- Validate required configuration parameters. - Init config values as necessary. - Begin a NewRelic transaction if applicable. - Instrument the database driver. - Initialize the database client and connect. - Start a shutdown handler.

func (*DB) BeginTx 

func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)

BeginTx is the constructor for Transaction instances.

Transaction instances handle multiple statements and can be committed or rolled back. If a New Relic application has been provided, transaction metrics will be written there. https://golang.org/pkg/database/sql/#Conn.BeginTx

func (*DB) Close 

func (db *DB) Close() error

Close closes the database, releasing any open resources. It is rare to Close a DB, as the DB handle is meant to be long-lived and shared between many goroutines. https://golang.org/pkg/database/sql/#DB.Close

func (*DB) Config 

func (db *DB) Config() *Config

Config returns the database configuration.

func (*DB) Connect 

func (db *DB) Connect() error

Connect opens a connection to the database with the provided credentials. If a database connection exists it will be disconnected before trying to reconnect.

func (*DB) Exec 

func (db *DB) Exec(query string, args ...interface{}) (sql.Result, error)

Exec implements database/sql.Exec

func (*DB) ExecContext 

func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

ExecContext implements database/sql.ExecContext

func (*DB) Ping 

func (db *DB) Ping() error

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

func (*DB) Prepare 

func (db *DB) Prepare(query string) (*Statement, error)

Prepare is the constructor for Statement instances.

Statement instances handle all transaction logic.

func (*DB) PrepareContext 

func (db *DB) PrepareContext(ctx context.Context, query string) (*Statement, error)

PrepareContext is the constructor for Statement instances.

Statement instances handle all transaction logic.

func (*DB) Query 

func (db *DB) Query(query string, args ...interface{}) (*sql.Rows, error)

Query implements Tx.Query. Query executes a query that returns rows, typically a SELECT. https://golang.org/pkg/database/sql/#Tx.Query

func (*DB) QueryContext 

func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

QueryContext implemtnts Tx.Query. Query executes a query that returns rows, typically a SELECT. https://golang.org/pkg/database/sql/#Tx.Query

func (*DB) QueryRow 

func (db *DB) QueryRow(query string, args ...interface{}) *sql.Row

func (*DB) QueryRowContext 

func (db *DB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row

QueryContext implemtnts Tx.Query. QueryRow executes a query that returns row, typically a SELECT. https://golang.org/pkg/database/sql/#Tx.Query

type Statement 

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

Statement defines the prepared statement structure and API.

func (*Statement) Bind 

func (statement *Statement) Bind(key string, value interface{}) *Statement

Bind provides a concise way to bind values to named arguments.

func (*Statement) Close 

func (statement *Statement) Close() error

Close closes the current prepared statement and all related items.

func (*Statement) Commit 

func (statement *Statement) Commit() error

Commit commits the current transaction to the database.

func (*Statement) Err 

func (statement *Statement) Err() error

Err returns the error, if any, that was encountered during iteration. Err may be called after an explicit or implicit Close. https://golang.org/pkg/database/sql/#Rows.Err

func (*Statement) Exec 

func (statement *Statement) Exec(args ...interface{}) (sql.Result, error)

Exec executes the prepared statement with any arguments that have been added using Bind() calls.

func (*Statement) ExecContext 

func (statement *Statement) ExecContext(ctx context.Context, args ...interface{}) (sql.Result, error)

ExecContext executes the prepared statement with any arguments that have been added using Bind() calls.

func (*Statement) LastErr 

func (statement *Statement) LastErr() error

LastErr returns the last error encountered by this statement.

func (*Statement) MapNext 

func (statement *Statement) MapNext(dest map[string]interface{}) bool

MapNext prepares the next result row for reading with the Scan method(). It returns true on success, or false if there is no next result row or an error happened while preparing it. Statement.Err should be consulted to distinguish between the two cases. https://golang.org/pkg/database/sql/#Rows.Next

This also performs a Scan operation. Scan copies the columns in the current row into the values pointed at by dest. The number of values in dest must be the same as the number of columns in Rows. https://golang.org/pkg/database/sql/#Rows.Scan

func (*Statement) MapScan 

func (statement *Statement) MapScan(dest map[string]interface{}) error

MapScan copies the columns in the current row into the values pointed at by dest. The number of values in dest must be the same as the number of columns in Rows. https://golang.org/pkg/database/sql/#Rows.Scan

func (*Statement) Next 

func (statement *Statement) Next(dest ...interface{}) bool

Next prepares the next result row for reading with the Scan method(). It returns true on success, or false if there is no next result row or an error happened while preparing it. Statement.Err should be consulted to distinguish between the two cases. https://golang.org/pkg/database/sql/#Rows.Next

This also performs a Scan operation. Scan copies the columns in the current row into the values pointed at by dest. The number of values in dest must be the same as the number of columns in Rows. https://golang.org/pkg/database/sql/#Rows.Scan

func (*Statement) Query 

func (statement *Statement) Query(args ...interface{}) (*sql.Rows, error)

Query executes the prepared statement with any arguments that have been added using Bind() calls. Query stores a cursor to the result of the SQL query.

func (*Statement) QueryContext 

func (statement *Statement) QueryContext(ctx context.Context, args ...interface{}) (*sql.Rows, error)

QueryContext executes the prepared statement with any arguments that have been added using Bind() calls. Query stores a cursor to the result of the SQL query.

func (*Statement) QueryRow 

func (statement *Statement) QueryRow(args ...interface{}) *sql.Row

QueryRow executes the prepared statement with any arguments that have been added using Bind() calls. Query stores a cursor to the result of the SQL query.

func (*Statement) QueryRowContext 

func (statement *Statement) QueryRowContext(ctx context.Context, args ...interface{}) *sql.Row

QueryRowContext executes the prepared statement with any arguments that have been added using Bind() calls. Query stores a cursor to the result of the SQL query.

func (*Statement) Result 

func (statement *Statement) Result() sql.Result

Result returns the internal sql.Result struct.

func (*Statement) Rollback 

func (statement *Statement) Rollback() error

Rollback aborts the current transaction.

func (*Statement) Rows 

func (statement *Statement) Rows() *sql.Rows

Rows returns the internal sql.Rows pointer.

func (*Statement) Scan 

func (statement *Statement) Scan(dest ...interface{}) error

Scan copies the columns in the current row into the values pointed at by dest. The number of values in dest must be the same as the number of columns in Rows. https://golang.org/pkg/database/sql/#Rows.Scan

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.