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

What ORMs have taught me: just learn SQL (2014)

wozniak.ca

Post reply on HN
Print view View original

371–380 of 654 posts

Re: What ORMs have taught me: just learn SQL (2014)

#371

Post by zmmmmm »

brianpan wrote: post #362
zmmmmm wrote: post #352

Writing SQL is a lot of work, not type safe (in the programming language static typing sense), often not re-usable, and hard to test. There are libraries that solve these parts for you (or at least help with them). For me the optimum is libraries that do only these parts but actively try to NOT introduce any new layer of abstraction over the database model itself. The problem is that in this whole ORM discussion, the…

I’ve had success in Java with Freemarker. It’s a templating engine so you’re just producing strings. But you can add typing; use variables, loops, and conditionals; and include other templates for sub query reuse. IntelliJ has a plug-in. It’s a nice compromise between crafting strings vs SQL DSL. As for the objects, you can get very far with everything being a Map until you really need to add a class or two. :)

Absolutely. One of the things I like about Groovy is that it solves the other half of that equation because it has the built in map constructor and built in SQL APIs that work with it. So you can actually do

    class Foo {
        Long id
        String name
        ...
    }

    Foo foo = new Foo(db.firstRow("select * from foo limit 1"))
And it all just works if the database columns match the fields of Foo. And if you just do

    Foo foo = new Foo(db.firstRow("select name from foo limit 1"))
Then you get a `foo` with only the name populated, etc, and you got type safety, easy direct, efficient SQL queries and the ability to test your code without hitting the database, all without imposing any "leaky" abstractions that cause all the problems.

Re: What ORMs have taught me: just learn SQL (2014)

#372

Post by tracker1 »

qaq wrote: post #128
Nican wrote: post #125

Earlier quoted context omitted.

It was not the first time I heard the requirement about "all data access happen through stored procedures", and I find it ludicrous. Does anyone know how such a paradigm came to exist? What problem is this solving?

Security, decoupling calling code from db schema

Don't API services do that?

Re: What ORMs have taught me: just learn SQL (2014)

#373

Post by scarface74 »

delusional wrote: post #148
wvenable wrote: post #41

Earlier quoted context omitted.

That's not true. A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships. With SQL you will also have a lot of uncheckable strings containing code. Simple tasks are done maybe thousands of times in any one application. It's the complex tasks are rare.

Yeah, your "uncheckable strings" (which could be checked by spinning up a testing database) is replaced by 700k lines of code that, although tested, is still full of bugs. The Hibernate repo is 700 thousand lines of java code! There's a tendency amongst some (especially "enterprise" programmers) to forget that dependencies are also just code. If they break, you are on the hook too. You own that complexity.

So if I am using C#, I’m trusting that Microsoft wrote their other libraries correctly. Why wouldn’t I trust Entity Framework?

Re: What ORMs have taught me: just learn SQL (2014)

#374

Post by scarface74 »

AdrianB1 wrote: post #153
kbenson wrote: post #143

Advocating for the use of SQL over an ORM in every case is like advocating for the use of Assembly over C in every case. In both cases, one is a higher level abstraction over the lower level capabilities, which can provide a quite large gain in usability and ability to easily understand what is going on at the level you are working at, for the loss of hand optimizing at a low level to get just what you want in every…

I live in a world where tables have 10^12 rows and all the queries need to be manually optimized. In my world, an ORM is the most useless thing in the world. Different people, different needs. However, in all my projects SQL was more useful than ORM, except where the data models were so simple my mom could write the code for it.

If you have one table that you’re doing a query on, what types of optimizations are you doing to the query as opposed to the database - or even if you’re doing joins?

Re: What ORMs have taught me: just learn SQL (2014)

#375

Post by kls »

JesseAldridge wrote: post #227

This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly. I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One th…

