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

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

#561

Post by seunosewa » Sat, Sep 21, 2019, 1:02 PM UTC

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…

> I started coding some simple functions to help map the tabular data to objects.

Maybe you should not do that? Can you give us an idea of the domain problem you were trying to solve that made you feel the need for that?

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

#562

Post by mst » Sat, Sep 21, 2019, 1:19 PM UTC

sjwright wrote: post #367

mst wrote: post #294

Earlier quoted context omitted.

If your ORM influences your database structure, either your ORM is shit or you don't know how to use it yet. ... or both. Both is always a possibility. Welcome to programming.

If the object reasoning inherent in ORM design isn’t influencing your structure, either your structures are trivial or you don’t know how to use the full capabilities of your engine yet. ... or both. Both is always a possibility. Welcome to databases.

If the object part is influencing the relational part, either the ORM designer hasn't provided sufficient 'mapper' features or you haven't found them yet.

I agree that most ORMs are shit, and if you want to make specific complaints, I'll probably agree with most of them.

But if the choice of ORM is forcing you to design your database to its limitations, you should really be asking yourself whether it's time to switch to a different ORM.

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

#563

Post by mst » Sat, Sep 21, 2019, 1:24 PM UTC

sjwright wrote: post #398

oefrha wrote: post #387

Earlier quoted context omitted.

Most perfectly viable database schemas in the real world are trivial by your definition of trivial. Trivial designs aren’t necessarily bad designs; sometimes quite the opposite. Thanks for the condescension though.

I don't disagree with anything you've said. Though I might raise a very minor objection to the unspoken implication that the "most" database schemas which are currently trivial should be trivial. You're right that trivial designs are very often preferable. But I would hasten to add that when the application calls for data correctness, a bias towards triviality can occasionally manifest as a trade-off between complexi…

Personally, I find that when I run into a problem while programming and ask myself "is this library being stupid or am I?" it's rather useful to remember that "both" is always a possibility.

Of course, it's the internet, so dry british cynicism and condescension aren't as trivially distinguishable as one might hope. Sorry my tone didn't come across correctly.

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

#564

Post by mst » Sat, Sep 21, 2019, 1:26 PM UTC

misterdoubt wrote: post #344

mst wrote: post #290

Earlier quoted context omitted.

An ORMish thing that considers its primary purpose to enable metaprogramming SQL is actually useful. In http://p3rl.org/DBIx::Class perl has had such a thing for over a decade now. It makes me cry that nobody's ever adequately cloned it into other languages. Eventually I'll probably do so myself.

I guess I don't know enough Perl to understand how this differs dramatically from something like SQLAlchemy in Python.

SQLAlchemy is pretty close. Been quite a few years since I've had a chance to get drunk with the authors and compare notes though, so I'm not going to try and get into details because I'm pretty much guaranteed to get some of them wrong.

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

#565

Post by anthony_doan » Sat, Sep 21, 2019, 1:28 PM UTC

You should learn SQL first then ORM afterward.

Most modern ORM have escape hatch to let you write raw SQL.

Your favorite web dev language will have a dominant ORM. C#, Python, Elixir, Ruby all have a popular ORM that works within a popular framework.

ORM will make working with database and web framework easier.

I do agree with post that querying in ORM can be hard and sometime not possible without raw SQL. Writing query for Ecto, Elixir's most popular ORM, can be tricky when you want to do dynamic query.

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

#566

Post by mst » Sat, Sep 21, 2019, 1:30 PM UTC

weberc2 wrote: post #325

mst wrote: post #290

Earlier quoted context omitted.

An ORMish thing that considers its primary purpose to enable metaprogramming SQL is actually useful. In http://p3rl.org/DBIx::Class perl has had such a thing for over a decade now. It makes me cry that nobody's ever adequately cloned it into other languages. Eventually I'll probably do so myself.

Isn’t that just a query builder? I’m not familiar with Perl or your library, so maybe you could tell me where I’m wrong?

What I was trying (and evidently failing) to say is that a sufficiently powerful query generation system that's designed for people who actually like their database to be able to generate the exact SQL they would've written by hand is essential to the 'mapper' part of the equation being able to smooth out any impedence mismatches between the appropriate object model and the appropriate database schema.

