A partial archive of discourse.wicg.io as of Saturday February 24, 2024.

[proposal] - ship browsers with builtin wasm-sqlite3

kaizhu256
2019-10-08

proposal

a standardized, easy-to-use client-side sql-database is sorely needed to advance the state of frontend-development beyond its current dependency on backend-servers for state-management.

my current experience with sql.js (wasm-sqlite3) indicate its suitable for such role. at our company, we have a frontend-product that no longer relies on sql-server, with all its state-management migrated to sql.js (and persisted as indexeddb blobs). the product runs sql.js fine on chrome, firefox, and edge (albeit slowly) with the following anecdata performance numbers:

time to ingest 500,000 row csv (~100 MB) into sql.js web-worker (and persist in indexeddb):

  • chrome: 15 seconds
  • firefox: 20 seconds
  • edge: 90 seconds

time to run [product-specific] sql-queries on 500,000 row table in sql.js web-worker:

  • chrome: 35 seconds
  • firefox: 35 seconds
  • edge: 65 seconds

actionable items:

  • debate on whether including currently released sql.js v1.0.0 (or minor update to it) as a builtin browser/webworker-api is a good-idea

security issues:

i’m not knowledgeable about security. but the initial api will not address persistence, to keep the scope manageable – users are expected to manage persistence themselves via indexeddb/kv-storage/localforage.

possible future addon proposals (but out-of-scope of initial proposal):

  • allow indexeddb to persist 1gb sqlite3-blobs (chrome currently hard-limits indexeddb to 120 MB blobs).
  • add easy-to-use, web-worker builtin-api to ingest 1gb csv
  • future release of sql.js should have ability to attach multiple database/csv [1]
  • future release of sql.js should properly catch web-worker exceptions and pass them to main thread [2]

[1] sql.js issue 88 - A way to attach multiple databases?

[2] sql.js issue 288 - fix error-handling in worker

screenshot%20ingest%20csv

screenshot%20run%20sql-queries

tabatkins
2019-10-08

Unfortunately, shipping a sqlite would have the same problems now that it did in the past, when we effectively did so with Web SQL Database - SQL doesn’t have a web-quality spec (nothing that would allow an independent implementation to be written that would interoperate with content in the wild), and just using SQLite means we’re locked into the quirks and bugs of whatever version of SQLite we use, likely resulting in us at some point having to freeze/fork the web’s version of SQLite from the public version if they try to fix a bug or change a quirk that the web has come to depend on.

kaizhu256
2019-10-08

can it be different this time around?

  • unlike websql, sql.js is a more isolated, persistence-free wasm-package with smaller security audit-burden on browsers.

  • you mention idealogical and web-compat reasons for dropping websql, but in order to sell that to the community, a 3rd factor was promise that indexeddb would be a “good-enough” replacement. its not – as anecdata, i’m rarely aware of real-world scenarios where indexeddb is used as an “indexed” db (as opposed to dumb key-value store with improved quota over localstorage).

part of me feel like web-development has been set-back for nearly a decade after websql was dropped (and my perception of mozilla at time was it pulled a bait-and-switch over community). so much crap was added to javascript during intervening years to [sub-optimally] solve general-purpose programming issues on frontend/embedded systems that shouldn’t have existed if sqlite3 was accessible. we could have had truly serverless frontend-apps free from backend hindrances by now.

sorry for ranting, but i think the websql precedent can be revisited given how disappointing indexeddb turned out to be.

Anonymous2292900
2022-08-06

It would be cool to open .sqlite files directly from the browser. Why?

  1. Makes the user less dependent on proprietary solutions
  2. This increases the market for portable database like sqlite
  3. Makes sqlite more usable as it opens in browser as como ‘json’ or ‘html’ - I think if files like csv, sqlite, json can’t be rendered directly in browser
  4. Most of the apps that were built in electron work with a local database like sqlite, localdb etc - this could allow desktop apps to communicate easily with the browser if this solution/proposal is made, link: Electron Apps | Electron There are several requests here about the ‘writable file’ - I ask if this could not be applied here, in most cases when we use local database we want to have permanent data for some time.