dbresolver

package module
v1.1.2 Latest Latest
Warning

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

 Go to latest Published: Dec 6, 2022 License: MIT Imports: 6 Imported by: 1

README

dbresolver

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

Go Go.Dev

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
  • image
Usecases 2: Cross Region Database
Click to Expand
  • Your application deployed to multi regions.
  • You have your Databases configured globally.
  • image

Support

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

Getting Started

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

Example

With Multi *sql.DB
Click to Expand 
package main

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

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

func main() {
	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 {
		panic(err)
	}
	//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 {
		panic(err)
	}
	//configure the DBs for other setup eg, tracing, etc
	// eg, tracing.Postgres(dbReadOnlyReplica)

	connectionDB := dbresolver.WrapDBs(dbPrimary, dbReadOnlyReplica)

	//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 {
		panic(err)
	}
	_ = connectionDB.QueryRowContext(context.Background(), "SELECT * FROM book WHERE id=$1") // will use replicaReadOnlyDB

}
With Multi Connection String
Click to Expand 
package main

import (
	"context"
	"fmt"

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

func main() {
	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 {
		panic(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 {
		panic(err)
	}
	_ = connectionDB.QueryRowContext(context.Background(), "SELECT * FROM book WHERE id=$1") // will use replicaReadOnlyDB


}

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

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func Version 

func Version() string

Version is the current release version.

Types

type DB 

type DB interface {
	Begin() (*sql.Tx, error)
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
	Close() error
	// Conn(ctx context.Context) (*sql.Conn, error) // not relevant for multi connection DB
	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)
}

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 not support it

func WrapDBs 

func WrapDBs(dbs ...*sql.DB) DB

WrapDBs will wrap all DB connection first DB connection is the primary-writer connection (RW), the rest connection will be used for RO connection

Example 
package main

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

	"github.com/hiroBzinga/dbresolver"
	_ "github.com/lib/pq"
)

func main() {
	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.WrapDBs(dbPrimary, dbReadOnlyReplica)

	// 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")
	}
	_ = connectionDB.QueryRowContext(context.Background(), "SELECT * FROM book WHERE id=$1") // will use replicaReadOnlyDB

}

Output:

type DBImpl 

type DBImpl struct {
	*sql.DB
	// contains filtered or unexported fields
}

DBImpl is a logical database with multiple underlying physical databases forming a single ReadWrite (primary) with multiple ReadOnly(replicas) database. Reads and writes are automatically directed to the correct db connection

func Open 

func Open(driverName, dataSourceNames string) (db *DBImpl, err error)

Open concurrently opens each underlying db connection dataSourceNames must be a semi-comma separated list of DSNs with the first one being used as the RW-database(primary) and the rest as RO databases (replicas).

Example 
package main

import (
	"context"
	"fmt"
	"log"

	"github.com/hiroBzinga/dbresolver"
	_ "github.com/lib/pq"
)

func main() {
	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")
	}

	// 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")
	}
	_ = connectionDB.QueryRowContext(context.Background(), "SELECT * FROM book WHERE id=$1") // will use replicaReadOnlyDB

}

Output:

func (*DBImpl) Begin 

func (dbImpl *DBImpl) Begin() (*sql.Tx, error)

Begin starts a transaction on the RW-database. The isolation level is dependent on the driver.

func (*DBImpl) BeginTx 

func (dbImpl *DBImpl) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)

BeginTx starts a transaction with the provided context on the RW-database.

The provided TxOptions is optional and may be nil if defaults should be used. If a non-default isolation level is used that the driver doesn't support, an error will be returned.

func (*DBImpl) Close 

func (dbImpl *DBImpl) Close() error

Close closes all physical databases concurrently, releasing any open resources.

func (*DBImpl) Driver 

func (dbImpl *DBImpl) Driver() driver.Driver

Driver returns the physical database's underlying driver.

func (*DBImpl) Exec 

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

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query. Exec uses the RW-database as the underlying db connection

func (*DBImpl) ExecContext 

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

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query. Exec uses the RW-database as the underlying db connection

func (*DBImpl) Ping 

func (dbImpl *DBImpl) Ping() error

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

func (*DBImpl) PingContext 

func (dbImpl *DBImpl) PingContext(ctx context.Context) error

PingContext verifies if a connection to each physical database is still alive, establishing a connection if necessary.

func (*DBImpl) Prepare 

func (dbImpl *DBImpl) Prepare(query string) (Stmt, error)

Prepare creates a prepared statement for later queries or executions on each physical database, concurrently.

func (*DBImpl) PrepareContext 

func (dbImpl *DBImpl) PrepareContext(ctx context.Context, query string) (Stmt, error)

PrepareContext creates a prepared statement for later queries or executions on each physical database, concurrently.

The provided context is used for the preparation of the statement, not for the execution of the statement.

func (*DBImpl) Query 

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

Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query. Query uses a radonly db as the physical db.

func (*DBImpl) QueryContext 

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

QueryContext executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query. QueryContext uses a radonly db as the physical db.

func (*DBImpl) QueryRow 

func (dbImpl *DBImpl) QueryRow(query string, args ...interface{}) *sql.Row

QueryRow executes a query that is expected to return at most one row. QueryRow always return a non-nil value. Errors are deferred until Row's Scan method is called. QueryRow uses a radonly db as the physical db.

func (*DBImpl) QueryRowContext 

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

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always return a non-nil value. Errors are deferred until Row's Scan method is called. QueryRowContext uses a radonly db as the physical db.

func (*DBImpl) ReadOnly 

func (dbImpl *DBImpl) ReadOnly() *sql.DB

ReadOnly returns the replica database

func (*DBImpl) ReadWrite 

func (dbImpl *DBImpl) ReadWrite() *sql.DB

ReadWrite returns the primary database

func (*DBImpl) SetConnMaxIdleTime 

func (dbImpl *DBImpl) SetConnMaxIdleTime(d time.Duration)

SetConnMaxIdleTime sets the maximum amount of time a connection may be idle. Expired connections may be closed lazily before reuse. If d <= 0, connections are not closed due to a connection's idle time.

func (*DBImpl) SetConnMaxLifetime 

func (dbImpl *DBImpl) SetConnMaxLifetime(d time.Duration)

SetConnMaxLifetime sets the maximum amount of time a connection may be reused. Expired connections may be closed lazily before reuse. If d <= 0, connections are reused forever.

func (*DBImpl) SetMaxIdleConns 

func (dbImpl *DBImpl) SetMaxIdleConns(n int)

SetMaxIdleConns sets the maximum number of connections in the idle connection pool for each underlying db connection If MaxOpenConns is greater than 0 but less than the new MaxIdleConns then the new MaxIdleConns will be reduced to match the MaxOpenConns limit If n <= 0, no idle connections are retained.

func (*DBImpl) SetMaxOpenConns 

func (dbImpl *DBImpl) SetMaxOpenConns(n int)

SetMaxOpenConns sets the maximum number of open connections to each physical database. If MaxIdleConns is greater than 0 and the new MaxOpenConns is less than MaxIdleConns, then MaxIdleConns will be reduced to match the new MaxOpenConns limit. If n <= 0, then there is no limit on the number of open connections. The default is 0 (unlimited).

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.

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.