Can't run two queries in same transaction with promises

[andrewrothman]

I'd love to use SQLite in my React Native project, but I'm experiencing this issue:

sqlite.ts

const { default: SQLite } = require("react-native-sqlite-2");

class Sqlite {
	static db: any;
	
	static transact(func: (tx: any) => Promise<void>): Promise<void> {
		return new Promise((resolve, reject) => {
			this.db.transaction((tx: any) => {
				func(tx).then(() => {
					resolve();
				}).catch(e => {
					reject(e);
				});
			});
		});
	}
	
	static runQuery(tx: any, query: string, args: any[] = []): Promise<{ rows: any[] }> {
		return new Promise((resolve, reject) => {
			console.log("executing query", query, args);
			tx.executeSql(query, args, (tx: any, res: any) => {
				console.log("success");
				resolve({
					rows: res.rows,
				});
			 }, () => {
				 console.error("error");
				 reject();
			 });
		});
	}
	
	static async init() {
		this.db = SQLite.openDatabase('test.db', '1.0', 'Test Database', 2 * 1024 * 1024);
		
		console.log("init");
		
		await this.transact(async tx => {
			console.log("running first");
			await this.runQuery(tx, 'CREATE TABLE IF NOT EXISTS LOGS2 (id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT)');

			console.log("running second");
			await this.runQuery(tx, 'SELECT * FROM LOGS2');
			console.log("done with both");
		});
		
		console.log("done with tx");
	}
}

export default Sqlite;

index.ts

import Sqlite from "./sqlite.ts"
Sqlite.init().then(() => {console.log("done with init")}).catch(e => {console.error("error", e)});

Log output is:

init
sqlite.ts:39 running first
sqlite.ts:20 executing query CREATE TABLE IF NOT EXISTS LOGS2 (id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT) []
sqlite.ts:22 success
sqlite.ts:41 running second
sqlite.ts:20 executing query INSERT INTO LOGS2 (timestamp) VALUES (:timestamp) 

But if I remove the second query:

init
sqlite.ts:39 running first
sqlite.ts:20 executing query CREATE TABLE IF NOT EXISTS LOGS2 (id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT) []
sqlite.ts:22 success
sqlite.ts:46 done with tx
App.tsx:84 test

Note that done with both is missing. Looks like an issue with promises, because I can run these two fine using the callback style. Maybe the transaction block closes before the others are run? By the way, I'm running this on Android.

Is there something I'm doing wrong here?

[andrewrothman]

For now, I had to omit support for transactions by doing this instead:

static runQuery(query: string, args: any[] = []): Promise<{ res: any }> {
    return new Promise((resolve, reject) => {
        this.db.transaction((tx: any) => {
            tx.executeSql(query, args, (tx: any, res: any) => resolve(res), reject);
        });
    });
}

...

console.log("running first");
await this.runQuery('CREATE TABLE IF NOT EXISTS LOGS2 (id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT)');
console.log("running second");
await this.runQuery('SELECT * FROM LOGS2', []);
console.log("done with both");

This achieves the correct response. I tried looking in the source code, but don't really see much for the term "transaction". Could you point me down the right path to looking more into this issue?

Thanks so much, Andrew

[blabadi]

here is an exmaple of how I managed to get it done in case someone is interested:

// sqlite transactions don't play with promises nicely.
// the fn param here shouldn't use promises, see `save` method as example.
const transactional = async (fn: (tx: Transaction) => void) => {
  const db = await getDb();
  const x = new Promise((resolve, reject) => {
    const trans = db.transaction(fn, (e) => {
      console.log('transaction failed ')
      reject(e)
    },
      () => {
        console.log('transaction finished ok')
        resolve();
      });
  });
  await x;
};

and it's called like this:

async function save(image: FileMetadata) {
  try {
    await transactional((tx) => {
      const last  = (tx: Transaction) => {
        // ... omitted code 
        const statement = `INSERT INTO table...`;
        tx.executeSql(statement, vals, () => { }, sqliteErrorLogger);
      }

      const second = (tx: Transaction) => {
        // ... omitted code 
        const statement = `INSERT INTO table...`;
        tx.executeSql(statement, vals, last, sqliteErrorLogger);
      }

      tx.executeSql(`INSERT ....`, params , second, sqliteErrorLogger);
    })
  } catch (err) {
    console.error('failed to save image', err)
  }
}

so inside the transaction scope you cannot use promises because sqlite will commit the transaction as far as I understood it only supports call backs (could be a bug or misunderstanding from my end) at least that's what I observed in my experiments

and just a note the error call back is the most important thing to know what is going on: sqliteErrorLogger otherwise you will not get any errors back from sqlite unless you open logcat in adb

[ezze]

@blabadi Am right, that you're still not able to pass the results of the first SELECT query to the chained second one?

[zanyar3]

No solution has been found for this issue yet ?