Doltgres provides functions for version control features that update the state of the database, such
as creating or deleting branches, making a new commit, etc. Each is named after the Git command that
it imitates. So for example, the following Git command:
git add .
Is modeled as the following SQL statement in Doltgres:
SELECT DOLT_ADD('.');
The functions in this section are also available as stored procedures. Whether you access them as
functions or as procedures is up to you. The main difference is that in Postgres, stored procedures
cannot return values, but functions can. This means that if your application needs the result of an
operation, it must use the function version, rather than the procedure version.
Otherwise, these statements are equivalent:
SELECT DOLT_ADD('.'); -- returns a status value
CALL DOLT_ADD('.'); -- returns nothing, but will fail on an error
DOLT_ADD()
Adds working changes to staged for this session.
After adding tables to the staged area, they can be committed withDOLT_COMMIT().
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.
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Example
-- Set the current database for the session
USE mydb;
-- Make modifications
UPDATE table
SET column = 'new value'
WHERE pk = 'key';
-- Stage all changes.
SELECT DOLT_ADD('-A');
-- Commit the changes.
SELECT DOLT_COMMIT('-m', 'committing all changes');
DOLT_BACKUP()
Sync with a configured backup. Other backup commands not supported
via SQL yet.
SELECT DOLT_BACKUP('sync', 'name');
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Example
-- Set the current database for the session
USE mydb;
-- Upload the current database contents to the named backup
SELECT dolt_backup('sync', 'my-backup')
DOLT_BRANCH()
Create, delete, and rename branches.
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.
-- Create a new branch from the current HEAD
SELECT DOLT_BRANCH('myNewBranch');
-- Create a new branch from start point of tip of feature1 branch.
SELECT DOLT_BRANCH('myNewBranch', 'feature1');
-- Create a new branch by copying an existing branch
-- Will fail if feature1 branch already exists
SELECT DOLT_BRANCH('-c', 'main', 'feature1');
-- Create or replace a branch by copying an existing branch
-- '-f' forces the copy, even if feature1 branch already exists
SELECT DOLT_BRANCH('-c', '-f', 'main', 'feature1');
-- Delete a branch
SELECT DOLT_BRANCH('-d', 'branchToDelete');
-- Rename a branch
SELECT DOLT_BRANCH('-m', 'currentBranchName', 'newBranchName')
Notes
All characters must be ASCII (7 Bit)
May not start with '.' (period)
May not contain '..' (two periods)
May not contain '@{'
May not contain ASCII control characters
May not contain characters: ':', '?', '[', '\', '^', '~', '*'
May not contain whitespace (spaces, tabs, newlines)
May not end with '/'
May not end with '.lock'
May not be HEAD (case insensitive)
May not be indistinguishable from a commit hash. 32 characters, where all characters are 0-9 or a-z (case sensitive)
The dolt_branch() function implicitly commits the current transaction and begins a new one.
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.
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Examples
-- List the available branches
SELECT * FROM DOLT_BRANCHES;
+--------+----------------------------------+
| name | hash |
+--------+----------------------------------+
| backup | nsqtc86d54kafkuf0a24s4hqircvg68g |
| main | dvtsgnlg7n9squriob3nq6kve6gnhkf2 |
+--------+----------------------------------+
-- Create a new branch for development work from the tip of head and switch to it
SELECT DOLT_BRANCH('myNewFeature');
SELECT DOLT_CHECKOUT('myNewFeature');
-- View your current branch
select active_branch();
+----------------+
| active_branch |
+----------------+
| myNewFeature |
+----------------+
-- Create a new branch from an existing branch
SELECT DOLT_BRANCH('-c', 'backup', 'bugfix-3482');
-- Rename a branch
SELECT DOLT_BRANCH('-m', 'bugfix-3482', 'critical-bugfix-3482');
-- Delete a branch
SELECT DOLT_BRANCH('-d', 'old-unused-branch');
DOLT_CHECKOUT()
Switches this session to a different branch.
With table names as arguments, restores those tables to their contents
in the current HEAD.
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(). Uncommitted changes in the working set do not
transfer to the checked out branch as on the command line. We modified this behavior in the SQL
context because multiple users may be connected to the same branch. Having one user bring changes
from various other branches with them when they switch branches is too disruptive in the
multi-tenant SQL context.
DOLT_CHECKOUT() with a branch argument has two side effects on your session state:
The session's current database, as returned by SELECT DATABASE(), is now the unqualified
database name.
For the remainder of this session, references to the unqualified name of this database will
resolve to the branch checked out.
use mydb/branch1; -- current db is now `mydb/branch1`
insert into t1 values (1); -- modifying the `branch1` branch
select dolt_checkout('branch2'); -- current db is now `mydb`
insert into t1 values (2); -- modifying the `branch2` branch
use mydb/branch3; -- current db is now `mydb/branch3`
insert into mydb.t1 values (3); -- modifying the `branch2` branch
Options
-b: Create a new branch with the given name and switch to it.
-B: Similar to -b, but will move a branch if it already exists.
-t: When creating a new branch, set up 'upstream' configuration.
Output Schema
+---------+------+-----------------------------+
| Field | Type | Description |
+---------+------+-----------------------------+
| status | int | 0 if successful, 1 if not |
| message | text | success/failure information |
+---------+------+-----------------------------+
Example
-- Set the current database for the session
USE mydb;
-- Create and checkout to a new branch.
SELECT DOLT_CHECKOUT('-b', 'feature-branch');
-- Make modifications
UPDATE table
SET column = 'new value'
WHERE pk = 'key';
-- Stage and commit all changes.
SELECT DOLT_COMMIT('-a', '-m', 'committing all changes');
-- Go back to main
SELECT DOLT_CHECKOUT('main');
DOLT_CHERRY_PICK()
Apply the changes introduced by an existing commit.
Apply changes from existing commit and creates a new commit from the current HEAD.
+-----------------------+------+---------------------------------+
| Field | Type | Description |
+-----------------------+------+---------------------------------+
| hash | text | hash of the applied commit |
| data_conflicts | int | number of data conflicts |
| schema_conflicts | int | number of schema conflicts |
| constraint_violations | int | number of constraint violations |
+-----------------------+------+---------------------------------+
Example
For the below example consider the following set up of main and mybranch branches:
-- Checkout main branch
SELECT DOLT_CHECKOUT('main');
-- View a log of commits
SELECT commit_hash, message FROM dolt_log;
+----------------------------------+----------------------------+
| commit_hash | message |
+----------------------------------+----------------------------+
| 7e2q0hibo2m2af874i4e7isgnum74j4m | create a new table |
| omuqq67att6vfnka94drdallu4983gnr | Initialize data repository |
+----------------------------------+----------------------------+
2 rows in set (0.00 sec)
-- View the table
SELECT * FROM mytable;
Empty set (0.00 sec)
-- Checkout new branch
SELECT DOLT_CHECKOUT('mybranch');
-- View a log of commits
SELECT commit_hash, message FROM dolt_log;
+----------------------------------+----------------------------+
| commit_hash | message |
+----------------------------------+----------------------------+
| 577isdjbq1951k2q4dqhli06jlauo51p | add 3, 4, 5 to the table |
| k318tpmqn4l97ofpaerato9c3m70lc14 | add 1, 2 to the table |
| 7e2q0hibo2m2af874i4e7isgnum74j4m | create a new table |
| omuqq67att6vfnka94drdallu4983gnr | Initialize data repository |
+----------------------------------+----------------------------+
4 rows in set (0.00 sec)
-- View the table
SELECT * FROM mytable;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)
We want to cherry-pick only the change introduced in commit hash'k318tpmqn4l97ofpaerato9c3m70lc14', which inserts 1 and 2 to the table. Specifying'mybranch~1' instead of the commit hash also works.
-- Checkout main branch
SELECT DOLT_CHECKOUT('main');
-- Cherry-pick the commit
SELECT DOLT_CHERRY_PICK('k318tpmqn4l97ofpaerato9c3m70lc14');
+----------------------------------+
| hash |
+----------------------------------+
| mh518gdgbsut8m705b7b5rie9neq9uaj |
+----------------------------------+
1 row in set (0.02 sec)
mydb> SELECT * FROM mytable;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
mydb> SELECT commit_hash, message FROM dolt_log;
+----------------------------------+----------------------------+
| commit_hash | message |
+----------------------------------+----------------------------+
| mh518gdgbsut8m705b7b5rie9neq9uaj | add 1, 2 to the table |
| 7e2q0hibo2m2af874i4e7isgnum74j4m | create a new table |
| omuqq67att6vfnka94drdallu4983gnr | Initialize data repository |
+----------------------------------+----------------------------+
3 rows in set (0.00 sec)
DOLT_CLEAN()
Deletes untracked tables in the working set.
Deletes only specified untracked tables if table names are passed as arguments.
With --dry-run flag, tests whether removing untracked tables will
return with zero status.
--dry-run: Test removing untracked tables from working set.
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Example
-- Create three new tables
create table tracked (x int primary key);
create table committed (x int primary key);
create table untracked (x int primary key);
-- Commit the first table
select dolt_add('committed');
select dolt_commit('-m', 'commit a table');
+----------------------------------+
| hash |
+----------------------------------+
| n7gle7jv6aqf72stbdicees6iduhuoo9 |
+----------------------------------+
-- Track the second table
select dolt_add('tracked');
-- Observe database status
select * from dolt_status;
+-------------------+--------+-----------+
| table_name | staged | status |
+-------------------+--------+-----------+
| public.tracked | 1 | new table |
| public.untracked | 0 | new table |
+-------------------+--------+-----------+
-- Clear untracked tables
select dolt_clean('untracked');
-- Observe final status
select * from dolt_status;
+-----------------+--------+-----------+
| table_name | staged | status |
+-----------------+--------+-----------+
| public.tracked | 1 | new table |
+-----------------+--------+-----------+
-- Committed and tracked tables are preserved
select tablename from pg_tables;
+----------------+
| tablename |
+----------------+
| committed |
| tracked |
+----------------+
DOLT_CLONE()
Clones an existing Dolt database into a new database within the current Dolt environment. The
existing database must be specified as an argument, either as a file URL that points to an existing
Dolt database on disk, or a doltremote URL for remote hosted database (e.g. a database hosted in
an S3 bucket). An additional argument can optionally be supplied to specify the name of the new,
cloned database, otherwise the current name of the existing database will be used.
--remote: Name of the remote to be added to the new, cloned database. The default is 'origin'.
-b, --branch: The branch to be cloned. If not specified all branches will be cloned.
--depth: Clone a single branch and limit history to the given commit depth.
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Examples
-- Clone the us-jails database from a local remote
SELECT DOLT_CLONE('file:///myDatabasesDir/us-jails/.dolt/noms');
-- Use the new, cloned database
-- NOTE: quotes are required for database names with hyphens
USE "us-jails";
select tablename from pg_tables;
+-----------------------------+
| tablename |
+-----------------------------+
| incidents |
| inmate_population_snapshots |
| jails |
+-----------------------------+
DOLT_COMMIT()
Commits staged tables to HEAD.
DOLT_COMMIT() also implicitly commits the current transaction.
SELECT DOLT_COMMIT('-a', '-m', 'This is a commit');
SELECT DOLT_COMMIT('-m', 'This is a commit');
SELECT DOLT_COMMIT('-m', 'This is a commit', '--author', 'John Doe <johndoe@example.com>');
Options
-m, --message: Use the given <msg> as the commit message. Required
-a, --all: Stages all modified tables (but not newly created tables) before committing.
-A, --ALL: Stages all tables (including new tables) 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.
--skip-empty: Record a commit only if there are changes to be committed. The commit operation will be a no-op, instead of an error, if there are no changes staged to commit. An error will be thrown if --skip-empty is used with --allow-empty.
--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
author@example.com" format.
--amend: Overwrite the commit message for the current HEAD, rather than creating a new commit.
Output Schema
+-------+------+----------------------------+
| Field | Type | Description |
+-------+------+----------------------------+
| hash | text | hash of the commit created |
+-------+------+----------------------------+
Examples
-- Set the current database for the session
USE mydb;
-- Make modifications
UPDATE table
SET column = 'new value'
WHERE pk = 'key';
-- Stage all changes and commit.
SELECT DOLT_COMMIT('-a', '-m', 'This is a commit', '--author', 'John Doe <johndoe@example.com>');
DOLT_CONFLICTS_RESOLVE()
When a merge finds conflicting changes, it documents them in the dolt_conflicts table.
A conflict is between two versions: ours (the rows at the destination branch head) and theirs
(the rows at the source branch head).dolt conflicts resolve will automatically resolve the conflicts by taking either
the ours or theirs versions for each row.
<table>: List of tables to be resolved. '.' can be used to resolve all tables.
--ours: For all conflicts, take the version from our branch and resolve the conflict.
--theirs: For all conflicts, take the version from their branch and resolve the conflict.
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Examples
-- Set the current database for the session
USE mydb;
-- Attempt merge
SELECT DOLT_MERGE('feature-branch');
-- Check for conflicts
SELECT * FROM dolt_conflicts;
-- Resolve conflicts for tables t1 and t2 with rows from our branch.
SELECT DOLT_CONFLICTS_RESOLVE('--ours', 't1', 't2');
DOLT_FETCH()
Fetch refs, along with the objects necessary to complete their histories
and update remote-tracking branches.
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Example
-- Get remote main
SELECT DOLT_FETCH('origin', 'main');
-- Inspect the hash of the fetched remote branch
SELECT HASHOF('origin/main');
-- Merge remote main with current branch
SELECT DOLT_MERGE('origin/main');
Notes
Dropping the second argument, or passing NULL, will result is using the default refspec.
DOLT_GC()
Cleans up unreferenced data from the database to reclaim disk space.
SELECT DOLT_GC();
SELECT DOLT_GC('--shallow');
Options
--shallow Performs a faster but less thorough garbage collection.
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
DOLT_MERGE()
Incorporates changes from the named commits (since the time their
histories diverged from the current branch) into the current
branch.
Any resulting merge conflicts must be resolved before the transaction
can be committed or a new Dolt commit created. DOLT_MERGE() creates
a new commit for any successful merge with auto-generated commit
message if not defined.
SELECT DOLT_MERGE('feature-branch'); -- Optional --squash parameter
SELECT DOLT_MERGE('feature-branch', '--no-ff', '-m', 'This is a msg for a non fast forward merge');
SELECT DOLT_MERGE('--abort');
Notes
The dolt_merge() function implicitly commits the current transaction and begins a new one.
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.
--author: Specify an explicit author using the standard A U Thor <author@example.com> format.
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.
Output Schema
+--------------+------+--------------------------------------+
| Field | Type | Description |
+--------------+------+--------------------------------------+
| hash | text | hash of the merge commit |
| fast_forward | int | whether the merge was a fast forward |
| conflicts | int | number of conflicts created |
| message | text | optional informational message |
+--------------+------+--------------------------------------+
Example
-- Set the current database for the session
USE mydb;
-- Create and checkout to a new branch.
SELECT DOLT_CHECKOUT('-b', 'feature-branch');
-- Make modifications
UPDATE table
SET column = 'new value'
WHERE pk = 'key';
-- Stage and commit all changes.
SELECT DOLT_COMMIT('-a', '-m', 'committing all changes');
-- Go back to main
SELECT DOLT_MERGE('feature-branch', '--author', 'John Doe <johndoe@example.com>');
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 bydolt merge <remote>/<branch>.
Any resulting merge conflicts must be resolved before the transaction
can be committed or a new Dolt commit created.
--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.
Output Schema
+--------------+------+-------------------------------------+
| Field | Type | Description |
+--------------+------+-------------------------------------+
| fast_forward | int | whether the pull was a fast forward |
| conflicts | int | number of conflicts created |
| message | text | optional informational message |
+--------------+------+-------------------------------------+
Example
-- Update local working set with remote changes
-- Note: this requires upstream tracking information to be set in order for
-- Dolt to know what remote branch to merge
SELECT DOLT_PULL('origin');
-- Update local working set with remote changes from an explicit branch
SELECT DOLT_PULL('origin', 'some-branch');
-- View a log of new commits
SELECT * FROM dolt_log LIMIT 5;
DOLT_PURGE_DROPPED_DATABASES()
Example
-- Create a database and populate a table in the working set
CREATE DATABASE database1;
use database1;
create table t(pk int primary key);
-- Dropping the database will move it to a temporary holding area
DROP DATABASE database1;
-- At this point, the database can be restored by calling dolt_undrop('database1'), but
-- instead, we permanently delete it by calling dolt_purge_dropped_databases().
SELECT dolt_purge_dropped_databases();
DOLT_PUSH()
Updates remote refs using local refs, while sending objects necessary to
complete the given refs.
--force: Update the remote with local history, overwriting any conflicting history in the remote.
Output Schema
+---------+------+--------------------------------+
| Field | Type | Description |
+---------+------+--------------------------------+
| status | int | 0 if successful, 1 if not |
| message | text | optional informational message |
+---------+------+--------------------------------+
Example
-- Checkout new branch
SELECT DOLT_CHECKOUT('-b', 'feature-branch');
-- Add a table
CREATE TABLE test (a int primary key);
-- Create commit
SELECT DOLT_COMMIT('-a', '-m', 'create table test');
-- Push to remote
SELECT DOLT_PUSH('origin', 'feature-branch');
DOLT_REBASE()
For example, consider the commit graph below, where a feature branch has branched off of a main branch, and both branches have added commits:
A → B → C → D → E → F main
↘
G → H → I feature
If we rebase from the feature branch using the main branch as our upstream, the default rebase plan will include commits G, H, and I, since those commits are reachable from our current branch, but NOT reachable from the upstream branch. By default, the changes from those same commits will be reapplied, in the same order, to the tip of the upstream branch main. The resulting commit graph will then look like:
A → B → C → D → E → F main
↘
G' → H' → I' feature
Rebasing is useful to clean and organize your commit history, especially before merging a feature branch back to a shared branch. For example, you can drop commits that contain debugging or test changes, or squash or fixup small commits into a single commit, or reorder commits so that related changes are adjacent in the new commit history.
Currently only interactive rebases are supported, and there is no support for resolving conflicts that arise while executing a rebase plan. If applying a commit creates a conflict, the rebase will be automatically aborted.
Options
--interactive or -i: Start an interactive rebase. Currently only interactive rebases are supported, so this option is required.
--continue: Continue an interactive rebase after adjusting the rebase plan stored in dolt_rebase.
--abort: Abort a rebase in progress.
Output Schema
+---------+------+-----------------------------+
| Field | Type | Description |
+---------+------+-----------------------------+
| status | int | 0 if successful, 1 if not |
| message | text | success/failure information |
+---------+------+-----------------------------+
Example
-- create a simple table
create table t (pk int primary key);
select dolt_commit('-Am', 'creating table t');
-- create a new branch that we'll add more commits to later
select dolt_branch('branch1');
-- create another commit on the main branch, right after where branch1 branched off
insert into t values (0);
select dolt_commit('-am', 'inserting row 0');
-- switch to branch1 and create three more commits that each insert one row
select dolt_checkout('branch1');
insert into t values (1);
select dolt_commit('-am', 'inserting row 1');
insert into t values (2);
select dolt_commit('-am', 'inserting row 2');
insert into t values (3);
select dolt_commit('-am', 'inserting row 3');
-- check out what our commit history on branch1 looks like before we rebase
select commit_hash, message from dolt_log;
commit_hash | message
----------------------------------+----------------------------
m2v3oajs9jesvvc44ihqlsu1uq2c8jf2 | inserting row 3
qa1t5ieqs418s1b7mssqlmpn68ackq20 | inserting row 2
tgltn67jjho1mp8a3jdl3jkip08jbbun | inserting row 1
nof0lk6ufv031mddiahqfqfelqcpjdv5 | creating table t
pfjaqljdrdn43877sbc2d2sla9g3eb8u | CREATE DATABASE
k23mej9jdej41s0n7o2g8gp5rpgvrfdb | Initialize data repository
(6 rows)
-- start an interactive rebase and check out the default rebase plan; this will rebase
-- all the new commits on this branch and move them to the tip of the main branch
select dolt_rebase('-i', 'main');
select * from dolt_rebase order by rebase_order;
rebase_order | action | commit_hash | commit_message
--------------+--------+----------------------------------+-----------------
1.00 | pick | tgltn67jjho1mp8a3jdl3jkip08jbbun | inserting row 1
2.00 | pick | qa1t5ieqs418s1b7mssqlmpn68ackq20 | inserting row 2
3.00 | pick | m2v3oajs9jesvvc44ihqlsu1uq2c8jf2 | inserting row 3
(3 rows)
-- adjust the rebase plan to reword the first commit, drop the commit that inserted row 2,
-- and combine the third commit into the previous commit
update dolt_rebase set action='reword', commit_message='insert rows' where rebase_order=1;
update dolt_rebase set action='drop' where rebase_order=2;
update dolt_rebase set action='fixup' where rebase_order=3;
-- continue rebasing now that we've adjusted the rebase plan
select dolt_rebase('--continue');
-- check out the history
select commit_hash, message from dolt_log;
commit_hash | message
----------------------------------+----------------------------
8jc1dpj25fv6f2kn3bd47uokc8hs1vp0 | insert rows
gd5rnrmjvbf0fb6sb8dfaf5a344t68ei | inserting row 0
nof0lk6ufv031mddiahqfqfelqcpjdv5 | creating table t
pfjaqljdrdn43877sbc2d2sla9g3eb8u | CREATE DATABASE
k23mej9jdej41s0n7o2g8gp5rpgvrfdb | Initialize data repository
(5 rows)
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Example
-- Add a HTTP remote
SELECT DOLT_REMOTE('add','origin','https://doltremoteapi.dolthub.com/Dolthub/museum-collections');
-- Add a HTTP remote with shorthand notation for the URL
SELECT DOLT_REMOTE('add','origin1','Dolthub/museum-collections');
-- Add a filesystem based remote
SELECT DOLT_REMOTE('add','origin2','file:///Users/jennifer/datasets/museum-collections');
-- List remotes to check.
SELECT * FROM dolt_remotes;
+---------+--------------------------------------------------------------+-----------------------------------------+--------+
| name | url | fetch_specs | params |
+---------+--------------------------------------------------------------+-----------------------------------------+--------+
| origin | https://doltremoteapi.dolthub.com/Dolthub/museum-collections | ["refs/heads/*:refs/remotes/origin/*"] | {} |
| origin1 | https://doltremoteapi.dolthub.com/Dolthub/museum-collections | ["refs/heads/*:refs/remotes/origin1/*"] | {} |
| origin2 | file:///Users/jennifer/datasets/museum-collections | ["refs/heads/*:refs/remotes/origin2/*"] | {} |
+---------+--------------------------------------------------------------+-----------------------------------------+--------+
-- Remove a remote
SELECT DOLT_REMOTE('remove','origin1');
-- List remotes to check.
SELECT * FROM dolt_remotes;
+---------+--------------------------------------------------------------+-----------------------------------------+--------+
| name | url | fetch_specs | params |
+---------+--------------------------------------------------------------+-----------------------------------------+--------+
| origin | https://doltremoteapi.dolthub.com/Dolthub/museum-collections | ["refs/heads/*:refs/remotes/origin/*"] | {} |
| origin2 | file:///Users/jennifer/datasets/museum-collections | ["refs/heads/*:refs/remotes/origin2/*"] | {} |
+---------+--------------------------------------------------------------+-----------------------------------------+--------+
DOLT_RESET()
With no arguments, resets staged tables to their HEAD state. Can also be used to reset a database to
a specific commit.
Like other data modifications, after a reset you must COMMIT the
transaction for any changes to affected tables to be visible to other
clients.
With the --hard option, the dolt_reset() function implicitly commits the current transaction
and begins a new one.
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.
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Example
-- Set the current database for the session
USE mydb;
-- Make modifications
UPDATE table
SET column = 'new value'
WHERE pk = 'key';
-- Reset the changes permanently.
SELECT DOLT_RESET('--hard');
-- Makes some more changes.
UPDATE table
SET column = 'new value'
WHERE pk = 'key';
-- Stage the table.
SELECT DOLT_ADD('table')
-- Unstage the table.
SELECT DOLT_RESET('table')
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.
--author=<author>: Specify an explicit author using the standard A U Thor <author@example.com> format.
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Example
-- Create a table and add data in multiple commits
CREATE TABLE t1(pk INT PRIMARY KEY, c VARCHAR(255));
SELECT dolt_add('t1')
SELECT dolt_commit('-m', 'Creating table t1');
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
SELECT dolt_commit('-am', 'Adding some data');
insert into t1 VALUES(10, 'aa'), (20, 'bb'), (30, 'cc');
SELECT dolt_commit('-am', 'Adding some more data');
-- Examine the changes made in the commit immediately before the current HEAD commit
SELECT to_pk, to_c, to_commit, diff_type FROM dolt_diff_t1 WHERE to_commit=hashof('HEAD~1');
+-------+------+----------------------------------+-----------+
| to_pk | to_c | to_commit | diff_type |
+-------+------+----------------------------------+-----------+
| 1 | a | fc4fks6jutcnee9ka6458nmuot7rl1r2 | added |
| 2 | b | fc4fks6jutcnee9ka6458nmuot7rl1r2 | added |
| 3 | c | fc4fks6jutcnee9ka6458nmuot7rl1r2 | added |
+-------+------+----------------------------------+-----------+
-- Revert the commit immediately before the current HEAD commit
SELECT dolt_revert('HEAD~1');
-- Check out the new commit created by dolt_revert
SELECT commit_hash, message FROM dolt_log limit 1;
+----------------------------------+---------------------------+
| commit_hash | message |
+----------------------------------+---------------------------+
| vbevrdghj3in3napcgdsch0mq7f8en4v | Revert "Adding some data" |
+----------------------------------+---------------------------+
-- View the exact changes made by the revert commit
SELECT from_pk, from_c, to_commit, diff_type FROM dolt_diff_t1 WHERE to_commit=hashof('HEAD');
+---------+--------+----------------------------------+-----------+
| from_pk | from_c | to_commit | diff_type |
+---------+--------+----------------------------------+-----------+
| 1 | a | vbevrdghj3in3napcgdsch0mq7f8en4v | removed |
| 2 | b | vbevrdghj3in3napcgdsch0mq7f8en4v | removed |
| 3 | c | vbevrdghj3in3napcgdsch0mq7f8en4v | removed |
+---------+--------+----------------------------------+-----------+
--author: Specify an explicit author using the standard "A U Thor
author@example.com" format.
Output Schema
+--------+------+---------------------------+
| Field | Type | Description |
+--------+------+---------------------------+
| status | int | 0 if successful, 1 if not |
+--------+------+---------------------------+
Example
-- Set the current database for the session
USE mydb;
-- Make modifications
UPDATE table
SET column = 'new value'
WHERE pk = 'key';
-- Stage and commit all changes.
SELECT DOLT_COMMIT('-am', 'committing all changes');
-- Create a tag for the HEAD commit.
SELECT DOLT_TAG('v1','head','-m','creating v1 tag');
DOLT_UNDROP()
SELECT DOLT_UNDROP(<database_name>);
Options
dolt_undrop() takes a single argument – the name of the dropped database to restore. When called
without any arguments, dolt_undrop() returns an error message that contains a list of all dropped
databases that are available to be restored.
Example
-- Create a database and populate a table in the working set
CREATE DATABASE database1;
use database1;
create table t(pk int primary key);
-- Dropping the database will move it to a temporary holding area
DROP DATABASE database1;
-- calling dolt_undrop() with no arguments will return an error message that
-- lists the dropped database that are available to be restored
SELECT dolt_undrop();
-- Use dolt_undrop() to restore it
SELECT dolt_undrop('database1');
SELECT * FROM database1.t;
DOLT_VERIFY_CONSTRAINTS()
DOLT_VERIFY_CONSTRAINTS by default does not detect constraints for row changes
that have been previously committed. The --all option can be specified if you
wish to validate all rows in the database. If FOREIGN_KEY_CHECKS has been disabled in prior commits,
you may want to use the --all option to ensure that the current state is
consistent and no violated constraints are missed.
Arguments and Options
<table>: The table(s) to check constraints on. If omitted, checks all tables.
-a, --all:
Verifies constraints against every row.
Output Schema
+------------+------+-----------------------------------------+
| Field | Type | Description |
+------------+------+-----------------------------------------+
| violations | int | 1 if violations were found, otherwise 0 |
+------------+------+-----------------------------------------+
Example
For the below examples consider the following schema:
The ACTIVE_BRANCH() function returns the name of the currently
active branch for this session.
DOLT_MERGE_BASE()
DOLT_MERGE_BASE() returns the hash of the common ancestor between
two branches.
Consider the following branch structure:
A---B---C feature
/
D---E---F---G main
The following would return the hash of commit E:
DOLT_HASHOF()
The DOLT_HASHOF() function returns the commit hash of a branch or other commit spec.
DOLT_HASHOF_TABLE()
The DOLT_HASHOF_TABLE() function returns the value hash of a table. The hash is the hash of all the rows in the table,
and is dependent on their serialization format. As such a table could have the same rows, but different hashes if the
serialization format has changed, however if a table hash has not changed, then it's guaranteed that the table's data has
not changed.
This function can be used to watch for changes in data by storing previous hashes in your application and comparing them
to the current hash. For example, you can use this function to get the hash of a table named color like so:
SELECT dolt_hashof_table('color');
+----------------------------------+
| dolt_hashof_table('color') |
+----------------------------------+
| q8t28sb3h5g2lnhiojacpi7s09p4csjv |
+----------------------------------+
1 row in set (0.01 sec)
DOLT_HASHOF_DB()
This function can be used to watch for changes in the database by storing previous hashes in your application and comparing them
to the current hash. For example, you can use this function to get the hash of the entire database like so:
It should be noted that if you are connected to branch 'main' and you call dolt_hashof_db('feature'), the hash may be different
than if you were connected to branch 'feature' and called dolt_hashof_db(). This happens if there exist changes to the working set on
branch 'feature' that have not been committed. Calling dolt_hashof_db('feature') while on 'main' is equivalent to callingdolt_hashof_db('HEAD') while on branch 'feature'.
The general recommendation when trying to look for changes to the database is to connect to the branch you want to use, then
call dolt_hashof_db() without any arguments. Any change in the hash means that the database has changed.
DOLT_VERSION()
The DOLT_VERSION() function returns the version string for the Dolt
binary.
The HASH_ANCESTOR(target, ancestor) function returns a boolean indicating whether a
candidate ancestor commit is in the commit graph of the target ref.
Consider the example commit graph from above:
A---B---C feature
/
D---E---F---G main
A hypothetical example where we substitute letters for commit
hashes would look like:
Table functions operate like regular SQL functions, but instead of returning a single,
scalar value, a table function returns rows of data, just like a table. Dolt's table
functions have several restrictions in how they can be used in queries. For example, you
cannot currently alias a table function or join a table function with another table or
table function.
DOLT_DIFF()
The main difference between the results of the DOLT_DIFF() table function and the dolt_commit_diff_$tablename
system table is the schema of the returned results. dolt_commit_diff_$tablename generates the resulting schema
based on the table's schema at the currently checked out branch. DOLT_DIFF() will use the schema at the from_commit
for the from_ columns and the schema at the to_commit for the to_ columns. This can make it easier to view
diffs where the schema of the underlying table has changed.
Note that the DOLT_DIFF() table function currently requires that argument values be literal values.
The DOLT_DIFF() table function takes either two or three required arguments:
from_revision — the revision of the table data for the start of the diff. This may be a commit, tag, branch name, or other revision specifier (e.g. "main~").
to_revision — the revision of the table data for the end of the diff. This may be a commit, tag, branch name, or other revision specifier (e.g. "main~").
from_revision..to_revision — gets the two dot diff, or revision of table data between the from_revision and to_revision. This is equivalent to dolt_diff(<from_revision>, <to_revision>, <tablename>).
from_revision...to_revision — gets the three dot diff, or revision of table data between the from_revision and to_revision, starting at the last common commit.
tablename — the name of the table containing the data to diff.
Schema
+------------------+----------+
| field | type |
+------------------+----------+
| from_commit | TEXT |
| from_commit_date | DATETIME |
| to_commit | TEXT |
| to_commit_date | DATETIME |
| diff_type | TEXT |
| other cols | |
+------------------+----------+
The remaining columns are dependent on the schema of the user table as it existed at the from_commit and at
the to_commit. For every column X in your table at the from_commit revision, there is a column in the result
set named from_X. Likewise, for every column Y in your table at the to_commit revision, there is a column
in the result set named to_Y. This is the major difference between the DOLT_DIFF() table function and thedolt_commit_diff_$tablename system table – DOLT_DIFF() uses the two schemas at the to_commit andfrom_commit revisions to form the to and from columns of the result set, while dolt_commit_diff_$tablename uses
only the table schema of the currently checked out branch to form the to and from columns of the result set.
Example
Consider a table named inventory in a database with two branches: main and feature_branch. We can use theDOLT_DIFF() function to calculate a diff of the table data from the main branch to the feature_branch branch
to see how our data has changed on the feature branch.
Here is the schema of inventory at the tip of main:
+----------+------+
| field | type |
+----------+------+
| pk | int |
| name | text |
| quantity | int |
+----------+------+
Here is the schema of inventory at the tip of feature_branch:
+----------+------+
| field | type |
+----------+------+
| pk | int |
| name | text |
| color | text |
| size | int |
+----------+------+
Based on the schemas at the two revision above, the resulting schema from DOLT_DIFF() will be:
+------------------+----------+
| field | type |
+------------------+----------+
| from_pk | int |
| from_name | text |
| from_quantity | int |
| from_commit | TEXT |
| from_commit_date | DATETIME |
| to_pk | int |
| to_name | text |
| to_color | text |
| to_size | int |
| to_commit | TEXT |
| to_commit_date | DATETIME |
| diff_type | text |
+------------------+----------+
To calculate the diff and view the results, we run the following query:
SELECT * FROM DOLT_DIFF('main', 'feature_branch', 'inventory')
The results from DOLT_DIFF() show how the data has changed going from main to feature_branch:
Let's say the above database has a commit graph that looks like this:
A - B - C - D (main)
\
E - F (feature_branch)
The example above gets the two dot diff, or differences between two revisions: main and feature_branch.dolt_diff('main', 'feature_branch', 'inventory') (equivalent to dolt_diff('main..feature_branch', 'inventory'))
outputs the difference from F to D (i.e. with effects of E and F).
Three dot diff is useful for showing differences introduced by a feature branch from the point at which it diverged
from the main branch. Three dot diff is used to show pull request diffs.
Therefore, dolt_diff('main...feature_branch') outputs just the differences in feature_branch (i.e. E and F).
DOLT_DIFF_STAT()
The DOLT_DIFF_STAT() table function calculates the data difference stat between any two commits
in the database. Schema changes such as creating a new table with no rows, or deleting a table with no rows will
return empty result. Each row in the result set describes a diff stat for a single table with statistics information of
number of rows unmodified, added, deleted and modified, number of cells added, deleted and modified and total number of
rows and cells the table has at each commit.
For keyless tables, this table function only provides the number of added and deleted rows. It
returns empty result for tables with no data changes.
Note that the DOLT_DIFF_STAT() table function currently requires that argument values be literal values.
Privileges
DOLT_DIFF_STAT() table function requires SELECT privilege for all tables if no table is defined or
for the defined table only.
The DOLT_DIFF_STAT() table function takes three arguments:
from_revision — the revision of the table data for the start of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main~", "WORKING", "STAGED").
to_revision — the revision of the table data for the end of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main~", "WORKING", "STAGED").
from_revision..to_revision — gets the two dot diff stat, or revision of table data between the from_revision and to_revision. This is equivalent to dolt_diff_stat(<from_revision>, <to_revision>, <tablename>).
from_revision...to_revision — gets the three dot diff stat, or revision of table data between the from_revision and to_revision, starting at the last common commit.
tablename — the name of the table containing the data to diff. This argument is optional. When it's not defined, all tables with data diff will be returned.
Consider we start with a table inventory in a database on main branch. When we make any changes, we can use
the DOLT_DIFF_STAT() function to calculate a diff of the table data or all tables with data changes across specific
commits.
Here is the schema of inventory at the tip of main:
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| pk | int | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| quantity | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
Here is what table inventory has at the tip of main:
The DOLT_DIFF_SUMMARY() table function is a summary of what tables changed and how
between any two commits in the database. Only changed tables will be listed in the result,
along with the diff type ('added', 'dropped', 'modified', 'renamed') and whether there are
data and schema changes.
It returns empty result if there are no tables with changes.
Note that the DOLT_DIFF() table function currently requires that argument values be literal values.
Privileges
DOLT_DIFF_SUMMARY() table function requires SELECT privilege for all tables if no
table is defined or for the defined table only.
The DOLT_DIFF_SUMMARY() table function takes three arguments:
from_revision — the revision of the table data for the start of the diff. This
argument is required. This may be a commit, tag, branch name, or other revision
specifier (e.g. "main~", "WORKING", "STAGED").
to_revision — the revision of the table data for the end of the diff. This argument is
required. This may be a commit, tag, branch name, or other revision specifier (e.g.
"main~", "WORKING", "STAGED").
from_revision..to_revision — gets the two dot diff summary, or revision of table data
between the from_revision and to_revision. This is equivalent todolt_diff_summary(<from_revision>, <to_revision>, <tablename>).
from_revision...to_revision — gets the three dot diff summary, or revision of table data
between the from_revision and to_revision, starting at the last common commit.
tablename — the name of the table containing the data to diff. This argument is
optional. When it's not defined, all tables with data diff will be returned.
Schema
+-----------------+---------+
| field | type |
+-----------------+---------+
| from_table_name | TEXT |
| to_table_name | TEXT |
| diff_type | TEXT |
| data_change | BOOLEAN |
| schema_change | BOOLEAN |
+-----------------+---------+
Example
Consider we start with a table inventory in a database on main branch. When we make
any changes, we can use the DOLT_DIFF_SUMMARY() function to calculate a diff of the
table data or all tables with data changes across specific commits.
Here is the schema of inventory at the tip of main:
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| pk | int | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| quantity | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
Here is what table inventory has at the tip of main:
The DOLT_LOG table function gets the commit log for all commits reachable from the
provided revision's HEAD (or the current HEAD if no revision is provided).
Note that the DOLT_LOG() table function currently requires that argument values be literal values.
Privileges
DOLT_LOG() table function requires SELECT privilege for all tables.
The DOLT_LOG() table function takes any number of optional revision arguments:
optional_revision: a branch name, tag, or commit ref (with or without an ancestor
spec) that specifies which ancestor commits to include in the results. If no revisions
are specified, the default is the current branch HEAD.
--min-parents: The minimum number of parents a commit must have to be included in the log.
--merges: Equivalent to min-parents == 2, this will limit the log to commits with 2 or
more parents.
--parents: Shows all parents of each commit in the log.
--decorate: Shows refs next to commits. Valid options are short, full, no, and auto. Defaults to "no".
--not: Excludes commits reachable by revision.
--tables: Limits the log to commits that affect the specified tables. Any number of comma separated tables can be specified.
Schema
+-------------+----------+
| field | type |
+-------------+--------- +
| commit_hash | text |
| committer | text |
| email | text |
| date | datetime |
| message | text |
| parents | text | -- column hidden unless `--parents` flag provided
| refs | text | -- column hidden unless `--decorate` is "short" or "full"
+-------------+--------- +
Example
Consider we have the following commit graph:
A - B - C - D (main)
\
E - F (feature)
To get the commit log for the main branch, we can use the query:
SELECT * FROM DOLT_LOG('main');
And it would return commits in reverse-chronological order - D,C, B, and A. The
output will look something like:
To get the commit log for the feature branch, we can change the revision in the above
query:
SELECT * FROM DOLT_LOG('feature');
And it would return all commits reachable from the HEAD of feature - F, E, C,B, and A.
Two and three dot log
We also support two and three dot log. Two dot log returns commits from a revision,
excluding commits from another revision. If we want all commits in feature, excluding
commits from main, all of these queries will return commits F and E.
SELECT * FROM DOLT_LOG('main..feature');
SELECT * FROM DOLT_LOG('feature', '^main');
SELECT * FROM DOLT_LOG('feature', '--not', 'main');
Three dot log returns commits in either revision, excluding commits in BOTH revisions. If
we want commits in main OR feature, excluding commits in main AND feature, this
query would return commits F, E, and D.
SELECT * FROM DOLT_LOG('main...feature');
Note: The order of revisions in two dot log matters, but not for three dot log.DOLT_LOG('main..feature') returns F and E, while DOLT_LOG('feature..main')
returns just D. DOLT_LOG('main...feature') and DOLT_LOG('feature...main')
both return F, E, and D.
DOLT_PATCH()
Generate the SQL statements needed to patch a table (or all tables) from a starting revision
to a target revision. This can be useful when you want to import data into Dolt from an external source,
compare differences, and generate the SQL statements needed to patch the original source.
Both schema and/or data diff statements are returned if applicable. Some data diff cannot be
produced from incompatible schema changes; these are shown as warnings containing
which table this occurred on.
The order of the statements is that the schema patch comes first after the data patch. If patching
all tables, then we recommend to turn off the foreign key checks (SET foreign_key_checks=0;)
before applying these patch statements in order to avoid conflicts.
Privileges
DOLT_PATCH() table function requires SELECT privilege for all tables if no table is defined or
for the defined table only.
The DOLT_PATCH() table function takes the following arguments:
from_revision — the revision of the table data for the start of the patch. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main~", "WORKING", "STAGED").
to_revision — the revision of the table data for the end of the patch. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main~", "WORKING", "STAGED").
from_revision..to_revision — gets the two dot patch, or revision of table data between the from_revision and to_revision. This is equivalent to dolt_patch(<from_revision>, <to_revision>, <tablename>).
from_revision...to_revision — gets the three dot patch, or revision of table data between the from_revision and to_revision, starting at the last common commit.
tablename — the name of the table containing the data and/or schema to patch. This argument is optional. When it's not defined, all tables with data and/or schema patch will be returned.
Schema
+------------------+--------+
| field | type |
+------------------+--------+
| statement_order | BIGINT |
| from_commit_hash | TEXT |
| to_commit_hash | TEXT |
| table_name | TEXT |
| diff_type | TEXT |
| statement | TEXT |
+------------------+--------+
Example
Consider we start with a table inventory in a database on main branch. When we make any changes, we can use
the DOLT_PATCH() function to get SQL patch statements of the table data or all tables with data changes across specific
commits.
Here is the schema of inventory at the tip of main:
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| pk | int | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| quantity | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
Here is what table inventory has at the tip of main:
The DOLT_REFLOG() table function shows the history of named refs (e.g. branches and tags), which is useful when you want to understand how a branch or tag has changed over time to reference different commits, particularly for information that isn't surfaced through the dolt_log system table or dolt_log() table function. For example, if you use dolt_reset() to change the commit a branch points to, you can use dolt_reflog() to see what commit the branch was pointing to before it was moved to that commit. Another common use case for dolt_reflog() is to recreate a branch or tag that was accidentally deleted. The example section below shows how to recreate a deleted branch.
The Dolt reflog currently only supports named references, such as branches and tags, and not any of Git's special refs (e.g. HEAD, FETCH-HEAD, MERGE-HEAD).
The Dolt reflog can be queried for the log of references, even after a reference has been deleted. In Git, once a branch or tag is deleted, the reflog for that ref is also deleted and to find the last commit a branch or tag pointed to you have to use Git's special HEAD reflog to find the commit, which can sometimes be challenging. Dolt makes this much easier by allowing you to see the history for a deleted ref so you can easily see the last commit a branch or tag pointed to before it was deleted.
Privileges
There are no special privileges required to use the dolt_reflog() table function.
Options
DOLT_REFLOG()
DOLT_REFLOG(['--all'], <ref_name>)
The dolt_reflog() table function can be called with no arguments or with one argument. If called without any arguments, it will return the full reference log, which lists changes from newest to oldest for all tracked references. If called with one argument, that argument is the name of a ref to query. This can be the name of a branch (e.g. "myBranch") or the name of a tag (e.g. "v1.1.4") or it can be the fully qualified ref path (e.g. "refs/heads/myBranch"). The ref_name parameter is case-insensitive.
The dolt_reflog() table function can also be called with the --all flag to show all refs, including hidden refs, such as DoltHub workspace refs.
Schema
+-----------------------+-----------+
| field | type |
+-----------------------+-----------+
| ref | TEXT |
| ref_timestamp | TIMESTAMP |
| commit_hash | TEXT |
| commit_message | TEXT |
+-----------------------+-----------+
Example
The example below shows how to recreate a branch that was deleted by finding the last commit it referenced in Dolt's reflog.
-- Someone accidentally deletes the wrong branch!
select dolt_branch('-D', 'prodBranch');
-- After we realize the wrong branch has been deleted, we query the Dolt reflog on the same Dolt database instance
-- where the branch was deleted to see what commits the prodBranch branch has referenced. Using the same Dolt
-- instance is important, since reflog information is always local and not included when pushing/pulling databases.
select * from dolt_reflog('prodBranch');
+-----------------------+---------------------+----------------------------------+-------------------------------+
| ref | ref_timestamp | commit_hash | commit_message |
+-----------------------+---------------------+----------------------------------+-------------------------------+
| refs/heads/prodBranch | 2023-10-25 20:54:37 | v531ptpmv2tquig8v591tsjghtj84ksg | inserting row 42 |
| refs/heads/prodBranch | 2023-10-25 20:53:12 | rvt34lqrbtdr3dhnjchruu73lik4e398 | inserting row 100000 |
| refs/heads/prodBranch | 2023-10-25 20:53:06 | v531ptpmv2tquig8v591tsjghtj84ksg | inserting row 42 |
| refs/heads/prodBranch | 2023-10-25 20:52:43 | ihuj1l7fmqq37sjhtlrgpup5n76gfhju | inserting row 1 into table xy |
+-----------------------+---------------------+----------------------------------+-------------------------------+
-- The last commit prodBranch pointed to was v531ptpmv2tquig8v591tsjghtj84ksg, so to restore our branch, we
-- just need to create a branch with the same name, pointing to that last commit.
select dolt_branch('prodBranch', 'v531ptpmv2tquig8v591tsjghtj84ksg');
DOLT_SCHEMA_DIFF()
The DOLT_SCHEMA_DIFF() table function calculates the schema difference between any two commits in the database.
Each row in the result set describes how a table was altered between the two commits, including the table's create statement at to and from commits.
Note that the DOLT_SCHEMA_DIFF() table function currently requires that argument values be literal values.
Privileges
DOLT_SCHEMA_DIFF() table function requires SELECT privilege for all tables if no table is defined or
for the defined table only.
The DOLT_SCHEMA_DIFF() table function takes three arguments:
from_revision — the revision of the table data for the start of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main~", "WORKING", "STAGED").
to_revision — the revision of the table data for the end of the diff. This argument is required. This may be a commit, tag, branch name, or other revision specifier (e.g. "main~", "WORKING", "STAGED").
from_revision..to_revision — gets the two dot diff, or revision of table schema between the from_revision and to_revision. This is equivalent to dolt_schema_diff(<from_revision>, <to_revision>, [<tablename>]).
from_revision...to_revision — gets the three dot diff, or revision of table schema between the from_revision and to_revision, starting at the last common commit.
tablename — the name of the table to diff. This argument is optional. When it's not defined, all tables with schema diffs will be returned.
Schema
+-----------------------+------+
| field | type |
+-----------------------+------+
| from_table_name | TEXT |
| to_table_name | TEXT |
| from_create_statement | TEXT |
| to_create_statement | TEXT |
+-----------------------+------+
Example
For this example, we'll consider three tables within the context of two branches: main and feature_branch.
These are the tables on main: employees, inventory, vacations.
These are the tables on feature_branch: inventory, photos, trips.
To figure out how these tables changed, we run the following query:
SELECT * FROM DOLT_SCHEMA_DIFF('main', 'feature_branch')
The results from DOLT_SCHEMA_DIFF() show how the schema for all tables has changed going from tip of main to tip of feature_branch:
The first row has values in from_table_name and from_create_statement columns, while to_table_name and to_create_statement columns are empty. This means that between main and feature_branch, the table employees was deleted.
The second row has identical values for from_table_name and to_table_name, but from_create_statement is different from to_create_statement. This means the table's schema changed between main and feature_branch.
The third row is similar to the first row, except its to_* columns are empty, and from_* columns are set. This means that between main and feature_branch, the table photos was added.
Finally, the last row has mostly identical from_create_statement and to_create_statement columns, but different from_table_name and to_table_name columns. This means the table was renamed changed between main and feature_branch.
We invoked DOLT_SCHEMA_DIFF() with branch names, but we could have used any revision specifier. For example, we could have used commit hashes or tag names, and would have gotten the same results.
Using tags or commit hashes:
select * from dolt_schema_diff('v1', 'v1.1');
select * from dolt_schema_diff('tjj1kp2mnoad8crv6b94mh4a4jiq7ab2', 'v391rm7r0t4989sgomv0rpn9ue4ugo6g');
So far, we have always supplied just the first two parameters, the from and to revisions, but we have not specified the optional table parameter, so DOLT_SCHEMA_DIFF() returned schema diffs of all changed tables.
We can scope DOLT_SCHEMA_DIFF() to a specific table simply by specifying it as the last parameter.
Let's try this with the inventory table.
SELECT * FROM DOLT_SCHEMA_DIFF('main', 'feature_branch', 'inventory')
When a table is renamed, we can specify either the "old" table name, or the "new" table name, and we will receive the same results. The following two queries will provide the same results:
SELECT * FROM DOLT_SCHEMA_DIFF('main', 'feature_branch', 'trips');
SELECT * FROM DOLT_SCHEMA_DIFF('main', 'feature_branch', 'vacations');
Note the difference between this select and the previous dolt_schema_diff('main', 'feature_branch') invocation:
First row shows that the table photos was deleted
Second row show the creation of employees table
Third row has the from_create_statement and to_create_statement columns swapped
Fourth row shows the inverse rename of trips to vacations
Example query
DOLT_QUERY_DIFF()
The DOLT_QUERY_DIFF() table function calculates the data difference between any two queries, producing a table similar to the DOLT_DIFF() table function.
Privileges
DOLT_QUERY_DIFF() table function requires SELECT privilege for all tables used in each query.
Example
For this example, we have the table t in two branches main and other.
We can use the DOLT_QUERY_DIFF() table function to calculate the difference between the two tables:
dolt> select * from dolt_query_diff('select * from t as of main', 'select * from t as of other');
+--------+--------+------+------+-----------+
| from_i | from_j | to_i | to_j | diff_type |
+--------+--------+------+------+-----------+
| 1 | 10 | 1 | 1 | modified |
| NULL | NULL | 2 | 2 | added |
| 3 | 3 | NULL | NULL | deleted |
+--------+--------+------+------+-----------+
3 rows in set (0.00 sec)
Note
Query diff is performed brute force and thus, will be slow for large result sets.
The algorithm is super linear (n^2) on the size of the results sets.
Over time, we will optimize this to use features of the storage engine to improve performance.
Version control features that only inspect the state of the database are modeled as or instead.
To list branches, use the ,
instead of the DOLT_BRANCH() function.
To look up the current branch, use the , or the active_branch() SQL function, as shown in the
examples section below.
Branch names have a few restrictions which are similar to the constraints Git puts on branch names. Dolt's branches are a little more restrictive, as characters are required. Rules are as follows:
See the comments after the statements below for an example of this behavior, and also read
NOTE: When cloning from a file URL, you must currently include the .dolt/noms subdirectories. For
more details see the GitHub tracking issue,.
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 for
details.
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 for
details.
Permanently deletes any dropped databases that are being held in a temporary holding area. When a
Doltgres database is dropped, it is moved to a temporary holding area where the can restore it. The dolt_purge_dropped_databases() function clears this
holding area and permanently deletes any data from those databases. This action is not reversible,
so callers should be cautious about using it. The main benefit of using this function is to reclaim
disk space used by the temporary holding area. Because this is a destructive operation, callers must
have SUPER privileges in order to execute it.
Rewrites commit history for the current branch by replaying commits, allowing the commits to be
reordered, squashed, or dropped. The commits included in the rebase plan are the commits reachable
by the current branch, but NOT reachable from the branch specified as the argument when starting a
rebase (also known as the upstream branch). This is the same as Git and Dolt's , or
|upstreamBranch|..|currentBranch|.
Adds a remote for a database at given url, or removes an existing remote with its remote-tracking
branches and configuration settings. To list existing remotes, use the .
Creates a new tag that points at specified commit ref, or deletes an existing tag. To list existing
tags, use .
Restores a dropped database. See the for info on how to permanently remove dropped databases.
Verifies that working set changes (inserts, updates, and/or deletes) satisfy the
defined table constraints. If any constraints are violated they are written to the table.
-o, --output-only:
Disables writing results to the
system table.
The DOLT_HASHOF_DB() function returns the value hash of the entire versioned database. The hash is the hash of all tables
(schema and data) in the database, and includes additional versioned items such as stored procedures and triggers. The hash
does not include unversioned items such as tables which have been . The function
takes an optional argument to specify a branch or one of the values of 'STAGED', 'WORKING', or 'HEAD' (default no argument call
is equivalent to 'WORKING').
The DOLT_DIFF() table function calculates the differences in a table's data at any two commits in the database.
Each row in the result set describes how a row in the underlying table has changed between the two commits,
including the row's values at to and from commits and the type of change (i.e. added, modified, or removed).DOLT_DIFF() is an alternative to the.
You should generally prefer the system tables when possible, since they have less restrictions on use.
However, some use cases, such as viewing a table data diff containing schema changes or viewing the ,
can be easier to view with the DOLT_DIFF table function.
Learn more about two vs three dot diff .
If you'd like to get
(all commits reachable by revision2, but NOT reachable by revision1), you can
use .. between revisions (DOLT_LOG('revision1..revision2')) or ^ in front of
the revision you'd like to exclude (DOLT_LOG('revision2', '^revision1')). Note: if providing two
revisions, one must contain ^.
If you'd like to get
(all commits reachable by revision1 or revision2, excluding commits reachable by
BOTH revision1 AND revision2), you can use ... between revisions (DOLT_LOG('revision1...revision2')).
Learn more about two vs three dot log .
The data from Dolt's reflog comes from . This data is local to a Dolt database and never included when pushing, pulling, or cloning a Dolt database. This means when you clone a Dolt database, it will not have any reflog data until you perform operations that change what commit branches or tags reference.
Dolt's reflog is similar to , but there are a few differences:
You can try calling DOLT_SCHEMA_DIFF() against the , by getting the diff of schemas between schema_diff_v1 and schema_diff_v2 tags, which correspond to main and feature_branch branches from these examples.
