Knex - MySQL How could I execute multiple statements in a single JavaScript string?

Question

I'm trying to carry out some SQL instructions inside my migrations file by using knex. To get more flexibility, I'd like to put all my related SQL inside a single string. The problem is: When I have two or more statements inside the string, MySQL isn't able to understand it and throws the following error: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server..."

I believe that happens due to \n, \s, and \t characters which are inside my raw result. I've tried .replace(/\n/g, '') and .replace(/\t/g, ''), however it didn't make any effect on it.

In this case, is there some way to execute my RAW string containing multiple statements?

Thanks a lot.

My original method:

// Fail
    exports.up = async (knex) => {
      try {
        console.info('Migrating CLIENT...');
        return await knex.raw(
          `
          DELETE FROM monthly_consumption;
          DELETE FROM project;
          DELETE FROM project_client;
          DELETE FROM client;
          ALTER TABLE client DROP PRIMARY KEY;
          ALTER TABLE client ADD id BIGINT auto_increment;
          ALTER TABLE client ADD CONSTRAINT client_pk PRIMARY KEY (id);
        `
            .replace(/\n/g, '')
            .replace(/\t/g, ' ')
        );
      } catch (err) {
        console.error('******** ERROR ********\n', err);
        throw new Error(err);
      }
    };

On the other hand, If I run the following, it works, that is, if it contains only one statement, it's gonna work fine.

// Success
exports.up = async (knex) => {
  try {
    console.info('Migrating CLIENT...');
    return await knex.raw(
      `
      DELETE FROM monthly_consumption;
    `
        .replace(/\n/g, '')
        .replace(/\t/g, ' ')
    );
  } catch (err) {
    console.error('******** ERROR ********\n', err);
    throw new Error(err);
  }
};

I tried to put all that in a single line too, but it fails again:

// Fail
exports.up = async (knex) => {
  try {
    console.info('Migrating CLIENT...');
    return await knex.raw(
      `
      DELETE FROM monthly_consumption; DELETE FROM project;
    `
        .replace(/\n/g, '')
        .replace(/\t/g, ' ')
    );
  } catch (err) {
    console.error('******** ERROR ********\n', err);
    throw new Error(err);
  }
};

Original SQL output from the last one example:

migration failed with error: Error:       DELETE FROM monthly_consumption; DELETE FROM project;     - ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELETE FROM project' at line 1

score 2 · Answer 1 · answered Aug 18 '20 at 14:04

2

I found a solution. It was related to MySQL itself. We need to enable multiple statement feature. Inside the knexfile.js, I simply inserted:

 multipleStatements: true

At last, it worked perfectly.

answered Aug 18 '20 at 14:04

Rogério Oliveira

414
1
4
15

2

with migrations that should be fine, but remember that it may make your app to be vulnerable for sql-injection, because values are not sent to DB as bindings anymore https://stackoverflow.com/questions/23266854/node-mysql-multiple-statements-in-one-query/23267627 – Mikael Lepistö Aug 19 '20 at 07:19
Hey @MikaelLepistö That's it. I'm aware of that. For a while, that was the best solution for me. I've plans to implement some mechanisms to avoid sql-injection in my app. Thanks for your feedback. – Rogério Oliveira Sep 01 '20 at 14:11

Knex - MySQL How could I execute multiple statements in a single JavaScript string?

1 Answers1