Testing database logic, and applications that use databases can be difficult. In order to do it properly every test that is run needs to be run against a clean database. This is because tests can leave the database in an unknown state, and cause other tests to fail. This means that at the start of every test you need to seed your database with the data needed for the test, which makes writing tests take a lot longer. It also means that you need to write a lot of boilerplate code to set up and tear down the database for each test. This is where Dolt comes in. With the ability to rollback your changes to a given dolt commit, you can have seed data in your database, and make sure that the database is in the same state for each test run. This is done by saving the hash of the last commit on your branch at the start of the test suite, and then resetting the database to that commit before each test is run. This avoids some of the issues with other approaches such as the slowness of re-seeding for each test, or using nested transactions (which can become quite subtle, especially when there is concurrency in the tests themselves).

While these concepts are applicable to any programming language, I'm going to be showing you how to do it in Go using the Dolt Go Driver.

The Application

In May of 2021, I wrote about a Dolt powered robot bartender which I had built a prototype of. The initial software was written in Python, and backed by Dolt. Since then, I've built a new version of the robot:

Now I'm writing new software for it. The new server is written in Go and is backed by Dolt. In this blog I'll show how I'm using Dolt to test this application.

DBSuite

Within the Go ecosystem, Stretchr Inc provides a very popular testing framework called testify. It provides a lot of great features that help make your tests more readable, and easier to write. In the suite package, the file interfaces.go provides interfaces that can be implemented when writing your own test suite. suite.TestingSuite is the core interface to implement for writing tests which share common setup and teardown logic, and other interfaces within that file can be implemented to give your suite additional functionality. suite.Suite provides the basic implementation of a suite.TestingSuite and typically you will embed this in your test suite struct.

In this application I have created a reusable test suite called DBSuite which handles connecting to, storing the initial state of a test database, and rolling back the database to the initial state before each test is run. This suite is then embedded in the test suites of other packages which use a test database. DBSuite handles two different types of databases.

1. Databases that do not exist, but which we have migration scripts for. These databases are created, and initialized during suite setup. They do not have any data in them beyond what was put there by the migration scripts.
2. Databases that have been created and seeded with data and are accessible on the filesystem. For testing, these databases should be stored within a "testdata" directory.

We will be looking at the second type of database throughout this blog.

SetupSuite

SetupSuite is the method that we'll implement to handle common suite initialization. This code will happen a single time for the entire suite before any tests are run.

In this method we first copy our test database to a temp directory. This isn't a requirement, however when writes occur against your database, Dolt writes chunks of data to new files in the filesystem. Because our testdata is managed by git, these chunk files show up as changes that need to be committed or reverted. This happens each time you run your tests if you don't copy the directory elsewhere before connecting to it and modifying it.

After we have our database copied, we connect to it, and then query the dolt_branches table to get the dolt commit hash of the last commit on our branch. We store that hash in our DBSuite struct so that we can use it later to reset the database.

func (s *DBSuite) SetupSuite() {
	// Copy our database directory so that any chunks written during the test, even
	// if they are rolled back, will not be in the git diff.
	if s.DbDir != "" {
		newDbDir := s.T().TempDir()
		err := cp.Copy(s.DbDir, newDbDir)
		if err != nil {
			panic(err)
		}

		s.DbDir = newDbDir
	}

	// Append the branch to the database name if a branch is provided.
	database := s.Database
	if len(s.Branch) > 0 {
		database = database + "/" + s.Branch
	}

	// Use the dolt driver to open a connection to the database.
	openDB, err := sql.Open("dolt", "file://"+s.DbDir+"?commitname=Test%20Committer&commitemail=test@test.com&database="+s.Database+"&multistatements=true")
	if err != nil {
		panic(err)
	}

	s.conn = &dbr.Connection{DB: openDB, Dialect: dialect.MySQL, EventReceiver: &dbr.NullEventReceiver{}}

	// Create a DBProvider which will provide the database to our tests
	s.DBProvider, err = dbutils.NewDBProvider(s.conn, s.Database, s.SchemaDir)
	if err != nil {
		panic(err)
	}
	sess := s.Session()

	// Query the database to get the hash of the last commit on our branch and store it
	tx, err := sess.BeginTx(context.Background(), &sql.TxOptions{Isolation: sql.LevelSerializable})
	if err != nil {
		panic(err)
	}
	defer tx.Rollback()

	var bh struct {
		Hash string `db:"hash"`
	}
	_, err = tx.Select("*").From("dolt_branches").Where(dbr.Eq("name", s.Branch)).Load(&bh)
	if err != nil {
		panic(err)
	}

	s.Hash = bh.Hash
}

