sqflite on top of sqflite_async

[alextekartik]

Hello and thanks for your package which I think make the right decisions in terms of feature (WAL, multiple readers, read/write transaction...). I'm the author of sqflite and try to provide multiple implementations for the various platforms using a common API (opiniated and mostly based on the Android original API).

I'm currently working on sqflite_common_ffi_async (Yes, I know bad name). sqflite has a java-like abstraction using a factory so your implementation is a good test for the abstraction layer. It has some history (6+ years) and compatibility constraints that luckily you don't have.

After some work, most of my tests are working except the ones that are specific to sqflite. I might publish it at some point.

I might send (or not) some requests or PR regarding supporting:

• read only databases
• in memory database (could be useful for unit tests I think)

or might simply decide to use existing sqflite_common_ffi (which is similar is some sense to your implementation but not as powerful) for theses cases.

The goal is mainly to provide a better implementation for those who need it without having to change their existing code. Developers might decide to switch to sqlite_async at some point which I'm perfectly fine.

One the main drawback of the existing sqflite is cross isolate support which is kind complex so I was wondering whether your implementation supports multiple isolates (the common scenario being accessing SQLite in a push notification).

I hope you're ok with that and we'll end up cooperating when needed, I have a nice relation with Simon Binder where we help each other (well he helps me more than I help him). I'm a freelance developer and ended up maintaining sqflite just because I started it. People might get confused of having multiple SQLite solution but I think it helps make the overall solution better in the long term.

Thank you!

[rkistner]

Hi, thanks for the input! API compatibility with sqflite is something I've wanted for a while, but haven't gotten around to it. We're definitely open to PRs to help with those goals.

On the points you've mentioned:

Read-only databases

This should be fairly simple - read-only connections are already used. Probably just needs some changes to the connection pool logic to not have a write connection.

In-memory databases

This will have be restricted to a single connection. Can probably bypass the connection pool and just use SqliteConnectionImpl directly for this case.

Multiple Isolates

sqlite_async does support multiple isolates on different levels:

1. The connection pool uses a separate isolate per connection by default. computeWithDatabase can be used to run code directly on the database isolate, with synchronous access to the underlying sqlite3 database.
2. SqliteDatabase#isolateConnectionFactory() can be used to get a factory object that can be passed to different isolates. This can then be used to either open a synchronous sqlite3 database directly, or open a SqliteConnection with the actual connection being in another background Isolate.

It is still required to have a single global SqliteDatabase to manage these connections. I don't have enough experience with e.g. push notifications to know whether additional work is required for that.

Other related projects

Web support

We're starting work on this, but it's quite tricky. The underlying sqlite3 lib does have web support, but with some caveats:

1. It requires a custom WASM build, not compatible with e.g. wa-sqlite or official SQLite builds.
2. The VFS implementations are all in implemented Dart, instead of using existing JS VFS implementations. This is nice if you want to customize in Dart, but the current implementations are a little lacking compared to e.g. IDBBatchAtomicVFS from wa-sqlite.

Regardless of that, web support has some challenges:

1. No Isolate support - would need to have alternative implementations for most of the current library.
2. Multiple tabs can use the same database concurrently. This means needing WebLock or similar to manage locks, and a mechanism to propagate update hooks over tabs.
3. Depending on which VFS implementation is used, the database connection may need to be managed from a web worker.

Drift

The Drift project by Simon Binder is great, and some of the ideas here come from that. In an ideal world, I'd unify the projects so that Drift is just an ORM layer on top of a lower-level library like sqlite_async or similar. I haven't yet looked in detail at what would be required for this.

[alextekartik]

Thanks for the quick reply.

• Access from a push notification is also something I need to test more but the setup/testing is painful but it is major complaint about sqflite. I think Drift handle this better,
• I have a web implementation https://pub.dev/packages/sqflite_common_ffi_web that works in a shared web worker (but chrome android does not support that yet so it default to a simple web worker so here as well multi tab support is still something to test). I personally don't use it but I know some people are using it in production and are happy with it.
• Yeah read-only/in memory are not a must have but I know some production app like dictionary are using it to simply bundle an existing database as a source of their app data. I don't use that neither so I'll likely use sqflite_common_ffi for that for now (don't really care about WAL here but multiple readers might help at some point)

Thanks again and looking forward providing good solutions for SQLite for everyone!