116

I am making a hybrid Android app.

At first I decided to use localStorage, after spending 2 days, I realized that it is very strange and so dropped it.

Then, I picked up indexedDB, after spending today's whole day and actually getting the output in Google Chrome, it is not running inside a WebView of the android app.

And I never used Web SQL database at all because it was deprecated. Anyhow, it has come to my notice that PhoneGap still uses Web SQL and android's browsers support it.

Why was Web SQL deprecated in the first place? And will it be a good idea for me to go with Web SQL now?

gnat
  • 21,442
  • 29
  • 112
  • 288
  • 1
    Just a little remark from someone who has to support both localStorage, indexeddb and websql as underlying storage techs: find an existing wrapper technology and just use that. I would go with IndexedDB, as it is available on Android 4.4+ and iOS 7, and there is good adapter tech available that makes it feel like you are using indexeddb even when it is not available (on older devices). – oligofren May 09 '14 at 07:10
  • 4
    What did you find strange about localStorage? It's just a key/value pair store. I'm curious what you didn't like about it and the type of problems you ran into. I'm using it in a project and would like to know the case issue you ran into. – jmq Jun 01 '14 at 01:26
  • 1
    @oligofren, If you are using more-than-just-brain-dead-simple SQL in web SQL, you can't exactly translate that to localStorage and etc. – Pacerier Jun 23 '15 at 02:55
  • @Pacerier Of course. But that assumes you have already committed to WebSQL and invested lots of resources into that. My tip was assuming TS was still pondering what tech to choose. WebSQL cannot be emulated by LocalStorage, but IndexedDB can be emulated by WebSQL quite nicely. And these days, IndexedDB is even more prevalent than it was when I wrote that. – oligofren Jun 24 '15 at 16:58
  • 2
    But save yourself the hassle of creating an abstraction layer (which I did), and just use YDN-DB for now https://dev.yathit.com/ydn-db/index.html. It will use the best available tech for that device. – oligofren Jun 24 '15 at 17:04
  • 1
    @oligofren, Speed and performance at the magnitude of 100 milliseconds is important to me. I can't use abstraction layers. – Pacerier Jun 26 '15 at 23:52
  • 2
    You are always using an abstraction layer of some sort. That's programming and how you achieve consistent behaviour regardless of implementation bugs in the browser. Dummy js calls exceed 5000 per ms, so unless the author of YDN-DB has done something ridiculously stupid, you should not get a performance hit anywhere near the order of 100ms. More like 1ms, for 1:1 ops, on platforms that don't support IndexedDB natively. Which, at the moment, is only older versions. All current browsers support IndexedDB. WebSQL is deprecated. And try some simple profiling before you "optimize" away tech :-) – oligofren Jun 29 '15 at 20:15
  • 5
    @oligofren, You're missing the point of my comment. I'm not talking about the overhead of one function calling another and viceversa. I'm saying when you use a db abstraction layer you are limiting yourself to a subset of SQL query *patterns* you can use without suffering from performance penalities. You can do no tuning because the library does it for you automatically and don't always get it correct. It's not going to be 1ms unless you store only 1 row of data. – Pacerier Jul 02 '15 at 12:35
  • Fair enough, but I think we are not talking on the same level. I am basically just saying, stop wasting time on WebSQL as you are not able to support IE or Firefox using your investment. But you might create something that only runs in a WebView, in which case you're good. – oligofren Jul 02 '15 at 14:58
  • localStorage is like SharedPreferences on native Android. – LEMUEL ADANE Jun 14 '16 at 16:05
  • 1
    Now you can just embed sqlite in the browser... https://web.archive.org/web/20191220194314/https://vkbansal.me/blog/sqlite-in-browser-using-webassembly-wasm/ Ta da! – ADJenks Jun 11 '20 at 22:49

4 Answers4

116

Short version: Web SQL was deprecated because standards are really important and turning Web SQL into a proper standard would have been prohibitively difficult.

