News for hackers. Stuff that matters.
Re:SQLite is not a toy database by petercooper on Thursday March 25, 2021 @11:16AM (#26581291)
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.

Re:SQLite is not a toy database by neerajdotname2 on Thursday March 25, 2021 @01:50PM (#26583272)
Using sql.js we have built online SQL course where the code is executed in the browser itself. https://academy.bigbinary.com/learn-sql
Re:SQLite is not a toy database by lilyball on Thursday March 25, 2021 @10:10PM (#26588112)
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.
Re:SQLite is not a toy database by syzygyhack on Saturday March 27, 2021 @11:36AM (#26603049)
This is cool!
Re:SQLite is not a toy database by gary_bernhardt on Thursday March 25, 2021 @01:31PM (#26583027)
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.

Re:SQLite is not a toy database by haolez on Thursday March 25, 2021 @12:18PM (#26582128)
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 :)

Re:SQLite is not a toy database by fabiospampinato on Thursday March 25, 2021 @12:44PM (#26582457)
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.
Re:SQLite is not a toy database by bogwog on Thursday March 25, 2021 @02:12PM (#26583493)
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).
Re:SQLite is not a toy database by hajile on Friday March 26, 2021 @04:25PM (#26596511)
I'd point out that it's 1.2MB uncompressed, but only around 390k compressed.
Re:SQLite is not a toy database by tln on Thursday March 25, 2021 @12:44PM (#26582453)
There's a 1.2 Mb WASM file as well
Re:SQLite is not a toy database by api on Thursday March 25, 2021 @12:15PM (#26582073)
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.
Re:SQLite is not a toy database by petercooper on Thursday March 25, 2021 @12:20PM (#26582148)
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).
Re:SQLite is not a toy database by rhombocombus on Thursday March 25, 2021 @12:31PM (#26582284)
I love it. SQLite is light, easy, and useful in a variety of situations.
Re:SQLite is not a toy database by no-s on Thursday March 25, 2021 @11:50AM (#26581757)
>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!

Re:SQLite is not a toy database by jakoblorz on Thursday March 25, 2021 @02:19PM (#26583568)
sqlite can actually be scaled quite high: 4mio qps (queries per second) https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
Re:SQLite is not a toy database by xnx on Thursday March 25, 2021 @08:09PM (#26587336)
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.
Re:SQLite is not a toy database by edwinyzh on Friday March 26, 2021 @02:55AM (#26589451)
Can you share more details? Why Mozilla opposed SQLite?
Re:SQLite is not a toy database by xnx on Friday March 26, 2021 @10:08AM (#26592320)
Re:SQLite is not a toy database by faizshah on Thursday March 25, 2021 @02:11PM (#26583490)
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...
Re:SQLite is not a toy database by napsterbr on Thursday March 25, 2021 @02:28PM (#26583656)
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.

Re:SQLite is not a toy database by faizshah on Thursday March 25, 2021 @04:06PM (#26584751)
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.
Re:SQLite is not a toy database by napsterbr on Thursday March 25, 2021 @04:40PM (#26585107)
Definitely not your fault :)
Re:SQLite is not a toy database by fiddlerwoaroof on Thursday March 25, 2021 @02:56PM (#26583990)
I believe jwz did this first
Re:SQLite is not a toy database by setr on Thursday March 25, 2021 @02:50PM (#26583900)
well, he doesn't like one particular group of people linking to it :-)
Re:SQLite is not a toy database by kroggen on Thursday March 25, 2021 @03:20PM (#26584278)
we can also have a SQLite database with branches, like in Git:

https://github.com/aergoio/litetree

Re:SQLite is not a toy database by dheera on Thursday March 25, 2021 @11:36AM (#26581563)
Which if any of these is efficient enough to run on mobile and can cerealize an entire database to localStorage?
Re:SQLite is not a toy database by WJW on Thursday March 25, 2021 @12:31PM (#26582280)
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.
Re:SQLite is not a toy database by dheera on Thursday March 25, 2021 @04:15PM (#26584825)
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.

Re:SQLite is not a toy database by tehbeard on Thursday March 25, 2021 @04:50PM (#26585205)
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

Re:SQLite is not a toy database by kpkrish on Thursday March 25, 2021 @09:52PM (#26587995)
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.

Re:SQLite is not a toy database by tehbeard on Friday March 26, 2021 @04:57AM (#26590034)
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.

Re:SQLite is not a toy database by yrrr on Thursday March 25, 2021 @02:43PM (#26583835)
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.