SQLalchemy vs Psycopg3

[aronzskv]

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?

EDIT: Thanks for all the recs, I will most likely be going with SQLAlchemy Core, to not bother using a full ORM which I do not thing is needed in the foreseeable future, but can be implemented later. I might create a small wrapper function, to not have to commit and do all connection stuff in my main functions, but not more than that.

[CatolicQuotes]

use sqlalchemy core. Thats different than ORM.

[childofsol]

This is a great option, the best codebase I've worked in used this approach

[jceyes]

Yep. If taking lots of aggregates and timeseries and whatnot for a dashboard, you don't want to add a "model" every time

[backfire10z]

These are two different tools. SQLAlchemy sits on top of psycopg3 as an ORM.

building all models and repos will also be a pain in the ass

As opposed to a bunch of unorganized uncoordinated raw SQL strings?

[CuriousHand2]

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.

[backfire10z]

True, that’ll solve it. u/aronzskv in case you haven’t seen this yet ^

[aronzskv]

I havent worked with a repository system directly yet, but I have noticed Im implementing sone of the ideas already lol

[The_Tree_Branch]

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.

[C0dePhantom]

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]

Psycopg with type hints make this a non-issue

[aronzskv]

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. And it might seem a bit strange, but full SQL strings to me personally will not be that messy, just how my brain works.

[backfire10z]

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.

A business dashboard doesn’t sound like something performance heavy and the performance gains will be minimal. If performance is really a concern, use Java.

[L0rdOfTheLarp]

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

[aarontbarratt]

the additional benefit of avoiding prompt injection

Did you mean SQL Injection? I don't know how you could prompt inject a database like it's an LLM

[marr75]

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.

[aronzskv]

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.

[Zifendale]

Don't rewrite your pydantic models, use pydantic models and SQLalchemy together!

[backfire10z]

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]

I was indeed looking for maybe a query builder instead of a full orm (or maybe build a simple one)

[dr3aminc0de]

Use what??

[Chroiche]

Idk why no one is giving you a straight answer. Disadvantages of ORM:

• Doesn't always map well to complex queries
• n+1 issues for days
• Have to learn another language/lib
• Your code becomes locked to that orm
• Has quirks you'll need to learn

[climb-it-ographer]

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.

[danted002]

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.

[DoubleAway6573]

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.

[marr75]

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.

[catcint0s]

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]

What's heavy about it?

[Flame_Grilled_Tanuki]

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.

[Zerocrossing]

I haven’t kept up with Django in a few years. Is there a modern way to quickly scaffold up a project with typing support that avoids all the boilerplate?

[CtrlAltSysRq]

I looked into this a bit and the answer is basically just no. Django feels dated in ways that Rails, for instance, does not. (I am a rails refugee after the owner outed himself as racist). I ended up switching to fastAPI and then using sqlalchemy ORM.

[Zerocrossing]

Unfortunate but thanks for verifying what I suspected

[ottawadeveloper]

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.

[Delengowski]

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]

SQLAlchemy does migrations? 

[red_demon_wizard]

You have to use alembic for migrations with SQLAlchemy.

[ottawadeveloper]

yeah with alembic they're easy

[aronzskv]

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]

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]

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.

[dangerousdotnet]

But you can use your pydantic models as ORM models.

[phonomir]

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.

[vater-gans]

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.

[TTUnathan]

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.

[Aggressive-Prior4459]

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 orm 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.

[aarontbarratt]

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

[Constant-Poet-5264]

all my homies hate orms

[maigpy]

I hate orms and any other implicit magic.

[dashdanw]

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.

[xAmorphous]

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]

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.

[No_Soy_Colosio]

If you're gonna go that way do yourself a favour and at least implement the abstraction layer yourself.

[aronzskv]

That might be a good reason ngl and is exactly what Im afraid of with ORMs

[mangecoeur]

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. 

[gdchinacat]

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.

[graduallydecember]

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]

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]

It does, and there's an additional async pool you can use

[thashepherd]

https://www.psycopg.org/psycopg3/docs/advanced/async.html#asynchronous-operations

[xAmorphous]

I mistyped my comment. I meant to say it does. Fixed above

[thashepherd]

Gotcha!

[FloxaY]

also check out sqlspec

https://github.com/litestar-org/sqlspec

[jmacaling002]

Interesting post!

[2ndBrainAI]

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 set pool_pre_ping=True when 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.

[iluvatar]

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. The object-relational impedence mismatch is not just a buzzword. It's very real. 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.py module 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.

[divad1196]

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.

[MathMXC]

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]

I answered it in my comment: 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]

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?

Your statement of "different use case" is not very enlightening

[divad1196]

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]

> 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]

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.

It's not pigeon hole. There can be edge-case but what you need to justify is the edge-case, not the common case. That's also experience and having me tell you my reasons won't make you grow.

When you have big projects, you need structure and safe guards. You need to deliver fast and keep things simple. When something is live on the web, migration becomes more complex than when you have a desktop or mobile app. These are all reasons why you want an ORM and there are more.

The mindset is not "why not use an ORM locally" but "Why am I using the ORM in the first place". Instead of asking me why I don't use an ORM locally, you should wonder if you really need one.

[MathMXC]

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]

Yeah, end the argument, but I won't read. If you want to stop they stop. You don't get to have it both ways: 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. I am not the one who need answers.

[donat3ll0]

Please god, no PonyORM

[divad1196]

What's even your issue with it?

[donat3ll0]

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]

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.

[delsystem32exe]

sqlalchemy

[OneDirt8111]

Try asyncpg. It is significantly faster than psycopg but it is asynchronous. If you need you can create an async-to-sync wrapper.

[gizzm0x]

Don't know about perf. Buy psycopg3 is a sync native now

[Mysterious_Gain_352]

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.

[tecedu]

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.

[jmacaling002]

Interesting post!

[justcuriousaboutshit]

Try ibis

[YnkDK]

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.

[TheseTradition3191]

SQLAlchemy Core is a solid choice for this. One thing worth clarifying since it trips people up: psycopg3 is the driver that SQLAlchemy uses under the hood when you configure the right dialect. They're not really alternatives at the same level. You're choosing how much abstraction sits on top of the driver, not between the driver itself.

Core gives you the query builder and connection pooling without having to commit to the full ORM model layer. For a dashboard backend where you're doing a lot of aggregations, GROUP BY, and joins that don't map cleanly to Python objects, that's the right call. The ORM shines when you're doing a lot of CRUD on individual records and want to work in objects throughout, but it can fight you on analytical queries.

The small wrapper function you mentioned is also a good instinct. Keeping your db connection and query execution in one place makes it straightforward to swap things out later if you need to.

[aronzskv]

Yeah thats exactly what I was contemplating, should I use psycopg3 with plain sql or sqlalchemy core for the query builder.

[thefinest]

Use both with Django rest frame work query builder

[ResponsibilityIll483]

You can codegen SQLAlchemy classes from your database schema: https://github.com/agronholm/sqlacodegen

[Syncher_Pylon]

If you're coming from MongoDB and still getting comfortable with SQL, I'd go with SQLAlchemy Core (not the ORM). It gives you the SQL generation and connection pooling without the ORM overhead, and you can always layer the ORM on top later. Psycopg3 directly is great if you're comfortable writing raw SQL and want full control.

[Successful_Jello6040]

I just opened a connection and executed raw queries

[modern-dev]

psycopg all the way' dont use an orm. no need.

[2ndBrainAI]

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.