Multi-tenant DB design advice needed
Unanswered
Giant panda posted this in #help-forum
Giant pandaOP
I'm in the planning phase of a B2B SaaS platform (taking inspiration from Linear/Fibery/some generic admin dashboard) and need some advice on the database architecture for multi-tenancy using Neon Postgres.
Current planned tech stack (Any feedback is greatly appreciated):
* React
* Next.js
* Vercel for hosting
* Node.js (+Hono or Express.js?)
* Clerk for auth
* Neon postgres
* Drizzle ORM and/or GraphQL
I'm torn between two approaches:
* db with shared schema + RLS
* database-per-user
Would love to get some input to both approaches. What are the pros/cons?
How would the onboarding work for new organizations? Using database-per-user for organizations/companies, can one user be part of multiple organizations?
Some more info on the app:
Every instance, regardless of company will have a table fit to their needs (in example: for keeping track of orders/projects). I reckon it will mostly be the same for many, but may contain different columns. To begin with there will be a dashboard, project management, and calendar view + resource management. Will also, down the line, probably be a chat function, some collaboration features/note taking, upload files per project, AI functionality etc (I want to plan my db and tech stack for these from the start, so I won't need to do major changes later). Also, I have some knowledge with GraphQL, and limited knowledge of Drizzle, any thoughts on using either?
I realized this became quite a few questions, but I appreciate any guidance!
Thanks in advance!
Current planned tech stack (Any feedback is greatly appreciated):
* React
* Next.js
* Vercel for hosting
* Node.js (+Hono or Express.js?)
* Clerk for auth
* Neon postgres
* Drizzle ORM and/or GraphQL
I'm torn between two approaches:
* db with shared schema + RLS
* database-per-user
Would love to get some input to both approaches. What are the pros/cons?
How would the onboarding work for new organizations? Using database-per-user for organizations/companies, can one user be part of multiple organizations?
Some more info on the app:
Every instance, regardless of company will have a table fit to their needs (in example: for keeping track of orders/projects). I reckon it will mostly be the same for many, but may contain different columns. To begin with there will be a dashboard, project management, and calendar view + resource management. Will also, down the line, probably be a chat function, some collaboration features/note taking, upload files per project, AI functionality etc (I want to plan my db and tech stack for these from the start, so I won't need to do major changes later). Also, I have some knowledge with GraphQL, and limited knowledge of Drizzle, any thoughts on using either?
I realized this became quite a few questions, but I appreciate any guidance!
Thanks in advance!
3 Replies
Database per user is ultimate for privacy/security, but also the most complex and toness of databases to look after.
Schema is the next best for privacy and security, ands is a little bit easier as just one DB to manage.
Same database, same schema is the easiest but also the riskiest privacy/security wise, but it is the way most choose to go as it is easier.
I haven't played with your stack before. I'm a Django backend guy, and I've tried different schemas and same schema before. I struggled with a lot of niggles, and ended up choosing same schema. You may not get as many issues if you write it all, whereas Django's ORM isn't aimed at different schemas, hence why I had more issues.
So i would suggest playing with either, build a small prototype of each and see which you prefer.
With regards to GraphQL, it is amazing and powerful. and makes fetching the data you want easy, however it can have security risk if not managed carefully, as its easy to give access to data you don't want to because of how linked everything is. Defining your types is important.
Schema is the next best for privacy and security, ands is a little bit easier as just one DB to manage.
Same database, same schema is the easiest but also the riskiest privacy/security wise, but it is the way most choose to go as it is easier.
I haven't played with your stack before. I'm a Django backend guy, and I've tried different schemas and same schema before. I struggled with a lot of niggles, and ended up choosing same schema. You may not get as many issues if you write it all, whereas Django's ORM isn't aimed at different schemas, hence why I had more issues.
So i would suggest playing with either, build a small prototype of each and see which you prefer.
With regards to GraphQL, it is amazing and powerful. and makes fetching the data you want easy, however it can have security risk if not managed carefully, as its easy to give access to data you don't want to because of how linked everything is. Defining your types is important.
If you say you will be looking at chat, look into this early, work out how you would find the correct database/schema if using different ones over websocket (which you will presumably use for chat), this was a sticking point for me in django because that would have been hard with the way django channels works. Again, this might not be an issue depending on the library you use or if you build totally yourself.
Remember if you choose different databases or schema, you will need one central database or schema for managing authentication, billing and also storing the database details/schema details in.
Remember if you choose different databases or schema, you will need one central database or schema for managing authentication, billing and also storing the database details/schema details in.
I built and run a production SaaS app with the below stack
Django Backend
GraphQL API (Although I am introducing some REST hybrid)
React + Vite Frontend (Could have easily chose NextJS but the app router was still experimental when I started building and I just had less bugs with React, but I would happily use NextJS now if doing it now.
Digital Ocean for hosting.
Thought I would offer the reason why I am going hybrid with a REST API alongside GraphQL and that is simply because the django implementation of graphql (graphene) is missing some key features which libraries like Apollo are not such as native subscriptions, and is quite limited on how to implement pagination. But I don't think you would get these issues if using something like Apollo.
As I said not the same stack but have built one before so though I'd offer my two cents, let me know if you have any other questions.
Django Backend
GraphQL API (Although I am introducing some REST hybrid)
React + Vite Frontend (Could have easily chose NextJS but the app router was still experimental when I started building and I just had less bugs with React, but I would happily use NextJS now if doing it now.
Digital Ocean for hosting.
Thought I would offer the reason why I am going hybrid with a REST API alongside GraphQL and that is simply because the django implementation of graphql (graphene) is missing some key features which libraries like Apollo are not such as native subscriptions, and is quite limited on how to implement pagination. But I don't think you would get these issues if using something like Apollo.
As I said not the same stack but have built one before so though I'd offer my two cents, let me know if you have any other questions.