You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
proposal: use SQLite instead of in-memory database #881
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
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.
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.
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).
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.
Activity
kylecarbs commentedon Apr 5, 2022
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.
Emyrk commentedon Apr 5, 2022
Does vault use a relational db? I figured they use a key/val store. I thought it was etcd backed
deansheather commentedon Apr 5, 2022
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.
deansheather commentedon Apr 5, 2022
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
kylecarbs commentedon Apr 5, 2022
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).
kylecarbs commentedon Apr 5, 2022
Vault doesn't use it for production, and neither do we: https://learn.hashicorp.com/tutorials/vault/getting-started-dev-server?in=vault/getting-started#starting-the-dev-server
Emyrk commentedon Apr 6, 2022
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.
Emyrk commentedon Apr 6, 2022
I'm finding so many random neat projects: https://github.com/genjidb/genji
kylecarbs commentedon Apr 6, 2022
That one specifically is pretty cool.
coadler commentedon Apr 7, 2022
I wonder if once sqlc comes out with templating support we could use that to generate the in-memory representations.
kylecarbs commentedon Apr 7, 2022
That's 🧠
tjcran commentedon May 16, 2022
Because the in-memory db is meant for --dev use cases and not production, I am marking this as a Post-MVP item.
coadler commentedon May 16, 2022
I think we decided to not pursue sqlite cc @deansheather
[-]proposal: use SQLite for in-memory database[/-][+]proposal: use SQLite instead of in-memory database[/+]ammario commentedon May 23, 2022
Per comment, closed for now. Feel free to re-open if wrong.