Postgres SearchPath

[pratikpparikh]

How do i apply searchpath to my postgres connection?

https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH

Regards, Pratik Parikh

[igalklebanov]

Hey 👋

Kysely supports explicitly stating schema names. It also provides .withSchema(schema) as a shortcut to prepend given schema name to all references.

For further details, refer to https://github.com/koskimas/kysely/blob/master/recipes/schemas.md.

Not being explicit about schemas can be a footgun as far as type-safety goes. Imagine having 2 tables with the same name but a different structure in different schemas.

You can perform actions on newly created connections:

new PostgresDialect({
  ...
  onCreateConnection: async (connection) => {
    await connection.executeQuery(CompiledQuery.raw('set search_path to user,moms_spaghetti'))
  }
})

Alternatively, you can extend the built-in postgres dialect and override PostgresDriver.acquireConnection as follows:

  async acquireConnection(): Promise<DatabaseConnection> {
    const client = await this.#pool!.connect()
    let connection = this.#connections.get(client)

    if (!connection) {
      connection = new PostgresConnection(client, {
        cursor: this.#config.cursor ?? null,
      })
      this.#connections.set(client, connection)

      await connection.executeQuery(CompiledQuery.raw('set search_path to user,moms_spaghetti')) // hard-coded, or get it from your dialect's config.

      // The driver must take care of calling `onCreateConnection` when a new
      // connection is created. The `pg` module doesn't provide an async hook
      // for the connection creation. We need to call the method explicitly.
      if (this.#config?.onCreateConnection) {
        await this.#config.onCreateConnection(connection)
      }
    }

    return connection
  }

@koskimas, is this something worth adding to the core as optional prop @ PostgresDialect? e.g. schemaSearchPath: string[]..

[koskimas]

I don't think this should be an option since it requires extra query on each connection. We already have onCreateConnection. Although I haven't given the onCreateConnection hook much thought lately. We should probably make running queries on the connection cleaner. Or at least document it better.