I've always enjoyed working on hardware projects. Some past projects include a dual analog controller that worked with my iPhone, a "Make it Rain" machine that threw out a real dollar bill every time you swiped on your phone, and a pixel art painting robot. However, the one project that I have always wanted to build was a robot bartender. In this blog I'm going to cover how I installed Dolt on a Raspberry Pi to serve cocktails sourced from an open repository of cocktails on dolthub.com.

Backstory

I dipped my toe in the world of cocktail robotics more than 15 years ago. My first design was a gravity fed cocktail mixing machine which used solenoid valves to dispense liquids, and a Palm tablet for the interface. I didn't get past the planning stage at that time, but in 2011 I made a more serious attempt. I bought the components, and began building a prototype. I used an Android tablet and interfaced with the solenoid valves using the IOIO for Android and some relays. It kinda worked in that I was able to open and close valves and dispense some liquids but there were a lot of problems:

• Accuracy - In order to be useful, I needed to be able to dispense a reasonably accurate amount of each ingredient in order to get all the proportions correct. The gravity fed solenoid valves worked, but as the amount of liquid in each container decreased, the rate at which the liquids flowed varied. I tried accounting for that using various techniques, but between that and the challenge of different viscosity I never got great results.
• Cocktail Recipes - In order to be great, the robot needs to know how to make a lot of different recipes. I created a json file which contained cocktail recipes, but it was a pain. Manually compiling a list of cocktail recipes and getting them into this file was time-consuming, and error prone.
• Configuration - The robot needs to be configured with information about which of the IOIO's pins dispense from which container, and it also needs to know which ingredient is in which container.
• Determining What can be Made - Once you know all the recipes, and the ingredients available, you need to determine which drinks the robot can make currently. This was complicated, and hard to read.

Due to all these shortcomings, I scrapped the project within a few months of starting.

The Prototype

When I discovered how inexpensive peristaltic pumps had become, I immediately began working on a new design, and I found others who had made cocktail robots. The hardware portion of the robot is pretty straightforward. A Raspberry Pi acts as the brains. 6 of its GPIO pins are connected to an relay module, and six of the relays are connected to pumps. When the Raspberry Pi sets a pin high then a pump turns on, when it sets the pin low the pump turns off.

Using peristaltic pumps took care of my accuracy problems, and all the remaining issues I had were solved by using Dolt

Installing Dolt on a Raspberry Pi

Dolt is written in Golang and one of it's compelling features is the ability to cross compile it for other architectures and operating systems. While it is possible to build Dolt from source on the Raspberry Pi itself, it is much faster to install Go on a computer and cross compile it for Raspberry Pi. In addition to having Golang installed you will also need to install git. Once those prerequisites have been met you will need to clone Dolt and cross compile it for the Raspberry Pi.

# clone dolt
git clone git@github.com:dolthub/dolt.git

# change to the directory where the dolt command lives
cd dolt/go/cmd/dolt
GOOS=linux GOARCH=arm GOARM=5 go build

# build git-dolt
cd ../git-dolt
GOOS=linux GOARCH=arm GOARM=5 go build

# build git-dolt-smudge
cd ../git-dolt-smudge
GOOS=linux GOARCH=arm GOARM=5 go build

Once the executables have been built, you will need to transfer them to your Raspberry Pi and put them in a directory which is referenced by your PATH variable.

The Cocktails Repository

At this point I had a platform that I felt confident could make drinks. One issue that remained is how I would source cocktail recipes. I drink a fair number of whiskey based cocktails, and felt comfortable that I could assemble my favorite recipes, but I am not going to expose myself to anything new if I just hard code my favorites. Also, what if somebody else wanted to make this, and they like gin, or vodka based cocktails. What I need is an open data repository where people can collaborate. Well this is exactly what we built with Dolt and DoltHub. The cocktails repository on dolthub.com is a place where I will be collaborating with others to build a large database of cocktails. Anyone can create a DoltHub account and submit a PR with their favorite cocktail recipes, and whenever I pull down the repository on my cocktail robot, all those recipes will be available.

BarpyDB

