Raw sql execute does not use schema during migration

[Sata51]

Hi,

We are currently trying to have a custom migration that runs everytime reagardless the current migration step. This migration will hold the last version of some stored procedure. These stored procedure must run using the latest constant from our codebase.

We work with schema for testing purpose and we are doing the following

export async function up(db: Kysely<MyDatabase>): Promise<void> {
await sql`
      CREATE OR REPLACE procedure recompute_ranges()
      LANGUAGE plpgsql
      as $$
      declare
        _current record;
      begin
          select * into _current from ${sql.table('something_entry')} where "id" = b_current_entry_id;
      end;
      $$;`.execute(db)          
}

The compiled query does not contains any information about the schema provided by using:

const db = new Kysely<MyDatabase>({
    dialect: new PostgresDialect({
      pool: new Pool(...)
    }),
    plugins: [new WithSchemaPlugin(schemaName)],
  });

Is there any way to use the right schema ?

Thanks

[Sata51]

For who will hit the same issue, we find a temporary workaround using this function to retrieve the current schema name

export const getSchemaName = (schema: SchemaModule) => {
  let name = 'public';
  schema.createTable('test').$call(b => {
    name = b.toOperationNode().table.table.schema?.name ?? 'public';
  });
  return name;
};

[igalklebanov]

Hey 👋

Once https://github.com/kysely-org/kysely/commit/b481619c633910541bd18aa78570e091669db185 gets released you'll be able to inject non-select queries built with builders into sql template tags like this.

Looks like support for sql.table wasn't implemented in WithSchemaPlugin. It creates a TableNode, so there's no good reason (that I can think of) to not transform it as well and add the schema name.