Python developers seem so desperate to do anything to avoid writing actual SQL. If I could never see sqlalchemy trash ever again I would be so happy!

What about a slightly more complex example, like a User having Followers (which are other Users)?

Depending on your schemas/tables, you do a join, or a double join. That's it.

It's probably wayyyyyy simpler than learning an ORM.

When I just started as software engineer 20 years ago we did not have ORMs and our first versions of software looks exactly as you described in your article. It was fine.

After some months and dosens of new features we had hundreds of such repository functions and maintainse became an issue.

We are develpers, so we desided to reuse some common queries and start to extract some sql queries to it's own constants and then functions, and of corse at some moment we realize that this common queries sometimes not so common and we start to add `if` statement to them.

Next step was to use Builder pattern! So you see where it goes...

After 3 years we had our own ORM which generate classes from DB with all bells and whistles we need, it was a huge win for us. And everybody did it that time, we all had same problem, that adding a repository for a new Model took day or two and we hire jun/mid level developers to do this absolutly boring and repetitive work.

And do not forget about transactions, you'll need to do it. With UnitOfWork or whatever.
And migrations.
My point is - ok, you will start simple and clean, but in some month you will start to build your own ORM instead of working on business functionality.

So with this approach you building a trap for your business.

> So with this approach you building a trap for your business

See, I would argue that leaning heavily into ORMs also traps businesses. Businesses become defined by the ORMs, working outside of that boundary becomes difficult.

I believe ORMs push software developers to think of their database as their business. This is not to take away from the importance of databases (or storage), but business should always be within the code, within the abstractions we build to define our business.

Honestly it looks like you just wrote your own little ORM with classes, abstract base classes, custom methods, etc. At that point I'd rather use a library that has done that for me. If you want to "just write SQL", then you should JUST WRITE SQL and squish the result into a custom row factory. You've implemented multiple files, classes, etc., which is more than just writing SQL.

This was an article titled "...just write SQL" in which precisely two SQL statements appeared, the rest being Python code.

You are right to spot the software patterns and abstractions in the post, but you will also notice that my implementation lacks the fundamental aspect of modern ORMs: The query builder, or an API to interact with SQL directly.

That is the fundamental aspect here. I am not advocating for messy code where you directly write SQL in your business logic, I do believe in abstractions and basic software design principles, but I also believe that once you drill into the details you should be able to see well defined communication with your storage layer, without the need for profiling or debugging.

I actually did a lot of this at a previous job! using psycopg2 even. We didn't quite have an object-oriented repository pattern like this though, just straight up `cursor.execute`/`cursor.fetchone`/`cursor.fetchall` calls. I did put some more common operations into helper functions that took in a cursor and some arguments and returned a record, record iterator, or `None` (sometimes they'd raise an exception if a record couldn't be found, if that made sense for the application)

Pros:
- I learned more about SQL doing this than the rest of my career combined
- it was *very* easy to debug the db interaction because I could practically copy and paste the queries between python code and a database interface (e.g. psql)
- Once I (and others) was fluent enough in SQL, we could instantly understand what some code wanted the database to do, and find problems very quickly
- Extremely versatile - if the db can do it, you can code it. No need to wrangle with an ORM or figure out the 'correct' way to do it for that ORM's paradigm

Cons:
- Zero migration support (that wasn't actually a problem, as the db schema was managed by another project, but it would be a big Con in any other context)
- No record cacheing or magic association navigation
- pagination and iterating over records was completely manual
- Transaction management was also very manual (again this one may be less broadly applicable, as psycopg2 does have some transaction support)
- We had to hand-roll testing support for database interaction
- Embedding SQL queries in python code didn't always play nice with tooling and wasted a bunch of time bikeshedding on the formatting of the SQL and how to embed it
- It contributed to a false sense of safety w.r.t interacting with databases directly... including the production one
- There was a lot of copied & pasted (often with subtle changes) versions of similar queries in a lot of places (but that is not a terribly hard thing to avoid, to be fair)

I don't have a bottom line opinion here. Like any technique or tool, raw SQL has its benefits and drawbacks, and a mature team can make their own decisions about what's the right call for them. As the complexity of the domain model increases, an ORM's benefits may outweigh the drawbacks.

> On the other hand, Python does not have anything in the standard library that supports database interaction

The sqlite module has been a part of the Python standard library for several years..

Great article. Some potential minor typos/errors about imports:

from psycopg.rows import class_row should be in external/postgres.py, not user/repository.py;
Should also import new_cursor (i.e. from external.postgres import conn, new_cursor) in user/repository.py

Thanks for pointing that out! Fixed.

IMO, the key here is to ask what you're using SQL for in the first place.

If what you're doing is standard application stuff, where the database is being used as a dumb repository for application data, this is exactly the scenario ORMs are designed for. Pick one that's battle-tested, widely used, and well designed, it'll serve you well. (In Python, SQLAlchemy gets my vote for not being hardwired into a specific web framework, and for general flexibility.) If you don't, you'll just end up cobbling your own together anyway.

If you're building a reporting or analysis tool, however, or orchestrating complex data-heavy operations, that's when it makes sense to ditch the ORM and go to raw SQL (or SQL with a templating engine). This is the kind of thing where you will inevitably find yourself using the "raw SQL" escape hatches in the ORM because you need precise control over exactly how the query is written, or you want to use esoteric features of your particular database which the ORM doesn't support. At that point, the ORM is just adding complexity. Again, go with the flow.

(Side note for a common talking point on the ORM side: "What if we switch databases?" is a silly reason to use an ORM. How often have you actually needed to do that? I'm not saying it never happens, but it's far too rare a scenario to let it dictate how you write code every day. And if you do have to do it, it's going to hurt whether you use an ORM or not.)

https://github.com/sqlc-dev... might be a good addition to your toolbox if you like writing sql: it allows you to write sql in a file and generate python code to use it.

I have tried using ORM tools for python a couple of times over the years, but (at least for me), just normal SQL is just always easier and faster. Great article!

The point of a good ORM is to manage migrations for you imo, not to provide easier access than SQL. Deterministic, atomic forward and backward migrations versioned together with the codebase and autogenerated from the python types are the real value provided by something like the Django ORM, and are really hard to do well manually across a large team.

With sqlalchemy you don't have to use the ORM, you can use just sqlalchemy core and write sql.
Agree with you, sql is universal, ORMs are not.

This seems to be entirely skipping over the biggest reason not to write your own SQL: injection attacks. Could you explained a little on how you avoid them?

Binding variables, they're there.

In the examples, cur.execute safely replaces %s with escaped representations of values. The author doesn't fill the query template by hand. I think, most modern SQL libraries have and encourage this feature. psycopg 3.1+ doesn't even typecheck with mypy if the string is dynamically constructed by hand

Got it, thanks