Skip to main content

MySQL Design Patterns in Go

This document illustrates how to implement the Repository Pattern in Go for interacting with a MySQL database.

Repository Pattern​

The Repository Pattern is a design pattern that provides an abstraction layer between the data access layer (e.g., database operations) and the business logic of an application. This promotes loose coupling, testability, and maintainability.

1. Define the Entity​

Start by defining the entity (struct) that represents your database table.

package entity

type Comment struct {
	ID      int32  `json:"id"`
	Email   string `json:"email"`
	Comment string `json:"comment"`
}
  • Purpose: Represents a row in the comments table.
  • Fields:
    • ID: Unique identifier of the comment (int32).
    • Email: Email address associated with the comment (string).
    • Comment: The actual comment text (string).
  • JSON Tags: The json:"..." tags allow the struct to be easily serialized to JSON format.

2. Define the Repository Interface​

Next, define an interface that specifies the contract for interacting with the Comment entity in the database.

package repository

import (
	"context"
	"database/sql"
	"your_project_name/entity" // Replace with your project's import path
)

type CommentRepository interface {
	Insert(ctx context.Context, comment entity.Comment) (entity.Comment, error)
	FindByID(ctx context.Context, id int32) (entity.Comment, error)
	FindAll(ctx context.Context) ([]entity.Comment, error)
}
  • Purpose: Defines the operations that can be performed on the Comment entity in the database.
  • Interface CommentRepository:
    • Insert(ctx context.Context, comment entity.Comment) (entity.Comment, error): Inserts a new comment into the database.
    • FindByID(ctx context.Context, id int32) (entity.Comment, error): Retrieves a comment by its ID.
    • FindAll(ctx context.Context) ([]entity.Comment, error): Retrieves all comments from the database.
  • Context: All methods use context.Context for cancellation and timeout handling.

3. Implement the Repository​

Implement the interface with a concrete type that interacts directly with the MySQL database.

package repository

import (
	"context"
	"database/sql"
	"log"
	"your_project_name/entity" // Replace with your project's import path
)

type commentRepositoryImpl struct {
	DB *sql.DB
}

func NewCommentRepository(db *sql.DB) CommentRepository {
	return &commentRepositoryImpl{DB: db}
}

func (repo *commentRepositoryImpl) Insert(ctx context.Context, comment entity.Comment) (entity.Comment, error) {
	sqlExec := "INSERT INTO comments(email, comment) VALUES(?,?)"
	result, err := repo.DB.ExecContext(ctx, sqlExec, comment.Email, comment.Comment)
	if err != nil {
		return entity.Comment{}, err // Return an empty Comment struct on error.
	}

	id, err := result.LastInsertId()
	if err != nil {
		return entity.Comment{}, err // Return an empty Comment struct on error.
	}

	comment.ID = int32(id) // Set the generated ID on the comment entity. **Important!**
	return comment, nil
}

func (repo *commentRepositoryImpl) FindByID(ctx context.Context, id int32) (entity.Comment, error) {
	query := "SELECT id, email, comment FROM comments WHERE id = ? LIMIT 1"
	rows, err := repo.DB.QueryContext(ctx, query, id)
	if err != nil {
		return entity.Comment{}, err // Return an empty Comment struct on error.
	}
	defer rows.Close()

	comment := entity.Comment{} // Create a comment variable to store the result.

	if rows.Next() {
		err := rows.Scan(&comment.ID, &comment.Email, &comment.Comment)
		if err != nil {
			return entity.Comment{}, err // Return an empty Comment struct on error.
		}
		return comment, nil
	} else {
		return entity.Comment{}, sql.ErrNoRows // Return an empty Comment struct and sql.ErrNoRows if no rows were found.
	}
}

func (repo *commentRepositoryImpl) FindAll(ctx context.Context) ([]entity.Comment, error) {
	query := "SELECT id, email, comment FROM comments"
	rows, err := repo.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, err // Return nil slice and the error
	}
	defer rows.Close()

	var comments []entity.Comment
	for rows.Next() {
		comment := entity.Comment{} // Create a comment variable to store the result.
		err := rows.Scan(&comment.ID, &comment.Email, &comment.Comment)
		if err != nil {
			// It's important to log or handle errors that occur *within* the loop.
			log.Printf("Error scanning row: %v", err)
			continue // Skip to the next row
		}
		comments = append(comments, comment) // Append data to comments array
	}

	if err := rows.Err(); err != nil { // Check for errors *after* iterating through all rows.
		return nil, err
	}

	return comments, nil
}

  • commentRepositoryImpl struct: Holds the database connection.
  • NewCommentRepository function: Constructor for the repository. Takes a *sql.DB as a dependency.
  • Insert method:
    • Executes an INSERT statement.
    • Retrieves the last inserted ID using result.LastInsertId().
    • Sets the ID on the returned Comment entity.
  • FindByID method:
    • Executes a SELECT statement with a WHERE clause.
    • Uses rows.Scan() to populate a Comment struct.
    • Returns sql.ErrNoRows if no row is found.
  • FindAll method:
    • Executes a SELECT statement to retrieve all rows.
    • Iterates over the rows using rows.Next().
    • Uses rows.Scan() to populate a Comment struct for each row.
    • Appends each Comment struct to a slice.
    • It's critical to check rows.Err() after rows.Next() returns false.

