WAL mode with typeorm stopped working from version 0.3.18

[CosminCraciun]

Describe the bug When creating a OrmDataSource with journalMode WAL, I get the error: Error: Run: RunSQL: run: cannot change into wal mode from within a transaction

To Reproduce This piece of code fails.

DatabaseService.OrmDataSource = new DataSource({
             type: 'capacitor',
             driver: sqlite,
             database: DATABASE_NAME,
             journalMode: 'WAL',
             logging: ['error', 'warn'],
             mode: 'no-encryption',
             entities: [ ...  ],        
             synchronize: false, 
             migrationsRun: false 
         } as CapacitorConnectionOptions);
await DatabaseService.OrmDataSource.initialize();

I looked inside the orm source code and found the issue inside CapacitorDriver.ts:

     await connection.run(
                `PRAGMA journal_mode = ${this.options.journalMode}`
            )
        }

it should be replaced with

     await connection.run(
                `PRAGMA journal_mode = ${this.options.journalMode}`, undefined, false
            )
        }

.run will run with transaction set to true as a default. I created an issue in typeOrm package: https://github.com/typeorm/typeorm/issues/10680

You can replicate it directly by running without typeorm:

 const conn = await sqlite.createConnection(DATABASE_NAME, false, 'no-encryption', 1, false);
 await conn.open();
 await conn.run(`PRAGMA foreign_keys = ON`);

 await conn.run(`PRAGMA journal_mode = WAL`); // throws error
//  await conn.run(`PRAGMA journal_mode = WAL`, undefined, false); // won't throw error

[jepiqueau]

@CosminCraciun this is a known error. Someone has modify the driver wrongly it has been reported to the typeOrm team. See the close issue @capacitor-community/sqlite #512 PRAMA table_info throws an error. I suggested a script to postinstall in waiting the fix from the typeOrm team.

[CosminCraciun]

I had already included that in the post-install. Also the error only occurs in WAL mode, not on any of the others. The error does not occur in CapacitorQueryRunner.js. It's actually in CapacitorDriver.js, when journal mode is set. That calls directly .run from the sqlite driver, rather than using the capacitor query runner

[jepiqueau]

@CosminCraciun you could try to modify the script modify-typeorm.cjs like this

const fs = require('fs');

const modTypeOrmCapacitor = (filePath, lineToModify, replacementText) => {
  fs.readFile(filePath, 'utf8', (err, data) => {
    if (err) {
      console.error('Error reading file:', err);
      return;
    }

    // Split data by line
    const lines = data.split('\n');

    // Modify the specific line
    if (lines.length >= lineToModify) {
      lines[lineToModify - 1] = replacementText; // Line numbers are 1-based
    } else {
      console.error('Line number to modify is out of range.');
      return;
    }

    // Join lines back together
    const modifiedData = lines.join('\n');

    // Write the modified data back to the file
    fs.writeFile(filePath, modifiedData, 'utf8', (err) => {
      if (err) {
        console.error('Error writing file:', err);
        return;
      }

      console.log('File modified successfully.');
    });
  });
}
/* Moddify CapacitorQueryRunner.js */
let filePath = './node_modules/typeorm/driver/capacitor/CapacitorQueryRunner.js';
let lineToModify = 61;
let replacementText = '    else if (["INSERT", "UPDATE", "DELETE"].indexOf(command) !== -1) {';

modTypeOrmCapacitor(filePath, lineToModify, replacementText)

/* Moddify CapacitorDriver.js */
filePath = './node_modules/typeorm/driver/capacitor/CapacitorDriver.js';
lineToModify = 62;
replacementText = '        await connection.execute(`PRAGMA foreign_keys = ON`);';

modTypeOrmCapacitor(filePath, lineToModify, replacementText)

/* Moddify CapacitorDriver.js */
filePath = './node_modules/typeorm/driver/capacitor/CapacitorDriver.js';
lineToModify = 65;
replacementText = '            await connection.execute(`PRAGMA journal_mode = ${this.options.journalMode}`);';

modTypeOrmCapacitor(filePath, lineToModify, replacementText)

if it works you must report the error on the typeorm package

[CosminCraciun]

There is a problem with modifying CapacitorDriver.js in this way. Because the operations are asyncronous, the modifications are done in parallel, so we hit a concurency issue. I end up with just the second modification instead of both. Also, looking into the API section, .execute still does stuff in a transaction as default. I did some changes to the script:

const fs = require('fs');

const modTypeOrmCapacitor = (filePath, lineToModify, replacementText) => {
  fs.readFile(filePath, 'utf8', (err, data) => {
    if (err) {
      console.error('Error reading file:', err);
      return;
    }

    // Split data by line
    const lines = data.split('\n');

    // Modify the specific line
    if (lines.length >= lineToModify) {
      lines[lineToModify - 1] = replacementText; // Line numbers are 1-based
    } else {
      console.error('Line number to modify is out of range.');
      return;
    }

    // Join lines back together
    const modifiedData = lines.join('\n');

    // Write the modified data back to the file
    fs.writeFile(filePath, modifiedData, 'utf8', (err) => {
      if (err) {
        console.error('Error writing file:', err);
        return;
      }

      console.log('File modified successfully.');
    });
  });
}

