How to unit testing with Gorm, mux, postgresql

Question

I'm new in Go and unit test. I build a samll side projecy called "urlshortener" using Go with Gorm, mux and postgresql.

There is a qeustion annoying me after search many articles.

To make the question clean, I delete some irrelevant code like connect db, .env, etc

My code is below(main.go):

package main

type Url struct {
    ID       uint   `gorm:"primaryKey"` // used for shortUrl index
    Url      string `gorm:"unique"`     // prevent duplicate url
    ExpireAt string
    ShortUrl string
}

var db *gorm.DB
var err error


func main() {
    // gain access to database by getting .env
    ...

    // database connection string
    ...

    // make migrations to the dbif they have not already been created
    db.AutoMigrate(&Url{})

    // API routes
    router := mux.NewRouter()

    router.HandleFunc("/{id}", getURL).Methods("GET")

    router.HandleFunc("/api/v1/urls", createURL).Methods("POST")
    router.HandleFunc("/create/urls", createURLs).Methods("POST")

    // Listener
    http.ListenAndServe(":80", router)

    // close connection to db when main func finishes
    defer db.Close()
}

Now I'm building unit test for getURL function, which is a GET method to get data from my postgresql database called urlshortener and the table name is urls.

Here is getURL function code:

func getURL(w http.ResponseWriter, r *http.Request) {
    params := mux.Vars(r)
    var url Url

    err := db.Find(&url, params["id"]).Error
    if err != nil {
        w.WriteHeader(http.StatusNotFound)
    } else {
        w.WriteHeader(http.StatusOK)
        json.NewEncoder(w).Encode(url.Url)
    }
}

This is work fine with my database. See curl command below:

I know that the unit test is not for mock data, and it aim to test a function/method is stable or not. Although I import mux and net/http for conncetion, but I think the unit test on it should be "SQL syntax". So I decide to focus on testing if gorm return the right value to the test function.

