Managing Multiple Database Connections in Golang

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 😉 —