6 ms·
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 i
by petercooper 6y ago
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 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/ https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replication system.
https://sqlite.org/lang_with.html#rcex3 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 https://github.com/aergoio/aergolite - AergoLite is replicated SQLite but secured by a blockchain.
https://github.com/simonw/datasette 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/ 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.
- dheera 6y agoWhich if any of these is efficient enough to run on mobile and can cerealize an entire database to localStorage?
- WJW 6y agoSQLite 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.
- yrrr 6y agolocalStorage 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.
- dheera 6y agoI 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.
- tehbeard 6y agoReally 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
- kpkrish 6y agoIs 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.
- tehbeard 6y agoindexedDB 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/ https://dexie.org/> or <https://pouchdb.com/ https://pouchdb.com/> ) or you'd roll your own atop whatever you were using.
- no-s 6y ago>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!
- api 6y agoSQLite 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.
- petercooper 6y agoAgreed. 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).
- rhombocombus 6y agoI love it. SQLite is light, easy, and useful in a variety of situations.
- haolez 6y agoWow! 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 :)
- tln 6y agoThere's a 1.2 Mb WASM file as well
- fabiospampinato 6y agoNope, try using the demo website, it loads a 1.2mb wasm file (https://sql.js.org/dist/sql-wasm.wasm https://sql.js.org/dist/sql-wasm.wasm). SQLite might be impressively small but it's not _that_ small.
- bogwog 6y agoI 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).
- deleted 6y ago[deleted]
- hajile 6y agoI'd point out that it's 1.2MB uncompressed, but only around 390k compressed.
- gary_bernhardt 6y agoSeconded; it's a fantastic tool and ecosystem. We use sql.js in production (https://www.executeprogram.com 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.
- neerajdotname2 6y agoUsing sql.js we have built online SQL course where the code is executed in the browser itself. https://academy.bigbinary.com/learn-sql https://academy.bigbinary.com/learn-sql
- lilyball 6y agoThat’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.
- syzygyhack 6y agoThis is cool!
- faizshah 6y agoThere'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://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ https://web.archive.org/web/20141127001741/https://charlesle...
- napsterbr 6y agoClicking the above link takes me to https://imgur.com/32R3qLv https://imgur.com/32R3qLv. I fail to understand why have a blog at all if its author don't like people linking to it.
- setr 6y agowell, he doesn't like one particular group of people linking to it :-)
- fiddlerwoaroof 6y agoI believe jwz did this first
- faizshah 6y agoHow 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.
- napsterbr 6y agoDefinitely not your fault :)
- jakoblorz 6y agosqlite can actually be scaled quite high: 4mio qps (queries per second) https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
- kroggen 6y agowe can also have a SQLite database with branches, like in Git: https://github.com/aergoio/litetree https://github.com/aergoio/litetree
- xnx 6y agoIt'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.
- edwinyzh 6y agoCan you share more details? Why Mozilla opposed SQLite?
- xnx 6y agoMozilla's long justification https://hacks.mozilla.org/2010/06/beyond-html5-database-apis-and-the-road-to-indexeddb/ https://hacks.mozilla.org/2010/06/beyond-html5-database-apis... More interesting commentary here https://softwareengineering.stackexchange.com/questions/220254/why-is-web-sql-database-deprecated https://softwareengineering.stackexchange.com/questions/2202...