Is this a good practice for joining data in Drizzle? Should I use SQL or handle it in JS?
Hey! I'm building an app and, to be honest, I'm not super strong on the backend side, but I’ve been given this task as a full-stack developer.
The app has a main table called car_formula_colors. Each formula can have:
Multiple car models
Multiple color types
Multiple layers
And each layer can contain multiple toners
So the data structure is deeply nested, and I’m using join tables to associate these related values to a single formula.
I’m using SQL-like syntax with Drizzle (not the relational query builder) because I want to learn more SQL along the way. The problem is: Drizzle doesn’t support JOIN + json_agg out of the box. When I try to do left/right joins, I end up with a bunch of repeated rows instead of properly grouped nested data.
I found some helper functions made by the Drizzle team that simulate json_agg, but that made me wonder:
If those helpers exist, why aren’t they built into Drizzle natively?
Is this approach considered best practice?
Or should I just fetch flat data and restructure it into nested objects in JavaScript?
There will eventually be many formulas, so I’m also worried about performance and efficiency. I'd really appreciate any advice or patterns others are using for this kind of structure!
0 Replies