DEV Community

vinsu rick
vinsu rick

Posted on

Behind the Scenes: How I Tackled Connecting to Multiple Databases in Golang

The challenge

Connecting your backend application to a database is a necessary step to take if you intend on persisting data within your application to be retrieved later in the future.
There are different types of databases each with different tradeoffs or pros and cons more like the different types of dbs have usecases where they shine better and others where another option might be better or more efficient. Ultimately it is up to you the developer to choose what database to use in your particular usecase.

Two of the more popular types of databases are:

Relational Databases (RDMS)

Often referred to as relational database management systems (RDMS). they store data in tables with predefined schemas, and they use SQL (structured query language) for defining and manipulating the data

  • Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server etc.

NoSQL databases

NoSQL databases as the name suggests are databases that don't require SQL for defining and manipulating data. They are designed for flexibility, scalability, and performance. They do not require a fixed schema and handle large volumes of data and high user loads well.

  • Examples: Document Stores like MongoDB, and Key-Value Stores like Redis and DynamoDB

Like I said earlier different databases have places they shine better and its easy to imagine one application having to use different databases for different things.

In this article, I'll go over how I connected a go application to both a MySQL database and a MongoDB database step by step.

Step 1: Setting up the Project

First, I set up my Golang project using Go modules for dependency management. This involved initializing a new Go module and creating the necessary directory structure for the project.

mkdir muli-db-connection
cd multi-db-connection
go mod init multi-db-connection
Enter fullscreen mode Exit fullscreen mode

Step 2: Installing Database Drivers

Golang uses specific drivers to interact with different databases. For MySQL, I used go-sql-driver/mysql, and for MongoDB, I used mongo-go-driver. I installed these drivers using the go get command.

go get -u github.com/go-sql-driver/mysql
go get go.mongodb.org/mongo-driver/mongo
Enter fullscreen mode Exit fullscreen mode

Step 3: Setting up the Databases

I used Docker to run MySQL and MongoDB locally, you can download and install it from the Docker website

First I pulled the Docker images for MySQL and MongoDB from the Docker Hub.

docker pull mysql:latest
docker pull mongodb/mongodb-community-server:latest
Enter fullscreen mode Exit fullscreen mode

Then I ran a MySQL container with the following command

docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=rootpassword -d -p 3306:3306 mysql:latest
Enter fullscreen mode Exit fullscreen mode

This will set up a MySQL container with the root password rootpassword.

Then I ran a mongoDB container with the following command

docker run --name mongodb-container -p 27017:27017 -d mongodb/mongodb-community-server:latest
Enter fullscreen mode Exit fullscreen mode

Verify the containers are running by running

docker ps
Enter fullscreen mode Exit fullscreen mode

You should see both mysql-container and mongodb-container listed.

Access MySQL
To access the MySQL container with docker you can run the following command

docker exec -it mysql-container mysql -uroot -prootpassword
Enter fullscreen mode Exit fullscreen mode

while in here you can run the following SQL commands to:

  1. create and use a new database
CREATE DATABASE mydatabase;
USE mydatabase;
Enter fullscreen mode Exit fullscreen mode
  1. create a users table to read from later
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

note: you can type exit to exit;

Acess MongoDB
To access the MongoDB container with mongosh (the MongoDB shell) you can run the following command

mongosh --port 27017
Enter fullscreen mode Exit fullscreen mode

Step 4 Establishing Database Connections

With the drivers installed and the local databases setup the next step was to establish connections to both databases. I created a db package to manage my database connections. In this package, I wrote seperate functions for connecting to MySQL and MongoDB.

Connecting to MySQL

package db

import (
    "context"
    "log"
    "time"

    "database/sql"
    _ "github.com/go-sql-driver/mysql"

    "go.mongodb.org/mongo-driver/mongo"
    "go.mongodb.org/mongo-driver/mongo/options"
)

