How can I run multiple raw queries with sequelize in MySql?

Question

I am trying to run a script to drop all the tables from the database before sequelize syncs via sequelize.sync({ force: true });

The script runs with no problems when I run it from the console, the problem happens when I try to run it from my node.js application; MySql returns a parse error.

node.js

var dropAllTables = [
    'SET FOREIGN_KEY_CHECKS = 0;',
    'SET GROUP_CONCAT_MAX_LEN = 32768;',
    'SET @tables = NULL;',
    "SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables FROM information_schema.tables WHERE table_schema = (SELECT DATABASE());",
    "SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);",
    "SELECT IFNULL(@tables, 'SELECT 1') INTO @tables;",
    'PREPARE stmt FROM @tables;',
    'EXECUTE stmt;',
    'DEALLOCATE PREPARE stmt;',
    'SET FOREIGN_KEY_CHECKS = 1;',
    "SET GLOBAL sql_mode = 'STRICT_ALL_TABLES';"
].join(' ');

sequelize.query(dropAllTables, {
    raw: true
}).then(function() {
    return sequelize.sync({ force: true });
}).then(function() {
    console.log('Database recreated!');
    callback();
}, function(err) {
    throw err;
});

error

{ [Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET GROUP_CONCAT_MAX_LEN = 32768; SET @tables = NULL; SELECT GROUP_CONCAT('`', t' at line 1] code: 'ER_PARSE_ERROR', errno: 1064, sqlState: '42000', index: 0, sql: 'SET FOREIGN_KEY_CHECKS = 0; SET GROUP_CONCAT_MAX_LEN = 32768; SET @tables = NULL; SELECT GROUP_CONCAT(\'`\', table_name, \'`\') INTO @tables FROM information_schema.tables WHERE table_schema = (SELECT DATABASE()); SET @tables = CONCAT(\'DROP TABLE IF EXISTS \', @tables); SELECT IFNULL(@tables, \'SELECT 1\') INTO @tables; PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1; SET GLOBAL sql_mode = \'STRICT_ALL_TABLES\';' }

I found nothing regarding multiple raw queries with sequelize in Google nor at sequelize docs page (I looked for a specific parameter for the query method).

EDIT:

I found this thread from an SO clone, where people seem to have the same problem but I can't figure out what the solution was.

By the way, I got this working script from; http://stackoverflow.com/questions/12403662/drop-all-tables/18625545#18625545 — Renato Gama, Sep 26 '14 at 14:59

score 53 · Accepted Answer · answered Sep 27 '14 at 15:35

53

You can pass the multipleStatements option using

new Sequelize(user, pass, db, {
  dialectOptions: {
    multipleStatements: true
  }
});

Anything you put into dialectOptions will be passed on to the underlying connection lib (in this case mysql)

answered Sep 27 '14 at 15:35

Jan Aagaard Meier

28,078
8
95
66

Wow, thank you for this. I can't believe it isn't enabled by default. – ow3n Apr 03 '18 at 13:01
Hi @janaagaardmeier , Does this mean we can send multiple SQL queries in one single network call and get result of all those queries in one single object? If so, can you please guide me through? I am working on a NodeJS app which has ~12 SQL queries which I hope to club into 1 or 2 to decrease the database requests over the network – viditkothari Apr 27 '20 at 07:19

score 8 · Answer 2 · answered Sep 26 '14 at 15:38

8

Depending on the underlying mysql module being used, at least mysql/mysql2 supports the multipleStatements: true connection setting. This will allow you to send multiple queries at once. By default it is disabled for security reasons.

answered Sep 26 '14 at 15:38

mscdex

104,356
15
192
153

Do you know what is the proper way to pass this parameter to the underlying MySql module? Currently I am using `"mysql": "^2.2.0"` and `"sequelize": "^1.7.3"`. – Renato Gama Sep 26 '14 at 16:46
Also I tried uninstalling `mysql` module and installing `mysql2` as a test but it says; 'You need to install mysql package manually'. – Renato Gama Sep 26 '14 at 17:01
you can use `dialectModulePath: 'mysql2'` parameter if you want myql2 driver and mysql dialect with sequelize – Andrey Sidorov May 08 '15 at 03:34
what would the equivalent be in sqlite? – J.Kirk. May 21 '18 at 23:10

score 0 · Answer 3 · edited Feb 11 '20 at 05:26

0

Fix using:

  dialectOptions: {
    multipleStatements: true
  }

edited Feb 11 '20 at 05:26

J. Scott Elblein

4,013
15
58
94

answered Feb 11 '20 at 05:07

leonetosoft

11
1

How can I run multiple raw queries with sequelize in MySql?

node.js

error

EDIT:

3 Answers3