What ORMs have taught me: just learn SQL (2014) • Hacker News Board

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

#461

Post by yxhuvud » Sat, Sep 21, 2019, 6:52 AM UTC

jasonkester wrote: post #450

I don't know. I've been building database backed stuff for 25 odd years now, and I've never experienced this Object/Relational Impedance Mismatch that everybody talks about in any of my designs. I sometimes wonder if it's just the approach I take that has ended up dodging that bullet somehow. My initial design is always done in the database. Whether it's a little feature or a green field new project on a blank sheet…

Having migrations for updating the schema makes sense even if the schema is raw sql and the migrations also is that. What it does is that it enables having multiple environments without the headache of having to keep them in synch.

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

#462

Post by spion » Sat, Sep 21, 2019, 6:59 AM UTC

jasonkester wrote: post #459

spion wrote: post #457

Earlier quoted context omitted.

So if you have to change the schema afterwards (with lots of data already in the table), what do you do?

Change the schema, and run a data migration script if necessary. And, of course, check the change script into source control so that your build system knows to run it on the staging and prod dbs when the time comes. Then simply build your project and fix any compile-time errors that arrived when the base classes were all blown away and rewritten. Extra points for keeping your column names in string enumerations so yo…

I believe you've just described migrations (scripts that modify the schema and run data migrations, checked into source control).

Generating the ORM layer from the database schema seems OK to me. It does preclude generalizing certain subsets of the schema like e.g. lets say you want to publish a "comments module" reusable across projects that would install its own subset of tables in the DB, as well as provide functions (procedures) to create new tables that link comments to other entities on demand.

The problem with relational databases is that SQL has such poor facilities for abstractions. Whereas the typical language today has higher order functions (some even have higher order classes!) stored procedures are quite limited.

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

#463

Post by moring » Sat, Sep 21, 2019, 7:02 AM UTC

mumblemumble wrote: post #234

725686 wrote: post #37

Each time I see someone complain about ORMs I remember Greenspun's tenth rule[1], which adapted to ORM would be: "Any sufficiently complicated program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM." ORMs are hard for a reason. Using an ORM doesn't mean you can't or shouldn't use plain SQL where the situation calls for it. You can mix and match perfectly fine. [1] ht…

I think that this is true if you're writing your application in a way that requires object-relational mapping in the first place. And that's only necessary when you're trying to manage your data in the application in an object-oriented way. And managing your data in an object-oriented way implies more than just the simple fact of defining classes to serve as data records. Those classes can be entirely equivalent to a…

Two counter-points:

(1) While treating a single SQL table as a distinct entity is a sin by your argument, I would argue that treating a set of tables as a distinct entity is not. I'm referring to what DDD calls an aggregate, e.g. a customer (address + email history + scoring + whatever tables), or an order (line items, shipping info, payment info, ...) Reading the comments here, I'm starting to wonder if some kind of holy grail or at least useful approach lies in there.

(2) The impedance mismatch has two ends, and one might as well argue that the problem is not mapping tabular data to objects, but mapping objects to tables. This might be an underrated benefit of what is usually called "NoSQL", dwarfed by the whole discussion around "schemaless". Unfortunately, I don't have any experience in that area, but it's something I have long planned on trying. Note that I'm not trying to say that NoSQL "solves the impedance mismatch" but rather that, in DDD's approach to solve persistence, NoSQL might actually be able to do what DDD wants (e.g. with respect to defining a "unit of consistency"), unlike tabular data.

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

#464

Post by ne01 » Sat, Sep 21, 2019, 7:13 AM UTC

It’s like advising someone with an arrow in his heart to not smoke—who cares, he has a few moments left anyways.

OOP is enough of complexity to kill most projects—and yet we discuss the dangers of ORM.

“Object-oriented programming offers a sustainable way to write spaghetti code.” -PG

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

#465

Post by SomeOldThrow » Sat, Sep 21, 2019, 7:16 AM UTC

725686 wrote: post #37

Each time I see someone complain about ORMs I remember Greenspun's tenth rule[1], which adapted to ORM would be: "Any sufficiently complicated program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM." ORMs are hard for a reason. Using an ORM doesn't mean you can't or shouldn't use plain SQL where the situation calls for it. You can mix and match perfectly fine. [1] ht…

