Dolt is a SQL database that supports Git-like functionality, including branch, merge, clone, push and pull. Dolt targets compatibility with MySQL as an existing SQL dialect and wire protocol. We built Dolt on top of an excellent open-source implementation of a MySQL-compliant SQL engine called go-mysql-server, but there's still a gap between what MySQL supports and what Dolt supports today. We are constantly working on Dolt to close this gap. We're happy to announce that, as of Dolt 0.22.0, Dolt now supports prepared statements.

This was a big missing feature for us and we're glad to land support for prepared statements, paving the way for easier integrations of existing tools and workflows with Dolt, and making Dolt easier to target for OLTP workloads.

In this blog post I'll quickly walk through our prepared statement support and what it ends up looking like in the implementation.

Prepared Statements in SQL

Prepared statements are a way for a SQL client to optimize the performance of running the same query multiple times, and are also fundamental to the way variable values within queries can be supplied safely to a SQL engine. In a normal SQL query, constants can be supplied directly inline for evaluation by the engine:

SELECT * FROM students WHERE name = 'Robert';

When the SQL engine gets that query, it parses it, analyzes it into an execution plan, and executes the plan, evaluating the filters and projections as necessary in order to construct the correct result set.

A prepared statement can replace the constants in the query with placeholders, and can supply the correct values for those placeholders later. The SQL engine can parse and come up with the query plan for the statement before the variable values are supplied. The variable values themselves can be supplied without regard for SQL syntax quoting or escaping, which can be safer than trying to interpolate values in a programming language into a string which will become a SQL query. For example, the above SQL statement could look like:

SELECT * FROM students WHERE name = ?;

The ? is a placeholder and when we execute this query we will need to supply a parameter for what name should actually equal. We can execute the same prepared statement multiple times, supplying different parameter values each time.

Prepared Statement Support in Dolt

Prepared statements are most often used in programmatic interfaces, and that's where Dolt supports them currently. In particular, Dolt supports them when it's running in sql-server mode and speaking the MySQL wire protocol, where it has newly added support for the ComStmtPrepare and ComStmtExecute commands. The MySQL client libraries use these commands when implementing client-side features such as go's

func (db *DB) Prepare(query string) (*Stmt, error)
func (s *Stmt) Exec(args ...interface{}) (Result, error)

and Java's

PreparedStatement Connection.prepareStatement(String sql)
void PreparedStatement.setString(int parameterIndex, int x)
ResultSet PreparedStatement.executeQuery()

A small example of this in action is the following program, which prints the latest confirmed corona virus cases for a list of countries provided on the command line:

func main() {
        if len(os.Args) < 2 {
                fmt.Println("query-example: prints the results of a query from sql.")
                fmt.Println("usage: go run . <country_name> [<country_name>...]")
                os.Exit(1)
        }
        db, err := sql.Open("mysql", "root@tcp(127.0.0.1:3306)/corona_virus")
        panicIfErr(err)
        defer db.Close()
        stmt, err := db.Prepare("select max(observation_time), max(confirmed_count) from cases natural join places where places.country_region = ?")
        panicIfErr(err)
        defer stmt.Close()
        for i := 1; i < len(os.Args); i++ {
                region := os.Args[i]
                rows, err := stmt.Query(region)
                panicIfErr(err)
                var t *string
                var count *int64
                if rows.Next() {
                        err := rows.Scan(&t, &count)
                        panicIfErr(err)
                }
                if t != nil {
                        fmt.Printf("%s: %d cases on %v.\n", region, *count, *t)
                } else {
                        fmt.Printf("%s not found.\n", region)
                }
                panicIfErr(rows.Close())
        }
}

Here's an example of it in action:

$ go run . US France Croatia 'United Kingdom'
US: 7762546 cases on 2020-10-11 00:00:00.
France: 702148 cases on 2020-10-10 00:00:00.
Croatia: 20440 cases on 2020-10-11 00:00:00.
United Kingdom: 603716 cases on 2020-10-11 00:00:00.

Adding support for prepared statements in Dolt was relatively straightforward. We were fortunate that the underlying MySQL wire protocol implementation which go-mysql-server is built on, vitess, already supported bind variables in its lexer and parser and already supported STATMENT_PREPARE and STATEMENT_EXECUTE in its wire protocol implementation. To implement support within go-mysql-server and Dolt involved:

1. adding bind variable support to our parse tree

2. adding support for bind variable nodes to our query analyzer, which transforms the parse tree into an execution plan

3. adding appropriate implementations for prepare and execute, so that we parse and analyze the incoming statement when we get a prepare and we execute the analyzed statements with the appropriate variable bindings when we get an execute.

This was also the first time that we needed to convert from MySQL wire values into our engine's internal values, so we had to write a little bit of wire protocol conversion code as well. Previously we had only ever needed to go the other way, with values in a result set, for example, going out on the wire.

Conclusion

We're happy to announce support for prepared statements in sql-server for Dolt. Prepared statements improve the compatibility of sql-server with existing applications and OLTP use cases. Given the base that we were building on, this initial support for prepared statements ended up being nicely near at hand and it covers a lot of use cases that our users are actually hitting.