Integration test with Go and PostgreSQL

Integration tests plays a crucial role in ensuring the reliability and functionality of our application by verifying the interactions between different components, such as communication between the application logic and the database.

In this article, I'll focus on setting up integration tests for a specific repository that interacts with a PostgreSQL database using Gorm.

Prerequisites:

Here's an example structure of MediaRepository method we'll be testing

type MediaRepository struct {
    db *gorm.DB
}

func NewMediaRepository(db *gorm.DB) *MediaRepository {
    return &MediaRepository{db: db}
}

func (repo *MediaRepository) CreateMedia(input *model.CreateMediaInput) (*model.Media, error) {
    media := model.Media{
        MediaType:    input.MediaType,
        UploadStatus: input.UploadStatus,
        MediaStatus:  "active",
    }
    result := repo.db.Create(&media)
    if result.Error != nil {
        return nil, result.Error
    }
    return &media, nil
}

func (repo *MediaRepository) UpdateMedia(id string, input *model.UpdateMediaInput) error {
    result := repo.db.Model(&model.Media{}).Where("id = ?", id).Updates(map[string]interface{}{
        "upload_status": input.UploadStatus,
    })
    return result.Error
}

In this code snippet we have:

• MediaRepository: This class encapsulates all database interaction related to the 'Media' table, abstracting CRUD operations through methods that accepts input models and return either the result of the operation or an error.
• Gorm Integration: Each method uses Gorm to execute database commands, such as inserting new records or updating existing ones.

In the following part, we'll be using the sqlmock a mock SQL driver from DataDog to simulate the behavior of database.

1. Initiating shared MockDB instance with TestMain function

The TestMain function in Go is a powerful tool used to set up and tear down global resources needed before and after running tests in a package, such as when initializing a mock database instance that can be used across multiple tests.
Here's a detailed breakdown of how it's used for setting up sqlmock:

package repositories_test

import (
    "os"
    "testing"
    "github.com/DATA-DOG/go-sqlmock"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

var db *gorm.DB
var mock sqlmock.Sqlmock

func TestMain(m *testing.M) {
    var err error
    db, mock, err = sqlmock.New() // Create a new instance of sqlmock
    if err != nil {
        panic("failed to create sqlmock")
    }

    gormDB, err := gorm.Open(postgres.New(postgres.Config{
        Conn: db,
    }), &gorm.Config{})
    if err != nil {
        panic("failed to open db")
    }

    db = gormDB
    code := m.Run() // Execute all the tests
    db.Close()      // Clean up after all tests are done
    os.Exit(code)
}

• db, mock, err = sqlmock.New(): This line initializes a new instance of sqlmock, which effectively creates a simulated connection that can be used to mock SQL operations.
• gormDB, err := grom.Open(...): At here, Gorm is configured to use the mock database connection created earlier, we could also specify some optional database configurations here.
• db = gormDB: This assignment makes the gormDB instance globally available to the tests in this package by assigning it to the package-level db variable.
• code := m.Run(): This will execute all the test functions in the package. It returns an exit code that indicates whether the tests passed(0) or failed(non-zero).
• os.Exit(code): This ensures that the exit code is correctly propagated to the calling process, which is essential for CI/CD pipelines to detect test failures.

Basically we using TestMain to manage the lifecycle of a shared mock database, tests within the package can run in a controlled environment where database interactions are fully simulated.

2. Testing code with Repository

After setting up a shared mock database instance, the next step is to implement tests that interact with this mock database through the repository layer.

func TestCreateMedia(t *testing.T) {
    t.Run("CreateMediaSuccess", func(t *testing.T) {
        // Setup input data
        input := &input.CreateMedia{
            MediaType:    "image",
            UploadStatus: "pending",
        }

        // Expected ID returned from the mock query
        mockUUID := uuid.New()

        // Begin transaction
        mock.ExpectBegin()

        // Mock the SQL query execution
        mock.ExpectQuery(`INSERT INTO "media" \("deleted_at","media_type","upload_status","media_status"\) VALUES \(\$1,\$2,\$3,\$4\) RETURNING "id"`).
            WithArgs(sqlmock.AnyArg(), input.MediaType, input.UploadStatus, "active").
            WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(mockUUID.String()))

        // Expect transaction commit
        mock.ExpectCommit()

        // Create repository instance
        repo := repositories.NewMediaRepository(db)

        // Execute the test
        result, err := repo.CreateMedia(input)

        // Assertions
        assert.NoError(t, err)
        assert.Equal(t, mockUUID.String(), result.ID.String())
    })
}