Since existing implementations of Web SQL are basically wrappers around SQLite, any attempt to define a standard of it was basically "do what SQLite does." This isn't good enough; a true standard needs to be self-contained, to define the interface and corner cases and exceptions itself instead of pointing to an existing implementation (especially a third-party implementation like SQLite). Otherwise, you run the risk of taking one particular implementation's quirks and enshrining them as the standard. From what I've read, the W3C prefers multiple independent implementations of proposed standards to help ensure that this happens; since Web SQL was so tied to SQLite, that just wasn't going to happen.

Mozilla's blog gives more details on their reasoning in particular for not supporting Web SQL; apparently they were one of the major voices in getting Web SQL deprecated.

Should you go with Web SQL now? I don't expect the vendors that currently support it (like Google and Apple) to drop it any time soon, but IE and Firefox won't be adding it, and since it's deprecated, why invest in it? (For example, Ido Green, with Google Developer Relations, doesn't recommend using it.)

Josh Kelley
  • 10,991
  • 7
  • 38
  • 50
  • 10
    That post by Ido is super basic and doesnt even scratch the surface on why one should use one or the other. the fact is, noSQL databases were designed with large size in mind, and that just doesnt apply to a database running on a user's single computer. You may gain some advantages relevant to big data, but you lose stuff like JOINs. There is no way I could have developed my open-source "Plus for Trello" chrome extension if I had to use indexedDb (and I do use noSQL datastore in appengine) so I went for web sql. – Zig Mandel May 24 '14 at 06:08
  • 2
    In case anyone wants the full history of Web SQL's deprecation, [this blog post](http://nolanlawson.com/2014/04/26/web-sql-database-in-memoriam/) has details and links for further readong. – Josh Kelley Jun 30 '15 at 17:56
  • 4
    Because the Google GMail MS-Outlook competitor could then do fulltext-search, and because "embrace, extend, exterminate" is not possible when there's only one SQLite implementation (MS), and because Jonas Sicking (Mozilla) doesn't like SQL. Key value stores with an overcomplicated interface are of course that much better (aka in hyphe), especially since every JavaScript object is already an associative array. And let's face it, data normalization, referential integrity and set-based operations really are revolting for someone who doesn't (wantTo) understand SQL, aka "The users don't want SQL". – Quandary Jul 17 '15 at 11:53
  • 3
    ironically, WebSQL is perfect for interacting with SQLite if that is exactly what you want to do (and don't need PRAGMA). – Michael May 11 '16 at 00:49
  • 8
    so mozilla killed a project and a technology that was extremely useful in many situations becaulse some people there did not like it and people defend them. Why? they could implement BOTH IndexedDB AND WebSQL – yoyo_fun Feb 13 '17 at 06:27
  • @yoyo_fun: Exactly. It makes no sense whatsoever. But fortunately, Mozilla is getting irrelevant fast, and so is Edge, and IE already is. Too bad actually. I quite liked Mozilla (appart from their [Web]SQL hate), especially the new developer edition with an actually usable developer console.. Not a memory hog like Chrome - very relevant for my 200+ open tabs at a time. – Quandary Feb 04 '19 at 14:01
  • 1
    Safari 13 has now removed [support for WebSQL](https://caniuse.com/#search=websql) that earlier versions had. – Thunderforge Oct 08 '19 at 19:50
  • 1
    @Iain That's... just how standards work? The standard says what should happen, and implementations have to follow it, *or else*. Those people who create the standard should take implementability into account, however. They're allowed to base it on SQLite but that means any implementation which doesn't use SQLite, or uses the wrong version of SQLite, must include an adapter layer. – user253751 Dec 02 '20 at 14:55
19

Josh Kelley's answer is so far the BEST answer ever I've found about the reason of the standard work to be stopped. That said, I think there is an additional perspective to consider regarding the user-base.

Eventhough, I disagree on Ido Green's approach to the subject ("This is a recommendation for web developers to no longer use the technology as effectively")...

I believe (as vi4m states in the comments of Ido Green's article):

We (developers) can still use this technology. No browser vendor requested removal of this technology, nor plan to remove it. Developers are the voice of the web. We can just still using it, maybe Mozilla will change mind ;-)

And I would add another logical approach: If you are developing for mobile ambient... ¿what ambients are in more hands? Answer: iOS and Android... So if BOTH support webSQL, and your target is MASSIVE MOBILE, go for it!

Think as big apps have done almost always at the beggining, get the MOST first, then (once achieved success) recreate the work to get the remaining less (if you really want to achieve them or are asked to do so). Finally, ins't always success who marks the path?

After reading Nolan Lawson's article (in which is clear his intention to give a chance to his invention) I believe this matter became a new cold-war between tech-giants that shouldn't even exist. I believe specs are made to stay (as -longer and untouched as possible- the better for client oriented performance). Ironically the "specs guys" job is to generate NEW specs (sometimes where there is none needed, so he can have something more to do), and likewise programmers jobs sometimes focus on changing and rewriting what already works instead of doing solutions for new problems and new tendencies.

For me, Client-Side Databases was a matter of simply making parallels (between server and client sides) so we could create, store, upload and download data easily. Under this approach, having the same languages and structures (at least for us, LAMP opensource developers) is straight forward and logic.

I believe IndexedDB intention for being an alternative with wider and newer possibilities is an always good approach, but somehow it resembles for me to the need of developing software that NEEDS to be installed (even when the core solution can stay on the cloud). In a world that tends to stay connected it sounds like A) a matter of control and possession or B) focusing on developing monsters for the client-side... but for those kind of needs exist Apps (in the Mobile world) and software (in the PC world). I believe the goal of Webapps should stay mainly on extending the web no matter the device.

