在Golang中测试数据库交互

I have an API that has a storage layer. It only does the database interactions and perform the CRUD operations. Now I want to test these functions. In my path API/storage/ , I have different packages having functions to interact with different tables in same database. Tables A, B and C are in same database.

My file hierarchy goes like:

--api
  --storage
    --A
      --A.go
      --A_test.go
    --B
    --C
  --server
    --A
  --testData
    --A.sql
    --B.sql

In this way I want to test the whole storage layer using command

go test ./...

The approach I was following is that I have a function RefreshTables which first truncates the table, then fills it with a fixed test data that I have kept in testData folder. For truncating I do :

db.Exec("SET FOREIGN_KEY_CHECKS = 0;")
db.Exec("truncate " + table)
db.Exec("SET FOREIGN_KEY_CHECKS = 1;")

As go test runs test functions of different packages in parallel by default, multiple sql connections get created and truncate runs on some other connection while set foreign key runs on some other connection randomly from connection pool.

I am not able to pass my tests if run together but all tests pass if run alone or package wise.

If I do :

go test ./... -p 1

which makes test functions run one by one, all the tests pass.

I have also tried using a transaction for truncate and locking table before truncate.

I checked this article (https://medium.com/kongkow-it-medan/parallel-database-integration-test-on-go-application-8706b150ee2e), and he suggests making different databases in every test function and dropping that database after function ends. I think this will be very time taking.

It would be really helpful if someone suggest the best method for testing database interactions in Golang.

I don't have much experience in integration testing, I'm not sure if mocking the data base drivers could work for you, but if so, I've been using go-sqlmock package for mocking sql database results in unit tests and works like a charm. You could use it and literally have a separated "database engine" for each of your tests. It's a bit time consuming since you have to manually tell the mock what queries to expect and what to return but trust me, it's a good time investment.

As I said before, I'm not sure if using this strategy suits your case, because if you are interested in knowing how your application behaves in a "real database scenario", like verifying that registries are actually saved, then mocking the database results is kind of useless.