gRPC 103: Connecting to MySQL Database

In the last few posts we have been talking about the gRPC unary APIs and client server implementations. Today we will take a step further towards making it a real world application by adding support for a database.

The previous articles in this series:

1. gRPC vs REST
2. Introduction to Protocol Buffers
3. Protocol Buffers Compilation & Serialization
4. Introduction to gRPC
5. gRPC 101: Creating Services
6. gRPC 102: Creating Clients

gRPC with it's mascot

When talking about databases we have several options like the relational databases and NoSQL databases. We can either pick up MySQL, a relational database or MongoDB, a NoSQL database. For this post I will be using MySQL via Docker instead of installing it locally in my system.

We will also define the users via ProtoBuf, design the database, use Docker and then we will create a database tables to map the todos to the users and finally complete both the user and todo service in Go using MySQL. Also, we will use the repository pattern for the data layer.

Let’s cover a few basics terminologies before jumping to the database part.

Note: I am assuming you are familiar with the basics of MySQL or PostgreSQL or any other relational database like MariaDB.

Layered Architecture

Layered architecture is a way of structuring an application by separating responsibilities into distinct layers, where each layer has a clear role and communicates only with the layer directly below or above it.

We can think of it like a stack, where each layer depends on the one beneath it. Let’s take an example:

Transport Layer (gRPC / HTTP)
        |
      calls
        |
Service Layer (Business Logic)
        |
      calls
        | 
Repository or Data Access Layer
        |
      calls
        |
Database (MySQL, PostgreSQL, or MongoDB)

If you are into web development you might already know that we create services that the controller calls. Nest.js is a good example of the layered architecture in the Node.js ecosystem.

Repository Pattern

The Repository Pattern is a design pattern that acts as an abstraction layer between our business logic and our data source like a database. The repository hides how data is stored and retrieved. Instead of our application code talking directly to MySQL (SQL queries or ORM call), it talks to a repository interface.

Usefulness of Repositories

• Repositories make testing easy.
• Repositories allow us to create separation of concerns
• Repositories allow easy maintainability, SQL changes don’t affect services.
• Repositories allow us to swap the internal storage without breaking or touching other layers. We can switch from MySQL to MongoDB to Cassandra if we can keep the interface same.

Database Design

The database design in our example implementation is pretty simple. We have users who will create their todos. We can create the database diagram or the Entity Relationship diagram using the online tool https://dbdiagram.io/.

ER Diagram

This tool allows us to define the tables, create relationships between them and then it yield a beautiful ER diagram.

The database design for the Todos service

The code to generate the above ER looks like:

Table users {
  uuid varchar [primary key]
  full_name varchar 
  username varchar
  type enum('normal', 'admin')
  email varchar 
  created_at timestamp
}

Table todos {
  uuid string [primary key]
  title varchar
  description varchar
  user_id string [not null]
  status varchar
  created_at timestamp
}

Ref user_todos: todos.user_id > users.uuid // many-to-one

Database Considerations

The above diagram is very helpful visually to understand the database tables and relations between them yet is hides a few things like the size of the column data types and the constraints we have on them. We will look at the concrete SQL queries soon in this post.

Using Docker

Docker allows us to run container containing the softwares we want in our system. They are particularly useful for doing POCs and running several different types of applications that we need for development and keep our laptop/local system clean. Without Docker, we would have to install different tools and configure them like MySQL.

Docker is also very helpful in case of running different versions of the same tool. For example what if we want to test our database design on both MySQL version 8+ and version 5.7 (the older but most widely used)? Docker will allow us to install both and make them listen to our commands on different ports.

There’s a good intro video of Docker in Youtube.

Running MySQL via Docker

We will use the docker image of the MySQL version 8. Here’s the command:

docker run --name local-mysql -e MYSQL_ROOT_PASSWORD=grpc-example -p 3307:3306 -d mysql:8.0 -v mysql-data:/var/lib/mysql

