Open Coffee Database


Has an open coffee database/repo been created yet for contributions?


No, but that is great idea to get started on. I put it on my todo list for tomorrow and will update here when it’s up.


Do you have an idea of what the format will be?

I guess if it’s just a text file it would make it really easy just to do a PR to update it on GitHub.


For sure. We will have it available as a mysql dump and a csv file. Let me know any other formats you might think folks would be interested in.



Oh man, I’m so excited to get my hands on this database! It’s pretty hard to go wrong with mysql for something like this.


If it is just stored locally this would be a perfect use case for sqlite IMHO. If remote access then MariaDB I think would be preferred.


So I went with MySQL because thats what I know best and it was the easiest for me to implement without any learning curve. Also, at this scale, theres not a ton of over head for reads/writes so it doesn’t take up much processing power or ram. The DB is local but does phone home to get updates.

I’d be interested in hearing the pros/cons of both sqlite and mariaDB if you’re familiar with them. I believe I have played with sqlite back in the day a bit on some proof of concept chrome extensions but nothing beyond that. I’m open to using MariaDB just for the fact thats its the true open source fork of mySQL and for all intents and purposes, compatible with mysql, at least within our specific needs. But would definitely interested hearing if there other good reasons beyond that.



Yeah, I was wondering if SQLite might be a good choice. I guess it means you don’t have to have the server running in the background, which might be an advantage on a Pi.

@matt How do you plan on doing upgrades to the platform? Would it be difficult to migrate to a different DB after the first release?

If you’re using some kind of DB abstraction layer (eg doctrine/dbal) maybe it doesn’t matter which you choose? Or each user could choose which one they wanted to use?


I can definitely go into details on them, but the biggest thing is with sqlite you don’t run a server, just import the libraries and point it at a file.

More specifics:
This is exactly what sqlite was designed for, a local database that can be embedded in a program. It is super lightweight and very fast. There might be some minor changes needed as it uses syntax very similar to ANSI SQL (like Postgresql and Oracle). As for updating the sqlite database you would just close the connections, download new file, then you can start querying again. Most of what makes sqlite so lightweight and fast is just that there is no overhead to account for concurrency, or network communication. In this instance it would mean less resource usage, a bit simpler system and not much else I think.

MariaDB I mentioned mostly because most Linux distros now when you tell them to install mysql they actually install MariaDB. In most cases this one included MariaDB is just a drop in replacement for mysql. Actually MariaDB 10.2 has a lot of the new features in mysql 8.0, but MariaDB 10.2 is fully released while mysql 8.0 is still beta.

I almost forgot, I would be willing to help you implement sqlite if you are interested.


If you’re going for MySQL, MariaDB is the way to go. It’s meant to be a drop-in replacement and performs better.

If you’re going RDBMS, the one that first comes to my mind is PostgreSQL


I definitely agree on both counts, though this application is really more suited to sqlite. There are other, non-sql options that would work as well, but the only one I have developed with is sqlite.


I think swapping MySql for MariaDB is a great idea and we will definitely do that. My only fear about sqlite is how it will affect community development of integrations, I want it to be as easy as possible for folks to get started without a lot of experience. But like you guys have mentioned, an abstraction layer would simplify that.

I’m gonna take a couple days off after the campaign ends but after that we’re gonna start pushing really hard on this side of things. Apologies for any delays in replying while finishing up the KS, so many balls in the air right now!

And Im definitely interested in everyone’s help!


sqlite definitely has a lot of community support and is easy to get started with, but an abstraction layer makes the most sense for future expandability. I believe that other than sqlite being less well known it would actually make it easier for someone to pickup the project and help out, or make their own changes.


@matt will it be PHP or Python that’s connecting to the database? Or both?


@jjok Both of them are used, php for the middleware and python for most of the actual control code.


@thexder1 Very cool, I’m gonna do some research on sqlite today so I can have a more informed perspective.


What will the database be used for, other than storing coffee information? Will there be any information specific to a user or machine?
If the database just contains the same data for everyone, then SQLite has got to be the right solution, as you can just send the file in a system update.


yes, the DB handles more than the coffee. It stores pin to hardware mappings, user settings, your standard API backend infrastructure, recipes, etc.


With information specific to the user/machine you should just be able to use a separate database file for that, or distribute the updates as CSV or SQL files and load them in that way. It might require a little more work to load in from CSV or a SQL file, but not much more.


Yeah. I was thinking the same thing. Just a separate SQLite DB would be super simple… I think.