The Dolt Workbench is an open-source SQL workbench supporting MySQL, Postgres, Dolt, and Doltgres databases. The easiest way to get started with the workbench is to download the desktop app, which is available on Mac and Windows app stores. In this article, we'll discuss an alternate use case for the workbench involving Docker, which you can use to run a hosted instance of the workbench for your team.

This isn't just theory, one of our long-standing customers, Scorewarrior, asked for this. Scorewarrior has people who must edit a single cell in their Dolt database every six months or so. Downloading a new app to do that is a bit cumbersome. A central web interface is simple and easy.

How It Works

Since the workbench is a Next.js application written in TypeScript, it's naturally browser-compatible. If you're curious about how we converted a Next.js web application into a desktop app, check out this blog. We keep an up-to-date Docker image available on Docker Hub that you can use to spin up a browser version of the workbench. If you want to run a hosted workbench, all you have to do is deploy this Docker image.

Dolt Workbench on LAN

To illustrate this, I'll run a Docker container on my machine built from the workbench image and expose it to my local network. I'll start by pulling the image from Docker Hub.

docker pull dolthub/dolt-workbench:latest

The workbench is composed of two main pieces—a Next.js UI and a GraphQL API layer. These are two distinct processes that need to run on different ports. Additionally, if the GraphQL server is exposed anywhere other than localhost, you'll need to specify the address with the GRAPHQLAPI_URL environment variable. This command will run the container and make the workbench accessible to anyone on my LAN:

LAN_IP=$(ipconfig getifaddr en0)

docker run \
  -p ${LAN_IP}:3000:3000 \
  -p ${LAN_IP}:9002:9002 \
  -e GRAPHQLAPI_URL="http://${LAN_IP}:9002/graphql" \
  dolthub/dolt-workbench:latest

And that's it! Now anyone on my network can collaborate on the same workbench instance. To prove it, here's the workbench running on my iPhone:

More Docker Options

One of the features of the workbench is that it allows you to persist connection metadata between application runs. With the desktop app, this information lives on the host machine's disk. If you're running a Docker container, you'll need to specify where to put it. You can either mount a directory from the host machine to the /app/graphql/store directory in the container, or use a MySQL-compatible database to house the connection information. Make sure to check out the Dolt Workbench page on Docker Hub for more Docker-specific configuration options.

What About Authentication?

For those unfamiliar with Dolt, the idea of giving your team unfettered access to a shared SQL workbench connected to your production database might raise some eyebrows. Luckily, Dolt is the world's first version controlled SQL database, which makes it quite difficult for that rogue teammate to wreak havoc on your production data.

Still, it would be useful to have some minimal authentication system in place if for no other reason than to populate the --author field when the current user makes a Dolt commit. Let's walk through a simple example of what that might look like.

Reverse Proxy Authentication

Since the workbench runs in the browser, it can accept custom headers on any HTTP request it receives. The X-Forwarded-User and X-Forwarded-Email headers can be used to pass "full name" and "email" fields, which the workbench will use internally to populate author information when new commits are created. To make this work, I'll stand up a reverse proxy in front of the shared workbench example from earlier that will authenticate the user attempting to access the workbench and forward along the appropriate name and email headers.

For authentication, I'm using htpasswd, a simple command-line utility for managing user files. I'll start by creating a user in a file called .htpasswd.

htpasswd -c .htpasswd eric
New password:
Re-type new password:
Adding password for user eric

Next, I'll spin up a workbench container as well as an NGINX reverse proxy server using the following docker-compose.yml:

services:
  workbench:
    image: docker.io/dolthub/dolt-workbench:latest
    expose:
      - "3000:3000"
      - "9002:9002"

    environment:
      GRAPHQLAPI_URL: "/graphql"

  proxy:
    image: nginx:latest
    ports:
      - "${LAN_IP}:3000:80"
    volumes:
      - ./nginx.conf:/etc/nginx/nginx.conf:ro
      - ./.htpasswd:/etc/nginx/.htpasswd:ro
    depends_on:
      - workbench

Finally, I'll need an NGINX configuration file to implement basic authentication and forward requests to the workbench container:

events {}

http {
  map $remote_user $fw_user {
    default $remote_user;
    eric    "Eric Richardson";
  }
  map $remote_user $fw_email {
    default "$remote_user@dolthub.com";
    eric    "eric@dolthub.com";
  }
  server {
    listen 80;
    server_name _;

    auth_basic "Restricted Access";
    auth_basic_user_file /etc/nginx/.htpasswd;

    location / {
      proxy_pass http://workbench:3000;
      proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
      proxy_set_header Host $host;
      proxy_set_header X-Forwarded-Proto $scheme;
      proxy_set_header X-Forwarded-User $fw_user;
      proxy_set_header X-Forwarded-Email $fw_email;
    }

    location /graphql {
      proxy_pass http://workbench:9002/graphql;
      proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
      proxy_set_header Host $host;
      proxy_set_header X-Forwarded-Proto $scheme;
      proxy_set_header X-Forwarded-User  $fw_user;
      proxy_set_header X-Forwarded-Email $fw_email@dolthub.com;
    }
  }
}

This configuration requires that the user attempting to access the workbench server is authenticated through the .htpasswd scheme. It also defines a mapping between the authenticated user's username and the fields passed as headers to the workbench ($fw_user and fw_email). Now, when I run the container with docker compose up, I'm met with a popup asking for authentication.

After entering my credentials, I'm able to access the workbench. When I go to create a new commit, the following checkbox should now be checked:

After making the commit, the author information is correctly populated:

Conclusion

It's not so difficult to run a hosted version of the workbench! In fact, anywhere you can deploy a Docker image, you should be able to deploy the workbench. If you don't want to roll your own hosted workbench, you should check out Hosted Dolt, a fully managed, cloud-hosted Dolt database that comes with a workbench built in.

If you're interested in trying this out for your team or if you run into any issues with the setup, reach out to us on Discord and we'll help you out.