Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Raw sql execute does not use schema during migration #761

Open
Sata51 opened this issue Nov 7, 2023 · 2 comments · May be fixed by #826
Open

Raw sql execute does not use schema during migration #761

Sata51 opened this issue Nov 7, 2023 · 2 comments · May be fixed by #826
Labels
built-in plugin Related to a built-in plugin enhancement New feature or request

Comments

@Sata51
Copy link

Sata51 commented Nov 7, 2023

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
Copy link
Author

Sata51 commented Dec 31, 2023

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
Copy link
Member

igalklebanov commented Dec 31, 2023 •
edited

Hey 👋

Once b481619 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.

@igalklebanov igalklebanov added enhancement New feature or request built-in plugin Related to a built-in plugin labels Dec 31, 2023
@igalklebanov igalklebanov linked a pull request Jan 1, 2024 that will close this issue
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
built-in plugin Related to a built-in plugin enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

feat: add schema to TableNodes within RawNodes @ WithSchemaPlugin. igalklebanov/kysely
2 participants
@igalklebanov @Sata51