dbresolver

README

dbresolver

Golang Database Resolver and Wrapper for any multiple database connections topology, eg. master-slave replication database, cross-region application.

Idea and Inspiration

This DBResolver library will split your connections to correct defined DBs. Eg, all read query will routed to ReadOnly replica db, and all write operation(Insert, Update, Delete) will routed to Primary/Master DB.

Read more for the explanation on this blog post

Usecase 1: Separated RW and RO Database connection

Click to Expand

• You have your application deployed
• Your application is heavy on read operations
• Your DBs replicated to multiple replicas for faster queries
• You separate the connections for optimized query
• 

Usecases 2: Cross Region Database

Click to Expand

• Your application deployed to multi regions.
• You have your Databases configured globally.
• 

Support

You can file an Issue. See documentation in Go.Dev

Getting Started

Download

go get -u github.com/bxcodec/dbresolver/v2

Example

With Multi *sql.DB

Click to Expand

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	"github.com/bxcodec/dbresolver/v2"
	_ "github.com/lib/pq"
)

func ExampleWrapDBs() {
	var (
		host1     = "localhost"
		port1     = 5432
		user1     = "postgresrw"
		password1 = "<password>"
		host2     = "localhost"
		port2     = 5433
		user2     = "postgresro"
		password2 = "<password>"
		dbname    = "<dbname>"
	)
	// connection string
	rwPrimary := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host1, port1, user1, password1, dbname)
	readOnlyReplica := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host2, port2, user2, password2, dbname)

	// open database for primary
	dbPrimary, err := sql.Open("postgres", rwPrimary)
	if err != nil {
		log.Print("go error when connecting to the DB")
	}
	// configure the DBs for other setup eg, tracing, etc
	// eg, tracing.Postgres(dbPrimary)

	// open database for replica
	dbReadOnlyReplica, err := sql.Open("postgres", readOnlyReplica)
	if err != nil {
		log.Print("go error when connecting to the DB")
	}
	// configure the DBs for other setup eg, tracing, etc
	// eg, tracing.Postgres(dbReadOnlyReplica)

	connectionDB := dbresolver.New(
		dbresolver.WithPrimaryDBs(dbPrimary),
		dbresolver.WithReplicaDBs(dbReadOnlyReplica),
		dbresolver.WithLoadBalancer(dbresolver.RoundRobinLB))

	// now you can use the connection for all DB operation
	_, err = connectionDB.ExecContext(context.Background(), "DELETE FROM book WHERE id=$1") // will use primaryDB
	if err != nil {
		log.Print("go error when executing the query to the DB", err)
	}
	_ = connectionDB.QueryRowContext(context.Background(), "SELECT * FROM book WHERE id=$1") // will use replicaReadOnlyDB

	// Output:
	//
}

With Multi Connection String

Click to Expand

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/bxcodec/dbresolver/v2"
	_ "github.com/lib/pq"
)

func ExampleOpen() {
	var (
		host1     = "localhost"
		port1     = 5432
		user1     = "postgresrw"
		password1 = "<password>"
		host2     = "localhost"
		port2     = 5433
		user2     = "postgresro"
		password2 = "<password>"
		dbname    = "<dbname>"
	)
	// connection string
	rwPrimary := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host1, port1, user1, password1, dbname)
	readOnlyReplica := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host2, port2, user2, password2, dbname)
	connectionDB, err := dbresolver.Open("postgres", fmt.Sprintf("%s;%s", rwPrimary, readOnlyReplica))
	if err != nil {
		log.Print("go error when connecting to the DB", err)
	}

	// now you can use the connection for all DB operation
	_, err = connectionDB.ExecContext(context.Background(), "DELETE FROM book WHERE id=$1") // will use primaryDB
	if err != nil {
		log.Print("go error when connecting to the DB", err)
	}
	_ = connectionDB.QueryRowContext(context.Background(), "SELECT * FROM book WHERE id=$1") // will use replicaReadOnlyDB

	// Output:
	//
}

With Multi Master (Primary) Connection String

Click to Expand

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/bxcodec/dbresolver/v2"
	_ "github.com/lib/pq"
)

