Model-reload reverts to old-state when saving inside immediate-transaction with SQLite in WAL-mode #18344

Bug Description

I am using sequelize with SQLite for an electron-based application. Since there are 'peaks' of data-trafic I opted for WAL mode (by calling PRAGMA journal_mode=WAL after database creation). Apart from stil needing to 'throttle'/synchronize write-queries manually it works well. But I noticed a side-effect when using transaction (type: Transaction.TYPES.IMMEDIATE, autocommit: true), which is that the model is reloaded incorrectly after modifying/saving within the transaction.

Relevant code portions

const sequelize = new Sequelize('main', '', password, {
	database: 'main',
	password: password,
	dialect: 'sqlite',
	dialectModulePath: '@journeyapps/sqlcipher',
	storage: <path_to_db_file>,
	logging: msg => myLogger(msg),
	define: {
		freezeTableName: true,
		timestamps: false,
	}
})

await sequelize.query('PRAGMA cipher_compatibility = 4');
await sequelize.query("PRAGMA cipher_use_hmac = ON");
await sequelize.query("PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1");
await sequelize.query("PRAGMA cipher_hmac_algorithm = HMAC_SHA1");
await sequelize.query("PRAGMA cipher_page_size = 4096");
await sequelize.query("PRAGMA cipher  = 'aes-256-cbc'");
await sequelize.query("PRAGMA kdf_iter = 256000");
await sequelize.query("PRAGMA cipher_plaintext_header_size = 0");
await sequelize.query(`PRAGMA key = '${password}'`);
await sequelize.query("PRAGMA journal_mode=WAL");

await sequelize.transaction({ type: Transaction.TYPES.IMMEDIATE, autocommit: true}, (transaction) => {
	myModel.save({ transaction });
	// this does not work
	// myModel.reload({ transaction });
})
/*
//Uncomment this to 'fix the reload issue'
.then(() => {
	myModel.reload();
})*/

// Without the 'then clause' <myModel> would at this point have reverted to the old/pre-saved-old-state values

What do you expect to happen?

I would expect the model-instance to at least retain the values it has 'pushed' to the database, rather than reverse back to its old state

What is actually happening?

The model values gets reloaded back to an old/pre-saved version within the database.
So:

• set myModel.valueA to
• call save within immediate-transaction
• myModel.valueA is <old_value>
• call reload after transaction has fully completed
• myModel.valueA is <new_value>

Workaround

• Obviously this is not expected behavior in regards of the 'automatic-reload', but I found it can be 'fixed' by manualy calling reload again. the question is though if this is prone to race-conditions, as I suspect the reload fails because it has (not yet) realised the database had been written to when 'auto-reloaded'.

So if I missed anything obvious/helpfull, or if someone can suggest anything more robust/not prone to race-conditions, it would be verry helpfull.

Environment

[email protected]
[email protected]
├─┬ [email protected]
│ └── [email protected] deduped
├─┬ [email protected]
│ └── [email protected] deduped
├─┬ [email protected]
│ └── [email protected] deduped
└── [email protected]
database: sqlite3
library: @journeyapps/[email protected]

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.