Comment by petercooper | Hacker News Reader

Comment by petercooper | Hacker News Reader

Parent Full thread

With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further:

https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten.

https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replication system.

https://sqlite.org/lang_with.html#rcex3 - you can do graph-style queries against SQLite too (briefly mentioned in the article).

https://github.com/aergoio/aergolite - AergoLite is replicated SQLite but secured by a blockchain.

https://github.com/simonw/datasette - Datasette (mentioned at the very end of the OP article) is a tool for offering up an SQLite database as a Web accessible service - you can do queries, data analysis, etc. on top of it. I believe Simon, the creator, frequents HN too and is a true SQLite power user :-)

https://dogsheep.github.io/ - Dogsheep is a whole roster of tools for doing personal analytics (e.g. analyzing your GitHub or Twitter use, say) using SQLite and Datasette.

Using sql.js we have built online SQL course where the code is executed in the browser itself. https://academy.bigbinary.com/learn-sql

That’s pretty cool! Thanks for sharing this. My SQL knowledge has been a hodgepodge of random stuff for years, so maybe going through your course will help fill in the gaps.

This is cool!

Seconded; it's a fantastic tool and ecosystem. We use sql.js in production (https://www.executeprogram.com). The SQL course's live code examples all run directly in the browser via sql.js.

I initially had low expectations because it's such a weird use case, but it's been totally reliable. We did have to ignore a few types of errors from old browsers that don't support wasm properly, but we've never had a bug in current browsers caused by sql.js.

Wow! The sql.js bundle is only 8kb. This is a hidden gem for sure. Thanks for pointing it out!

EDIT: It's actually 1.2MB. Thanks for pointing it out :)

Nope, try using the demo website, it loads a 1.2mb wasm file (https://sql.js.org/dist/sql-wasm.wasm). SQLite might be impressively small but it's not _that_ small.

I was actually looking into options for storing some data for a game I'm working on a while back, and decided against using SQLite because it was too big of a dependency (project has a specific size limit/target).

I'd point out that it's 1.2MB uncompressed, but only around 390k compressed.

There's a 1.2 Mb WASM file as well

SQLite is one of those things that also shows by contrast how bloated and inefficient much of modern software is. It accomplishes so much so efficiently with such a small footprint.

Agreed. There's a real beauty to it, to me, like looking at a Swiss watch or something. Redis gives me similar vibes though is a little more complex given its networked nature (although the C is nice to read).

I love it. SQLite is light, easy, and useful in a variety of situations.

>so I have a bunch of links some of you might find interesting if you want to dig further

oooh, thank you! I'm starting to see SQLite the same way!

sqlite can actually be scaled quite high: 4mio qps (queries per second) https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

It's truly a shame that Web SQL was opposed by Mozilla. The web would be a better place if a tool as powerful as SQLite was available by default on billions of devices.

Can you share more details? Why Mozilla opposed SQLite?

Mozilla's long justification https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...

More interesting commentary here https://softwareengineering.stackexchange.com/questions/2202...

There's also a pretty nice built-in sqlite extension for transitive closures that helps in searching hierarchical/tree structures in sqlite: https://web.archive.org/web/20141127001741/https://charlesle...

Clicking the above link takes me to https://imgur.com/32R3qLv.

I fail to understand why have a blog at all if its author don't like people linking to it.

How strange, sorry, I didn't know that it did that. Just updated it to an archive link. This is the only source I know of that gives an overview of closures.c so it's worth the read.

Definitely not your fault :)

I believe jwz did this first

well, he doesn't like one particular group of people linking to it :-)

we can also have a SQLite database with branches, like in Git:

https://github.com/aergoio/litetree

Which if any of these is efficient enough to run on mobile and can cerealize an entire database to localStorage?

SQLite underpins large parts of both iOS and Android, so... all of them? Whether you can serialize "an entire database" just depends on the size of the database and whether it will fit in the local storage. SQLite scales up to a few TB at least, so that is unlikely to be the bottleneck.

I don't fully get it -- this is an SQlite implementation in JavaScript, which is different from what you'd use in a native app. Unless you store it to localStorage or cookies, all variables disappear when you navigate away from the page.

Can you write an offline HTML5 webapp with any of these libraries such that it can cerealize the entire database into a string and then store that to localStorage and reload the next time?

It's an honest question and not sure why people are downvoting without giving reasons.

Really you'd want to do this in to an indexeddb store rather than local storage. Less issues with size limits and string serialisation as you can chuck Blob instances straight into it.

You'd have to handle loading/saving though

Is there some way by which the indexedDB store can stay synchronised with the server database running in the backend.

Something simple so that the front-end app developer can just focus on implementing business requirements.

indexedDB is too low level for that kind of thing by itself. It's just a fast Key-Value store with secondary indexes that's in your browser.

You either need to find a solution that has a sync system built in (such as <https://dexie.org/> or <https://pouchdb.com/> ) or you'd roll your own atop whatever you were using.

localStorage is a web API, the storage limit is ~5MB.

However it can only store strings so it's pretty taxing.

Chrome and Safari actually embed SQLite directly as WebSQL, however it's not becoming standardized because Mozilla doesnt consider "add SQLite" as a sensible web standard.

Reply on news.ycombinator.com