Hacker Remix

Ask HN: Wouldn't it be cool to have a Supabase for SQLite?

26 points by thenorthbay 1 year ago | 26 comments

Wouldn't it be cool to have a Supabase for SQLite?

The core idea here is: let SQLite run next to your application on the server; but have all features a client-server database give you.

What's the spec for this?

- SQLite runs next to server as production database

- That way, reads and writes are very fast

- In dev, some sort of worker auto-copies the prod DB to the local repo. Production bugs can be reproduced easily and code fixed quickly

- Have an interface that lets you access, view, and modify data in the production DB, kind of like Firebase. Might need a server of its own... or couldn't that just be the app server itself?

- SQLite auto-backs up to a bucket (like Litestream)

I kinda really want this, but haven't found anything quite like it. I've seen Turso, but it seems they focus more on global replication instead of the OSS developer experience I'm looking for.

What do you think? What am I missing?

whodev 1 year ago

All the features you requested Turso can do, no?

> - SQLite runs next to server as production database

Embedded Replicas[1]

> - In dev, some sort of worker auto-copies the prod DB to the local repo. Production bugs can be reproduced easily and code fixed quickly

Maybe not exactly this, but you can replicate a DB from another with the CLI tool. Then just use that as a dev db.

> - Have an interface that lets you access, view, and modify data in the production DB, kind of like Firebase. Might need a server of its own... or couldn't that just be the app server itself?

I don't use their web interface, but I think it does allow this? I don't know for sure though.

> - SQLite auto-backs up to a bucket (like Litestream)

Turso does have point-in-time recovery[2]

[1] https://docs.turso.tech/features/embedded-replicas/introduct... [2] https://docs.turso.tech/features/point-in-time-recovery

0x_rs 1 year ago

It's an obvious question, but have you looked into Pocketbase?

https://github.com/pocketbase/pocketbase

xrd 1 year ago

Pocketbase is awesome. And, you can add to pb_hooks and customize the server (in golang or JS) and do whatever you want in addition to the already incredible feature set. And, it works with litestream, so you can automatically backup and restore.

thenorthbay 1 year ago

that's pretty cool! Would be great to have it for TS to configure my own backend

d1sxeyes 1 year ago

You can use a third party NPM module to introspect the DB and write out types for you. Can’t remember what it’s called off the top of my head, but maybe “pocketbase-type gen” or something like that.

iFire 1 year ago

I've been keeping a fork of MVSqlite (sqlite on foundationdb) maintained.

What is Mvsqlite? According to the author it's a distributed, MVCC SQLite that runs on top of FoundationDB.

https://github.com/V-Sekai/mvsqlite

Made an Elixir client, a Godot Engine client and CLI.

Supabase on Mvsqlite would be great! I am still puzzling over Mvsqlite's write performance, but its read performance makes sense to me.

# ycsb (workloadf, 10000, --wire-zstd)

Run finished, takes 1m18.086881433s

READ - Takes(s): 78.1, Count: 99957, OPS: 1280.3, Avg(us): 27061, Min(us): 1409, Max(us): 98751, 99th(us): 59391, 99.9th(us): 81791, 99.99th(us): 94591

READ_MODIFY_WRITE - Takes(s): 78.0, Count: 50061, OPS: 641.6, Avg(us): 69636, Min(us): 13320, Max(us): 205823, 99th(us): 121215, 99.9th(us): 189823, 99.99th(us): 203007

UPDATE - Takes(s): 78.0, Count: 50095, OPS: 642.0, Avg(us): 42630, Min(us): 7792, Max(us): 200319, 99th(us): 81535, 99.9th(us): 175487, 99.99th(us): 195199

From the github actions tests the original author wrote.

adius 1 year ago

Working on it! => https://www.airsequel.com