In the SQL universe, there are many tools which people use to interact with their data. There are applications like DBeaver which is a full GUI Application, or Dolt's WorkBench which uses a browser for its GUI components. On the other end of the spectrum there are command line shells, usually directly related to the DB your are connecting to. There is a MySQL shell, Postgres shell, and so forth. Dolt is slightly unusual because you can use the MySQL shell with it, or use the Dolt shell: dolt sql. There is no one-size-fits-all for interacting with your data, and each one differentiates itself in its own way.

dolt sql is no different. Today we are announcing a new feature to the dolt sql shell which enables the use of common Dolt CLI operations in the shell itself. Let's Go!

Dolt CLI is like Git

If you are unfamiliar with Dolt, it's important to know that there are more than a dozen commands which can be used much in the same way that Git is used. Go ahead and install it, then run dolt --help for a list. Almost a year ago I discussed how the Dolt CLI's history was early in the days of this experiment. To recap a little bit from that post, originally the CLI was the only way to interact with a Dolt database:

A big chunk of our docs cover all of these commands, and they generally look a lot like Git. (all examples performed against the SHAQ Database)

SQL Takes Prominence

The customer feedback we heard early on about Dolt was that tabular data is best manipulated using SQL, so Dolt was altered and updated until you could perform all Dolt specific functionality using SQL stored procedures and system tables. SQL became the preferred way to interact with Dolt. All Dolt capabilities can be accessed with SQL, such as in the following where we do the equivalent of dolt log -n 2:

It has much of the same information, but is forced to stay in the tabular output form familiar to SQL junkies.

Unfortunately, the CLI was kind of an orphan for a time, and the CLI operations expected they had direct access to data files:

Work done last summer fixed this discrepancy, and allowed us to have the CLI and SQL paradigms play well together.

While that work addressed the data access race conditions, reduced duplicate code, and ensured that we ate our own dog food on the SQL interface, it didn't actually change the user experience at all aside from doing the correct thing when a local server was running. That's all fine and good, but falls in the bucket of "Our Software Should Work as Advertised." Despite all that work to get our house in order, there are two distinctly separate experiences: Dolt via SQL vs Dolt via CLI.

Why Not Have Both?

I like the CLI look and feel. When I look at the two screen shots above, it's immediately obvious which one I want to look at. Doing things like dolt diff feel very familiar to me as a veteran Git user. Unfortunately, I also need to run SQL statements frequently, so I spend a lot of time in the dolt sql shell, and that doesn't play well with the CLI because each has their own session against the DB. That was the case until the recent release of Dolt 1.39.3; we live in a wondrous new world now! With the latest release of Dolt, dolt sql shell can do the following:

As stated above, the CLI commands are executed against the Dolt SQL interface. Given that all Dolt commands and database code reside in the one binary we vend, it's actually possible to leverage the CLI commands in the SQL Shell with a fairly small amount of work.

Thanks to the work to standardize the CLI on SQL, you can now execute 10 CLI commands directly from the dolt sql shell!

How to Use Slash Commands

The first hint that something is different in the shell is the mention of a /help; command when it starts. Previously there was no special behavior for executing commands starting with /. Now there is. What /help; shows:

There you can see the list of commands which are supported in the dolt sql shell. It's worth calling out that these commands are the same as the CLI commands, they are not a reimplemented or pared down version of each command. You can see this if you attempt to look at the help for a command:

For the careful observer, you'll see that output is identical to running dolt status --help. The same goes for all of the other supported commands. Furthermore, the arguments passed to slash commands are interpreted in exactly the same way as on the CLI. For example, if you want to look at the diff for the last two commits: /diff HEAD~2..HEAD is going to be a lot easier than the SQL equivalent. For example, the --stat argument is used here to see the statistics of the diff between two points in history:

The nice thing about having the shell and CLI operations share the same session is that you can go back and forth between them and not lose your workspace. For example:

Under the Hood

The PR to enable each of these commands was pretty minor. This results from the level of abstraction that the CLI is implemented in. All CLI commands use a generic interface called the Queryist

// Queryist is generic interface for executing queries. Commands will be provided a Queryist to perform any work using
// SQL. The Queryist can be obtained from the CliContext passed into the Exec method by calling the QueryEngine method.
type Queryist interface {
	Query(ctx *sql.Context, query string) (sql.Schema, sql.RowIter, error)
}

This interface is used to execute every read and write operations against the Dolt database in the CLI. By having this common interface, we can swap out different implementations depending on the context we are running in. If you have no server running, the Queryist used bypasses the wire protocol and uses the Dolt SQLEngine directly to execute queries. If instead you have a local server running, the Queryist used will authenticate you to the server. The point is that the CLI commands themselves don't need to know what SQL backend they are talking to.

Each command implements the Command interface, in particular the Exec command takes an argument which is the cliContext. This contains the Queryist to be used to run the command. Given that, it's simple for one command to call Exec of another command. One of the pieces of this work was to ensure we could chain uses of the cliContext and get the correct behavior. Once that was sorted out, it was simply a matter of making dolt sql have a syntax so that we could call out to the existing commands.

What's Next?

As you can see from the list of commands in the /help; message, not all commands are supported. We'd like to hear from you about which command you'd like next and how you leverage this feature. Notable absences from the current list are pull and push, so if you want them, let us know! Jump on our Discord to tell us about how you use Dolt!