Integrating MySQL in your Go project

Let's review a very introductive way to use MySQL in our Go projects:

package db

import (
	"database/sql"
	"fmt"
	"log"
	"github.com/go-sql-driver/mysql"
	"os"
)

const (
	mysqlUsersUsername = "mysql_users_username"
	mysqlUsersPassword = "mysql_users_password"
	mysqlUsersHost     = "mysql_users_host"
	mysqlUsersSchema   = "mysql_users_schema"
)

var (
	Client *sql.DB

	username = os.Getenv(mysqlUsersUsername)
	password = os.Getenv(mysqlUsersPassword)
	host     = os.Getenv(mysqlUsersHost)
	schema   = os.Getenv(mysqlUsersSchema)
)

func init() {
	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8",
		username, password, host, schema,
	)
	var err error
	Client, err = sql.Open("mysql", dataSourceName)
	if err != nil {
		panic(err)
	}
	if err = Client.Ping(); err != nil {
		panic(err)
	}
	log.Println("database successfully configured")
}

From this code, you can see the following actions being performed:

1. Database connection string is formed using environment variables instead of being hardcoded in our souce code.
2. We have a global variable Client being of type *sql.DB that we can use to interact with the database itself.
3. If we''re not able to connect or even ping the DB, the application should not even start. This is because of a current requirement saying that without a running database we want to avoid application deployment and being notified right away.

Now, in this way we have several issues we need to deal with:

1. First, sql.DB is an struct, not an interface. By using this data type we have no way of mocking a DB connection for plain unit tests and we need to rely on integration tests instead.
2. We're using an init function so we loose control over when a new DB should be created.
3. We're using the artifact at package level meaning every DB connection will be performed using this package alone and mocks should be configured to this package instead of the local package being tested.

In order to fix this, we can rely on the benefits from Go interfaces to provide the same funcionality but fixing all of the previous issues, like this:

package users_db

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"os"
)

const (
	mysqlUsersUsername = "mysql_users_username"
	mysqlUsersPassword = "mysql_users_password"
	mysqlUsersHost     = "mysql_users_host"
	mysqlUsersSchema   = "mysql_users_schema"
)

type Database interface {
	Query(query string, args ...interface{}) (*sql.Rows, error)
}

type dbClient struct {
	client *sql.DB
}

func (c *dbClient) Query(query string, args ...interface{}) (*sql.Rows, error) {
	return c.client.Query(query, args)
}

func GetNewDbClient() (Database, error) {
	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8",
		os.Getenv(mysqlUsersUsername),
		os.Getenv(mysqlUsersPassword),
		os.Getenv(mysqlUsersHost),
		os.Getenv(mysqlUsersSchema))

	client, err := sql.Open("mysql", dataSourceName)
	if err != nil {
		return nil, err
	}
	if err = client.Ping(); err != nil {
		return nil, err
	}

	log.Println("new database client successfully created")

	result := dbClient{
		client: client,
	}
	return &result, nil
}

As you can see, while we're doing pretty much the same, this approach is a little bit different because of these aspects:

1. The pointer to sql.DB now is hidden inside a local struct called dbClient. This struct will be holding the pointer to the actual database instead of interacting directly with it.
2. Access to DB methods is now available via implemented methods on an interface called Database and, since it is an interface, we can mock that interface as we want by creating a mock struct and implementing every method required by that interface.
3. In order to get a new database client now we need to call GetNewDbClient() that will return a Database and an error.
4. Now the DB client must be handled by the artifact calling this method.

What if we're creating a service layer? Now we can do something like this:

type userService struct {
	database db.Database // This is our database implementation
}

type UsersService interface {
	GetActiveUsers() ([]User, error)
}

func (s userService) GetActiveUsers() ([]User, error) {
	rows, err := s.database.Query(queryGetActiveUsers, nil)
	if err != nil {
		return nil, err
	}

	.....

	return users, nil
}

As you can see, the userService struct implements UsersService interface and it provides business logic accessing the database by holding a reference to our Database interface. Now, in order to create and get a new UsersService we can do something like this:

func GetNewUsersService(db Database) UsersService {
	service := userService{
		database: db,
	}
	return &service
}

As you can see, in order to create a new service we need to pass a given database client to the function. In this way we can define every dependency when creating our application and then relying on these dependencies to mock any layer we want and fully test our application!

Let me know how this worked for you!!