In addition to the cocktail recipes, our cocktail robot also needs to persist some local state. Information on the number of pumps connected to the Raspberry Pi, which ingredient is pumped by each pump, and what GPIO pin needs to be turned on to control that pump. In the past I may have saved this data is a file, or persisted it in a sqlite database but since I am running Dolt there is a lot of power in putting this in a second Dolt database being served by the same server

Determining what drinks can be made

Because I have one database which contains our recipes, and a second database which contains the ingredients that are attached to each pump, I can use the power of SQL to determine all the recipes that I have the ingredients to make with one simple query:

  SELECT we_need.recipe_name FROM (
      SELECT recipe_name, COUNT(recipe_name) AS cnt
      FROM cocktails.recipe_ingredients
      GROUP BY recipe_name
  ) we_need
  JOIN (
      SELECT recipe_name, COUNT(recipe_name) AS cnt
      FROM cocktails.recipe_ingredients ri
      WHERE ri.ingredient_name IN (SELECT fluid FROM barpydb.fluids)
      GROUP BY recipe_name
  ) we_have
  ON we_have.recipe_name = we_need.recipe_name and we_have.cnt = we_need.cnt

In this query, the first sub-select (aliased as we_need) will return a result set with the recipe name, and the number of ingredients needed to make it. The second sub-select (aliased as we_have) will return a result set with the recipe name, and the number of ingredients we have available for that recipe. By joining we_need with we_have where the number of ingredients needed equals the number of ingredients we have for a recipe, we get the list of cocktails we can make with the current ingredients.

Barpy

Now that I have a repository of cocktail recipes, and a database containing the hardware and ingredient configuration, my Raspberry Pi has all the information it needs. I put a simple web server together using Flask which accepts requests to make drinks. The Barpy code can be cloned to a Raspberry Pi and setup like so:

git clone git@github.com:bheni/barpy.git
cd barpy
git-dolt fetch cocktails.git-dolt
git-dolt fetch barpydb.git-dolt

That will clone the python web server code from github to your Raspberry Pi and then clone the cocktails and barpydb repositories from DoltHub.

To run the dolt server, which the Barpy web server connects to there is a config.yaml file in the barpy directory which will serve both the cocktails and barpydb databases.

dolt sql-server --config config.yaml

Finally the barpy web server can be run from the barpy directory like so:

python3 -m barpy

In Action

Connecting to dolt sql server directly I can query the state to see what ingredients are available, and which drinks it can make.

>mysql --host 192.168.1.90 --user barpy --database barpydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.9-Vitess

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [barpydb]> select * from fluids;
+------+-----------------+--------+
| idx  | fluid           | volume |
+------+-----------------+--------+
|    0 | BOURBON WHISKEY |     16 |
|    1 | CAMPARI         |     16 |
|    2 | SWEET VERMOUTH  |     16 |
|    3 | NULL            |     16 |
|    4 | NULL            |     16 |
|    5 | NULL            |     16 |
+------+-----------------+--------+
6 rows in set (0.031 sec)

MySQL [barpydb]>   SELECT we_need.recipe_name FROM (
    ->       SELECT recipe_name, COUNT(recipe_name) AS cnt
    ->       FROM cocktails.recipe_ingredients
    ->       GROUP BY recipe_name
    ->   ) we_need
    ->   JOIN (
    ->       SELECT recipe_name, COUNT(recipe_name) AS cnt
    ->       FROM cocktails.recipe_ingredients ri
    ->       WHERE ri.ingredient_name IN (SELECT fluid FROM barpydb.fluids)
    ->       GROUP BY recipe_name
    ->   ) we_have
    ->   ON we_have.recipe_name = we_need.recipe_name and we_have.cnt = we_need.cnt;
+----------------------+
| recipe_name          |
+----------------------+
| BOURBON MANHATTAN    |
| CLASSIC BOULEVARDIER |
+----------------------+
2 rows in set (0.160 sec)

Watch a drink being made:

Takeaways

Dolt runs on Raspberry Pi and can be used in your hardware project. It's simple to setup and run and has countless advantages over sqlite. In this project alone Dolt's data collaboration functionality allows me to source recipes from any number of collaborators, and when used as an application server provides the full power of SQL to greatly simplify the code. All the source and data for this project is open, and linked above. Download Dolt and give it a try. Or fork the cocktail repo and send me a PR with your favorite cocktail recipes.