Breaking change proposal: Manage connection pool in native code.

[simolus3]

TL;DR: We're considering options to make this package faster on native platforms, but that requires getting rid of Dart-defined SQL functions. Please complain if you're currently using those.

On native platforms, sqlite_async works by spawning a pool of isolates (one writer and multiple readers) and then using a custom RPC-style protocol to exchange messages implementing asynchronous access to SQLite.

Counterintuitively, the following approach is actually more performant:

1. Manage all connection pointers in a single isolate.
2. When we want to query, “borrow” a SQLite connection pointer.
3. Spawn a temporary isolate with Isolate.run for each database query (using fromPointer to wrap connections).

Isolates use green threads in recent Dart versions, so spawning them is cheap. Also, Isolate.run exploits a clever optimization: Because the temporary isolate exits when its result is returned, the result object can be moved instead of being cloned through send ports! Especially for result sets with lots of small objects, this is significantly faster.

The sqlite3_connection_pool package implements this approach. It also stores the connection pointers in Rust, which allows using NativeFinalizers to close connections on reloads. Also, Rust state is shared across isolates which means we can get rid of “isolate connection factory” tricks to share databases. Instead, connection pools are looked up by names, and shared even across multiple Flutter engines (e.g. UI app and background worker).

A very strong downside is that connections are used from multiple isolates (never concurrently, but still). This means we can’t install Dart callbacks on the SQLite connections, as those can only be called on the owning isolate (outside of this experimental feature). Without further workarounds, this prevents:

• User-defined functions implemented in Dart.
• Update, commit and rollback hooks (we can use the core extension workaround in PowerSync, but we’d have to come up with a different approach here). But since we have a Rust connection pool, we could use that to implement update notifications. We could potentially keep using the RPC system for writes and the new approach for reads, where we don’t need hooks.

I believe the benefits of simplifying this package and making it more performant outweigh the lack of user-defined functions. However, we'd like to get input from users of this package as well. Are you currently relying on Dart-defined SQL functions? If so, please let us know!

[hhuseyinpay]

We don't rely on Dart-defined SQL functions or custom hooks, so the proposed approach works well for our use case. We're using sqlite_async in a book reader app with a fairly heavy read workload — 20+ books, 50K+ paragraphs, and ~2M words stored, alongside user/sync data, with FTS5 for full-text search. The performance improvements from the new isolate model would actually be very welcome given our query volume.

[dominikschuwa]

Would this impact the drift ORM package support?

[simolus3]

drift_sqlite_async would continue to work without you having to adopt any drift-specific code. I'm also experimenting with this approach in drift itself since it unlocks a nice performance boost, but drift_sqlite_async would continue to use sqlite_async internally and I don't expect this to change the API in major ways.