SQL vs ORM - Which One To Choose For Database Interaction

Should you use Raw SQL or an ORM to interact with the database? this short, I break down the pros and cons of both options so you can make an informed decision. Spoiler alert: neither option is perfect, but one may be a better fit for your project than the other!

#arjancodes #softwaredesign #python

[maskettaman]

This is why I love Entity Framework when working with .NET. All database interactions are expressed using LINQ which is ubiquitous and understood by all .NET developers. No different than working with any other collection.

[kslader]

I'm curious what your thoughts on sqlachemy are now and a more indepth discussion.

[roberttran]

SQL injection risk is just not worth the flexibility, so I try and use ORMs as much as possible (even though they suck). Queries used in application should generally be simple. If not, your data management might need some data engineering processes independent of your application. Thoughts ?

[HexenzirkelZuluhed]

Sometimes ORM can be nice. But I'm back to SQL mostly. Especially when working (reverse engineering) with someone else's Schema.

[edgeeffect]

It seems to me that most (all?) ORMs are just SO bloated. But when I've got a complex multi-join query result that I need to map to JSON, working without an ORM generates A LOT of clunky code. Many people view ORMs as JUST an abstraction over SQL and they're missing the point... ORMs are there to map a result set into objects. When you've got a relational database talking to a front end via JSON (or, God forbid, XML) there's just no getting away from ORMs. I'd always recommend using one that uses a Repository / Unit of Work / Plain Data Object pattern rather than having to put up with the Active Record pattern though...

Venting, after struggling with having to do ORM "by hand" last week at work.

[rikschaaf]

The nice thing about ORM though is that it doesn't necessarily have to be implemented with a SQL Database backend. In Java you could use Hibernate for your Entity classes and Repository interfaces, but those repository interfaces can also be implemented manually with HashMaps instead of SQL database tables, for instance for testing purposes or for a demo where you don't have an actual database. The nice thing is that the ORM framework is SQL implementation agnostic and drivers implement the specific SQL interaction interface under the hood.

[solknar]

Do you plan on doing a video comparing dbt and sqlmodel? sqlmodel is awesome in my opinion, and for CRUD operations I think sqlmodel is just the best

[rihhard]

What is that foreshadowing stock office clip where the computer is coding by itself 😂

[GodotWorld]

I'm definitely an ORM fan. I find it can really help to break complex queries down into easily digestible steps, with way less code. I also find ORM queries written by other developers much easier to follow than massive SQL joins on more than three or four tables, written by others. I've seen some really nasty hacky things done to jump though multiple tables in a DB to link two things together. More likely bad DB design than the person just trying to get their data out.

[robbybobbyhobbies]

Is there an Ecto-equivalent in Python? The Elixir data access library shuns any object-orientation but offers excellent facilities with a clear interface, no hidden "magic" stuff.

[nsambataufeeq]

SQLAlchemy can be done in an"in-between" way

[NathanHedglin]

ORMs allow me to make soft deletes. No one can actually delete anything.

[irfanfauzi]

Use both. But my experience with sqlalchemy is just straight bad

[clauseclause]

ORM gives you one unified interface for any SQL dialect (or no-SQL in some cases)

[fred.flintstone]

So you say SQL is a unified language for querying data? Hold up! Not so unified! Queries written for one RDBMS does not work for another RDBMS, you can't write a query for SQL Server and have it run on PostgreSQL or MySQL, it doesn't work.