Why use an ORM?

Question

Why do people use an ORM (such as SQLAlchemy)?I find just writing/executing the SQL queries with some sort of api (e.g. asyncpg or sqlite3 for Python) much simpler. The only use-case I can think of is easy of transferring between databases (e.g. from SQLite to PostgreSQL) or a specified schema to reference from (so you get type hints and stuff like that).

colesantiago · Accepted Answer

We had a post mortem the other day with and engineering manager and some engineers in my team about a really bad security breach (SQL Injection) and this was due to hand coded SQL in a complex query.
From then on our team we decided to switch to using an ORM rather than doing messy SQL queries that could lead to that incident.
We are currently still hitting hundreds of thousands of queries on the backend and the team not looked back since.

gregjor · Answer

I can write SQL safely so I&rsquo;ve never used an ORM myself. I have maintained code that used an ORM.When I&rsquo;ve asked other programmers why they use an ORM the answer usually comes down to &ldquo;Because I don&rsquo;t know SQL well enough to write it myself.&rdquo;Understanding SQL and the relational model has for me proved the most valuable and durable skill I ever acquired. I&rsquo;ve avoided rounds of layoffs and got hired just because of that skill.

duskwuff · Answer

Because, in most applications, a lot of your SQL queries will be doing boring CRUD operations (select row by PK, select rows matching indexed condition with limit/offset, update/delete row by PK, etc). Using an ORM means you don't have to waste time writing out boilerplate SQL for those operations. If it's a really good ORM, it can help generate more complex queries as well.