Lovefield – A relational database for web apps | Hacker News Reader

Lovefield – A relational database for web apps

google.github.io

198 pointsdumindunuwan55 comments

From the first 1 minute and 15 seconds of the video:

> "With WebSQL being deprecated, and IndexedDB not providing structured queries, web developers need a tool to satisfy their structured query needs."

> "Web app developers need structured queries to work in the mobile world."

> "With IndexedDB ... there's a steeeep learning curve to make it useful for your app. Moreover, IndexedDB does not provide structured queries."

> "IndexedDB does not offer structured query features, such as sorting by multiple columns, or joining the results of multiple tables."

As someone that looked forward to WebSQL, this is painful to hear. Many of us realized the above from the beginning. Yet, some in the standards committee found a way to shoot down WebSQL, w/vague rationale such as "we don't feel it's the best path for the web".

Well, thank you. Instead of giving us one of the most well tested SQL implementations (SQL Lite) that addresses all of the above, and much more, we were shoved a harder to use, more verbose, and less powerful alternative (IndexedDB). And as a result, we're having to implement our own relational databases. In JavaScript. 4-5 years later.

No offense to Lovefield, but it represents everything wrong w/the decision to deprecate WebSQL and implement IndexedDB instead. It was also the single-most decision that made me lose faith in the standards committee.

I see the opposite. I see it as everything that was right about the decision not to include WebSQL. Specifically (a) you didn't needed WebSQL. See Lovefield. And (b) you can implement whatever you like that fits your needs instead if trying to shoehorn the one true solution.

The same is true of many other parts of the web. I'm glad O3D failed (I was on that team) and I'm glad something like Unity/X3D/VRML isn't built into the web. I'm glad instead we have something low-level like WebGL that people can build many and varied higher level solutions on (Three.js, X3D, Unity->WebGL, Unreal->WebGL, SceneGL, Babylon3D, Pixi.js, etc.) You pick the solution that best fits your project.

Lower-level building blocks are better IMO in most cases. They are easier to test. Have less incompatible edge cases and leave it up to devs to build or pick a solution that's right for their needs.

> It was also the single-most decision that made me lose faith in the standards committee.

When I try to think of any product or service born of a standards committee that has been wildly successful, I can't think of any.

Standards committees are useful if the goal is to bring consensus-driven stagnation (and standards), so that things can evolve around a group of products/services rather than just individual products/services. But, there have been extremely complicated standards like some of those from OASIS: https://www.oasis-open.org/ that made developers waste countless hours trying to adhere when a high level of complexity wasn't necessary. And even well-used standards like SQL have not moved quickly enough with the times, e.g. if you look at the differences between the major databases (PostgreSQL, Oracle, SQL Server, etc.), there are still many DB-specific features that could be standardized.

> Instead of giving us one of the most well tested SQL implementations (SQL Lite) that addresses all of the above, and much more

I wish embedding PostgreSQL would get to the point that people would think of it before SQLite, especially in situations like this that are generic, because there is so much more that could be done with it. Here's one recent effort in Java: https://github.com/yandex-qatools/postgresql-embedded and a slidedeck from a few years ago from the postgres lead at VMWare with that team's experience: http://www.slideshare.net/jkshah/pg-conf-eu2013embedded

> When I try to think of any product or service born of a standards committee that has been wildly successful, I can't think of any.

He says in a post submitted over HTTP, using DNS for name lookup, TCP for OSI layer 3 transport, implemented in client software via POSIX socket APIs, encapsulated in IEEE 802.2 frames traveling across IEEE 802.3 and IEEE 802.11-defined physical mediums, routed by BGP and maybe even some OSPF.

Come on. You really can't think of any?

Are any of those except maybe 802.11 born of a standards committee?

off the top of my head: HTTP came from CERN and w3c was years later; TCP is the alternative to the stillborn OSI standard; POSIX is a relatively recent AT&T UNIX-clone standardization effort; Ethernet was from Xerox PARC (although most of a modern implementation is from the IEEE era)

Exactly - most of these became standards after an entity produced a good solution in the real world and it became useful to formalize them and evolve collectively.

What qualifies as "born"?

Successful standards committees are just a group of implementing industry members. WebSQL, for example, was edited by Ian Hickson.

The work almost always predates the standards committee -- also just like WebSQL.

The decision to not standardize WebSQL as proposed was correct -- because it wasn't a specification of behavior for which independent implementations were going to happen. It was, effectively even if not in principle, a mandate of a particular implementation.

