sqlite: bug: cannot create tables with standard sql statements

Describe the bug I’m trying to create tables with standard sql statements as described in the documentation. But sqlite responds with {changes: {changes: 0}} and the table is not created. There is no exception thrown.

When I try to read or write to the table, an exception is thrown stating, that the table does not exist.

SelectSQL: queryAll: no such table: test

To Reproduce

   // example: database creation with standard SQLite statements 
    const ret = await sqlite.checkConnectionsConsistency();
    const isConn = (await sqlite.isConnection("db_tab3")).result;
    let db: SQLiteDBConnection
    if (ret.result && isConn) {
      db = await sqlite.retrieveConnection("db_tab3");
    } else {
      db = await sqlite.createConnection("db_tab3", false, "no-encryption", 1);
    }
    await db.open();
    const query = `
    CREATE TABLE IF NOT EXISTS test (
      id INTEGER PRIMARY KEY NOT NULL,
      name TEXT NOT NULL
    );
    `
    // res is `{changes: {changes: 0}}`
    const res = await db.execute(query);
    if(res.changes && res.changes.changes && res.changes.changes < 0) {
      throw new Error(`Error: execute failed`);
    }

    const statement = 'SELECT * FROM test;';
    const values = [];
    // The following line throws exception: "SelectSQL: queryAll: no such table: test"
    const result = await db.query(statement, values);
    console.log('select result', result.values);

    await sqlite.closeConnection("db_tab3");

Expected behavior The table should be created.

Additional context Tested in browser (Safari 15.4)

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 18 (8 by maintainers)

Most upvoted comments

@tobiasmuecksch It is not mandatory, you can do it at your discretion when the database got quite a number of changes and that if there is a crash of the browser or the app these changes would have already been stored in localforage store

@tobiasmuecksch in my code above you can replace

await sqlite.closeConnection("db_tab3");

by

if(platform === 'web') {
   // save the db to store
   await sqlite.saveToStore('db_tab3');
}

and continue working with the open connection