Announcing Janky Hosted DoltHub Databases
We all know that self-hosting a database is a challenging and daunting task. The hosting environment needs to have all the database's dependencies, must have the proper user/group permissions, and requires a slew of other nuanced, properly configured settings to prepare the database to run in production.
In recent years, Docker's come along and provided users a way to easily self-host a database by simply building and running an official image released regularly by the database's company. And let's be real, being able to docker pull ... then docker run ... is basically sliced bread 2.0.
For this reason, it wasn't surprising to hear rumblings from customers that wanted an official Docker image for Dolt to use for their DoltHub databases, since people seem to expect self-hosting a Dolt database to be as difficult and complex as self-hosting traditional SQL databases.
But the truth is, the incumbent SQL databases we all know to be difficult to self-host were developed around 14 million years ago, at a time when programmers needed to forage for dependencies deep in the forests of Pangea. But friends, a lot has changed since then.
The cool thing about Dolt is it's just a single binary that can run anywhere. And with Dolt's CLI it only takes a single command to start the database server.
However, we've created a new DoltHub feature we're calling "Janky Hosted," aimed at making this process even easier than running a database from the command line or with Docker!
Introducing ctrl c and ctrl v, or what we're calling "Copy and Paste."
Just kidding, I'm having some fun. "Janky Hosted" actually uses Docker too, but it is, in fact, a simple copy and paste workflow.
Announcing Janky Hosted DoltHub Databases
New on every DoltHub database is a "Deploy" tab where you can easily generate commands to copy and paste in your terminal to start running a database server and client connection.
Let's take a look at our most recently uploaded public database containing the US 2020 Census data.
As you can see from the image, there are only two fields you need to specify to get going. The required "Port" field tells the database server which port to listen on and the optional "Host Path" specifies a directory on your machine where you want the database persisted, so it isn't deleted when the server container stops.
Let's fill in both fields, and click "Download Dockerfile."
The generated dockerfile for this database is now in my "Downloads" folder. I could moved it somewhere else, but lazily, I'm going to leave it where it is.
Next, I open a terminal window and cd into my Downloads directory. I then create the directory I specified in the "Host Path" field so I can have access to the database when the server is off.
$ cd ~/Downloads
$ mkdir temp-dolthub-dbsAfter clicking "Download Dockerfile" on DoltHub, two sets of commands are generated for me to copy and paste. The first set allows me to build and start my US Census 2020 database server and the second set allows me to connect to it.
Since I already have a terminal open and rooted in /Users/dustin/Downloads where the generated dockerfile is, I'm gonna skip to Step 2 of the "Build and Start Server" steps and copy and paste the command into my terminal.
$ docker buildx build -t dolthub-census2020-sql-server -f dolthub-census2020.dockerfile . && docker run --mount type=bind,source=/Users/dustin/Downloads/temp-dolthub-dbs,target=/dolthub-dbs -p 42069:42069 dolthub-census2020-sql-server:latest
[+] Building 4.9s (13/13) FINISHED
=> [internal] load build definition from dolthub-census2020.dockerfile 0.0s
=> => transferring dockerfile: 3.58kB 0.0s
=> [internal] load .dockerignore 0.0s
=> => transferring context: 2B 0.0s
=> resolve image config for docker.io/docker/dockerfile:1.3-labs 2.1s
=> CACHED docker-image://docker.io/docker/dockerfile:1.3-labs@sha256:03ca0e50aa4b6e76365fa9a5607c3f988bc9284de6a82672 0.0s
=> [internal] load build definition from dolthub-census2020.dockerfile 0.0s
=> [internal] load .dockerignore 0.0s
=> [internal] load metadata for docker.io/library/ubuntu:20.04 0.6s
=> [1/6] FROM docker.io/library/ubuntu:20.04@sha256:9d6a8699fb5c9c39cf08a0871bd6219f0400981c570894cd8cbea30d3424a31f 0.0s
=> CACHED [2/6] RUN apt update && apt upgrade -y && apt install -y curl && curl -L https://github.com/dol 0.0s
=> [3/6] RUN cat <<EOF > /entrypoint.sh 1.3s
=> [4/6] RUN chmod +x /entrypoint.sh 0.3s
=> [5/6] WORKDIR /dolthub-dbs/dolthub 0.0s
=> exporting to image 0.1s
=> => exporting layers 0.0s
=> => writing image sha256:a9af9db80147d334a43d1b850148d2974b81b30cb2ebb2de86d990855de80360 0.0s
=> => naming to docker.io/library/dolthub-census2020-sql-server 0.0s
Checking Environment Variables...
Creating Directories...
Configuring Dolt Server...
Config successfully updated.
Config successfully updated.
Cloning database...
cloning https://doltremoteapi.dolthub.com/dolthub/census2020
2,308,027 of 2,308,027 chunks complete. 0 chunks being downloaded currently.
+--------------------+
| Database |
+--------------------+
| census2020 |
| information_schema |
+--------------------+
Updating database permissions...
*****
Client must run 'USE census2020;' to start accessing the data.
*****
Starting server with Config HP="0.0.0.0:42069"|U="root"|P=""|T="28800000"|R="false"|L="trace"
After running the pasted command, some Docker stuff ensues, the database is cloned, and most importantly at the end, the server is running!
Connecting to the server is also just as simple. I open a second terminal window, Step 1, of the "Connect to Server" section, then copy and paste the command from Step 2:
$ dolt sql-client --host=0.0.0.0 --port=42069
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql>Just like that, we're connected.
As with other SQL clients, it's important to USE the database first, which is Step 3 of "Connect to Server"...
mysql> USE census2020;After that, I can start living my best SQL life. In this example, that means listing populations by state:
mysql> SELECT census.stusab, P0010001 as pop
-> FROM census join (
-> SELECT stusab, sumlev, logrecno
-> FROM geo
-> WHERE sumlev = '040') as x
-> ON x.logrecno = census.logrecno AND x.stusab = census.stusab
-> ORDER BY pop desc;
+--------+----------+
| STUSAB | pop |
+--------+----------+
| CA | 39538223 |
| TX | 29145505 |
| FL | 21538187 |
| NY | 20201249 |
| PA | 13002700 |
| IL | 12812508 |
| OH | 11799448 |
| GA | 10711908 |
| NC | 10439388 |
| MI | 10077331 |
| NJ | 9288994 |
| VA | 8631393 |
| WA | 7705281 |
| AZ | 7151502 |
| MA | 7029917 |
| TN | 6910840 |
| IN | 6785528 |
| MD | 6177224 |
| MO | 6154913 |
| WI | 5893718 |
| CO | 5773714 |
| MN | 5706494 |
| SC | 5118425 |
| AL | 5024279 |
| LA | 4657757 |
| KY | 4505836 |
| OR | 4237256 |
| OK | 3959353 |
| CT | 3605944 |
| PR | 3285874 |
| UT | 3271616 |
| IA | 3190369 |
| NV | 3104614 |
| AR | 3011524 |
| MS | 2961279 |
| KS | 2937880 |
| NM | 2117522 |
| NE | 1961504 |
| ID | 1839106 |
| WV | 1793716 |
| HI | 1455271 |
| NH | 1377529 |
| ME | 1362359 |
| RI | 1097379 |
| MT | 1084225 |
| DE | 989948 |
| SD | 886667 |
| ND | 779094 |
| AK | 733391 |
| DC | 689545 |
| VT | 643077 |
| WY | 576851 |
+--------+----------+After I kill the client connection and running server, the database will be located at the "Host Path" I specified. From there, I can push, pull, fetch—work with the database as I normally would.
$ cd temp-dolthub-dbs/dolthub/census2020
$ dolt status
On branch master
nothing to commit, working tree clean
Conclusion
"Janky Hosted" is our quick and dirty first pass at providing customers with a hosted Dolt solution, but rest assured, non-janky iterations of our hosted products are already underway.
We are very excited about our upcoming product offerings available to DoltHub Pro and DoltHub Enterprise clients, which deliver all of Dolt's unique diffing, branching, and merging features as a fully hosted RDBMS cloud service.
DoltHub is also hiring! We want the builders of the future who are ready to disrupt the database industry and version the world's open data. We have a range of positions available, so if you're interested in working with us, let us know!
Curious about Dolt, DoltHub and the versioned database of the future? There's no better place to get started than DoltHub.com where you can download Dolt, host your own public and private Dolt databases, or janky deploy any public database!
Questions, comments, or looking to start backing your application with a Dolt database? Get in touch with our team here or join us on Discord!