6 ms·

I thought one of the main attractions of SQLite was that it was embedded, so specifically didn't need an HTTP round trip to query the DB. It seems like this pr

by zknill 3y ago

I thought one of the main attractions of SQLite was that it was embedded, so specifically didn't need an HTTP round trip to query the DB. It seems like this project removes that benefit, so what is it solving for or adding instead? What's the upside?

pama 3y ago
They mention replication and optimistic concurrency to allow multiple writers to work in parallel.
- zknill 3y ago
  The optimistic concurrency is a feature of SQLite, not their sqld server. And I think there are other approaches to replication that still retain the embedded aspect; litefs, litestream. I guess I'm thinking, if it looks like postgres, and it speaks postgres (wire protocol) and it has features or replication like postgres, why wouldn't you use postgres?
  - aerio 3y ago
    >why wouldn't you just use postgres Because postgres is not single file?
    - ChoHag 3y ago
      [dead]
    - runeks 3y ago
      Why do you want something that writes to a single file?
      - onetoo 3y ago
        The mental model of "a single DB file" is a easier to grog than a large and seemingly opaque DB server. Most (all?) of the well-known RDBMS are designed to host the data needs of multiple separate applications, which makes configuration look a lot more complicated when all you want is n=1.
        mbreese 3y ago
        But then why do you need to write to the DB remotely? It seems like the inflection point moving from "single-local-application" to "writing-to-remote-db" would be an appropriate point to switch from a single file DB to a larger RDBMS. I can think of a few scenarios where it could make sense, like a shared small DB on a LAN that doesn't need a full fledged "server", but a primary/secondary relationship makes sense. But those are pretty few... and n>1. Obviously someone wrote this because they felt a need for it, but I'm struggling to see the use-case. Maybe it's good for CI/or unit testing?
        pastage 3y ago
        The use case mentioned for http is: you have edge services that need to be able to query a db and http might be the only open way to communicate. Exactly why you run on the edge or use sqlite is actually off topic. I mean I run User Mode Linux in production because it was just easier than the alternative when we first developed it.
        upon_drumhead 3y ago
        I self host a lot of stuff at home. I don't need a large RDBMS. A lot of software is written for Postgres and I don't bother running it as I don't want to manage it. I'm really interested in this if I can start using those apps now, without spinning up postgresql.
        ElectricalUnion 3y ago
        > easier to grog than a large and seemingly opaque DB server. Not really, with this solution now you need to manage a non-replicated, non-high-availability mock of Postgres where every user is 'postgres', dealing with the usual remote RPC call issues and latency. If you need to manage a RDBMS anyways, you probably can manage Postgres; if you can't manage Postgres then it's unlikely that you will be able to manage this solution as well.
      - maitredusoi 3y ago
        [dead]
  - pastage 3y ago
    Obviously because you already run sqlite and need to access it remotely for some reason. You might want to standardize how applications like Datasette works and use a known working library for the postgres wire protocol.
