Dolt is a SQL Database which allows for the full branching and merging of tabular data. Being SQL based, we can interoperate with many existing tools and platforms which exist and are familiar to users.

There are many ways to interact with SQL servers. There are workbenches which allow you to both investigate a databases and alter them. ORMs are yet another way to interoperate with SQL Servers.

Another class of Database client which I personally really appreciate is - Command Line Shells. PostgreSQL has one. MySQL has one. There are two primary benefits of such tools:

• They are operational life savers. When it comes to operators needing to connect directly to a database remotely in difficult network topologies, sometimes all you have is a tool which runs on a command line. After years of personally operating databases and servers in production, I habitually depend on the lowest common denominator - which is a text terminal. It's a good tool to have in your toolbox if you are in the DevOps space!
• Database specific shells can enable Database specific experiences. This includes easy shorthands for common operations which may only make sense in the context of that database.

Today we'll discuss some of the features the Dolt SQL Shell enables for users who work with Dolt Databases. The shell has gotten some new tricks over the last few months, and I thought I'd highlight some of its nifty features!

Connecting

As stated above, the ease of connecting to a Dolt Database with the Shell is really important. This ranges from making it easy for a new user to hit the ground running to an operator needing to connect to a remote database for an emergency update.

The absolute simplest way to start using Dolt is the following:

lcl:~/demo$ dolt init
Successfully initialized dolt data repository.
lcl:~/demo$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit. "\help" for help.
demo/main>

And you're up and running. Every feature discussed today will work in this context.

Furthermore, if there is a server running, the shell will connect seamlessly without you even knowing it:

lcl:~/demo$ dolt sql-server
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"|S="/tmp/mysql.sock"
INFO[0000] Server ready. Accepting connections.

In another shell, but in the same directory:

lcl:~/demo$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit. "\help" for help.
demo/main>

Finally, you can connect to remote servers using the --host and --user options. This is a little more involved, but not much. Using profiles you can configure your environment to have a default host you always connect to. If you're a Hosted Dolt user, this is a great option.

Dolt Specific Features

There are some things right out of the gate that you expect from any credible SQL shell. You can perform multi-line queries and see their results. You can use arrow keys to scroll back through your history of queries. Control operations (^L) clears screen - things like that. If there are common features you expect from a shell that dolt sql doesn't support, please chime in on Discord!

The Dolt specific features are more interesting, and I'll highlight a few here.

The Prompt

When working with a version controlled database, it's critical to know what branch you are on, and if your workspace is dirty.

Git users have long had nifty configurable prompts which tell you things like how many commits ahead of your tracking branch you are. If there are such pieces of information you'd like to see in your prompt, ask Us!

\diff and Other Git CLI like Behaviors

The Prompt is a signal that you have changes that aren't committed. Several of Dolt's command line operations can be invoked with '\' prefixes. For example:

You can see which operations are supported by requesting \help, and you can see that most version control features are supported in the shell.

Available commands:
      status - Show the working tree status.
        diff - Diff a table.
         log - Show commit logs.
        show - Show information about a specific commit.
         add - Add table changes to the list of staged table changes.
      commit - Record changes to the repository.
    checkout - Checkout a branch or overwrite a table from HEAD.
       reset - Remove table changes from the list of staged table changes.
      branch - Create, list, edit, delete branches.
       merge - Merge a branch.
        help - What you see right now.
        edit - Use $EDITOR to edit the last command.

Using these tools in combination with Dolt SQL interfaces, you have lots of flexibility in how you work with your database.

These commands will get you started. There are notable missing commands though. revert and rebase jump out at me. Which should we build next? Let us know!.

Introducing \edit

dolt sql continues to get usability improvements! We recently added the ability to edit your last command in your editor of choice. This isn't a Dolt specific feature, as it was cribbed from the MySQL shell. The feature is particularly nice if you are working with a multi-line query that you may have copied and pasted from elsewhere. Using the EDITOR environment variable, an editor will open to allow you to edit the last command you ran (vim is the default). After you terminate the editor, the query is run.

See it here in action.

The situation where you need to copy and paste a complex query into a shell seems pretty common. It comes up more frequently than we'd like to admit. It could be a monthly report you regularly run, or a response needing to roll back to another state in time as part of an emergency. I copied a nonsense query from ChatGPT above, then ran the \edit command to tailor it.

Feedback Welcome

The dolt sql shell is pretty easy to tweak and improve. I've hinted at several areas where we could improve (cough tab completion), and several things we could envision like table editors and table paging support. Send feedback (or PRs!) our way. The fastest way to start a conversation about Version Controlled Databases and the ways you want to work with them is on our Discord!