We are creating a root user for the database with password grpc-example. This database instance will listen to the port 3306. So when we will try to connect to MySQL from application we will use the credentials:

database, err := db.NewMySQLConnection(db.MySQLConfig{
    User:     "root",
    Password: "grpc-example",
    Host:     "localhost",
    Port:     "3306",
    DBName:   "grpc_todos",
})

ProtoBuf Changes & Updates

For the protobuf files, we need to make some changes in the todo.proto file and also add a new user.proto file.

Todo ProtoBuf

For the existing todo.proto from the previous, we need to add a new field user_id this will be useful to map a todo to it’s creator. We can also name it creator_uuid.

message Todo {
  oneof todo_id {
    int64 id = 1;
    string uuid = 7;
  }

  // one complete eradication of 
  // `id` field from Todo message
  // reserve field number 1 and field name `id`
  // reserve 1;
  // reserve "id";

  string title = 2;
  optional string description = 3;
  bool done = 4;

  enum Priority {
    PRIORITY_UNSPECIFIED = 0;
    PRIORITY_LOW = 1;
    PRIORITY_MEDIUM = 2;
    PRIORITY_HIGH = 3;
  }
  Priority priority = 5; // <-- using the enum as a property

  google.protobuf.Timestamp created_at = 6;

  string user_uuid = 8; // <- new field
}

Note: For now we will not this field in the code. We will wire up this field once we cover Authentication in the upcoming posts.

User ProtoBuf

Let’s add the new user.proto file to add the user definition and services inside the protos folder.

syntax = "proto3";

package protos;

import "google/protobuf/timestamp.proto";

option go_package = "ashokdey.com/grpc-example/protos";
option java_package = "com.ashokdey.grpc-example.users";

// the user service definition
service UserService {
  // registers a user 
  rpc Register(RegisterUserRequest) returns (RegisterUserResponse);
  // allow user to login 
  rpc Login(LoginUserRequest) returns (LoginUserResponse);
  // get a user
  rpc GetUser(GetUserRequest) returns (GetUserResponse);
}

message RegisterUserRequest {
  string full_name = 1;
  string username = 2;
  string email = 3;
}

message RegisterUserResponse {
  string uuid = 1;
  string email = 2;
}

message LoginUserRequest {
  string email = 1;
  uint32 otp = 2;
}

message LoginUserResponse {
  string email = 1;
  string token = 2;
}

message GetUserRequest {
  string uuid = 1;
}

message GetUserResponse {
  User user = 1;
}

message User {
  string uuid = 1;
  string full_name = 2;
  string username = 3;
  string email = 4;

  enum UserType {
    USER_TYPE_UNSPECIFIED = 0;
    USER_TYPE_ADMIN = 1;
    USER_TYPE_NORMAL = 2;
  }

  UserType type = 5;
  google.protobuf.Timestamp created_at = 6;
}

The updated folder for the protos will now be having 2 files inside it.

protos
├── todo.proto
└── user.proto

Updating Makefile

Now that we have a new proto file we will need to compile that as well for the Go application and for that we will need to update the makefile to add the new compilation command for the user.proto file.

.PHONY: all clean todo user

all: clean todo user

clean:
	rm -rf ./generated && mkdir generated

todo:
	protoc -I protos \
	--go_out=./generated --go_opt=paths=source_relative \
	--go-grpc_out=./generated --go-grpc_opt=paths=source_relative \
	./protos/todo.proto

user:
	protoc -I protos \
	--go_out=./generated --go_opt=paths=source_relative \
	--go-grpc_out=./generated --go-grpc_opt=paths=source_relative \
	./protos/user.proto

Compiling ProtoBufs

Since there are new proto files, we have to run the command make so that we can compile both the .proto files to get the Go auto-generated code for the ProtoBuf message and gRPC services.

After executing the make command, the updated generated folder will have the following files now:

