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.
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: 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).
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.
MySQL also supports the execution of a string containing multiple statements separated by semicolon ( ; ) characters. This capability is enabled by special options that are specified either when you connect to the server with mysql_real_connect() or after connecting by calling mysql_set_server_option() .
As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can use the sequelize. query method. By default the function will return two arguments - a results array, and an object containing metadata (such as amount of affected rows, etc).
According to the doc : If you do not provide other arguments than the SQL, raw will be assumed to the true, and sequelize will not try to do any formatting to the results of the query.
Sequelize is a Node. js-based Object Relational Mapper that makes it easy to work with MySQL, MariaDB, SQLite, PostgreSQL databases, and more. An Object Relational Mapper performs functions like handling database records by representing the data as objects.
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
)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With