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

26 points by thenorthbay 2 years 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 2 years 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 2 years ago |

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

https://github.com/pocketbase/pocketbase

xrd 2 years 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 2 years ago | |

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

d1sxeyes 2 years 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 2 years 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 2 years ago |

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

DevNinjaS 2 years ago |

Hey thenorthbay,

I gotta say, your idea of a Supabase-like system for SQLite got me thinking. It's an interesting concept. SQLite running next to your server could certainly speed things up, but don't forget about potential issues with concurrent writes. SQLite might not be as graceful as other databases in handling those.

The dev-prod synchronization you mentioned is a neat idea. Just remember to keep things secure and don't expose sensitive data. Also, think about the size of your production database. If it's too big, this might not be the best way to go.

Having an interface to access, view, and modify data straight from the production DB would be super useful. Depending on how complex this gets, you could either make it part of the application server or set up a separate server.

Auto-backup to a bucket is definitely a thumbs up from me. Tools like Litestream can be a lifesaver here.

Have you heard of Datasette? It's an open-source tool that lets you explore and publish data from SQLite databases. It could be a good starting point for what you're trying to build.

Your idea sounds like it could really shake things up. Keep us posted on how it goes. Good luck!

exodust 2 years ago | |

This reads like an AI-generated comment. DevNinjaS, can you confirm?

PurpleRamen 2 years ago |

So like a normal SQL-server (PostgreSQL, MariaDB..), but with SQLite as backend. Which makes moving data simpler, as you only need to copy a file and can thus utilize the whole shell scripting-power. Well, seems like a nice idea for the dev, but how well can SQLite perform in a real production-environments with parallel writing? The only solutions I've seen so far are either relative poor, or "cheat" by using a separate file for each customer, which is not working with all use cases.

Anyway, there is a new generation of database-systems/frontends, like Grist, NocoDB, Airtable, etc. But they are more focused on a user-friendly all round experience, not so much a good experience. But some of them do support working with SQLite.

datascienced 2 years ago |

Leaderless, eventually consistent DB, with DB running on same servers as app.

Could be good, you need to rethink all the distributed DB stuff.

Does a node deal with certain subset (e.g. certain customers) so you use customerid as a partition key.

CRDTs?

Once a node fills up to say x% capacity do you split that out (using consistent hashing to minimize effect of such spits).

You probably end up reinventing one of the distributed DBs once you make it production ready.

I think it is a lot of work unless you have simplifying requirements e.g. just give each customer a seperate VM and then use a mounted cloud storage to scale up space to some max like 10Tb then maybe cap it there. Write your db log to s3 glacier for a backup.

Second replica for failover.

orm 2 years ago |

Not directly an answer, but similar what if thinking got me wondering: wouldn't it be cool to be able to use postgres much like how you use sqlite with python? I implemented this idea as a pip-installable python package, https://github.com/orm011/pgserver, and your feedback would be great :) I use it for my projects.

khimaros 2 years ago | |

neat project! how have you found resource usage and startup time compares with SQLite?

orm 2 years ago | | |

I have not compared it. Have you had issues in this front?

If you are asking in human terms, its instantaneous to start, it simply creates a few files etc and starts a process. If you were using Sqlite, there is no extra associated process. But the processes are simply waiting for your input, so normally this is not really extra work.

In benchmark terms, the way I'm using it startup time is not a huge issue, instead being able to use postgres is helpful (has extensions and you know you can eventually move to a hosted postgres if you want)

If startup really matters, the startup can be amortized (if multiple processes want to access the same file, they get a handle to the same already started process, so only the first one would have waited for startup).

In terms of ongoing resource consumption, I can easily have a bunch of separate servers running for different files (it depends on your workload how much work will happen behind the scenes.) I'm not sure how much extra work postgres needs to do vs sqlite beyond what is kind of inherent for a workload (eg index creation, saving a bunch of data etc).

ngrilly 2 years ago |

If I understand correctly, the SQLite library would be embedded in a sidecar processes that would run on the same machine as the app main process, instead of having the SQLite library embedded directly in the app main process? But then, what's the point of using SQLite? What makes SQLite reads and writes so fast is that it is running embedded in the app process. If you don't need that, and you want a client-server architecture, then you can simply run PostgreSQL or MySQL on the same machine, also as a sidecar process (or as a subprocess of the main process), and communicate using UNIX domain socket.

cheptsov 2 years ago |

Certainly interested in having more solutions helping use SQLite. At dstack.ai, we use SQLite and love it.

- For our hosted version, we use Litestream; we lack a UI for accessing data.

aspyct 2 years ago |

SQLite reads and writes are already very fast, in particular because it's running in process.

The minute you put it behind a socket, you lose that benefit. Also you would have to implement an efficient wire protocol.

I just don't see the added value here.

cosmicproc 2 years ago |

Cloudflare D1 maybe? https://developers.cloudflare.com/d1/

theage 2 years ago |

for dev there is already pg-lite server (postgres as nodejs/wasm) you can spin up, it's self-contained single-folder db to disk, 100MB RAM thingy. Highly experimental. Not sure if supabase codebase is setup for debugging the way you suggest.

https://github.com/kamilogorek/pglite-server

foxandmouse 2 years ago |

Pocketbase sounds like what you're describing.

ruslan_talpa 2 years ago |

Exactly what you describe but not free [1]

1. https://subzero.cloud/

endisneigh 2 years ago |

You can do all of this with Postgres.

zem 2 years ago | |

except for the single-file database

syndicatedjelly 2 years ago |

All those features sound simple to do by hand, so if it doesn’t exist already, you should make it.

marcobambini 2 years ago |

That's precisely what we are building at https://sqlitecloud.io

cchance 2 years ago |

So… pocketbase.