Making Dolt Compatible With SQL Editors

3 min read

Dolt is a SQL database with Git-style versioning. We've been working hard to make Dolt fully compatible with MySQL. An important test for compatibility is support for MySQL editors.

These editors provide user interfaces for you to inspect data and write queries. Some of the editors we support include:

Demo

We're going to go through the process of connecting dolt SQL Server (Dolt's inbuilt SQL server) with Tableplus, a popular SQL editor. Tableplus is free to use with a freemium model. Go ahead and download Tableplus and clone this repo from Dolthub.

$ dolt clone dolthub/us-president-precinct-results
cloning https://doltremoteapi.dolthub.com/dolthub/us-president-precinct-results
505,141 of 505,141 chunks complete. 0 chunks being downloaded currently.
$ cd us-president-precinct-results

To work with SQL Editors we need to define a config file that specifies certain Dolt behavior. Let's look at the config files config.yaml below. Save it in the same directory as the repo.

log_level: trace 

behavior:
    read_only: false
    autocommit: true

user:
    name: root
    password: ""

listener:
    host: localhost
    port: 3306
    max_connections: 10
    read_timeout_millis: 1000000
    write_timeout_millis: 1000000

The log_level indicates the type of logging dolt's sql-server will do. In this case we'll log at level trace meaning will print each query and its output. This will be pretty valuable for the Dolt team as our compatibility with these editors is still a little early. So if any query doesn't work you can send it to us to fix.

In the behavior section we can see that we've set read_only to false indicating that we can write queries to that affect that database state. We also turned autocommit on meaning that each query can execute insert and update statements.

Finally, in the listener section set host, port, the number of concurrent connections allowed as well as some timeouts. The important parameter here is max_connection parameter. Certain editors will connect with multiple connections so if we leave this at its default value (1) we'll see hanging from the application side.

Let's start our server as follows:

$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3306"|U="root"|P=""|T="1000000"|R="false"|L="trace"

Opening up Tableplus and click the "Create a New Connection Option" and select the MySQL Option. You should see a similar popup:

TablePlus Popup

Set the name to Dolt Server and put the host to 127.0.0.1 and port to 3306 as per the config. Set the user to root and leave the password empty. Finally set SSL mode to DISABLED. Dolt does not currently supoort SSL.

TablePlus Filled

Hit connect and your editor is now all setup with Dolt. If you select the circled button below you should see a view like this.

TablePlus FinalView .

You can go the Tableplus website to see more features.

Conclusion

In this blog we showed how to connect TablePlus to Dolt SQL Server. Our long term goal is full MySQL dialect and wire protocol compatibility which will mean Dolt can fit anywhere MySQL can, enabling the broadest possible set of use-cases across data science, data engineering, and applications.

For a full list of compatible editors see here. Please feel free to reach out if you want additional support for an editor not listed in this document in our discord channel.

SHARE

JOIN THE DATA EVOLUTION

Get started with Dolt