BeforeTest

By implementing the BeforeTest method of the suite.BeforeTest interface from testify we can run code before each test is run. In this method we will call our resetToHash method which will reset our database to the commit hash which we saved at setup time. This will ensure that our database is in the same state for each test.

// BeforeTest is called before each test. It calls resetToHash to reset the database to the
// hash of the last commit on the branch.
func (s *DBSuite) BeforeTest(suiteName, testName string) {
	ctx := context.Background()
	err := s.Transaction(ctx, func(tx *dbr.Tx) error {
		err := s.resetToHash(ctx, tx)
		if err != nil {
			return fmt.Errorf("failed to reset to hash '%s': %w", s.Hash, err)
		}

		return tx.Commit()
	})

	if err != nil {
		panic(err)
	}
}

func (s *DBSuite) resetToHash(ctx context.Context, tx *dbr.Tx) error {
	// Add all tables to the staging area so that everything will be reset and new tables will be deleted
	_, err := tx.ExecContext(ctx, "call dolt_add('-A')")
	if err != nil {
		return err
	}

	// Reset the database to the hash of the last commit on the branch that we stored at set up
	_, err = tx.ExecContext(ctx, fmt.Sprintf("call dolt_reset('--hard','%s')", s.Hash))
	return err
}

Subtests

The suite.Suite struct provides a method called Run which allows you to run subtests within your test suite.

