TL;DR

Git versions files and Dolt versions data. The diff and log commands in Git and Dolt are useful tools to view what has changed between different revisions. You can control what changes you want to include when listing logs or viewing diffs by including two or three dots between revisions (i.e. git log A..B or git diff A...B).

The differences between two dot and three dot log and diff aren't the most intuitive, and two and three dot mean the opposite things for diff and log. Here's a visual summary of what using dots mean for diff and log:

Check out this article for a great quick overview explaining these differences.

We recently added support for two and three dot diff and log for Dolt, and this blog will go more in depth about the differences between the two and how to use them in Dolt.

• Introduction
• What are logs and diffs in Git and Dolt?
• What's with the dots?
  • Two dot log
  • Three dot log
  • Two dot diff
  • Three dot diff

Introduction

Dolt is a version controlled SQL database. In Dolt both your data and schema are managed with a Git-like commit graph, which allows you to do things that you can't do with any other database, like branch, commit, merge, diff, etc.

Hosted Dolt is a place where you can get a fully-managed cloud-hosted Dolt database. We recently released pull requests for Hosted's SQL Workbench, a DoltHub-like UI that you can run against your hosted Dolt database. Unlike DoltHub, which gets database metadata through a deployed remote Dolt server, the Hosted workbench gets database metadata directly from the running SQL server. We use Dolt system tables to display the same data you see on DoltHub, such as commit logs and table diffs.

Pull requests need a specific kind of log and diff so that you only see changes on the new branch since it was checked out from the base branch, excluding new changes to the base branch (as opposed to both changes on the new branch and new changes on the base branch). We can achieve this for pull requests by using two dot log and three dot diff.

Yes, you read that correctly. Two dot and three dot mean different, almost opposite things for diff and log. Why? There's been some speculation, and it seems like it may have just been a mistake in design. There's a lot of information out there about how they are different, but my B- Googling did not lead me to any meaningful information about why.

We recently added full support for two and three dot log and diff to the Dolt CLI and DOLT_LOG and DOLT_DIFF SQL table functions. This blog will go more in depth about how two and three dot diff and log work in Dolt.

We'll use this commit graph, where there is a feature branch feature checked out from a base branch main, as an example:

What are logs and diffs?

If you're new to Git or Dolt, this section will give a basic overview of the log and diff commands. If you just want to know what's up with the dots, you can skip to that section.

What are logs?

The log command in Git and Dolt outputs a list of commits reachable from the given revision(s). You can exclude commits that are reachable from revision(s) with a ^ in front of them. Providing no revisions outputs the commit list reachable from the current branch HEAD. The commit list is in reverse chronological order by default.

This is what log looks like in Git, for two commits on the current main branch:

$ git log
commit 345561df1cd4d10473cc9f6897b82d0d6d9e2353 (HEAD -> main)
Author: Taylor Bantle <taylor@dolthub.com>
Date:   Mon Oct 31 14:42:54 2022 -0700

    first commit on main

commit 924f3a5fe870f13c3663a2b3680f631038005110
Author: Taylor Bantle <taylor@dolthub.com>
Date:   Mon Oct 31 14:42:15 2022 -0700

    Initial commit

This looks almost exactly the same using the dolt log CLI:

$ dolt log
commit e9hj7gsgq3u5phuladch2b0gvqdutiu4 (HEAD -> main)
Author: Taylor Bantle <taylor@dolthub.com>
Date:  Fri Nov 13 11:53:19 -0800 2020

        first commit on main

commit vun472c26i68kqhqrbpnjlk96tpp456a
Author: Taylor Bantle <taylor@dolthub.com>
Date:  Fri Nov 13 11:51:50 -0800 2020

        Initialize data repository

Since Dolt is a SQL database, we also expose version control features like log via system tables and table functions. This is the what the output of the dolt_log system table looks like (which is equivalent to the output of the dolt_log() table function if no arguments are provided):

