Dolt is a SQL database with Git-style versioning. With each new version of Dolt, we increase the number of supported SQL features, moving toward our goal of being a complete drop-in replacement for MySQL, while adding all of the versioning features you know and love from Git applied to a database, such as branching, diffs, merging, etc. In our latest release, we added support for stored procedures, and in this blog post I'll briefly go over how they're implemented in Dolt.

Our SQL Journey

Before diving right into stored procedures, I'd like to look over our journey with SQL, as it ties in with the implementation. When we first began working on Dolt, we did not have any kind of SQL support. All data interaction, from table creation to cell modification, was driven through dedicated commands on the terminal. It wasn't until my colleague Zach joined that we had our first support for SQL commands. However, developing support for SQL from the ground up proved to be a monumental task, and that is when we switched over to source{d}'s go-mysql-server library, which was a plug-in MySQL server for Go projects. Eventually the project was archived, and we forked it to continue development.

The SQL Engine

go-mysql-server is a standalone project. Although our development of the project (which I'll refer to as the SQL engine) is motivated by Dolt, we still treat it as a separate entity. In the case of stored procedures, all of the work is done in the engine, with Dolt being an integrator (see next section).

When it comes to adding a new feature to the engine, we usually think about the interface for the integrator before beginning development. Some features such as foreign keys require more work from the integrator, as they're heavily reliant on indexes when it comes to performance, and those are going to be specific to each integrator's implementation. Other features, such as the new stored procedures, are able to be handled completely in the engine. Integrators only need to store and retrieve them. Therefore, it was decided that we would first verify that the stored procedure is valid according to the same criteria used by MySQL. We then send the entire CREATE PROCEDURE string to the integrator, along with the parsed name and creation timestamps. Upon executing a stored procedure, we fetch all of them from the integrator, parse and reanalyze them, and then step through the procedure's logic.

Although the parse and analyze phases seem like they would result in slow performance, they execute quickly enough (for moderately-sized stored procedures) as to be nearly imperceptible when benchmarking the full process from input to output.

Integrating Into Dolt

As mentioned in the previous section, stored procedures are implemented in the SQL engine, therefore Dolt's integration is extremely simple. Dolt adds three functions that the engine requires for stored procedure support: one for retrieving all procedures, another for saving a given procedure, and another for deleting a procedure. That's it! It is this easy even for other projects who want to integrate stored procedures and other SQL functionality.

Internally, Dolt uses tables to store stored procedures as we already have the functionality in place to version them. They get added to a special table named dolt_procedures, and the three aforementioned functions are just wrappers for internal SELECT, INSERT and DELETE operations. Nifty!

Examples

Using stored procedures in Dolt is super simple, so let's create and use a few.

First, a very simple one that returns the input and adds 1 to it.

$ dolt init
Successfully initialized dolt data repository.

$ dolt sql -q "CREATE PROCEDURE example1(x INT) SELECT x + 1;"

$ dolt sql -q "CALL example1(4);"
+-------+
| x + 1 |
+-------+
| 5     |
+-------+

We named our procedure example1, and gave it a single parameter of type INT. For the procedure body, we simply SELECT on the x parameter and add 1 to it. We then use CALL to execute the procedure, and pass in 4. From there it's simple math: 4 + 1 = 5, which is our output!

Now for one that's a little more complex.

$ dolt sql -q "CREATE PROCEDURE example2(s VARCHAR(200), N DOUBLE, m DOUBLE)
BEGIN
    SET s = '';
    IF n = m THEN SET s = 'equals';
    ELSE
        IF n > m THEN SET s = 'greater';
        ELSE SET s = 'less';
        END IF;
        SET s = CONCAT('is ', s, ' than');
    END IF;
    SET s = CONCAT(n, ' ', s, ' ', m, '.');
    SELECT s;
END;"

$ dolt sql -q "CALL example2('', 1, 2);"
+-------------------+
| s                 |
+-------------------+
| 1 is less than 2. |
+-------------------+

$ dolt sql -q "CALL example2('', 5, 3);"
+----------------------+
| s                    |
+----------------------+
| 5 is greater than 3. |
+----------------------+

$ dolt sql -q "CALL example2('', 4, 4);"
+-------------+
| s           |
+-------------+
| 4 equals 4. |
+-------------+

Here we have a collection of statements under a BEGIN/END block, and it also makes use of control flow using IF/ELSE statements. Depending on the variables given in CALL, we construct a sentence that is then returned through SELECT.

NOTE: as of the writing of this blog, we are missing the DECLARE statement, so the s parameter is being used as a stand-in.

Conclusion

Stored procedures mark a major step towards our coverage of the MySQL feature set. The baseline is fully implemented, ranging from OUT parameters to the undocumented behavior of returning the last statement as the result set if no SELECT was called. Over the coming months, we will continue to expand on the number of supported SQL statements, making stored procedures ever more powerful and useful. You can stay up-to-date on our progress by following our releases, and you can directly interact with us by joining our Discord server. We hope you'll join us for the ride!