I'm currently facing an issue with inserting a list of exercises into an sqlite database. The app is built on express JS and I am utilizing sqlite3 and knex to handle interactions with the DB. My goal is to add a set of exercises into the table exercisesDB, but only if the exercise name does not already exist in the table using a knex transaction:
await knex.transaction(async (trx) => {
for (const exercise of exercisesToAdd) {
await trx('exercisesDB').insert(exercise).onConflict('name').ignore()
}
})
However, I encountered an error stating: [Error: insert into exercisesDB [data] on conflict (name) do nothing - SQLITE_CONSTRAINT: UNIQUE constraint failed: exercisesDB.id]
It appears that knex is checking the id constraint (which I did not include in the exercisesToAdd list) instead of the name constraint
Could this be a potential issue with knex?
To address the problem, I attempted to add a `then` and `catch` statement within the transaction as follows:
await knex.transaction(async (trx) => {
exercisesToAdd.forEach(
async (exercise) => {
await trx('exercisesDB').insert(exercise).onConflict('name').ignore().then(trx.commit).catch(trx.rollback)
}
)
})