test_db> SELECT * FROM dolt_log;
+----------------------------------+---------------+--------------------+-------------------------+----------------------------+
| commit_hash                      | committer     | email              | date                    | message                    |
+----------------------------------+---------------+--------------------+-------------------------+----------------------------+
| e9hj7gsgq3u5phuladch2b0gvqdutiu4 | Taylor Bantle | taylor@dolthub.com | 2020-11-13 19:53:19.259 | first commit on main       |
| vun472c26i68kqhqrbpnjlk96tpp456a | Taylor Bantle | taylor@dolthub.com | 2020-11-13 19:51:50.076 | Initialize data repository |
+----------------------------------+---------------+--------------------+-------------------------+----------------------------+

Here's a visual representation of running the above log commands on branch main:

What are diffs?

The diff command in Git and Dolt shows changes between revisions. In Git, this means showing the differences between files, whereas in Dolt this means showing the differences between database tables and schemas.

Running git diff HEAD feature on current branch main will show all changes from the HEAD of feature to the HEAD of main.

This is what diff looks like in Git, for a changed line in a file:

$ git diff HEAD feature
diff --git a/DiffTable/index.tsx b/DiffTable/index.tsx
index 91d1cd70a1..45b04fe30b 100644
--- a/DiffTable/index.tsx
+++ b/DiffTable/index.tsx
@@ -35,7 +35,7 @@ export function Inner({
 }: InnerProps) {
   const [hiddenColIndexes, setHiddenColIndexes] = useState<number[]>([]);

-  const status = tableStatus(tableDiff.schemaPatch);
+  const status = tableStatus(tableDiff.schemaPatch); // this line has changed
   const hasDataChanges = !!tableDiff.rowDiffs.list.length;
   const addedOrDropped =
     activeTableName.includes("(added)") ||
 }

Since Dolt versions database tables instead of files, the output of the diff command looks a little different from Git. This is what dolt diff looks like for a modified and added row:

$ dolt diff HEAD feature
diff --dolt a/table b/table
--- a/table @ taaghpg58k7s72fn34gcuqlmqc9gt2h1
+++ b/table @ q2k78bdlupigfkgvv56jqod3526qulae
+---+----+---------+
|   | id | name    |
+---+----+---------+
| < | 2  | Git     |
| > | 2  | Dolt    |
| + | 3  | DoltHub |
+---+----+---------+

Similar to log, we also have a system table and table function for getting diffs through SQL. A table name is required for these options. This is what the output of the dolt_commit_diff_$TABLENAME looks like (which is equivalent to the output of the dolt_diff() table function output using the same arguments):

test_db> SELECT * FROM dolt_commit_diff_table WHERE from_commit=HASHOF('feature') AND to_commit=HASHOF('HEAD');
+---------+-------+----------------------------------+-------------------------+-----------+---------+----------------------------------+-------------------------+-----------+
| to_name | to_id | to_commit                        | to_commit_date          | from_name | from_id | from_commit                      | from_commit_date        | diff_type |
+---------+-------+----------------------------------+-------------------------+-----------+---------+----------------------------------+-------------------------+-----------+
| Dolt    | 2     | bsgqppo9bcv51239aqta29ttpj1bhc4r | 2022-10-17 23:42:25.544 | Git       | 2       | e9hj7gsgq3u5phuladch2b0gvqdutiu4 | 2020-11-13 19:53:19.259 | modified  |
| DoltHub | 3     | bsgqppo9bcv51239aqta29ttpj1bhc4r | 2022-10-17 23:42:25.544 | NULL      | NULL    | e9hj7gsgq3u5phuladch2b0gvqdutiu4 | 2020-11-13 19:53:19.259 | added     |
+---------+-------+----------------------------------+-------------------------+-----------+---------+----------------------------------+-------------------------+-----------+
3 rows in set (0.00 sec)

Here's a visual representation of running the above diff commands on branch main:

What's with the dots?

In Git you can use two and three dots between revisions to control the changes you want to see. This notation isn't the most intuitive, and means the opposite things for diff and log. We recently added support for two and three dot diff and log to the Dolt CLI and SQL functions. This section will go through what each of the dots mean for diff and log, as well as how to use both in Dolt.

