如何测试使用数据库（MySQL）的Go代码？

I have a gRPC service using MySQL and need to clear records after every test case. I try to wrap every test case with a transaction. It works if there's no transaction in my rpc code but fails if there are. And there'll be errors like:

can't start transaction
...
sql: Transaction has already been committed or rolled back

Then I try to use truncate to clear the records but some of the test cases fails randomly.

My code is like(I use gorm):

func foo(db *gorm.DB) {
    tx := db.Begin()
    // query and insert
    tx.Commit()
}
// Use transaction to do database cleanup
func TestFooVersion1() {
    testDB := initDB()
    tx = testDB.Begin() // setup
    foo(testDB)
    tx.Rollback() // teardown
}
// Use truncate to do database cleanup
func TestFooVersion2() {
    testDB := initDB()
    foo(testDB)
    truncateTables(testDB) // teardown
}
func truncateTables(db *gorm.DB) {
    // exec "TRUNCATE TABLE table;" for every table
}

What's a proper way to test the code using DB(MySQL)? (I don't like mock like go-sqlmock)

It's hard to say without seeing code, but based on the error it sounds like you're sharing a connection between multiple tests, and each one is trying to start a transaction. Make sure that each test open its own connection, starts its own transaction, and when it is finished, commits or rolls back and closes the connection.

Maybe you can doing integration testing using docker. Test directly to spawned container that has a active mysql.

You can use Test suite from testify. Every time you test, run the docker container, and test it with a live mysql.