Raw SQL, SQL Query Builder, or ORM?

I was looking for a query builder and your video helped a lot in giving me a solution. Thank you.

I love SQL, It Is really a great DSL once I learn It properly. I read also a couple of great Brooks from Joe Chelko which are really informative. While I also appreciate query builders I actually use ORMs in production, because migration are vitali in a project life cycle. Nice video

Raw SQL is for real PROs. I am Raw SQL person in every universe.

Yes, it was a weird joke. Like without Red Nose? Are we doing an Redshift ad here??? 🙂

Your raw SQL had a LEFT join, but it looked like in SQLAlchemy you just used .join - are these the same? Or is that specific to the setup of your model classes?

I use raw SQL as functions with typed obj params. Using some custom helper functions, the SQL is compiled and executed.

I dont use ORMs or "?" args though since I find them unreliable.

best option: use a ORM that comes with a query builder as well as ability to run RAW sqls.

It depends :-) When starting from scratch with a smaller web project, an ORM is great. When adding to a bigger application, where parts are developed in different languages with a central database raw SQL often is the only option. I often saw, that complex queries where moved to stored procedures in the database and for another layer of possible validation, sanitation and security.

ORMs are harder to profile, debug and maintain in general.
Usually you need to invest quite some time in learning them and their quirks, pitfalls hidden behind the abstraction. It looks like SQL, tastes like SQL, but it really isn't.
Type Safe query builders are very good and a lot of the time really fast to master if you know the RAW staff, which you should anyway, even if you decide to use ORMs.
Also when choosing ORMs you usually lose performance, sometimes very badly, and it is harder to pinpoint when and where.

I prefer raw queries. They allow for better optimization, and the SQL engine you use is far less likely to change than the programming language you access it from.

ORMs kinda bear additional overhead in that you need to define your schema twice(in you mappers and in your migration scripts), while the other two only need it defined in one place.

Also, no matter which you pick you're still learning and executing all of the same SQL concepts. I've kinda landed in the camp that the only scenarios in which an ORM is being used effectively are also scenarios where you don't actually need an ORM.

I would like input on how to construct hierarchical data structures when joining relation tables. Say I have a one-to-many relationship and would like the many-part to be a list on level down of the parent it belongs to. How to best get an SQL-result into this structure in Python (automatically, or close to it)?

I'm a java developer, and we use spring jpa + hibernate orm.
Most say that I'm conflicted, in the one hand, hibernate makes the whole process of working with the db as an entity so much easier and smooth, on the other, when it comes to preformence, it can be a pain to optimize it.

I've always preferred the raw SQL. I need to know it anyway, to find my way through the database, review and validate the data, etc. My approach, most of the time, is to design and test the SQL on its own, in a SQL editor. After that I integrate it in the client code, with the confidence that the SQL works.

Unless you know SQL well, using the other two would be a mistake. But to be honest, ORM is something you'll regret down the road when you scale. Raw SQL is what I recommend, it'll scale well and it's something you need to learn anyway.

Abstracted SQl, query builders or ORMs, are superior in most scenarios.
The only thing that still bothers me is that there is no common abstraction for sql in CLI; I'm tired of typing the "wrong" squeel syntax over and over.

if you are new learn SQL skip the BS of ORM the only safe way i find ORM is for basic queries that don't join , if you start putting queries in for loop in your application its time to learn proper sql or just skip the BS and learn SQL

If you're dealing with huge amounts of data then an ORM can add a lot of memory and performance overhead compared to raw SQL or a query builder. For those type of queries you often want to get data into a numpy array (or dataframe) as fast as possible and having one object per row is not very useful.

Have worked with all 3 actually. At the end of day, question is what i want from the product i'm working on?
raw SQL tend to be the fastest option and ORM the slowest. However, if you do not have a super time sensitive task, i'd stick to ORM. When you are working on a massive codebase, the last thing you want is the ugly complexity of raw sql. debugging is a massive pain.
query builders are pretty good, but a bit too verbose for me personally. If i'm working with rails, i'd absolutely stick to ORM Active Record. Nothing beats the comfort that it provides. If django, i'd go for a query builder. And for nodejs, once again, query builder. I don't know any good ORM for node though so that's a reason and knex is pretty good.

