Recipe for row tenancy

[ilijaNL]

Hello, I just discovered this library and it seems really awesome especially combined with generated type definitions.

Currently I have a database structure where I do tenancy on a row basis, e.g every table has a tenant column, say tenant_id. I wonder what the best practise is to create "a tenant specific" kysely query builder.

One implementation I was thinking of is to have this pattern:

function toTenantQuery<DB, TB, O>(selectQuery:  SelectQueryBuilder<DB, TB, O>, tenant_id: string) {
  return selectQuery.where('tenant_id', '=', tenant_id);
}

The goal is to always append where tenant_id = tenant_id to the final query, however I am not sure how to deal with ambigious columns (joins where tenant_id exists on both tables).

Additionally how can I deal with mutations in the same manner, e.g. prefilling tenant_id automatically.

Currently it is no option for me to migrate to schema based tenancy.

[igalklebanov]

For whereable query builders... something like this could work...

function filterByTenant<
  QB extends 
    | SelectQueryBuilder<any, any, any> 
    | DeleteQueryBuilder<any, any, any> 
    | UpdateQueryBuilder<any, any, any, any>,
  DB = QB extends SelectQueryBuilder<infer D, any, any>
    ? D 
    : QB extends DeleteQueryBuilder<infer D, any, any> 
      ? D 
      : QB extends UpdateQueryBuilder<infer D, any, any, any>
        ? D
        : never,
  TB = QB extends SelectQueryBuilder<any, infer T, any> 
    ? T 
    : QB extends DeleteQueryBuilder<any, infer T, any> 
      ? T 
      : QB extends UpdateQueryBuilder<any, any, infer T, any>
        ? T
        : never
>(
  queryBuilder: QB, 
  tenantId: string, 
  tenantFields: StringReference<DB, TB> | ReadonlyArray<StringReference<DB, TB>> = 'tenant_id'
): QB {
  if (!Array.isArray(tenantFields)) {
    tenantFields = [tenantFields];
  }

  if (!tenantFields.length) {
    tenantFields = ['tenant_id'];
  }

  tenantFields.forEach(tenantField => queryBuilder = queryBuilder.where(tenantField, '=', tenantId));

  return queryBuilder;
}

For inserts... something like this could work... (not handling conflicts, it's too dialect specific).

function insertWithTenant<DB, TB extends keyof DB>(tenantId: string, db: Kysely<DB>) {
  return (
    table: TB, 
    values: Omit<InsertObject<DB, TB>, 'tenant_id'> | Omit<InsertObject<DB, TB>, 'tenant_id'>[]
  ): Promise<void> => {
    if (!Array.isArray(values)) {
      values = [values];
    }

    if (!values.length) return;

    const valuesWithTenant = values.map(value => ({ ...value, tenant_id: tenantId })) as InsertObject<DB, TB>;

    await db.insertInto(table).values(values).execute();
  }
}

The best approach for all cases, would be to make a plugin that transforms every query, but it'll require knowledge of Kysely's AST, and it might break in future versions since you're depending on internals.

[ilijaNL]

Hmm I wonder then if it is not better to convert generated SQL to an ast, add tenant where clause and convert back to the SQL. This can be done pre execute time ofcourse.

How stable is the kysely ast spec and how hard would it be to modify it.

In the end only your infra layer (as low as db driver) should be tenant aware.

So ideally there should a method

query.executeAsTenant(tenant_id)

or a tenant specific db query builder instance. However I am not sure if this is possible (by modifying the generated ast by Kysely )?

Could you share your thoughts on this?

[igalklebanov]

Kysely's plugin system answers your requirements - altering query AST before query execution.

If you wanna go there, you'll need to implement a plugin and a transformer.

plugin just passes root operation node to transformer..

  transformQuery(args: PluginTransformQueryArgs): RootOperationNode {
    return this.#tenantTransformer.transformNode(args.node)
  }

transformer overrides revelant transform node methods and injects WhereNodes and such with tenant id.

You can find some examples here

Once implemented, you can use your plugin at Kysely instantiation to apply to all queries OR per query using .withPlugin(...).

[ilijaNL]

Thanks for tips, I will look into it and try to come with something.

[kosciak9]

Hello, have you pushed forward with this? I want to dive into the plugin world, but if you had made some progress then we can collaborate on it :)

[kosciak9]

I have written an implementation that works for us (we decided against using many of more advanced SQL tooling like views, procedures or anything like it). In case anyone has similar use case or want some sort of starting point for more complete version, use the snippet below:

// transformer.ts
import type { OperationNode } from "kysely";
import {
  AndNode,
  BinaryOperationNode,
  ColumnNode,
  DeleteQueryNode,
  InsertQueryNode,
  OperationNodeTransformer,
  OperatorNode,
  PrimitiveValueListNode,
  SelectQueryNode,
  UpdateQueryNode,
  ValueNode,
  ValuesNode,
  WhereNode,
} from "kysely";

export class WithRowTenancyTransformer extends OperationNodeTransformer {
  #tenantId: string;
  #tenantColumnName: string;
  #tenantOperationNode: BinaryOperationNode;

  constructor(tenantId: string, tenantColumnName: string) {
    super();
    this.#tenantId = tenantId;
    this.#tenantColumnName = tenantColumnName;
    this.#tenantOperationNode = BinaryOperationNode.create(
      ColumnNode.create(tenantColumnName),
      OperatorNode.create("="),
      ValueNode.create(tenantId)
    );
  }

  protected combineWhereNodes(node: OperationNode): WhereNode {
    const combinedWhereNode = AndNode.create(node, this.#tenantOperationNode);
    return WhereNode.create(combinedWhereNode);
  }

  protected generateWhereNode(): WhereNode {
    return WhereNode.create(this.#tenantOperationNode);
  }

  protected transformSupportedTopLevelNode<
    T extends DeleteQueryNode | SelectQueryNode | UpdateQueryNode
  >(node: T): T {
    const topLevelWhereNode = node.where;

    if (topLevelWhereNode === undefined) {
      // no where clause
      return { ...node, where: this.generateWhereNode() };
    }
    return { ...node, where: this.combineWhereNodes(topLevelWhereNode.where) };
  }

  protected transformNodeImpl<T extends OperationNode>(node: T): T {
    if (
      UpdateQueryNode.is(node) ||
      DeleteQueryNode.is(node) ||
      SelectQueryNode.is(node)
    ) {
      return this.transformSupportedTopLevelNode(node);
    }

    return super.transformNodeImpl(node);
  }

  protected transformInsertQuery(node: InsertQueryNode): InsertQueryNode {
    // rather bare-bones for now

    const newValuesList: OperationNode[] = [];
    if (node.values !== undefined) {
      newValuesList.push(node.values);
      newValuesList.push(ValueNode.create(this.#tenantId));
    }

    const valuesNode = node.values;
    if (valuesNode === undefined || !ValuesNode.is(valuesNode)) {
      throw new Error("Unexpected node");
    }

    const primitiveValueListNodes = valuesNode.values.map((valuesListNode) => {
      if (!PrimitiveValueListNode.is(valuesListNode)) {
        throw new Error("Not implemented");
      }

      return PrimitiveValueListNode.create([
        ...valuesListNode.values,
        this.#tenantId,
      ]);
    });

    const newNode = InsertQueryNode.cloneWith(node, {
      columns: [
        ...(node.columns ?? []),
        ColumnNode.create(this.#tenantColumnName),
      ],
      values: ValuesNode.create(primitiveValueListNodes),
    });

    return newNode;
  }
}

// plugin.ts
import type {
  KyselyPlugin,
  PluginTransformQueryArgs,
  PluginTransformResultArgs,
  QueryResult,
  RootOperationNode,
  UnknownRow,
} from "kysely";

// reasoning below
// import { SqliteQueryCompiler } from "kysely";
import { WithRowTenancyTransformer } from "./transformer";

export class WithRowTenancy implements KyselyPlugin {
  readonly #transformer: WithRowTenancyTransformer;

  constructor(tenantId: string, tenantColumnName: string) {
    this.#transformer = new WithRowTenancyTransformer(
      tenantId,
      tenantColumnName
    );
  }

  transformQuery(args: PluginTransformQueryArgs): RootOperationNode {
    const result = this.#transformer.transformNode(args.node);
    // I'm leaving this in case you need to add / debug resulting SQL
    // console.log(
    //   JSON.stringify(
    //     {
    //       node: args.node,
    //       result: new SqliteQueryCompiler().compileQuery(result),
    //     },
    //     null,
    //     2
    //   )
    // );

    return result;
  }
  transformResult(
    args: PluginTransformResultArgs
  ): Promise<QueryResult<UnknownRow>> {
    return Promise.resolve(args.result);
  }
}