4. Example Usage (and Testing)​

Create a test file to demonstrate how to use the repository:

package repository

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"os"
	"testing"
	"your_project_name/entity" // Replace with your project's import path

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

// GetConnection establishes a database connection for testing.
func GetConnection() *sql.DB {
	dsn := "user:password@tcp(localhost:3306)/database_name?charset=utf8mb4&parseTime=True&loc=Local" // Replace with your DSN.
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("Failed to connect to MySQL: %v", err)
	}

	// Test the connection
	if err := db.Ping(); err != nil {
		log.Fatalf("Failed to ping MySQL: %v", err)
	}

	return db
}

func TestInsert(t *testing.T) {
	db := GetConnection()
	defer db.Close()

	commentRepository := NewCommentRepository(db)

	comment := entity.Comment{
		Email:   "[email protected]",
		Comment: "This is a test comment.",
	}

	insertedComment, err := commentRepository.Insert(context.Background(), comment)
	if err != nil {
		t.Fatalf("Failed to insert comment: %v", err)
	}

	fmt.Printf("Inserted comment: %+v\n", insertedComment)  // Print the inserted comment including the generated ID
}


func TestFindByID(t *testing.T) {
	db := GetConnection()
	defer db.Close()

	commentRepository := NewCommentRepository(db)

	// Insert a comment so we have something to find.
	comment := entity.Comment{
		Email:   "[email protected]",
		Comment: "This is a comment for FindByID test.",
	}

	insertedComment, err := commentRepository.Insert(context.Background(), comment)
	if err != nil {
		t.Fatalf("Failed to insert comment for FindByID: %v", err)
	}

	foundComment, err := commentRepository.FindByID(context.Background(), insertedComment.ID)
	if err != nil {
		t.Fatalf("Failed to find comment by ID: %v", err)
	}

	if foundComment.Email != "[email protected]" {
		t.Errorf("Expected email '[email protected]', but got '%s'", foundComment.Email)
	}

	fmt.Printf("Found comment: %+v\n", foundComment)

}


func TestFindAll(t *testing.T) {
	db := GetConnection()
	defer db.Close()

	commentRepository := NewCommentRepository(db)

	comments, err := commentRepository.FindAll(context.Background())
	if err != nil {
		t.Fatalf("Failed to find all comments: %v", err)
	}

	for _, comment := range comments {
		fmt.Printf("%+v\n", comment) // Print the comment including the generated ID
	}

	fmt.Printf("Found %d comments.\n", len(comments))
}
  • GetConnection(): Establishes a database connection for testing. You'll need to replace the placeholder DSN (Data Source Name) with your actual MySQL connection information.
  • TestInsert(): Tests the Insert() method. It creates a new comment, inserts it into the database, and asserts that the insertion was successful (no error). It's good practice to log or print the inserted comment to see the generated ID.
  • TestFindByID(): Tests the FindByID() method. It first inserts a comment, then retrieves it by ID and asserts that the retrieved comment matches the inserted comment.
  • TestFindAll(): Tests the FindAll() method. It retrieves all comments from the database and iterates through them, printing each comment.
  • Import _ "github.com/go-sql-driver/mysql": This line is crucial. It imports the MySQL driver, which is needed to establish the database connection. The blank identifier _ is used because we're not directly using any of the driver's functions in the code; importing it registers the driver with the database/sql package.

Important Notes for Testing:

  • Database Setup: Before running the tests, make sure you have a MySQL database set up and running with a comments table. The table structure should match the Comment struct in the entity package. For example:

    CREATE TABLE comments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) NOT NULL,
        comment TEXT
    );

  • DSN: Replace the placeholder DSN in GetConnection() with the correct connection details for your MySQL database. This includes the username, password, host, port, and database name. Make sure the user you specify has the necessary permissions to create, read, update, and delete data in the database.

  • Clean Up: Consider adding test setup and teardown to ensure a consistent testing environment. For example, you might want to truncate the comments table before each test run to remove any existing data.

  • Error Handling: The test functions use t.Fatalf() to indicate a test failure. This will stop the test immediately. You can also use t.Errorf() to report a non-fatal error, which will allow the test to continue running.

  • Dependencies: Ensure you have the necessary dependencies installed. You'll need the go-sql-driver/mysql package for connecting to MySQL. You install it with:

go get github.com/go-sql-driver/mysql

Advantages of the Repository Pattern​

  • Abstraction: Separates the application logic from the data access logic.
  • Testability: Allows you to easily mock the data access layer for unit testing.
  • Maintainability: Simplifies code changes by decoupling components.
  • Flexibility: Allows you to switch between different data sources (e.g., MySQL, PostgreSQL, in-memory database) without affecting the application logic.

This example provides a solid foundation for implementing the Repository Pattern in Go for MySQL database interactions. Remember to adjust the code to fit your specific application requirements and database schema.