func ExampleOpenMultiPrimary() {
	var (
		host1     = "localhost"
		port1     = 5432
		user1     = "postgresrw"
		password1 = "<password>"
		host2     = "localhost"
		port2     = 5433
		user2     = "postgresro"
		password2 = "<password>"
		dbname    = "<dbname>"
	)
	// connection string
	rwPrimary1 := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host1, port1, user1, password1, dbname)
	rwPrimary2 := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host2, port2, user2, password2, dbname)
	readOnlyReplica1 := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host1, port1, user1, password1, dbname)
	readOnlyReplica2 := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host2, port2, user2, password2, dbname)

	rwPrimary := fmt.Sprintf("%s;%s", rwPrimary1, rwPrimary2)
	readOnlyReplica := fmt.Sprintf("%s;%s", readOnlyReplica1, readOnlyReplica2)
	connectionDB, err := dbresolver.Open("postgres", fmt.Sprintf("%s;%s", rwPrimary, readOnlyReplica))
	if err != nil {
		log.Print("go error when connecting to the DB", err)
	}

	// now you can use the connection for all DB operation
	_, err = connectionDB.ExecContext(context.Background(), "DELETE FROM book WHERE id=$1") // will use primaryDB
	if err != nil {
		log.Print("go error when connecting to the DB", err)
	}
	_ = connectionDB.QueryRowContext(context.Background(), "SELECT * FROM book WHERE id=$1") // will use replicaReadOnlyDB

	// Output:
	//
}

Important Notes

• Primary Database will be used when you call these functions
  • Exec
  • ExecContext
  • Begin (transaction will use primary)
  • BeginTx
• Replica Databases will be used when you call these functions
  • Query
  • QueryContext
  • QueryRow
  • QueryRowContext

Contribution

---

To contrib to this project, you can open a PR or an issue.

Documentation

Index

• type DB
  • func New(opts ...OptionFunc) DB
  • func OpenMultiPrimary(driverName, primaryDataSourceNames, readOnlyDataSourceNames string) (res DB, err error)
• type DBConnection
• type DBLoadBalancer
• type LoadBalancer
• type LoadBalancerPolicy
• type Option
• type OptionFunc
  • func WithLoadBalancer(lb LoadBalancerPolicy) OptionFunc
  • func WithPrimaryDBs(primaryDBs ...*sql.DB) OptionFunc
  • func WithReplicaDBs(replicaDBs ...*sql.DB) OptionFunc
• type RandomLoadBalancer
  • func (lb *RandomLoadBalancer[T]) Name() LoadBalancerPolicy
  • func (lb *RandomLoadBalancer[T]) Resolve(dbs []T) T
• type RoundRobinLoadBalancer
  • func (lb RoundRobinLoadBalancer[T]) Name() LoadBalancerPolicy
  • func (lb *RoundRobinLoadBalancer[T]) Resolve(dbs []T) T
• type Stmt
• type StmtLoadBalancer

Examples

• New (MultiPrimaryMultiReplicas)

Types

type DB

