Comment by ErroneousBosh
4 days ago
I've always just Postgressed everything. I used MySQL a bit in the PHP3 days, but eventually moved onto Postgres.
SQLite when prototyping, Postgres for production.
If you need to power a lawnmower and all you have is a 500bhp Scania V8, you may as well just do it.
It's pretty easy these days to spin up a local Postgres container. Might as well use it for prototyping too, and save yourself the hassle of switching later.
It might seem minor, but the little things add up. Make your dev environment mirror prod from the start will save you a bunch of headaches. Then, when you're ready to deploy, there is nothing to change.
Even better, stage to a production-like environment early, and then deploy day can be as simple as a DNS record change.
Thanks to LetsEncrypt DNS-01, you can absolutely spin up a production-like environment with SSL and everything. It's definitely worth doing.
Have you given thought to why you prototype with SQLite?
I have switched to using postgres even for prototyping once I prepared some shell scripts for various setup. With hibernate (java) or knex (Javascript/NodeJS) and with unit tests (Test Driven Development approach) for code, I feel I have reduced the friction of using postgres from the beginning.
Because when I get tired of reconstructing the contents of the database between my various dev machines (at home, at work, on a remote server, on my laptop) I can just scp the sqlite db across.
Because it's "low effort" to just fire it into sqlite and if I have to do ridiculous things to the schema as I footer around working out exactly what I want the database to do.
I don't want to use nodejs if I can possibly avoid it and you literally could not pay me to even look at Java, there isn't enough money in the world.
I mentioned Hibernate and knex as examples of DB schema version control tools.
Incidentally, you can rsync postgres dumps as well. That's what I do when testing and when sharing test data with team mates. At times, I decide to pgload the database dump into a different target system.
My reason for sharing: I accepted that I was being lethargic about using postgres, so I just automated certain things as I went along.
I have now switched to pglite for prototyping, because it lets me use all the postgres features.
Oho, what is this pglite that I have never heard of? I already like the sound of it.
`pglite` is a WASM version of postgres. I use it in one of my side projects for providing a postgres DB running in the user's browser.
For most purposes, it works perfectly fine, but with two main caveats:
1. It is single user, single connection (i.e. no MVCC) 2. It doesn't support all postgres extensions (particularly postGIS), though it does support pgvector
https://github.com/supabase-community/pg-gateway is something that may be used to use pglite for prototyping I guess, but I haven't used this.