Hacker News Reader

Comment by Cymen | Hacker News Reader

I have on all my personal projects but I wouldn't bother with an existing project unless I had a specific need that PostgreSQL met (and MySQL did not).

I'm actually all in on PostgreSQL at this point -- I'm not bothering with trying to use vanilla SQL and worrying about switching databases but I have that luxury on my side projects. PostgreSQL has a lot of interesting features that I am happy to use and I frankly never really relied on using vanilla SQL in order to be database server agnostic anyway (not saying that doesn't have a place and maybe someone will reply with how it was absolutely essential for their X but...).

To be clear, I still am all for portable SQL queries but I no longer worry about relying on a feature only PostgreSQL offers and/or taking advantage of things it offers that are not part of the standard.

View on HN

Are you talking about ORM? ORM is such a waste of time to learn as you switch to another language at one point, your time spent on ORM is gone and you don't even learn the underlying tech that is SQL.

Stick with SQL and you can keep that skill mostly the same across different SQL products. And obviously you will sooner or later hit some performance penalty when you start writing complex ORM and ORM starts spitting out SQL without thinking and then your only solution is to complain on GitHub issue as you can't solve it.

This is bad advice. An ORM is a useful tool for smoothing over the generation of queries and their conversion into object graphs, and make sense for many use cases.

It is true that using them effectively still requires knowing the underlying database technology, but that is a given - they are not meant to supplant that knowledge.

I will say though that every time I have encountered a codebase developed by a fanatically anti-ORM developer, it has contained a bug-riddled implementation of a half of an application-specific ORM anyway…

"An ORM is a useful tool for smoothing over the generation of queries and their conversion into object graphs, and make sense for many use cases."

I'm finding it's more powerful to have some language convenient query generator + query -> basic data structure + basic data structure -> final data structure broken into three separate parts, instead of bound together monolithically as most ORMs do it. Those steps are all useful on their own merits, but it's really quite frequent that for some specific task the prepackaged monolithic ORM is not what I need; either I need to tweak the query, or I want to get a basic data structure from some other source (JSON or something) and want to be able to reuse the logic, or I need a tweak to how I'm processing it down to an object (e.g., processing it into a class that is standalone vs. one that is integrated with the rest of the world, the "banana vs. a jungle containing a gorilla holding a banana" sort of thing Joe Armstrong talked about), or I want to create data structures from a complex query involving joins, aggregates, etc. that I can't necessarily express as "a class that represents a table" or something.

It's not the three tools that are the problem; it's the inflexibility of jamming them all together in one shot, along with the semantic contradictions encountered while trying to make one class be the query generator, the basic data structure representing the query, and the "final" data structure, all at once, even before we consider the affordance of the ORM that really, really encourages you to make DB structures to OO classes instead of queries.

Have you looked into sqlalchemy? I think it would meet some of your requirements; I'm not sure about the rest.

It is one of my favorites, yes, but alas, only works when I'm in Python, which for me personally is not very often. YMMV.

I'm working at three projects with three languages and three different ORMs so you have my sympathy. They're the ORMs of Django, Rails, Phoenix. Rails' got the easiest ORM to use by far, maybe because IMHO it's now the closest to SQL. Phoenix's Ecto is needlessly complicated and Django's is as verbose as Python's libraries can get. Example: Model.objects.get(), because nobody could understand Model.get(), right? /s

So, along the years I felt sometimes like I have to relearn how to do SQL in arbitrary library X, for the sake of it.

However ORMs have an advantage: no need to change queries when adding/removing fields to the database. No need to deserialize data from resultsets. The usually migrate the database either by applying migrations to the database and infer models (Rails) or syncing the db with the model (Django). Weirdly Ecto forces the developer to both write the migration and the model.

My ideal ORM would be something that lets me write

  sql("select * from employee join department
    on employee.department_id = department.id
    order by employee.id desc limit 10").each do |record|
   
    puts("#{record.employee.id}, #{record.department.name}")

I don't know how that would play with static typed languages but if all ORMs would be like that we could know only SQL and be able to perform arbitrarily complex queries.

Sounds like you don't want an ORM but a DB driver like psycopg2 http://initd.org/psycopg/docs/cursor.html#fetch

I know psycopg2 and other drivers for several languages. They're ok to make queries but they lack everything else ORMs are good at.

I want an ORM, but a smarter one and as similar as possible across languages. Instead everybody reinvented the wheel in every language, with different approaches, more or less over engineering their solution and making the polyglot experience as difficult as learning English and Russian instead of Italian, French, Portuguese, Spanish (learn one, you'll get the others quickly.)

My background is that I learned SQL almost 10 years before the first Java ORMs went mainstream. I've been using ORMs since the 90s but for writing queries they're a kind of waste: the query in the example in Rails is

  Employee.joins(:departments).
    limit(10).
    order_by("employees.id desc")

at the cost of

1) defining some relationships between the tables in the models

2) learning how to translate SQL in Ruby

ActiveRecord is magically convenient most of the time. Sometimes it's quicker to write that SQL, sometimes is not. Django (I can't remember the name of the ORM) and Ecto are nearly always slower to write than SQL, especially Ecto.

For complex queries SQL is the only way to go in any framework and the ORM is there only to deserialize the results, so why not using it for simple ones too?

  Employee.find(params["id"])

is about the limit.

> Example: Model.objects.get(), because nobody could understand Model.get(), right? /s

That has a simple explanation: each Model can have multiple Managers [1], and the `objects` is the default one. Also, by convention, methods on the Model are usually relative to "one DB record", and methods on the manager are relative to N records.

[1]: https://docs.djangoproject.com/en/2.2/topics/db/managers/

Thank you, I know now why Django inflicts that .objects method to everybody :-)

I'd rather design a less verbose API for the default manager

  Model.get()
  Model.filter()
  ...

and Model.manager.get() if one really needs custom managers.

The fact that I didn't know about managers after years of Django (and nobody told me) should be telling of me and my team, of course, but also how needed managers are. Having to go through that API without shortcuts is not nice to developers.

You're welcome. I'm probably biased because Django has been my framework of choice for more than 10 years, but I really like the distinction between methods relative to the Model and methods that act on multiple records and the `objects` thing doesn't bother me.

I've used the Django ORM professionally for 8 years. Back when I started migrations came in the form of the South app. I can remember one time where the ORM spat out SQL and that was a migration where I botched something in the index of a column. I realized immediately, fixed the error and was done with it. I never once saw anything on Sentry with an SQL error. This is anecdotal and may speak to the genius of Andrew Godwin (and others on the Django team) but it is a data point.

I used to hate ORMs, but then I found Diesel for Rust, which checks that structs match the database schema, which is really nice when building basic CRUD apps. I ditch it when doing more complex work, but for simple tasks, it saves a lot of time and catches some errors at compile time that might not be caught as quickly at runtime.

Nothing beats knowing the underlying database, but an ORM certainly has its place.

Reply on news.ycombinator.com