[petcat]

> the Postgres container you spun up out of habit was never needed

These posts need to stop comparing their contrived use-cases for SQLite to Postgres. Sure, SQLite is all you need if it really is all you need. But Postgres does so much more than just act as a data dump with an SQL engine on top.

Dr. Hipp himself even said that SQLite does not, and will never, compete with the likes of Postgres. It competes with fopen.

[chii]

> SQLite is all you need if it really is all you need.

most use cases are really capable of being satisfied by sqlite, but the "architect" imagines they need more (or is preparing for the potential).

[petcat]

> the "architect" imagines they need more (or is preparing for the potential)

I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back.

Hipp said that it will never be added because it will bloat the size of the embedded object. Because that is what SQLite was designed for: single-user embedded databases. Like the address book on your phone.

[Xenoamorphous]

> I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back.

To be honest if you're using JSON at any point in your stack you have the same issue.

[masklinn]

In fairness, sqlite is perfectly happy with Julian dates or Unix timestamps (that’s the affinity of a column typed “datetime” in non-strict mode) and timestamp(tz) are nothing to write home about except in complaint.

[petcat]

so now we've traded a text column for an int column that still can't validate that the number is actually externally consistent with the real world.

[echoangle]

Is there an invalid unix timestamp? What is there to validate?

[petcat]

Well for one we should probably validate that the number is smaller than the total life of the physical universe.

SQLite will gladly store a u64::MAX as a "unix timestamp" despite it being about 300x larger than the number of seconds that the universe and everything in it has existed.

Try reading that back in any application date/time code and your app probably crashes immediately.

[f3b5]

I was such a big sqlite fan that I used it for my data-intensive startup. Once I actually started scaling it up I ran into crippling file system race conditions. This was because we were hosted on a distributed file system in the cloud which I learned is very very bad for sqlite.

So I had to migrate the production db under live load from sqlite to mysql which was a quite ...intense week. I still like sqlite but I'd be wary of using it again for a usecase like mine.

[pdimitar]

SQLite creators explicitly warn against using SQLite on a networked file system btw.

[inigyou]

If you could conceivably use multiple processes at once to access the database, and not just as an edge case, you need something more than sqlite.

Sqlite does support multi-process access correctly, but the performance is abysmal as it locks the entire file for any write transaction. Client/server databases have much smarter concurrency.

[masklinn]

It’s perfectly fine if your write throughput is low, especially in wal mode. Multiprocess does not actually change much if anything, even in multithreaded mode you want every thread to have its own connection and to have a good handle on who is writing when.

[inigyou]

Regardless, it's a sign sqlite is not right for you. You should start with postgres if you aren't sure your application will always be good with sqlite. It'll save you a migration, and doesn't really cost anything to start your project with postgres instead of sqlite.

[mahcha]

There is an interesting pattern SyncLite (https://github.com/syncliteio/SyncLite) attempts to solve to bring the best of both SQLite and Postgres with one or more SQLite databases directly serving the application while SyncLite replicating/consolidating data from all those SQLite databases into a centralized PostgreSQL database..

[inigyou]

It sounds like a solution trying to sell itself. While you probably can do something like that and it might even be useful for some applications, I can't imagine it's a very good generic solution - you probably want to do it yourself if you want to do it.

[haspok]

> It competes with fopen.

No, it actually competes with Excel.

At least in the finance world, there are still a million small processes driven by an Excel spreadsheet put together in an afternoon by an intern 20 years ago. If it is a really business critical process, then the input is usually a csv file, read by an Excel macro.

Because Excel is user friendly, and "is good enough". Mostly...

[TZubiri]

and for that matter, fopen is all you need, you do not need sqlite

[teaearlgraycold]

For example you cannot have concurrent access. As soon as you need a worker process and a web process SQLite is out. Or if you are trying to use it as a vector db all of those vector searches will block a node event loop.

[Cthulhu_]

Article mentions WAL and how this sentiment is about 16 years out of date. But also, you can duplicate databases for out-of-order processes.

But also also, if you have higher concurrency requirements - e.g. multiple servers, one database - or a more write-heavy use case, sqlite is no longer the right choice.

[callamdelaney]

Yeah it blocks for the 0.001ms it takes for 99.99% of queries to come back. Or you can enable WAL and allow readers to read at the same time as somebody is writing.

[adzm]

WAL mode allows concurrency with reads and writes fwiw

[smarx007]

https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlit...

[chistev]

That site is hard on my eyes