13 ms·

SQLite-based databases on the Postgres protocol

259 points by matesz 4y ago

chasil 4y ago
This is quite an accomplishment. It is unfortunate that it could not be done in the main project.
cutler 4y ago
Excuse my ignorance but isn't the whole point of SQLite that it's embedded, not clint-server?
- Kinrany 4y ago
  Ideally you want both an embedded database, a library for manipulating requests understood by the database, and a few libraries for exposing the same interface over different networks.
  - deleted 4y ago
    [deleted]
- bob1029 4y ago
  For us, a major point of SQLite is that we can execute queries in ~100uS when deployed on top of NVMe storage. Everything being in the same process is 50% of the value proposition for our product. Once you involve a network and another computer, it gets a lot slower unless you are doing RDMA-style shenanigans with very expensive hardware. The other 50% of the value proposition is simplified operations in a B2B setting.
  - MuffinFlavored 4y ago
    > For us, a major point of SQLite is that we can execute queries in ~100uS when deployed on top of NVMe storage. Postgres can't achieve these query times?
    - bob1029 4y ago
      How long does it take to round trip through the network stack of 2 machines in a typical datacenter?
      - MuffinFlavored 4y ago
        I thought we'd be comparing apples-to-apples with Postgres running locally in this case?
        bob1029 4y ago
        Ok - How many microseconds does it take to round trip SELECT 1 to localhost on a typical cloud VM? You are still using the network stack and involving multiple processes.
        Spivak 4y ago
        People really do be forgetting how unbelievably fast doing things in-process is. It’s why people suffer the complexity of threads instead multiple processes with ipc - like the network.
        bob1029 4y ago
        Agreed. In-process is where you get things like instruction-level parallelism. I think our university programs have failed us the most in this regard. ILP should be drilled into every student's brain as the penultimate performance hack. Once you start separating everything into a bunch of different buckets/systems/processes, none of these advantages can be explored anymore. In some cases, ILP can give you a 100x improvement in performance. That's serial throughput. This is the reason many developers consider bit manipulation/hacking to be essentially free.
        linuxdude314 4y ago
        There is no “round trip” and no network stacked involved in querying a local Postgres instance (web app on same VM as db). Attention is better spent designing an appropriate schema than worrying about the network being too slow to run a single query (if it is, you’ve already failed).
Thaxll 4y ago
Basically re-inventing MySQL / PG but worse. Next step, we don't have auth over the network, let's bake in RBAC into SQLite.
- vidarh 4y ago
  From what else they're doing, it appears the point is to be able to use sqlite in serverless setups where MySQL/Postgres would be way too heavy to deploy on a per-customer or per-function basis.
  - matesz 4y ago
    Honestly I don't get the point of edge. Do people really care whether their website loads in 50ms vs 300ms?
    - maxmcd 4y ago
      Sure it's on a bit of a hype wave at the moment, but yes, most internet users are mobile users that don't live in major metropolitan areas with robust internet infrastructure? I think at least having the option to serve some things at the edge can dramatically improve the browsing experience for those users (and many others!).
    - mirzap 4y ago
      Of course they care. It directly impacts on conversion rates. I'm still stunned with simple fact that most people think 100ms doesn't make a difference (for their business). https://www.globaldots.com/resources/blog/how-website-speed-affects-conversion-rates/ https://www.globaldots.com/resources/blog/how-website-speed-...
      - matesz 4y ago
        Can't they just cache let's say these 1000 marketing, sales and customer support pages into cdn then?
        4y ago
Existenceblinks 4y ago
Enlighten me, if it needs a host different network location than the app, what is the advantage over postgres, is it because it's easier to "setup" and "maintain?
- vidarh 4y ago
  Given their webassembly sqlite function support and other changes, the advantage would seem to be that you're most of the way there to being able to provide "serverless" databases. Have a proxy handle auth, spin up an instance if it's not running/shut it down after a while, and add syncing of changes to/from object storage, and you're there.
  - jimperio 4y ago
    Interesting, hadn't thought about it like that but it makes sense. Used SQLite for a desktop app but never thought it would make any sense on the server.
  - Existenceblinks 4y ago
    > syncing of changes to/from object storage So you are suggesting to have both the "serverless" db and the storage? If so, you now have 3 problems.
    - vidarh 4y ago
      I'm describing capabilities that already exist. Reliably streaming sqlite data to object storage is not a new thing and supported by multiple separate implementations at this point.
      - Existenceblinks 4y ago
        The problem of having multiple sources of data is Consistency, Availability, Partition tolerance. It also depends on IO characteristic of apps. Who are readers, who writers, how long stale data is acceptable. Basically all the distribute system problems. In web app, frontend + backend + db sit next to it is enough of problem (e.g. SPA vs MPA state problem)
