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.

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

Knex' default implementation of _setNullableState fails for tables in non-standard schemas #6024

Open
OliverHenrichs opened this issue Mar 6, 2024 · 0 comments · May be fixed by #6025
Open

Comments

@OliverHenrichs
Copy link

OliverHenrichs commented Mar 6, 2024

Environment

Knex version: 3.1.0
Database + version: Mssql 2019-latest
OS: N/A

@smorey2

Bug

  1. What I get:
    I have an MsSql-table 'primary_table' in a non-standard schema called 'testSchema'.
    The table has a non-nullable column 'id_not_nullable'.
    I execute await knex.schema.withSchema("testSchema").table(tableName, (table) => { table.setNullable('id_not_nullable'); }); and it fails.

  2. What it should do:
    Above code removes the 'not_nullable'-flag in the table's column.

  3. Error message:
    .setNullable: Column [id_not_nullable] does not exist in table [testSchema].[primary_table].

  4. Reduced test code

const knex = require('knex')({
  client: 'mssql',
  connection: { // knex.js' mssql docker config
    user: 'sa',
    password: 'S0meVeryHardPassword',
    server: 'localhost',
    port: 21433,
    database: 'knex_test',
  }
});

const schemaName = "testSchema";
const tableName = "primary_table";

/**
 * Prior to running create schema "testSchema" in DB (Knex doesn't support this for mssql)
 * */
async function main() {
  try {
    await knex.schema.withSchema(schemaName).dropTable(tableName);
  } catch {
    // on first run this does not work because table does not exist.
  }
  await knex.schema.withSchema(schemaName).createTable(tableName, (table) => {
    table.integer('id_nullable').nullable();
    table.integer('id_not_nullable').notNull();
  });
  try {
    const builder = knex.schema.withSchema("testSchema").table(tableName, (table) => {
      table.setNullable('id_not_nullable');
    });
    const queries = await builder.generateDdlCommands();
    console.log(queries);
    await builder;
  } catch (e) {
    /**
     * The error will say:
     *        .setNullable: Column [id_not_nullable] does not exist in table [testSchema].[primary_table].
     *
     * SetNullable does not work because the schema is actually not used in the resulting sql.
     * @see tablecompiler.js -> _setNullableState, lin 318ff read:
     *
     *          return this.client
     *           .queryBuilder()
     *           .from(this.tableNameRaw)
     *           .columnInfo(column)
     *
     * It should be including `.withSchema(this.schemaNameRaw)`:
     *
     *          return this.client
     *           .queryBuilder()
     *           .withSchema(this.schemaNameRaw) // if schema is undefined, this does not hurt.
     *           .from(this.tableNameRaw)
     *           .columnInfo(column)
     *
     *  which works.
     *  I will add a PR that does exactly this. 
     *  Should also be a thing in all connections that do not implement their own _setNullableState
    */
    console.log(e);
  }
  return 0;
}

main();

OliverHenrichs added a commit to OliverHenrichs/knex that referenced this issue Mar 6, 2024
@OliverHenrichs OliverHenrichs linked a pull request Mar 6, 2024 that will close this issue
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant