Procedures

Table of Contents
​Dolt SQL Procedures​
​dolt_add()​
​dolt_backup()​
​dolt_branch()​
​dolt_checkout()​
​dolt_clean()​
​dolt_commit()​
​dolt_fetch()​
​dolt_merge()​
​dolt_pull()​
​dolt_push()​
​dolt_reset()​
​dolt_revert()​
Dolt SQL Procedures
Dolt provides native stored procedures to allow access to dolt CLI commands from within a SQL session. Each procedure is named after the dolt command line command it matches, and takes arguments in an identical form.
For example, dolt checkout -b feature-branch is equivalent to executing the following SQL statement:
1
CALL DOLT_CHECKOUT('-b', 'feature-branch');
Copied!
SQL procedures are provided for all imperative CLI commands. For commands that inspect the state of the database and print some information, (dolt diff, dolt log, etc.) system tables are provided instead.
One important note: all procedures modify state only for the current session, not for all clients. So for example, whereas running dolt checkout feature-branch will change the working HEAD for anyone who subsequently runs a command from the same dolt database directory, running CALL DOLT_CHECKOUT('feature-branch') only changes the working HEAD for that database session. The right way to think of this is that the command line environment is effectively a session, one that happens to be shared with whomever runs CLI commands from that directory.
DOLT_ADD()
Adds working changes to staged for this session. Works exactly like dolt add on the CLI, and takes the same arguments.
After adding tables to the staged area, they can be committed with DOLT_COMMIT().
1
CALL DOLT_ADD('-A');
2
CALL DOLT_ADD('.');
3
CALL DOLT_ADD('table1', 'table2');
Copied!
Options
table: Table(s) to add to the list tables staged to be committed. The abbreviation '.' can be used to add all tables.
-A: Stages all tables with changes.
Example
1
-- Set the current database for the session
2
USE mydb;
3
​
4
-- Make modifications
5
UPDATE table
6
SET column = "new value"
7
WHERE pk = "key";
8
​
9
-- Stage all changes.
10
CALL DOLT_ADD('-a');
11
​
12
-- Commit the changes.
13
CALL DOLT_COMMIT('-m', 'committing all changes');
Copied!
DOLT_BACKUP()
Sync with a configured backup. Other backup commands not supported via SQL yet.
1
CALL DOLT_BACKUP('sync', 'name');
Copied!
Example
1
-- Set the current database for the session
2
USE mydb;
3
​
4
-- Upload the current database contents to the named backup
5
CALL dolt_backup('sync', 'my-backup')
Copied!
DOLT_BRANCH()
Create, delete, and rename branches.
To list branches, use the DOLT_BRANCHES system table, instead of the DOLT_BRANCH() stored procedure.
To look up the current branch, use the @@<dbname>_head_ref system variable, or the active_branch() SQL function, as shown in the examples section below.
WARNING: In a multi-session server environment, Dolt will prevent you from deleting or renaming a branch in use in another session. You can force renaming or deletion by passing the --force option, but be aware that active clients on other sessions will no longer be able to execute statements after their active branch is removed and will need to end their session and reconnect.
1
-- Create a new branch from the current HEAD
2
CALL DOLT_BRANCH('myNewBranch');
3
​
4
-- Create a new branch by copying an existing branch
5
-- Will fail if feature1 branch already exists
6
CALL DOLT_BRANCH('-c', 'main', 'feature1');
7
​
8
-- Create or replace a branch by copying an existing branch
9
-- '-f' forces the copy, even if feature1 branch already exists 
10
CALL DOLT_BRANCH('-c', '-f', 'main', 'feature1');
11
​
12
-- Delete a branch
13
CALL DOLT_BRANCH('-d', 'branchToDelete');
14
​
15
-- Rename a branch
16
CALL DOLT_BRANCH('-m', 'currentBranchName', 'newBranchName')
Copied!
Options
-c, --copy: Create a copy of a branch. Must be followed by the name of the source branch to copy and the name of the new branch to create. Without the --force option, the copy will fail if the new branch already exists.
-m, --move: Move/rename a branch. Must be followed by the current name of an existing branch and a new name for that branch. Without the --force option, renaming a branch in use on another server session will fail. Be aware that forcibly renaming or deleting a branch in use in another session will require that session to disconnect and reconnect before it can execute statements again.
-d, --delete: Delete a branch. Must be followed by the name of an existing branch to delete. Without the --force option, deleting a branch in use on another server session will fail. Be aware that forcibly renaming or deleting a branch in use in another session will require that session to disconnect and reconnect before it can execute statements again.
-f, --force: When used with the --copy option, allows for recreating a branch from another branch, even if the branch already exists. When used with the --move or --delete options, force will allow you to rename or delete branches in use in other active server sessions, but be aware that this will require those other sessions to disconnect and reconnect before they can execute statements again.
-D: Shortcut for --delete --force.
Examples
1
-- List the available branches
2
SELECT * FROM DOLT_BRANCHES;
3
+--------+----------------------------------+
4
| name   | hash                             |
5
+--------+----------------------------------+
6
| backup | nsqtc86d54kafkuf0a24s4hqircvg68g |
7
| main   | dvtsgnlg7n9squriob3nq6kve6gnhkf2 |
8
+--------+----------------------------------+
9
    
