Hacker News Reader

Comment by throwaway81523 | Hacker News Reader

This is a cute idea but it sounds headed for pain, as most typical client environments will come with bindings for multiple db's including sqlite. So if you want to use pg bindings, that probably means you want to use pg features, which aren't present in sqlite. There might be a use case where you want to use sqlite remotely as a client/server db, but even then, is this such a great approach? Sqlite has its own special features that likely don't fit the pg wire protocol that well.

View on HN

It's literally in the first paragraph...

> Postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier.

Yes, I'm saying if the gui tools expect postgres, they are likely to use postgres features, which expands the amount of work this thing needs. If the tools are written specifically for this proxy, then ok, but why bring postgres into it at all? Write something sqlite-specific that exposes sqlite's features.

> Yes, I'm saying if the gui tools expect postgres, they are likely to use postgres features,

Unlikely. A lot of such tools are mainly for displaying the data that's present; they might have support for displaying e.g. GIS types but they're not going to need to write GIS data into the database, and certainly not to the point where you can't run them at all if that's not possible.

All of these tools will at least run an introspection query to get the available tables and columns to display the data. But these queries will fail because SQLite doesn't have these tables.

So the idea is very nice, but any existing tool for PostgreSQL will not work. You need to either work with the psql terminal application or write a new client.

I believe you are looking for these: https://github.com/benbjohnson/postlite/blob/main/server.go#...

This is explicitly accounted for, as per the README:

> Many Postgres clients also inspect the pg_catalog to determine system information so Postlite mirrors this catalog by using an attached in-memory database with virtual tables.

> Write something sqlite-specific that exposes sqlite's features

This is about using SQLite with existing tools that don't have support for it. It's very common since it's easier to support multiple remote database servers (MySQL, Postgres) vs adding the in-process model for SQLite only.

Also you need to take the context into account - Fly.io has SQLite databases distributed across their nodes (see their post about replacing Consul [1]) and most likely want to be able to manage those remotely. Since SQLite doesn't have a server protocol, tools that have bindings for it only support working with local databases.

[1] https://fly.io/blog/a-foolish-consistency/

I'd imagine the biggest use case will be to simplify unit tests and local dev.

Postlite author here. The primary use case is to administer remote SQL databases using existing GUI database tools like DBeaver. It's just a thin shim to translate between Postgres wire protocol to SQLite transactions so I don't think it would help with unit tests or local dev.

Reply on news.ycombinator.com