3 ms·

I thought the whole point of sqlite is that you don't have to deal with the cruft and overhead of having to send queries over a network connection. Why tack tha

by ginko 3y ago

I thought the whole point of sqlite is that you don't have to deal with the cruft and overhead of having to send queries over a network connection. Why tack that back on?

zipping1549 3y ago
To me whole db being a single file is something.
_heimdall 3y ago
That's a great question. This pattern is actually getting popular today with services like Turso and Cloudflare D1 (technically only SQLite compliant). There are a few reasons I can see reaching for SQLite behind an API rather than something like Postgres. Portability can be a big benefit and I would expect you don't need to deal with connection pooling. If I had a service running on a single box and didn't mind setting up my own db backups, I might reach for SQLite just for the ease of standing up new environments for dev and automated testing.
- johnny22 3y ago
  I've found postgres via docker to be fine for dev and testing, but there are various epheremal postgres scripts, plus this could be promising for node stuff https://github.com/electric-sql/pglite https://github.com/electric-sql/pglite if it works out. I'd imagine if this does work out we'll see the same kind of builds for other runtimes as well (like python)
- deleted 3y ago
  [deleted]
tracker1 3y ago
Accessing the database from multiple applications or systems. SQLite is great for sharing application data as well. If you have, for example data for a specific event,. It can make sense to use a separate database/file. Being able to simply copy as a backup/archive is big here. You might still want your application or services separate. Similarly take a look at Turso or AstroDB for more options. Turso created libSQL as a fork with libSQL server.
brigadier132 3y ago
Frankly, you thought wrong. The whole point of sqlite was to build a database for naval vessels. Aside from that, given it's open source, the "point" of it doesn't really matter anymore.
- SOLAR_FIELDS 3y ago
  Huh, this is a little piece of history I was not aware of. Neat and somewhat helps explain the design philosophy of SQLite knowing that
  - whartung 3y ago
    Semi-related, there’s an anecdote that the InterBase database was selected for use within the M1 Abraham’s tank because it was particularly robust in its implementation. Apparently an issue with the M1 is that because of the energy produced when the main gun is fired, internal systems may spontaneously reset. So they had to design around that phenomenon through things like robustness and rapid system restart times.
  - tda 3y ago
    If you want to know more of the origins of SQLite I can highly recommend this episode of the Corecursive podcast https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/
- SQLite 3y ago
  That is inaccurate. Ginko's statement is closer to truth. I was inspired to write SQLite while working with Informix on DDG-79 and I saw how useful an embedded database would be in some situations, compared to a client/server solution. So I went off and wrote SQLite on my own, while the development contract was on hiatus. There was never a request for SQLite or anything like it coming from the the navy (or more precisely, Bath Iron Works) as they were both very happy with Informix on the ship and Oracle on land and had zero desire for anything new or different. The development team I worked on ended up using SQLite some for prototyping and testing on that project, but it was never deployed to the ship, as far as I know. So yes, the whole point of SQLite was to build a database that operated as a library linked into the application, rather than as a separate server, as ginko postulates. Design issues on a single system within DDG-79 (Automated Common Diagrams) were the inspiration for that idea, but to say that SQLite was designed for DDG-79 is not true. There was never a request for SQLite coming from the navy or the ship designers. Indeed, there is was a lot of pushback against SQLite. SQLite was just a crazy idea coming from a rogue developer who happened to be working on one of the many on-board systems at that time.
galaxyLogic 3y ago
>I thought the whole point of SqLite is that you don't have to deal with the cruft and overhead of having to send queries over a network connection. Why tack that back on? An interesting application could be a web-application whose ONLY server is the SqLite -server. The state of the application would be stored into the SqLite database, not into "local storage" etc. Using a relational database is a great improvement in flexibility over using the key-value store of Local Storage. Could this work? I guess we would need to load one web-page into the browser first to load some HTML and scripts and then that page could use fetch() to get everything else from the SqLite -server.
- chongli 3y ago
  What’s the security model for this? Seems like you’d end up with users able to run arbitrary queries against the database.
  - galaxyLogic 3y ago
    Good Question. The SqLite REST-server should probably provide ways of allowing only selected operations. There's no reason why EVERYTHING should be possible over the network. One possibility could be that each user would have their own copy of the database, let them break it if they want to :-)
- johnny22 3y ago
  There's also the wasm build of sqlite that you could just run directly if it's really necessary. Of course there might be performance differences
boffinAudio 3y ago
I regularly use an embedded sqlite database in my projects, and the existence of tools such as soul and pocketbase means I can easily make those databases available to other members of my team without having to write a custom front-end or component of my application to deal with their unwillingness to install an sqlite GUI on systems they may not necessarily be able to do so, in the first place.

