Please provide the following:
- SDK Version: 39
- Platforms(Android/iOS/web/all): all
Hi there, folks. I’ve spent hours today working on a library to make expo-sqlite a bit more ergonomic to use. Part of that included attempting to wrap tx.executeSql in promises, to allow it to be easily used inside of async functions without stacking callbacks.
What I realized through experimentation was that this isn’t possible with the current implementation. I’m not totally sure why. My best guess is that at runtime the code that keeps the transaction open must count the number of queries in the transaction queue in the current tick, and if there are none, the transaction closes? That still doesn’t feel totally right to me but it’s as close as I’ve come. As long as executeSql is used with callbacks, the transaction stays open for all requests. See the following example:
let db = openDatabase("wat");
db.transaction(
(tx) => {
tx.executeSql(`create table if not exists test (name text)`, [], () => {
console.log("created the table");
tx.executeSql(`insert into test (name) values ('bob')`, [], () => {
console.log("I inserted");
tx.executeSql(`select * from test`, [], (_, result) => {
console.log("I got result ", result);
tx.executeSql(`delete from test`, [], () => {
console.log("I deleted");
tx.executeSql(`select * from test`, [], (_, result) => {
console.log("I got result ", result);
});
});
});
});
});
},
(err) => {
console.error("There was a problem with the tx", err);
return true;
},
() => {
console.log("all done");
}
);
output:
created the table
I inserted
I got result WebSQLResultSet {
"insertId": undefined,
"rows": WebSQLRows {
"_array": Array [
Object {
"name": "bob",
},
],
"length": 1,
},
"rowsAffected": 0,
}
I deleted
I got result WebSQLResultSet {
"insertId": undefined,
"rows": WebSQLRows {
"_array": Array [],
"length": 0,
},
"rowsAffected": 0,
}
all done
But now let’s add a function that wraps a transaction in a promise:
export function executeAsyncInUnderlyingTx<T>(
tx: SQLTransaction,
query: query
): Promise<T[]> {
let params =
typeof query == "string"
? { text: query, values: [] }
: (query as any).str
? { text: (query as any).str, values: (query as any).vals }
: (query as Statement).toParams();
return new Promise((resolve, reject) => {
const onResult: SQLStatementCallback = (
_: SQLTransaction,
dbResult: SQLResultSet
) => {
console.error(`I got success for query`, params, dbResult.rows);
// @ts-ignore based on https://docs.expo.io/versions/latest/sdk/sqlite/#resultset--objects
resolve(dbResult.rows.array as T[]);
};
const onError: SQLStatementErrorCallback = (
_: SQLTransaction,
error: SQLError
) => {
console.error(`I got an error`, error);
reject(error);
return true;
};
console.log(`I am about to execute`, params);
tx.executeSql(params.text, params.values, onResult, onError);
});
}
And rewrite the tests:
let db = openDatabase("wat");
db.transaction(
(tx) => {
executeAsyncInUnderlyingTx(
tx,
`create table if not exists test (name text)`
)
.then((_) => {
console.log("created the table");
return executeAsyncInUnderlyingTx(
tx,
`insert into test (name) values ('bob')`
);
})
.then((_) => {
console.log("I inserted");
return executeAsyncInUnderlyingTx(tx, `select * from test`);
})
.then((result) => {
console.log("I got result ", result);
return executeAsyncInUnderlyingTx(tx, `delete from test`);
})
.then((_) => {
console.log("I deleted");
return executeAsyncInUnderlyingTx(tx, `select * from test`);
})
.then((result) => {
console.log("I got result ", result);
});
},
(err) => {
console.error("There was a problem with the tx", err);
return true;
},
() => {
console.log("all done");
}
);
And the output shows that only the first query runs. The next query is begun, and then the transaction ends:
I am about to execute Object {
"text": "create table if not exists test (name text)",
"values": Array [],
}
created the table
I am about to execute Object {
"text": "insert into test (name) values ('bob')",
"values": Array [],
}
all done
I got success for query, Object {
"text": "create table if not exists test (name text)",
"values": Array [],
}, WebSQLRows {
"_array": Array [],
"length": 0,
}
May I suggest that the driver code be changed to either slightly depart from the WebSQL standard, and add support for promises of some sort, or that at least the underlying implementation change in a way that would support keeping the transaction open until all queries that have been queued within promise callbacks are completed somehow? Seems like the most straightforward way to do that would be to pass in another argument to db.transaction that would allow the consumer to signal the end of the transaction themselves?