// Run provides suite functionality around golang subtests.  It should be
// called in place of t.Run(name, func(t *testing.T)) in test suite code.
// The passed-in func will be executed as a subtest with a fresh instance of t.
// Provides compatibility with go test pkg -run TestSuite/TestName/SubTestName.
func (suite *Suite) Run(name string, subtest func()) bool {
	...

This allows you to organize your tests hierarchically. However, our BeforeTest method will not be called for these subtests. In order to make sure that our database is in the correct state for these subtests we need to implement the SetupSubTest method of the suite.SetupSubTest interface from testify. In our method we will simply call our BeforeTest method (The suiteName and testName fields are left empty as we do not use them).

func (s *DBSuite) SetupSubTest() {
	s.BeforeTest("", "")
}

Accessing the Database

Now that we have a way to create a suite for testing code which uses our database, we'll add a DBSuite method which will allow us to run queries against our database.

func (s *DBSuite) BeginTx(ctx context.Context) (*dbr.Tx, error) {
	return s.Session().BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
}

This method will return a transaction object which is used to query our database, and it is how we will access the database in our tests.

Testing our Cocktails DB

Now that we have a way to create a suite for testing code which uses our database, we'll create a testSuite struct which embeds our DBSuite struct. We'll then create a TestCocktailsDBPackage function which serve as the entry point for all of the tests which use this test suite.

type testSuite struct {
	*test.DBSuite
}

func TestCocktailsDBPackage(t *testing.T) {
	testdataDbs := test.FindTestdataDBs()
	dbsuite := test.NewDBSuite("cocktails", "test", "", testdataDbs)
	suite.Run(t, &testSuite{DBSuite: dbsuite})
}

Now any testSuite methods that start with the word Test will be run as tests. For example:

func (s *testSuite) TestCocktails() {
	s.Run("GetCocktails", func() {
		s.Run("Get All Cocktails", s.testGetAllCocktails)
		s.Run("Get Cocktails With Names", s.testGetCocktailsWithNames)
	})
	s.Run("CreateCocktail", func() {
		s.Run("Create Valid Cocktails", s.testCreateCocktail)
		s.Run("Test Cocktail Uniqueness", s.testCocktailUniqueness)
	})
	s.Run("NormalizeCocktail", s.testNormalizeCocktail)
	s.Run("DeleteCocktail", s.testDeleteCocktail)
	s.Run("UpdateCocktail", s.testUpdateCocktail)
}

Here the TestCocktails method triggers several subtests, some of which have subtests of their own. This allows us to organize our tests in a way that makes sense. Then, within a subtest such as testDeleteCocktails we can write table-driven tests to test different scenarios, each of which runs as its own subtest.

testDeleteCocktails defines a table of tests that contain the subtest name, the keys of cocktails we will delete, how many cocktails are expected at the start of the test, how many are expected after the delete clause runs, and whether we expect an error to be returned then loops over them running each test as a subtest. We loop over the tests so that we can test multiple scenarios without having to write the same code over and over again.

func (s *testSuite) testDeleteCocktail() {
	tests := []struct {
		name                string
		toDelete            []string
		expectedCountBefore int
		expecteCountAfter   int
		expectErr           bool
	}{
		{
			name:                "Delete Nonexistent Cocktail",
			toDelete:            []string{"noexist"},
			expectedCountBefore: initialCocktailsInTestDB,
			expecteCountAfter:   initialCocktailsInTestDB,
			expectErr:           true,
		},
		{
			name:                "Delete Single Cocktail",
			toDelete:            []string{"boulevardier"},
			expectedCountBefore: initialCocktailsInTestDB,
			expecteCountAfter:   initialCocktailsInTestDB - 1,
		},
		{
			name:                "Delete Multiple Cocktails",
			toDelete:            []string{"boulevardier", "americano"},
			expectedCountBefore: initialCocktailsInTestDB,
			expecteCountAfter:   initialCocktailsInTestDB - 2,
		},
		{
			name:                "Delete Same Cocktail Twice",
			toDelete:            []string{"boulevardier", "boulevardier"},
			expectedCountBefore: initialCocktailsInTestDB,
			expecteCountAfter:   initialCocktailsInTestDB - 1,
		},
	}

	for _, test := range tests {
		s.Run(test.name, func() {
			ctx := context.Background()
			tx, err := s.BeginTx(ctx)
			s.Require().NoError(err)

			cocktails, err := GetCocktails(ctx, tx)
			s.Require().NoError(err)
			initialLen := len(cocktails)
			s.Require().Equal(test.expectedCountBefore, initialLen)

			err = DeleteCocktails(ctx, tx, test.toDelete...)
			if test.expectErr {
				s.Require().Error(err)
			} else {
				s.Require().NoError(err)
			}

			cocktails, err = GetCocktails(ctx, tx)
			s.Require().NoError(err)
			s.Require().Len(cocktails, test.expecteCountAfter)
		})
	}
}

The test itself is pretty simple. We begin a transaction that we use to access our database, then we query the database to get the initial number of cocktails in the database. We then call our DeleteCocktails method which deletes the cocktails we specified in the test. We then query the database again to get the number of cocktails in the database after the delete clause has run. Finally we assert that the number of cocktails in the database is what we expect it to be.

The thing you won't see in this test is any code to set up the database. This is because we seeded our database with data before we started writing tests. It saves us a lot of time, and makes our tests easier to read, though it does require familiarity with the data in the database, but the database can be inspected using the Dolt CLI. If we want to change our schema or our test data, we can do so using the CLI and then commit the changes to our database on our test branch.

Conclusion

Using Dolt to test your applications can save you a lot of time, and allows you to test your application against your real database logic rather than just deal with mock data. It also allows you to test your application against the real database that you will use in production which should provide a greater degree of confidence that your application will work as intended. If you want to see the full code for the tests in this blog, you can find it here.

In addition to providing a great way to test your applications, Dolt is the only database that allows you to version your data like you do your code. Functionality like data diff (to see how your data has changed over time) and branch and merge (to allow you to work on new features without affecting the main branch) are just some of the features that make Dolt a great database for your applications, many of which can only be found on Dolt. Check out the project, and if you have any questions, or want to chat about how you're using Dolt in your applications, join our Discord server.