drej 4y ago
Note that this already exists on top of SQLite proper - authored by Ben Johnson (Litestream, Fly.io etc.) - https://github.com/benbjohnson/postlite https://github.com/benbjohnson/postlite
- maxmcd 4y ago
  I think they are quite different it seems. Postlite expects you to connect over the postgres wire protocal. Sqld is compiled into your application so your application behaves like it's talking to an embedded sqlite, the calls are then made over the network (using one of three available transports) before being returning to your application.
  - MuffinFlavored 4y ago
    Dumb question, with all of this newfangled WASM stuff, why couldn't we also bake the Postgres server (and then client) into the code? I know the WASM runtime would need to expose the low-level ability to do filesystem operations, mmap()ing, network sockets, etc.
    - maxmcd 4y ago
      Then you'd need to run and maintain Postgres, which is much more complicated, not just a single database file. Postgres also can't be embedded (according to some brief googling), so you'd need to run it as a separate process.
    - stuaxo 4y ago
      This would be handy for apps that use Postgres features, that would; nonetheless work in WASM.
maxmcd 4y ago
Wild stuff - Fork of SQLite with new features https://github.com/libsql/libsql https://github.com/libsql/libsql - And you can embed it in your application and it will really talk to your SQLite/libsql database over the network (subject of this blogpost): https://github.com/libsql/sqld https://github.com/libsql/sqld - Oh, and if you're wondering about backup to S3, they have that too: https://github.com/libsql/bottomless https://github.com/libsql/bottomless - Uh, sqld can integrated with this https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite, so now your SQLite is backed by FoundationDB!? -- - Meanwhile Litestream exists https://github.com/benbjohnson/litestream/ https://github.com/benbjohnson/litestream/ - Ben is developing https://github.com/superfly/litefs https://github.com/superfly/litefs at Fly so that you can talk to your SQLite through a FUSE filesystem. (and work has stopped on the streaming replication effort https://github.com/benbjohnson/litestream/issues/8 https://github.com/benbjohnson/litestream/issues/8) - And, of course, SQLite has announced a new backend that hopes to support concurrent writes and streaming replication: https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html What a time for SQLite Presumably all of these things provide a wildly different experience in terms of consistency, availability, latency, complexity, and concurrent use. Would be so nice to read a lengthy blogpost comparing all or some of these with their pros and cons.
- v3ss0n 4y ago
  Cool but NIH syndrome.. I don't see any point here. SQLite was doing one thing and doing it well. The rest is well handled by pg
- maxmcd 4y ago
  Questions for libsql: - Looks like bottomless does automatic restoring of the database? Litestream seems to avoid this, I assume because of concerns about accidentally creating multiple writers on a rolling-deploy (or similar mistake). Any concerns about this possible footgun? - Bottomless is a sqlite extension, not a separate process? Pros and cons compared to Litestream? - Similar questions for sqld. How does sqld handle the lifecycle of deploys and multiple readers/writers talking to the database? Anything a new user should be concerned about?