In this case, db.Find will return a *gorm.DB struct which should be exactly same with second line. (see docs https://gorm.io/docs/query.html)

db.Find(&url, params["id"])

SELECT * FROM urls WHICH id=<input_number>

My question is how to write a unit test on it for check the SQL syntax is correct or not in this case (gorm+mux)? I've check some articles, but most of them are testing the http connect status but not for SQL.

And my function do not have the return value, or I need to rewrite the function to have a return value before I can test it?

below is the test structure in my mind:

func TestGetURL(t *testing.T) {

    //set const answer for this test

    //set up the mock sql connection

    //call getURL()
    
    //check if equal with answer using assert

}

Update

According to @Emin Laletovic answer

Now I have a prototype of my testGetURL. Now I have new questions on it.

func TestGetURL(t *testing.T) {
    //set const answer for this test
    testQuery := `SELECT * FROM "urls" WHERE id=1`
    id := 1

    //set up the mock sql connection
    testDB, mock, err := sqlmock.New()
    if err != nil {
        panic("sqlmock.New() occurs an error")
    }

    // uses "gorm.io/driver/postgres" library
    dialector := postgres.New(postgres.Config{
        DSN:                  "sqlmock_db_0",
        DriverName:           "postgres",
        Conn:                 testDB,
        PreferSimpleProtocol: true,
    })
    db, err = gorm.Open(dialector, &gorm.Config{})
    if err != nil {
        panic("Cannot open stub database")
    }

    //mock the db.Find function
    rows := sqlmock.NewRows([]string{"id", "url", "expire_at", "short_url"}).
        AddRow(1, "http://somelongurl.com", "some_date", "http://shorturl.com")

    mock.ExpectQuery(regexp.QuoteMeta(testQuery)).
        WillReturnRows(rows).WithArgs(id)

    //create response writer and request for testing
    mockedRequest, _ := http.NewRequest("GET", "/1", nil)
    mockedWriter := httptest.NewRecorder()

    //call getURL()
    getURL(mockedWriter, mockedRequest)

    //check values in mockedWriter using assert

}

In the code, I mock the request and respone with http, httptest libs. I run the test, but it seems that the getURL function in main.go cannot receive the args I pass in, see the pic below.

when db.find called, mock.ExpectQuery receive it and start to compare it, so far so good.

db.Find(&url, params["id"])

mock.ExpectQuery(regexp.QuoteMeta(testQuery)).WillReturnRows(rows).WithArgs(id)

According to the testing log, it shows that when db.Find triggerd, it only excute SELECT * FROM "urls" but not I expected SELECT * FROM "urls" WHERE "urls"."id" = $1.

But when I test db.Find on local with postman and log the SQL syntax out, it can be excute properly. see pic below.

In summary, I think the problem is the responeWriter/request I put in getURL(mockedWriter, mockedRequest) are wrong, and it leads that getURL(w http.ResponseWriter, r *http.Request) cannot work as we expect.

Please let me know if I missing anything~

Any idea or way to rewrite the code would be help, thank you!

Answer 1

If you just want to test the SQL string that db.Find returns, you can use the DryRun feature (per documentation).

stmt := db.Session(&Session{DryRun: true}).Find(&url, params["id"]).Statement
stmt.SQL.String() //returns SQL query string without the param value
stmt.Vars // contains an array of input params

However, to write a test for the getURL function, you could use sqlmock to mock the results that would be returned when executing the db.Find call.

func TestGetURL(t *testing.T) {
    //set const answer for this test
    testQuery := "SELECT * FROM `urls` WHERE `id` = $1"
    id := 1
    
    //create response writer and request for testing

    //set up the mock sql connection
    testDB, mock, err := sqlmock.New()
    //handle error

    // uses "gorm.io/driver/postgres" library
    dialector := postgres.New(postgres.Config{
        DSN:                  "sqlmock_db_0",
        DriverName:           "postgres",
        Conn:                 testDB,
        PreferSimpleProtocol: true,
    })
    db, err = gorm.Open(dialector, &gorm.Config{})
    //handle error

    //mock the db.Find function
    rows := sqlmock.NewRows([]string{"id", "url", "expire_at", "short_url"}).
        AddRow(1, "http://somelongurl.com", "some_date", "http://shorturl.com")
    mock.ExpectQuery(regexp.QuoteMeta(testQuery)).
        WillReturnRows(rows).WithArgs(id)

    //call getURL()
    getUrl(mockedWriter, &mockedRequest)
    
    //check values in mockedWriter using assert

}

Answer 2

This Post and Emin Laletovic are really helps me alot.

I think I get the answer to this qeustion.

Let's recap this questioon. First, I'm using gorm for postgresql and mux for http services and build a CRUD service.

I need to write a unit test to check if my database syntax is correct (we assuming that the connection is statusOK), so we focus on how to write a unit test for SQL syntax.

But the handler function in main.go don't have return value, so we need to use mock-sql/ ExpectQuery(), this function will be triggered when the db.Find() inside getURL(). By doing this, we dont have to return a value to check if it match our target or not.

The problem I met in Update is fixed by This Post, building an unit test with mux, but that post is focusing on status check and return value.

I set the const answer for this test, the id variable is what we expect to get. Noticed that $1 I don't know how to change it, and I've try many times to rewrite but SQL syntax is still return $1, maybe it is some kind of constraint I dont know.

//set const answer for this test
    testQuery := `SELECT * FROM "urls" WHERE "urls"."id" = $1`
    id := "1"

I set the value pass into the getURL() by doint this

//set the value send into the function
    vars := map[string]string{
        "id": "1",
    }
//create response writer and request for testing
    mockedWriter := httptest.NewRecorder()
    mockedRequest := httptest.NewRequest("GET", "/{id}", nil)
    mockedRequest = mux.SetURLVars(mockedRequest, vars)

Finally, we call mock.ExpectationsWereMet() to check if anything went wrong.

if err := mock.ExpectationsWereMet(); err != nil {
        t.Errorf("SQL syntax is not match: %s", err)
    }

Below is my test code:

func TestGetURL(t *testing.T) {
    //set const answer for this test
    testQuery := `SELECT * FROM "urls" WHERE "urls"."id" = $1`
    id := "1"

    //set up the mock sql connection
    testDB, mock, err := sqlmock.New()
    if err != nil {
        panic("sqlmock.New() occurs an error")
    }

    // uses "gorm.io/driver/postgres" library
    dialector := postgres.New(postgres.Config{
        DSN:                  "sqlmock_db_0",
        DriverName:           "postgres",
        Conn:                 testDB,
        PreferSimpleProtocol: true,
    })
    db, err = gorm.Open(dialector, &gorm.Config{})
    if err != nil {
        panic("Cannot open stub database")
    }

    //mock the db.Find function
    rows := sqlmock.NewRows([]string{"id", "url", "expire_at", "short_url"}).
        AddRow(1, "url", "date", "shorurl")

    //try to match the real SQL syntax we get and testQuery
    mock.ExpectQuery(regexp.QuoteMeta(testQuery)).WillReturnRows(rows).WithArgs(id)

    //set the value send into the function
    vars := map[string]string{
        "id": "1",
    }

    //create response writer and request for testing
    mockedWriter := httptest.NewRecorder()
    mockedRequest := httptest.NewRequest("GET", "/{id}", nil)
    mockedRequest = mux.SetURLVars(mockedRequest, vars)

    //call getURL()
    getURL(mockedWriter, mockedRequest)

    //check result in mockedWriter mocksql built function
    if err := mock.ExpectationsWereMet(); err != nil {
        t.Errorf("SQL syntax is not match: %s", err)
    }

}

And I run two tests with args(1, 1) and args(1, 2), and it works fine. see pic below(please ignore the chinese words)