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

71–80 of 274 posts

Re: Consider SQLite

#71

Post by tfehring »

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 think the usual approach would be to SSH into the server and run sqlite3 there. This issue [0] mentions some workarounds for connecting from DBeaver, which I assume would work for other graphical client software. I haven't tried those approaches, they seem pretty hacky and I imagine performance isn't great, but I guess that's to be expected given that SQLite isn't designed for that type of access.

[0] https://github.com/dbeaver/dbeaver/issues/6876

Re: Consider SQLite

#72

Post by samwillis »

dorian-marchal wrote: post #44
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?

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

Re: Consider SQLite

#73

Post by anyfoo »

anderspitman wrote: post #42

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

Most relational databases offer ACID, for one thing.

And also a query language, which is easy to write and modify queries for, presenting a great benefit over writing large amounts of boilerplate code for looping over records and accessing the right indices every time (keep abstracting that and you'll end up with your own relational database system).

I often import data into SQLite just to work with it, without necessarily even an application in between. Depending on the nature of the data it's either that or Matlab.

Re: Consider SQLite

#74

Post by alin23 »

I'm exactly at a point where I'm considering SQLite for its single file db advantage, but I'm struggling to find solutions for my use case.

I need to import some 30k JSONs of external monitor data from Lunar (https://lunar.fyi) into a normalized form so that everyone can query it.

I'd love to get this into a single SQLite file that can be served and cached through CDN and local browser cache.

But is there something akin to Metabase that could be used to query the db file after it was downloaded?

I know I could have a Metabase server that could query the SQLite DB on my server, but I'd like the db and the queries to run locally for faster iteration and less load on my server.

Besides, I'm reluctant to run a public Metabase instance given the log4j vulnerabilities that keep coming.

Re: Consider SQLite

#75

Post by bob1029 »

We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it.

We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performance is hardly a concern when you have reasonable hardware (NVMe/SSD) and utilize appropriate configuration (PRAGMA journal_mode=WAL).

In our testing, our usage of SQLite vastly outperformed an identical schema on top of SQL Server. It is my understanding that something about not having to take a network hop and being able to directly invoke the database methods makes a huge difference. Are you able to execute queries and reliably receive results within microseconds with your current database setup?

Sure, there is no way we are going to be able to distribute/cluster our product by way of our database provider alone, but this is a constraint we decided was worth it, especially considering all of the other reduction in complexity you get with single machine business systems. I am aware of things like DQLite/RQLite/et.al., but we simply don't have a business case that demands that level of resilience (and complexity) yet.

Some other tricks we employ - We do not use 1 gigantic SQLite database for the entire product. It's more like a collection of microservices that live inside 1 executable with each owning an independent SQLite database copy. So, we would have databases like Users.db, UserSessions.db, Settings.db, etc. We don't have any use cases that would require us to write some complex reporting query across multiple databases.

Re: Consider SQLite

#76

Post by WesolyKubeczek »

bonyt wrote: post #46

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

Consider, though, that in the days past (when your server would be probably on an equivalent of dual-socket Pentium 166-MMX), most clients would be coming from slow links like 33.4-56.1kbps dialup, and it wouldn't be a problem to serve them at all. Links were slow, users were patient, timeouts were high, webpages were sort of slim. Although if you ask me, they always have been heavy, just within the constraints of their time.

Then, of course, there was ISDN and xDSL, which would give you true to god whopping 128 kbits/s for a while. 64 kpbs if you were cheap. It took a while to get to affordable multiples of Mbits per second.

Now that there's at least 10 Mbps uplink from each residential subscriber, doesn't take long to DoS even a beefy server.

And I'd say that server-side, things improved vastly with advent of FastCGI and its equivalents. Back in that heyday of your P166-MMX server, it was CGI with Perl, spawning a process for each incoming request, or "blazing-fast" Apache's server-side includes, or other things like that. Maybe mod_perl with its caveats on memory sharing.

Anyway, you're right in that whenever you show them a wider pipe, they will find more stuff to congest it with.

Re: Consider SQLite

#77

Post by dkjaudyeqooe »

alin23 wrote: post #74

I'm exactly at a point where I'm considering SQLite for its single file db advantage, but I'm struggling to find solutions for my use case. I need to import some 30k JSONs of external monitor data from Lunar ( https://lunar.fyi ) into a normalized form so that everyone can query it. I'd love to get this into a single SQLite file that can be served and cached through CDN and local browser cache. But is there something…

You could do incremental updates of the local databases using the session extension:

https://sqlite.org/sessionintro.html

Re: Consider SQLite

#78

Post by paulryanrogers »

dorian-marchal wrote: post #53
paulryanrogers wrote: post #38

Earlier quoted context omitted.

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.

Re: Consider SQLite

#79

Post by gunshowmo »

bob1029 wrote: post #75

We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…

This is really interesting. Would you mind sharing a bit about how you keep the data that is shared across services in sync? Or is there a hard separation of concerns so that services would only commuincate with the service that owns that data to obtain it?

Re: Consider SQLite

#80

Post by js4ever »

mushufasa wrote: post #14
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…

Note -- the single process write at any one time is a killer for most web apps, where for example within SaaS you have many users doing things at the same time.

It's not really an issue if you have 1 db per customer
Post reply on HN