I think ORM's are better choice, I like the part where I can see what's going on with my dB and how it is structured.
I do can get irritated when I want to change the structure of the tables or database
I have created dynamic system in Python which generates with generates schema and alters the schema automatically instead of hard punching them in the program

The problem with ORM and query builder is that you have quite limited opportunities for query optimization. It is fine to use them for small databases, but if you really want scalability or want to query database in the most performant way - raw sql is the way to go. I have seen quite a lot of projects that ended up with poor performance eventually because of using ORMs.

Django ORM, simple and integrated. Save so much work on most small/medium projects.
- Free admin interface (already debugged) with security, users, login, etc.
- Form conected to your models, etc.
When it’s not up to the task, you drop to SQL for the few queries where you need something special

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.

++SqlAlchemy mixed with Stored Procedures/Functions in the database itself.

While I like ORMs because they get you going fast on projects when you aren’t really interested in how the underlying queries are written / work, I’m in the camp of using raw sql and the native drivers for the languages I use (Java, Python or TS). It’s great because you can continuously practice your sql skills while writing app code, you can get full control over the sql and functionality and you can optimize right within the implementation.

Plus writing sql is fun.

I'm a data cientist and I use raw SQL. Now I am writing a internet app for my wife's company and deciding which approach I must use. And with your video I decided: raw SQL. ORM can't use all the potencials that a SGDB could give. Thanks for the video.

learning sqlalchemy. any in depth guides u can direct me to?

What is lost is the use of SQLite itself. For other DBMSs, they provide a driver which is usually a dynamic C library. This library may be written differently depending on the different standards for defining APIs or it may be a custom API for the database itself, such as ODBC, etc. Eventually, this API can be used with a protocol, for example, when connecting to a certain database, you need to send this URL and the username and password.

But for SQLite, interestingly, the driver itself is part of the database

Like if want a guide for MongoDB

I mainly use the ORM (sqlalchemy) because I'm dealing with a very complex and extensive schema. It works well for development, in that it makes it easier/cleaner to write code. However, I've noticed that for large databases, using the ORM when retrieving objects (rows) is slow (by a lot!) compared to raw sql queries. I guess the overhead cost of creating python objects from the raw queries starts to add up. That's the one downside I've noticed with sqlalchemy.

The issue here is one of purpose. If you are developing a data model, which relations will be there, attribute domains, keys, constraints etc you are likely using pure SQL but once you have the data model and you are building an application which communicates with a database implementing the model then if your application is written using OOP then maybe ORM can be useful but if you are using other paradigms maybe a query builder. For instance in the context of "data <analitics/engineer/science>" "query builder tools" like dbt are used extensively. At the same time inside a analytics team depending on your architecture SQL injection might not be a problem and the "flexibility" of dialects built for analytics like SQL Snowflake or Redshift are a very important feature. So it's a matter of being sensible to the context, as mostly of the time.

Definitely at least know how to use Raw SQL if needed. As a Database Admin I can say, there are times when the generated SQL from any option other than RAW is less than optimal. One thing i do is look for SQL statements that use a ton of resources and suggest ways to make them better. An example i had a few years ago, the sql sent to the database by some auto generated tool was 7 pages printed out. I printed the SQL and used a pen and highlighter and rewrote the SQL to 3 pages. The one i wrote ran in about 1 second, the one the app sent ran for 2+ hours. I gave the developer the new sql statement and he was confused as to how to fix his application. Eventually we found another developer to help him fix it. This kind of thing happens on large complex systems all the time. Its nit really an issue with small simple queries but at least know how to do this, you never know when it may be needed.

