Hacker News Reader

Comment by henning | Hacker News Reader

I wish people did not think the choice was solely between "write raw SQL with raw strings" and "try to pretend the database is object-oriented when it is not."

The third approach is to safely wrap the database and its columns with code in a way that is composable.

In Python, SQLAlchemy has an ORM, but it is optional, and you can just work with tables and columns if you want.

View on HN

The real third approach is that you can safely pretend the database is object-oriented for manipulation and simple lists and still use SQL for complex queries. Most ORMs let you safely mix and match both methods easily.

This ORM or not ORM is the wrong question. Use an ORM to save you headaches where it's appropriate and use direct SQL when it's not.

I use this approach with Mongoose in Node.JS. If you want to update a user document, we always query the entire document, set the field, and call .save(). This triggers all kinds of very useful validation hooks and is easy to think about. But if you want to find a series of users, or build an API for a specific front-end form, I've found the ORM just gets in the way, and we have not had any trouble writing db queries inline.

We used to use Mongoose in our project[0], but found that at the volumes of interactions we were dealing with, it was orders of magnitude faster to just use the raw Node.JS driver.

[0]: https://github.com/coralproject/talk/tree/next

You are absolutely correct. Using both is a very valid options.

We use ActiveRecord a lot, and then have custom SQL queries using `find_by_sql` for very complex, optimized joins. It works very well. Rails gets out of the way when we need it to.

Two caveats with find_by_sql: it’s read-only, so no insert or update commands, and it still does column-to-instance-variable monkeypatching on the object level, as opposed to the class-level monkeypatching that’s applied to normal ActiveRecord classes as soon as the DB schema is read.

For the former, there's always ActiveRecord::Base.connection.execute. For the latter, I think it's more complicated than that. There also is object-level mapping even for regular AR usage. If you do something like Foo.select("true as bar"), your Foo objects will have a bar variable available to them.

This is true.

> The third approach is to safely wrap the database and its columns with code in a way that is composable.

Like Haskell's Esqueleto[1] which lets you work with SQL code as Haskell functions and values. As an example from the documentation:

  select $ from $ \p -> do
    where_ (p ^. PersonAge >=. just (val 18))
    return p

results in roughly:

  SELECT * FROM Person
    WHERE Person.age >= 18

This lets you define new functions to use in your queries that use SQL functions. For example, you can define `isPrefixOf` for SQL using the SQL functions `char_length()` and `left()`. The use of the function will be expanded into the corresponding SQL code when the query runs. Like,

  select $ from $ \p -> do
    where_ $ val "John" `isPrefixOfE` p ^. PersonName
    return p

could result in:

  SELECT * FROM Person
    WHERE LEFT(Person.name, CHAR_LENGTH('John')) = 'John'

[1] https://www.stackage.org/haddock/nightly-2019-05-07/esquelet...

Would something like Python "records" ever get traction? Just write SQL.

https://github.com/kennethreitz/records

Dapper is similar and it got some traction in the .NET community.

https://github.com/StackExchange/Dapper

At Metabase I think we've found a nice middle ground with our "Toucan" library for Clojure: https://github.com/metabase/toucan

For a long time these were the main choices and the third way had very little library support and generally required you to role your own, which were generally crappy and inefficient. Even when "lightweight ORM's" were first gaining traction their composability was quite poor (and some ways it still is) and would often leave the denormalisation part to the application.

Even though I think this third way should be the "default" way to write an application now there's an awful lot of code left over from when an ORM was better.

There are so many incompatible needs for databases that most DB wrappers of any kind rarely make sense except when prototyping. And database wrapper authors have a tendency to cater to lowest common denominator of features so that they can treat all databases the same, which is going even further in the wrong direction. Choosing a database requires learning what your requirements are, learning what the candidates are, and when you pick one, learning how to use it correctly. People and companies rarely seem to do all of these steps.

The "write-raw-SQL-with-raw-strings" approach has one serious issue -- sql-injections. Some people argue that it is not so hard to filter strings before concatenating them into sql-query, but I know also people who argue that it is not so hard to write C code and to not introduce bugs around NULL and wild-pointers, one just needs to be careful. I, personally, do not believe that strategy "be careful" can work reliably here.

I cannot imagine work with SQL without query builder, which introduces type checks and conversions, and escapes strings when needed. ORM is an optional thing, but it is nice to have some layer that maps rows into structs and vice-versa. With dynamically typed scripts it doesn't matter: in any case you would get a hashtable (the only difference is a syntax used), but with compiled language and static typing I feel some uneasiness when using slow hash table mapping instead of blazingly fast struct field access. And the tooling can help to declare that structs statically.

> The "write-raw-SQL-with-raw-strings" approach has one serious issue -- sql-injections.

Nobody has advocated writing "raw SQL with raw strings" in years.

The valid way of using Raw SQL is using prepared statements and parametrized queries.

This method will protect you from SQL injection, will handle most issues with type/conversions and the queries are cacheable, so it's fast too.

Parametrization is handled by the database itself (not the specific driver), so it is battle tested.

https://stackoverflow.com/questions/8263371/how-can-prepared...

> Nobody has advocated writing "raw SQL with raw strings" in years.

It is an overstatement. Every time I look into some random PHP code I see there raw SQL with raw strings. Maybe it is just me being "lucky"?

By the way, the thread starter comment was mentioned it, I got phrase from it.

“Random PHP code” might be the operative phrase there.

Reply on news.ycombinator.com