Greetings, I am currently working on developing a front end Single Page Application (SPA) using Typescript. I have chosen to utilize capacitorJS for cross-platform compatibility and have integrated the @capacitor-community/sqlite plugin into my project. While I am relatively experienced with databases, I am new to SQLite specifically.
My progress may be hindered by the use of a specific API over SQLite.
You can find the API documentation here: https://github.com/capacitor-community/sqlite#supported-methods
Unfortunately, the API behavior is not aligning with my expectations.
I am in search of a method that allows me to initiate a transaction, perform multiple operations within that transaction, and then either commit or roll back based on the outcomes. It would be ideal if I could execute read operations within the same transaction or at least outside it while a write transaction is active.
It appears that the wrapper provided by capacitor-community/sqlite is automatically creating a transaction for each individual call I make unless I specify otherwise using a boolean parameter. If I choose to override this default behavior, the responsibility falls on me to include the transaction logic within the SQL statement block.
Initially, I attempted to start a transaction with a statement, carry out additional API calls meant to be part of the transaction, and ultimately finalize with a command to `commit` the transaction.
For example:
async Transaction(actions: () => Promise<void>): Promise<void> {
await SqliteService.Instance.db.execute('BEGIN TRANSACTION;',
try {
await actions();
await SqliteService.Instance.db.execute('COMMIT TRANSACTION;', false); //useTransaction = false
} catch (e) {
await SqliteService.Instance.db.execute('ROLLBACK TRANSACTION;', false); //useTransaction = false
throw e;
}
However, this approach proved unsuccessful as it seemed that the state was not maintained between calls. While certain elements like an open cursor might keep the session active, typically the session should establish and close with each API call.
Upon reflecting on the situation, it seems that submitting a single comprehensive block of SQL statements within a transaction might be necessary to achieve my desired outcome.
Therefore, I am contemplating revising my documentStore API to entail implementing a sqlBuilder type class instead of directly executing statements with SQLite. This way, when I need to perform multiple tasks within a transaction, I could concatenate various statements together.
...
Yet, I am fond of the current interface and transforming the 'write' methods on my documentStore to incorporate a sqlBuilder entity feels like a significant deviation from the original course. For instance:
async Transaction(sqlBuilder: (builder: WriteSqlBuilder<T>) => {statement: string, values: unknown[]} []) {
...
I am curious to know - how do other developers typically handle executing multiple operations (queries, writes) in SQLite within a single transaction?
Thank you!