3 ms·

I have a couple of questions... - So LiteFS turns sqlite into a client server database. But SQLite being embedded is what gives it its low latency in the first

I have a couple of questions... - 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"?

ergonaught 4y ago
The Litestream and LiteFS sections near the beginning of the article cover your first question. And I guess the second one is covered by the "generator" entry, but: https://www.prisma.io/docs/concepts/components/prisma-client https://www.prisma.io/docs/concepts/components/prisma-client
- sureglymop 4y ago
  Do you like Prisma? 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.
  - davnicwil 4y ago
    I really like Prisma, and talk about it to anyone who'll listen, but it is still very new and as such I think doesn't have a lot of features figured out yet and some really surprising things do come up. 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-1368247621 https://github.com/prisma/prisma/issues/5149#issuecomment-13...
- LAC-Tech 4y ago
  The Litestream and LiteFS sections near the beginning of the article cover your first question. I don't feel it did.
chillfox 4y ago
I haven't tried LiteFS yet, but as far as I understand, you still get the embedded performance for reads, and on the primary node you get that for writes as well. 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.
- paulryanrogers 4y ago
  Postgres has MERGE and INSERT ON CONFLICT. Pg also has JSONB type which can store a variety of types within it. How is SQLite's replace and typing is superior to these?
  - chillfox 4y ago
    It all depends upon what you are trying to do. 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).
    - paulryanrogers 4y ago
      If you make a column JSONB you can store strings, numbers, arrays, objects, booleans. They don't have to be nested in an object. Though I suppose if you don't want to decode strings that's an extra step. 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.
BFLpL0QNek 4y ago
I think the usecase is the new generation of platform as a service (PaaS) popping up. Cloudflare workers/pages, Netifly, Vercel ect. 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.
- benbjohnson 4y ago
  > I’m fairly interested in LiteFS as a distributed cache. 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.
- fmajid 4y ago
  The real tragedy is that Microsoft and Mozilla conspired to kill WebSQL in the window of opportunity before they both became completely irrelevant in the browser space, and we got the misbegotten monstrosity that is IndexedDB instead of a full-featured SQLite in the browser.
benbjohnson 4y ago
> 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? 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.

ergonaught 4y ago

The Litestream and LiteFS sections near the beginning of the article cover your first question. And I guess the second one is covered by the "generator" entry, but: https://www.prisma.io/docs/concepts/components/prisma-client https://www.prisma.io/docs/concepts/components/prisma-client

sureglymop 4y ago
Do you like Prisma? 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.
- davnicwil 4y ago
  I really like Prisma, and talk about it to anyone who'll listen, but it is still very new and as such I think doesn't have a lot of features figured out yet and some really surprising things do come up. 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-1368247621 https://github.com/prisma/prisma/issues/5149#issuecomment-13...
LAC-Tech 4y ago
The Litestream and LiteFS sections near the beginning of the article cover your first question. I don't feel it did.

chillfox 4y ago

I haven't tried LiteFS yet, but as far as I understand, you still get the embedded performance for reads, and on the primary node you get that for writes as well. 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.

paulryanrogers 4y ago
Postgres has MERGE and INSERT ON CONFLICT. Pg also has JSONB type which can store a variety of types within it. How is SQLite's replace and typing is superior to these?
- chillfox 4y ago
  It all depends upon what you are trying to do. 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).
  - paulryanrogers 4y ago
    If you make a column JSONB you can store strings, numbers, arrays, objects, booleans. They don't have to be nested in an object. Though I suppose if you don't want to decode strings that's an extra step. 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.

BFLpL0QNek 4y ago

I think the usecase is the new generation of platform as a service (PaaS) popping up. Cloudflare workers/pages, Netifly, Vercel ect. 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.

benbjohnson 4y ago
> I’m fairly interested in LiteFS as a distributed cache. 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.
fmajid 4y ago
The real tragedy is that Microsoft and Mozilla conspired to kill WebSQL in the window of opportunity before they both became completely irrelevant in the browser space, and we got the misbegotten monstrosity that is IndexedDB instead of a full-featured SQLite in the browser.

benbjohnson 4y ago

> 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? 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.