Dynamically choosing a schema in Postgres queries

[v4v4n]

Hello,
first of all, this is very nice library. we can maximize our sql skill for development.
Is there any guide we can do CRUD operation to different schema other than default public schema in postgreSQL ?
we need to perform CRUD to different schema.
we plan to build multi tenant system, where each tenant has their own schema.
so the CRUD operation must be performed to different schema dynamically.

thank you very much in advanced

[ForbesLindesay]

You have a few options:

Option 1: Set the schema when creating the database connection

You could create separate database connections for each client, and set the schema at that point. Documentation here: https://www.atdatabases.org/docs/pg-options

// TODO: memoise this call or something so you're not constantly creating new connection pools
function getConnection(client: string) {
  return connect({schema: client})
}

Option 2: update the search path for each connection

Running SET search_path TO schema_1 on a connection will make all queries made on that connection default to schema_1 from then on. This is what @databases does for you when you set the schema option. You could therefore just do this every time you get a connection from the database:

import createConnectionPool, {sql, Connection} from '@databases/pg';

// Never use the `db` directly as you don't know which schema
// you would be querying, only use it via the following function
const db = createConnectionPool();

export async function withSchema<T>(client: string, fn: (db: Connection) => Proimse<T>): Promise<T> {
  return await db.task(async db => {
    await db.query(sql`SET search_path TO ${sql.ident(client)}`)
    return await fn(db)
  })
}

Then any calls to query the database would be wrapped with withSchema. e.g.

export async function getUsers(client: string) {
  return await withSchema(client, async db => {
    return await db.query(sql`SELECT * FROM users`)
  })
}

Option 3: always use fully qualified table names:

You can use sql.ident to dynamically generate identifiers/paths:

export async function getUsers(client: string) {
  return await db.query(sql`SELECT * FROM ${sql.ident(client, `users`)}`)
}

With this code, calling getUsers("my_client") would run the SQL:

SELECT * FROM "my_client"."users"