That's not to say a web-platform relational API (even one based on SQL) standard would be a bad thing -- I think it would be a good thing. And its not to say that using SQLite as a key part of the stack in an implementation of such a standard would be a bad thing, either. But "behave exactly as version n of SQLite behaves" is not appropriate for a Web API standard, any more than "behave exactly as version n of Internet Explorer" would be.

If you are ever curious about why WebSQL was shot down, how it happened in excruciating detail:

http://nolanlawson.com/2014/04/26/web-sql-database-in-memori...

FYI, it's a common mistake, but it's just SQLite. :)

Another database which has similar characteristics (although not quite sql-like api's) that I've had a lot of luck with is https://github.com/tonsky/datascript. Bit of a tangent, but anyone looking for this kind of functionality might be interested.

What is a browser database and what is it good for? Would you use one instead of a back-end DB or in conjunction with one? How? Why? Why is this on 1st page - what is cool about it?

I'm clearly missing something, but I can't be bothered to watch the videos - can someone TL;DR it for me, please.

It's like having PouchDB -> CouchDB but with SQL not NoSQL

For me I'd like to:

Firebase as backend. Sync user data to browser. Query user data elegantly in browser using select statements.

> It's like having PouchDB -> CouchDB but with SQL not NoSQL

Also, without the replication and conflict detection.

Better way to describe it was "like SQLite"

Have you seen http://gunDB.io (disclosure: I am the author)? It has what you are describing. As well as replication and conflict resolution that the other commenter mentions.

Offline-first web apps. Think of all of the "native mobile apps" that could actually be done as a web app, but instead get built as native applications just because the application needs to store data.

Also Electron apps. It's currently pretty painful getting sqlite working with Electron, this could be a nice alternative.

Have you tried the emscripten port of SQLite? It's worked well for me... http://github.com/kripken/sql.js

I've tried it, the main problem I have withe it is that the whole DB has to be loaded into memory. It seems like it would be slow and unwieldy with even a moderate sized database. I'm still keeping it open as an option, but I like that Lovefield uses indexedb under the sheets.

We have a web app that downloads a large list of simple repetitive maintenance tasks ("check amount of spare printer toner", "water the office plants", and such) with documentation, images, etc. that need to be performed regularly, and then shows the user that list so he can perform the tasks and send back the results. Some companies using this app do not allow internet access in the specific areas where tasks have to be performed, so some parts of the app have to work without it. Business requirement was "store enough info on the device to be able to run without internet access for one month".

Without an in-browser persistent database (we used WebSQL, with all the future risks), it would require some interesting workarounds (can you serialize multiple megabytes to local storage? how long will it take? will it rollback from a fault correctly?) or Phonegap type solutions.

I implemented a character manager using TaffyDB[1], loading lots of static data upfront. Made calculations using cross "table" lookups much simpler from a coding perspective.

[1] http://www.taffydb.com

Any data-heavy applications that have significant grouping/filtering/etc.. SQL is very powerful, and when you try to work with large datasets in the browser, its kind of disappointing that you have to either transfer a lot of data on every data view change or do SQL's job with lodash collection methods.

What happens when the user opens two tabs, both of which try to access the same local database?

I would hope that there are some kind of locking mechanisms in place, at least at the table level, if not the row level.

Disclosure: I'm the author of a competing JavaScript database (http://gunDB.io/).

Unfortunately concurrency is typically the last thing that people think about. Concurrency is the first thing we deal with in our database, because you are right, without it data gets corrupted.

So would it be possible to communicate between the two tabs, via the database? In an event-driven way?

Yes, although right now I am doing that with websockets which is silly for local stuff. We are wanting to add WebRTC as well, but we could also add what you are talking about - make an issue for it on the github (although it will probably be low priority)

Check out intercom on github for inter-tab communication. I use it in we apps for many different things. Including log-back-in. One tab gets a session timeout and tells them all to go black asking for a password. Enter it in one tab and they all unlock.

It uses local storage

https://groups.google.com/forum/#!topic/lovefield-users/Xs97...

Interesting. At first I was thinking this might be part of their cross-platform (iOS/Android/web) architecture that they use for Inbox, Sheets, and a few other things.

E.g. they assert that, despite having pure-native views (e.g. no Swing-style "one UI for all platforms"), they share ~70% of the client-side code across iOS/Android/web.

Which to me insinuates the reused code is probably things like the domain models, validation rules, and also online/offline data storage/sync logic.

E.g. maybe Lovefield was part of that cross-platform client-side storage architecture. But probably not since it's Javascript. (Which is fine, Google is a big place/lots of different apps/needs.)

iOS/Android have SQLLite, LoveField is kind of like an SQL-lite for the Web.

Your speculation isn't that far off.

This is good for people used to sql but I personally find the way that IndexDB stores data to be superior. You just have to get used to the NoSQL way of persisting objects. Making one technology look like another is never efficient for an app.

Would be interesting to compare it to AlaSQL (https://github.com/agershun/alasql).

The api style looks very similar to JOOQ's one.

1: http://www.jooq.org/doc/3.6/manual/sql-building/sql-statemen...

The whole saga is very sad. WebSQL was the right move all along. NoSQL turned out to be a fad and people really just want to use SQL to build real apps. Especially newbies, learning SQL is a lot easier than learning how to reimplement it's features on top of a document store.

Lovefield is still relatively new and immature. When I tried to use it I ran into some strange errors. The situation might have improved now but I personally would still recommend PouchDB instead.

Niiice... last time I tried to get something done with IndexedDB it was total pita. If this manages to take away even 20% of IndexedDB's pain, it's sure worth taking a look at!

I'm not a web developer, but this looks great, I want to learn how SQL things work, maybe this is a good start for beginner ? BTW, speaker's face is too serious.

I would think you'd want to use a more mature/complete SQL database, with broader documentation and a larger community, to learn about SQL. MySQL or PostgreSQL, would be the obvious free choices. Unless you specifically want to learn about client-side browser-based SQL databases (which is a pretty specific niche of SQL database), in which case this would be interesting, but possibly not extremely relevant to development work you'd be doing today, since it is so new.

But a more mature/complete SQL database mostly write in c/c++, and the old design is very complex and hard to change.

Then why did they killed websql in chrome packaged apps?

probably because websql was not accepted as a w3c standard. Browser vendors should not add tech that works only on their browser and are not on the path to standardization.

Yeah, that was a bummer situation.

The problem was that browser APIs require independent implementations to become standard. Chrome used SQLite for their WebSQL, which left Firefox without a good option. (Implementing a SQLite clone isn't feasible with their budget.) So, the standard was dropped.

It's a shame, because SQLite is a great tech.

Why couldn't Firefox use SQLite?

There's a W3C rule, that each browser needs their own implementation. If everybody uses the same code, it's considered a Monoculture security risk [1].

1. https://www.schneier.com/blog/archives/2010/12/software_mono...

Thanks, that's a great article. May be worth a repost since it's been 5 years since it was last posted (according to my cursory searches).

Somebody posted it: https://news.ycombinator.com/item?id=10199163

ffs

I wonder then how come so many browsers are allowed to be Webkit based

Browsers are "allowed" to do whatever they want, but the W3C is very unlikely to endorse some feature as a standard if Webkit is the only codebase that intends to implement it. Which is basically what happened with WebSQL.

I ask myself the same thing regarding the file system API. It's like Mozilla and co are against serious offline apps that would have taken advantage of these 2 features heavily. And no , IndexedDB is not a replacement for these.

They could, but "use SQLite" is not a spec.

If all implementations use the same code, then they have the same bugs and quirks (and sqlite, being very lite on the SQL standard, has many quirks).

When all implementations have the same bugs/quirks then websites can end up (inadvertently) relying on these bugs, and this makes any future fixes or upgrades to the underlying sqlite very risky (almost guaranteed to break some sites on the net).

It's the situation IE6-only intranet sites ended up with: depending on every dark corner of a particular implementation, and then couldn't even work with IE7/8/9.

So similarly the WebSQL standard would de-facto freeze at some sqlite3 version, and wouldn't be able to upgrade to sqlite4/5/6 until all sites on the web are compatible, which won't happen (and we'd have "quirks modes" for websql versions, etc.)

The whole mess is largely avoided when there are multiple independent implementations. When each implementation has different bugs, then developers will notice that their code works in one browser, but not another, and see what's a bug and what is a feature.

wow, the landing page of this web is really fast...

This just goes to prove that what developers really want out of browsers is not a document viewer but a deployment platform. To go to these lengths, someone has to be very concerned about getting one set of code to run on practically any device. You don't otherwise end up with something like this.

This wouldn't have been possible without the work the Chromium team has done to achieve such complete compatibility between desktop and mobile versions. But unfortunately the browser is still stuck in the mentality that it's just a document viewer. I think we really need to wake up and make the browser into a proper deployment platform. I applaud efforts like this, even if I don't immediately have a need for such a thing, because they are doing the necessary work to push on browser developers to release the right features towards that goal.

Reply on news.ycombinator.com