Skip to content

proposal: use SQLite instead of in-memory database #881

Closed as not planned
Closed as not planned

Description

@deansheather

Instead of writing two DB implementations, we could have an SQLite implementation auto-generated from the exact same sqlc files. Any queries that don't work in SQLite can be specifically overridden, but I suspect this list will be small.

Pros:

  • We don't have to maintain our own flawed in-memory implementation with maps and mutexes
    • Transaction support
    • Enforced primary keys and constraints
    • Foreign keys
    • Basically full relational stuff

Cons:

  • Doesn't support the full range of Postgres features
    • No advisory locks (but these can be replaced with a Go implementation similar to map[string]sync.Mutex)
    • Some crazy queries with complex joins/aggregations and some complex types might not be supported (we can replace these queries specifically for the SQLite implementation)
  • Cgo is probably required 😢

Cgo is probably a dealbreaker but I decided to post this ticket anyways to get the discussion going

cc @kylecarbs

Activity

  1. kylecarbs commented on Apr 5, 2022

    @kylecarbs
    Member

    Could you explain the flaws you see with the current approach? None of the database features described have caused issues as far as I can tell. Vault does the same pattern, and it appears to have scaled for them.

  2. Emyrk commented on Apr 5, 2022

    @Emyrk
    Member

    Does vault use a relational db? I figured they use a key/val store. I thought it was etcd backed

  3. deansheather commented on Apr 5, 2022

    @deansheather
    CollaboratorAuthor

    Not using a relational database seems jank to me, so I'm suggesting a method that allows us to use a relational database. It is easy to make assumptions in the product especially with regards to foreign key cascade deletes that could result in a invalid DB state i.e. forgetting to delete all associated user sub-tables like api-keys when deleting a user.

  4. deansheather commented on Apr 5, 2022

    @deansheather
    CollaboratorAuthor

    Judging by the postgres schema for the vault postgres db driver it's just a KV so I don't think that's a fair comparison https://www.vaultproject.io/docs/configuration/storage/postgresql

  5. kylecarbs commented on Apr 5, 2022

    @kylecarbs
    Member

    HashiCorp created go-memdb to serve this purpose specifically. I felt it was overkill at the time of creation, and still likely is.

    The in-memory database is primarily for testing and trialing Coder, not for persisting data. I don't imagine foreign key constraints would provide much value for those use-cases.

    It's not perfect by any means, but all solutions will require some level of abstraction from PostgreSQL (unless you can find an in-memory PostgreSQL in Go).

  6. kylecarbs commented on Apr 5, 2022

    @kylecarbs
    Member
  7. Emyrk commented on Apr 6, 2022

    @Emyrk
    Member

    Not saying to use sqllite, but the foreign key relations I'd imagine we'd use as a form of validation checking. Maybe that's me being lazy and letting pg constraints do work.

  8. Emyrk commented on Apr 6, 2022

    @Emyrk
    Member

    I'm finding so many random neat projects: https://github.com/genjidb/genji

  9. kylecarbs commented on Apr 6, 2022

    @kylecarbs
    Member

    That one specifically is pretty cool.

  10. coadler commented on Apr 7, 2022

    @coadler
    Contributor

    I wonder if once sqlc comes out with templating support we could use that to generate the in-memory representations.

  11. kylecarbs commented on Apr 7, 2022

    @kylecarbs
    Member

    That's 🧠

  12. tjcran commented on May 16, 2022

    @tjcran

    Because the in-memory db is meant for --dev use cases and not production, I am marking this as a Post-MVP item.

  13. added this to the Post-MVP milestone on May 16, 2022
  14. coadler commented on May 16, 2022

    @coadler
    Contributor

    I think we decided to not pursue sqlite cc @deansheather

  15. changed the title [-]proposal: use SQLite for in-memory database[/-] [+]proposal: use SQLite instead of in-memory database[/+] on May 23, 2022
  16. ammario commented on May 23, 2022

    @ammario
    Member

    I think we decided to not pursue sqlite cc @deansheather

    Per comment, closed for now. Feel free to re-open if wrong.

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

    Type

    No type

    Projects

    No projects

      Milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions