SQLalchemy vs Psycopg3
Posted by aronzskv@reddit | Python | View on Reddit | 85 comments
So I am currently in the process of building my business dashboard, where the backend is fully written in Python. Now that I have some parts functioning properly I am in the process of migrating all the databases from mongodb to postgres (I used to hate sql and mongodb was easy to use, but Im starting to realise sql is quite useful in the current use case). Now the tables are all set up, but I am not sure what package to use in the backend code, mainly Psycopg3 or SQLalchemy. I know SQL and can write it easily, but the abstractions with SQLalchemy might give additional security features with the way it works, but building all the models and repos will also be a pain in the ass lol.
Does anyone have experience or recommendations on which to use?
YnkDK@reddit
You hate writing SQL? Go for psycopg
GenAI is really good at writing SQL queries that works exactly how you like it (or at least how you describe it). Tug them away using a repository pattern to keep all the raw SQL strings away from your other logic.
If at some point you feel the need to go full ORM you only need to update your repositories.
graduallydecember@reddit
Consider asyncpg if you're working with async and decide to go for raw SQL strings. Also consider how you want to handle migrations if your schemas change in the future.
aronzskv@reddit (OP)
If Im not mistaken psycopg3 has built in async support (and pretty good features on top of that). With migrations Im mainly looking to do that manually since Im used to doing that with sql code.
xAmorphous@reddit
It doesn't, and there's an additional async pool you can use
thashepherd@reddit
https://www.psycopg.org/psycopg3/docs/advanced/async.html#asynchronous-operations
xAmorphous@reddit
I mistyped my comment. I meant to say it does. Fixed above
thashepherd@reddit
Gotcha!
backfire10z@reddit
These are two different tools. SQLAlchemy sits on top of psycopg3 as an ORM.
CuriousHand2@reddit
May I introduce you to the repository pattern?
Regardless of raw sql or ORM, coordinate your database logic in meaningful ways!
I often start with raw salt in this pattern, but I'm old.
aronzskv@reddit (OP)
I havent worked with a repository system directly yet, but I have noticed Im implementing sone of the ideas already lol
The_Tree_Branch@reddit
The O'Reilly book Architecture Patterns with Python has a good example of this design pattern in practice using SQLAlchemy. The authors made the book available for free at https://www.cosmicpython.com/book/preface.html.
It's one of my favorite Python resources.
backfire10z@reddit
True, that’ll solve it. u/aronzskv in case you haven’t seen this yet ^
aronzskv@reddit (OP)
So thats what Im contemplating, I know the advantages of sqlalchemy, Im more interested into the tradeoffs though (a lot more code, read about performance decreases, etc) which is why Im looking for other people their experiences.
Chroiche@reddit
Idk why no one is giving you a straight answer. Disadvantages of ORM:
climb-it-ographer@reddit
To your first point-- with SQLAlchemy you can always just pass in raw SQL if you want. We have some monster queries that would be a nightmare to refactor into the ORM that we keep that way.
DoubleAway6573@reddit
For me the advantage of having an easy way to launch a sqlite for some tests without touching anything at all is too appealing. If that's not a requirement for you, and you are very confortable with sql and can organize this right, then go for psycopg.
backfire10z@reddit
That would depend on the size of the app you’re making and your prior experience. I can tell you that it may not seem messy, but it will become messy unless you’ve already got a plan to manage it. I use SQLAlchemy and mostly just stick to the core, which is effectively writing SQL but using the objects instead of a raw string. This helps with typing and keeping the column names proper. You can always dip into raw SQL strings if you want to with an ORM.
aronzskv@reddit (OP)
Ahaha defo not using java, but my main concern is having to rewrite all pydantic models I have into sqla models, instead of simply validating them and adding it using plain sql. Same with fetching and updating data.
backfire10z@reddit
Ahh I see. Like I said, I don’t know the scale nor your experience. It’s definitely doable with raw sql (or maybe a query builder?).
aronzskv@reddit (OP)
I was indeed looking for maybe a query builder instead of a full orm (or maybe build a simple one)
imheretocomment@reddit
Just use sqlmodel with SA then.
Zifendale@reddit
Don't rewrite your pydantic models, use pydantic models and SQLalchemy together!
dr3aminc0de@reddit
Use what??
L0rdOfTheLarp@reddit
Seconded - the “messiness” that an ORM (SQLAlchemy or otherwise) helps solve is generally rooted in keeping your code more OOP in style. The additional benefit of avoiding prompt injection is a nice to have that can be handled in other ways but should not be overlooked. Even if you’re expecting high numbers of concurrent users, the likely performance bottleneck you will reach first is making sure you have async’d the database driver an various other IO
marr75@reddit
You can use the sqlalchemy core APIs and skip the mapping and OO elements. I generally recommend it for all OLAP work. The ORM is okay for transactional work.
aarontbarratt@reddit
Did you mean SQL Injection? I don't know how you could prompt inject a database like it's an LLM
Zanoab@reddit
I've found SQLAlchemy has many ways to optimize operations so it won't waste time on things you don't need without relying on raw SQL strings.
In one project, I had a function that performs operations on thousands of rows in a particular order with only commits and it took over 20 seconds. I later learned I can manually build the lists of bulk operations so everything is ready in a single pass, execute each operation without synchronizing to skip reloading, and then manually update the state of instances I still need and expunge the rest so I don't accidentally use stale values. It is now nearly instant and the code is much nicer to read.
danted002@reddit
SQLAlchemy has 2 main components: Core and ORM. Core is a query builder while the ORM is, well the ORM. The ORM itself uses Core to build the queries so you can easily use the Repository pattern to group the logic and use Core in the Repository to build the queries.
marr75@reddit
It's a lot less code if you are using it well. SQL is VERBOSE and more difficult to express dynamic bits relevant to schema and names than python/sqlalchemy.
C0dePhantom@reddit
Yep, and from a security angle, having raw queries scattered everywhere is exactly how someone accidently string-concatenates thier way into a SQL injection. SQLAlchemy just makes it way harder to screw up.
phonomir@reddit
Psycopg with type hints make this a non-issue
divad1196@reddit
If you have to ask then SQLAlchemy with alembic.
And honestly, I could use PonyORM or TurtleORM for smaller project, but I would never use raw psycopg3 alone. But I would use DuckDB/Sqlite3 with mostly raw SQL: it's a different use-case.
donat3ll0@reddit
Please god, no PonyORM
divad1196@reddit
What's even your issue with it?
donat3ll0@reddit
Pony has terrible query abstractions that quickly become constraints. It has limited support for optimization and is entirely dependent on how your python is translated into SQL. Debugging is a nightmare due to the lack of transparency, which makes it harder to reason about. Then top it all off with byte code translation that actively breaks down between python versions. Look at its issue tracker.
divad1196@reddit
We agree that it's not meant for production and complex usage.
And we don't need to look at the tracker: the project isn't maintained at all.
Yet, I still use it from time to time on one-shot projects because I like the syntax.
The idea is IMO great, the implementation has flaws. I make a distinction between them.
MathMXC@reddit
Why no raw psycopg3 but raw SQL with sqlite3? Imo that doesn't make a lot of sense. Especially because psycopg3 can do a lot things those ORMs aren't built for (notifications being a major one)
divad1196@reddit
I answered it in my: different use-cases. It's not about the features like LISTEN/NOTIFY.
Web (exposed) / Large projects -> postgres + ORM. Local data processing -> Sqlite/DuckDB/Pandas
Using an ORM does not mean you cannot, on occasion, do raw sql. But I rarely see notify/listen used. On mono-processes, you can depend on the ORM event features if it exists. You can also use an external service like redis/kafka/rabbitmq/.. Supabase has realtime coded in elixir.
MathMXC@reddit
Your argument is confusing me quite a bit.
Nothing you said highlights why you'd want to use raw sql queries for local data processing? All of the benefits of an ORM apply to both web/large projects and local data processing especially because there is a significant overlap between those two (I've done some massive projects that do local processing).
Are you saying you prefer raw sql queries when you want something quick/easy and don't care about security?
divad1196@reddit
I don't know what you don't understand. Have you ever used pandas and duckdb? Or R lang? Or Denodo/Trino?
When you do local processing, you deal with your data, you are not exposed to the web. You mainly ingest your data once then all your inputs come from you. When you do this kind of processing, you might use CTE, views, plugins like graph plugin, .. you write deeply nested and complex data. You likely don't care about persistance or migration as you will ingest new a fresh data for your computation. If you want to empower your AI for example, you don't just give it all the data directly; you will let it pull data into a temporary database, create the queries/views it needs then create the request to get the data it needs. An ORM is just a burden for that.
An ORM is not about just the security. It's a mapper. It makes working in your language easier. This is important as your code grows. It's also easier to spot mistakes, do migrations live, etc ...it does not mean that raw SQL is bad, it'a just not suited.
So no, there is not a significant overlap between them and that's probably why you are getting confused.
SQL is a good language, but your codebase is not written in SQL. It's not meant for softwares. But it's fine to manage your own data.
MathMXC@reddit
> Have you ever used pandas and duckdb? Or R lang? Or Denodo/Trino?
Yes all of the above. There is no need to be condescending.
> An ORM is just a burden for that.
You never mention why an ORM is a burden in this context. Is it because you're changing schemas often? Or is it because you're changing database/backends frequently? Or is it because you have complete control so you don't need to worry about consistency/shared use.
Especially because in some of your examples (like the AI one) having an ORM can greatly increase LLM efficiency because it understands the desired structure of the data and not just the raw tables.
The point I'm trying to get at is: It's important to understand the actual pros/cons of an ORM. And not just pigeon hole it into "usecase A means ORM and usecase B means raw querries"
divad1196@reddit
A chair is convenient to sit on but you don't go wandering around with a chair. It would just be a burden and you don't need more explanation to understand it.
The ORM in the case of data processing does not bring any value. There is nothing to add to it and I don't understand that you don't understand my statement. This situation is as if I didn't receive my delivery and fedex asked me to prove I didn't receive it. If you think there is value to using an ORM, you are the one to bring elements for that.
Again, the statement is simple: ORM don't bring benefits in these cases.
For you examples, if I were to regularly change database, putting aside that there are clearly underlying issues, I would do the opposite of what you just said: using an ORM allows you to more easily change database. So no, the reason why I use raw sql is because I stick to duckdb when I need it.
A LLM knows SQL well enough. If you ingest data in the db it can whatever it needs very efficiently. It does not need an ORM.
MathMXC@reddit
So I'm just going to end this argument here due to your nievness and lack of communication. Here is my final response:
---
> The ORM in the case of data processing does not bring any value. There is nothing to add to it and I don't understand that you don't understand my statement.
Because I'm not taking your word for it. Why does it not bring any value. I built, manage, and run a data processing service which handles over a million unique documents a day. We use an ORM for better team coordination and consistency across developers.
> having me tell you my reasons won't make you grow.
I'm literally asking for your reasons because you haven't given any. I'm not taking your word for it.
> Instead of asking me why I don't use an ORM locally, you should wonder if you really need one.
Did you read my last post? I literally ended with:
`The point I'm trying to get at is: It's important to understand the actual pros/cons of an ORM. And not just pigeon hole it into "usecase A means ORM and usecase B means raw querries"`
divad1196@reddit
Yeah, end the argument, but I won't read. If you want to stop they stop. You don't get to stop and have the last word.
Never said to take my word for it, and that's exactly why I don't know why you think I owe you an explanation.
2ndBrainAI@reddit
SQLAlchemy Core is the sweet spot for your use case. You get parameterized queries and connection pooling out of the box without being forced into the ORM's model/session overhead. A couple practical tips: use
engine.begin()as a context manager for transactions, it auto-commits on success and rolls back on exceptions, which handles the 'commit/connection stuff' you mentioned. Also setpool_pre_ping=Truewhen creating the engine if you're on a long-running server, prevents stale connection errors. You can always layer the ORM on top later if your queries get complex. Solid choice.justcuriousaboutshit@reddit
Try ibis
aarontbarratt@reddit
Personally I would use Psycopg3 because I hate ORMs. They're nice. until they're not, then they suck massively. If you can already write SQL you'll probably find it frustrating using an ORM vs just writing the SQL yourself
If you just RTFM when you use Psycopg3 you will have 99% of the security benefits you would get from an ORM without the ball ache of using an ORM
aronzskv@reddit (OP)
That might be a good reason ngl and is exactly what Im afraid of with ORMs
gdchinacat@reddit
I've never understood how "they're nice till they're not" is a good reason for avoiding them for the things they are nice at. When they fail you revert to plain SQL and do the hard work you don't have to do for all the stuff they handle nicely. Take the leverage where you can so you don't have to do the hard thing all the time.
Most of the ORM performance issues are due to using objects in places where they aren't really appropriate, like aggregating tens of thousands of fields when you don't need all the others or a full object for each record. In those cases just don't use the mappers but rather query only the data you actually need and deal with rows...just as you'd do if you didn't use an ORM for managing the more common case where mapping results to objects makes sense.
mangecoeur@reddit
Eh there’s a lot of this kind of thinking thrown around, personally after 15 years of using sqlalchemy i haven’t had any problems that would have been solved by not using it. The issue is that as soon as you need to turn db rows into python objects you are de facto using an orm. A lot of people who say they are not using one in fact end up building a half baked one themselves, just without the decades of battle testing that sqlalchemy has.
Sqlalchemy is particularly well built, you can access the sql layer just as easily as the object layer, it has solutions for problems you don’t even know you have yet because it’s been around for so long.
Also, for me the one thing I cannot live without is alembic migrations. People say you can just write sql migrations, I’m guessing those people’s apps are quite simple, I cannot imagine wrangling deep object tree migrations without it.
xAmorphous@reddit
There are two types of developers: those who hate ORMs now, and those who will hate ORMs later.
Obviously hyperbole, but there are plenty of anecdotes of projects getting burned by ORMs, but I've yet to encounter a story where people migrated to an ORM after using a driver
gdchinacat@reddit
I worked on a project that used the "driver" for all interactions with the database. In part because the interface to the db was so low level there wasn't much structure around data access and it was strewn throughout the code. There were virtually no transactions because the data access wasn't managed well. These issues were in no way the fault of the driver, but the lack of structure allowed poor coding practices. When I came on to the project I had to fix these issues. Lots of debates were had about to use sqlalchemy or not, and if so just sqlalchemy.core or sqlalchemy.orm. I played around, wrote prototypes, compared, contrasted, and ultimately decided to use the ORM. I am a primary source that "migrated to an ORM after using a driver".
I would make the same decision again. I understand why ORMs raise concerns. They are complex solutions that are overkill for simple problems but don't handle complex problems very well either. They are good for the middle 80%. Fortunately most projects fall in that sweet spot where ORMs solve more problems than they create. They lower the barrier for entry and enable more junior engineers to work with the database while still allowing the other end to go directly to the driver and write the perfect SQL query when necessary.
Sure, they constrain what you do, but that is sort of the point of a framework (ruby on rails embraced this notion to great effect). Sure, they can have performance problems (what doesn't when done wrong). Sure, they have a learning curve (again, what doesn't).
I think your perspective of not "yet encounter[ing] a story where people migrated to an ORM" is because it's not an interesting story to tell. "I was frustrated with writing SQL and switched to an ORM and problems were solved" is not nearly as interesting as "i followed industry standard practice and had no end of problems and eventually scrapped it all and raw dogged it and live was great again". sqlalchemy is the beautiful monstrosity that it is because people had a need for that complexity and didn't want to manage it all themselves...they wanted it tucked away hidden in a mapper that just works...except when it doesn't. It's easy right up until it's not, and then you fall back to the more difficult way. You could do it the hard way everywhere in anticipation of having to do it from time to time, or you could take the easy route that occasionally fails you and you have to do it the hard way. I've never understood the mentality of doing it the hard way all the time because the easy doesn't work 5% of the time.
dashdanw@reddit
Curious to know what you dont like about ORMs. We use the Django ORM at my work at scale and we run into some systematic issues but none that have made me feel like I wanted to get rid of it.
Constant-Poet-5264@reddit
all my homies hate orms
maigpy@reddit
I hate orms and any other implicit magic.
jmacaling002@reddit
Interesting post!
jmacaling002@reddit
Interesting post!
tecedu@reddit
Both + alembic; I like defining the table schemas in code via ORM. And all of the relationships logics and stuff. Doing table operations I like doing via psycopg.
If you use polars anywhere nearby then I would recommend ConnecterX as well, its only for reads tho but its the fastest thing out there.
Mysterious_Gain_352@reddit
Ve por SQLAlchemy Core. Si usas Psycopg3 crudo terminarás programando tu propio mini-ORM a mano para mapear tuplas a diccionarios, lo cual es un dolor innecesario.
SQLAlchemy Core te permite escribir casi SQL puro con autocompletado en Python, asegurando tus queries nativamente sin obligarte a armar repositorios ni clases complejas.
OneDirt8111@reddit
Try asyncpgasyncpg you can create a async to sync wrapper.
gizzm0x@reddit
Don't j kW abiut perf. Buy psycopg3 is a sync native now
Aggressive-Prior4459@reddit
I am not sure I understand what exactly you are asking. But psycopg is a database driver, something that actually allows you to communicate with PostgreSQL database, it's sync and has an async counterpart called asyncpg, and sqlalchemy is a sql toolkit, you normally use it with that driver in most cases some people prefer using it's orm Api which allows you to write entitities like database tables using python objects, you can also use its core Api which is below this or abstraction but you can also use both apis in your application if you want. You might also want to keep your database schema up to Date with your application so you will need a migration tool alembic.
FloxaY@reddit
also check out sqlspec
https://github.com/litestar-org/sqlspec
CatolicQuotes@reddit
use sqlalchemy core. Thats different than ORM.
jceyes@reddit
Yep. If taking lots of aggregates and timeseries and whatnot for a dashboard, you don't want to add a "model" every time
childofsol@reddit
This is a great option, the best codebase I've worked in used this approach
Public_Raise_142@reddit
the “models and repos” part is exactly why i avoided sqlalchemy early on lol
feels like a lot of setup before you even ship anything
psycopg3 lets you just move faster
iluvatar@reddit
I have used both SQLalchemy and psycopg extensively, and I can assure you that from real world experience, I wouldn't go near SQLalchemy for any new project. That's more a critique of ORMs in general than SQLalchemy specifically, although SQLalchemy is the one with which I have the most hands on experience. They're fine for trivial examples, but as soon as you need to start doing anything more complex in a real world application, the ORM just gets in the way. By which I mean that it makes the code less readable and makes the queries impenetrable. So I'd go for psycopg with hand written SQL. It might seem like a burden, but actually in even quite a complex application, there aren't really that many queries, and sticking them all in a single
db.pymodule makes it quite manageable. For reference, when I say a complex application, I can point to two specific examples I've built and shipped which have handled around $1bn in transactions to date.2ndBrainAI@reddit
Both are solid choices — the decision really hinges on your complexity needs. If you're comfortable writing raw SQL and want lean, async-native performance with minimal overhead, psycopg3 is excellent. It gives you full control with very little magic.
SQLAlchemy shines when your schema evolves: Alembic migrations, relationship management, and the ORM pay dividends as the project grows or a team joins.
For a business dashboard where you already know your queries, psycopg3 feels natural and fast. That said, you don't have to choose forever — SQLAlchemy Core works well on top of psycopg3 if you want to layer in abstractions later without switching drivers.
ottawadeveloper@reddit
sqlalchemy is good if you want a full ORM system layered on top of postgres and you want easier migrations. psychopg is better if you want to optimize for performance. Both have their place so it entirely depends whether you want to use that ORM model from sqlalchemy or the raw but more performant psycopg that might make you udo some more work to manage it long term.
aronzskv@reddit (OP)
Yeah exactly, code wise I feel like psycopg3 might seem a bit more messy, but giving more performance. Based off of what Ive heard sqlalchemy does have a bit of a learning curve and is also a lot more code.
ottawadeveloper@reddit
I think it's less code for simple things - like if you just want to treat tables as objects and relationships between them, you can get CRUD operations going fast and you don't need to design the database AND the classes, just the classes (SQLA will build the SQL code for you). Plus SQLA is mostly cross-engine compliant as long as you use the right tools. The code to make those classes and handle edge cases (and work with many different databases) is more complex by far - I've written it myself once upon a time and it's a lot of code and knowledge of how different engines handle things.
psycopg3 will be nice and simple to write queries, but you can't rely on the ORM or you have to make your own classes to do so - I've done that when SQLA felt like overkill and I wanted the performance gains.
aronzskv@reddit (OP)
Thing is, the tables are already designed and models are already built out using pydantic (since I first was using mongodb). What I could do instead of rebuilding all pydantic models into more complex ORM models, is just validate the data (which also happens on the front-end before the request is ever sent) using the model and using psycopg3 and simple strings to add the data to the db. This project will mist likely stay with postgres indefinitely, so future migrations are not an issue.
For future projects you might be right though and sqla might be better fit in those cases.
phonomir@reddit
I would recommend this approach. Psycopg can also be setup to return pydantic models directly from queries, reducing the amount boilerplate for reading and validating data. Look at the class_row row factory.
dangerousdotnet@reddit
But you can use your pydantic models as ORM models.
vater-gans@reddit
the performance impact of sqlalchemy constructing the orm objects will be very small compared to the time you spend in the database.
obviously there’s always exceptions (like de/serializing gigantic json), but generally the performance impact is negligible.
Delengowski@reddit
This is a bit unfair to sqlalchemy.
You can use the orm to completely construct a statement without even establishing a connection, and then pass that directly psycopg3, by passing a ton of object creation.
This shouldn't even be a consideration by the way unless you're doing queries that are retrieving like millions of rows at a time.
Darwinmate@reddit
SQLAlchemy does migrations?
red_demon_wizard@reddit
You have to use alembic for migrations with SQLAlchemy.
ottawadeveloper@reddit
yeah with alembic they're easy
delsystem32exe@reddit
sqlalchemy
Flame_Grilled_Tanuki@reddit
Alternatively, consider Django and sweep all the database wrangling under the rug. You also get migrations, fixtures and access to an admin dashboard for easy additions/modifications of data in the db.
catcint0s@reddit
I have always found SQLAlchemy to be a bit too heavy, I would check Piccolo ORM and see if you find yourself missing any features.
Delengowski@reddit
What's heavy about it?
TTUnathan@reddit
Can’t speak to Psycopg3 but I’m really enjoying Psycopg2 + PyPika right now. I’ve used Psycopg for a few years now and have always felt icky writing raw SQL in my code, that might just be a me thing. Not sure what others have to say about PyPika but it’s pretty feature rich and clean in my 6 or so months experience with it.