10
-- Create a new branch for development work from the tip of head and switch to it
11
CALL DOLT_BRANCH('myNewFeature');
12
CALL DOLT_CHECKOUT('myNewFeature');
13
​
14
-- View your current branch 
15
select active_branch();
16
+----------------+
17
| active_branch  |
18
+----------------+
19
| myNewFeature   |
20
+----------------+
21
    
22
-- Create a new branch from an existing branch 
23
CALL DOLT_BRANCH('-c', 'backup', 'bugfix-3482');
24
    
25
-- Rename a branch
26
CALL DOLT_BRANCH('-m', 'bugfix-3482', 'critical-bugfix-3482');
27
​
28
-- Delete a branch
29
CALL DOLT_BRANCH('-d', 'old-unused-branch');
Copied!
DOLT_CHECKOUT()
Switches this session to a different branch.
With table names as arguments, restores those tables to their contents in the current HEAD.
When switching to a different branch, your session state must be clean. COMMIT or ROLLBACK any changes before switching to a different branch.
Note, unlike the Git command-line, if you have a modified working set, those changes remain on the branch you modified after a DOLT_CHECKOUT(). The working set does not transfer to the new checked out branch. We modified this behavior because Dolt assumes multiple users of a branch in SQL context. Having one user change the state of other users working set was deemed undesirable behavior.
1
CALL DOLT_CHECKOUT('-b', 'my-new-branch');
2
CALL DOLT_CHECKOUT('my-existing-branch');
3
CALL DOLT_CHECKOUT('my-table');
Copied!
Options
-b: Create a new branch with the given name.
Example
1
-- Set the current database for the session
2
USE mydb;
3
​
4
-- Create and checkout to a new branch.
5
CALL DOLT_CHECKOUT('-b', 'feature-branch');
6
​
7
-- Make modifications
8
UPDATE table
9
SET column = "new value"
10
WHERE pk = "key";
11
​
12
-- Stage and commit all  changes.
13
CALL DOLT_COMMIT('-a', '-m', 'committing all changes');
14
​
15
-- Go back to main
16
CALL DOLT_CHECKOUT('main');
Copied!
DOLT_CLEAN()
Deletes untracked tables in the working set.
Deletes only specified untracked tables if table names passed as arguments.
With --dry-run flag, tests whether removing untracked tables will return with zero status.
1
CALL DOLT_CLEAN();
2
CALL DOLT_CLEAN('untracked-table');
3
CALL DOLT_CLEAN('--dry-run');
Copied!
Options
--dry-run: Test removing untracked tables from working set.
Example
1
-- Create three new tables
2
create table tracked (x int primary key);
3
create table committed (x int primary key);
4
create table untracked (x int primary key);
5
​
6
-- Commit the first table
7
call dolt_add('committed');
8
call dolt_commit('-m', 'commit a table');
9
+----------------------------------+
10
| hash                             |
11
+----------------------------------+
12
| n7gle7jv6aqf72stbdicees6iduhuoo9 |
13
+----------------------------------+
14
​
15
-- Track the second table
16
call dolt_add('tracked');
17
​
18
-- Observe database status
19
select * from dolt_status;
20
+------------+--------+-----------+
21
| table_name | staged | status    |
22
+------------+--------+-----------+
23
| tracked    | true   | new table |
24
| untracked  | false  | new table |
25
+------------+--------+-----------+
26
​
27
-- Clear untracked tables
28
call dolt_clean('untracked');
29
​
30
-- Observe final status
31
select * from dolt_status;
32
+------------+--------+-----------+
33
| table_name | staged | status    |
34
+------------+--------+-----------+
35
| tracked    | true   | new table |
36
+------------+--------+-----------+
37
​
38
-- Committed and tracked tables are preserved
39
show tables;
40
+----------------+
41
| Tables_in_tmp3 |
42
+----------------+
43
| committed      |
44
| tracked        |
45
+----------------+
Copied!
DOLT_COMMIT()
Commits staged tables to HEAD. Works exactly like dolt commit with each value directly following the flag.
DOLT_COMMIT() also commits the current transaction.
1
CALL DOLT_COMMIT('-a', '-m', 'This is a commit');
2
CALL DOLT_COMMIT('-m', 'This is a commit');
3
CALL DOLT_COMMIT('-m', 'This is a commit', '--author', 'John Doe <[email protected]>');
Copied!
Options
-m, --message: Use the given <msg> as the commit message. Required
-a: Stages all tables with changes before committing
--allow-empty: Allow recording a commit that has the exact same data as its sole parent. This is usually a mistake, so it is disabled by default. This option bypasses that safety.
--date: Specify the date used in the commit. If not specified the current system time is used.
--author: Specify an explicit author using the standard "A U Thor [email protected]" format.
Examples
1
-- Set the current database for the session
2
USE mydb;
3
​
4
-- Make modifications
5
UPDATE table
6
SET column = "new value"
7
WHERE pk = "key";
8
​
9
-- Stage all changes and commit.
10
CALL DOLT_COMMIT('-a', '-m', 'This is a commit', '--author', 'John Doe <[email protected]>');
Copied!
DOLT_FETCH()
Fetch refs, along with the objects necessary to complete their histories and update remote-tracking branches. Works exactly like dolt fetch on the CLI, and takes the same arguments.
1
CALL DOLT_FETCH('origin', 'main');
2
CALL DOLT_FETCH('origin', 'feature-branch');
3
CALL DOLT_FETCH('origin', 'refs/heads/main:refs/remotes/origin/main');
Copied!
Options
--force: Update refs to remote branches with the current state of the remote, overwriting any conflicting history
Example
1
-- Get remote main
2
CALL DOLT_FETCH('origin', 'main');
3
​
4
-- Inspect the hash of the fetched remote branch
5
SELECT HASHOF('origin/main');
6
​
7
-- Merge remote main with current branch
8
CALL DOLT_MERGE('origin/main');
Copied!
DOLT_MERGE()
Incorporates changes from the named commits (since the time their histories diverged from the current branch) into the current branch. Works exactly like dolt merge on the CLI, and takes the same arguments.
Any resulting merge conflicts must be resolved before the transaction can be committed or a new Dolt commit created.
1
CALL DOLT_MERGE('feature-branch'); -- Optional --squash parameter
2
CALL DOLT_MERGE('feature-branch', '-no-ff', '-m', 'This is a msg for a non fast forward merge');
3
CALL DOLT_MERGE('--abort');
Copied!
Options
--no-ff: Create a merge commit even when the merge resolves as a fast-forward.
--squash: Merges changes to the working set without updating the commit history
-m <msg>, --message=<msg>: Use the given as the commit message. This is only useful for --non-ff commits.
--abort: Abort the current conflict resolution process, and try to reconstruct the pre-merge state.
When merging a branch, your session state must be clean. COMMIT orROLLBACK any changes, then DOLT_COMMIT() to create a new dolt commit on the target branch.
If the merge causes conflicts or constraint violations, you must resolve them using the dolt_conflicts system tables before the transaction can be committed. See Dolt system tables for details.
Example
1
-- Set the current database for the session
2
USE mydb;
3
​
4
-- Create and checkout to a new branch.
5
CALL DOLT_CHECKOUT('-b', 'feature-branch');
6
​
7
-- Make modifications
8
UPDATE table
9
SET column = "new value"
10
WHERE pk = "key";
11
​
12
-- Stage and commit all  changes.
13
CALL DOLT_COMMIT('-a', '-m', 'committing all changes');
14
​
15
-- Go back to main
16
CALL DOLT_MERGE('feature-branch');
Copied!
DOLT_RESET()
Default mode resets staged tables to their HEAD state. Can also be used to reset a database to a specific commit. Works exactly like dolt reset on the CLI, and takes the same arguments.
Like other data modifications, after a reset you must COMMIT the transaction for any changes to affected tables to be visible to other clients.
1
CALL DOLT_RESET('--hard', 'featureBranch');
2
CALL DOLT_RESET('--hard', 'commitHash123abc');
3
CALL DOLT_RESET('myTable'); -- soft reset
Copied!
Options
--hard: Resets the working tables and staged tables. Any changes to tracked tables in the working tree since are discarded.
--soft: Does not touch the working tables, but removes all tables staged to be committed. This is the default behavior.
Example
1
-- Set the current database for the session
2
USE mydb;
3
​
4
-- Make modifications
5
UPDATE table
6
SET column = "new value"
7
WHERE pk = "key";
8
​
9
-- Reset the changes permanently.
10
CALL DOLT_RESET('--hard');
11
​
12
-- Makes some more changes.
13
UPDATE table
14
SET column = "new value"
15
WHERE pk = "key";
16
​
17
-- Stage the table.
18
CALL DOLT_ADD('table')
19
​
20
-- Unstage the table.
21
CALL DOLT_RESET('table')
Copied!
DOLT_REVERT()
Reverts the changes introduced in a commit, or set of commits. Creates a new commit from the current HEAD that reverses the changes in all the specified commits. If multiple commits are given, they are applied in the order given.
1
CALL DOLT_REVERT('gtfv1qhr5le61njimcbses9oom0de41e');
2
CALL DOLT_REVERT('HEAD~2');
3
CALL DOLT_REVERT('HEAD', '[email protected]');
Copied!
Options
--author=<author>: Specify an explicit author using the standard A U Thor <[email protected]> format.
Example
1
-- Create a table and add data in multiple commits
2
CREATE TABLE t1(pk INT PRIMARY KEY, c VARCHAR(255));
3
CALL dolt_commit("-am", "Creating table t1");
4
INSERT INTO t1 VALUES(1, "a"), (2, "b"), (3, "c");
5
CALL dolt_commit("-am", "Adding some data");
6
insert into t1 VALUES(10, "aa"), (20, "bb"), (30, "cc");
7
CALL dolt_commit("-am", "Adding some more data");
8
​
9
-- Examine the changes made in the commit immediately before the current HEAD commit
10
SELECT to_pk, to_c, to_commit, diff_type FROM dolt_diff_t1 WHERE to_commit=hashof("HEAD~1");
11
+-------+------+----------------------------------+-----------+
12
| to_pk | to_c | to_commit                        | diff_type |
13
+-------+------+----------------------------------+-----------+
14
| 1     | a    | fc4fks6jutcnee9ka6458nmuot7rl1r2 | added     |
15
| 2     | b    | fc4fks6jutcnee9ka6458nmuot7rl1r2 | added     |
16
| 3     | c    | fc4fks6jutcnee9ka6458nmuot7rl1r2 | added     |
17
+-------+------+----------------------------------+-----------+
18
​
19
-- Revert the commit immediately before the current HEAD commit  
20
CALL dolt_revert("HEAD~1");
21
    
