Hacker News Reader

Comment by stilisstuk | Hacker News Reader

Why is nobody ever writing raw SQL? I've never understood why ORMs are sine qua min.

Because you won't use raw SQL on any serious project forever. You'll end up building an ORM yourself or at least a query builder.

Do you mean "sine qua non"?

People don't generally like writing raw SQL because you have to map the results to and from your programming language. So at a minimum, you need a query builder that does some minimal and flexible mapping.

Ha. Yes. I apologise for the auto correct.

Building basic CRUD apps as a hobbyist, I've just never had that problem. My app needs some data. I fire of a query af psocopg2 gives me back my data.

I know I'm the least experienced. So I'm not arguing. I just don't understand it

(I work mostly be with data / BI, so I'm familiar with SQL)

Imagine be you build a "search users" page with many filters, you will end up inventing query builder at one point. The more dynamic data you need, you will end up reinventing those systems. The worst that can happen is when you start concatenating sql queries together.ORM/query builders save you that headache.

Yes..i have concatenated SQL i must admit.

How do people who prefer to write raw SQL usually deal with:

1. Query building, particularly when the query needs to be dynamic based on user input? Do you end up concatenating strings together or do you use a separate query builder?

2. Coalescing result sets produced by JOINs back into object form? Example: if you want to fetch users along with all their posts your query will return multiple rows per user, but when working with objects in your app you want each user to have a list of posts so you can simply say users.posts.

3. Property change tracking? Example: different parts of your app might update different properties for each user. If the user's email and last_login changes you need to write one query. If the user's password changes you need to write a different query. If the user's email, name, and location changes you need to write another query. An ORM with change tracking will figure out exactly which properties have been modified and issue the correct SQL to update only the changed properties. When working with raw SQL do you simply end up writing different queries for each possible permutation of changes?

Usually by writing their own lightweight ORM as part of their application. ¯\_(ツ)_/¯

1) concatenating. Not always possible in complex cases. 2) I don't think it's that hard to write 'select posts from user where user = ?' 3) I prefer functional to oop. In my small apps writing a function with a transparent SQL have worked fine.

Re: 2) For a single user, that's fine. What if you are trying to fetch posts for N users at a time? Issue N separate queries?

It's easy to write raw SQL queries. It's a lot harder to generate SQL programmatically, correctly, based on a variety of requirements your app may have. ORMs do this well.

You can write direct SQL queries using SQLAlchemy Core without engaging the ORM layer at all. It protects you from a few of the footguns associated with raw SQL but still provides most of the power and features you would want from raw SQL. If you decide that some part of your system could use the ORM then it is easy to integrate it into your existing Core work or visa versa.

If you want to use an SQL database with an OOP language like Python, you will end up using an ORM library, or writing one.

For record/object-at-a-time, an ORM saves tons of code.

SQL's design is optimized for processing unordered sets of records.

Raw sql is not composable.

There is a middle-ground between writing SQL statement strings in your code, and a full-blown ORM: query builders. At least in my experience with small to medium projects, these have far fewer footguns while keeping the code composable and readable. Here's one for Python: https://github.com/kayak/pypika

I'll just say that SQLAlchemy can also be used effectively as just a query builder in places that you need it.

Thanks

This is where I tend to gravitate. Even using ActiveRecord, what people consider the “ultimate” ORM, I still find it gets in my way. None of my colleagues write SQL queries, it’s all completely abstracted away behind ActiveRecord, and we are constantly running into issues where someone forgets an `includes` and we end up with an N+1 error. N+1 errors are so easy to slip into with an ORM, it’s pretty much one of our biggest performance issues.

This seems like the biggest trend the more I use RAILs: it’s great to iterate and prototype, but as soon as you hit scale the maintainability becomes a big issue. I’m not saying other web frameworks are immune or that this is a problem that cannot be solved, it’s just all of these abstractions and cascade of configuration objects to make RAILs do what you want end up getting in the way.

A query builder is the best of both worlds: semantics that resemble raw SQL with some ability for composition.

In SQLAlchemy, you can choose to use only the query builder without using the ORM part at all. The query builder in SQLAlchemy is very well documented[1] and its use without ORM has the same level of support as its ORM counterpart. SQLAlchemy call their query builder a SQL Expression Language[2], e.g.

    # Boilerplate
    from sqlalchemy import create_engine, MetaData, Table
    engine = create_engine('sqlite:///:memory:', echo=True)
    metadata = MetaData()

    customers = Table('customers', metadata, autoload_with=engine)
    query = customers.select([customers.c.id, customers.c.fname, customers.c.lname, customers.c.phone])
    # => SELECT customers.id, customers.fname, customers.lname, customers.phone FROM customers

    conn = engine.connect()
    conn.execute(query)
    # => [(1, "Foo", "Bar", "12345678"), ...]

ActiveRecord also has Arel which can be use as a standalone. Documentation is a bit more sparse compared to SQLAlchemy Core or its ActiveRecord ORM counterpart, though.

[1]: https://docs.sqlalchemy.org/en/13/index.html

[1]: https://docs.sqlalchemy.org/en/13/core/tutorial.html

And the next day on HN people lament ORM's: https://news.ycombinator.com/item?id=26188765

Reply on news.ycombinator.com