drizzle-team
[DOCS] Missing link/section about sync and async APis for sqlite
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.
@vlad-stohnii looks to be the author according to the blame. Do you remember where it was supposed to link to? 🙏
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:
- https://github.com/drizzle-team/drizzle-orm/pull/1033
But currently better-sqlite3 and bun:sqlite only provide a synchronous API.
- https://github.com/WiseLibs/better-sqlite3/issues/1262
- Worker threads as alternative solution
- https://github.com/oven-sh/bun/issues/978
- https://github.com/tursodatabase/libsql/issues/3
- https://github.com/rhashimoto/wa-sqlite/issues/7
- https://github.com/nextauthjs/next-auth/issues/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.
- https://github.com/drizzle-team/drizzle-orm/issues/2275
- https://github.com/drizzle-team/drizzle-orm/issues/1472
- https://github.com/drizzle-team/drizzle-orm/issues/1723
- https://github.com/WiseLibs/better-sqlite3/issues/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.
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.
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. #2275, #1472, #1723
Reportedly in all but one SQLite adaptor (
sqlite-proxy), the transaction is not awaited because the wrapped library only has a sync API.https://github.com/drizzle-team/drizzle-orm/blob/04c91434c7ac10aeb2923efd1d19a7ebf10ea9d4/drizzle-orm/src/bun-sqlite/session.ts#L92-L99
From the workarounds suggested in the issues, this one by @gabrielgrover looks like a good start: https://github.com/drizzle-team/drizzle-orm/issues/1723#issuecomment-1950721628
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
It seems that libsql supports async transactions
https://github.com/drizzle-team/drizzle-orm/blob/04c91434c7ac10aeb2923efd1d19a7ebf10ea9d4/drizzle-orm/src/libsql/session.ts#L141-L148
I see, a few lines above it, there is a comment:
https://github.com/drizzle-team/drizzle-orm/blob/04c91434c7ac10aeb2923efd1d19a7ebf10ea9d4/drizzle-orm/src/libsql/session.ts#L98
Also noticed in op-sqlite the transaction class is marked as async (extends SQLiteTransaction<'async'>) but the transaction callback is not awaited.
https://github.com/drizzle-team/drizzle-orm/blob/04c91434c7ac10aeb2923efd1d19a7ebf10ea9d4/drizzle-orm/src/op-sqlite/session.ts#L86-L94
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
Hey everyone!
I've created this message to send in a batch to all opened issues we have, just because there are a lot of them and I want to update all of you with our current work, why issues are not responded to, and the amount of work that has been done by our team over ~8 months.
I saw a lot of issues with suggestions on how to fix something while we were not responding – so thanks everyone. Also, thanks to everyone patiently waiting for a response from us and continuing to use Drizzle!
We currently have 4 major branches with a lot of work done. Each branch was handled by different devs and teams to make sure we could make all the changes in parallel.
First branch is drizzle-kit rewrite
All of the work can be found on the alternation-engine branch. Here is a PR with the work done: https://github.com/drizzle-team/drizzle-orm/pull/4439
As you can see, it has 167k added lines of code and 67k removed, which means we've completely rewritten the drizzle-kit alternation engine, the way we handle diffs for each dialect, together with expanding our test suite from 600 tests to ~9k test units for all different types of actions you can do with kit. More importantly, we changed the migration folder structure and made commutative migrations, so you won't face complex conflicts on migrations when working in a team.
What's left here:
- We are finishing handling defaults for Postgres, the last being geometry (yes, we fixed the
sridissue here as well). - We are finishing commutative migrations for all dialects.
- We are finishing up the command, so the migration flow will be as simple as
drizzle-kit upfor you.
Where it brings us:
- We are getting drizzle-kit into a new good shape where we can call it
[email protected]!
Timeline:
- We need ~2 weeks to finish all of the above and send this branch to beta for testing.
Second big branch is a complex one with several HUGE updates
- Bringing Relational Queries v2 finally live. We've done a lot of work here to actually make it faster than RQBv1 and much better from a DX point of view. But in implementing it, we had to make another big rewrite, so we completely rewrote the drizzle-orm type system, which made it much simpler and improved type performance by ~21.4x:
(types instantiations for 3300 lines production drizzle schema + 990 lines relations)
TS v5.8.3: 728.8k -> 34.1k
TS v5.9.2: 553.7k -> 25.4k
You can read more about it here.
What's left here:
- We have 1 issue with TS that is already in progress of being fixed. The issue and Post about fixing.
Where it brings us:
- We are getting drizzle-orm into a new good shape where we can call it
[email protected]!
Breaking changes:
- We will have them, but we will have open channels for everyone building on top of drizzle types, so we can guide you through all the changes.
Third branch is adding support for CockroachDB and MSSQL dialects
Support for them is already in the alternation-engine branch and will be available together with the drizzle-kit rewrite.
Summary
All of the work we are doing is crucial and should be done sooner rather than later. We've received a lot of feedback and worked really hard to find the best strategies and decisions for API, DX, architecture, etc., so we can confidently mark it as v1 and be sure we can improve it and remain flexible for all the features you are asking for, while becoming even better for everyone building on top of the drizzle API as well.
We didn't want to stay with some legacy decisions and solutions we had, and instead wanted to shape Drizzle in a way that will be best looking ahead to 2025–2026 trends (v1 will get proper effect support, etc.).
We believe that all of the effort we've put in will boost Drizzle and benefit everyone using it.
Thanks everyone, as we said, we are here to stay for a long time to build a great tool together!
Timelines
We are hoping to get v1 for drizzle in beta this fall and same timeline for latest. Right after that we can go through all of the issues and PRs and resond everyone. v1 for drizzle should close ~70% of all the bug tickets we have, so on beta release we will start marking them as closed!
