Better multi-tenancy support

[lutter]

We use Postgres namespaces ('schemas') heavily, and it would be great if Diesel had support for a typical multi-tenancy setup where the structure of tables is known at compile time, but the namespace for the tables is generated dynamically. I've been thinking that all that is needed is a macro namespaced_table! that largely has the same functionality as the existing table!, i.e., you could declare a table

  namespaced_table! {
    posts(id) {
      id -> Integer,
      title -> Text,
      body -> Text
   }
 }

To query from it, you would write

   posts::table("namespace").filter(posts::id.eq(1)).select(posts::title)

It should be possible to use all the same constructs that you can form with a static users::table, except that you write posts::table("namespace"). The advantage is that Diesel and Rust will still check most aspects of your queries, like that you only use columns that are declared (I actually got bitten by that one using plain SQL for such queries)

I tried to set up something like this by copying the declaration of the table! macro and making a few changes, but it seems that this could only be done as part of Diesel since the macro depends on a bunch of private helpers. It would probably also only be possible for Postgres; I have no experience how this sort of thing is set up in MySQL or SQLite.

I'd be willing to try implementing this for Postgres; before I do, I'd love to hear whether this is the right direction, and whether the maintainers would be willing to accept a PR along these lines since I don't want to maintain a Diesel fork.

[weiznich]

While I can understand the demand of such a feature I do not think this should be added to diesel any time soon, for the following reasons:

• Moving more code into diesel makes the my life as maintainer harder. It's already at a level where I can barley keep up with maintaining existing code, so adding new non-essential features is on halt till we have more maintainer resources.
• This feature is postgres dependent and only required for a very specific mostly business related use case. That's all fine, but in addition to the point above it makes me skeptical spend my time on maintaining such a implementation as part of diesel itself. I would rather like to spend that time with maintaining features more fundamental to a query builder like diesel (like the missing support for some more basic sql constructs)

My proposal here is to keep this as third party crate. That implies that if diesel does not expose any required API we could talk about opening this API as part of the public API or providing some alternative better suited for public consumption.

[lutter]

Thanks for the quick reply! I can totally understand the concern about taking on additional maintenance burden. I will try and get something going outside of Diesel and get back in touch if/when I run into issues with private symbols.

[sycured]

@weiznich I understand your feedback and I agree because it's database specific (not all database engine respect what is a schema)

@lutter have you built your multi-tenant crate or a middleware for this multi-tenant using PostgreSQL schemas?

Are you planning to open source it?

Actually, I've the same issue porting a Django and django-tenant-schemas to Rust (actix) and your feedback can be helpful

Thanks

[weiznich]

@sycured I'm not aware of any open source solution in this area. I would welcome and support any work done here by providing suggestions and the necessary underlying features from diesel.

As it seems like you also asked this question on reddit let me add some more words here. Someone there suggested to use SET search_path TO schemaname to switch between schemas. I would be very careful there as I assume this would screw up a lot of things for almost all database libraries. Diesel does use prepared statements internally and caches them as far as this is meaningful. As far as I'm aware that's also the case for most of the other solutions, as this is really important for performance. Just changing the search_path will likely break this for all of them, as now the already prepared statements will not longer work as expected. They will refer to tables/columns in the old schema, so that possibly the wrong data are loaded.