Two dot log

Two dot log lists commit logs for revision A, while excluding commits from revision B. Two dot log is used for listing commits in pull requests. When reviewing a pull request, you want to see a summary of changes introduced by the new feature branch, not all changes from the feature branch AND new changes on the base branch since the feature branch was checked out.

There are a few ways to get two dot logs between branches main and feature using the Dolt CLI (which is equivalent to the Git CLI):

$ dolt log main..feature
$ dolt log feature --not main
$ dolt log ^main feature

These also translate to the DOLT_LOG table function:

test_db> SELECT * FROM DOLT_LOG('main..feature');
test_db> SELECT * FROM DOLT_LOG('feature', '--not', 'main');
test_db> SELECT * FROM DOLT_LOG('^main', 'feature');

For the commit graph above, dolt log main..feature will return a list of commits unique to the feature branch in reverse chronological order: E, D, and C.

Three dot log

Three dot log lists commit logs for revision A OR revision B, while excluding commits from BOTH revision A and revision B. This is useful for merges.

There are a few ways to get three dot logs between branches main and feature using the Dolt CLI (which is equivalent to the Git CLI):

$ dolt log main...feature
$ dolt log main feature --not $(dolt merge-base main feature)

This also translates to the DOLT_LOG table function:

test_db> SELECT * FROM DOLT_LOG('main...feature');

For the commit graph above, dolt log main...feature will return a list of commits on branches main and feature, excluding common ancestor commits (A and B), in reverse chronological order: E, D, C, H, G, and F.

Two dot diff

Two dot diff shows the differences between revisions A and B. This is the same as using dolt diff with no dots (i.e. dolt diff A B). This is useful for viewing changes between two arbitrary revisions.

There are a few ways to get two dot diffs between revisions main and feature using the Dolt CLI (which is equivalent to the Git CLI):

$ dolt diff main..feature
$ dolt diff main feature

These also translate to the DOLT_DIFF table function:

test_db> SELECT * FROM DOLT_DIFF('main..feature', 'table');
test_db> SELECT * FROM DOLT_DIFF('main', 'feature', 'table');

You can also use the dolt_commit_diff_$TABLENAME system table to get two dot diffs. The equivalent query would be:

test_db> SELECT * FROM dolt_commit_diff_table WHERE from_commit=HASHOF('feature') AND to_commit=HASHOF('main');

For the commit graph above, dolt diff main..feature will return the differences from commit H (HEAD of branch main) to commit F (HEAD of branch feature), which includes effects from commits G and F.

Three dot diff

Three dot diff shows changes between revisions A and revision B starting at the last common commit. This is useful for viewing pull request diffs.

Pull requests are intended for human review of changes. In a pull request you only want to review the changes initiated by a new branch, not all the changes between now and when the branch was checked out. Three dot diff excludes changes on other branches and isolates the changes being reviewed to only changes made on this branch.

There are a few ways to get three dot diffs between revisions main and feature using the Dolt CLI (which is equivalent to the Git CLI):

$ dolt diff main...feature
$ dolt diff $(dolt merge-base main feature) feature

These also translate to the DOLT_DIFF table function:

test_db> SELECT * FROM DOLT_DIFF('main...feature', 'table');

test_db> SET @MergeBase = DOLT_MERGE_BASE('main', 'feature');
test_db> SELECT * FROM DOLT_DIFF(@MergeBase, 'feature', 'table');

For the commit graph above, dolt diff main..feature will return the differences from branch feature (commit E) to the merge base of branches main and feature (commit B), which does NOT include any new changes to branch main (commits F through H).

Conclusion

While using two and three dots in revisions for diff and log can be confusing, they're useful for controlling what and how changes are shown in Git and Dolt. This is especially relevant for pull requests, which we recently added to the SQL Workbench for Hosted Dolt and uses the DOLT_DIFF and DOLT_LOG table functions to get three dot diffs and two dot logs.

If you have any questions or feature requests regarding log or diff, come talk to us on Discord or file an issue on GitHub.