Q&A highlight
Postedabout 1 month ago
Key Takeaways
If everyone lives within one database I'd throw up a per-customer read-only database in front of it for running their queries so they don't create performance issues.
Another option might be to let them ingest their data directly into the existing BI tools they use, cool thing about that is it can entrench you into their infrastructure and it offloads a lot of this complexity you're dealing with.
1) I create a baseline set of views I want my customers to have 2) For each new customer, I’ll run a script that create a replica of those views- filtered by their customer ID 3) I’ll allow my customers to write pure SQL- limiting them to only SELECT queries and a couple niche business rules, as well as masking any DB-level errors, because that just feels wrong
How does that approach sound?
CREATE USER customer_xyz WITH PASSWORD 'foo';
CREATE VIEW customer_xyz_data AS SELECT * FROM data_stuff WHERE customer_id=x;
GRANT SELECT ON customer_xyz_data TO customer_xyz;
You can enforce all of that yourself but ultimately if they're using an account that can read/write other tables you will always have to be careful to make sure you are sanitizing their input not just to selecting but like, limiting joins and nested queries too.
Dumb question- is creating a set of Views for each customer even efficient for my MySQL database? I could realistically see us having ~12 customer-facing views- is having 12*N views a smart and scalable way to architect this?
"Your side" of this can be optimized easily enough, but the user-submitted queries are likely to be inefficient or miss indexes, which is why one database per customer can be better since they each have their own resources.
You can create the views and accounts as needed and destroy them when sessions end rather than keeping them permanently too, so when the user signs in you create the view and account, after the session or some period of inactivity you remove them.
The idea of deleting and recreating views is an interesting one. I see that as a really cool approach- considering we can go without it as a v1 then include it as we scale.
Thank you for all your advice so far! This has been truly helpful.
Not affiliated with Hacker News or Y Combinator. We simply enrich the public API with analytics.