I have an array of raw SQL expressions I want to pass to knex.
const rawExpressions = ["CREATE TABLE new_table (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL,lastname VARCHAR(30) NOT NULL,email VARCHAR(50))","INSERT INTO new_table (firstname, lastname, email)VALUES (\"Test\", \"Name\", \"[email protected]\")", "INSERT INTO new_table (firstname, lastname, wrongfield) VALUES (\"Another\", \"Name\", \"[email protected]\")"]
knex.transaction(trx => {
const queries: any[] = [];
rawExpressions.forEach(async expression => {
const query = knex.raw(expression).transacting(trx)
queries.push(query)
})
Promise.all(queries)
.then(trx.commit)
.catch(trx.rollback)
}).catch(error => console.log('error', error))
This works if there's no error, but if there's an error (in the rawExpressions above there's a wrongfield) I want it to not commit any part of the expressions and rollback. Instead it performs the first two expressions (creates table and inserts).
Any idea what's going wrong?
It is related to the fact that DDL (Data Definition Language) can't be mixed with DML(Data Manipulation Language) in transactions.
DDL statements and operations with nontransactional engines do not "register" in thd->transaction lists, and thus do not modify the transaction state. Besides, each DDL statement in MySQL begins with an implicit normal transaction commit (a call to end_active_trans()), and thus leaves nothing to modify. However, as noted above for CREATE TABLE .. SELECT, some DDL statements can start a new transaction.
https://dev.mysql.com/doc/internals/en/transactions-notes-on-ddl-and-normal-transaction.html