better-sqlite3 support

[Lokowitz]

Hi guys,

i was trying to upgrade Pangolin to the latest better-sqlite3 version (currently 12.2.0).
The current version in this project is 11.7.0.
The latest possible upgrade is version 11.9.1 because since 11.10.0 they [disallowed to return a promise and asyc transactions].(WiseLibs/better-sqlite3#1364)
Node v24 support is added in 12.0.0.

I was not able to find a good way to support both sync and async.

Any ideas how you want to make it future-proof? Maybe switching only to Postgresql oder back to normal sqlite3 instead of better-sqlite3?

[oschwartz10612]

Thanks as always for helping us maintain these dependencies. Sounds like to be future proof we need to fix the transactions? I think we want to keep supporting sqlite. I guess we would have to go around all of the code and make refactor so transactions dont return an async function. Could be a bit of a refactor. Will need to figure out when to schedule in. What do you think?

[Lokowitz]

Problem is that PG still needs async.
I tested a bit to support both, but you have to change a lot of files and it is not allowed to have both versions while compiling.
The only way i got it to work was to split it in sqlite and pg files and delete the not needed files while building the image.

I checked the drizzle docs and saw that they are also supporting libSQL. Maybe this is an option.

I already tried to make a change to libSQL but there is also a lot of refactor work especially for all the migrations.

[marcschaeferger]

IMPORTANT: We don't need to rush this. There is no need for a immediate implementation/migration!!!
@oschwartz10612 @Lokowitz

One idea worth exploring and that ties directly into the async/sync discussion here is switching the SQLite driver in Drizzle from better-sqlite3 to libSQL.

LibSQL is a fully open-source fork of SQLite (GitHub repo) that Drizzle officially supports.
For Node, there’s libsql-js, which offers a better-sqlite3-compatible API you can keep running in-process against a local .db file, while gaining:

• Native async driver support (avoids the async transaction limitation that blocks us past better-sqlite3 11.9.x)
• Easy switch to remote/cloud backends like Turso
• Extended SQL functionality (more ALTER TABLE ops, experimental extensions)
• Seamless use in Drizzle via drizzle-orm/libsql, so most application code remains unchanged
• Upgrade-safety with Node 24+ without requiring a big transaction refactor

FYI: On local-only setups libSQL’s async abstraction may have a slightly higher overhead than better-sqlite3, but the tradeoff is much more flexibility for future cloud/edge and multi-node scenarios.

You can check the npm package @libsql/core for install instructions, or test locally right away since Drizzle native support is already in place Drizzle Docs

---

Possible plan:

1. Short-term: Pin better-sqlite3 at 11.9.1 to keep current builds stable.
2. Experiment: Create a branch using Drizzle’s libSQL driver with a local file backend.
3. Test: Validate migrations and core app flows (dashboard, proxy) for both SQLite and PG setups.
4. Adopt: If stable, merge and make libSQL the default SQLite backend — removing the async/sync blocker and unlocking future remote DB options for SQLite. (I would still use PG for that).

---

Alternative if staying on better-sqlite3:

Introduce a thin DB adapter layer: SQLite runs sync internally (optionally in a worker thread), PG stays async.
The adapter exposes a unified Promise API, so the codebase remains backend-agnostic.
This involves more refactoring than the libSQL path but keeps dependencies closer to the current setup.

---

This way we get a safe short-term fix (pinning), plus a longer-term option (libSQL) that aligns with Drizzle’s roadmap and ensures smooth upgrades going forward.

[github-actions]

This issue has been automatically marked as stale due to 14 days of inactivity. It will be closed in 14 days if no further activity occurs.

[phiresky]

Introduce a thin DB adapter layer: SQLite runs sync internally (optionally in a worker thread),

You don't really need this, you could use your own tranasction callback function that does begin first and commit in the end, something like

async function transaction(db, callback) {
  db.exec("begin");
  try {
    await callback(db);
    db.exec("commit");
  } catch(e) {
    db.exec("rollback");
  }
}

It's fine as long as you never use the connection for anything outside the transaction (not sure how pangolin works). If you do you'll get the same behaviour as previously (buggy unless you only do reads).
Otherwise you could use a pool that wraps better-sqlite3, and transaction takes an exclusive connection from the pool. Not sure if libsql implements a pool or what it does if a tranasction is running.

[github-actions]

This issue has been automatically marked as stale due to 14 days of inactivity. It will be closed in 14 days if no further activity occurs.