Mocking database return types with interfaces

How to correctly mock some <code>*sql.DB</code> methods for unit testing using interfaces? For example, I want to have a <code>Store</code> struct that is used as a datasource for my handlers. It has a <code>conn</code> field that is the <code>*sql.DB</code>. But in my tests I want to mock the DB, so I try to return not the actual <code>*sql.Rows</code> but an interface that the <code>*sql.Rows</code> should satisfy. Here is the code:

type TestRows interface {
    Scan ()
}

type TestDB interface {
    Query (query string, args ...interface{}) (TestRows, error) 
}

type Rows struct {
    //..
}

func (r *Rows) Scan () {
    fmt.Println("Scanning")
}

This is the <code>*sql.DB</code>

type DB struct {
    //...
}

func (d *DB) Query (query string, args ...inteface{}) (*Rows, error) {
    return &Rows{/* .... */}, nil
}

And when I want to instantiate the <code>Store</code>, it throws error

type Store struct {
    conn TestDB
}

func main() { 
    cl := Store {conn: &DB{}}
    fmt.Println("Hello, playground")
}

cannot use DB literal (type *DB) as type TestDB in field value:
	*DB does not implement TestDB (wrong type for Query method)
    	have Query(string) (*Rows, error)
	    want Query(string) (TestRows, error)

I would suggest that it's a bad idea to mock the DB. You can verify that queries are being called with a particular SQL string and return fake results but this really isn't useful. These tests add little value and install a false sense of confidence. There's absolutely no link between what your test accepts and what will actually run in your DB.

One of the real dangers is that your database schema could change but your mocks aren't updated. Then your code will fail in production but pass the tests.

Instead, implement some sort of data abstraction layer, e.g. like a Data Access Object (DAO).

You can write automatic integration tests that will test your DAO against your database. This will ensure that your DAO actually works and catch any errors like a query not being updated after a schema change.

You can then write unit tests for your other code. Anything that interacts with the database should use a mock/stub/fake DAO depending on what the test requires.

The definition of Query function needs to be:

func (d *DB) Query(query string, args ...interface{}) (TestRows, error) {
    return &Rows{count: 1}, nil
}

Edit:

If as -- you pointed out -- you can't change the implementation of the Query function because you need to meet other interfaces, then you need to adjust your own interfaces. In this case

type TestDB interface {
    Query(query string, args ...interface{}) (*Rows, error)
}