I often use a hybrid of ORM and raw SQL:
Some more complex parts of a query I might write as raw SQL, then pass them into the ORM alongside more standard things like selecting normal columns and making reasonably simple joins.
I do sometimes use the SQLAlchemy `with_hint` method to force certain indexes to be used.

One very important thing to note about SQLAlchemy is that it has a `Core` and an `ORM` - I know SQLAlchemy 2.0 changes some of this up, but we are still on 1.x.
The `Core` side compiles statements, executes them and returns simple tuples of the raw data.
The `ORM` side can allow you to fetch an entire row of the table, loaded as a Python Class, meaning you can have your own custom methods defined on that class for calculated properties used later, or for common functions to update fields on the row all at once.

The ORM query can be much bigger (it will fetch all fields on a table), but can be powerful if you want are try to be Object-Oriented - you effectively treat each row of a table as an Object. Though, again, for many situations where speed of queries is important, you often just want to load the limited data you need and keep it quick.
(That said, SQLAlchemy have 'Bundles' which allow you to subclass the main Class for a Table and limit down the columns loaded - SOOOO helpful for speeding up some of my queries loading a half-dozen columns of a 30+ column table)

The SQL Injection protection is massive for a webapp if we are using customer input in the query (especially searches!) and resorting to raw SQL, even in part, needs that little more careful attention to prevent possibilities.

The con you addressed for ORM that its difficult to update the model when ypu change table. In that case you are using ORM in the way it wasnt intended. Why would you change table directly when you can just update the model and let the ORM deal with that?that is actually a pro of ORM 😂

Great content Arjan and team! Can you make something for DynamoDB? Do you recommend any libraries for that?

You can add --sql in start line and vscode will be show pretty sql query

What's the best for building a database that constantly has new columns being added to some of the tables?
I currently have a flimsy solution that's driving me maaaaaad

Python is like systemd, the solution to a problem nobody has.

Hi Arjan, about a year ago I was learning SQL with PostgreSQL and eventually MySQL. My first programming language was SQL before jumping onto python. My SQL is a bit rusty now I am learning Django's ORM and I must say personally I prefer raw SQL versus the ORM. The ORM doesn't feel as powerful as SQL from MySQL or POSTGRESQL. Now the question is, how can I use a SQL database on my Django project versus using an ORM? I would prefer to open up the MySQL and creating the database, tables and rows myself versus using an ORM for my project, but I haven't figured it out yet. Thanks for the video.

Dapper, query builder, stored proc, ORM, raw SQL

I wouldn’t use raw SQL but create stored procs instead

Why I prefer raw sql over ORM's is because the things you learn from raw sql can translate over quite easily to other sql type languages. Whereas, with ORM's, going from one ORM to another is not as simple. This is even more of an issue if you work with multiple programming languages on the backend as they will have their own ORM's you need to learn as well. Raw SQL does not have this issue.

Other advantages are sometimes the queries it generates are not the most efficient. Even some of the methods you use don't do what you expect them to. Prisma was one that messed me up.

ORM's may also not have all the features that the sql language provides due to its complexity, maturity in the ecosystem or simply because the feature itself is very new and not yet supported.

Raw query and some times if database support storage procedures. Fast, Powerful with Less coding, and easy to migrate an another programing language. If you really know sql. All other option will be meaningless. work sql, know sql, write sql ...

ORMs are a cancer, like JavaScript.

SQL is obvious, readable and understandable for what exactly happens to fetch data. Everything else is headblowing. Especially orm which creates simple and absolutely non optimized queries. And you can't deal with that.
Talking about injections is ridiculous.
ORM cannot correctly use foreign keys, cannot correctly create indexes and sequencies. ORM never able to create stored procedures.
When you use sql-like insertions or conditions in orm definition get rid of that orm.

I like Sqlalchemy as it gives you ORM and Core so you can choose to do SQL with Core if you need it for something more complex than is practical with the ORM.

raw SQL. i have my own crud using raw sql for any legacy tables.

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.

Love the channel Arjan! So grateful for your content! keep going!