- So LiteFS turns sqlite into a client server database. But SQLite being embedded is what gives it its low latency in the first place. So, I kind of have to ask - what's point? Just prefering the way sqlite does SQL over postgres?
- What language is that database model under "Evaluating the existing data"?
SQLite can do things PostgreSQL can't. Like the "insert or replace" is superior to PostgreSQL upsert for maintaining seed data. And the flexible typing is great for a settings table where you can just have key/value columns and store numbers/text/bool/dates/etc... in the value column. Also, if you want to do a graph style query then SQLite is better then PostgreSQL.
The way I see it, both PostgreSQL and SQLite have their strengths and LiteFS expands the areas where SQLite can be used.
How is SQLite's replace and typing is superior to these?
If you want to store lot's of json data then yes, JSONB is the better option.
As for the MERGE and INSERT ON CONFLICT vs INSERT OR REPLACE, they all work differently, they are not the same. The SQLITE option is simpler and better if you just want to ensure a full row is there in a specific state. If you want to preserve certain columns then the PostgreSQL options are better.
Edit: I do wish that PostgreSQL had the SQLite options and that SQLite had the PostgreSQL options, because they are good for different things and it would be nice to have access to all of it regardless of which DB I am working with (I use both in different apps).
I find duck typing in a DB more of a problem than a solution, unless the dataset is so small and variable I don't even need a RDBMS at all.
And I guess the second one is covered by the "generator" entry, but:
https://www.prisma.io/docs/concepts/components/prisma-client
I think it's cool but it sucks that their prisma schema doesn't support constraints and more complex things. It is very basic and for any real world use migrations need to be used.
And I've had issues using it with SQLite. It doesn't support case insensitive filtering when using SQLite (I know, neither does SQLite). But as a result, i have to build a custom query which is pretty much what i wanted to avoid by using Prisma.
One I discovered just the other day [0] was that if you pass an undefined value into a field in a where clause of findFirst, it's treated the same as if you didn't pass anything at all.
If you do this with an id, for example, this can result in just the first record in the table being returned, because it's as though you have no where clause at all. Naturally this can lead to some surprising bugs if undefined values crop up at runtime, even potentially some really bad security bugs (imagine if this happens on the account table for instance).
I don't mean this as a criticism, I think it is just a normal part of being new and exciting software, maturing over time. But it's always a tradeoff to be aware of.
[0] https://github.com/prisma/prisma/issues/5149#issuecomment-13...
I don't feel it did.
They all deploy web apps at the edge, so are distributed by default. A lot of the web apps deployed on these services have small volumes of data, read heavy and low writes. They are also low system resources so an embedded db makes sense?
Running SQLLite locally to the application will give extremely low read latency with most of the apps being read heavy/biased. The sales pitch for deploying at the edge is you can have hundreds of apps running across the globe near your users transparently.
I’m fairly interested in LiteFS as a distributed cache. I’ve seen systems use a message queue listening for updates then updating local caches which is a bunch of custom code/plumbing that can be replaced with LiteFS. Using LiteFS the read replicas are essentially materialised views.
We've been finding a lot of use cases for this at Fly.io. We'll use the static leasing in LiteFS for this as it's simpler for that use case.
LiteFS author here. Some other folks responded but I'll try to clarify here. LiteFS keeps SQLite as an embedded database so it maintains low latency. It injects itself as a passthrough file system so it can track changes with each transaction. On the replica side, it mostly acts like a separate SQLite process would in that it obtains the same locks as SQLite and applies page change sets to the database.
tl;dr from the application perspective, it just looks like a SQLite database. I mean, it is. We just capture change sets and propagate them behind the scenes.