type DB interface {
	Begin() (*sql.Tx, error)
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
	Close() error
	// Conn only available for the primary db or the first primary db (if using multi-primary)
	Conn(ctx context.Context) (*sql.Conn, error)
	Driver() driver.Driver
	Exec(query string, args ...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	Ping() error
	PingContext(ctx context.Context) error
	Prepare(query string) (Stmt, error)
	PrepareContext(ctx context.Context, query string) (Stmt, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
	SetConnMaxIdleTime(d time.Duration)
	SetConnMaxLifetime(d time.Duration)
	SetMaxIdleConns(n int)
	SetMaxOpenConns(n int)
	PrimaryDBs() []*sql.DB
	ReplicaDBs() []*sql.DB
	// Stats only available for the primary db or the first primary db (if using multi-primary)
	Stats() sql.DBStats
}

DB interface is a contract that supported by this library. All offered function of this library defined here. This supposed to be aligned with sql.DB, but since some of the functions is not relevant with multi dbs connection, we decided to forward all single connection DB related function to the first primary DB For example, function like, `Conn()“, or `Stats()` only available for the primary DB, or the first primary DB (if using multi-primary)

func New

func New(opts ...OptionFunc) DB

New will resolve all the passed connection with configurable parameters

Example (MultiPrimaryMultiReplicas)

var (
	host1     = "localhost"
	port1     = 5432
	user1     = "postgresrw"
	password1 = "<password>"
	host2     = "localhost"
	port2     = 5433
	user2     = "postgresro"
	password2 = "<password>"
	dbname    = "<dbname>"
)
// connection string
rwPrimary := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host1, port1, user1, password1, dbname)
readOnlyReplica := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host2, port2, user2, password2, dbname)

// open database for primary
dbPrimary1, err := sql.Open("postgres", rwPrimary)
if err != nil {
	log.Print("go error when connecting to the DB")
}
// open database for primary
dbPrimary2, err := sql.Open("postgres", rwPrimary)
if err != nil {
	log.Print("go error when connecting to the DB")
}

// configure the DBs for other setup eg, tracing, etc
// eg, tracing.Postgres(dbPrimary)

// open database for replica
dbReadOnlyReplica1, err := sql.Open("postgres", readOnlyReplica)
if err != nil {
	log.Print("go error when connecting to the DB")
}
// open database for replica
dbReadOnlyReplica2, err := sql.Open("postgres", readOnlyReplica)
if err != nil {
	log.Print("go error when connecting to the DB")
}
// configure the DBs for other setup eg, tracing, etc
// eg, tracing.Postgres(dbReadOnlyReplica)

connectionDB := dbresolver.New(
	dbresolver.WithPrimaryDBs(dbPrimary1, dbPrimary2),
	dbresolver.WithReplicaDBs(dbReadOnlyReplica1, dbReadOnlyReplica2),
	dbresolver.WithLoadBalancer(dbresolver.RoundRobinLB))

// now you can use the connection for all DB operation
_, err = connectionDB.ExecContext(context.Background(), "DELETE FROM book WHERE id=$1") // will use primaryDB
if err != nil {
	log.Print("go error when executing the query to the DB", err)
}
_ = connectionDB.QueryRowContext(context.Background(), "SELECT * FROM book WHERE id=$1") // will use replicaReadOnlyDB

func OpenMultiPrimary

func OpenMultiPrimary(driverName, primaryDataSourceNames, readOnlyDataSourceNames string) (res DB, err error)

OpenMultiPrimary concurrently opens each underlying db connection both primaryDataSourceNames and readOnlyDataSourceNames must be a semi-comma separated list of DSNs primaryDataSourceNames will be used as the RW-database(primary) and readOnlyDataSourceNames as RO databases (replicas).

type DBConnection

type DBConnection interface {
	*sql.DB | *sql.Stmt
}

DBConnection is the generic type for DB and Stmt operation

type DBLoadBalancer

type DBLoadBalancer LoadBalancer[*sql.DB]

DBLoadBalancer is loadbalancer for physical DBs

type LoadBalancer

type LoadBalancer[T DBConnection] interface {
	Resolve([]T) T
	Name() LoadBalancerPolicy
	// contains filtered or unexported methods
}

LoadBalancer define the load balancer contract

type LoadBalancerPolicy

type LoadBalancerPolicy string

LoadBalancerPolicy define the loadbalancer policy data type

const (
	RoundRobinLB LoadBalancerPolicy = "ROUND_ROBIN"
	RandomLB     LoadBalancerPolicy = "RANDOM"
)

Supported Loadbalancer policy

type Option

type Option struct {
	PrimaryDBs []*sql.DB
	ReplicaDBs []*sql.DB
	StmtLB     StmtLoadBalancer
	DBLB       DBLoadBalancer
}

Option define the option property

type OptionFunc

type OptionFunc func(opt *Option)

OptionFunc used for option chaining

func WithLoadBalancer

func WithLoadBalancer(lb LoadBalancerPolicy) OptionFunc

WithLoadBalancer configure the loadbalancer for the resolver

func WithPrimaryDBs

func WithPrimaryDBs(primaryDBs ...*sql.DB) OptionFunc

WithPrimaryDBs add primaryDBs to the resolver

func WithReplicaDBs

func WithReplicaDBs(replicaDBs ...*sql.DB) OptionFunc

WithReplicaDBs add replica DBs to the resolver

type RandomLoadBalancer

type RandomLoadBalancer[T DBConnection] struct {
	// contains filtered or unexported fields
}

RandomLoadBalancer represent for Random LB policy

func (*RandomLoadBalancer[T]) Name

func (lb *RandomLoadBalancer[T]) Name() LoadBalancerPolicy

RandomLoadBalancer return the LB policy name

func (*RandomLoadBalancer[T]) Resolve

func (lb *RandomLoadBalancer[T]) Resolve(dbs []T) T

Resolve return the resolved option for Random LB

type RoundRobinLoadBalancer

type RoundRobinLoadBalancer[T DBConnection] struct {
	// contains filtered or unexported fields
}

RoundRobinLoadBalancer represent for RoundRobin LB policy

func (RoundRobinLoadBalancer[T]) Name

func (lb RoundRobinLoadBalancer[T]) Name() LoadBalancerPolicy

RandomLoadBalancer return the LB policy name

func (*RoundRobinLoadBalancer[T]) Resolve

func (lb *RoundRobinLoadBalancer[T]) Resolve(dbs []T) T

Resolve return the resolved option for RoundRobin LB

type Stmt

type Stmt interface {
	Close() error
	Exec(...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, args ...interface{}) (sql.Result, error)
	Query(...interface{}) (*sql.Rows, error)
	QueryContext(ctx context.Context, args ...interface{}) (*sql.Rows, error)
	QueryRow(args ...interface{}) *sql.Row
	QueryRowContext(ctx context.Context, args ...interface{}) *sql.Row
}

Stmt is an aggregate prepared statement. It holds a prepared statement for each underlying physical db.

type StmtLoadBalancer

type StmtLoadBalancer LoadBalancer[*sql.Stmt]

StmtLoadBalancer is loadbalancer for query prepared statements