How to use database testing

<Copied from Discord from @Varun Sethu >
See the relevant PR for more information.

1. hey guys! Just some updates on how to write database bound tests (ill probs add a github wiki entry later) but basically all methods that use a database should do so via a DatabaseContext this is basically just an interface that wraps around a database connection and exposes methods for querying and execution. now there are 2 types that implement this interface: liveContext and testingContext, liveContext is a wrapper around a connection to our actual real database while testingContext is a wrapper around any random database that we're using for testing (this can be one spinned up locally on the fly or in the case of github actions an actual test_db) soooo.... when a HTTP request is made from an actual client (not as a test) all the service methods (the methods using the database) will be fed a liveContext, sometimes we wanna test the database bound methods and to do so we switch the liveContext for testingContext, all methods that require using a database must be called from an anonymous function passed into a special method attached to the testingContext type, basically if u wanna write a database test the general structure is:

   func TestSuperCoolDatabaseFeature(t *testing.T) { assert := assert.New(t) testingContext := database.NewTestingContext() testingContext.RunTest(func() { databaseBoundFn(testingContext) amICool := anotherDatabaseBoundFn(testingContext) assert.True(amICool) }) }

   now internally RunTest starts a postgresql transaction and switches the "exposed connection" to this transaction, all queries that are then made with that DatabaseContext are made against this transaction. Once the function finishes runTests then rollsback the transaction to delete the changes made by the unit test. The reason we have this is so that when you write a test the effects of the test on the database arent propagated to other unit tests and instead they are completely local to your test one annoying consequence of this however is that since everything is coupled in a transaction if one query fails then everything fails, to resolve this there is a "WillFail" method that creates a nested transaction for methods that you expect to fail, to use it just do smth like:

   func TestSuperCoolDatabaseFeature(t *testing.T) { assert := assert.New(t) testingContext := database.NewTestingContext() testingContext.RunTest(func() { databaseBoundFn(testingContext) didFail := testingContext.WillFail(func() error { return myShoddyMethod(testingContext) }) assert.True(didFail) }) }

   now some other new things to note: - any update you make to a liveContext from go test will cause a panic (so ur tests will fail), ideally we dont wanna be updating a live DB via tests - you cannot perform any DB queries on a testingContext unless its within a RunTest method, if u do so ur tests will panic and fail (edited)

2. [20:18] an example service method you may write is:

   // getRootID returns the id of the root directory func getRootID(ctx database.DatabaseContext) (int, error) { // pool is thread safe so its calm var parentID int err := ctx.Query("query stuff", []interface{}{}, &parentID) if err != nil { return 0, errors.New("failed to find root id") } return parentID, nil }