What is a decent ORM under this definition? I don’t think I’ve ever seen one sufficient for business logic.

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

#466

Post by qaq » Sat, Sep 21, 2019, 7:16 AM UTC

A good alternative is libs like https://pugsql.org/

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

#467

Post by gmac » Sat, Sep 21, 2019, 7:18 AM UTC

kccqzy wrote: post #439

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…

There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter is what I don't like about ORMs but the former is fine. I feel that this is an important distinction to make. As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you con…

Agreed. Helpers (and indeed types) can make working with SQL an actual pleasure. You do need to learn the SQL, though. (My TypeScript/Postgres solution, in this vein: https://github.com/jawj/mostly-ormless/blob/master/README.md).

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

#468

Post by headmelted » Sat, Sep 21, 2019, 7:18 AM UTC

In these cases, I’ve elected to write queries using a templating system and describe the tables using the ORM. I get the convenience of an application level description of the table with direct use of SQL. It’s a lot less trouble than anything else I’ve used so far.

I was with the spirit of the article save for this.

Recently I've been developing some work for one of my client's in the .NET world. There had been ongoing discussions about developers wanting to use Entity Framework as an ORM vs. using stored procedures.

The client already uses SQL projects and DACPACs (effectively a system for specifying the DB in SQL then diffing it between versions to alter the database).

The arguments for and against ORM were largely naive from each side of the fence - the developers wanted strong typing (doesn't require an ORM) while the DBAs wanted to be able to review query execution and suggest changes if there's an issue (you can see the generated SQL for the ORM).

The solution I came up with was to use reflection on the build server as part of the CD process to map the inputs and outputs of the stored procedures into strongly-typed C# methods and objects, generate code for it, and build/pack/push a nuget package back to our feed. It's similar to what EF provides without maintaining the EDMX (which we can do as we don't need to cover every possible data access scenario like EF does). It means that once the database project is checked and the build green-lights, an updated Nuget package that constitutes the DAL is automatically waiting on the internal Nuget feed.

I've found it gives us the best of both worlds. We can do anything we need to in t-SQL and the C# wrapper only cares about the ultimate input/output. I can force the use of parameter sanitization in the wrapper (by not providing any other way to call the procs), and the DBAs can review/amend whatever they want without the developers needing to change their code as the interfaces don't break.

We also don't have to write DAL boilerplate or worry about inexperienced developers getting it wrong and opening injection attack surfaces at the DAL layer.

There's usually a solution to your use case if you look for it is my point.

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

#469

Post by speedplane » Sat, Sep 21, 2019, 7:26 AM UTC

I'm okay with ORMs, but I can't stand lazy evaluation. It often leads to situations where it's entirely unclear when the program is making a database call.

For example, given the small django program:

  dbcall = models.Purchases.objects.filter(amount = 100)
  if dbcall:
    do_something()
    if len(dbcall) > 5:
      do_something_else()
    third_thing(dbcall[0])

Does the above app make 1, 2 or 3 database requests? There is an answer, but it's not at all clear to the developer.

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

#470

Post by jmnicolas » Sat, Sep 21, 2019, 7:28 AM UTC

I learned SQL at school. When I was hired and suggested to use an ORM, the boss didn't know what an ORM was, so he said no.

I have no problem with SQL, I actually like the language it's very powerful. However mapping query results to objects is soul crushing.

So a few year ago when I became the sole dev in the company, I decided I would try Entity Framework (Microsoft .net ORM)on a new project that needed to be delivered yesterday.

To get even more speed I didn't even design the database as I do usually, I went with the code first approach. It was magical, so much less work to do.

Then something went wrong with Entity Framework it didn't retrieve the right data I think (sorry I don't remember what exactly, but it was a show stopper) and I couldn't find a solution to my problem online.

Then I had to do everything by hand in a rush, I was very late with the project.

I kind of lost interest in ORMs after that but I thought there should be a way to map queries to objects. So I started to develop my own micro ORM with Tuples. I went nowhere fast but while I was searching I discovered that what I wanted to do already existed and was actually named a micro ORM.

Now I use Dapper (Stackoverflow .net micro ORM) and I'm very satisfied with it. Its level of magic is relatively low, so if it doesn't work I can quickly replace it with a manual query without losing much time.