Web Support

[Cretezy]

To have web support, https://github.com/kripken/sql.js seems like a great option. Is there any plans to have web support eventually?

[alextekartik]

I think it is too early at this point to make good speculation about what the plugin system will be and whether sqflite is a good fit for the web (at first I would say no). One big limitation in the implementation you pointed is:

doesn't persist the changes

which does not make it a full solution. Maybe new solutions would come, such as an SQL interpreter on top of IndexedDB, although I'd rather have a new top level API for applications that could target efficiently the best storage implementation for mobile (sqlite), web (indexed_db) and desktop (sqlite, leveldb).

I really like to have feedback of people that have use lovefield https://github.com/google/lovefield. I don't know if the API could be a good candidate (as I think porting it on top of sqflite could be easy) to be ported to dart to provide a common application database interface for web, flutter and vm.

I would keep this issue open maybe some people have opinions on what has been done in other languages that could be a good approach for providing a database interface for all the targets.

[alextekartik]

As a side note, one big constraint with the web is that you can have multiple instances of the same app (multiple windows or tabs) running at the same time. That will definitely be something to handle gracefully.

[Cretezy]

That is correct, although the same can apply with Flutter with multiple windows of the same activity.

Using SQLite on the web would definitely be the best though, because it wouldn't require rewriting all the SQL (which can be a very big hassle if you're using advance SQLite features and optimizing for it).

sql.js actually does allow for persistence though, it just doesn't have it built-in. You can export the database itself, and save it using localStorage (although not great because there's no way to save binary directly). It definitely is hacky, but could work as a POC.

[sachaarbonel]

Hi @alextekartik, just wanted to share that the project moor (uses sqflite under the hood) which has now support for flutter web. He uses the project sql.js which is a wasm version of sqlite. And to solve the issue of persistence he saves the data in window.localStorage, the key being the database name and the value a str version of the database binary. The main advantage of sql.js over lovefield imho is features parity. Furthermore it seems that lovefield still use for package management which could be problematic.

[alextekartik]

Interesting. I'd like to see how it goes to see if it becomes a viable solution. I'm puzzled by the persistency though (saving a whole file in the local storage every time?) and the mutli tab behavior.

[sachaarbonel]

Yeah I agree that might be problematic for applications that mutate instensively the state of the database. And for the multi tab behavior I think I've found a solution using an event listener on the storage.

[alextekartik]

As of today, this is not planned, there is no decent solution on the Web

[Kraakhoofd]

As of today, this is not planned, there is no decent solution on the Web

In my use case, persistence is not needed, read-only is fine. Any thoughts on that?

[alextekartik]

sql-js could be a solution for that assuming you can load the whole database in memory. You could write a dart wrapper around it.

[deakjahn]

@alextekartik I need it badly, so badly that I'm ready to work on it if needed. :-) Both Moor and plain SQL.js seems to be WASM, so what's your take on it? Besides, you might want to reorganize Sqflite into a federated plugin, that could open up the alley to platform versions that could be worked on independently from the current plugin and officially supported when they are ready.

[Cretezy]

Moor uses sql.js, and stores the database data to IndexedDB.

Web APIs don't have a random I/O byte storage unfortunately, which would be very useful for storing SQLite database's on the web

[deakjahn]

@Cretezy Yep, and seems to use Sqflite on other platforms, actually. :-)

[Cretezy]

@deakjahn yep exactly. The reason I originally opened this ticket is because I believe that the web support should be here instead of in a separate package. Maybe I'll extract the moor code into something soon

[deakjahn]

I've just modified two plugins to federated (neither is publicly available, I did one to get going and sent it to the original author, he will decide what he wants to do with it, the second is for my local use). I learned the advantages of federated plugins that way. I don't know if you ever delved into it, it means that plugins can be separated into different packages and still act as a single, the user will simply depend on the central package and receive whatever platform is needed, automatically. If we did anything remotely similar, it should be done that way.

