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

Excalidraw live diagram

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
• 

Usecase 2: Cross Region Database

Click to Expand

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

Usecase 3: Multi-Master (Multi-Primary) Database

Click to Expand

• You're using a Multi-Master database topology eg, Aurora Multi-Master
• 

Support

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

Getting Started

Download

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

Example

Implementing DB Resolver using *sql.DB

Click to Expand

package main

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

	"github.com/bxcodec/dbresolver/v2"
	_ "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.New(
		dbresolver.WithPrimaryDBs(dbPrimary),
		dbresolver.WithReplicaDBs(dbReadOnlyReplica),
		dbresolver.WithLoadBalancer(dbresolver.RoundRobinLB))

	defer connectionDB.Close()
	// 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
}

Important Notes

• Primary Database will be used when you call these functions
  • Exec
  • ExecContext
  • Begin (transaction will use primary)
  • BeginTx
  • Queries with "RETURNING" clause
    • Query
    • QueryContext
    • QueryRow
    • QueryRowContext
• 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.