SQLite Support?

[karlhorky]

Hi @porsager 👋 hope you're well!

I have an unconventional request - I would like to connect to an SQLite database and perform queries on it, via Postgres.js, eg:

import postgres from 'postgres'

// Connect to SQLite database
const sql = postgres({ database: './database.db' })

// Query SQLite database
const users = sql`SELECT * FROM users`;

This is to take advantage of all DX / tooling benefits of Postgres.js.

I realize that this may not be the goal of the Postgres.js library, nor does the name Postgres.js describe the usage above (maybe SQL.js for a combined library or SQLite.js would be alternative names for this).

Workaround 1

Use sqld to enable PostgreSQL wire protocol to a SQLite database:

WIP demo - not working yet

• https://github.com/karlhorky/postgres-js-sqld
• Workflow: https://github.com/karlhorky/postgres-js-sqld/blob/main/.github/workflows/read-write-sqlite-database-from-postgres-js.yml
• Demo workflow run: https://github.com/karlhorky/postgres-js-sqld/actions/runs/4404519322/jobs/7714229727
• sqld issue 1: "Not a directory" error on GitHub Actions / Homebrew libsql/sqld#288
• sqld issue 2: Portal not found with posgres protocol libsql/sqld#292

Workaround 2

Use db-to-sqlite to export a PostgreSQL database to SQLite database:

• https://github.com/karlhorky/postgres-js-db-to-sqlite
• Workflow: https://github.com/karlhorky/postgres-js-db-to-sqlite/blob/main/.github/workflows/export-postgresql-database.yml
• Demo workflow run: https://github.com/karlhorky/postgres-js-db-to-sqlite/actions/runs/4398959482/jobs/7703172029

Alternatives considered

Use one of the existing SQLite libraries eg. better-sqlite3 (with not as great DX as Postgres.js):

• https://npmtrends.com/better-sqlite3-vs-node-sqlite3-vs-sqlite-vs-sqlite-sync-vs-sqlite3

[JAForbes]

Please let us know how you go with sqld, I'm also very interested in this. @karlhorky

[karlhorky]

@JAForbes will do! I'll keep the Workaround 1 demo section above updated, as they fix the bugs I encounter. I think it's probably pretty close to working already (you can check out the repo + workflows at the links above if you're interested)

[mattbishop]

This library is called postgres, why do you thing it should "support" sqlite?

[karlhorky]

I mentioned it in my description a bit above, but to reiterate: the DX is unparalleled and it would be great to have this also with other database engines. Trying to avoid a good idea being held back by the library name. Some ideas (also posted in my description above): a new adapter library could be created, a new generic library name could be created, a core could be extracted, etc.

[porsager]

@karlhorky definitely not a bad request / idea - i love it!! I've been very close to reaching for sqlite in various scenarios, but simply didn't because I couldn't use it like I use PostgreSQL with Postgres.js...

Unfortunately my time is very limited, so I don't know how much I can participate in exploring it, but I'll follow the conversation eagerly ;)

[daguitosama]

I think they are two different animals, whenever they try to encompass more target techs, to get something like a Swiss Knife, stuff get's complicated, hard to understand and hard to use, wish is the main reason I think we use this lib to do PostgreSQL, simplicity , easy of use, really fast and really close to the metal.

I think would be wiser to fork this one and adapt it to SQLite, with that exact use case in mind, not something aiming to harness two wild beasts.

[JAForbes]

I mean if libsql implements the wire protocol then there should be no extra work to do, I've been watching the repo and they are making great headway, I think all the work required is just to raise issues as they arise and let them look into it.

Also, the postgres wire protocol is becoming a pseudo standard that lots of database engines are adhering to, so I doubt this is the first or the last time this sort of request will come up, whether it's for materialize, or yugabyte, or libsql etc.

[karlhorky]

It's possible that sqld is dropping support for the PostgreSQL wireprotocol (if I am understanding correctly):

• Portal not found with posgres protocol libsql/sqld#292 (comment)

[karlhorky]

Or, an alternative would be this PGLite project which is emerging 👀

• https://twitter.com/ElectricSQL/status/1773027908161843226