I believe a nice infographic could come out of this approach.

UPDATE (2021):

Nowadays, Mozilla (Firefox) and Apple (Safari) do NOT support the technology on any of their browsers.

All other full Browsers (not mini) do support it (Edge, Chrome, Opera, etc):

https://caniuse.com/sql-storage

For web client-side DB's, we still have indexedDB, which has nowadays even more support than WebAssembly (96% vs 92%). Native is double or triple job if you want to cover as many users as possible, unless you have a specific need that still can't be done with Web (fewer every year). I personally prefer the approach of Progressive WebApps, so I'm using IndexedDB.

DavidTaubmann
  • 290
  • 2
  • 6
  • 3
    Please note that recent Firefox versions and IE do not support WebSQL at all. – ocodo Mar 25 '15 at 00:00
  • 1
    As far as I know they have never supported WebSQL. You can check that here: [link]http://caniuse.com/#feat=sql-storage . The only one that amazes me is Opera Mini, they are loosing market this way. Anyways, for me as developer the only ones that matter are iOS and Android for WebApps, and sameways WebKit which I believe is both's systems engine. – DavidTaubmann Apr 08 '15 at 19:33
  • 1
    Nevertheless, no client-side storage standard has been adopted by all commercial browsers: http://www.html5rocks.com/en/features/storage – DavidTaubmann Apr 08 '15 at 20:39
  • 7
    Safari 13 has now removed [support for WebSQL](https://caniuse.com/#search=websql) that earlier versions had. So "No browser vendor requested removal of this technology, nor plan to remove it" is no longer true. – Thunderforge Oct 08 '19 at 19:51
  • @Thunderforge Thanks for the information! Really good to know! Thinking a bit forth, I don't know if this is going to be bad for devs o worse for iOS, since this tool has been complete and useful for us since so many years. We might recommend our users not to use or buy any more Mac or iOS devices, unless someone pays the costs for reprogramming the projects to indexedDB. – DavidTaubmann Oct 08 '19 at 20:07
  • 1
    This answer should definitely be updated - we shouldn't have to dig the comments to discover half of the argument is now invalid. – igorsantos07 May 02 '21 at 16:04
  • @igorsantos07 Done. – DavidTaubmann May 05 '21 at 00:12
  • So just ignore the Web all together and make native applications. I like it. Also, nothing is impeding me from compiling SQLite to webassembly. – Luiz Felipe May 24 '21 at 15:04
  • @igorsantos07 For web client-side DB's, we still have indexedDB, which has nowadays even more support than WebAssembly (96% vs 92%). Native is double or triple job if you want to cover as many users as possible, unless you have a specific need that still can't be done with Web (fewer every year). – DavidTaubmann Jun 08 '21 at 01:28
6

The reality is that the contributing parties reached an impasse on the direction of the standard. In short, no one could agree.

The W3C site explains this.

The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path.

WSC site

htm11h
  • 169
  • 1
  • 3
  • 2
    For me, this somehow means they agree there's nothing else to standardize in that path... It works fine the way it is because it connects the path of the standard to an existing third party technology that should/may not be standardized by them. – DavidTaubmann Apr 08 '15 at 19:54
  • For me that sound like: They disagreed on it, because it doesn't allow for vendor-specific features (embrace, extend, exterminate?). – Quandary Jul 17 '15 at 11:30
  • 1
    I believe it is some sort of vendor specific preference, the next sentence states that research is continuing. So I am not sure all parties were satisfied with the current state..."The Web Applications Working Group continues work on two other storage-related specifications: Web Storage and Indexed Database API. " – htm11h Feb 08 '16 at 13:42
  • 3
    @Quandary The opposite actually. There was only one vendor, and this was a problem because the bugs of that vendor became part of the standard. – user253751 Nov 06 '20 at 14:07
  • @user253751: the problem with that is just that the vendors can't decide on a uniform standard. So what happens in the end, is that the bugs of two vendors become part of a non-existing standard, and that's way worse. IndexedDb is a good example of that. And by good, I mean bad. – Quandary Nov 06 '20 at 14:39
  • @Quandary What are the bugs with IndexedDB? – user253751 Nov 06 '20 at 15:16
  • 1
    @user253751: A small collection of issues: https://developer.mozilla.org/en-US/docs/Web/API/IDBMutableFile https://stackoverflow.com/a/20675480/155077 https://github.com/dfahlander/Dexie.js/issues/317 https://stackoverflow.com/questions/14113278/storing-image-data-for-offline-web-application-client-side-storage-database https://stackoverflow.com/questions/9384128/how-to-delete-indexeddb https://stackoverflow.com/questions/3971813/html5-indexeddb-web-sql-database-and-browser-wars – Quandary Nov 06 '20 at 16:25
  • @Quandary I don't see any unfixable problems there. They all got fixed. Unlike the SQL one which is unfixable without writing a SQL validator in every browser. (and we all know what happens when you have a separate validator and parser - security bugs galore! the validator and parser must be the same code. which means they have to write a SQL parser and then re-write the SQL for SQLite...) – user253751 Nov 06 '20 at 16:30
  • 1
    @Quandary Half the things you linked aren't even bugs! "How do I delete an IndexedDB?" is not a bug. "What are the pros and cons of IndexedDB vs Web SQL vs Web Storage?" is not a bug. – user253751 Nov 06 '20 at 16:32
  • It is not that they couldn't agree. Rather they agreed to much! All of the vendors used the same implementation (Sqlite), but the W3C requires multiple independent implementations of a standard. – JacquesB May 05 '21 at 07:34
  • which is stupid because there's already a standard from SQL https://en.wikipedia.org/wiki/SQL:2016 – Luiz Felipe May 24 '21 at 15:05
2

Just posting to let you know that

  • just because WebSQL is deprecated/removed/unsupported in all the crappy browsers,
    you don't need to use IndexedDb.

You can use SQLite via WebAssembly or JS-Polyfill via sql.js.
That is to say,

  • SQLite compiled to WebAssembly (for modern browsers) **
  • and to JavaScript (for the die-hard browsers) .

(via emscripten). Cool, huh ?
Now, since IE/OldEdge and Firefox is basically already gone, this gives us room to manouvre Safari out of the way, which means the future for WebSQL is great again, even in the case that Google would remove it from Chrome/Chromium ;-)

Also, this allows us to ourselfs choose the SQLite version we want.
Actually, this is better than WebSQL.
Caveat: You need to store the data in IndexedDb for persistence.

https://github.com/sql-js/sql.js
https://github.com/kripken/sql.js
https://github.com/Philzen/WebSQL-Polyfill

Demo:
https://sql.js.org/examples/GUI/index.html

Quandary
  • 393
  • 3
  • 10