CODEWITHSUNDEEP
databasepostgresqlmulti-tenantnode-postgreskysely
Martin Avramov
Martin Avramov

Reputation: 43

SET search_path on every user with database connection pooling

I have a question which may sound dumb but I have never done this before and have some questions that I cant seem to find the answer of.

My project is in Next.js 14 with Kysely and node-postgres. I am trying to setup a multi tenant app where every tenant gets his own schema. The problem comes where I am making a connection and trying to set the search_path to the specific schema of every tenant but it is being set only once. Meaning if i log in with 2 different accounts only 1 schema is being used.

Here is my Kysely setup:

let pool: Pool | undefined;

if (!pool) {
  pool = new Pool({
    connectionString: process.env.DATABASE_URL,
  });
}

export const adapter = new NodePostgresAdapter(pool, {
  user: "auth_user",
  session: "user_session",
});

export const createPostgresDialect = (tenantId?: number) => {

  return new PostgresDialect({
    pool,
    onCreateConnection: async (connection) => {
      if (tenantId) {
        await connection.executeQuery({
          sql: `SET search_path TO tenant_${tenantId}, public`,
          query: { kind: 'RawNode', sqlFragments: [], parameters: [] },
          parameters: []
        });
      }
    }
  });
};

export const getDB = async(tenantId?: number) => {
  let db = new Kysely<Database & TenantSchema>({
    dialect: createPostgresDialect(tenantId),
    log: ['query', 'error'],
  });  

  return db
}

And this is how I use it:

const auth = await getUser()

export const db = await getDB(auth?.session?.tenant_id)

I then import db and use it in my components and pages for making the queries I need.

Another thing that I cannot understand is why when I do it this way:

const db = await getDB(auth?.session?.tenant_id)

and use this directly in my page or component for example it works. Logged in with 2 different accounts gets separate schemas. But this way I think is not ideal because I have to get the session basically everywhere I want to do queries.

This may be stupid questions but again I have never done this before and I am asking for help. Not sure if this is possible but any help would be appreciated.

Upvotes: 0

Views: 59

Answers (1)

Adlen Afane
Adlen Afane

Reputation: 1532

Dialect (docs) refers to the underlying type of database (MySQL, PG etc). I am not sure you can directly instantiate a schema in it.

To work with schemas, you can refer to the following documentation and especially to the withSchema. So maybe something like:

export const getDB = async(tenantId?: number) => {
  const dialect = new PostgresDialect({});

  const db = new Kysely<Database>({
    dialect,
    log: ['query', 'error'],
  });  

  return db.withSchema(tenantId)
}

Hope this helps

Upvotes: 0

Related Questions