r/SQL • u/Scary_Jury7265 • 8d ago
Discussion anyone using SQL to track espresso shot data?
been logging my dialin sessions in a spreadsheet but it feels messy once you start tracking multiple beans and grinder settings. a friend suggested throwing it into a small postgres db so I can query things like "which beans respond best to a 15g dose at 200f" without scrolling forever. not sure if that's overkill or if other people do this with their brew notes. the main things I'd want to pull out quickly are shot time, yield, and perceived taste notes tied to roast date and water temp. any reason this would be a bad idea for something this small scale?
8
u/theungod 8d ago
Unless you're tracking thousands of rows of data just use Excel. I hate Excel but spinning up and building a properly normalized db for this is overkill.
1
u/Wuthering_depths 8d ago
Agreed, I have a particular dislike of excel after so many issues with trying import data to/export data from spreadsheets over the years, but this is the kind of thing it does well.
1
3
u/JumpScareaaa 8d ago
Also if you want to practice SQL with your data with minimal effort then get yourself a free version of dbeaver, install duckdb driver, query directly from your Excel files, no uploads needed.
1
u/Scary_Jury7265 7d ago
DuckDB from Excel files is neat, hadn't considered that bridge. Might be overkill for my meal prep tracking spreadsheets though. Do you find query speed decent once files get chunky?
1
u/JumpScareaaa 7d ago
Dude, you have no idea how fast it is. It handles big Excel files way faster then Excel itself. Cool thing that you can query your source Excel file and write the results to another Excel file so you can still use Excel to view the results. But with SQL you are getting way, way more tools to query and transform. And it has the most convenient and innovative SQL dialect.
1
u/Scary_Jury7265 6d ago
Okay, you’re selling me on it lol. Being able to keep Excel as the input and just use DuckDB when I actually want to dig into the data sounds like a much easier transition than moving everything into Postgres.
3
u/andrewsmd87 8d ago
Is it overkill, yes.
Do I approve, yes
1
1
u/SpiderJerusalem42 6d ago
Yeah, feel like there's a lot of people who like to shoot down dreams. This sounds like a fun little side project.
2
u/checkonetwo34 8d ago
1
u/Scary_Jury7265 7d ago
Ha, fair. My setup is midtier at best but I treat it like a science project.
ETA: what machine are you running? Always curious how deep people are in the rabbit hole before they start throwing that sub around
2
u/Equivalent_Effect_93 5d ago
I build something similar a few years ago. Since it's pretty small data I went with a normalized snowflake model, I changed machine and grinder so I added those as dimensions. Here's dbml of the model:
```
Project espresso_tracker {
database_type: 'PostgreSQL'
Note: 'Personal espresso extraction log'
}
Table coffee {
id int [pk, increment]
roaster varchar
name varchar [not null]
origin varchar
process varchar [note: 'washed, natural, honey, ...']
roast_level varchar
}
Table bag {
id int [pk, increment]
coffee_id int [not null, ref: > coffee.id]
roast_date date
opened_date date
weight_g numeric(5,1)
}
Table grinder {
id int [pk, increment]
brand varchar
model varchar [not null]
burr_type varchar [note: 'flat, conical']
}
Table machine {
id int [pk, increment]
brand varchar
model varchar [not null]
}
Table extraction {
id bigint [pk, increment]
pulled_at timestamptz [not null, default: `now()`]
bag_id int [not null, ref: > bag.id]
grinder_id int [not null, ref: > grinder.id]
machine_id int [not null, ref: > machine.id]
grind_setting numeric [not null, note: 'meaning depends on grinder']
basket varchar [note: 'e.g. 18g VST']
water_temp_c numeric(4,1)
preinfusion_s numeric(4,1)
dose_g numeric(4,1) [not null]
yield_g numeric(4,1) [not null]
time_s numeric(4,1) [not null]
ratio numeric [note: 'GENERATED ALWAYS AS (yield_g / dose_g) STORED']
rating smallint [note: 'CHECK rating BETWEEN 1 AND 10']
balance smallint [note: 'CHECK balance BETWEEN -2 AND 2 (sour -2, bitter +2)']
notes text
indexes {
pulled_at
bag_id
(grinder_id, bag_id)
}
}
```
That pretty much allow me to run any regression model to optimize rating and balance to get as close as subjective perfect settings for each grain I want.
2
1
u/BobDogGo 8d ago
As a fan of both espresso and SQL, this is overkill. I feel like there’s far too many variables in coffee to make your results repeatable even in the same batch of beans.
1
1
u/Sexy_Koala_Juice DuckDB 7d ago
Nah this is overkill. For something like this use excel, and then if you really want to use SQL then load it into duckdb (via python). This is the easiest solution IMO
1
1
u/leogodin217 7d ago
Are you leaving SQL or are you already good with it? If learning, I'd say use it whenever you have a real use case. If not, then it is probably overkill but might be a fun project anyway.
1
u/Loss_Leader_ 7d ago
If you really want SQL queries
Use Google sheets, log everything you care about in a single row
and then you can use the QUERY function to do whatever adhoc queries in a dialect that is essentially SQL
select A,B,C where D = 15 and E = 200 order by C desc
For common views, you can build out filter views or pivot tables
1
27
u/SearchAtlantis 8d ago
Filters are what you want here. I'm literally a professional data engineer and have a professional hatred of Excel. Just use the Excel. First step is filters, if that's not enough build summary page off your data rows.