Dolt is a SQL database with Git-style versioning. Dolt ships with a MySQL compatible server that you can start on a repository using dolt sql-server. Once started, you can then connect to the running server using standard MySQL clients.

We now support C, Python, Java, and .NET MySQL clients through automated testing. Dolt should work with any MySQL client. Some MySQL clients do some weird stuff so we're testing as many as we can to make sure they all work as expected.

We'll be posting new supported clients in our documentation, so follow along there. If you want a client supported, come join us on our Discord server and let us know or submit a GitHub issue.

Test Design

We had a couple of design considerations when thinking about these new tests. We already had an automated test suite we really like that uses Bash Automated Testing System (BATS) that we blogged about. Could we leverage that system to test MySQL clients? We decided these tests would be better in a separate test suite. We would inevitably be installing a bunch of new, large dependencies like a Java Development Kit (JDK) and the .NET framework. We encourage our developers to run BATS before committing code and we didn't want to force them to download a large kit of dependencies. We also run BATS on Windows and Linux (and maybe more in the future) so the explosion of dependency and OS combinations would get rather hairy to manage.

So, once the above decision was made, how should these tests be executed? In order to execute the tests in each language we support, we would need to:

1. Install Dolt
2. Run dolt init and dolt sql-server to bootstrap the test
3. Install a language build framework and runtime
4. Install the language specific MySQL Client to test
5. Build and run the language specific test we wrote

Since we're already familiar with BATS, we decided to re-use that testing framework. We use Docker on Ubuntu to build the test environment. You can see the results here.

Details

Let's examine a test in a bit of detail.

setup() {
    REPO_NAME="dolt_repo_$$"
    mkdir $REPO_NAME
    cd $REPO_NAME

    dolt init
    let PORT="$$ % (65536-1024) + 1024"
    USER="dolt"
    dolt sql-server --host 0.0.0.0 --port=$PORT --user=$USER --loglevel=trace &
    SERVER_PID=$!
    # Give the server a chance to start
    sleep 1
}

teardown() {
    cd ..
    kill $SERVER_PID
    rm -rf $REPO_NAME
}

@test "python mysql.connector client" {
    python3 $BATS_TEST_DIRNAME/python/mysql.connector-test.py $USER $PORT $REPO_NAME
}

@test "node mysql client" {
    node $BATS_TEST_DIRNAME/node/index.js $USER $PORT $REPO_NAME
}

First, we make a repository. We append the process id (ie. pid or $$) of the process to the repository so we can have multiple tests running in parallel on the same machine. We randomize the port for the same reason.

We then dolt init a fresh repository. To start the MySQL Compatible server for the Dolt database, we run dolt sql-server in the repository directory with the dolt user and randomized port.

For the non-compiled languages like Python or Javascript (using Node), we then execute the test. It's a pretty simple test. It establishes a connection, performs a handful of queries, and verifies the results. If everything works it exits 0. Otherwise, it exits 1. For compiled languages like C and Java, we have an extra build step.

Finally in the teardown step, we kill the running Dolt SQL server and remove the directory.

Let's break down the Node test.

Here we grab the user, port and database name passed in from the command line and construct a configuration map. We use 127.0.0.1 instead of localhost to force a TCP/IP connection.

const mysql = require("mysql");

const args = process.argv.slice(2);

const user = args[0];
const port = args[1];
const db = args[2];

const config = {
  host: "127.0.0.1",
  user: user,
  port: port,
  database: db,
};

We make a database class to encapsulate the connection and query logic. This abstracts away the error handling so our main method can contain the actual test logic. We use a Promise so we can loop through a bunch of queries without chaining callbacks.

class Database {
  constructor(config) {
    this.connection = mysql.createConnection(config);
    this.connection.connect();
  }

  query(sql, args) {
    return new Promise((resolve, reject) => {
      this.connection.query(sql, args, (err, rows) => {
        if (err) return reject(err);
        return resolve(rows);
      });
    });
  }
  close() {
    this.connection.end((err) => {
      if (err) {
        console.error(err);
      } else {
        console.log("db connection closed");
      }
    });
  }
}

Now the test logic. We start by defining the queries and the JSON of the test results we expect in two arrays.

async function main() {
    const queries = [
			"create table test (pk int, value int, primary key(pk))",
			"describe test",
			"select * from test",
			"insert into test (pk, value) values (0,0)",
			"select * from test"
    ];

    const results = [
	[],
	[ { Field: 'pk',
	    Type: 'int',
	    Null: 'NO',
	    Key: 'PRI',
	    Default: '',
	    Extra: '' },
	  { Field: 'value',
	    Type: 'int',
	    Null: 'YES',
	    Key: '',
	    Default: '',
	    Extra: '' }
	],
	[],
	{
	    fieldCount: 0,
	    affectedRows: 1,
	    insertId: 0,
	    serverStatus: 0,
	    warningCount: 0,
	    message: '',
	    protocol41: true,
	    changedRows: 0
	},
	[ { pk: 0, value: 0 } ]
    ];

Then, we loop through the queries and compare against the expected results. If the two match, we exit 0. Otherwise, we exit 1 which will fail the test. We avoid a callback mess by using promises.

    const database = new Database(config);

		await Promise.all(queries.map((query, idx) => {
			const expected = results[idx];
			return database.query(query).then(rows => {
				const resultStr = JSON.stringify(rows);
				const result = JSON.parse(resultStr);
				if (resultStr !== JSON.stringify(expected) ) {
					console.log("Query:", query);
					console.log("Results:", result);
					console.log("Expected:", expected);
					throw new Error("Query failed")
				} else {
					console.log("Query succeeded:", query)
				}
			}).catch(err => {
				console.error(err)
				process.exit(1);
			});
		}));

		database.close()
    process.exit(0)
}

main();

This is pretty straightforward code and it's pretty similar in every language. These tests can be used as example code to get started connecting to Dolt if you're wondering how to do it in a language whose MySQL client we support.

Gotchas

For .NET, we discovered the standard client makes use of utc_timestamp(), timediff(), character sets, and collations. Dolt was either missing or had an incomplete implementation of those features. As a result of our .NET testing, we implemented these in go-mysql-server and thus made those features available to Dolt. We're glad we tested.

Conclusion

Testing programmatic clients is the start of a long journey to support as many Dolt integrations as possible. We see Dolt as a critical piece of many data workflows. If you want a client supported, come join us on our Discord server and let us know or submit a GitHub issue. We're just getting started.