Testing database-dependent code?

[Nate Finch]

I was sad to notice that database/sql's DB was a struct and not an interface, which makes testing code that uses the database a little more difficult. Does anyone have some tips on how to set up code that talks to the database such that I can test it easily? 

I have something like this:

package users

type Mgr struct {

create *sql.Stmt

// etc

}

func NewMgr(db *sql.DB) *Mgr {

         // use db.Prepare to set up sql statements

}

func (m *Mgr) Create(email, username, password string) int, error {

hash, err := hash(password)

if err != nil {

return -1, err

}

var id int

err = m.create.QueryRow(email, username, hash).Scan(&id)

if err != nil {

return -1, err

}

return id, nil

}

My thought had been to mock out the DB calls so I didn't have to create an actual DB on disk.... I guess I could write a fake driver that does the same thing, and load that instead, but that seems awfully indirect.

Anyone have a good way to test this code? Or a better way to set up the code to make it more testable?

[Nate Finch]

That requires different SQL statements and possibly different behavior for testing vs. production (unless I use sqlite in production... which I'm not).  I had hoped to be able to mock out the database so I could always return a known value... so I could do something like "test when a user with that email already exists"... without having to write to code to create the user in an actual DB.  

Maybe a fake driver is the way to do that.  In which case, anyone already have one of these things written?

On Monday, March 25, 2013 9:26:18 AM UTC-4, benjam...@gmail.com wrote:

My thought had been to mock out the DB calls so I didn't have to create an actual DB on disk.... 

Say you use sqlite and open a memory-only database (":memory:")- does that fail to fulfill some need? 

[Julien Schmidt]

On Monday, March 25, 2013 3:27:55 PM UTC+1, Nate Finch wrote:

Maybe a fake driver is the way to do that.  In which case, anyone already have one of these things written?

 

http://code.google.com/p/go/source/browse/src/pkg/database/sql/fakedb_test.go

But it doesn't use SQL.

[Erik St. Martin]

Nate / Julien,

I actually have a very good start to exactly what you're talking about: https://github.com/erikstmartin/go-testdb it allows you to use a fake driver, and stub queries with responses/errors, so that a given SQL statement will always return the same results, it also has some helpers to turn csv data into rows/columns of the correct types understood by the sql package.

It still needs Prepared statements and Transaction support, but I should be getting around to that soon.

Erik

[Erik St. Martin]

http://godoc.org/github.com/erikstmartin/go-testdb has some more examples that aren't on the github README

[Nate Finch]

Awesome, thanks Erik. That's exactly what I was thinking of - something really simple that I could just hand-tune what the db calls return.

[Jason McVetta]

Are you interested in getting the DB out of your tests entirely; or just in making it easy to test DB-dependent code?  

If it's the latter, you might check out Drone.io.  It's a continuous integration service that can automagically spin up a fresh DB (Postgres or MySQL) when it runs your tests.  I've been playing with it lately - it's free for open source projects - and found it pretty useful for my needs.   Also fwiw, I think Drone itself is written in Go.  

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Brad Rydzewski]

Drone is in fact written in Go and since we're using the db/sql package I can share how we do our database testing.

We have a package where we define all of our database interactions using interfaces:

type ProjectService interface {

// Create a new Project.

Create(proj *Project) (bool, error)

// Update an existing Project in the database.

Update(proj *Project) (bool, error)

...

}

We then have an implementation of the interface that works with a postgres driver (all the select statements, prepared statements, etc). We have unit tests for each method that rely on a local database to verify all our sql syntax is correct and that we are mapping all of our columns correctly. These tests get run on our CI server every time we make a commit. Each build is executed in a fresh VM with an untainted postgres install (like Jason mentioned) which makes the unit testing more repeatable.

We also have a mock implementation of the interface so that we can run tests without a database all together:

type MockProjectService struct {

OnCreate             func(proj *Project) (bool, error)

OnUpdate             func(proj *Project) (bool, error)

...

}

func (t *MockProjectService) Create(proj *Project) (bool, error) {

return t.OnCreate(proj)

}

func (t *MockProjectService) Update(proj *Project) (bool, error) {

return t.OnUpdate(proj)

}

When unit testing our http.Handlers we use the mock implementation. This allows us to create very specific success and failure scenarios without having to worry about the database running locally and being populated with an exact dataset (which is fragile and can have cascading effects if a single unit test fails).

[Kamil Kisiel]

This is almost exactly what I have done the database-dependant services I've developed. I use interfaces and then implement one version using Postgres and another one using in-memory storage using maps and slices. I run both through the same test suite that tests the behaviour of the interface, and then use the in-memory version for testing other components of the system.

I like the idea you've presented here of a Mock-style implementation with pluggable functions, that would certainly make it easier to test some things as a unit-style test as opposed to integration.

[Peter Bourgon]

We also do exactly this at SoundCloud.

Sent from my iPhone

[Craig Weber]

Sorry for the late reply, but how do you reuse the test suite? Are you using a special test infrastructure besides that which is provided out of the box in Go? Any examples you could point me to would be appreciated.

[Kamil Kisiel]

Basically something like:

func testDB(t *testing.T, db DBInterface) {

    // tests on db are here

}

func TestDB1(t *testing.T) { 

    db1 := makeDB1()

    testDB(t, db1)

}

func TestDB2(t *testing.T) {

    db2 := makeDB2()

    testDB(t, db2)

}

With better type names of course :)

[gediminas....@gmail.com]

There is a very convenient package for this http://godoc.org/github.com/DATA-DOG/go-sqlmock
it has a sql driver implementation which allows to easily mock any database related operation,
with errors, results even argument comparison, which basically is everything you need to ensure the correct execution flow

[Matt Cottingham]

(+ list)

There is a fake driver in the standard library that you could probably use if you wanted to go that route: https://code.google.com/p/go/source/browse/src/pkg/database/sql/fakedb_test.go

I just took the approach suggested earlier in this thread and created an interface with the same methodset as sql.DB:

https://github.com/mattcottingham/dber/blob/master/README.md

It works fine when all you need to do is swap out the database implementation when running certain tests.

--

[gedimi...@gmail.com]

Fake driver in the standard library is for different purpose, etc it simulates a database, while the mock driver mocks it. It is basically the same as mocking an interface, you make your mocks to return data based on test cases, you do not create tables or whole application state snapshot, you just test the logic of a single method or a chain of those to have a test case scenario. what relates to sqlmock package it does not keep any state nor lets you define any, it just helps you ensure that the logical behavior is as expected. Interface based stubs is a very nice approach and I agree to it 100% sqlmock is a mock interface to sql, the idea behind it is the same

[tim.s...@gmail.com]

I like the look of the MockProjectService struct you describe.  Can you provide a bit more detail on how you use this mock implementation when testing your http.Handlers?  Being new to Go, I'm trying to figure out the best way to create http handlers where alternate storage implementations can be injected/set.

Thanks for any additional pointers.

Tim 

[s.esco...@gmail.com]

Brad, how do you inject your mocks services to your http.Handlers?