22
-- Check out the new commit created by dolt_revert
23
SELECT commit_hash, message FROM dolt_log limit 1;
24
+----------------------------------+---------------------------+
25
| commit_hash                      | message                   |
26
+----------------------------------+---------------------------+
27
| vbevrdghj3in3napcgdsch0mq7f8en4v | Revert "Adding some data" |
28
+----------------------------------+---------------------------+
29
​
30
-- View the exact changes made by the revert commit 
31
SELECT from_pk, from_c, to_commit, diff_type FROM dolt_diff_t1 WHERE to_commit=hashof("HEAD");
32
+---------+--------+----------------------------------+-----------+
33
| from_pk | from_c | to_commit                        | diff_type |
34
+---------+--------+----------------------------------+-----------+
35
| 1       | a      | vbevrdghj3in3napcgdsch0mq7f8en4v | removed   |
36
| 2       | b      | vbevrdghj3in3napcgdsch0mq7f8en4v | removed   |
37
| 3       | c      | vbevrdghj3in3napcgdsch0mq7f8en4v | removed   |
38
+---------+--------+----------------------------------+-----------+
Copied!
DOLT_PUSH()
Updates remote refs using local refs, while sending objects necessary to complete the given refs. Works exactly like dolt push on the CLI, and takes the same arguments.
1
CALL DOLT_PUSH('origin', 'main');
2
CALL DOLT_PUSH('--force', 'origin', 'main');
Copied!
Options
--force: Update the remote with local history, overwriting any conflicting history in the remote.
Example
1
-- Checkout new branch
2
CALL DOLT_CHECKOUT('-b', 'feature-branch');
3
​
4
-- Add a table
5
CREATE TABLE test (a int primary key);
6
​
7
-- Create commit
8
CALL DOLT_COMMIT('-a', '-m', 'create table test');
9
​
10
-- Push to remote
11
CALL DOLT_PUSH('origin', 'feature-branch');
Copied!
DOLT_PULL()
Fetch from and integrate with another database or a local branch. In its default mode, dolt pull is shorthand for dolt fetch followed by dolt merge <remote>/<branch>. Works exactly like dolt pull on the CLI, and takes the same arguments.
Any resulting merge conflicts must be resolved before the transaction can be committed or a new Dolt commit created.
1
CALL DOLT_PULL('origin');
2
CALL DOLT_PULL('feature-branch', '--force');
Copied!
Options
--no-ff: Create a merge commit even when the merge resolves as a fast-forward.
--squash: Merges changes to the working set without updating the commit history
--force: Ignores any foreign key warnings and proceeds with the commit.
When merging a branch, your session state must be clean. COMMIT orROLLBACK any changes, then DOLT_COMMIT() to create a new dolt commit on the target branch.
If the merge causes conflicts or constraint violations, you must resolve them using the dolt_conflicts system tables before the transaction can be committed. See Dolt system tables for details.
Example
1
-- Update local working set with remote changes
2
CALL DOLT_PULL('origin');
3
​
4
-- View a log of new commits
5
SELECT * FROM dolt_log LIMIT 5;
Copied!