Multi-tenancy or at least hooks to modify queries on the fly?

[frederikhors]

Hi! I'm new to Rust. I'm finally starting to write something with this amazing tech! And I would like to try SeaORM.

I cannot find in Docs a way to have what in other techs are called "hooks". Nor I can find a way to handle multi-tenancy.

My need is to use tenant_id columns in (almost) each table of the DB (Postgres in this case).

So I need a way to say to the ORM to add a WHERE tenant_id = [current_user.tenant_id] to each query is created.

I know there are other solutions like RLS but for now we have to stick with this old legacy DB design and architecture.

Is there an easy way to do this with SeaORM?

[frederikhors]

I found https://www.sea-ql.org/SeaORM/docs/generate-entity/expanded-entity-structure/#active-model-behavior.

I would like to have a before_select action.

Like https://bun.uptrace.dev/guide/hooks.html#model-query-hooks.

(@billy1624 sorry for the tag: is it already possible today someway?)

[frederikhors]

I am thinking that I could create a Trait perhaps called "Tenanted" for an entity.

But I would like to understand how to hook the hooks of the various actions (SELECT but also UPDATE / DELETE) to these "tenanted" entities.

[billy1624]

Hey @frederikhors, welcome and sorry for the delay. I saw your message on Discord as well.

Temporary solution:

I'd suggest having a method to apply the condition on your select, update and delete.

#[derive(Debug, sea_query::Iden)]
pub struct Tenanted;

fn filter_tenanted<Q>(query: Q, tenant_id: i32) -> Q
where
    Q: QueryFilter
{
    query.filter(Expr::col(Tenanted).eq(tenant_id))
}

filter_tenanted(Entity::find(), tenant_id)
filter_tenanted(Entity::update_many(), tenant_id)
filter_tenanted(Entity::delete_many(), tenant_id)

---

In the long-run:

I agree we should add hook to alter the query. Let me think and come back to you in a bit.

[frederikhors]

@billy1624 I'll start in a few hours with sea-orm. Any news on these hooks?

[billy1624]

Hey @frederikhors, sorry, no progress yet. I'll get back to you tomorrow :)

[p-bakker]

I know, thread from 2022, but I wonder if anything has changed wrt handling tenant filters since then

The proposed filter_tenanted method requires the query authors to not forget to call this function at every step along the way (CTEs, joins, subqueries, across unions/intersect/etc), which makes it brittle

Rather, I would like my devs or AI's to not have to worry about any of this and let them just author their query which they then send into the executor, which introspects the query and adds the tenant filtering before stringifying the query and sending it to the database

Found #2820, which is more SeaORM focused (but of course would depend on SeaQuery capabilities)