search

tekartik / sqflite

SQLite flutter plugin
BSD 2-Clause "Simplified" License
2.88k stars 527 forks source link

package:indexeddb - CRUID for iphone, android, windows, linux, ios (and web & pwa) #339

Open PavelPZ opened 4 years ago

PavelPZ commented 4 years ago

@alextekartik, thanks for your great sqflite package.

I've studied your other projects and I think that in the current "Flutter for web is in beta" period, integrating some of your existing projects would bring huge benefits to developers. I even think that (hypothetical) "package: indexeddb" is basically done for web+pwa, iphone, android.

Do you think the following statements are correct?

sqflite and idb_sqflite

I thing sqflite and idb_sqflite could be a good basis for the local CRUID enabled flutter package. Its main goal will be to emulate indexeddb functionality using sqlite.

why

idb_shim, sembast

package:indexeddb

I think many people (including me) will be happy to support the creation of a robust and well mainained package:indexeddb package as a missing part in the Flutter puzzle.

alextekartik commented 4 years ago

Hello @PavelPZ and thanks for starting the discussion (although I’m not sure sqflite is the right package but at least that’s the most popular). I agree with most of your statements. Below is my personal opinion, so don’t take anything for granted.

The only real databases available without any additional components for applications are:

I was looking as well for the greatest common divisor to have a database that works for web and mobile. As you said, indexeddb can easily be emulated on top of sqlite (hence my idb_sqflite package).

The history of idb_shim was at first to mimic the existing javascript library that existed to support safari where there was at first no indexed db and then later a completely buggy implementation while websql was working fine. Websql is no longer a standard and indexed db implementation is better. So this goal is no longer valid. The other goal was to enable unit testing on the regular dartvm (unit testing for the web remains a pain), so it was easy to implement a similar API on top of sembast (that I don’t consider a real database but is is a convenient placeholder).

I used idb_shim for regular (non-flutter) web app so it is important for me to keep a non-flutter package. Maybe in the future that will change but I’m not in a good place to know what is going to happen.

I have a basic demo app that uses a github package app_idb that has conditional import to have the same code running on mobile and for the web.

Having sqflite on other platform (linux, windows, fuschia) is not a done deal yet and I hope flutter web won’t prevent using js native library (I am not a fan of web plugin - i.e. why adding a service layer when we can communicate directly with native libraries - although it is cleaner, data transferred can be huge so it has to be efficient).

I understand that you might not want to have an extra sembast dependency but here as well having a way to run unit test is a must (people always complain about the difficulty to test sqflite code since flutter does not allow easily to run unit test that has plugins) so sembast remains a convenient mocking implementation.

It is always a tough decision to start a new package that needs to be maintained (I’m on my own and I already spend a lot of time maintaining the packages I have, that includes answering issues, fixing bugs and following sdk upgrades).

Usually I start something when I have a need. I would say what exists already answer your needs (idb_sqflite + idb_shim) and the app_idb package although not on pub and although having too many dependencies meets my needs currently for flutter web and mobile. Making a new indexeddb package flutter only would prevent me from using it from a non-flutter package (indexed_db can also be used in worker thread on the web). Ideally we could have multiple packages:

One issue I have with the indexed_db API is that it is not “sounded”. I.e. any parameter can be anything so it is not very darty. At some point I wanted to create a sounded api with generics but I don’t have the needs yet (hence not the time).

Also some query API could be added (indexed_db has very poor query capability) that could take advantage of some sqlite SELECT feature (instead of having to pull a whole query result and filter it later).

Well it seems I’m going in too many directions for now. I would definitely not integrate indexed_db into sqflite directly (sqflite should be SQLite focused only) and I’d rather not create a flutter only package for indexed_db. Idb_shim naming is very bad (I’m very bad at naming package).

Maybe having an indexed_db_api package (or just indexed_db or indexeddb) that just define the interface could be a good start.

As I said, all of this is just a personal opinion. People have different needs. My main priority is unit testing so having a mock implementation is a must for me.

I’m interested in following the discussion with you (and whoever is interested) and definitely interested in participating in any initiative in this direction.

PavelPZ commented 4 years ago