func ConnectMySQL() (*sql.DB, error) {
    dsn := "root:rootpassword@tcp(localhost:3306)/mydatabase"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatalf("Error opening database: %v", err)
        return nil, err
    }

    if err := db.Ping(); err != nil {
        log.Fatalf("Error connecting to the database: %v", err)
        return nil, err
    }

    log.Println("Connected to MySQL successfully!")
    return db, nil
}

Enter fullscreen mode Exit fullscreen mode

Connecting to MongoDB

...

func ConnectMongoDB() (*mongo.Client, error) {
    clientOptions := options.Client().ApplyURI("mongodb://localhost:27017")
    client, err := mongo.NewClient(clientOptions)
    if err != nil {
        log.Fatalf("Error creating MongoDB client: %v", err)
        return nil, err
    }

    ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
    defer cancel()

    if err := client.Connect(ctx); err != nil {
        log.Fatalf("Error connecting to MongoDB: %v", err)
        return nil, err
    }

    if err := client.Ping(ctx, nil); err != nil {
        log.Fatalf("Error pinging MongoDB: %v", err)
        return nil, err
    }

    log.Println("Connected to MongoDB successfully!")
    return client, nil
}
Enter fullscreen mode Exit fullscreen mode

Step 5: Using the Connections

With the connections established, the next step was to use these connections in my application. I created a simple function to demonstrate querying both databases.

package main

import (
    "context"
    "fmt"
    "log"

    "multi-db-connection/db"
    "go.mongodb.org/mongo-driver/bson"
)

func main() {
    // Connect to MySQL
    mysqlDB, err := db.ConnectMySQL()
    if err != nil {
        log.Fatalf("Could not connect to MySQL: %v", err)
    }
    defer mysqlDB.Close()

    // Connect to MongoDB
    mongoClient, err := db.ConnectMongoDB()
    if err != nil {
        log.Fatalf("Could not connect to MongoDB: %v", err)
    }
    defer mongoClient.Disconnect(context.Background())

    // Query MySQL
    rows, err := mysqlDB.Query("SELECT id, name FROM users")
    if err != nil {
        log.Fatalf("Error querying MySQL: %v", err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            log.Fatalf("Error scanning MySQL row: %v", err)
        }
        fmt.Printf("MySQL User: %d, %s\n", id, name)
    }

    // Query MongoDB
    collection := mongoClient.Database("testdb").Collection("users")
    cursor, err := collection.Find(context.Background(), bson.M{})
    if err != nil {
        log.Fatalf("Error querying MongoDB: %v", err)
    }
    defer cursor.Close(context.Background())

    for cursor.Next(context.Background()) {
        var user bson.M
        if err := cursor.Decode(&user); err != nil {
            log.Fatalf("Error decoding MongoDB document: %v", err)
        }
        fmt.Printf("MongoDB User: %v\n", user)
    }
}

Enter fullscreen mode Exit fullscreen mode

Step 6: Testing and debugging

Testing the application involved running the main function and ensuring that both databases were queried successfully. Debugging was a crucial part of this process, as I encountered various issues such as connection timeouts, incorrect credentials, and network issues, it wasn't all smooth sailing but I guess thats where learning happens. Logging detailed error messages helped identify and resolve these problems quickly.

Conclusion

Connecting to multiple databases in Golang was a challenging yet rewarding experience. It pushed me to understand the intricacies of database drivers, connection handling, and error management in Golang. This project was a significant step in my backend development journey, and I am excited to continue building on this foundation during the HNG Internship.

The HNG Internship has always been a beacon of learning and growth for me. While I didn't finish the internship multiple times in the past, it provided invaluable knowledge that I still use today. Now, as I embark on this new journey into backend development, I look forward to the internship being an equally enriching supplement to my learning. I am eager to tackle new challenges, learn from experienced mentors, and ultimately become a more well-rounded developer.

Who knows maybe I'll be a finalist this year 🤞🏾.

link to project on github:
https://github.com/vinuch/go-multi-db-connection

links to the HNG Internship:
https://hng.tech/internship, https://hng.tech/hire

Do check them out, thank you!.

Top comments (0)