| Yeah, worked in projects with both of those methodologies. Combining tenants in one database is good for cross tenant aggregation but causes people to reach for... > brittle permission logic at the application level or complex, hard-to-debug, row-level security policies in databases like Postgres Separating them into separate databases has its own problems with connection pooling, resource management, and query speed. It's also a pain when you have to combine the data. Then you go down the rabbit hole of ETL and SaaS products with a "scratch" interface that promises to speed things up through (yet another) customer DSL and new naming convention for everything you already know. A better option is separating tenants by schema. Think about how you switch between schemas in Postgres. Queries look something like this: > set search_path to public, tenantA;
> select * from somewhere as smw inner join elsewhere lsw on smw.id = lsw.somewhere_id; The somewhere table is in the public schema, elsewhere is in the tenantA schema. In Rails, there are a couple gems that come to mind for multi-tenancy. acts_as_tenant works exactly like this, by swapping the tenant schema in ActiveRecord (the Rails ORM layer). The ros-apartment gem is another (formerly just 'apartment'). I ran across another gem called 'roomer' too but it is outdated. It works the same way though. You could even roll your own if you insert the logic at the right layer of whatever stack you're using. Set the tenant at the request level and the ORM auto switches the schema for you. Match a subdomain to the tenant and away you go. You got a SaaS application. edit: And, it'll pass a security audit because of the separation. And you can query across LOB tenant data in a BI front end if you can live without real-time data and build some materialized views; if Redshift and Snowflake are just too expensive. Using schema separation you get the best of both worlds; smaller data in tenant specific tables with the ability to cross tenant query because they're in the same database. And yes, I'm well aware foreign servers are a thing in Postgres but with sufficiently large data you have to spend more time tuning them for fetch size and indexes to get decent performance. Forget the fact that sometimes you have to implement dynamic SQL using SQL which is a nightmare of repetitious quotation marks with any complex data structure. Foreign servers amplify the escaped quotes. My stomach turns remembering having to write that code... Is there a npm package for something like this? React/node is not my daily driver. |
We build our JS SDK by wrapping Knex (nice query builder, not quite an ORM) and injecting our logic to the connection process. You could do the same.
The issue is that there are at least 5 popular JS ORMs, so we don't get the same solve-it-once that we get in the Rails ecosystem.