Unit Tests for your Dolt Database
Dolt is the only database with a built-in testing framework called Dolt CI. We've talked a lot about how AI agents work better with tests in the last couple of weeks. Today, we're announcing the launch of a new, simpler test framework called Dolt unit tests.
Why add unit tests? Dolt's CI is powerful, but a tad cumbersome for unit tests. You can run any query you want, including ones that modify your database (like cleaning data, perhaps). CI's queries are also split across two separate interfaces: the dolt_query_catalog table, and the yaml files you write. At least everyone loves yaml.
We realized that we wanted a simpler, easier-to-define, more restricted testing framework. Specifically, the main goals for unit tests:
- Only one table instead of the saved query + yaml 2-step process CI uses.
- Limit tests to read-only queries
- Keep the interface within SQL. Design is tricky otherwise. How would you export a table to YAML within a MySQL session?
Let's look at what's been added.
A look into dolt_tests
dolt_tests is a "configuration" system table. Like most other system tables (dolt_log, dolt_commits, etc.), it always exists, and you can't change its schema. Unlike those tables, however, it is fully writable. Nick does a good job breaking down configuration tables in more depth here if you're interested.
With that out of the way, what does dolt_tests look like? Make sure you're on the latest version of Dolt, 1.58.6 as I write this.
% dolt version
dolt version 1.58.6
% dolt sql -q "describe dolt_tests"
+----------------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------+------+-----+---------+-------+
| test_name | text | NO | PRI | NULL | |
| test_group | text | YES | | NULL | |
| test_query | text | NO | | NULL | |
| assertion_type | text | NO | | NULL | |
| assertion_comparator | text | NO | | NULL | |
| assertion_value | text | NO | | NULL | |
+----------------------+------+------+-----+---------+-------+Hopefully it's pretty intuitive, but we can go over each column in a bit more detail:
test_nameandtest_groupare what they sound like. The former defines each individual test and must be unique. The latter is optional, and allows you to run multiple tests at once (we'll get to that later).test_queryis the single test SQL query that will be run. As mentioned earlier, this can only be a read query that doesn't modify tables or data.assertion_typeis one of a predefined group of assertions you can make. Right now we support:expected_rows,expected_columns, andexpected_single_valuewhich looks at only one cell.assertion_comparatoris a comparator like "<", "!=", "==", and so on.assertion_valueis a scalar. Allowed types depends on the assertion type (integers only forexpected_rows, for instance).
Using dolt_test_run()
Tests usually aren't very interesting unless you can run them. We've added the table function dolt_test_run() to let you use your unit tests. How does it work?
dolt_test_run() takes in one or more arguments, each of which can be either the name of a test or the name of a group.
Alternatively, to run all tests, you can use * as a wildcard, or simply pass in no arguments.
Let's look at an example. Suppose you have a table that has the following data:
> select * from inventory;
+------------+------------------------+----------------------+----------------+------------+---------------+-------------+---------------------+
| product_id | product_name | category | stock_quantity | unit_price | reorder_level | supplier_id | last_updated |
+------------+------------------------+----------------------+----------------+------------+---------------+-------------+---------------------+
| 1 | dolt plush | household essentials | 10 | 10.00 | 5 | 10024 | 2025-08-26 12:29:20 |
| 2 | live laugh love poster | decor | 25 | 4.50 | 10 | 23543 | 2025-08-26 12:29:20 |
| 3 | dolthub neon sign | decor | 20 | 55.00 | 5 | 43564 | 2025-08-26 12:29:20 |
+------------+------------------------+----------------------+----------------+------------+---------------+-------------+---------------------+
3 rows in set (0.00 sec)We want to write tests to ensure the following:
stock_quantityis never negativeunit_priceis always a reasonable valueunit_pricenever greatly exceeds our historical average
So we'll insert into dolt_tests:
> INSERT INTO dolt_tests VALUES (
'no negative stock',
'data integrity',
'SELECT * FROM inventory WHERE stock_quantity < 0',
'expected_rows',
'==',
'0'
), (
'price not negative',
'data integrity',
'SELECT * FROM inventory WHERE unit_price <= 0',
'expected_rows',
'==',
'0'
), (
'validate price range',
'',
'SELECT AVG(unit_price) FROM inventory',
'expected_single_value',
'<=',
'85'
);
Query OK, 3 rows affected (0.01 sec)Now we can call dolt_test_run:
> SELECT * FROM dolt_test_run('*')
+----------------------+-----------------+--------------------------------------------------+--------+---------+
| test_name | test_group_name | query | status | message |
+----------------------+-----------------+--------------------------------------------------+--------+---------+
| no negative stock | data integrity | select * from inventory where stock_quantity < 0 | PASS | |
| price not negative | data integrity | select * from inventory where unit_price <= 0 | PASS | |
| validate price range | | SELECT AVG(unit_price) FROM inventory | PASS | |
+----------------------+-----------------+--------------------------------------------------+--------+---------+Everything passed, lovely! But what if all was not well? We can do some modifications to see what happens when tests fail.
> UPDATE inventory SET unit_price = 550 where product_id = 3
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
> SELECT * FROM dolt_test_run('validate price range')
+----------------------+-----------------+---------------------------------------+--------+----------------------------------------------------------------------------------+
| test_name | test_group_name | query | status | message |
+----------------------+-----------------+---------------------------------------+--------+----------------------------------------------------------------------------------+
| validate price range | | SELECT AVG(unit_price) FROM inventory | FAIL | Assertion failed: expected single value less than or equal to 85, got 188.166667 |
+----------------------+-----------------+---------------------------------------+--------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec) Whoops! Looks like the unit price was an order of magnitude off for our beloved DoltHub signs. We caught it early, however, and can fix the issue.
Dolt CI vs. Unit Tests
You might now be wondering, when should I use Dolt CI? When should I use dolt_tests? Here's a guide:
Unit tests are for:
- Simple, read-only assertions.
- Local testing (for now, at least).
- Especially designed for agents because of (1). No unexpected modifications.
CI is for:
- Running assertions and queries on DoltHub when you push or open a pull request.
- More complex write queries, as they are run on a clone of your database.
- Multi-step testing processes that require multiple individual SQL queries.
What's Next?
We're really excited about testing here at Dolt. Agents need tests. We plan to expand this feature a lot in the upcoming months. If you need something soon, or just want to chat, join our discord. We'd love to talk.