generated/
├── todo_grpc.pb.go
├── todo.pb.go
├── user_grpc.pb.go
└── user.pb.go

Implementing Repositories

Let’s get into the repository implementation for the data layer. We will create an interface for the repository for the users and for the todos.

We will create a new folder ``/internal/repository. Then we will add three files to it using the command touch repositories.go todo_repository.go user_repository.go`.

Here’s the code:

package repository

import (
	pb "ashokdey.com/grpc-example/generated"
)

type UserRepository interface {
    Save(user *pb.User) (*pb.User, error)
    FindByID(id string) (*pb.User, error)
    FindAll() ([]*pb.User, error)
    Delete(id string) error
    UpdateUser(user *pb.User) error
    FindByEmail(email string) (*pb.User, error)
}

type TodoRepository interface {
    Save(todo *pb.Todo) (*pb.Todo, error)
    FindByID(id string) (*pb.Todo, error)
    FindAll() ([]*pb.Todo, error)
    Delete(id string) error
    UpdateTodo(todo *pb.Todo) error
}

Now we have the repository interface defined. We can have it’s implementation for any data layer be it MySQL, Mongo, Cassandra or In-Memory. Let’s Look at the MySQL implementations of the repositories.

Todo Repository

We will implement the repository methods for the todos table of MySQL database. If a struct implements all the methods of an interface with identical signatures, Go implicitly considers the struct to implement that interface. This is an example of Go’s static structural typing, often described as duck typing.

package repository

import (
    "database/sql"
    "errors"
    "log"
    "time"

    pb "ashokdey.com/grpc-example/generated"
    "github.com/google/uuid"
    "google.golang.org/protobuf/types/known/timestamppb"
)

type MySQLTodoRepository struct {
    // the db instance is for the SQL driver to execute the queries 
    db *sql.DB
}

func NewMySQLTodoRepository(db *sql.DB) *MySQLTodoRepository {
    // accept the db instance and return the todo_repository instance 
    return &MySQLTodoRepository{db: db}
}

func (r *MySQLTodoRepository) Save(todo *pb.Todo) (*pb.Todo, error) {
    query := "INSERT INTO todos (uuid, title, description) VALUES (?, ?, ?)"

    // assign uuid
    todo.Uuid = uuid.NewString()

    _, err := r.db.Exec(
        query, todo.Uuid, todo.Title,
        todo.Description,
    )
    if err != nil {
        log.Printf("error creating todo: %+v", err)
        return nil, err
    }

    todo, err = r.FindByID(todo.Uuid)
    if err != nil {
        log.Printf("error finding todo: %+v", err)
        return nil, err
    }
    return todo, nil
    }

    func (r *MySQLTodoRepository) FindByID(id string) (*pb.Todo, error) {
    query := "SELECT uuid, title, description, is_done, priority, created_at FROM todos WHERE uuid = ?"

    row := r.db.QueryRow(query, id)

    todo := &pb.Todo{}
    if err := row.Scan(
        &todo.Uuid, &todo.Title, &todo.Description, &todo.Done,
        &todo.Priority, &todo.CreatedAt,
    ); err != nil {
        log.Printf("error finding todo: %+v", err)
        return nil, err
    }

    return todo, nil
}

func (r *MySQLTodoRepository) FindAll() ([]*pb.Todo, error) {
    query := "SELECT uuid, title, description, is_done, priority, created_at FROM todos"

    rows, err := r.db.Query(query)
    if err != nil {
        log.Printf("error finding todos: %+v", err)
        return nil, err
    }
    defer rows.Close()

    var todos []*pb.Todo
    for rows.Next() {
        // holders
        var todo pb.Todo
        var priority string
        var createdAt time.Time

        if err := rows.Scan(
            &todo.Uuid, &todo.Title, &todo.Description, &todo.Done,
            &priority, &createdAt,
        ); err != nil {
            return nil, err
        }

        // attach createdAt
        todo.CreatedAt = timestamppb.New(createdAt)

        switch priority {
        case "low":
            todo.Priority = pb.Todo_PRIORITY_LOW
        case "medium":
            todo.Priority = pb.Todo_PRIORITY_MEDIUM
        case "high":
            todo.Priority = pb.Todo_PRIORITY_HIGH
        default:
            todo.Priority = pb.Todo_PRIORITY_LOW
        }

        todos = append(todos, &todo)
    }

    return todos, nil
}

func (r *MySQLTodoRepository) UpdateTodo(todo *pb.Todo) error {
    if todo.GetUuid() == "" {
        return errors.New("todo Id is required for update")
    }

    query := `
        UPDATE todos
        SET title = ?, description = ?, priority = ?, is_done = ?
        WHERE uuid = ?
    `

    result, err := r.db.Exec(
        query,
        todo.Title,
        todo.Description,
        todo.Priority,
        todo.Done,
        todo.Uuid,
    )

    if err != nil {
        log.Printf("error updating todo: %+v", err)
        return err
    }

    rowsAffected, err := result.RowsAffected()
    if err != nil {
        log.Printf("no todo record updated: %+v", err)
        return err
    }

    if rowsAffected == 0 {
        return sql.ErrNoRows
    }

    return nil
}

func (r *MySQLTodoRepository) Delete(id string) error {
    query := "UPDATE todos SET is_deleted = 1 WHERE uuid = ?"
    _, err := r.db.Exec(query, id)
    return err
}

User Repository

package repository

import (
    "database/sql"
    "errors"
    "log"

    pb "ashokdey.com/grpc-example/generated"
    "github.com/google/uuid"
)

type MySQLUserRepository struct {
    db *sql.DB
}

func NewMySQLUserRepository(db *sql.DB) *MySQLUserRepository {
    return &MySQLUserRepository{db: db}
}

func (r *MySQLUserRepository) Save(user *pb.User) (*pb.User, error) {
    query := "INSERT INTO users (uuid, full_name, username, email) VALUES (?, ?, ?, ?)"

    // assign uuid
    user.Uuid = uuid.NewString()

    _, err := r.db.Exec(query, user.Uuid, user.FullName, user.Username, user.Email)
    if err != nil {
        log.Printf("error saving user = %+v", err)
        return nil, err
    }

    newUser, err := r.FindByID(user.Uuid)
    if err != nil {
        log.Printf("error finding user = %+v", err)
        return nil, err
    }

    return newUser, nil
}

func (r *MySQLUserRepository) FindByID(id string) (*pb.User, error) {
    query := "SELECT uuid, full_name, username, email FROM users WHERE uuid = ?"

    row := r.db.QueryRow(query, id)

    user := &pb.User{}
    if err := row.Scan(&user.Uuid, &user.FullName, &user.Username, &user.Email); err != nil {
        log.Printf("error finding user= %+v", err)
        return nil, err
    }

    return user, nil
}

func (r *MySQLUserRepository) FindByEmail(email string) (*pb.User, error) {
    query := "SELECT uuid, full_name,username, email FROM users WHERE email = ?"

    row := r.db.QueryRow(query, email)

    user := &pb.User{}
    if err := row.Scan(&user.Uuid, &user.FullName, &user.Username, &user.Email); err != nil {
        log.Printf("error finding user = %+v", err)
        return nil, err
    }

    return user, nil
}

func (r *MySQLUserRepository) FindAll() ([]*pb.User, error) {
    query := "SELECT uuid, full_name, username, email FROM users"

    rows, err := r.db.Query(query)
    if err != nil {
        log.Printf("error find all users= %+v", err)
        return nil, err
    }
    defer rows.Close()

    var users []*pb.User
    for rows.Next() {
        var user pb.User
        if err := rows.Scan(&user.Uuid, &user.FullName, user.Username, &user.Email); err != nil {
            log.Printf("error scanning user = %+v", err)
            return nil, err
        }
        users = append(users, &user)
    }

    return users, nil
}

func (r *MySQLUserRepository) UpdateUser(
	user *pb.User,
) error {
    if user.GetUuid() == "" {
        return errors.New("user ID is required for update")
    }

    query := `
        UPDATE users
        SET full_name = ?, username = ?, email = ?
        WHERE uuid = ?
    `

    result, err := r.db.Exec(
        query,
        user.FullName,
        user.Username,
        user.Email,
        user.Uuid,
    )
    if err != nil {
        log.Printf("error updating user= %+v", err)
        return err
    }

    rowsAffected, err := result.RowsAffected()
    if err != nil {
        log.Printf("no user record updated= %+v", err)
        return err
    }

    if rowsAffected == 0 {
        return sql.ErrNoRows
    }

    return nil
}

func (r *MySQLUserRepository) Delete(id string) error {
    query := "UPDATE users SET is_deleted = 1 WHERE uuid = ?"
    _, err := r.db.Exec(query, id)
    return err
}

In the user repository we have added an extra method called FindByEmail which will be particularly useful for the Login scenario.

cmd/repository/
├── repository.go
├── todo_repository.go
└── user_repository.go

The repository should contain 3 files as we can see above.

Using MySQL Repositories

Now that we have the repositories in place. Let’s use them in the services. We have to do a bit of refactoring here, create a folder server and then three files touch server.go todo_service.go user_service.go.

Todo Service

Here’s the code for the Todo service that is using the uuid and the TodoRepository.

package server

import (
    "context"
    "fmt"
    "log"

    "ashokdey.com/grpc-example/cmd/repository"
    pb "ashokdey.com/grpc-example/generated"
    "google.golang.org/grpc/codes"
    "google.golang.org/grpc/status"
    "google.golang.org/protobuf/types/known/emptypb"
    "google.golang.org/protobuf/types/known/timestamppb"
)

type TodoService struct {
    pb.UnimplementedTodoServiceServer
    repo repository.TodoRepository
}

func (ts *TodoService) CreateTodo(_ context.Context, req *pb.CreateTodoRequest) (*pb.CreateTodoResponse, error) {
    // create the todo
    todo := &pb.Todo{
        Title:     req.GetTitle(),
        Priority:  req.GetPriority(),
        Done:      false,
        CreatedAt: timestamppb.Now(),
    }

    if req.GetDescription() != "" {
        desc := req.GetDescription()
        todo.Description = &desc
    }

    // save in DB
    todo, err := ts.repo.Save(todo)
    if err != nil {
        log.Printf("error creating todo: %+v", err)
        return nil, status.Error(codes.Internal, fmt.Sprintf("failed to create Todo: %v", err))
    }

    // return the response
    return &pb.CreateTodoResponse{
        Todo: todo,
    }, nil
}

func (ts *TodoService) GetTodo(_ context.Context, req *pb.GetTodoRequest) (*pb.GetTodoResponse, error) {
    if req.GetUuid() == "" {
        return nil, status.Error(codes.InvalidArgument, "ID should be positive")
    }

    // get from DB
    val, err := ts.repo.FindByID(req.GetUuid())
    if err != nil {
        log.Printf("error finding todo: %+v", err)
        return nil, status.Error(codes.Internal, fmt.Sprintf("failed to get Todo: %v", err))
    }

    // return response
    return &pb.GetTodoResponse{
        Todo: val,
    }, nil
    }

func (ts *TodoService) ListTodos(_ context.Context, req *pb.ListTodosRequest) (*pb.ListTodosResponse, error) {
    // get all from db
    todos, err := ts.repo.FindAll()
    if err != nil {
        log.Printf("error listing todos: %+v", err)
        return nil, status.Error(codes.Internal, "something broke - find all todos")
    }

    // return response
    return &pb.ListTodosResponse{
        Todos: todos,
    }, nil
}

func (ts *TodoService) UpdateTodo(_ context.Context, req *pb.UpdateTodoRequest) (*pb.UpdateTodoResponse, error) {
    if req.GetUuid() == "" {
        return nil, status.Error(codes.InvalidArgument, "ID should be positive")
    }

    // get todo by id
    val, err := ts.repo.FindByID(req.GetUuid())
    if err != nil {
        log.Printf("error updating todo: %+v", err)
        return nil, status.Error(codes.Internal, fmt.Sprintf("failed to get Todo: %v", err))
    }

    // update the values from req
    val.Title = req.GetTitle()
    val.Done = req.GetDone()
    val.Uuid = req.GetUuid()

    if req.GetDescription() != "" {
        desc := req.GetDescription()
        val.Description = &desc
    }

    // update the record
    err = ts.repo.UpdateTodo(val)
    if err != nil {
        log.Printf("error updating todo: %+v", err)
        return nil, status.Error(codes.Internal, fmt.Sprintf("failed to update Todo: %v", err))
    }

    // return response
    return &pb.UpdateTodoResponse{
        Todo: val,
    }, nil
}

func (ts *TodoService) DeleteTodo(_ context.Context, req *pb.DeleteTodoRequest) (*emptypb.Empty, error) {
    if req.GetUuid() == "" {
        return nil, status.Error(codes.InvalidArgument, "ID should be positive")
    }

    err := ts.repo.Delete(req.GetUuid())
    if err != nil {
        log.Printf("error deleting todo: %+v", err)
        return nil, status.Error(codes.Internal, fmt.Sprintf("failed to delete Todo: %v", err))
    }

    // return response
    return &emptypb.Empty{}, nil
}

User Service

Similarly for the user service the user service is also using the uuid and the UserRepository. The code will look like:

package server

import (
    "context"
    "log"

    "ashokdey.com/grpc-example/cmd/repository"
    pb "ashokdey.com/grpc-example/generated"
    "google.golang.org/grpc/codes"
    "google.golang.org/grpc/status"
)

type UserService struct {
    pb.UnimplementedUserServiceServer
    repo repository.UserRepository
}

func NewUserService(repo repository.UserRepository) *UserService {
    return &UserService{repo: repo}
}

func (us *UserService) GetUser(_ context.Context, req *pb.GetUserRequest) (*pb.GetUserResponse, error) {
    if req.GetUuid() == "" {
        return nil, status.Error(codes.InvalidArgument, "invalid user_id")
    }

    user, err := us.repo.FindByID(req.GetUuid())
    if err != nil {
        log.Printf("error finding user: %+v", err)
        return nil, status.Error(codes.Internal, "something broke while finding user")
    }

    return &pb.GetUserResponse{
        User: user,
    }, nil
}

func (us *UserService) Login(_ context.Context, req *pb.LoginUserRequest) (*pb.LoginUserResponse, error) {
    if req.GetEmail() == "" {
        return nil, status.Error(codes.InvalidArgument, "missing email of user")
    }

    user, err := us.repo.FindByEmail(req.GetEmail())
    if err != nil {
        log.Printf("error finding user: %+v", err)
        return nil, status.Error(codes.Internal, "something broke while finding user")
    }

    return &pb.LoginUserResponse{
        Token: user.Email + " " + user.Uuid,
    }, nil
}

func (us *UserService) Register(_ context.Context, req *pb.RegisterUserRequest) (*pb.RegisterUserResponse, error) {
    // validate the payload
    fullName := req.GetFullName()
    email := req.GetEmail()
    username := req.Username

    if len(fullName) < 3 {
        return nil, status.Error(codes.InvalidArgument, "invalid full name of user")
    }
    if len(email) < 3 {
        return nil, status.Error(codes.InvalidArgument, "invalid email of user")
    }
    if len(username) < 3 {
        return nil, status.Error(codes.InvalidArgument, "invalid full name of user")
    }

    user, err := us.repo.Save(&pb.User{
        FullName: fullName,
        Email:    email,
        Username: username,
    })

    if err != nil {
        log.Printf("error registering user: %+v", err)
        return nil, status.Error(codes.Internal, "something broke while registering user")
    }

    return &pb.RegisterUserResponse{
        Uuid:  user.Uuid,
        Email: user.Email,
    }, nil
}

Note: For the Login service, right now we are just sending an arbitrary string value for token, we will replace this with JWT token once we cover authentication.

The Server

package server

import (
    "database/sql"
    "ashokdey.com/grpc-example/cmd/repository"
)

// the interface of TodoService embedded in our server
type Server struct {
    // the database connection to be 
    // passed to the repositories
    db          *sql.DB

    // will be used to register the 
    // services with the gRPC stubs
    UserService *UserService
    TodoService *TodoService
}

func NewServer(
    database *sql.DB,
    user_repo repository.UserRepository,
    todo_repo repository.TodoRepository,
) *Server {
    return &Server{
        db: database,
        UserService: NewUserService(
            user_repo,
        ),
        TodoService: NewTodoService(
            todo_repo,
        ),
    }
}

The server folder having the files should look like:

cmd/server/
├── server.go
├── todos_service.go
└── users_service.go

MySQL Connection

Now that we have the repositories defined and the services created using the MySQL repositories, we are ready to connect the gRPC server with the MySQL database. For this we need to do the following:

• Create the database table in the docker database instance.
• Create the connection in the Go application with the MySQL database.
• Wire up the server, database and repositories in the main.go

Defining Connection

We will create a file inside the cmd folder named mysql.go. The connection code will look like:

package db

import (
    "database/sql"
    "fmt"
    "time"

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

type MySQLConfig struct {
    User     string
    Password string
    Host     string
    Port     string
    DBName   string
}

func NewMySQLConnection(cfg MySQLConfig) (*sql.DB, error) {
    dsn := fmt.Sprintf(
        "%s:%s@tcp(%s:%s)/%s?parseTime=true",
        cfg.User,
        cfg.Password,
        cfg.Host,
        cfg.Port,
        cfg.DBName,
    )

    db, err := sql.Open("mysql", dsn)
    if err != nil {
        return nil, err
    }

    // setup the connection pool
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)

    // verify the connection
    if err := db.Ping(); err != nil {
        return nil, err
    }

    return db, nil
}

We are doing nothing special here, this is the standard Go and MySQL connection code that is also available in the official documentation. We have just declared a configuration struct to make our life bit easy.

There are a few setting related to connection pooling that we are providing to the MySQL driver and needs your focus. They are:

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)

Feel free to update them as per your requirement.

Creating the Tables

In MYSQL, to create and use tables for storing the data we first need to define a database. Since we are using docker, we can use the terminal go go inside the docker container of MySQL and login using the credentials which I have already highlighted.

Let’s get inside the running container to access the MySQL database.

docker exec -it local-mysql bash

Then we will connect to the MySQL database from inside the container.

mysql -u root -p 

It will ask for the password and we have to enter the password we set during the creation of the MySQL container which is grpc-example.

Now that we are inside the MySQL database we need to do a few things:

• Create the database grpc_todos.
• Create the user table.
• Create the todos table.

Note: Ideally we have migrations, a set of files that are basically the database manipulations we have performed over time, for now I am skipping migrations.

The terminal screenshot for all the MySQL operation from inside the docker container.

The commands are as follows:

-- create database 
CREATE DATABASE grpc_todos;

-- switch to the newly created database 
USE grpc_todos;

Now we can create the users and todos tables using the following commands:

-- users table 
CREATE TABLE `users` (
  `uuid` varchar(128) NOT NULL,
  `full_name` varchar(256) NOT NULL,
  `username` varchar(128) NOT NULL,
  `email` varchar(256) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `email` (`email`)
)

-- todos table 
CREATE TABLE `todos` (
  `uuid` varchar(128) NOT NULL,
  `user_id` varchar(128) DEFAULT NULL,
  `title` varchar(256) NOT NULL,
  `description` varchar(1024) NOT NULL DEFAULT '',
  `is_done` tinyint(1) NOT NULL DEFAULT '0',
  `priority` enum('high','medium','low') NOT NULL DEFAULT 'low',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`uuid`),
  KEY `fk_user_id` (`user_id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
)

