feat(dprocedures): Add dolt_sql_commit procedure for atomic SQL execution + Dolt commit #10139
Conversation
Add a new stored procedure dolt_sql_commit that executes a SQL statement and creates a Dolt commit in a single atomic transaction. The procedure ensures SQL execution and Dolt commit are tightly coupled—if either step fails, the entire operation rolls back, preventing partial changes.
|
Hey @GodGavin! Thanks for the contribution! After discussing with the team, we believe that this approach is not correct. Using a stored procedure which takes a SQL string is not the approach we believe should be taken here. Transactionality is difficult, not to mention the SQL injection risks of running an arbitrary SQL string. The correct approach is to use the There are some limitations with this approach which we can address. Specifically, it is currently not possible to you to specify the message,author, or other details of the commit. If filling those gaps would meet your needs, it's a path to consider. Again, your thoughtful contribution to improving Dolt is greatly appreciated! |
Alright, I understand Dolt's considerations regarding security. However, please also note that the atomic execution of SQL and the committing of Dolt commits is indeed a relatively common application scenario. Are there any other methods besides setting the global autocommit variable? Moving forward, I will look into proposing a new solution to address this. |
4 participants
Description
This PR introduces a new stored procedure dolt_sql_commit that executes a SQL statement and creates a Dolt commit in a single atomic transaction. The procedure ensures SQL execution and Dolt commit are tightly coupled—if either step fails, the entire operation rolls back, preventing partial changes.
Key capabilities:
Accepts a commit message (-m) and SQL statement as arguments
Executes SQL in a transaction (starts one if none exists)
Stages all table changes from the SQL execution
Creates a Dolt commit atomically with the SQL transaction
Rolls back entirely on any failure (SQL execution, staging, commit creation)
Motivation
Currently, users must execute SQL and call dolt_commit separately, which risks inconsistent states (e.g., SQL succeeds but commit fails, leaving uncommitted changes). This procedure solves the atomicity gap, making it safe to pair data modifications with Dolt versioning in a single operation—critical for workflows like ETL, automated updates, or any scenario requiring guaranteed consistency between SQL changes and Dolt commits.
Implementation Details
Parameter Parsing: Extracts commit message (-m flag) and SQL statement from arguments, validates required inputs.
Transaction Management:
Reuses existing transactions or starts a new read-write transaction.
Uses isolated contexts (context.Background()) to prevent external cancellations from breaking atomicity.
Implements a rollbackIfNeeded helper to clean up transactions on failure.
SQL Execution: Runs the input SQL via the Go-MySQL-Server engine, iterates and closes the result iterator properly.
Dolt Commit Workflow:
Loads current database roots and stages all tables (ensuring SQL changes are included).
Creates commit properties (message, author, timestamp) aligned with dolt_commit behavior.
Uses DoltCommit to atomically finalize both the SQL transaction and Dolt commit.
Testing
CALL dolt_sql_commit('-m', 'modify employees10',"INSERT INTO employees (
id,last_name,first_name) VALUES (10, 'Gavin', 'Gao')");Verified the row exists and a Dolt commit is created
Confirmed no partial changes when SQL/commit fails