useReturning is not correctly set for the SQLite DB dialect

[WalkingPizza]

Bug report

Required System information

• Node.js version: v16.16.0
• Yarn version: 1.22.19
• Strapi version: 4.3.4
• Database: SQLite
• Operating system: MacOS 12

Describe the bug

The SQLite Dialect class does not definite a useReturning method, which defaults the value to false even though SQLite does support RETURNING statements.

This causes a series of issues, including insert queries not returning IDs appropriately from the query builder.

Steps to reproduce the behavior

1. Launch a test Strapi instance with SQLite.
2. Edit the entity-manager.js file to console.log the result of await this.createQueryBuilder(uid).insert(dataToInsert).execute().
3. Try calling strapi.db.query(<UID>).createMany({ data: [<ENTRIES>] }).
4. Notice how the line from step 2 is only returning one of the multiple IDs it should be returning.

Expected behavior

useReturning should be defined so that it returns true.

[not-vainglorious]

Can you check it out again? Im getting node-sqlite3 does not currently support RETURNING clause` with 4.3.8 while using sqlite in tests.

[WalkingPizza]

Can you check it out again? I`m getting node-sqlite3 does not currently support RETURNING clause with 4.3.8 while using sqlite in tests.

Are you using better-sqlite3 or the legacy sqlite3 package?

[not-vainglorious]

Can you check it out again? I`m getting node-sqlite3 does not currently support RETURNING clause with 4.3.8 while using sqlite in tests.

Are you using better-sqlite3 or the legacy sqlite3 package?

As referenced in docs. I'm using sqlite3. Should i change it to better-sqlite3?

[WalkingPizza]

I would, yes, as it is generally better than sqlite3!
@alexandrebodin should we update that piece of documentation to mention better-sqlite3 instead?

[not-vainglorious]

I would, yes, as it is generally better than sqlite3! @alexandrebodin should we update that piece of documentation to mention better-sqlite3 instead?

Also you can change this example
This code is old
const dbSettings = strapi.config.get("database.connections.default.settings");
Now seems like it should look like this
const dbSettings = strapi.config.get('database.connection.connection');

async function cleanupStrapi() {
    const dbSettings = strapi.config.get("database.connections.default.settings");
  
    //close server to release the db-file
    await strapi.server.httpServer.close();
  
    //delete test database after all tests have completed
    if (dbSettings && dbSettings.filename) {
      const tmpDbFile = `${__dirname}/../${dbSettings.filename}`;
      if (fs.existsSync(tmpDbFile)) {
        fs.unlinkSync(tmpDbFile);
      }
    }
    // close the connection to the database
    await strapi.db.connection.destroy();
  } 

I changed to next code

async function cleanupStrapi() {
    const dbSettings = strapi.config.get('database.connection.connection');

    // close server to release the db-file
    await strapi.server.httpServer.close();

    // delete test database after all tests have completed
    if (dbSettings && dbSettings.filename) {
        if (fs.existsSync(dbSettings.filename)) {
            fs.unlinkSync(dbSettings.filename);
        }
    }

    // close the connection to the database
    await strapi.db.connection.destroy();
}

P.S. Thanks. With better-sqlite3 everything is fine.

[alexandrebodin]

ping @pwizla Could you have a look at this :)

[strapi-bot]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/issue-4-3-8-after-update-sqlite-error-near-returnin/22172/5