5 ms·

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

bagol 6y ago
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.
oblio 6y ago
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.
- stilisstuk 6y ago
  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)
  - ergo14 6y ago
    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.
    - stilisstuk 6y ago
      Yes..i have concatenated SQL i must admit.
scrollaway 6y ago
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.
evgen 6y ago
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.
Hamuko 6y ago
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.
baq 6y ago
Raw sql is not composable.
- bityard 6y ago
  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 https://github.com/kayak/pypika
  - nrmitchi 6y ago
    I'll just say that SQLAlchemy can also be used effectively as just a query builder in places that you need it.
  - stilisstuk 6y ago
    Thanks
  - qudat 6y ago
    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.
  - sirn 6y ago
    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 https://docs.sqlalchemy.org/en/13/index.html [1]: https://docs.sqlalchemy.org/en/13/core/tutorial.html https://docs.sqlalchemy.org/en/13/core/tutorial.html
asah 6y ago
For record/object-at-a-time, an ORM saves tons of code. SQL's design is optimized for processing unordered sets of records.
nodamage 6y ago
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?
- cooperadymas 6y ago
  Usually by writing their own lightweight ORM as part of their application. ¯\_(ツ)_/¯
- stilisstuk 6y ago
  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.
  - nodamage 6y ago
    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?
stilisstuk 6y ago
And the next day on HN people lament ORM's: https://news.ycombinator.com/item?id=26188765 https://news.ycombinator.com/item?id=26188765

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.

oblio 6y ago

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.

stilisstuk 6y ago
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)
- ergo14 6y ago
  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.
  - stilisstuk 6y ago
    Yes..i have concatenated SQL i must admit.

scrollaway 6y ago

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.

evgen 6y ago

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.

Hamuko 6y ago

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.

baq 6y ago

Raw sql is not composable.

bityard 6y ago
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 https://github.com/kayak/pypika
- nrmitchi 6y ago
  I'll just say that SQLAlchemy can also be used effectively as just a query builder in places that you need it.
- stilisstuk 6y ago
  Thanks
- qudat 6y ago
  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.
- sirn 6y ago
  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 https://docs.sqlalchemy.org/en/13/index.html [1]: https://docs.sqlalchemy.org/en/13/core/tutorial.html https://docs.sqlalchemy.org/en/13/core/tutorial.html

asah 6y ago

For record/object-at-a-time, an ORM saves tons of code. SQL's design is optimized for processing unordered sets of records.

nodamage 6y ago

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?

cooperadymas 6y ago
Usually by writing their own lightweight ORM as part of their application. ¯\_(ツ)_/¯
stilisstuk 6y ago
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.
- nodamage 6y ago
  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?

stilisstuk 6y ago

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