Is there a way to enforce that all queries include a where clause on tenantId?

[rsslldnphy]

I'm writing a single-database, multi-tenant app and would like to make sure every query contains a filter on tenantId so I don't accidentally show data from one tenant to another. Doesn't need to do anything fancy like rewrite the query, just check if it's there and throw an exception if it isn't. Ideally would be able to check that the correct tenantId is specified so would be great if the check could be changed on a request per request basis. Have commented on another discussion about hooks which feel like they could be a good way to achieve this, but thought I'd ask it as a separate question in case there already is a way to do this?

[michaeldebetaz]

Would wraping the query in a function and using a branded type be a good idea?

Something like this:

type AdminTenantId = Brand<string, "AdminTenantId">

const adminTenantId: AdminTenantId = requireAdminTenantId(request)

/**
 * @param {any} options - the query options.
 * @param {AdminTenantId} tenantId.
 */
const data = await getAdminData(options, tenantId)

The runtime check would occur in requireTenantId and you get the type safety with the branded type 👍

[rsslldnphy]

I don’t think that quite gets what I’m after I’m afraid, let me explain a bit more what I mean.

What I’d like is a single, global place at which I can enforce that every query in the app, including any I write in future, have to include a where clause on tenantId. This is a column that every table in the database has so any query for any table should include it as a clause.

so db.select().from(posts) should throw an exception but db.select().from(posts).where(eq(posts.tenanted, session.tenantId)) should work.

or alternatively the where condition should be automatically applied to every possible query at the start of each request, and a precooked query builder provided as part of the request object

[michaeldebetaz]

I get it, thanks for clarifying! Unfortunately, I don't see a solution. It sounds like the Prisma extensions, where you can trigger a function when running a query.

For drizzle, I don't see how you could do that.

[rsslldnphy]

yep that's the kind of thing. was originally looking at prisma but found too many weird things about it to feel comfortable adopting. this was one thing i really liked about it though.

is it possible to get the root table of a query from the query object? i.e.

const query = db.select().from(posts).join(comments);
query._.tableName // => this currently seems to be undefined. should it be?

i think this would allow me to write a function that would enforce what i need it too, something like this:

export const query = async <
  T extends AnyPgSelect,
  Q extends (_db: typeof db) => T
>(
  tenant: { id: string },
  query: Q
) => {
  const q = query(db);
  const tableName = q._.tableName as keyof typeof tables; // tables is a wildcard import of the tables from my schema
  return await q
    .$dynamic()
    .where(eq(table.tenantId, tenant.id));
};

i could then use eslint rules to prevent direct importing of db, meaning the only way queries could be run would be by using the query function like this:

await query(tenant, db => db.select().from(posts))

which is a bit awkward but feels like it should work. except q._.tableName seems to always be undefined, even though the field exists on the type :-(

[rsslldnphy]

ok i think i have it working, though i have to hackily access a private property of the query. will need more testing to make sure it works for other types of query, but initial testing seems promising. this is how the function ends up. would love it if there was a neater way to do this built into drizzle itself:

export const query = async <
  Select extends AnyPgSelect,
  QueryFn extends (_db: typeof db) => Select
>(
  tenant: { id: string },
  queryFn: QueryFn
) => {
  const query = queryFn(db);
  // tables is a wildcard import of all the tables from my schema
  const tableName = query["tableName"] as keyof typeof tables;
  const table = tables[tableName];
  if ("tenantId" in table) {
    return await query.$dynamic().where(eq(table.tenantId, tenant.id));
  } else {
    return await query;
  }
};

// and then use it like this:
const people = await query(tenant, (db) => db.select().from(person));

[rsslldnphy]

Scratch that, I've found a solution that I am much, much happier with. Instead of exporting tables directly for use by the application, I instead export subqueries that already include the constraint on tenant id (which are created on demand on each request). Imports of the tables themselves are restricted, so all I can import in the rest of the app are functions like this:

export const person = (tenant: { id: string }) =>
  db
    .select()
    .from(s.person)
    .where(eq(s.person.tenantId, tenant.id))
    .as("person");

which are then used like this:

  const people = await db
    .select()
    .from(person(tenant))
    .where(like(person(tenant).name, "%Cronenberg%"))
  );