const correctBugInCapacitorDriver = (file) => {
    if (fs.existsSync(file)) {
        fs.readFile(file, 'utf8', function (err, data) {
            if (err) {
                return console.error(err);
            }

            const index = data.indexOf('await connection.run(`PRAGMA foreign_keys = ON`);');
            if (index === -1) {
                console.warn('Line not found. Package probably fixed.');
                return;
            }

            var result = data.replace(
                'await connection.run(`PRAGMA foreign_keys = ON`);',
                'await connection.execute(`PRAGMA foreign_keys = ON`);'
            );
            result = result.replace(
                'await connection.run(`PRAGMA journal_mode = ${this.options.journalMode}`);',
                'await connection.execute(`PRAGMA journal_mode = ${this.options.journalMode}`, false);'
            );

            fs.writeFile(file, result, 'utf8', function (err) {
                if (err) return console.error(err);
            });
        });
    } else {
        utils.warn(`Couldn't find file ${file}`);
    }
}

/* Moddify CapacitorQueryRunner.js */
let filePath = './node_modules/typeorm/driver/capacitor/CapacitorQueryRunner.js';
let lineToModify = 61;
let replacementText = '    else if (["INSERT", "UPDATE", "DELETE"].indexOf(command) !== -1) {';

modTypeOrmCapacitor(filePath, lineToModify, replacementText)

/* Moddify CapacitorDriver.js */
correctBugInCapacitorDriver('./node_modules/typeorm/driver/capacitor/CapacitorDriver.js');
correctBugInCapacitorDriver('./node_modules/typeorm/browser/driver/capacitor/CapacitorDriver.js');

The problem is, I'm still getting the error on WAL. I'm still investigating why. I've tried removing the lines alltogether instead of replacing, and I still get the error. Which doesn't make sense at all.

[CosminCraciun]

Apparently, angular was caching my node_modules files. The script above works fine. So we need the .execute(..., false) and editing the file in one go.

[jepiqueau]

@CosminCraciun ok i modify the script based on your proposal

const fs = require('fs');

const modTypeOrmCapacitor = (filePath, lineToModify, replacementText) => {
  fs.readFile(filePath, 'utf8', (err, data) => {
    if (err) {
      console.error('Error reading file:', err);
      return;
    }

    // Split data by line
    const lines = data.split('\n');

    // Modify the specific line
    if (lines.length >= lineToModify) {
      lines[lineToModify - 1] = replacementText; // Line numbers are 1-based
    } else {
      console.error('Line number to modify is out of range.');
      return;
    }

    // Join lines back together
    const modifiedData = lines.join('\n');

    // Write the modified data back to the file
    fs.writeFile(filePath, modifiedData, 'utf8', (err) => {
      if (err) {
        console.error('Error writing file:', err);
        return;
      }

      console.log('File modified successfully.');
    });
  });
}
/* Moddify CapacitorQueryRunner.js */
let filePath = './node_modules/typeorm/driver/capacitor/CapacitorQueryRunner.js';
const correctBugInCapacitorQueryRunner = (file) => {
  if (fs.existsSync(file)) {
    fs.readFile(file, 'utf8', function (err, data) {
        if (err) {
            return console.error(err);
        }

        const index = `"DROP",`
        if (index === -1) {
            console.warn('Line not found. Package probably fixed.');
            return;
        }

        var result = data.replace(
          `    "DROP",`,
          `    "DROP",
               "PRAGMA"`

        );
        result = result.replace(
            'else if (["INSERT", "UPDATE", "DELETE", "PRAGMA"].indexOf(command) !== -1) {',
            'else if (["INSERT", "UPDATE", "DELETE"].indexOf(command) !== -1) {'
        );

        fs.writeFile(file, result, 'utf8', function (err) {
            if (err) return console.error(err);
        });
    });
  } else {
      utils.warn(`Couldn't find file ${file}`);
  }

}
/* Moddify CapacitorDriver.js */
const correctBugInCapacitorDriver = (file) => {
  if (fs.existsSync(file)) {
      fs.readFile(file, 'utf8', function (err, data) {
          if (err) {
              return console.error(err);
          }

          const index = data.indexOf('await connection.run(`PRAGMA foreign_keys = ON`);');
          if (index === -1) {
              console.warn('Line not found. Package probably fixed.');
              return;
          }

          var result = data.replace(
              'await connection.run(`PRAGMA foreign_keys = ON`);',
              'await connection.execute(`PRAGMA foreign_keys = ON`, false);'
          );
          result = result.replace(
              'await connection.run(`PRAGMA journal_mode = ${this.options.journalMode}`);',
              'await connection.execute(`PRAGMA journal_mode = ${this.options.journalMode}`, false);'
          );

          fs.writeFile(file, result, 'utf8', function (err) {
              if (err) return console.error(err);
          });
      });
  } else {
      utils.warn(`Couldn't find file ${file}`);
  }
}
correctBugInCapacitorQueryRunner('./node_modules/typeorm/driver/capacitor/CapacitorQueryRunner.js');
correctBugInCapacitorQueryRunner('./node_modules/typeorm/browser/driver/capacitor/CapacitorQueryRunner.js');
correctBugInCapacitorDriver('./node_modules/typeorm/driver/capacitor/CapacitorDriver.js');
correctBugInCapacitorDriver('./node_modules/typeorm/browser/driver/capacitor/CapacitorDriver.js');

i remove the cache files of the browser close it and reopen it i run

npm run build --force
npm run dev

Now it is working

[jepiqueau]

@CosminCraciun Sorry i did not see your previous answer yes it was a cash issue. I add PRAMA to the list for execute command. I test it on a react-ionic-vite-app

[CosminCraciun]

Perfect! Thanks a lot! For me, I actually had to delete the .angular folder to remove the cache.

[jepiqueau]

@CosminCraciun OK i close the issue then