Managing Multiple Database Connections in Golang

Chazool
4 min readSep 25, 2023

--

Introduction

As software engineers, often encountered situations where applications need to work with multiple databases, each with its unique requirements and configurations. Efficiently handling these various database connections while adhering to coding best practices is crucial for building robust and easily maintainable applications. In this article, the topic of managing multiple database connections in the Golang will be explored. Insights from software engineering will be shared, demonstrating an approach aligned with SOLID principles, which facilitate the creation of clean and maintainable code.

The Challenge: Multiple Database Connections

In software development, working with multiple databases like MySQL, PostgreSQL, or others is common. Managing these connections can be challenging due to differences in configuration and requirements. To address this challenge effectively, a step-by-step guide and a practical example will be provided.

Step 1: Database Configuration

Begin by defining a DBConfig struct to hold the configuration details for each database. These configurations include critical parameters like the database type, user credentials, host, port, and more.

// DBConfig represents the configuration for a database.
type DBConfig struct {
IdentificationName string // IdentificationName is used to obtain the specific database connection.
DB string // Database name.
User string // Database user.
Password string `json:"_"` // Database password.
Host string // Database host.
Port string // Database port.
Type string // Type of the database ("mysql", "postgres", "mssql", etc.).
SSLMode string // SSL mode for the database connection.
TimeZone string // Time zone for the database.
dialector gorm.Dialector // GORM dialector for database configuration.
}

// Connect establishes a database connection based on the provided configuration.
func (config *DBConfig) Connect() (DBConnection, error) {
db, err := gorm.Open(config.dialector, &gorm.Config{})
return db, err
}

Step 2: Database Connection Interface

Create a DatabaseConnection interface to represent a database connection. This interface includes a Connect method that returns a database connection and an identificationName to uniquely identify the connection.

type DBConnection *gorm.DB

type DatabaseConnection interface {
Connect() (DBConnection, error)
}

Step 3: Implementing Database Connections

Implement two types of database connections: MySQL and PostgreSQL. Each connection type has its Connect method that configures the database connection parameters based on the provided configuration and returns a GORM DB instance.

// MySQLConnection implements DatabaseConnection for MySQL.
type MySQLConnection struct {
Config *DBConfig
}

// Connect connects to a MySQL database and returns a GORM DB instance.
func (m *MySQLConnection) Connect() (DBConnection, error) {
dsn := "%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=%s"
m.Config.dialector = mysql.Open(fmt.Sprintf(dsn, m.Config.User, m.Config.Password, m.Config.Host, m.Config.Port, m.Config.DB, m.Config.TimeZone))
db, err := m.Config.Connect()
return db, err
}

// PostgresConnection implements DatabaseConnection for PostgreSQL.
type PostgresConnection struct {
Config *DBConfig
}

// Connect connects to a PostgreSQL database and returns a GORM DB instance.
func (p *PostgresConnection) Connect() (DBConnection, error) {
dsn := "host=%s user=%s password=%s dbname=%s port=%s sslmode=%s TimeZone=%s"
p.Config.dialector = postgres.Open(fmt.Sprintf(dsn, p.Config.Host, p.Config.User, p.Config.Password, p.Config.DB, p.Config.Port, p.Config.SSLMode, p.Config.TimeZone))
db, err := p.Config.Connect()
return db, err
}

Step 4: Creating and Managing Database Connections

Create a NewConnection function that generates a new database connection based on the given configuration. This function also automatically performs database migrations to ensure the database schema is up to date.

// NewConnection creates a new DatabaseConnection based on the given config.
func (config *DBConfig) NewConnection() (DBConnection, error) {
var dbConnection DatabaseConnection
switch config.Type {
case "mysql":
dbConnection = &MySQLConnection{Config: config}
case "postgres":
dbConnection = &PostgresConnection{Config: config}
default:
return nil, fmt.Errorf("Unsupported database type: %s", config.Type)
}

// create new connection
con, err := dbConnection.Connect()
if err != nil {
return nil, err
}

//AutoMigrate dtos
err = con.Statement.AutoMigrate(&dto.User{})
if err != nil {
return nil, err
}

return con, nil
}

Step 5: Using the Database Connections

In the main function, initialize and utilize the database connections. Access a specific connection by its identificationName and perform necessary database operations.

func init() {
// Initialize database connections during program startup.
configs.InitDBConnections()
}

func main() {
users := []dto.User{
{
UserName: "user1",
Password: "test1",
},
{
UserName: "user2",
Password: "test2",
},
}

// Create a new UserRepository with a specified connection name
repo := repo.NewUserRepo("TEST_POSTGRES_CON")

// save users
err := repo.Save(users...)
if err != nil {
return
}

// get all users
users, err = repo.FindAll()
if err != nil {
return
}

// print users
for _, user := range users {
fmt.Printf("%+v\n", user)
}

// Set up a channel to listen for OS signals (e.g., Ctrl+C)
c := make(chan os.Signal, 1)
signal.Notify(c, os.Interrupt, syscall.SIGTERM)
// Wait for a signal (e.g., Ctrl+C) to gracefully exit the program
<-c

// Close database connections when the program terminates.
defer configs.CloseDBConnections()
}

Conclusion

Managing multiple database connections in Golang is a common challenge in software development. By following the steps outlined in this article and utilizing the provided example code, efficiently handle various database types while maintaining clean and maintainable code.

With this approach, confidently work with multiple databases in Golang projects, ensuring scalability and maintainability as the application grows. Clean and maintainable code is essential for the success of any software project, and mastering the management of multiple database connections is a valuable skill for software engineers.

Managing Multiple Database Connections Repository

— happy coding 😉 —

--

--

No responses yet