Skip to content

SQLite Support? #568

Closed
Closed

Description

@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

Workaround 2

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

Screenshot 2023-03-12 at 20 00 41

Alternatives considered

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

Activity

  1. JAForbes commented on Mar 16, 2023

    @JAForbes
    Contributor

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

  2. karlhorky commented on Mar 16, 2023

    @karlhorky
    ContributorAuthor

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

  3. mattbishop commented on Mar 16, 2023

    @mattbishop

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

  4. karlhorky commented on Mar 16, 2023

    @karlhorky
    ContributorAuthor

    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.

  5. porsager commented on Mar 16, 2023

    @porsager
    Owner

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

  6. daguitosama commented on Jun 11, 2023

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

  7. JAForbes commented on Jun 12, 2023

    @JAForbes
    Contributor

    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.

  8. Repository owner locked and limited conversation to collaborators on Jun 26, 2023
  9. converted this issue into a discussion #620 on Jun 26, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions