Dolt is best known as a SQL database that has branching and merging support. Did you know it also has support for data validation built in? We released dolt_tests last August, and we like to think of the feature as unit testing for your data.
Today we announce Commit Verification: the ability to block committing data that doesn’t pass those tests. With Commit Verification, you can define tests that must pass before a Dolt commit is made. This ensures that your data remains consistent and reliable and helps prevent errors from being introduced into your database. This doesn’t apply to SQL COMMITs, to be clear. Commit Verification is available in the latest release of Dolt 1.81.9.
These verifications are performed for all new Dolt commits, including when merging, cherry-picking, or rebasing. If any of the tests fail, the commit will be rejected and an error message will be displayed, indicating which tests failed and why.
Never let bad data into your database again! Let’s dig in!
TL;DR;#
If you already have dolt_tests defined in your database, you can enable commit verification with the following system variable:
dolt sql -q "SET @@PERSIST.dolt_commit_verification_groups = '*'"Start a new session and begin making commits!
Scenario#
I have a stock trading portfolio represented in a SQL table as the following:
CREATE TABLE transactions (
pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
trade_date timestamp NOT NULL,
asset varchar(8) NOT NULL,
quantity int NOT NULL,
cents_per_unit int NOT NULL,
fees_cents int unsigned NOT NULL,
cash_flow enum('deposited','withdrawn')
);
CALL DOLT_COMMIT('-A', '-m', 'Initial commit of transactions table');The table’s schema is straightforward. Let’s look at a few transactions to make it clear how this works.
The asset column is generally the ticker symbol of the stock being traded. We’ll have one exception: the $$$ asset, which represents the cash in the account. Every purchase of a stock will have a corresponding $$$ transaction which represents the cash being withdrawn from the account to pay for the stock. Every sale of a stock will have a corresponding $$$ transaction which represents the cash being deposited into the account from the sale of the stock. Each of these individual changes is represented as a new row in the transactions table.
Key Point: In order for a transaction to be valid, there must be two rows: an adjustment to $$$ and an adjustment to the stock being bought or sold.
There are two special transaction types: deposited and withdrawn. These transactions will only have a $$$ asset and will not have a corresponding stock transaction. The cash_flow column is used to indicate the transaction type.
Finally, we use the penny as the unit of currency in the cents_per_unit column to avoid issues with floating point precision. So if you want to add $100.00 to the account, you would insert a transaction with quantity of 10,000 and cents_per_unit of 1.
Example#
Let’s deposit $5,000.00 into the account on January 1st, 2026:
INSERT INTO transactions (trade_date, asset, quantity, cents_per_unit, fees_cents, cash_flow)
VALUES ('2026-01-01', '$$$', 500000, 1, 0, 'deposited');
CALL DOLT_COMMIT('-a', '-m', 'Deposit $5000');Now let’s buy 10 shares of ACME Software Co., at $150.00 per share on January 2nd, 2026, which will cost $1,500.00 plus $10.00 in fees:
INSERT INTO transactions (trade_date, asset, quantity, cents_per_unit, fees_cents)
VALUES ('2026-01-02', 'ACME', 10, 15000, 1000);
INSERT INTO transactions (trade_date, asset, quantity, cents_per_unit, fees_cents)
VALUES ('2026-01-02', '$$$', -160000, 1, 0);
CALL DOLT_COMMIT('-a', '-m', 'Buy 10 shares of ACME');If you want to know how many pennies you have to invest, sum up the quantity of the $$$ asset:
SELECT SUM(quantity) AS cash_pennies FROM transactions WHERE asset = '$$$';
+--------------+
| cash_pennies |
+--------------+
| 340000 |
+--------------+
1 row in set (0.00 sec)When I want to sell 5 of my 10 shares of ACME at $200.00 per share on January 3rd (wow, what a great day for ACME!), I would insert the following rows in the transactions table:
INSERT INTO transactions (trade_date, asset, quantity, cents_per_unit, fees_cents)
VALUES ('2026-01-03', 'ACME', -5, 20000, 1000);
INSERT INTO transactions (trade_date, asset, quantity, cents_per_unit, fees_cents)
VALUES ('2026-01-03', '$$$', 99000, 1, 0);
CALL DOLT_COMMIT('-a', '-m', 'Sell 5 shares of ACME');That was a pretty good day. Now I have $4,390.00 in cash and 5 shares of ACME left in the account.
db8/main> SELECT asset, SUM(quantity) AS total_quantity FROM transactions GROUP BY asset;
+-------+----------------+
| asset | total_quantity |
+-------+----------------+
| $$$ | 439000 |
| ACME | 5 |
+-------+----------------+
2 rows in set (0.00 sec)Getting the total cash value of the account is a little more work because we need to get the real-time price of the stock to calculate the value of the 5 shares of ACME. We’ll leave that as an exercise for the reader. The point of all of this is that while the schema of the transactions table is simple, there are some important rules that must be followed in order for the data to be valid. For example, if I try to insert a transaction for 10 shares of ACME without a corresponding $$$ transaction, where does the money come from to buy those shares? Or if I try to insert a transaction for 10 shares of ACME and a corresponding $$$ transaction, but the $$$ transaction only withdraws $1,000.00 from the account, then where does the extra $500.00 come from to pay for the shares and fees?
Refresher on Dolt Tests#
Before we dive into Commit Verification, let’s do a quick refresher on how to write tests in Dolt. Dolt tests are defined in a special table called dolt_tests. Each row in the dolt_tests table represents a single test case and has the following schema:
show create table dolt_tests;
+------------+------------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------+
| dolt_tests | CREATE TABLE `dolt_tests` ( |
| | `test_name` text NOT NULL, |
| | `test_group` text, |
| | `test_query` text NOT NULL, |
| | `assertion_type` text NOT NULL, |
| | `assertion_comparator` text NOT NULL, |
| | `assertion_value` text, |
| | PRIMARY KEY (`test_name`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+------------+------------------------------------------------------------------+Each row in the dolt_tests table should be considered like a unit test in a traditional software codebase.
Using the dolt_test_run table function, you can execute the test cases defined in the dolt_tests table and see which tests pass and which tests fail.
You can read more about dolt_tests from our original announcement blog post.
Tests for the Transactions Table#
Now that we have a good understanding of the scenario and how to write tests in Dolt, let’s write some tests for the transactions table to ensure that our data remains consistent and valid.
First, we’ll note that no Dolt commit should ever remove or update a row in the transactions table. The data model we have is strictly additive, and updating a row would be an error to be avoided. So our first test will be to ensure that every commit only adds rows to the transactions table and does not remove or update any existing rows.
The query we want to run for this test is the following:
SELECT COUNT(*)
FROM dolt_diff('HEAD~1', 'HEAD', 'transactions')
WHERE diff_type <> 'added'Using the dolt_diff table function, we expect to get zero rows from this query for every commit, which would indicate that no rows were removed or updated in the transactions table.
Inserting that into the dolt_tests table requires that we escape some quotes, but the final insert statement looks like this:
INSERT INTO dolt_tests (
test_name, test_group, test_query, assertion_type, assertion_comparator, assertion_value)
VALUES (
'transactions_strictly_additive',
'transactions',
'
SELECT COUNT(*)
FROM dolt_diff(''HEAD~1'', ''HEAD'', ''transactions'')
WHERE diff_type <> ''added''
',
'expected_single_value',
'==',
'0'
);To demonstrate how we run the test, we run the dolt_test_run() table function:
SELECT test_name,status,message from dolt_test_run();
+---------------------------------------------------+--------+---------+
| test_name | status | message |
+---------------------------------------------------+--------+---------+
| transactions_strictly_additive | PASS | |
+---------------------------------------------------+--------+---------+
1 rows in set (0.00 sec)The next test we’ll add is to ensure that for any deposit or withdrawal, there is only a single valid $$$ cash flow transaction and no corresponding stock transaction. And if we deposit a negative value, or withdraw a positive value, that’s invalid as well. Here is the dolt_test insert statement for that test:
INSERT INTO dolt_tests (
test_name,
test_group,
test_query,
assertion_type,
assertion_comparator,
assertion_value
) VALUES (
'transactions_cash_flow_is_single_valid_row_commit',
'transactions',
'
SELECT
CASE
-- No cash_flow rows added in this commit => OK
WHEN SUM(CASE WHEN to_cash_flow IS NOT NULL THEN 1 ELSE 0 END) = 0 THEN 1
-- Otherwise: exactly one added row, and it must be a valid $$$ cash-flow row
WHEN COUNT(*) = 1
AND MAX(to_cash_flow IN (''deposited'',''withdrawn'')) = 1
AND MAX(to_asset = ''$$$'') = 1
AND MAX(to_cents_per_unit = 1) = 1
AND MAX(to_fees_cents = 0) = 1
AND MAX(
(to_cash_flow = ''deposited'' AND to_quantity > 0)
OR (to_cash_flow = ''withdrawn'' AND to_quantity < 0)
) = 1
THEN 1
ELSE 0
END
FROM dolt_diff(''HEAD~1'', ''HEAD'', ''transactions'')
',
'expected_single_value',
'==',
'1'
);Just to highlight how this can find problems, let’s insert a negative deposit, which should be invalid:
INSERT INTO transactions ( trade_date, asset, quantity, cents_per_unit, fees_cents, cash_flow )
VALUES ( '2026-01-04, '$$$', -10000, 1, 0, 'deposited');Let’s run the tests again:
select test_name,status,message from dolt_test_run();
+---------------------------------------------------+--------+-----------------------------------------------------------+
| test_name | status | message |
+---------------------------------------------------+--------+-----------------------------------------------------------+
| transactions_cash_flow_is_single_valid_row_commit | FAIL | Assertion failed: expected_single_value equal to 1, got 0 |
| transactions_strictly_additive | PASS | |
+---------------------------------------------------+--------+-----------------------------------------------------------+
2 rows in set (0.00 sec)This tells us that our insert of a negative deposit failed the transactions_cash_flow_is_single_valid_row_commit test, which is exactly what we want. We can fix the data and then re-run the tests to confirm that everything is valid again.
We’ll add one more test, which is to ensure that for every stock transaction, there are two rows modified: one with the $$$ asset and one with a stock asset. Most importantly, the sum change to the $$$ asset must be the negative of the sum change to the stock asset multiplied by the cents_per_unit plus any fees. This ensures that every stock purchase or sale has a corresponding cash flow transaction which is consistent with the price and quantity of the stock being bought or sold.
INSERT INTO dolt_tests (
test_name,
test_group,
test_query,
assertion_type,
assertion_comparator,
assertion_value
) VALUES (
'transactions_regular_trade_two_rows_cash_balances_both_directions',
'transactions',
'
WITH d AS (
SELECT *
FROM dolt_diff(''HEAD~1'', ''HEAD'', ''transactions'')
WHERE diff_type = ''added''
)
SELECT
CASE
WHEN
COUNT(*) = 2
AND SUM(CASE WHEN to_cash_flow IS NOT NULL THEN 1 ELSE 0 END) = 0
AND SUM(CASE WHEN to_asset = ''$$$'' THEN 1 ELSE 0 END) = 1
AND SUM(CASE WHEN to_asset <> ''$$$'' THEN 1 ELSE 0 END) = 1
AND SUM(CASE WHEN to_asset = ''$$$'' AND to_cents_per_unit = 1 AND to_fees_cents = 0 THEN 1 ELSE 0 END) = 1
AND SUM(CASE WHEN to_asset <> ''$$$'' AND to_fees_cents >= 0 THEN 1 ELSE 0 END) = 1
AND (
-- Accounting identity: cash + (asset_cost + fees) == 0, using signed quantities.
SUM(CASE WHEN to_asset = ''$$$'' THEN to_quantity ELSE 0 END)
+ SUM(CASE WHEN to_asset <> ''$$$'' THEN to_quantity * to_cents_per_unit ELSE 0 END)
+ SUM(CASE WHEN to_asset <> ''$$$'' THEN to_fees_cents ELSE 0 END)
= 0
)
THEN 1
ELSE 0
END
FROM d
',
'expected_single_value',
'==',
'1'
);To demonstrate how this can catch errors, let’s create a transaction where we don’t draw down the correct amount of cash for a stock purchase:
INSERT INTO transactions ( trade_date, asset, quantity, cents_per_unit, fees_cents)
VALUES ( '2026-01-05', 'FOO', 10, 10, 0 );
INSERT INTO transactions ( trade_date, asset,quantity, cents_per_unit, fees_cents)
VALUES ( '2026-01-05', '$$$', -99, 1, 0);
CALL DOLT_COMMIT('-a', '-m', 'Invalid purchase of 10 shares of FOO for only $0.99');Now when we run the tests, we should see that the transactions_regular_trade_two_rows_cash_balances_both_directions test fails:
select test_name,status,message from dolt_test_run();
+-------------------------------------------------------------------+--------+-----------------------------------------------------------+
| test_name | status | message |
+-------------------------------------------------------------------+--------+-----------------------------------------------------------+
| transactions_cash_flow_is_single_valid_row_commit | PASS | |
| transactions_regular_trade_two_rows_cash_balances_both_directions | FAIL | Assertion failed: expected_single_value equal to 1, got 0 |
| transactions_strictly_additive | PASS | |
+-------------------------------------------------------------------+--------+-----------------------------------------------------------+
3 rows in set (0.01 sec)We’ll remove that with a dolt reset so that we have a clean state. There are many other tests we could write. No asset can ever have a negative quantity. No transaction can have a negative fee. The trade_date of each row of a regular trade must match. And so on and so forth. What makes sense in your application is up to you.
Now with Commit Verification!#
This has all been a lead-up to the actual new feature we are announcing today, which is Commit Verification. Note that all of our examples above forced us to run the dolt_run_test() function manually to see if our tests passed or failed. With Commit Verification, you can configure your Dolt database to automatically run the tests in the dolt_tests table on every commit, and halt committing when your tests fail. This means that you can ensure that your data remains consistent and valid without having to remember to run the tests manually.
In order to enable Commit Verification, you need to set the dolt_commit_verification_groups system variable to the name of the test group you want to run for verification. You can also use * as a wildcard to run all tests in the dolt_tests table for every commit. You want to use a persistent system variable so that the setting remains in place for all sessions:
dolt sql -q "SET @@PERSIST.dolt_commit_verification_groups = '*'"Let’s connect with a new session and try to make that commit we made above with the invalid purchase of 10 shares of FOO for only $0.99:
INSERT INTO transactions ( trade_date, asset, quantity, cents_per_unit, fees_cents)
VALUES ( '2026-01-05', 'FOO', 10, 10, 0 );
INSERT INTO transactions ( trade_date, asset,quantity, cents_per_unit, fees_cents)
VALUES ( '2026-01-05', '$$$', -99, 1, 0);
CALL DOLT_COMMIT('-a', '-m', 'Invalid purchase of 10 shares of FOO for only $0.99');That… succeeded. Why? Because our tests are specifically written to check the diff between the current commit and the previous commit. In this case, we run the tests before the commit is made, so their use of the dolt_diff function needs to be updated slightly to compare the staged changes to the current HEAD commit. Replace occurrences of dolt_diff('HEAD~1', 'HEAD', 'transactions') with dolt_diff('HEAD', 'WORKING', 'transactions') in the test queries. This is admittedly suboptimal, but this really stems from the fact that I’m using HEAD in the test. If you have straightforward data tests, this isn’t an issue because tests are run against your working set always.
The easiest way to address this is to drop the existing tests, update them with the new dolt_diff usage, and then re-insert them. Here are the updated test insert statements:
DELETE FROM dolt_tests;Click to see updated test inserts
INSERT INTO dolt_tests (
test_name, test_group, test_query, assertion_type, assertion_comparator, assertion_value)
VALUES (
'transactions_strictly_additive',
'transactions',
'
SELECT COUNT(*)
FROM dolt_diff(''HEAD'', ''WORKING'', ''transactions'')
WHERE diff_type <> ''added''
',
'expected_single_value',
'==',
'0'
);
INSERT INTO dolt_tests (
test_name,
test_group,
test_query,
assertion_type,
assertion_comparator,
assertion_value
) VALUES (
'transactions_cash_flow_is_single_valid_row_commit',
'transactions',
'
SELECT
CASE
-- No cash_flow rows added in this commit => OK
WHEN SUM(CASE WHEN to_cash_flow IS NOT NULL THEN 1 ELSE 0 END) = 0 THEN 1
-- Otherwise: exactly one added row, and it must be a valid $$$ cash-flow row
WHEN COUNT(*) = 1
AND MAX(to_cash_flow IN (''deposited'',''withdrawn'')) = 1
AND MAX(to_asset = ''$$$'') = 1
AND MAX(to_cents_per_unit = 1) = 1
AND MAX(to_fees_cents = 0) = 1
AND MAX(
(to_cash_flow = ''deposited'' AND to_quantity > 0)
OR (to_cash_flow = ''withdrawn'' AND to_quantity < 0)
) = 1
THEN 1
ELSE 0
END
FROM dolt_diff(''HEAD'', ''WORKING'', ''transactions'')
',
'expected_single_value',
'==',
'1'
);
INSERT INTO dolt_tests (
test_name,
test_group,
test_query,
assertion_type,
assertion_comparator,
assertion_value
) VALUES (
'transactions_regular_trade_two_rows_cash_balances_both_directions',
'transactions',
'
WITH d AS (
SELECT *
FROM dolt_diff(''HEAD'', ''WORKING'', ''transactions'')
WHERE diff_type = ''added''
)
SELECT
CASE
WHEN
COUNT(*) = 2
AND SUM(CASE WHEN to_cash_flow IS NOT NULL THEN 1 ELSE 0 END) = 0
AND SUM(CASE WHEN to_asset = ''$$$'' THEN 1 ELSE 0 END) = 1
AND SUM(CASE WHEN to_asset <> ''$$$'' THEN 1 ELSE 0 END) = 1
AND SUM(CASE WHEN to_asset = ''$$$'' AND to_cents_per_unit = 1 AND to_fees_cents = 0 THEN 1 ELSE 0 END) = 1
AND SUM(CASE WHEN to_asset <> ''$$$'' AND to_fees_cents >= 0 THEN 1 ELSE 0 END) = 1
AND (
-- Accounting identity: cash + (asset_cost + fees) == 0, using signed quantities.
SUM(CASE WHEN to_asset = ''$$$'' THEN to_quantity ELSE 0 END)
+ SUM(CASE WHEN to_asset <> ''$$$'' THEN to_quantity * to_cents_per_unit ELSE 0 END)
+ SUM(CASE WHEN to_asset <> ''$$$'' THEN to_fees_cents ELSE 0 END)
= 0
)
THEN 1
ELSE 0
END
FROM d
',
'expected_single_value',
'==',
'1'
);Now, let’s try that same invalid commit again:
INSERT INTO transactions ( trade_date, asset, quantity, cents_per_unit, fees_cents)
VALUES ( '2026-01-05', 'FOO', 10, 10, 0 );
INSERT INTO transactions ( trade_date, asset,quantity, cents_per_unit, fees_cents)
VALUES ( '2026-01-05', '$$$', -99, 1, 0);
CALL DOLT_COMMIT('-a', '-m', 'Invalid purchase of 10 shares of FOO for only $0.99');
commit verification failed: transactions_regular_trade_two_rows_cash_balances_both_directions (Assertion failed: expected_single_value equal to 1, got 0)Bad data crisis averted! In the event that you need to bypass Commit Verification for a particular commit, you can use the --skip-verification flag:
CALL DOLT_COMMIT('-a', '--skip-verification', '-m', 'Committing data even though my tests reject it!');That flag exists on the dolt_commit, dolt_merge, dolt_cherry_pick, and dolt_rebase commands. It has also been added to the equivalent CLI commands.
What Will You Build?#
There you have it. A new way to make sure your data is always valid and consistent. If you are using agents to modify your data, consider constraining them with this feature. Or perhaps you have a data pipeline where you want to make sure that bad data doesn’t get into your database. Commit Verification can help!
Come to our Discord and let us know what you build with this new feature, or if you have any questions or feedback!