5 ms·
I ran into something similar recently. I'm writing a new backend app. I was thinking of maybe using SQLite for dev and Postgres for staging & prod (bad idea, I
by Scene_Cast2 3y ago
I ran into something similar recently. I'm writing a new backend app. I was thinking of maybe using SQLite for dev and Postgres for staging & prod (bad idea, I know..)
I found that there are just so many differences that supporting both leads to having multiple codepaths. Some examples:
- only postgres supports `with conn.cursor as cur`, need `cur.close()` or commit or similar with SQLite
- Escaping things for columns and table names: postgres has psycopg2.sql.SQL.format() that only works with postgres connections.
- greggyb 3y agoThe things you have described as differences between the DBs are differences in Python libraries.
- amluto 3y agoThe Python SQL ecosystem is IMO pretty bad, and the root cause seems to be that DB-API (PEP 249) is really quite awful. As my least favorite example, DB-API has two transaction modes, both of which are unnecessarily error prone: autocommit on, and autocommit off. Autocommit on makes transactions useless (intentionally), but it still allows commit() and rollback(), so one can write code that thinks transactions are enabled, have it appear to work, and end up with all manner of corruption. Autocommit off has a bad interface. The begin() function is entirely missing, so you can’t pass it arguments. Worse, since there is no explicit start of a transaction, the library can’t catch misuse of a transaction. As a result, every SQL library for Python of any reasonable quality implements its own special API, and nothing is particularly interoperable. This is all sort of fine if the database is only accessed through a higher level library like SQLAlchemy. But sometimes a developer just wants to execute raw SQL against a database, and the situation in Python is not very nice.
- patmorgan23 3y agoDoesn't SQL alchemy make it pretty easy to use raw SQL?
- amluto 3y agoYes, but how many people use SQLAlchemy just as a database API? I think the real problem is that DB-API exists and is bad — its existence comes with the automatic imprimatur of being an official standard and this seeming desirable to use.
- pphysch 3y agodevcontainers (e.g. in VSCode) make local development with Postgres almost as easy as SQLite.
- CharlesW 3y agoAnd if you're on a Mac, Postgres.app makes it even easier: https://postgresapp.com/ https://postgresapp.com/ (PostgreSQL experts: Is there a magic way to sync schema changes and data from a local instance to a production server?)
- niels_bom 3y agoNot an expert, but as both schema changes and data changes are queries you can probably do Streaming Replication (1) 1: https://wiki.postgresql.org/wiki/Streaming_Replication https://wiki.postgresql.org/wiki/Streaming_Replication
- anonzzzies 3y agoTrue, but they do put a noticeable strain on my battery life of my laptop (m1), making it go from day long to hours, so I stopped doing that. That’s with only arm64 containers; with x64 ones, it’s even worse of course.
- sureglymop 3y agoI usually just run a postgres container in development. A small bash script or even docker compose works fine.