Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

To ORM or Not to ORM

eli.thegreenplace.net

Post reply on HN
Print view View original

201–210 of 300 posts

Re: To ORM or Not to ORM

#201

Post by SOLAR_FIELDS »

ozim wrote: post #111
bunderbunder wrote: post #97

I've come to a couple conclusions, over the years. First, when you get down to it, the most-valued feature of ORMs is not the "writing queries in some language other than SQL" feature, it's the "not having to write a mess of mapping code" feature. Second, the biggest drawbacks to ORMs all derive from the "writing queries in some language other than SQL" feature. Fortunately, there are tools out there that solve the "…

Actually "writing queries in some language other than SQL" which has static typing and catching issues in compile time is quite big for me. Add automatic database migrations that are also keeping types in line with code and whole bunch of "mess of mapping code" goes away. Though I use .NET EntityFramework which by now is really mature and heavily invested into by MS. Not sure how it is with other environments but I t…

Lack of adequate support for versioning your schema and in general bad support for anything versioning with RDBMS is one of the main dislikes I have for SQL. I find SQL to be a beautifully expressive language when you get the hang of it, but no RDBMS that I know of has been able to adequately tackle the versioning problem at both the schema and the script level. Everyone's schema evolves over time, why is it so difficult for the RDBMS to try to capture the idea of database evolutions? This is the single biggest selling point for me in ORM land, because with regular (read: non-SQL) code you have all the typical version control mechanisms to help you manage changes to the schema. It's still not ideal, but a lot better than trying to manage this kind of thing in any of the tools that mainstream RDBMS have.

Re: To ORM or Not to ORM

#202

Post by dinkleberg »

Grollicus wrote: post #117

I'm surprised with so many mentions of Django nobody mentioned the migration system. I've hand-written sql (the good old days of mysql_real_escape_string) and I've used some ORMs. Django stands out because of their code first-approach for models: You define models in Python and they generate migrations based on that. That makes updating very easy and also quite robust, as a simple makemigrations call in the CI ensure…

I'm in total agreement with you. I've tried different ORMs in different languages and I am always disappointed in how lackluster they feel in comparison to the Django ORM.

There are several things that bother me about using Django, but because of the ORM and it's tight integration with the rest of the system I don't think there is a more productive environment for me.

That being said I'm not a great developer. As a business-minded person who codes out of necessity, I find the Django tooling to be the right way to work most of the time.

Not having to deal with migrations is a thing of beauty.

Re: To ORM or Not to ORM

#203

Post by ccleve »

bunderbunder wrote: post #97

I've come to a couple conclusions, over the years. First, when you get down to it, the most-valued feature of ORMs is not the "writing queries in some language other than SQL" feature, it's the "not having to write a mess of mapping code" feature. Second, the biggest drawbacks to ORMs all derive from the "writing queries in some language other than SQL" feature. Fortunately, there are tools out there that solve the "…

Agree. That's the approach I took with Norm (https://github.com/dieselpoint/norm). The general idea is that I just wanted to get rid of JDBC boilerplate and use plain Java objects, but not stop using SQL. Being lightweight is the key.

Re: To ORM or Not to ORM

#204

Post by sigstoat »

strokirk wrote: post #182
GordonS wrote: post #178

Earlier quoted context omitted.

I don't like to say "you're doing it wrong", but TBH it sounds like you are. > restricted to SQL structures So is your ORM; it just adds a layer of abstraction > require absurd hacks or custom dlls Literally no idea what you're doing that views require anything out of the ordinary, or indeed "custom DLLs" > not properly source controlled Eh? You can store your view DDL files in source control just fine. I mean, they'…

What SQL GUIs do you use that draws graphs for EXPLAIN statements? The ones I've used for OSX (PopSQL, Sequel Pro, TablePlus) hasn't had that feature. Haven't used MySQL Workbench in a few years, so not sure about that one.

pgadmin? https://www.pgadmin.org/screenshots/#9

Re: To ORM or Not to ORM

#205

Post by taffer »

