How can i have a schema based multi-tenancy ( not row based tenant access) ?

[vvhybe]

For this I want data structured differently and isolated in different schemas as tenant virtual DB rather than all the tenants being stored on the default schema (public) and using tenant row-based access,

So far I come up with combinations to result in what seems a solid solution:

defining the schema table to be used by the registered tenant:

Considering this example of the TAX table, covering potential relations zod schema and graphql schema

import * as t from 'drizzle-orm/pg-core';
import { pgEnum, pgTable } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';
import { globProducts } from '@/database/schema/products';
import {
  TAXES_ON,
  TAXES_CALCULATION,
  TAXES_TYPES,
  TaxOn,
  TaxCalculation,
  TaxType,
} from '@/lib/data/tax';
import { tallestElement } from '@/lib/utils';
import { z } from 'zod';
import { timestamps } from '@/database/schema/timestamps';

export const taxCalculationEnum = pgEnum('calculation', TAXES_CALCULATION);
export const taxTypeEnum = pgEnum('type', TAXES_TYPES);

const taxColumns = {
  id: t.serial().primaryKey(),
  name: t.varchar('name', { length: 100 }).notNull(),
  code: t.varchar('code', { length: 50 }),
  on: t.varchar('on', {
    enum: TAXES_ON,
  length: tallestElement([...TAXES_ON]).length // special case enum: on -> ["sale", "purchase"] | ["sale"] | ["purchase"]
  })
    .array()
    .$type<TaxOn>()
    .notNull()
    .default([...TAXES_ON]),
  calculation: taxCalculationEnum().default('excluded').$type<TaxCalculation>().notNull(),
  description: t.text(),
  rate: t.real().notNull(),
  type: taxTypeEnum().default('percentage').$type<TaxType>().notNull(),
  ...timestamps,
}

export const globTax = pgTable('taxes', taxColumns); // this only be used in the zod schema

export const tenantTaxes = (tenantSchema: t.PgSchema<string>) => {
  const tenantTable = tenantSchema.table('taxes', taxColumns);
  return {
    taxes: tenantTable,
    taxesRelations: relations(tenantTable, ({ many }) => ({
      products: many(globProducts, { relationName: 'productTaxes' })
    })),
  };
};

export const insertTaxSchema = createInsertSchema(globTax, {
  name: z.string().min(3),
  rate: z
    .number({ message: 'Tax rate is required' })
    .min(0, 'Tax rate should be greater than 0')
}).superRefine((data, ctx) => {
  if (data.type === 'percentage' && data.rate > 100) {
    ctx.addIssue({
      code: z.ZodIssueCode.custom,
      path: ['rate'],
      message:
        'Rate should be less than or equal to 100 if the type is percentage'
    });
  }
})

export const selectTaxSchema = createSelectSchema(globTax);

export type Tax = typeof globTax.$inferSelect;
export type NewTax = typeof globTax.$inferInsert;

Defining a dynamic schema:

Generate a dynamic schema on demand by passing the instance of the PgSchema

export function tenantSchema(schema: PgSchema<string>) {
  return {
    ...tenantTaxes(schema),
    ...tenantProducts(schema),
    ....
  }
}

export default tenantSchema;

Retrieving the tenant virtual DB *(schema):

export function getTenantDB(tenantId: string) {
  const schema = pgSchema(`tenant_${tenantId}`);
  // Return a new drizzle instance scoped to the tenant's schema
  return drizzle(pool, { schema: tenantSchema(schema), casing: 'camelCase' });
}

+Plus: Querying the specific tenant virtual Db

For that i used drizzle with graphql via drizzle-graphql pkg with nextjs app router stack

// app/api/graphql/[tenant]/route.ts
import { getTenantDB } from '@/database'; // Assumes getTenantDB fetches tenant-specific DB
import { ApolloServer } from '@apollo/server';
import { buildSchema } from 'drizzle-graphql';
import { startServerAndCreateNextHandler } from '@as-integrations/next';
import { NextRequest, NextResponse } from 'next/server';
import { validateTenant } from '@/lib/tenant';

type TenantParams = {
  params: {
    tenant: string;
  };
};


export async function setupGraphQLServer(tenantId: string) {
  // Get the tenant-specific database instance
  const tenantDB = getTenantDB(tenantId);
  
  // Build the schema using the tenant-specific database
  const { schema } = buildSchema(tenantDB);
  
  // Configure Apollo Server with the tenant's schema
  const server = new ApolloServer({
    schema,
    includeStacktraceInErrorResponses: process.env.NODE_ENV !== 'production',
    nodeEnv: process.env.NODE_ENV,
    introspection: process.env.NODE_ENV !== 'production',
  });
  
  return startServerAndCreateNextHandler(server);
}

// Handler for the /api/graphql/[tenant] route
async function GQLhandler(req: NextRequest, { params }: TenantParams){
  const { tenant } = params;
  const isValidTenant = await validateTenant(tenant, req.nextUrl.origin);

  if (!isValidTenant) {
    return new NextResponse('Not Found', { status: 404 });
  }

  const handler = await setupGraphQLServer(params.tenant);
  return handler(req);
}

export const GET = GQLhandler;
export const POST = GQLhandler;

Now the problem I'm facing is how I can generate migration files for this, if someone can help, or even if there is an alternate approach to use than the one i used (sharing the same goal of virtual tenant DB)

I will be so grateful if you guys dropped your opinion about this.