Hopefully that longer answer is a bit clearer than my first attempt.

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

#567

Post by stickfigure » Sat, Sep 21, 2019, 1:50 PM UTC

taffer wrote: post #551

shkkmo wrote: post #404

Earlier quoted context omitted.

> Hibernate and JPA encourage designing your domain classes first and then generate the DDL from that. This is a feature, not requirement or need of the ORM. It seems pretty silly to let the existence of a feature prevent you from making designing the structure of your DB correctly. > does the ORM something with JSON that Postgres cannot do? Postgres's json functionality is used for manipulating and querying data sto…

> I believe the poster is talking about deserializing and validating json from REST requests and serializing json for REST responses using the mapping defined for the ORM. These are also things that the json functionality of Postgres can do. For example, look at to_json and json_agg.

In my current company I use Postgres JSONB with Hibernate extensively. One of the benefits of the ORM is that json fields can be constrained to a fixed schema. For example, a tag list field can be a SortedSet of strings.

A couple other things I've learned:

* Never re-use complex types in both your API and your schema. These things evolve at different paces and you should never have to worry that a change to your schema will break an API (or vice-versa). The minimal extra typing to have dedicated API types is well worth it.

* Storing untrusted client-submitted JSON in your database is a terrible idea. This is a great attack surface, either by DOSing your system with large blobs or by guessing keys that might have meaning in the future.

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

#568

Post by fuzz4lyfe » Sat, Sep 21, 2019, 1:59 PM UTC

fastball wrote: post #560

fuzz4lyfe wrote: post #523

Earlier quoted context omitted.

I validate at the point of use/when it matters. For example if I'm going to use a value in a query, because I'm using parameterized queries the type conversion to string happens implicitly so type doesn't actually matter. If I get 2 or '2' it all ends up as '2' and the database infers type by the column type. If I need something to be a integer and I don't trust the upstream system then you have to: int(*number*) At…

I gotcha. I work on a lot of user-facing stuff, so if the type is somehow wrong, I generally prefer to let the user know, which is a lot easier with models.

That is sensible and is generally the approach I take in user facing code. When I have my way I have validators on all input fields in the UI to warn users of invalid input. I like them to be very specific, checking to see if a phone number is formatted in such a way that it is usable by Twillo for example.

On the other hand services that aren't actively utilizing data I write to be fairly agnostic about that data. "be conservative in what you do, be liberal in what you accept from others." is sort of how I aim.

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

#569

Post by Daishiman » Sat, Sep 21, 2019, 2:00 PM UTC

pknopf wrote: post #269

Daishiman wrote: post #210

Earlier quoted context omitted.

My "toy" project with several hundred tables and tens of thousands of users does fine. I have, at most, a couple dozen "complex" queries in this project. Whereas I have an order of magnitude more queries that need to be composed from several different query criteria, a task for which SQL is very poorly optimized for and most ORMs excel at. I have used my ORM for so long that writing a report in SQL or the ORM languag…

Your "toy project" doesn't have 10+ team members, ranging in experience.

Yes it does.

Seriously using an ORM is just not hard. Especially if actually do code reviews with your junior team members, which you should always be doing.

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

#570

Post by mannykannot » Sat, Sep 21, 2019, 2:03 PM UTC

Avamander wrote: post #341

tomtomtom777 wrote: post #236

Earlier quoted context omitted.

What you are saying exactly highlights what the author is missing: That if your application has some logic, you will eventually have to map your database rows to your in memory typed structures. It sucks. Sometimes it sucks less if you map your queries as well, sometimes it sucks less if you stick to SQL and only map your results, sometimes it sucks so much you're better off with a no-sql solution. But when using a r…

Um, PL/PGSQL and et al for stored procedures exist, OOP is optional.

OOP is optional, but if your program has any concept of structured data, then, regardless of whether that structure can be expressed syntactically within the programming language, you will necessarily have a mapping between that and the database. It might be as simple as a 1:1 mapping between relations and ADTs, with collections of references being used within the program to represent the keyed relationships that exist within the database scheme.