SQLite-based databases on the Postgres protocol? Yes we can | Hacker News Reader

SQLite-based databases on the Postgres protocol? Yes we can

blog.turso.tech

202 pointsthunderbong42 comments

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?

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.

I liked the idea of using EFS and Sqlite with lambdas ( 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). 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.

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.

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.

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

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

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?

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.

>why wouldn't you just use postgres

Because postgres is not single file?

Why do you want something that writes to a single file?

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.

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?

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.

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.

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

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.

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

So while working on Marmot (https://github.com/maxpert/marmot) I considered something similar, and the thing that stood obvious with my benchmarks was readers can go really fast if I let them do things in process vs over some protocol (be HTTP or Postgres). I had the same problem with rqlite and dqlite, so I made a very conscious design choice to build Marmot into a side-car and not a layer on top. This idea is so tempting and goes against ethos of SQLite IMO.

Damn my dyslexia I read marmot and thought you meant this unrelated project https://github.com/synopse/mORMot

I ran into something similar recently. I'm writing a new backend app. I was thinking of maybe using SQLite for dev and Postgres for staging & prod (bad idea, I know..)

I found that there are just so many differences that supporting both leads to having multiple codepaths. Some examples:

- only postgres supports `with conn.cursor as cur`, need `cur.close()` or commit or similar with SQLite

- Escaping things for columns and table names: postgres has psycopg2.sql.SQL.format() that only works with postgres connections.

The things you have described as differences between the DBs are differences in Python libraries.

The Python SQL ecosystem is IMO pretty bad, and the root cause seems to be that DB-API (PEP 249) is really quite awful.

As my least favorite example, DB-API has two transaction modes, both of which are unnecessarily error prone: autocommit on, and autocommit off. Autocommit on makes transactions useless (intentionally), but it still allows commit() and rollback(), so one can write code that thinks transactions are enabled, have it appear to work, and end up with all manner of corruption.

Autocommit off has a bad interface. The begin() function is entirely missing, so you can’t pass it arguments. Worse, since there is no explicit start of a transaction, the library can’t catch misuse of a transaction.

As a result, every SQL library for Python of any reasonable quality implements its own special API, and nothing is particularly interoperable.

This is all sort of fine if the database is only accessed through a higher level library like SQLAlchemy. But sometimes a developer just wants to execute raw SQL against a database, and the situation in Python is not very nice.

Doesn't SQL alchemy make it pretty easy to use raw SQL?

Yes, but how many people use SQLAlchemy just as a database API?

I think the real problem is that DB-API exists and is bad — its existence comes with the automatic imprimatur of being an official standard and this seeming desirable to use.

devcontainers (e.g. in VSCode) make local development with Postgres almost as easy as SQLite.

And if you're on a Mac, Postgres.app makes it even easier: https://postgresapp.com/

(PostgreSQL experts: Is there a magic way to sync schema changes and data from a local instance to a production server?)

Not an expert, but as both schema changes and data changes are queries you can probably do Streaming Replication (1)

1: https://wiki.postgresql.org/wiki/Streaming_Replication

True, but they do put a noticeable strain on my battery life of my laptop (m1), making it go from day long to hours, so I stopped doing that. That’s with only arm64 containers; with x64 ones, it’s even worse of course.

I usually just run a postgres container in development. A small bash script or even docker compose works fine.

SQLite is a Library. Just start a new sqld project that imports SQLite and you're done. It's like Django making a fork of SQLite so they can include SQLite in Django?

They're just adding more complexity to a well established project. NIH syndrome to me.

The article explains they needed to fork SQLite to be able to intercept the WAL.

Does Django need to intercept the WAL? No.

NIH syndrome.

Someone might find this useful, so sharing here, since it's relevant.

I wrote a small PHP library that gives you a key-value storage interface to SQlite files: https://github.com/aaviator42/StorX

I've been dogfooding for a while by using it in my side projects, and it holds up very well (mostly thanks SQLite's robustness!).

And there's a basic API too, to use it over a network: https://github.com/aaviator42/StorX-API

How is this different from using an ORM that supports both dialects using middleware? I mean I know it's clearly different, I'm just saying I'd rather use an ORM that supports both dialects. Seems like less overhead and same basic functionality.

I don't think this should turn in to an ORM or not debate, but there are plenty of reasons, especially for the crowd that would do anything to avoid ORMs. Just try to take a peek into the multitude of "ORMs are bad" articles / discussions.

For instance - I would love to be able to use https://github.com/porsager/postgres with sqlite.

What are the supported isolation levels? :)

Can I use this for integration testing in ci/cd without a real postgres instance?

If I read it correctly: maybe. The database underneath is still SQLite and has SQLite data types. If your db schema, app code and the db library you use allow you to switch on the fly, then yes. But those are big ifs.

Doesn't DuckDB do this.

has anyone tried connecting turso from psql or tableplus? does it work or does it require a custom deployment?

Linked article has out of date code.

Reply on news.ycombinator.com