For a toy project I used Postgres to generate JSON objects for me and a helper function in ruby (db_helper) that parses the JSON into ruby data structures so that the OP example can be written with much less boilerplate code as:

  def db_all_posts_in_tag(tag_id)
    db_helper("
      SELECT json_agg(r)
        FROM (
              SELECT post.post_id, post.published, post.title, post.content
                FROM post
                     INNER JOIN post_tag ON post.post_id = post_tag.post_id
               WHERE post_tag.tag_id = $1
             ) AS r;
    ", [tag_id])
  end

Re: To ORM or Not to ORM

#206

Post by derefr »

spion wrote: post #194
bunderbunder wrote: post #97

I've come to a couple conclusions, over the years. First, when you get down to it, the most-valued feature of ORMs is not the "writing queries in some language other than SQL" feature, it's the "not having to write a mess of mapping code" feature. Second, the biggest drawbacks to ORMs all derive from the "writing queries in some language other than SQL" feature. Fortunately, there are tools out there that solve the "…

I've come to the exact opposite conclusion. The query DSL for ORMs is fine, works well in a large majority of the situations. When it gets problematic, you can almost always drop down to pure SQL. The mapping part of the ORM on the other hand is a disaster. Table rows make poor OO classes. The best OO classes are "workers" that have some concrete task at hand. "Active record" style classes have no scope whatsoever -…

So don't map DB records to objects, then. DB records are records, and their proper typing in your business logic is as records—chunks of plain old data, strongly-typed, that your (OO or otherwise) code can declare DB-side interfaces against. The only responsibilities of the module/class that owns the record type, should be getting things converted into and out of that record type.

Plain-old-data DB record types (and their respective owner modules) are to ORMs, as code-gen'ed wire record types (and their respective owner modules) are to ProtoBuf-like wire-format codec libraries. They're the containers you put data into and take it out of, to ensure it's in the right types when being sent to, or received from, your DB. No more than that.

And, corollary to this idea: the POD record-type MyDB.FooTable shouldn't be taken as a type to use to stuff in the results from any random query you run against the DB table `foo`. It should be taken to represent the specific row type that the DB table `foo` has—the one you get when you `SELECT * from foo`. If you do an SQL query, and the result of that SQL query has fewer or extra or differently-typed columns, then the resulting row-set will have a different record type, and so you should have a separate POD data-type that does match these records. (Example: if you join two tables with a query, you don't need the ORM to spit out two records objects with a fancy OO-style relationship between them; you just need one record type that represents the resulting joined rows, without any particular idea that it happens to be an amalgamation of data from two tables.)

For an example of this approach to ORM, see Elixir's https://github.com/elixir-ecto/ecto.

Re: To ORM or Not to ORM

#207

Post by barking »

Sorry for being off topic but is there an ORM or any other tool that can rewrite messy SQL, ideally replacing right joins with left ones at the same time? I have several hundred of these that I am afraid to touch.

Re: To ORM or Not to ORM

#208

Post by spion »

derefr wrote: post #206
spion wrote: post #194

Earlier quoted context omitted.

I've come to the exact opposite conclusion. The query DSL for ORMs is fine, works well in a large majority of the situations. When it gets problematic, you can almost always drop down to pure SQL. The mapping part of the ORM on the other hand is a disaster. Table rows make poor OO classes. The best OO classes are "workers" that have some concrete task at hand. "Active record" style classes have no scope whatsoever -…

So don't map DB records to objects, then. DB records are records , and their proper typing in your business logic is as records —chunks of plain old data, strongly-typed, that your (OO or otherwise) code can declare DB-side interfaces against. The only responsibilities of the module/class that owns the record type, should be getting things converted into and out of that record type. Plain-old-data DB record types (an…

Would that be a "RRM" (Record-Relational mapper)? If so we're already on board (in a different language).

P.S. I bet you would never guess which typed language with great support for records makes it easy (for the most part) to build most type-safe SQL queries on the fly, even with projections, without explicitly defining types for every possible projection variation.

Re: To ORM or Not to ORM

#209

Post by karmakaze »

bunderbunder wrote: post #97

I've come to a couple conclusions, over the years. First, when you get down to it, the most-valued feature of ORMs is not the "writing queries in some language other than SQL" feature, it's the "not having to write a mess of mapping code" feature. Second, the biggest drawbacks to ORMs all derive from the "writing queries in some language other than SQL" feature. Fortunately, there are tools out there that solve the "…

I've come to the same conclusion from using a number of query and relation mapping frameworks (e.g. Rails, Spring). I've finally decided to write one that solves the problems that I have with them. It comes down to just a few things:

  1. Embrace SQL
  2. Handle the ON clauses for JOINs
  3. Fetch relations of sets of records (eager or lazy at the call site)
     in batches without making N+1 queries
  4. (bonus) async composability
I decided to make it in Java so it works with any language in the ecosystem and having a simple type system will make it easy to port to any others. Don't mind all the various typed expressions, you can just pass in an SQL string with locally resolved bindings then use that element in larger queries then finally execute the resulting tree that maps to the result type. I just started last week and I'd love any feedback like if you think it's a good idea or any specifics. I'm using JDBI underneath which is a good level of abstraction to complement it.

Edit (you might want the link): https://github.com/karmakaze/safeql

Re: To ORM or Not to ORM

#210

Post by thymanl23 »

The desire for the object mapping (to pure objects, not db-aware objects), while having an aversion to query building APIs led me to use https://github.com/craigmichaelmartin/pure-orm - where native, unobstructed SQL is written in a "data access layer", which returns pure "business objects" to be used in the app's business layer.
Post reply on HN