Perfect SQLite for Flutter

[cliftonlabrum]

I just wanted to say thank you for this package. In my opinion, it's exactly what 95% of Flutter devs need for SQLite, but they just don't know it yet.

As a Flutter dev, I want:

1. Consistent, cross-platform SQLite that's easy to use.
2. No heavy ORM on top of it. Usually the ORM proves to be more complex to learn than SQL itself (custom class definition and query syntax, using build_runner to render part files, etc.).
3. Built-in management of threading so I can just use it and not worry about it blocking the UI.

You've done exactly that, so thank you!

A couple other thoughts:

• SQFlite is super-popular and is well done, but not having Windows support without more packages and lots of shenanigans is a pain. Also, it relies on platform-specific packages (i.e. FMDB on iOS) underneath, so it can create a ton of warnings at compile time.
• SQLite 3 is excellent, but why would anyone want to roll their own thread management? No thanks.

I wonder if you should talk to the author of SQLite3 and just merge all your isolate management goodness into it so that more people can know about it.

I'll post my thoughts on Flutter's Reddit channel as well in hopes of spreading the word about your package.

Thanks again!

[rkistner]

Thanks for the feedback!

My thoughts:

• sqlite3 is a great library, and should probably stay lean like it currently is - effectively just wrapping and exposing native sqlite3 directly.
• drift is also great if you're looking for an ORM and type safety. Some of the ideas for this library are inspired by its implementation.
• I'm with you on the ORM front. It's nice in theory, but just adds too much complexity without enough gains in my opinion. It's a completely valid choice, I just personally prefer to work with SQL directly.

I think it would be great to have some collaboration with drift to avoid having completely separate implementations of the same internals - things like the connection pools and Isolate management. I just haven't had the chance to start that discussion yet.

[cliftonlabrum]

It might also be worth looking at what SQFlite does to manage connections and isolates. I can throw multiple queries at it recursively and it quickly executes them all and never complains. sqlite3 also manages to do this on its single thread (at the risk of potential UI stuttering).

I implemented the fix in #4 and still get connection exceptions because I'm doing ~30 transactions in quick succession. The use case is downloading a bunch of records from a server and populating my local database. But the records get fed at unpredictable intervals based on whenever my HTTP fetches complete.

Food for thought. 😄 Thanks for your help! 🙏🏼

[rkistner]

Sqflite doesn't manage multiple connections and isolates - it only supports a single concurrent transaction, and only from the main isolate.

This library uses multiple connections in separate isolates to achieve concurrent read transactions, and also allows you to pass a connection to a separate isolate. Since it's so new it's possible that there are some underlying bugs, but I haven't been able to reproduce the issue you mentioned.

Could you open a new issue with the error message you're getting, and some sample code (even if you can't share everything)?