by ginko 3y ago

I thought the whole point of sqlite is that you don't have to deal with the cruft and overhead of having to send queries over a network connection. Why tack that back on?

zipping1549 3y ago

To me whole db being a single file is something.

_heimdall 3y ago

That's a great question. This pattern is actually getting popular today with services like Turso and Cloudflare D1 (technically only SQLite compliant). There are a few reasons I can see reaching for SQLite behind an API rather than something like Postgres. Portability can be a big benefit and I would expect you don't need to deal with connection pooling. If I had a service running on a single box and didn't mind setting up my own db backups, I might reach for SQLite just for the ease of standing up new environments for dev and automated testing.

johnny22 3y ago
I've found postgres via docker to be fine for dev and testing, but there are various epheremal postgres scripts, plus this could be promising for node stuff https://github.com/electric-sql/pglite https://github.com/electric-sql/pglite if it works out. I'd imagine if this does work out we'll see the same kind of builds for other runtimes as well (like python)
deleted 3y ago
[deleted]

tracker1 3y ago

Accessing the database from multiple applications or systems. SQLite is great for sharing application data as well. If you have, for example data for a specific event,. It can make sense to use a separate database/file. Being able to simply copy as a backup/archive is big here. You might still want your application or services separate. Similarly take a look at Turso or AstroDB for more options. Turso created libSQL as a fork with libSQL server.

brigadier132 3y ago

Frankly, you thought wrong. The whole point of sqlite was to build a database for naval vessels. Aside from that, given it's open source, the "point" of it doesn't really matter anymore.

SOLAR_FIELDS 3y ago
Huh, this is a little piece of history I was not aware of. Neat and somewhat helps explain the design philosophy of SQLite knowing that
- whartung 3y ago
  Semi-related, there’s an anecdote that the InterBase database was selected for use within the M1 Abraham’s tank because it was particularly robust in its implementation. Apparently an issue with the M1 is that because of the energy produced when the main gun is fired, internal systems may spontaneously reset. So they had to design around that phenomenon through things like robustness and rapid system restart times.
- tda 3y ago
  If you want to know more of the origins of SQLite I can highly recommend this episode of the Corecursive podcast https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/
SQLite 3y ago
That is inaccurate. Ginko's statement is closer to truth. I was inspired to write SQLite while working with Informix on DDG-79 and I saw how useful an embedded database would be in some situations, compared to a client/server solution. So I went off and wrote SQLite on my own, while the development contract was on hiatus. There was never a request for SQLite or anything like it coming from the the navy (or more precisely, Bath Iron Works) as they were both very happy with Informix on the ship and Oracle on land and had zero desire for anything new or different. The development team I worked on ended up using SQLite some for prototyping and testing on that project, but it was never deployed to the ship, as far as I know. So yes, the whole point of SQLite was to build a database that operated as a library linked into the application, rather than as a separate server, as ginko postulates. Design issues on a single system within DDG-79 (Automated Common Diagrams) were the inspiration for that idea, but to say that SQLite was designed for DDG-79 is not true. There was never a request for SQLite coming from the navy or the ship designers. Indeed, there is was a lot of pushback against SQLite. SQLite was just a crazy idea coming from a rogue developer who happened to be working on one of the many on-board systems at that time.

galaxyLogic 3y ago

>I thought the whole point of SqLite is that you don't have to deal with the cruft and overhead of having to send queries over a network connection. Why tack that back on? An interesting application could be a web-application whose ONLY server is the SqLite -server. The state of the application would be stored into the SqLite database, not into "local storage" etc. Using a relational database is a great improvement in flexibility over using the key-value store of Local Storage. Could this work? I guess we would need to load one web-page into the browser first to load some HTML and scripts and then that page could use fetch() to get everything else from the SqLite -server.

chongli 3y ago
What’s the security model for this? Seems like you’d end up with users able to run arbitrary queries against the database.
- galaxyLogic 3y ago
  Good Question. The SqLite REST-server should probably provide ways of allowing only selected operations. There's no reason why EVERYTHING should be possible over the network. One possibility could be that each user would have their own copy of the database, let them break it if they want to :-)
johnny22 3y ago
There's also the wasm build of sqlite that you could just run directly if it's really necessary. Of course there might be performance differences

boffinAudio 3y ago

I regularly use an embedded sqlite database in my projects, and the existence of tools such as soul and pocketbase means I can easily make those databases available to other members of my team without having to write a custom front-end or component of my application to deal with their unwillingness to install an sqlite GUI on systems they may not necessarily be able to do so, in the first place.