newaccount2021 4y ago
[dead]
bawolff 4y ago
I don't get why you would want this. It seems like its taking away all the key advantages of the sqlite approach and leaving only the disadvantages.
houqp 4y ago
Very cool and well executed project. Love the sprinkle of Rust in all the other companion projects as well :) The ROAPI(https://github.com/roapi/roapi https://github.com/roapi/roapi) project I built also happened to support a similar feature set, i.e. to expose sqlite through a variety of remote query interfaces including pg wire protocols, rest apis and graphqls.
philipwhiuk 4y ago
"libSQL" is a silly name. It's not a library is it? I wish people would at least vaguely try to be helpful naming products.
- another2another 4y ago
  Well, it can be linked as an external library, probably also statically linked (? never tried), or - how I use it, compiled into your program as a single sqlite.c file.
- glommer 4y ago
  of course it is a library. An embedded database is essentially a library that you add to your application. That you can build stuff around it, doesn't invalidate the fact that the core of it is a library.
- Spivak 4y ago
  It seems like it’s a library you’re meant to embed in an application and that sqld is just one application built on libSQL. It does seem like it makes more sense to call it something that alludes that it’s postgres
- zffr 4y ago
  From the SQLite homepage: "SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine." Wouldn't a fork also be a library?
ronanyeah 4y ago
I've been using render.com to host Rust+sqlite stacks recently, and I'd like to leverage this. Should my Rust server be running sqld, and I would add a passthrough endpoint through its existing http api to the sqlite? Or alternatively, should I be using sqld locally to access my sqlite instance through SSH or something?
_448 4y ago
A noob question, how does one specify unique constraint on a column in SQlite?

259 points by matesz 4y ago

chasil 4y ago

This is quite an accomplishment. It is unfortunate that it could not be done in the main project.

cutler 4y ago

Excuse my ignorance but isn't the whole point of SQLite that it's embedded, not clint-server?

Kinrany 4y ago
Ideally you want both an embedded database, a library for manipulating requests understood by the database, and a few libraries for exposing the same interface over different networks.
- deleted 4y ago
  [deleted]
bob1029 4y ago
For us, a major point of SQLite is that we can execute queries in ~100uS when deployed on top of NVMe storage. Everything being in the same process is 50% of the value proposition for our product. Once you involve a network and another computer, it gets a lot slower unless you are doing RDMA-style shenanigans with very expensive hardware. The other 50% of the value proposition is simplified operations in a B2B setting.
- MuffinFlavored 4y ago
  > For us, a major point of SQLite is that we can execute queries in ~100uS when deployed on top of NVMe storage. Postgres can't achieve these query times?
  - bob1029 4y ago
    How long does it take to round trip through the network stack of 2 machines in a typical datacenter?
    - MuffinFlavored 4y ago
      I thought we'd be comparing apples-to-apples with Postgres running locally in this case?
      - bob1029 4y ago
        Ok - How many microseconds does it take to round trip SELECT 1 to localhost on a typical cloud VM? You are still using the network stack and involving multiple processes.
        Spivak 4y ago
        People really do be forgetting how unbelievably fast doing things in-process is. It’s why people suffer the complexity of threads instead multiple processes with ipc - like the network.
        bob1029 4y ago
        Agreed. In-process is where you get things like instruction-level parallelism. I think our university programs have failed us the most in this regard. ILP should be drilled into every student's brain as the penultimate performance hack. Once you start separating everything into a bunch of different buckets/systems/processes, none of these advantages can be explored anymore. In some cases, ILP can give you a 100x improvement in performance. That's serial throughput. This is the reason many developers consider bit manipulation/hacking to be essentially free.
        linuxdude314 4y ago
        There is no “round trip” and no network stacked involved in querying a local Postgres instance (web app on same VM as db). Attention is better spent designing an appropriate schema than worrying about the network being too slow to run a single query (if it is, you’ve already failed).

Thaxll 4y ago

Basically re-inventing MySQL / PG but worse. Next step, we don't have auth over the network, let's bake in RBAC into SQLite.

vidarh 4y ago
From what else they're doing, it appears the point is to be able to use sqlite in serverless setups where MySQL/Postgres would be way too heavy to deploy on a per-customer or per-function basis.
- matesz 4y ago
  Honestly I don't get the point of edge. Do people really care whether their website loads in 50ms vs 300ms?
  - maxmcd 4y ago
    Sure it's on a bit of a hype wave at the moment, but yes, most internet users are mobile users that don't live in major metropolitan areas with robust internet infrastructure? I think at least having the option to serve some things at the edge can dramatically improve the browsing experience for those users (and many others!).
  - mirzap 4y ago
    Of course they care. It directly impacts on conversion rates. I'm still stunned with simple fact that most people think 100ms doesn't make a difference (for their business). https://www.globaldots.com/resources/blog/how-website-speed-affects-conversion-rates/ https://www.globaldots.com/resources/blog/how-website-speed-...
    - matesz 4y ago
      Can't they just cache let's say these 1000 marketing, sales and customer support pages into cdn then?
      - 4y ago

Existenceblinks 4y ago

Enlighten me, if it needs a host different network location than the app, what is the advantage over postgres, is it because it's easier to "setup" and "maintain?

vidarh 4y ago
Given their webassembly sqlite function support and other changes, the advantage would seem to be that you're most of the way there to being able to provide "serverless" databases. Have a proxy handle auth, spin up an instance if it's not running/shut it down after a while, and add syncing of changes to/from object storage, and you're there.
- jimperio 4y ago
  Interesting, hadn't thought about it like that but it makes sense. Used SQLite for a desktop app but never thought it would make any sense on the server.
- Existenceblinks 4y ago
  > syncing of changes to/from object storage So you are suggesting to have both the "serverless" db and the storage? If so, you now have 3 problems.
  - vidarh 4y ago
    I'm describing capabilities that already exist. Reliably streaming sqlite data to object storage is not a new thing and supported by multiple separate implementations at this point.
    - Existenceblinks 4y ago
      The problem of having multiple sources of data is Consistency, Availability, Partition tolerance. It also depends on IO characteristic of apps. Who are readers, who writers, how long stale data is acceptable. Basically all the distribute system problems. In web app, frontend + backend + db sit next to it is enough of problem (e.g. SPA vs MPA state problem)

drej 4y ago

Note that this already exists on top of SQLite proper - authored by Ben Johnson (Litestream, Fly.io etc.) - https://github.com/benbjohnson/postlite https://github.com/benbjohnson/postlite

maxmcd 4y ago
I think they are quite different it seems. Postlite expects you to connect over the postgres wire protocal. Sqld is compiled into your application so your application behaves like it's talking to an embedded sqlite, the calls are then made over the network (using one of three available transports) before being returning to your application.
- MuffinFlavored 4y ago
  Dumb question, with all of this newfangled WASM stuff, why couldn't we also bake the Postgres server (and then client) into the code? I know the WASM runtime would need to expose the low-level ability to do filesystem operations, mmap()ing, network sockets, etc.
  - maxmcd 4y ago
    Then you'd need to run and maintain Postgres, which is much more complicated, not just a single database file. Postgres also can't be embedded (according to some brief googling), so you'd need to run it as a separate process.
  - stuaxo 4y ago
    This would be handy for apps that use Postgres features, that would; nonetheless work in WASM.

maxmcd 4y ago

Wild stuff - Fork of SQLite with new features https://github.com/libsql/libsql https://github.com/libsql/libsql - And you can embed it in your application and it will really talk to your SQLite/libsql database over the network (subject of this blogpost): https://github.com/libsql/sqld https://github.com/libsql/sqld - Oh, and if you're wondering about backup to S3, they have that too: https://github.com/libsql/bottomless https://github.com/libsql/bottomless - Uh, sqld can integrated with this https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite, so now your SQLite is backed by FoundationDB!? -- - Meanwhile Litestream exists https://github.com/benbjohnson/litestream/ https://github.com/benbjohnson/litestream/ - Ben is developing https://github.com/superfly/litefs https://github.com/superfly/litefs at Fly so that you can talk to your SQLite through a FUSE filesystem. (and work has stopped on the streaming replication effort https://github.com/benbjohnson/litestream/issues/8 https://github.com/benbjohnson/litestream/issues/8) - And, of course, SQLite has announced a new backend that hopes to support concurrent writes and streaming replication: https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html What a time for SQLite Presumably all of these things provide a wildly different experience in terms of consistency, availability, latency, complexity, and concurrent use. Would be so nice to read a lengthy blogpost comparing all or some of these with their pros and cons.

v3ss0n 4y ago
Cool but NIH syndrome.. I don't see any point here. SQLite was doing one thing and doing it well. The rest is well handled by pg
maxmcd 4y ago
Questions for libsql: - Looks like bottomless does automatic restoring of the database? Litestream seems to avoid this, I assume because of concerns about accidentally creating multiple writers on a rolling-deploy (or similar mistake). Any concerns about this possible footgun? - Bottomless is a sqlite extension, not a separate process? Pros and cons compared to Litestream? - Similar questions for sqld. How does sqld handle the lifecycle of deploys and multiple readers/writers talking to the database? Anything a new user should be concerned about?

newaccount2021 4y ago

[dead]

bawolff 4y ago

I don't get why you would want this. It seems like its taking away all the key advantages of the sqlite approach and leaving only the disadvantages.

houqp 4y ago

Very cool and well executed project. Love the sprinkle of Rust in all the other companion projects as well :) The ROAPI(https://github.com/roapi/roapi https://github.com/roapi/roapi) project I built also happened to support a similar feature set, i.e. to expose sqlite through a variety of remote query interfaces including pg wire protocols, rest apis and graphqls.

philipwhiuk 4y ago

"libSQL" is a silly name. It's not a library is it? I wish people would at least vaguely try to be helpful naming products.

another2another 4y ago
Well, it can be linked as an external library, probably also statically linked (? never tried), or - how I use it, compiled into your program as a single sqlite.c file.
glommer 4y ago
of course it is a library. An embedded database is essentially a library that you add to your application. That you can build stuff around it, doesn't invalidate the fact that the core of it is a library.
Spivak 4y ago
It seems like it’s a library you’re meant to embed in an application and that sqld is just one application built on libSQL. It does seem like it makes more sense to call it something that alludes that it’s postgres
zffr 4y ago
From the SQLite homepage: "SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine." Wouldn't a fork also be a library?

ronanyeah 4y ago

I've been using render.com to host Rust+sqlite stacks recently, and I'd like to leverage this. Should my Rust server be running sqld, and I would add a passthrough endpoint through its existing http api to the sqlite? Or alternatively, should I be using sqld locally to access my sqlite instance through SSH or something?

_448 4y ago

A noob question, how does one specify unique constraint on a column in SQlite?