In the above MySQL tables there are a few improvements that can be done. Can you identify them? If not then don’t stress on them we will try to improve the tables via migration in future posts.

Putting it All Together

Now we can wire everything together in the main.go file which is located at the root of the folder of our project grpc-example.

package main

import (
    "log"
    "net"

    "ashokdey.com/grpc-example/cmd/db"
    "ashokdey.com/grpc-example/cmd/repository"
    "ashokdey.com/grpc-example/cmd/server"
    pb "ashokdey.com/grpc-example/generated"
    "google.golang.org/grpc"
    "google.golang.org/grpc/reflection"
)

func main() {
    database, err := db.NewMySQLConnection(db.MySQLConfig{
        User:     "root",
        Password: "grpc-example",
        Host:     "localhost",
        Port:     "3306",
        DBName:   "grpc_todos",
    })
    if err != nil {
        log.Fatal(err)
    }

    // create a listener
    lis, err := net.Listen("tcp", ":9001")
    if err != nil {
        log.Fatalf("failed to listen: %v", err)
    }

    // create a gRPC server object
    srv := grpc.NewServer()

    // create our server
    server := server.NewServer(
        database,
        // pass the repositories
        repository.NewMySQLUserRepository(database),
        repository.NewMySQLTodoRepository(database),
    )

    // register services
    pb.RegisterTodoServiceServer(srv, server.TodoService)
    pb.RegisterUserServiceServer(srv, server.UserService)

    // register the reflection service on the grpc server
    reflection.Register(srv)

    log.Printf("server started at port %s\n", "9001")

    // providing the listener to the grpc service
    if err = srv.Serve(lis); err != nil {
        log.Fatalf("failed to serve gRPC server: %v", err)
    }
}

