Comment by samwillis | Hacker News Reader

Comment by samwillis | Hacker News Reader

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/

1: https://github.com/jlongster/absurd-sql

2: https://www.sqlite.org/sessionintro.html

I 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.

> 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).

I read that one and agree it feels absurd. Not something I want to depend on.

I 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

Obviously this project is still young, but I find it convincing (disclaimer: I don't use it yet).

I’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.

Do 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.

It’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.

Yeah 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.

When 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.

You 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-aw...

That's EFS, not EBS. EFS is file-level storage (i.e., NFS); EBS is block-level storage (SAN). AWS Lambda only supports EFS.

Correct, I mispelled. The link points to the correct service.

I wouldn’t use a SQLite file over NFS. It has caused me locking problems when used by multiple clients.

NFS 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.

> 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.

> 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?

The golang equivalent of native SQL can be found here - https://pkg.go.dev/modernc.org/sqlite

Very useful for pure go applications!

Wouldn't that be leaving one huge advantage of sqlite behind (namely it's insane test regimen and battle tested history)?

It’s cross compiled, so all the tests are brought along and test fine. And god, there are so many tests.

How would we deal with conflicts (e.g. syncing back several conflicting offline clients for the same user) with something based on the Session Extension?

You have to handle the merge conflicts yourself, see 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

How does one deal with fault tolerance ?

Using 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.

Reply on news.ycombinator.com