I am currently working on a project that was originally a proof of concept i.e I am now to convert it to a real application. In it’s original form, it lacked tests, so I figured out that would be the first place I start from while refactoring and adding newer features.

While I was writing integration tests for mysql, I needed to be able to setup the database by creating appropriate schemas and all of that. Ideally, I would have used migrations that would run during app startup and tests initialization. But I didn’t have the luxury here 0, so I just moved the entire sql dump to testdata/init.sql.

The problem now was how to import the file into the database (during CI, as I obviously wouldn’t have access to the MySQL shell to run source file), I reached out for bufio#Scanner to implement some parsing but figured out it would not serve my usecase as it defaults to splitting content by lines. See here . I needed to be able to distinguish sql statements. A valid one for instance might span multiple lines and terminated by a semi-colon (;). I then decided to write a custom splitting function..

See this gist for a sample sql dump

Here is what I ended up with for the splitting

func TestDB_KeyExists(t *testing.T) {

	db, _ := sql.Open("user:[email protected](localhost:3306)/test?parseTime=true")

	f, err := os.Open("testdata/init.sql")
	require.NoError(t, err)

	buf := bufio.NewScanner(f)

	buf.Split(func(data []byte, atEOF bool) (int, []byte, error) {

		// Trim out unnecessary whitespaces
		trimSpaces := func(b []byte) []byte {
			return bytes.TrimSpace(b)
		}

		if len(data) == 0 {
			return 0, nil, nil
		}

		// SQL statements are delimited by ';'
		if i := bytes.IndexByte(data, ';'); i >= 0 {
			return i + 1, trimSpaces(data[0:i]), nil
		}

		if atEOF {
			return len(data), trimSpaces(data), nil
		}

		return 0, nil, nil
	})

	for buf.Scan() {
		db.Exec(buf.Text())
	}

	// Real test comes here
}

Hopefully this helps someone trying to implement something of this sort

Footnotes

[0] The major reason why I couldn’t use migrations was because the app uses an existing database..If I have made the dump the first migration, I risk having errors on running it against the production database as indexes that already exist would refuse to be created again and thus the app would refuse to start