[BUG]: Async SQLite transaction will not rollback

[nonua]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

When using a synchronous sqlite driver with transactions, only a synchronous callback would apply the rollback.

Example:

// rollback will not undo the delete because of async callback
db.transaction(async (tx) => {
   tx.delete(example).where(eq(example.id, exampleId)).run();
   tx.rollback();
})

// rollback will undo
db.transaction((tx) => {
   tx.delete(example).where(eq(example.id, exampleId)).run();
   tx.rollback();
})

this was particularly difficult to debug because the docs describe that this is a valid syntax for any driver (or rather does not specify a difference).

Expected behavior

Either:

• (acceptable) Get a type error for the Promise callback
• (not ideal) Make the async callback able to rollback despite the synchronous behaviour of sqlite
• (best solution) Make async sqlite API work with transactions

[Trugamr]

Running into same issue using sqlite.

[icflorescu]

Running into a similar issue: I just noticed that async transactions aren't automatically rolled back on error, which would be the expected behavior 🥹

[gabrielgrover]

This is definitely an issue. https://github.com/drizzle-team/drizzle-orm/issues/1723#issuecomment-1947752593

[cobbvanth]

This is a major issue that really needs to be fixed...