I'm not really sure yet which would be the best approach but I have a mobile app that I need to port. All the other important plugins are more or less done (if there was no web support, I wrote it myself) but SQL is on the menu now. So, by hook or by crook, I will come up with something, but co-ordinating efforts and sharing the work to do would be much better than all of us reinventing the wheel separately. :-) Count me in.

[alextekartik]

sqflite_common tries to allow other implementation (including web). Basically it defines a DatabaseFactory type. sqflite_common_ffi (sqflite on FFI for Windows/Linux/Mac Desktop/VMandsqflite_sqlcipher` (Encrypted SQLite) uses this mechanism. The good thing is that it does not rely on Flutter so could even allow defining an API for the Web (with or without Flutter).

Going though a worker or through flutter services will add an additional level of data serialization/deserialization which could be a performance killer for big queries.

So basically there is no rush in needing to have a federated plugin for Web Support, although nothing prevent from defining it now (taking really good care about compatibility).

The first thing would be to design a good core implementation for an sqlite js implementation (being sql.js, wasm, JsStore, manuel sql parsing and simulation):

• does it run in a worker?
• provide a minimum API for open/closing, SQL command execution and data reading
• Being clear on what it means opening a database (what is a file in a web context, how do you import/export data)
• Define the persistence strategy and the cross-tab behavior if 2 web pages open the same database in write mode.
• Once a well tested web implementation allowing opening a database and running SQL commands is available
• Write a DatabaseFactory implementation on top of it or provide a federated pluging for the web (when federated plugin is tested)

The abstraction in DatabaseFactory and federated plugin is similar (invoke) and it is basically one API to implement.

Federated plugin is supported, but not fully tested since not used. Currently nothing prevent you from writing an implementation in a different project (i.e. you don't need to modify sqflite to add web support). As long as you register your plugin class in your pubspec.yaml section you can just have:

import 'package:flutter_web_plugins/flutter_web_plugins.dart';
import 'package:sqflite/sqflite_dev.dart';

import 'database_factory_web.dart';

/// The Web plugin registration.
///
/// Define a default `DatabaseFactory`
class SqflitePluginWeb {
  /// Registers the default database factory.
  static void registerWith(Registrar registrar) {
    /// Set the default database factory to use.
    /// Currently calling an on-purpose deprecated helper.
    // ignore: deprecated_member_use
    setMockDatabaseFactory(databaseFactoryWeb);
  }
}

Maybe a better API can be done in the future, but nothing prevents your from starting now. Dummy example here: https://github.com/tekartik/sqflite/blob/develop/sqflite_test_app/lib/src/sqflite_web_sim.dart

Of course you will need to learn the various parameters in and out which is not explicitly documented but the sqflite_common_ffi implementation is good example on implementing a database factory (here it uses moor_ffi in a background isolate).

That is said, it remains important to know why sqflite is needed on the browser. For regular application usage, the persistence story must be clearly acknowledge by the developer if any limitation (cross tab support, speed, limitations). It can however serve to be a web tool to generate database binaries.

[deakjahn]

"That is said, it remains important to know why sqflite is needed on the browser."

My answer is rather simple: because I have a mobile app that already uses it (SQLite being the default store on all mobile platforms). And I want that app to work in the browser, too. With the same source code, if possible. It has been possible so far, even with relatively complicated technologies like OpenGL/WebGL, so I don't want to give up now. :-)

Yes, it's true that if I were to start a webapp from scratch now, with all the browser limitations, I would probably think hard about the various options. But many could be in my shoes, either with a directly cross-platform application or just wanting to port an existing codebase with the least amount of modification.

[alextekartik]

I want that app to work in the browser, too. With the same source

Ok then we end-up then with the initial problem: persistency. I have not found any acceptable generic solutions for now (even lovefield has cross tab issues) so maybe a custom solution could work. I hope I'm wrong! Good if you find a solution!

[deakjahn]

I won't say it would suit all needs but I have a two step approach. The most important one, the first step for me is to have a database during the session. The second step, to persist it comes later. I can very well live with the whole database persisted to IndexedDB when I ask for it. Less ideal than on mobile, of course, but I'm still OK with that.

[deakjahn]

Cross-tab problems: I plan to check out https://github.com/nodeca/tabex. In my case, it would be enough to disable (or very seriously warn the user) if the app runs in more than one tab. My database is not critical in this regard, it just contains base data that I can download again plus the work of the user. I can simply warn them that their work is in danger if they run more than one copy.

[deakjahn]

OK. I cloned _ffi here, started up an sqflite_web with it and will put Sql.js under it. As soon as it's capable of running the limited example (creating a table and writing-reading a couple of items with an in-memory database) in the browser, I'll put it up on Github for all of us to see. Persistence, cross-tab and everything else can come later. Deal?

[alextekartik]

Great! Ideally the test app in sqlite: https://github.com/tekartik/sqflite/tree/master/sqflite_test_app should work (right now it launches on the Web using main_web.dart entry point but fails on every sqflite calls due to missing implementation). You will likely need some extra calls for opening/exporting data. And as i said multiple implementation can be created on the web until we find the best one!

[deakjahn]

Right now I just put a customary example into the plugin and that's easy to run while trying to match up the JS behind.

Sql.js does have a worker version but they say is "more limited". I couldn't yet find the details about what that means. Do you think this is important, at least in this stage? It probably won't be important for my app but if we aim to come up with a solution that you can use later in a more universal way...

Oh. The API itself is more limited. Well, I don't think this could be answered up front, only when trying to implement it really.

[deakjahn]

Alex, give me some advice, please. :-)

I wasn't aware of Sembast and its already exxisting web support (no, I don't want to chicken out, I already have half of the initial sqflite_web package done, I'll do it anyway). I spent my day to port my mobile app to Sembast yesterday. When I first ran it, I was really worried about the performance because it was slow, very slow. The initial refreshing (loading a JSON from our server, reading it and pushing its contents into the database) took about 30 seconds compared to 3-5 for Sqflite (that's always debug mode figures, I prefer to keep that down knowing that release will be even nicer).

Then I realized that I had transactions turned off (I wanted to see which line causes JSON conversion exceptions during porting) and with transactions, it sped up, yes, seems just as fine as Sqflite. But it this something I can rely on going forward?

The database has 12 stores, currently has 2700 lines in the JSON. Each and every store keeps complex objects* . It's about twice as large as the Sqlite file but JSON is verbose, no denying that. I don't need to do large bulk operations apart from this initial loading. In Sqflite, I could make use of the transaction batch operations but there's no such thing in Sembast.

*At first I instinctively tried StoreRef<String, Address> stores, Address has toJson and fromJson methods all right but then I realized I have to call those manually and use a Map in the store. I don't know if this has any bearing on performance.

[alextekartik]

(As a side note, there was no link to any code in your precedent posts when you were talking about what you have done on sqflite_web. Maybe it is in a private repo. And maybe you can monitor issues and progress on sqflite_web in your repo.)

Then you talk about sembast which is a different topic. Sembast has some features (listening to records, nosql, noschema, can work on top of sqflite using sembast_sqflite, on web using sembast_web) and limitations (in memory). If you have issues with it, we should follow up in sembast repo https://github.com/tekartik/sembast.dart.or you can use also stackoverflow for usage question to get help (good to split in multiple questions)

But it this something I can rely on going forward?

sembast like sqflite is mostly a one-guy-opiniated-work with community support so I'm not the best person to give an honest advice. Both projects have been up-to-date and stable for some time now.

[deakjahn]

Yes, the two threads seem to merge for me right now. :-) I'm working on the initial sqflite_web right now, it's only on my computer. I'm rather close to being able to upload something to GitHub but not yet there. I'll notify you when I do.

The question was a bit unrelated but still, it was triggered by our previous talks and was, in part, a reflection to your original question: why bother with SQL on the web at all. I simply wasn't aware of your other plugin that, for the most part, addresses this question: simply use that and be done with both mobile and web. Part of the reason that I still decided to write it here rather than in Sembast's issue queue is that, just like myself, other people looking for ways to use Sqflite on the web might not be aware of Sembast, either, and this is the place Google directs people when looking for it.

Consequently, things have been forked into two partly separate questions for me. The one I asked above was simply whether you, in your own undisputed experience, think that using Sembast for the use case I mentioned above would provide me roughly the same performance as Sqflite did so far. With 'rely on', you probably misunderstood me: I didn't mean your work, that's trusted and relied on for sure. I only meant the performance being comparable. But let's drop that, I go on and decide for myself, you're right, let's concentrate on Sqflite Web here all right.

[deakjahn]

@alextekartik: OK, here we go: https://github.com/deakjahn/sqflite_web

I'll start an issue over there with a few items.

[alextekartik]

think that using Sembast for the use case I mentioned above would provide me roughly the same performance as Sqflite did so far

Hard to say. Besides initial load time, sembast should perform faster. Having to export/import is another issue (sembast has its own import/export format). But I could be wrong and it is always hard to compare a SQL to a noSQL database since access is different and data organization too.

OK, here we go: https://github.com/deakjahn/sqflite_web

Excellent!

[deakjahn]

@Cretezy Charles, if you also want to check it out: https://github.com/deakjahn/sqflite_web/issues/1

[Bonsai11]

Is there any news about this? Web is probably going stable soon and this is quite important to have.

[lovasoa]

Hello ! I'm the main maintainer of sql.js. If you need help or someone wants to make this happen and has questions, feel free to ask. The database is indeed in-memory, but nothing prevents you from asynchronously persisting it as a blob in localStorage or IndexedDB. Unless the database is very large, this doesn't usually prevent an application from running smoothly. I know many users use sql.js in that way.

[Kraakhoofd]

Hello ! I'm the main maintainer of sql.js. If you need help or someone wants to make this happen and has questions, feel free to ask. The database is indeed in-memory, but nothing prevents you from asynchronously persisting it as a blob in localStorage or IndexedDB. Unless the database is very large, this doesn't usually prevent an application from running smoothly. I know many users use sql.js in that way.

Maybe I should read up on the topic a bit more, but am I right in the following? My use-case: I have a Flutter iOS/Android app that I ship with a sql db and sqflite that I want to migrate to Web. The DB file is 30 MB, I only use it read-only, so persistence is not needed. So I could in principle host the DB file (e.g. on Firebase) and load it in-memory? Is the 30 MB an issue? Any pointers or a minimal code example would be most helpful!

[lovasoa]

Yes, you can host the database and then load it in memory. 30Mb shouldn't be a problem for sql.js. It will take time to download the database initially, but once it has been loaded, everything should be fast.

[deakjahn]

@Kraakhoofd - this sounds much like this very recent issue: https://github.com/deakjahn/sqflite_web/issues/14. The current implementation only reads from a path but I can see no problem with passing an in-memory data array.

[dramcio]

Hello, any news in this topic?

[Avengong]

any news? everyone?

[silveirasc]

Hello!! News?

[felipecastrosales]

we need this feature 😥

[alextekartik]

I have added some docs on this topic:

• Notes about Web support

that I copy here for quick reference:

Web support

sqflite is not supported on the web as there is no decent platform support. You can check https://github.com/deakjahn/sqflite_web for an initial web support but it lacks real persistency.

[bksubhuti]

I could be wrong.. but it seems like the way to tackle this is to create some type of client server component, but allow the sqlite ffi run client side with minimal code changes. This way there is no need to load the whole database into cache memory. Our database needs will be about 150mb so this is not conceivable . The pub.dev listing for the ffi says it is web compatible. This should be removed.

[alextekartik]

The pub.dev listing for the ffi says it is web compatible. This should be removed.

Unfortunately there is no way currently to specify the compatible platforms

[bksubhuti]

Sorry to open this again. I think it is possible to know what is supported and what is not supported. The only platforms that should be listed are the ones that have been tested on. I think that we can say good things about android, ios, mac , linux, & windows. I think it is known.. or known now.. that web is not supported and there are reasons for multiuser support and record locking and loading db on the client side over the net versus, the server side. So there are reasons why it is not supported.

[alextekartik]

The only platforms that should be listed are the ones that have been tested on.

Currently there is no way to control the platforms listed on pub.dev. See https://github.com/dart-lang/pub/issues/2353

[fdietze]

There is new hope: https://jlongster.com/future-sql-web

[apparatchiki]

There is new hope: https://jlongster.com/future-sql-web

This looks quite promising, hopefully this can be assessed soon and pushed forward as a good enough solution, sqflite is integral to so many flutter apps built around it that are suitable for a web version but that have no easy path forward due to this limitation.

[simolus3]

Starting from version 1.6.0, the sqlite3 Dart package used by sqflite_common_ffi can be used on the web with a bit of setup. There's a new package:sqlite3/wasm.dart library with an interface identical to the one used by the FFI-based variant (package:sqlite3/common.dart defines platform-independent interfaces), so it may be possible to integrate that into sqflite_common_ffi with moderate effort.

Unlike sql.js, it doesn't use Emscripten, so no additional JavaScript code is required. All of the wasm bindings are done in minifiable + tree-shakable Dart. At the moment, persistence in the WebAssembly version is still experimental though.

[alextekartik]

Thanks @simolus3 for the information. I was looking at wrapping absurd sql at some point but that sounds like an even better solution. It seems absurd sql requires running on a worker thread. Does your implementation work on the UI thread and are the calls blocking? Or do we need to run it a worker thread?

[simolus3]

AFAIK, absurd sql uses workers as a hack to be able to call asynchronous APIs (like IndexedDB) synchronously (because the file system implementation in sqlite3 needs to be synchronous). In the implementation from the sqlite3 package, we keep an in-memory cache of the file system and persist data asynchronously without sqlite waiting for the write to be complete.

So the implementation should be able to run on both the UI and a worker thread (although I haven't tested workers yet). That makes calls blocking, but this shouldn't be a huge problem since the virtual file system doesn't block. Running computationally expensive queries on the UI thread would cause yank, this can only be solved with workers.

[muhleder]

I've got sqlite_common_ffi working with the sqlite3 wasm implementation.

https://github.com/muhleder/sqflite/tree/web/sqflite_common_ffi

You will need to use sqlite_common_ffi and sqlite_common from this branch. sqlite3.wasm needs to placed in the web directory of your application at /web/sqlite3.wasm Apparently this file can be found in the sqlite3 release, I could only find it at https://storage.googleapis.com/simon-public-euw3/assets/sqlite3.wasm

The following sample code should then work on the web.

import 'package:sqflite/sqflite.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

Future<Database> openWebDb() async {
  databaseFactory = databaseFactoryFfi;
  Database db = await openDatabase('/test/test.db');
  return db;
}

[alextekartik]

Excellent!

[Fraa-124]

I've got sqlite_common_ffi working with the sqlite3 wasm implementation.

https://github.com/muhleder/sqflite/tree/web/sqflite_common_ffi

You will need to use sqlite_common_ffi and sqlite_common from this branch. sqlite3.wasm needs to placed in the web directory of your application at /web/sqlite3.wasm Apparently this file can be found in the sqlite3 release, I could only find it at https://storage.googleapis.com/simon-public-euw3/assets/sqlite3.wasm

The following sample code should then work on the web.

import 'package:sqflite/sqflite.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

Future<Database> openWebDb() async {
  databaseFactory = databaseFactoryFfi;
  Database db = await openDatabase('/test/test.db');
  return db;
}

@alextekartik do you think this would be integrated in this repository?