3 ms·
Ask HN: Why use an ORM?
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 4y agoWe 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.
- PaulHoule 4y agoThis. You might have to do a bunch of inserts and updates and want to know all the escaping is done correctly, that insert and update timestamps are handled, etc.
- Silhouette 4y agoYou don't generally need an ORM for that though. The main RDBMSes and the popular APIs for them all support prepared statements that will handle placeholders and escaping for example. And there are lots of query builder libraries that can help to build more complicated SQL queries programmatically without imposing any object model on top.
- PaulHoule 4y agoThose tools blend into each other. If you want to model an inheritance hierarchy as SQL tables that is problematic because there are three ways to do it that all have some good points and some bad points but none of which are completely satisfactory. If you were working in PHP you can make something very simple that looks like an ‘active record’ that writes your INSERTs and UPDATES for you. You could have something like that in Java but you might rather generate some Java Bean stubs to do things that Java way. There’s something to say for “relational-object mapping” that brings relational structures into the OO world of which JooQ is a good example.
- gregjor 4y agoI can write SQL safely so I’ve never used an ORM myself. I have maintained code that used an ORM. When I’ve asked other programmers why they use an ORM the answer usually comes down to “Because I don’t know SQL well enough to write it myself.” Understanding SQL and the relational model has for me proved the most valuable and durable skill I ever acquired. I’ve avoided rounds of layoffs and got hired just because of that skill.
- duskwuff 4y agoBecause, 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.