• Test Setup (TestCreateMedia): This function defines the test scenario for creating an entry. (Remember the function name must prefix with capital Test). It is structured using t.Run to allow for multiple sub-tests.
  
  

• Mock Transaction Setup (ExpectBegin): Initiates the expectation of a transaction. Transactions are used in database operations that need to be atomic. This reflects the practice of managing database changes within a transaction to maintain data integrity.
  
  

• Mock Query Execution (ExpectQuery): This line sets up the mock response for the SQL query executed by the CreateMedia method. It specifies the SQL statement, expected inputs, and the mocked return value (the UUID of the newly created media record). The RETURNING "id" clause in PostgreSQL queries is typical for immediately retrieving values of newly inserted rows, such as auto-generated IDs.
  (Overrides default generation such as ID)
  
  

• Commit Transaction (ExpectCommit): Ensures that a commit operation is expected as part of the transaction workflow. This mirrors the typical behavior in an application where changes are committed if all operations within the transaction succeed.
  
  

• Repository Instance Creation: Instantiates the MediaRepository with the mocked database connection (db), allowing the repository to perform operations using Gorm as if it was interacting with a real database.
  
  

• Execute the Test and Assertions: Calls the CreateMedia method with the predefined input and checks the outcomes. The assert.NoError verifies that the method did not return an error, indicating that the operation was successful. The assert.Equal ensures that the returned media ID matches the expected UUID, confirming that the method processes and returns the correct data.

Key Takeaways:

1. Use .Debug() to get the executed SQL statement

The .Debug() method in Gorm is straightforward to use and is particularly beneficial when paired with sqlmock during testing, as it allows you to verify that the queries generated by Gorm match the expectations set in your mock database configuration.

// media_repo
func (repo *MediaRepository) CreateMedia(input *model.CreateMediaInput) (*model.Media, error) {
    media := model.Media{
        MediaType:    input.MediaType,
        UploadStatus: input.UploadStatus,
        MediaStatus:  "active",
    }
    // Debugging the SQL query
    result := repo.db.Debug().Create(&media)
    if result.Error != nil {
        return nil, result.Error
    }
    return &media, nil
}

The SQL statement will be shown in terminal view:

2. Why ExpectQuery instead of ExpectExec?

In the context of SQL operations, particularly when interacting with databases like PostgreSQL, the distinction between using ExpectQuery and ExpectExec in sqlmock is crucial and is based on the nature of the SQL command being executed.

• ExpectExec: This method is used in sqlmock to mock expectations for SQL commands that perform operations but do not return any rows. Common use cases include INSERT, UPDATE, and DELETE commands.

• ExpectQuery: Conversely, ExpectQuery is used when the SQL command is expected to return one or more rows. This is commonly used with SELECT statements.

However!!!

In our testing scenario, ExpectQuery is used because the INSERT statement in PostgreSQL includes a RETURNING clause. The clause makes the INSERT operation return the generated id of the inserted row immediately, thus the operation needs to be treated as a query that fetches data.

3. SQL syntax of different databases

The setup for sqlmock generally remains consistent regardless of the database, but the SQL syntax and the expectations need to be adjusted according to the database-specific features and SQL dialect.

In our scenario, employing MySQL or PostgreSQL will necessitate distinct variations in the test code.

//  PostgreSQL
mock.ExpectQuery(regexp.QuoteMeta(`INSERT INTO "media" ("media_type", "upload_status") VALUES ($1, $2) RETURNING "id"`)).
    WithArgs("image", "pending").
    WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(1))

//  MySQL
mock.ExpectExec(regexp.QuoteMeta(`INSERT INTO media (media_type, upload_status) VALUES (?, ?)`)).
    WithArgs("image", "pending").
    WillReturnResult(sqlmock.NewResult(1, 1)) // Assuming ID 1, 1 row affected
mock.ExpectQuery("SELECT LAST_INSERT_ID()").
    WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(1))

Thanks for Reading 🙌 🙌 🙌