which has the nice effect of meaning every table in the query will have the constraint applied, not just the root table. will also be able to use this for soft deletes which will be very helpful too.

the only limitation i've encountered so far: i can't figure out how it could be possible to make this work for relational queries, which i'd really like to use. is there any way to make this possible? i initially tried creating the schema like this:

export const db = drizzle(sql, { schema: { person: person(tenant) } });

but of course this doesn't work because the person subquery is defined using db so can't be used in its creation.

would be amazing if there was a way to achieve this.

[Angelelz]

My bad, it should be table. I'll edit the answer for future reference.

[cr101]

@rsslldnphy did you manage to get it to work?
I’d love to see a working example. Thank you

[rsslldnphy]

@cr101 I did kind of get it working with some ugly typecasting - but then I realised it wouldn't work for my use case anyway, as I also use soft deletes, so need a deletedAt = null clause applied to all joined tables, which afaict isn't currently supported by relational queries. FWIW this is what I ended up with (tenants are "organisations" in my data model, and a Tenancy is a combination of currently logged in user and the selected org):

import { SQLWrapper, and, eq } from "drizzle-orm";

import { db as _db } from "./client";
import { Tenancy } from "./types";

type Tables = (typeof _db)["query"];
type TableNames = Exclude<keyof Tables, "user" | "organisation">;

function getRQB<TTable extends TableNames>(
    tenancy: Tenancy,
    tableQuery: TTable,
) {
    const { org } = tenancy;
    return {
        findFirst(
            input?: NonNullable<
                Parameters<(typeof _db)["query"][TTable]["findFirst"]>[0]
            >,
        ) {
            if (!org) {
                throw new Response("No org context, but there should be", {
                    status: 500,
                });
            }

            return _db.query[tableQuery].findFirst({
                ...input,
                where: (table, obj) => {
                    if ("organisationId" in table) {
                        const scope = eq(
                            table.organisationId as SQLWrapper,
                            org.id,
                        );
                        return typeof input?.where === "function"
                            ? and(input.where(table, obj), scope)
                            : input?.where
                              ? and(input.where, scope)
                              : scope;
                    } else {
                        throw new Response(
                            "Tried to query a non-org scoped table",
                            { status: 500 },
                        );
                    }
                },
            });
        },
        findMany(
            input?: NonNullable<
                Parameters<(typeof _db)["query"][TTable]["findMany"]>[0]
            >,
        ) {
            if (!org) {
                throw new Response("No org context, but there should be", {
                    status: 500,
                });
            }

            return _db.query[tableQuery].findMany({
                ...input,
                where: (table, obj) => {
                    if ("organisationId" in table) {
                        const scope = eq(
                            table.organisationId as SQLWrapper,
                            org.id,
                        );
                        return typeof input?.where === "function"
                            ? and(input.where(table, obj), scope)
                            : input?.where
                              ? and(input.where, scope)
                              : scope;
                    } else {
                        throw new Response(
                            "Relational queries do not support non-org-scoped tables",
                            { status: 500 },
                        );
                    }
                },
            });
        },
    };
}

export type ScopedClient = Pick<typeof _db, "select" | "query">;

export const getScopedClient = (tenancy: Tenancy): ScopedClient => {
    const query = Object.keys(_db.query).reduce(
        (acc, key) => {
            acc[key as keyof typeof acc] = getRQB(tenancy, key as TableNames);
            return acc;
        },
        {} as Record<TableNames, unknown>,
    ) as (typeof _db)["query"];

    return new Proxy(_db, {
        get(target, p) {
            if (p === "query") return query;
            return target[p as keyof typeof target];
        },
    }) as ScopedClient;
};

unfortunately i'm not sure it'll be possible for me to use relational queries with my data model without some changes to drizzle itself.

[ItayTamary]

@rsslldnphy this is really impressive. would be really curious to see how your set-up evolved after a few months for both select and query

Thanks!

[rsslldnphy]