@alextekartik, thank you for a quick response. I attach some notes.

dart:ffi

We both agree with The only real databases available without any additional components for applications are indexed_db for the web and SQLite for mobile. But I would like to add SQLite for windows, linux and macos (macOS is in alpha now) See following links. I hope that SQLite will be the first solution for testing dart:ffi capabilities:

If I understand dart:ffi well, then in the near future it will be possible to use direct interaction of sqlite with dart (without the need to use the platform channel). And mainly without flutter dependency, just dart .

If I am not mistaken, this eliminates the problems of flutter testing. I agree that flutter testing is nightmare not only for plugins but for async code in general.

sqflite vers dart:ffi's sqlite API

I can see some difference in sqflite vers dart:ffi's sqlite in https://github.com/dart-lang/sdk/blob/master/samples/ffi/sqlite/example/main.dart. Can you imagine some abstraction how to unify both sqlite's APIs?

package:indexed_db

Example is more than long text explanation so I prepared simple one at https://github.com/PavelPZ/indexeddb. Do you think this idea is feasible? Or it is something as a solution for the far future?

I wanted to continue, but this error stopped me (https://github.com/flutter/flutter/issues/44937).

alextekartik commented 4 years ago

using dart:ffi is not a done deal yet (see my comments here: https://github.com/tekartik/sqflite/issues/200#issuecomment-532662550). The moor package is still experimenting it and I'm following it. It is easy to make small experiments that are super fast but in a real application having to deal with a cursor from a single dart thread could definitely hang the UI.

I think indexed_db should definitely not be a flutter package. Some people (including me) are still doing non flutter app (web, native io). As I said it should just re-defined the js indexed_db package (i.e. what idb_shim does now - https://github.com/tekartik/idb_shim.dart/blob/master/idb_shim/lib/idb.dart without some definition)

Yes having SQLite for windows/linux and macos using ffi could solve some unit testing issue but it sounds heavy weighted to test code using indexed_db with such a layer.

Regarding abstraction, sqflite API could easily work on a raw dart:ffi sqlite implementation (that's actually what sqflite does on the native side now).

indexed_db has very poor query capabilities and this part is actually an issue when using sqlite (we have have to read all the data first when using openCursor). I'm currently thinking about adding extra API in idb_shim to allow data retrieval without cursor but with queries that could be implemented differently on the web and when using SQLite. My point here being that maybe the indexed_db API is not the ultimate solution for a common web/mobile database API.

PavelPZ commented 4 years ago

Thanks a lot for detailed explanation.

Moor as cross platform is useless for many cases (for web, it uses in-memory sqlite database port).

I read somewhere that Mozilla uses sqlite to realize its indexed_db. But my knowledge of C++ development is very poor so I cannot try this way.

I agree that some issues will need to be addressed separately, e.g. pagination (LIMIT for sqlite, conditional cursor.continue() for indexed_db). However, this functionality should be an extension of the indexed_db package, not its internal part.

For my new eLearning project (12 tables) I originally planned two different codebase. Now I'm thinking about using your great libraries.

PavelPZ commented 4 years ago

Thanks a lot for detailed explanation.

Moor as cross platform is useless for many cases (for web, it uses in-memory sqlite database only).

I agree that some issues will need to be addressed separately, e.g. pagination (LIMIT for sqlite, conditional cursor.continue() for indexed_db). However, this functionality should be an extension of the indexed_db package, not its internal part.

I read somewhere that Mozilla uses sqlite to realize its indexed_db. But my knowledge of C++ development is very poor so I cannot try this way.

For my new eLearning project (12 tables) I originally considered two codebases. Now I'm thinking about using your great libraries. The ability to debug everything in pure dart (sembast) and finally use sqflite is really unique.

Then, when the evolution and possibilities of dart:ffi's sqlite become clearer, it is possible to make a small redesign.

In addition, the indexed_db interface as a base looks (thanks to idb_shim) really promising.

PavelPZ commented 4 years ago

In any case, I think the cross platform flutter decoupled package: indexed_db is extremely needed. And that your repository contains all the know-how to create it. But I understand that monetization of github projects and dart packages is problematic...