Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Consider SQLite

blog.wesleyac.com

Post reply on HN
Print view View original

41–50 of 274 posts

Re: Consider SQLite

#41

Post by tyingq »

pgwhalen wrote: post #23

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.

>it's not easy to inspect or fix data in production the way we would with postgres.

I assume because you're using a remote socket connection from the client?

I haven't tried it in a serious setting yet, but I did play around with dqlite and was impressed. Canonical uses it as the backing data store for lxd. Basically sqlite with raft clustering and the ability for clients to connect remotely via a wire protocol. https://dqlite.io/

Re: Consider SQLite

#42

Post by anderspitman »

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to exist? Why can't everyone be on a federated server with state that fits in memory/JSON? I guess that's more of a philosophical question about big tech. But I think it's interesting that most of our tech stack choices are driven by projects designed to work at a scale most of us will never need, and maybe nobody needs.

As an aside, is there something like SQLite but closer to my use cases? So I guess like the nosql version of SQLite.

[0]: https://boringproxy.io/

[1]: https://github.com/boringproxy/boringproxy/blob/master/datab...

Re: Consider SQLite

#43

Post by koeng »

samwillis wrote: post #27

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 ena…

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

Very useful for pure go applications!

Re: Consider SQLite

#44

Post by dorian-marchal »

samwillis wrote: post #27

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 ena…

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?

Re: Consider SQLite

#45

Post by chasil »

teraflop wrote: post #21
chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

> NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. And the safety of your data depends on the quality of your network filesystem's locking implementation. It's not too difficult to design a locking method that works most of the time, but it's a lot harder to build something that guarantees mutual exclusion over an imperfect network. On a single machine, file l…

Databases on remote filesystems should be limited to SQLITE3_OPEN_READONLY access, agreed.

Re: Consider SQLite

#46

Post by bonyt »

I've always thought it interesting that there was a time when large(ish) websites were hosted using servers that would struggle to outperform a modern smart toaster or wristwatch, and yet modern web applications tend to demand a dramatic distributed architecture. I like the examples in this article showing what a single modern server can do when you're not scaling to Google's level.

As an aside, what about distributed derivatives of sqlite, like rqlite, as a response to the criticism that sqlite requires your database server to also be your web server. Could something like rqlite also provide a way for an sqlite database to grow into a distributed cluster at a later point?

https://github.com/rqlite/rqlite

Re: Consider SQLite

#47

Post by pgwhalen »

brunoluiz wrote: post #34
pgwhalen wrote: post #23

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.

I believe you mean that you can't easily do a "psql ..." or connect using DataGrid and similars, right? Does this mean that devs need to copy the production database file locally to then inspect it? Or are there tools to connect/bridge to a remote sqlite file?

> I believe you mean that you can't easily do a "psql ..." or connect using DataGrid and similars, right?

Yeah.

> Does this mean that devs need to copy the production database file locally to then inspect it? Or are there tools to connect/bridge to a remote sqlite file?

We use "kubectl copy" currently when we want to inspect it, and we haven't actually had to write back to a production file yet. We've explored the "remote" option, but since it's just a file, everything seems to boil back down to "copy locally" then "copy back to remote prod".

It's only a small part of our stack at the moment, so we haven't invested in tooling very much - but I'd be curious if others have solved similar problems.

Re: Consider SQLite

#48

Post by boring_twenties »

chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

None of those matter to me, but I'm being forced to switch from SQLite simply because of its lack of uint64 support.

Re: Consider SQLite

#50

Post by config_yml »

Something1234 wrote: post #7
cheeselip420 wrote: post #6

Earlier quoted context omitted.

What would a managed sqlite even look like? I can't tell if this is a real response or not...

Maybe an NFS mount or something that handles back ups automatically? Scripting to handle an automatic restore of the database? Maybe a heroku that knows about your database file and automatically loads the latest version for you? I kind of feel like GP is a troll comment, as there's no real value add for a managed SQLite.

Or just use litestream, it‘s perfect for this and the closest you can get to managed by replicating to S3 or Cloud Storage or the likes.
Post reply on HN