I eventually gave up with this as it was starting to feel too complex for such a critical part of the codebase, and resigned myself to using Prisma (which has some weirdnesses and design decisions I consider worrying signals). But even though it initially seemed like Prisma gave me what I needed via client extensions, it's turning out that limitations of that functionality as it applies to nested queries/includes are making this problem similarly difficult to solve.

I don't have a good solution. I've somewhat stalled in my project - I spent a good few weeks over christmas writing the basics (types only!) of my own ORM - a terrible idea but it was fun, I learned a ton about typescript and got some really nice types working that would let me express the queries I wanted to in a typesafe way, like this:

db.post.find({
    select: ["title", "content", "createdById"],
    where: {
        title: "string",
        [op.or]: [
            { title: { [op.eq]: "hello" } },
            { content: { [op.ilike]: "hello %", [op.ne]: "foo" } },
        ],
    },
    include: {
        tenant: {
            select: ["id"],
            where: { name: { [op.in]: ["cat", "dog"] } },
        },
        createdBy: {
            select: ["name"],
            include: {
                posts: {
                    select: ["title"],
                    where: { createdAt: { [op.gte]: new Date() } },
                },
            },
        },
    },
    limit: 10,
});

..and then started work on making middleware for multitenancy and softdeletes working before running out of steam/enthusiasm, because for the purposes of this project I really can't justify the effort it would take to fully build it out and maintain it. (It's something I do in my spare time and people rely on it, so I have to prioritize my time as much as possible on what will deliver immediate value to them.)

I'm a bit stuck. On the one hand it feels like an exciting time for Typescript ORMs at the moment, there's a few libraries out there that are moving things on from how they were in the Sequelize/javascript days in a really really significant way. On the other hand it feels like it might still be too early to adopt: hitting a wall needing support for multi-tenancy + soft deletes, neither of which feel like particularly "out there" needs, is making me wonder whether libs like Prisma are ready, and it's understandable if this kind of stuff isn't the focus of lower level libs like Drizzle. To be fair to Prisma, they do have a multi-tenancy example that uses one db = one tenant, but that's not how my app is architected (this is a v2) and it's not something I can easily change. Not really sure what to do.

[ikupenov]

I was having the same question, coming from another ORM myself. Unfortunately, I couldn't find a solution that suited my needs so I decided to create one from scratch. It utilizes proxies to intercept Drizzle functions. You can check the gist here.

[waltershewmake]

This is absolutely genius, thank you for posting!

[Schmavery]

I haven't gone deep into this yet, but maybe adapting the drizzle eslint plugin could be an alternate approach:
https://github.com/drizzle-team/drizzle-orm/blob/main/eslint-plugin-drizzle/src/enforce-update-with-where.ts

Like taking the enforce-update-with-where and make it check that the where clause has a "tenantID" field. Might be fiddly to get right depending on how your tables are structured though.

[laurent512]

Could someone achieve it simply ? Either using eslint plugin with specific enforcing or any other solution?

[tehnrd]

I'm only scoping out this library, but this is a feature I find myself needing repeatedly in multi-tenant apps that share the same database. I would love to see this implemented.

I have seen it done a few ways: before query lifecycle hooks on the models that allow you to add filters to the query. Global middleware that intercepts and allows modification of all queries. I have no strong preference on the approach, but this is a pain point for multi-tenant apps.

[coryvirok]

If you want to have the strongest isolation between tenants but still use the same database, you essentially have 2 choices:

1. Different tables per tenant, (e.g. tenant1_products, tenant2_products, etc.)
2. Query-time isolation using either stored procedures (e.g. select_products("tenant1")) or views that enforce the tenant filter.

Here's how I do it (option 2):

1. Define your tables that each have tenant_id column
2. Define a cur_tenant_id() function in the database as a stored proc/function/whatever your DB allows
3. Create a view for each table that filters on tenant_id = cur_tenant_id() and selects all columns (e.g. CREATE VIEW tenant_products AS SELECT * FROM products WHERE tenant_id = cur_tenant_id();)

This way, it's impossible for tenants to access one another's data if they always use the views. The real trick is getting cur_tenant_id() to work in a secure manner. But it's possible and can be secure if you know what you're doing.

Note: I believe this is how Supabase works, minus all the RLS and security stuff they have in place.