db

README

DB

A Go package to handle database connections.

This package is a convenience wrapper around other libraries and currently supports databases utilising the following protocols:

1. MySQL (github.com/go-sql-driver/mysql)
2. PostgreSQL (github.com/lib/pq)
3. MSSQL (github.com/denisenkom/go-mssqldb)

• Github: https://github.com/usvc/go-db
• Gitlab: https://gitlab.com/usvc/modules/go/db

The main database tool by usvc can be found at github.com/usvc/db if that's what you were looking for

• DB
• Usage
  • Importing
  • Creating a new database connection
  • Creating a new, named database connection
  • Importing an existing connection
  • Verifying a connection works
  • Retrieving a database connection
  • Closing a database connection
  • Closing all connections
• Configuration
  • db.Options
• Development Runbook
  • Getting Started
  • Continuous Integration (CI) Pipeline
    • On Github
      • Releasing
    • On Gitlab
      • Version Bumping
      • DockerHub Publishing
• Licensing

---

Usage

Importing

import "github.com/usvc/go-db"

Creating a new database connection

The following registers a new connection instance (but does not estabish a connection):

if err := db.Init(Options{
  Username: "user",
  Password: "password",
  Database: "schema",
  Hostname: "localhost",
  Port:     3306,
}); err != nil {
  log.Printf("an error occurred while creating the connection: %s", err)
}

Creating a new, named database connection

The following registers a new connection instance (but does not estabish a connection) named "my-connection":

if err := db.Init(Options{
  ConnectionName: "my-connection",
  Username: "user",
  Password: "password",
  Database: "schema",
  Hostname: "localhost",
  Port:     3306,
}); err != nil {
  log.Printf("an error occurred while creating the connection: %s", err)
}

Importing an existing connection

The following imports an existing *sql.DB connection and names it "connection-name"

var existingConnection *sql.DB
// ... initialise `existingConnection` by other means ...
if err := db.Import(existingConnection, "connection-name"); err != nil {
  log.Printf("connection 'connection-name' seems to already exist: %s\n", err)
}

Verifying a connection works

The following checks if the connection instance named "existing-connection" can establish a connection to the database server:

var existingConnection *sql.DB
// ... initialise `existingConnection` by other means ...
if err := db.Check("existing-connection"); err != nil {
  log.Printf("connection 'existing-connection' could not connect: %s\n", err)
}

Retrieving a database connection

The following retrieves the default *sql.DB connection instance:

// retrieve the 'default' connection
connection := db.Get()
if connection == nil {
  log.Println("connection 'default' does not exist")
}

The following retrieves the *sql.DB connection instance named "my-connection":

// retrieve the connection with name 'my-connection'
connection := db.Get("my-connection")
if connection == nil {
  log.Println("connection 'my-connection' does not exist")
}

Closing a database connection

The following closes the default database connection:

// ... run db.Init ...
if err := db.Close(); err != nil {
  log.Println("the default connection could not be closed")
}

The following closes the database connection named "my-connection":

// ... run db.Init for a connection named 'my-connection'...
if err := db.Close("my-connection"); err != nil {
  log.Println("the connection named 'my-connection' could not be closed")
}

Closing all connections

The following closes all database connections:

// ... run db.Init for a connection named 'my-connection'...
if err := db.Close("my-connection"); err != nil {
  for i := 0; i < len(err); i++ {
    log.Println(err[i])
  }
}

---

Configuration

db.Options

• ConnectionName string: Defines a local name of the connection. Defaults to "default"
• Hostname string: Defines the hostname where the database service can be reached. Defaults to "127.0.0.1"
• Port string: Defines the port which the database service is listening on. Defaults to uint16(3306)
• Username string: Defines the username of the user used to login to the database server. Defaults to "user"
• Password string: Defines the password of the user represented in the Username property. Defaults to "password"
• Database string: Defines the name of the database schema to use. Defaults to "database"
• Driver string: Defines the database driver to use. One of db.DriverMySQL, db.DriverPostgreSQL, or db.DriverMSSQL. Defaults to db.DriverMySQL
• Params map[string]string: Defines connection parameters to use in the data source name (DSN).

---

Development Runbook

Getting Started

1. Clone this repository
2. Run make deps to pull in external dependencies
3. Write some awesome stuff
4. Run make test to ensure unit tests are passing
5. Push

Continuous Integration (CI) Pipeline

On Github

Github is used to deploy binaries/libraries because of it's ease of access by other developers.

Releasing

Releasing of the binaries can be done via Travis CI.

1. On Github, navigate to the tokens settings page (by clicking on your profile picture, selecting Settings, selecting Developer settings on the left navigation menu, then Personal Access Tokens again on the left navigation menu)
2. Click on Generate new token, give the token an appropriate name and check the checkbox on public_repo within the repo header
3. Copy the generated token
4. Navigate to travis-ci.org and access the cooresponding repository there. Click on the More options button on the top right of the repository page and select Settings
5. Scroll down to the section on Environment Variables and enter in a new NAME with RELEASE_TOKEN and the VALUE field cooresponding to the generated personal access token, and hit Add

