Multi-tenant app with single-tenant data layer

[dsod]

A great feature of postgres is that you can separate data on multiple levels; databases and schemas. While it is possible to create mutli-tenant Payload applications today, there is a use-case for separating tenant data on the database layer, while keeping the application layer multi-tenant. This way, we can deploy and run one instance of the Payload application code and have it connect and use a tenant-specific database schemas.

This can simplify access control logic in the payload application code by a lot, since you no longer have to create access control functions that scopes the returned data according to the authenticated tenant. It would also allow multi-tenant payload instances to use globals natively (#3268).

Today, when you instantiate a payload instance you pass in a Postgres DB adapter with connection defails, as well as an optional schemaName argument. But, since NextJS (payload 3) / express (payload 2) servers are long-running processes and would handle requests coming from different tenant users (since the application still have mutli-tenancy), it is currently not enough to be able to set the schema name during instantiation.

My proposal is to add a new method to the postgres adapter, which allows us to change schema being used during runtime. Something like this:

const schemaMap = {
  "tenant1.example.com": "schema1"
}

// Create a single shared DB connection pool
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export const updateSchemaName = async (payload: Payload, req: NextRequest) => {
  const schemaName = schemaMap[new URL(req.url).hostname]; 
  
    if (!schemaName) {
      return notFound();
    }
  
    // Get a client from the pool
    const client = await pool.connect();
  
    // Switch the schema on the client
    await client.query(`SET search_path TO ${schemaName}`);
  
    // Create Drizzle instance using the client (not the pool directly)
    payload.db = drizzle(client, {schema: payload.db.schema});
  
    // Important: Release the client back to the pool when the request is finished
    return client.release;
}

// Somewhere in the request flow / middleware?
const release = payload.db.updateSchemaName(payload, req)

// Before returning a response, release the client back to the pool
release(true);

[jjimenez22991]

This is a must have for some companies to avoid to have all in single DB and being able to change DB by request. Is there any workaround that can be used now to update the DB connection per request?