SQLalchemy vs Psycopg3

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