On Gitlab

Gitlab is used to run tests and ensure that builds run correctly.

Version Bumping

1. Run make .ssh
2. Copy the contents of the file generated at ./.ssh/id_rsa.base64 into an environment variable named DEPLOY_KEY in Settings > CI/CD > Variables
3. Navigate to the Deploy Keys section of the Settings > Repository > Deploy Keys and paste in the contents of the file generated at ./.ssh/id_rsa.pub with the Write access allowed checkbox enabled

• DEPLOY_KEY: generate this by running make .ssh and copying the contents of the file generated at ./.ssh/id_rsa.base64

DockerHub Publishing

1. Login to https://hub.docker.com, or if you're using your own private one, log into yours
2. Navigate to your security settings at the /settings/security endpoint
3. Click on Create Access Token, type in a name for the new token, and click on Create
4. Copy the generated token that will be displayed on the screen
5. Enter the following varialbes into the CI/CD Variables page at Settings > CI/CD > Variables in your Gitlab repository:

• DOCKER_REGISTRY_URL: The hostname of the Docker registry (defaults to docker.io if not specified)
• DOCKER_REGISTRY_USERNAME: The username you used to login to the Docker registry
• DOCKER_REGISTRY_PASSWORD: The generated access token

---

Licensing

Code in this package is licensed under the MIT license (click to see full text))

Documentation

Index

• Constants
• Variables
• func Check(optionalConnectionName ...string) error
• func Close(optionalConnectionName ...string) error
• func CloseAll() []error
• func Get(optionalConnectionName ...string) *sql.DB
• func Import(existingConnection *sql.DB, optionalConnectionName ...string) error
• func Init(options Options) error
• type Options
  • func (o *Options) AssignDefaults()

Constants

const (
	// DriverMySQL is the key for the MySQL driver
	DriverMySQL = "mysql"
	// DriverPostgreSQL is the key for the PostgreSQL driver
	DriverPostgreSQL = "postgres"
	// DriverMSSQL is the key for the Microsoft SQL Server driver
	DriverMSSQL = "sqlserver"

	// DefaultConnectionName is the assigned driver name when no .ConnectionName property is specified in Options
	DefaultConnectionName = "default"
	// DefaultDatabaseName is the default schema which the connection will connect to
	DefaultDatabaseName = "database"
	// DefaultDriver is the assigned driver name when no .Driver property is specified in Options
	DefaultDriver = DriverMySQL
	// DefaultHostname is the default hostname at which the database server is reachable at
	DefaultHostname = "127.0.0.1"
	// DefaultUser is the default username to use to login to the database service
	DefaultUser = "user"
	// DefaultPassword is the default password to use to login to the database service
	DefaultPassword = "password"
	// DefaultPortMySQL is the default MySQL port
	DefaultPortMySQL = uint16(3306)
	// DefaultPortPostgreSQL is the default PostgreSQL port
	DefaultPortPostgreSQL = uint16(5432)
)

Variables

var (
	// SupportedDrivers is a list of driver names which the `db` package supports
	SupportedDrivers = []string{DriverMySQL, DriverPostgreSQL, DriverMSSQL}
)

Functions

func Check

func Check(optionalConnectionName ...string) error

Check verifies that a connection can be made using the configuration provided in the connection :options parameter

func Close

func Close(optionalConnectionName ...string) error

Close closes a single database connection

func CloseAll

func CloseAll() []error

CloseAll attempts to close all established connections, returning a list of errors in cases where the connection could not be closed.

func Get

func Get(optionalConnectionName ...string) *sql.DB

Get returns the instance of the database connection

func Import

func Import(existingConnection *sql.DB, optionalConnectionName ...string) error

Import imports an existing database connection if another connection with the same name does not exist (an error is returned if so)

func Init

func Init(options Options) error

Init initialises the `db` module so that Get can be called anywhere in the consuming package

Types

type Options

type Options struct {
	// ConnectionName defines a local name of the connection, defaults to DefaultConnectionName
	ConnectionName string
	// Hostname defines the hostname where the database service can be reached
	Hostname string
	// Port defines the port which the database service is listening on
	Port uint16
	// Username defines the username of the user used to login to the database server
	Username string
	// Password defines the password of the user represented in the Username property
	Password string
	// Database defines the name of the database schema to use
	Database string
	// Driver defines the database driver to use, defaults to DefaultConnectionName (see SupportedDrivers for a list of supported drivers)
	Driver string
	// Params define connection parameters to use in the data source name (DSN)
	Params map[string]string
}

Options stores the database options that we use while establishing a connection to the database, remember to update README.md if this gets updated!

func (*Options) AssignDefaults

func (o *Options) AssignDefaults()

AssignDefaults takes in a pointer to a connection :options parameter and updates optional fields with the defaults if they haven't been specified