DEV Community

Oleksandr Ivanchenko
Oleksandr Ivanchenko

Posted on

How to test database interactions in golang applications

Testing of functions with database interactions always was challenging. Recently, I found a wonderful library go-sqlmock which will simplify writing tests and mocking database queries in golang applications a lot. And I want to share a short example of how to work with it.

First, we have to install it

go get

We have this function with SQL query:

func MenuByNameAndLanguage(ctx context.Context, db *sql.DB, name string, langcode string) (*models.Menu, error) {
    result, err := db.Query("SELECT id, langcode, title, link__uri, view_sidemenu FROM menu_link_content_data WHERE menu_name=? AND langcode=?",
    defer result.Close()

    var menuLinks []models.MenuLink
    for result.Next() {
         menuLink := models.MenuLink{}
         err = result.Scan(&menuLink.ID, &menuLink.Langcode, &menuLink.Title, &menuLink.URL, &menuLink.SideMenu)
         menuLinks = append(menuLinks, menuLink)    
    menu := &models.Menu{
         Name: name,
         Links: menuLinks,
    return menu, err
Enter fullscreen mode Exit fullscreen mode

This function just getting menu links by menu name and language.

And now let's test it.

We are going to test that MenuByNameAndLanguage function will return correct Menu structure.

package menu

import (


func TestShouldReturnCorrectMenu(t *testing.T) {

    // Creates sqlmock database connection and a mock to manage expectations.
    db, mock, err := sqlmock.New()

    if err != nil {
        t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
    // Closes the database and prevents new queries from starting.
    defer db.Close()

    // Here we are creating rows in our mocked database.
    rows := sqlmock.NewRows([]string{"id", "langcode", "title", "link__uri", "view_sidemenu"}).
        AddRow(1, "en", "enTitle", "/en-link", "0").
        AddRow(2, "en", "enTitle2", "/en-link2", "0")

    // This is most important part in our test. Here, literally, we are altering SQL query from MenuByNameAndLanguage
    // function and replacing result with our expected result. 
    mock.ExpectQuery("^SELECT (.+) FROM menu_link_content_data*").
        WithArgs("main", "en").

    ctx := context.TODO()

    // Calls MenuByNameAndLanguage with mocked database connection in arguments list. 
    menu, err := MenuByNameAndLanguage(ctx, db, "main", "en")

    // Here we just construction our expecting result.
    var menuLinks []models.MenuLink
    menuLink1 := models.MenuLink{
        ID:       1,
        Title:    "enTitle",
        Langcode: "en",
        URL:      "/en-link",
        SideMenu: "0",
    menuLinks = append(menuLinks, menuLink1)

    menuLink2 := models.MenuLink{
        ID:       2,
        Title:    "enTitle2",
        Langcode: "en",
        URL:      "/en-link2",
        SideMenu: "0",

    menuLinks = append(menuLinks, menuLink2)

    expectedMenu := &models.Menu{
        Name:  "main",
        Links: menuLinks,

    // And, finally, let's check if result from MenuByNameAndLanguage equal with expected result.// Here I used Testify library (
    assert.Equal(t, expectedMenu, menu)
Enter fullscreen mode Exit fullscreen mode

As you see everything in this example was pretty straightforward.

For mo details, you can refer to GoDocs.

Top comments (5)

ksbeasle profile image
Kahlil Beasley

Hi, quick question what is the point of ^ and * for your ^SELECT (.+) FROM menu_link_content_data* ?

glaucoleme profile image
Glauco Leme

It's a regex expression for validate incoming query.

zonetw profile image

How do you write test that require multiple query in one function ?

dspillere profile image
Daniel Andrade

Great post, thank you!

jpcorry profile image
John Corry

I have done it this way...and I have done it with a test version of the DB. The mock way is 1000% better IMO.