Raw SQL, SQL Query Builder, or ORM?

Not sure if you should use Raw SQL, a SQL query builder, or an ORM for your next project? In this video, I’ll explore the pros and cons of each option, allowing you to make an informed decision based on your specific needs. Choosing the wrong technology for your project can be a costly mistake, so it's crucial to understand these tools.

🎓 Courses:

👀 Code reviewers:
- Yoriz
- Ryan Laursen
- James Dooley
- Dale Hagglund

🔖 Chapters:
0:00 Intro
1:18 Code example
2:09 Main options
2:44 raw SQL
7:12 ORM
12:49 SQL query builder
14:30 Final thoughts
16:01 Outro

#arjancodes #softwaredesign #python

DISCLAIMER - The links in this description might be affiliate links. If you purchase a product or service through one of those links, I may receive a small commission. There is no additional charge to you. Thanks for supporting my channel so I can continue to provide you with free content each week!

[AggyLovesGames]

I'm a data engineer. I almost always use raw sql because I can more explicitly optimize queries

[kurt]

Almost always raw sql. Here's why:

- I know exactly what I'm getting.
- The query can be run completely independent from my app.
- The query can be shared between multiple apps.
- I can always find a DBA who can read raw sql.
- I never have have to debug an abstraction for corner cases.
- Every single advanced feature of my database is supported and works exactly as documented.
- If I use a different language on a new project, my SQL knowledge is still valid.

[hirolau]

My databases are typically complex. Never found abstractions around the SQL helped me. Just going raw SQL

[FlisB]

I like SQLalchemy, but I think raw SQL is a very useful skill to master, it is been around for ages and it will be around in the future as well.

[SaniSensei]

Note that, if you use e.g. Mapped[str] = mapped_column(...) - the nullability is also derived from the Mapped type. Mapped[str] would be NOT NULL in your cases, and you need either Mapped[Optional[str]] or Mapped[str | None] to allow NULL (or I believe set nullable=True explicitly)

[bernhardkrickl]

The main problem with ORM is when you fall into the trap of stopping to think about and harness the power of the relational database model. You limit your possibilities and don't even notice. And I'm not talking about complicated queries here. Eg. with an ORM you can easily load big, deeply nested data from the DB into an Object. This is nice but can produce dramatic performance issues. This is not hypothetical. Been there, done that, didn't like the t-shirt.

[wp_panther]

Subscribed for the smoke-signal, sign-language, and Binary Clown-based SQL queries. glad I finally found a channel paying attention to the bleeding edge of clownbit accessibility 🙏

[dirkschannel]

It’s personal preference. I prefer writing SQL. Well, I do SQL for 25 years, so it’s muscle memory for me.
But I do understand people who prefer ORMs. In big projects I use stored procedures, materialized views, etc. I recently learned that some people think this is bad practice.
Well, in the end, a software must do it’s job and the customer must be satisfied.

[edgeeffect]

A lot of people assume that the sole purpose of an ORM is to provide an abstraction over SQL. But the important thing about an ORM is that it maps result sets into objects. ORMs often (always?) do provide that abstraction layer over SQL but where we're often dealing with APIs that are expected to return result sets in JSON, it's the object mapping that's their most important function. Some ORMs take that "Object Mapping" idea a little to literally and only map data into "strict OOP" objects with some kind of class definition but often all we actually need is for the result set to be returned in a collection of nested dictionaries.

[CharlieSmithv]

Would be interested in a part 2 where you give your thoughts on which to use given the application use case. My understanding is an ORM can help with a multi-user application like a Web frontend, where throttling to the DB might be necessary. 

In either event, it seems you need to know the SQL query you need (obviously) so would be a stretch to go above and beyond unless for performance or security.

Thank you!

[manonthedollar]

Really great topic! I have experimented with ORMs but I just keep going back to raw SQL. I know exactly what's going on in my queries, I can get really nitty-gritty with optimizations, and it works with any language. It certainly can get rocky "manually" handling the mapping between python objects and their representations in a database, but there's no perfect solution for that I guess.

[bryan_hiebert]

I love taking a break, watching your channel and reading the comments. I think my brain grows every time.😂. I really like the implicit handling of transactions using ORM with SQLAlchemy. I think it just makes the code cleaner when working with tables that are unlikely to change. But when doing machine learning or exploratory data analysis the flexibility of SQL would certainly be the way to go.

[strikef]

Most of the tech debt I’ve come across has been caused by orm generated queries. It gets to the point where at scale you just have to use raw sql or you’ll be dealing with contention. Soft deletes, inserts are all a nightmare via ORMs. There are instances where an ORM will come in handy, such as building something quick, but it’s important to know the caveats of doing so; mainly that you’ll be hiring a sql person to refactor your ORM queries as you scale.

[vhphan]

I almost always use raw SQL. And when I do, I use the Jetbrains IDE, such as Pycharm Professional (not community version). It has such great support for database which amongst other things:
1) SQL syntax highlighting and SQL code completion from within the .py file
2) Ability to run SQL query from any SQL text string in the .py file directly to the query console
3) Provide ability to jump to SQL table or views definitions from python code...

I do love VS Code. But as much as I love VS code, when it comes to any Python code that I need to use raw SQL, Jetbrains IDEs just blows VS Code out of the water.
But of course, the con is it is not free :-)

[cyberjoy]

I always prefer to use raw SQL, and I have been doing SQL for 30-plus years.

[miquelgonzalezrossello]

Thanks for the video Arjan! It's always advisable to close the connection when you're finished querying.

[midicine]

Can you speak to the advantages and disadvantages between data mapper pattern vs active record?

[marioluizbernardinelli]

Hi, Arjan. Nice video. I have a suggestion for an another video: SQL and NoSQL comparison. Think about this. Thank you.

[raj.svc.google]

Please talk about stored procedures. For mature schemas that contain deeply derived tables, I believe SPs to be arguably the best option for secure, efficient & idempotent inserts/updates.

[julienyt]

SQLModel, combine validation with pydantic and sqlalchemy In the same code.