Hacker News Reader

Comment by ginko | Hacker News Reader

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?

View on HN

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

What’s the security model for this? Seems like you’d end up with users able to run arbitrary queries against the database.

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 :-)

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

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.

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 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)

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.

To me whole db being a single file is something.

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.

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.

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

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.

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/

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.

Thank you for your work on SQLite and Fossil !! They are amazing.

Any chance of WAL2 being included as a standard journal_mode option in the near future? ..or BEGIN CONCURRENT ? :)

I've always wondered why sqlite came along, but adoption of msql (Matt Dillons' similar database stack) didn't catch on .. did you have any opportunity to review the existing database tools that were available at the time, and if so - what did you find?

I realize this question is a bit archaic so if there's not really any good answers, no worries - but as I was using Matt's msql in the 2000's in the same way that I now use sqlite, its something I often wonder whenever I set up a new sqlite.db ...

This was great, but you should have started the comment with "I'm D. Richard Hipp. You know nothing of my work."

Reply on news.ycombinator.com