3cats-in-a-coat 3y ago
That remains the main attraction of SQLite, but it's also a small, well-written and highly reusable component for other solutions. LibSQL doesn't compete with SQLite, it doesn't replace it. It augments it, so it can scale & address more scenarios than were convenient before.
rpastuszak 3y ago
I liked the idea of using EFS and Sqlite with lambdas ( https://twitter.com/simonw/status/1361453530226192385?lang=en https://twitter.com/simonw/status/1361453530226192385?lang=e...) but then attempted to get super simple setup for using a Sqlite db file with Serverless on Vercel. The main idea was that I could use Sqlite as a nicer/easier to query JSON with almost no upfront infra for a relatively simple project (https://lemonade.sonnet.io https://lemonade.sonnet.io). It worked quite well! But then I had some issues with accessing fs (it _is_ officially supported for reads). I could read the file from one route, but accessing it from another would result in a file not found error... I pivoted when I managed to get it working... by renaming the db file to db.png and getting its path via `import` in Next. I know there's a simpler way to to that.
- roblh 3y ago
  I dunno if it’s just me, but every time I’ve used NextJs I’ve run into weird pointy edges like that. It looks so polished from the outside but then starts feeling wonky as soon as I start to use it.
  - chrisweekly 3y ago
    You're not alone. There's a lot to really like and appreciate, but there's also a lot of opinionated decisions baked in, and almost inevitable fighting against the framework. The good news is, there are good alternatives. Remix is worth a close look; if you accept up front that a js server runtime is required, it's fantastic. Or, there's Vite, which (with vite-plugin-ssr) covers most Next.js use cases with fewer opinions and less framework magic.
  - rpastuszak 3y ago
    I think it's an overall productivity boost and it scales pretty well (have been using it since 2017). I think this is more of a limitation of Vercel/their Next config/adapter (the name escapes me atm). For most of my small/medium projects now I use SvelteKit. I used to think it was a bit too magical because of the new syntax, but I tend to write more vanilla JS/TS with it than I expected. Productivity- and dev experience wise it's quite impressive. My workflow is prototyping/spikes, many wip commits then -> rewrite/sometimes TDD for critical parts. ( More info here: https://sonnet.io/posts/wip/ https://sonnet.io/posts/wip/ and https://sonnet.io/posts/code-sober-debug-drunk/ https://sonnet.io/posts/code-sober-debug-drunk/ ) For large apps I'd still stick with Next, although the refresh times in dev mode are a bit annoying and I don't share th3o's optimism re Vercel having so much control over React.
Scarblac 3y ago
Another benefit is that the database is just a single file, so it can be used as a format to exchange data, etc.
rjmunro 3y ago
AFAICS, it doesn't "remove" that benefit. It just adds a second way to access your databases. I believe FirebirdSQL has done this for a while - you can either link the whole database library to your code, or with the same API, link a client library that talks to a remote database over the network. It's a great idea because you can link to the embedded version for development and unit testing, but upgrade to a full networked DB server for the live deployment.

by zknill 3y ago

pama 3y ago

They mention replication and optimistic concurrency to allow multiple writers to work in parallel.

zknill 3y ago
The optimistic concurrency is a feature of SQLite, not their sqld server. And I think there are other approaches to replication that still retain the embedded aspect; litefs, litestream. I guess I'm thinking, if it looks like postgres, and it speaks postgres (wire protocol) and it has features or replication like postgres, why wouldn't you use postgres?
- aerio 3y ago
  >why wouldn't you just use postgres Because postgres is not single file?
  - ChoHag 3y ago
    [dead]
  - runeks 3y ago
    Why do you want something that writes to a single file?
    - onetoo 3y ago
      The mental model of "a single DB file" is a easier to grog than a large and seemingly opaque DB server. Most (all?) of the well-known RDBMS are designed to host the data needs of multiple separate applications, which makes configuration look a lot more complicated when all you want is n=1.
      - mbreese 3y ago
        But then why do you need to write to the DB remotely? It seems like the inflection point moving from "single-local-application" to "writing-to-remote-db" would be an appropriate point to switch from a single file DB to a larger RDBMS. I can think of a few scenarios where it could make sense, like a shared small DB on a LAN that doesn't need a full fledged "server", but a primary/secondary relationship makes sense. But those are pretty few... and n>1. Obviously someone wrote this because they felt a need for it, but I'm struggling to see the use-case. Maybe it's good for CI/or unit testing?
        pastage 3y ago
        The use case mentioned for http is: you have edge services that need to be able to query a db and http might be the only open way to communicate. Exactly why you run on the edge or use sqlite is actually off topic. I mean I run User Mode Linux in production because it was just easier than the alternative when we first developed it.
        upon_drumhead 3y ago
        I self host a lot of stuff at home. I don't need a large RDBMS. A lot of software is written for Postgres and I don't bother running it as I don't want to manage it. I'm really interested in this if I can start using those apps now, without spinning up postgresql.
      - ElectricalUnion 3y ago
        > easier to grog than a large and seemingly opaque DB server. Not really, with this solution now you need to manage a non-replicated, non-high-availability mock of Postgres where every user is 'postgres', dealing with the usual remote RPC call issues and latency. If you need to manage a RDBMS anyways, you probably can manage Postgres; if you can't manage Postgres then it's unlikely that you will be able to manage this solution as well.
    - maitredusoi 3y ago
      [dead]
- pastage 3y ago
  Obviously because you already run sqlite and need to access it remotely for some reason. You might want to standardize how applications like Datasette works and use a known working library for the postgres wire protocol.

3cats-in-a-coat 3y ago

That remains the main attraction of SQLite, but it's also a small, well-written and highly reusable component for other solutions. LibSQL doesn't compete with SQLite, it doesn't replace it. It augments it, so it can scale & address more scenarios than were convenient before.

rpastuszak 3y ago

I liked the idea of using EFS and Sqlite with lambdas ( https://twitter.com/simonw/status/1361453530226192385?lang=en https://twitter.com/simonw/status/1361453530226192385?lang=e...) but then attempted to get super simple setup for using a Sqlite db file with Serverless on Vercel. The main idea was that I could use Sqlite as a nicer/easier to query JSON with almost no upfront infra for a relatively simple project (https://lemonade.sonnet.io https://lemonade.sonnet.io). It worked quite well! But then I had some issues with accessing fs (it _is_ officially supported for reads). I could read the file from one route, but accessing it from another would result in a file not found error... I pivoted when I managed to get it working... by renaming the db file to db.png and getting its path via `import` in Next. I know there's a simpler way to to that.

roblh 3y ago
I dunno if it’s just me, but every time I’ve used NextJs I’ve run into weird pointy edges like that. It looks so polished from the outside but then starts feeling wonky as soon as I start to use it.
- chrisweekly 3y ago
  You're not alone. There's a lot to really like and appreciate, but there's also a lot of opinionated decisions baked in, and almost inevitable fighting against the framework. The good news is, there are good alternatives. Remix is worth a close look; if you accept up front that a js server runtime is required, it's fantastic. Or, there's Vite, which (with vite-plugin-ssr) covers most Next.js use cases with fewer opinions and less framework magic.
- rpastuszak 3y ago
  I think it's an overall productivity boost and it scales pretty well (have been using it since 2017). I think this is more of a limitation of Vercel/their Next config/adapter (the name escapes me atm). For most of my small/medium projects now I use SvelteKit. I used to think it was a bit too magical because of the new syntax, but I tend to write more vanilla JS/TS with it than I expected. Productivity- and dev experience wise it's quite impressive. My workflow is prototyping/spikes, many wip commits then -> rewrite/sometimes TDD for critical parts. ( More info here: https://sonnet.io/posts/wip/ https://sonnet.io/posts/wip/ and https://sonnet.io/posts/code-sober-debug-drunk/ https://sonnet.io/posts/code-sober-debug-drunk/ ) For large apps I'd still stick with Next, although the refresh times in dev mode are a bit annoying and I don't share th3o's optimism re Vercel having so much control over React.

Scarblac 3y ago

Another benefit is that the database is just a single file, so it can be used as a format to exchange data, etc.

rjmunro 3y ago

AFAICS, it doesn't "remove" that benefit. It just adds a second way to access your databases. I believe FirebirdSQL has done this for a while - you can either link the whole database library to your code, or with the same API, link a client library that talks to a remote database over the network. It's a great idea because you can link to the embedded version for development and unit testing, but upgrade to a full networked DB server for the live deployment.