Feature: Transaction

[michaelpeterlee]

Firstly, thanks sincerely for your contributions.

Q. Are you interested in extending transactions to support the existing SQLite standard?

I am looking for a drop-in replacement for WebSQL/SQLite. This library creates it's own unique interfaces [I mitigate by wrapping SQLocal.exec()].

Attempting to intercept executeSql() to manage transactions is complicated as inferred here: https://github.com/sqlite/sqlite-wasm/issues/42#issuecomment-1756337690

The standard WebSQL/SQLite transaction interface is elegant; simply obtain a transaction object and use that.

db.transaction(
    (tx) => {
        tx.executeSql('INSERT INTO myTable', null, (tx, results) => {
            tx.executeSql('SELECT FROM myTable', null, (tx, results) => {});
        });
    },
    (error) => {}
);

[DallasHoff]

Hi there. Here is the documentation for transactions: https://sqlocal.dallashoffman.com/api/transaction

[michaelpeterlee]

For reference: BEGIN/END TRANSACTION statements offer more functionality than SQLocal.transaction().

Eg.

await SQLocal.exec('BEGIN TRANSACTION')
await SQLocal.exec('INSERT')
await SQLocal.exec('SELECT')
// Do something
await SQLocal.exec('UPDATE')
// throw new Error('Insert and Update will rollback');
await SQLocal.exec('END TRANSACTION')

[DallasHoff]

If you'd prefer, you can open and close the transaction yourself by running it with the sql method.

const { sql } = new SQLocal('database.sqlite3');

async function addTagToLatestNote(newTagLabel: string) {
  await sql`BEGIN TRANSACTION`;

  try {
    const [{id: tagId}] = await sql`INSERT INTO tag (label) VALUES (${newTagLabel}) RETURNING id`; 
    const [{id: noteId}] = await sql`SELECT id FROM note ORDER BY createdAt desc LIMIT 1`;
    await sql`INSERT INTO noteTag (tagId, noteId) VALUES (${tagId}, ${noteId})`;
  } catch (err) {
    await sql`ROLLBACK`;
    return;
  }

  await sql`END`;
}