4 ms·
I believe SQLite is about to explode in usage into areas it’s not been used before. SQL.js[0] and the incredible “Absurd SQL”[1] are making it possible to buil
by samwillis 5y ago
I believe SQLite is about to explode in usage into areas it’s not been used before.
SQL.js[0] and the incredible “Absurd SQL”[1] are making it possible to build PWAs and hybrid mobile apps with a local SQL db. Absurd SQL uses IndexedDB as a block store fs for SQLite so you don’t have to load the whole db into memory and get atomic writes.
Also I recently discovered the Session Extension[2] which would potentially enable offline distributed updates with eventual consistency!
I can imagine building a SAAS app where each customer has a “workspace” each as a single SQLite db, and a hybrid/PWA app which either uses a local copy of the SQLite db synced with the session extension or uses a serveless backend (like CloudFlare workers) where a lightweight function performs the db operations. I haven’t yet found a nice way to run SQLite on CloudFlare workers, it need some sort of block storage, but it can’t be far off.
0: https://sql.js.org/ https://sql.js.org/
1: https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql
2: https://www.sqlite.org/sessionintro.html https://www.sqlite.org/sessionintro.html
- paulryanrogers 5y agoI evaluated sqlite for a web extension but ultimately decided it wasn't worth it. There is no easy way to save the data directly to the file system. And saving the data in other ways meant I was probably better off with IndexDB instead. Still it is a tempting option and one that seems to work well for separate tenancy.
- dorian-marchal 5y ago> There is no easy way to save the data directly to the file system. That's what absurd SQL is for (link in the parent comment).
- paulryanrogers 5y agoI read that one and agree it feels absurd. Not something I want to depend on.
- dorian-marchal 5y agoI believe the absurd things are: - the idea (i.e. storing a database in a database) - the fact that the resulting storage is faster than vanilla indexed db. The repo's readme is a bit misleading and it does make it look like a mad scientist experiment but you can read more about it here (really interesting stuff): https://jlongster.com/future-sql-web https://jlongster.com/future-sql-web Obviously this project is still young, but I find it convincing (disclaimer: I don't use it yet).
- samwillis 5y agoI’m 100% sure this is (very nearly) suitable for production. It works and works well. It is only a matter of time before browsers implement the proposed sandboxed virtual file system apis with block level access and this would be superseded.
- PopeUrbanX 5y agoDo you know of any oss projects using absurd? Or maybe an ORM built on top of it? It doesn't seem like there's any ecosystem built around it yet, and their example project is too trivial to be useful. I'd have to have to built even the most basic CRUD functionality from scratch.
- jitl 5y agoIt’s really not that much trouble. Most of the ORM like things over IndexedDB introduce extra bugs and slowness for you to deal with anyways - SQLite is millions of times easier to use than IndexedDB, so you’re getting a good deal out of considering AbsurdSQL “just” an ORM for IndexedD… as long as you don’t need to support Safari.
- PopeUrbanX 5y agoYeah but you don't need to go through a web worker to use any other database product. Seems like with absurd you'd have to create a separate worker for each query and write your CRUD stuff in a potentially clunky, asynchronous style by passing messages to/from those workers. Sounds painful to me.
- koeng 5y agoThe golang equivalent of native SQL can be found here - https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite Very useful for pure go applications!
- stjohnswarts 5y agoWouldn't that be leaving one huge advantage of sqlite behind (namely it's insane test regimen and battle tested history)?
- koeng 5y agoIt’s cross compiled, so all the tests are brought along and test fine. And god, there are so many tests.
- dorian-marchal 5y agoHow would we deal with conflicts (e.g. syncing back several conflicting offline clients for the same user) with something based on the Session Extension?
- samwillis 5y agoYou have to handle the merge conflicts yourself, see https://www.sqlite.org/session/sqlite3changeset_apply.html https://www.sqlite.org/session/sqlite3changeset_apply.html So you need to be carful how you design your schema, but very possible. One option is to use something like Yjs and a JSON column to get proper CRDTs for merging. https://github.com/yjs/yjs https://github.com/yjs/yjs
- pfarrell 5y ago> I can imagine building a SAAS app where each customer has a “workspace” each as a single SQLite db I did just that at my (now defunct) startup a few years ago. We were building a collaborative database query tool. The software we built used sqlite to keep a local db for storing user credentials, caching query results, etc. Bonus, we were able to have the local database file be encrypted protecting the data at rest.
- edwinyzh 5y ago> we were able to have the local database file be encrypted protecting the data at rest. How do you store the password locally? Or do you requesting the passwords from a remote server?
- lenkite 5y agoHow does one deal with fault tolerance ?
- bsenftner 5y agoUsing the transaction API for modifications, and ordinary fault tolerance measures for the hosting application. It is no more difficult than any other app-foundation technical feature.
- rmbyrro 5y agoYou can run it on AWS Lambda and store the SQLite file(s) on EBS [1]. [1] https://aws.amazon.com/blogs/compute/using-amazon-efs-for-aws-lambda-in-your-serverless-applications/ https://aws.amazon.com/blogs/compute/using-amazon-efs-for-aw...
- metaltyphoon 5y agoI wouldn’t use a SQLite file over NFS. It has caused me locking problems when used by multiple clients.
- rmbyrro 5y agoNFS does have a relatively low limit in terms of lock. For some use cases it can handle it fine. Having clients running on read-only mode, when possible, can help. Also partitioning the database can help, but then it's too much trouble already.
- otterley 5y agoThat's EFS, not EBS. EFS is file-level storage (i.e., NFS); EBS is block-level storage (SAN). AWS Lambda only supports EFS.
- rmbyrro 5y agoCorrect, I mispelled. The link points to the correct service.
- hinkley 5y agoWhen they tried to put SQLite into browsers I was excited but I had no project that needed it. It took years to finally get there, and wouldn’t you know it, they had just marked it as deprecated. KV stores are just not the same.
- deleted 5y ago[deleted]