Out of curiosity what platform and tech where you using? I am making the assumption of a predominately OO one based on the virtues of ORM. I have always found that when I try to solution back end or middleware based platforms with OO dominate languages (read Java, C#, et. al.) that there quickly becomes an impedance mismatch and any communication with the database becomes a monster of mapping OO philosophy to relational theory, whether that be via home rolled or an ORM.

That being said, I personally have found that I do not like OO languages for back end dev and I find that functional languages such as any variety of LISP marry extremely well to the transnational and process oriented nature of back-end systems as well as lend themselves to not having to jump thru hoops to contort the data into relational sets (Clojure's destructuring is an absolute life saver here). I find that there is little to no duplication of code in regards to transferring data to the db. You may want to give Clojure or F# (depending on your stack) a try for your back end and see if it does not alleviate a host of issues with trying to develop a process and transaction oriented system, which most back ends fit that definition.

I find the converse to be true for the front end. I find most attempts to deal with the UI in anything other than objects and components (read jQuery, React Hooks), turns to spaghetti rather quickly.

If you are using OO languages to communicate and transfer data to the DB you may very well be trying to solution for the impedance mismatch that is easily solved by using a functional language.

Re: What ORMs have taught me: just learn SQL (2014)

#377

Post by scarface74 »

takeda wrote: post #257
dillonmckay wrote: post #168

Earlier quoted context omitted.

So, SQL is the shovel, correct?

The typical language people use is imperative language where you state how to do something, while sql is a declarative language where you say what you want. Since we are in the world of analogies, using an ORM is like taking a shovel and using it as a prop (without speaking) to explain excavator operator where to dig, how deep, how wide, what things to avoid etc. Except querying a database can be much more complicate…

How is this standard C# code to work over a list:

  var males = from p in context.Person where p.Sex = “M” select c;
Any more imperative whether context represents a C# object or a database?

Re: What ORMs have taught me: just learn SQL (2014)

#378

Post by vips7L »

philwelch wrote: post #308
erik_seaberg wrote: post #301

Earlier quoted context omitted.

A lot of people who believe only one app (or one language) accesses their org's datastore are mistaken. You have to take extreme measures to prevent ad hoc uses from popping up.

Yes, yes, yes. Why is this the case? 1. If you are doing anything interesting, people are going to ask questions about what you are doing, and the best way to answer those questions is going to be by querying your database. 2. One day you might want to rewrite some of your service/s, split them into microservice/s, etc. At that point, there will be a minimum of two services talking to your datastore: the legacy servi…

The simple solution to 1 is to never allow direct database access. Api only.

Re: What ORMs have taught me: just learn SQL (2014)

#379

Post by sjwright »

chipotle_coyote wrote: post #248
sjwright wrote: post #233

Earlier quoted context omitted.

Even more advanced programmer: writing the ‘boilerplate’ queries out manually takes barely more time than composing them in an ORM, means less indirection, saves me a major dependency, and encourages me to think intelligently about each query no matter how boilerplate they might seem at the surface. Super-advanced programmer: allowing my database structure to be influenced by the needs of an off-the-shelf ORM will ma…

Programmer without anything in particular to prove: hey, I already know SQL, and ORMs create queries "under the hood" in ways I can reason about and control, so I'm going to use this ORM in a way that doesn't involve just throwing objects and tables together in a big pile and mooshing them all together with duct tape.

One of the biggest reasons why I don't use ORMs is because I try to avoid using objects at all unless there's a really good reason to do so. And when I forget to follow that principle it has always turned out to be a mistake; de-objectifying has consistently resulted in simpler, shorter code with fewer data bugs.

My working principle is to have data spend as little time as possible being thrown around within application code. I tend to find that the longer data spends being sieved through layers and tossed around inside your application, the more data bugs you'll end up having.

And when it comes time to display data to the user, it's rarely inconvenient to write an SQL query that fetches exactly what you want to display in exactly the right format and exactly the right order—obviating the need to have any "objects" that "understand" your data model.

The problem is that far too few programmers realise how deep the SQL rabbit hole goes; it's treated like a little side-hustle like regular expressions, when for so many programmers it's the most valuable skill to level up.

Re: What ORMs have taught me: just learn SQL (2014)

#380

Post by inimino »

rmilejczz wrote: post #299
dymk wrote: post #246

Earlier quoted context omitted.

But you shouldn’t live with a hand-rolled pseudo ORM that stumbled into existence when there’s developed alternatives

An in house solution is almost always better than an external dependency

This is correct. An in-house solution is a solution developed in-house for your specific problem, which no one else has ever had exactly. The more specific the need, the more the benefit of the made-to-measure solution. The alternatives are something your organization didn't develop, which may be better, but you don't know how to use it, or may be worse, but you don't know that when you pick it, or may be slower, but you don't know that when you start using it, or may have vendor lock in, but you don't know that when they sell it to you as "open", or may have hidden pitfalls, but they aren't in the glossy brochure, or may be unmaintained by anyone except your org in ten years, but you can't know that until ten years from now, or may be full of security holes because it was developed by idiots, but you can't know that because you didn't see who wrote it, or might be full of solid security features and a great design cleverly compromised by a hidden flaw placed in a specification you haven't read by a nation state, but you don't see that because why would you, or... etc etc etc. But don't worry, at least you didn't have to understand the problem space well enough to be able to sit down and solve it yourself, so you sure saved some effort there!
Post reply on HN