Behind the Scenes: How I Tackled Connecting to Multiple Databases in Golang
Published: 2024-07-02 11:13:57


## 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
```

## 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
```

## 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](https://docs.docker.com/get-docker/)

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
```

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
```
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
```

## Verify the containers are running by running
```
docker ps
```
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
```

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

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

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
```


## 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
}

```
## 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
}
```

## 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)
    }
}

```

## 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]( https://hng.tech/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](https://github.com/vinuch/go-multi-db-connection)

links to the HNG Internship: 
[https://hng.tech/internship]( https://hng.tech/internship), [https://hng.tech/hire](https://hng.tech/hire)

Do check them out, thank you!.