[DOCS] Missing link/section about sync and async APis for sqlite

[8549]

At the end of these pages https://orm.drizzle.team/docs/quick-sqlite/bun and https://orm.drizzle.team/docs/quick-sqlite/better-sqlite3 there's this paragraph:

More on sync and async APIs for sqlite - read here.

The link (or the following section) appear to be missing.

[winghouchan]

@vlad-stohnii looks to be the author according to the blame. Do you remember where it was supposed to link to? 🙏

[eliot-akira]

I saw the missing link in the docs site and wanted to contribute a quick pull request to add it. However, that led me down a rabbit hole - apparently async API for SQLite is still an unsolved question.

It was originally added here:

• Add async API to SQLite #1033

But currently better-sqlite3 and bun:sqlite only provide a synchronous API.

• Make transaction work with async callbacks. WiseLibs/better-sqlite3#1262
  • Worker threads as alternative solution
• Asynchronous SQLite API oven-sh/bun#978
• Adapt read and write paths to async I/O tursodatabase/libsql#3
• Why are there synchronous and asynchronous builds? rhashimoto/wa-sqlite#7
• Support Async SQLite Clients with DrizzleORM nextauthjs/next-auth#8335

This mismatch (?) of wrapping the synchronous interface of SQLite with an async API seems to be the cause of an issue with transaction rollback.

• [BUG]: sqlite transactions can't be async for 4 of 5 implementations #2275
• [BUG]: Async SQLite transaction will not rollback #1472
• [BUG]: Transactions rollback doesn't work #1723
  • Make transaction work with async callbacks. WiseLibs/better-sqlite3#1262

Someone wrote a workaround for that particular issue, but it does seem like this topic deserves its own section in the documentation.

---

This page in the SQLite docs gets to the heart of the matter, describing the tradeoffs of sync/async interface.

• An Asynchronous I/O Module For SQLite

Normally, when SQLite writes to a database file, it waits until the write operation is finished before returning control to the calling application. Since writing to the file-system is usually very slow compared with CPU bound operations, this can be a performance bottleneck.

The asynchronous I/O backend is an extension that causes SQLite to perform all write requests using a separate thread running in the background. Although this does not reduce the overall system resources (CPU, disk bandwidth etc.), it does allow SQLite to return control to the caller quickly even when writing to the database.

Functionality

With asynchronous I/O, write requests are handled by a separate thread running in the background. This means that the thread that initiates a database write does not have to wait for (sometimes slow) disk I/O to occur. The write seems to happen very quickly, though in reality it is happening at its usual slow pace in the background.

Asynchronous I/O appears to give better responsiveness, but at a price. You lose the Durable property. With the default I/O backend of SQLite, once a write completes, you know that the information you wrote is safely on disk. With the asynchronous I/O, this is not the case. If your program crashes or if a power loss occurs after the database write but before the asynchronous write thread has completed, then the database change might never make it to disk and the next user of the database might not see your change.

You lose Durability with asynchronous I/O, but you still retain the other parts of ACID: Atomic, Consistent, and Isolated. Many applications get along fine without the Durability.

[kaaax0815]

@AndriiSherman @eliot-akira Is there any update to this?

[eliot-akira]

The original issue description is out of date now, these mentioned links don't exist anymore:

• https://orm.drizzle.team/docs/quick-sqlite/bun
• https://orm.drizzle.team/docs/quick-sqlite/better-sqlite3

Searching the docs, there is a mention of an async API for SQLite, still without a link.

unlike any other ORM, for synchronous drivers like bun:sqlite we have both async and sync APIs

From: https://orm.drizzle.team/docs/connect-bun-sqlite

---

From what I can see, the solution may be a big effort.

• Implement full support of async API for SQLite

  As described in these issues, there is missing support for async transaction and rollback. [BUG]: sqlite transactions can't be async for 4 of 5 implementations #2275, [BUG]: Async SQLite transaction will not rollback #1472, [BUG]: Transactions rollback doesn't work #1723

  Reportedly in all but one SQLite adaptor (sqlite-proxy), the transaction is not awaited because the wrapped library only has a sync API.

  drizzle-orm/drizzle-orm/src/bun-sqlite/session.ts

  Lines 92 to 99 in 04c9143

  	try {
  	const result = transaction(tx);
  	this.session.run(sql.raw(`release savepoint ${savepointName}`));
  	return result;
  	} catch (err) {
  	this.session.run(sql.raw(`rollback to savepoint ${savepointName}`));
  	throw err;
  	}

  From the workarounds suggested in the issues, this one by @gabrielgrover looks like a good start: [BUG]: Transactions rollback doesn't work #1723 (comment)

  It seems each SQLite adaptor needs an async version of its session class.

• Document how to use async version of SQLite adaptors

• Add any missing link to the new docs page

[kaaax0815]

It seems that libsql supports async transactions

drizzle-orm/drizzle-orm/src/libsql/session.ts

Lines 141 to 148 in 04c9143

	try {
	const result = await transaction(tx);
	await this.session.run(sql.raw(`release savepoint ${savepointName}`));
	return result;
	} catch (err) {
	await this.session.run(sql.raw(`rollback to savepoint ${savepointName}`));
	throw err;
	}

[eliot-akira]

I see, a few lines above it, there is a comment:

drizzle-orm/drizzle-orm/src/libsql/session.ts

Line 98 in 04c9143

// TODO: support transaction behavior

Also noticed in op-sqlite the transaction class is marked as async (extends SQLiteTransaction<'async'>) but the transaction callback is not awaited.

drizzle-orm/drizzle-orm/src/op-sqlite/session.ts

Lines 86 to 94 in 04c9143

	const tx = new OPSQLiteTransaction('async', this.dialect, this.session, this.schema, this.nestedIndex + 1);
	this.session.run(sql.raw(`savepoint ${savepointName}`));
	try {
	const result = transaction(tx);
	this.session.run(sql.raw(`release savepoint ${savepointName}`));
	return result;
	} catch (err) {
	this.session.run(sql.raw(`rollback to savepoint ${savepointName}`));
	throw err;

[kaaax0815]

This is a project so many depend on and the code is insanely weird and unmaintained.

I hope transactions work with libsql otherwise a lot of work is wasted

Edit: It seems that drizzle support async transaction with libsql