Now we can run the gRPC server via command - go run main.go. And we can also start the grpcui tool to test the RPCs. To start grpcui we will use the command.

grpcui -plaintext localhost:9001

gRPC Web UI available at http://127.0.0.1:49591/

grpcui with the user service

grpcui with the user service RPCs

Conclusion

In this post, we extended our gRPC-based application beyond in-memory data handling and took an important step toward a real-world backend by integrating a relational database. Also applying the repository pattern, we created a clean separation between business logic and data access, making the application easier to maintain, test, and evolve.

We have accomplished a lot in this post.

• Designed a relational database schema for users and todos
• Ran MySQL locally using Docker for a clean development setup
• Introduced layered architecture to separate transport, service, and data layers
• Implemented the repository pattern to abstract database access
• Integrated repositories into gRPC services
• Wired the database, repositories, and gRPC server together
• Tested the services using grpcui

There are a lot of things we missed from a real world production grade application. Let me list them so that we can take a few of them in future posts.

• Use a proper migration tool (e.g., golang-migrate or Flyway)
• Secure credentials using environment variables or a secret manager
• Implement password hashing and secure authentication flows
• Replace placeholder tokens with JWT based authentication
• Add request-level context propagation to database queries
• Handle soft deletes consistently in all queries
• Add indexes to frequently queried columns
• Add structured logging and monitoring (metrics, tracing)
• Enable TLS for gRPC communication
• Introduce rate limiting and input validation

Thanks for taking the time to read through this post. I hope it helped you understand how to integrate gRPC services with a relational database in a real-world app. Stay tuned for the next article in this series, where we’ll continue building and improving this application step by step.

Stay healthy, stay blessed!