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

491–500 of 654 posts

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

#491

Post by nikolasburk »

Rapzid wrote: post #479
Trisell wrote: post #407

Earlier quoted context omitted.

That’s how I’ve done it on my last two projects. We used TypeORM for the standard repeated simple queries, and then wrote custom SQL for our complicated queries that the ORM failed at and then just executed them with the ORM. It was really nice and made for easier table refactors because we didn’t have to go through and audit every query that was calling that table.

TypeORM is a step in the right direction for JS ORMs bit it's like 1/8th of the way there IMHO. A nearly fully typed ORM is possible now with Typescript and of course proxy's are out now.. TypeORM was doing too much ADHOC string building under the covers as well. I believe a SQL AST is the way to go. It can be transformed and compiled to database specific SQL allowing for things like predicate push down, optimization…

I really like your point about type-safety! I think one major issue with the current ORMs in the Node.js/TypeScript ecosystem (Sequelize, TypeORM, ...) is that they're not fully type-safe. As you mention, TypeORM is definitely a step into the right direction here, but the way how it leverages the TypeScript type system isn't where it could be. I work at Prisma where we're building [Photon.js][1], an auto-generated database client with a fully type-safe data access API.

[1][https://github.com/prisma/photonjs]

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

#492

Post by fastball »

fuzz4lyfe wrote: post #369
fastball wrote: post #365

Earlier quoted context omitted.

Sorry, maybe I wasn't being clear. I don't just validate that the request is JSON, I validate that the fields in the JSON are valid fields to send over the wire. I do this by automatically hooking my ORM models into my request validation. If a request doesn't specify a column that is NOT-NULL, for example, it will automatically send an error response telling the client that it needs to specify that column in the requ…

Python doesn't have type safety to begin so those sort of checks have less utility to me and since json.loads returns a dictionary and python objects are effectively dictionaries you are pretty much done.

You don't validate incoming request types / values?

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

#493

Post by kilburn »

timmy-turner wrote: post #475
gmac wrote: post #467

Earlier quoted context omitted.

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 ).

Wow this is great! Very well written README. What just blew me away is the thing with the `JOIN` and the `to_jsonb(authors)`, all with complete typing support for the nested author object. I was actually looking to use a classical, attribute driven query generator (with the sort of chaining API everyone is used to: `tableName.select(...coumns)` etc.) for my next project involving to maybe replace/wrap/rewrite a Rails…

I'm also awed by this!

> Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper.

My current team is pretty junior, and I don't see any problem with this. Simple SQL queries are really easy to learn, and complex queries are harder to understand with ORMs than in raw SQL.

Moreover, knowing SQL is a useful, marketable skill that will stay relevant for many years to come. If there's some resistance, I can easily convince the team that going this route will benefit them personally.

Back to the README, there are two questions I'd like to see addressed:

1. Whether `Selectable[]` can be used to query for a subset of fields and how.

2. In the `to_jsonb(authors)` example, what would you get back in the `author` field if there were multiple authors with the same `author.id` value? An array of `author.Selectable` objects? This part is awesome but brittle, isn't it?

I would love to see this move forward! I will definitely play with it and consider it for my next project.

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

#494

Post by dragonwriter »

goatlover wrote: post #276
delusional wrote: post #157

Earlier quoted context omitted.

I would actually let OOP off the hook here. I think what did the harm in this case was the java generation. The generation of programmers that were told that in the future they would only have to write the "business logic", and everthing else would just happen. They were taught javabeans, orms, gigantic frameworks. They completely forgot that their code actually needed to execute, and no one cared about their "busine…

> It turns out that SQL is actually much simpler than ORM's. Are they simpler when it comes to matching the data to the application's data structures? One advantage of ORMs is that they encourage this setup from the start.

> Are they simpler when it comes to matching the data to the application's data structures?

That depends on the modelling approaches taken by the application developer and DB developer.

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

#495

Post by bjoli »

laszlokorte wrote: post #84
bjoli wrote: post #38

Earlier quoted context omitted.

Can you elaborate? SQL queries compose just fine, it is just that most developers don't understand the relational part.

Composing in this context means that one part of your application (eg the list controller) builds part of a query (the select from) and another part (eg the filter controller) builds another part of the query (the where part) and yet another part of you application (the paginator) alters there where part, adds limits and offset and build another query based on the same filter conditions to calculate the total row cou…

As the other answer to you says: subqueries will do that. DBs automagically unnesting subqueries has been a thing for a long time, so usually you won't get any performance impact (With a big warning for correlated subqueries).

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

#496

Post by Traubenfuchs »

invokestatic wrote: post #27

This topic pops up frequently here on HN and every time I’m shocked at how many people have issues with ORMs! I’ve been using Hibernate/Spring Data for several years now and never ran into any issues. If I need to write a complex query, I can easily write a @Query annotation in HQL and it neatly fits right in to the repository class. I also develop with query logging enabled so I have better understanding of the quer…

And if you either lack the skills to properly use JPA/Hibernate or really have super custom special requirements, you can at any time execute native queries with the EntityManager. Still not enough? You can even directly work with Connection objects.

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

#497

Post by pnako »

vips7L wrote: post #378
philwelch wrote: post #308

Earlier quoted context omitted.

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.

Of course. But surely you don't let anyone access your API, and you put it behind another API, right? Just in case you need to change that first API without breaking all the users.

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

#498

Post by dkersten »

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 like Clojure’s HugSQL[1] for this reason: you can simply write raw SQL, but when you start duplicating code, you can start factoring those bits out into composable “snippets”. The best of both worlds: composability and reuse, while still writing raw SQL.

[1] https://www.hugsql.org/

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

#499

Post by timmy-turner »

kilburn wrote: post #493
timmy-turner wrote: post #475

Earlier quoted context omitted.

Wow this is great! Very well written README. What just blew me away is the thing with the `JOIN` and the `to_jsonb(authors)`, all with complete typing support for the nested author object. I was actually looking to use a classical, attribute driven query generator (with the sort of chaining API everyone is used to: `tableName.select(...coumns)` etc.) for my next project involving to maybe replace/wrap/rewrite a Rails…

I'm also awed by this! > Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper. My current team is pretty junior, and I don't see any problem with this. Simple SQL queries are really easy to learn, and complex queries are harder to understand with ORMs than in raw SQL. Moreover, knowing SQL is a useful, marketable skill that will stay relevant for many years to come. If there's so…

Right, only querying a few fields seems not to be a builtin feature. Looks like you have to create the partial selectable type yourself and there is no support to typecheck that the correct columns in the select are included.

Your second case, if I recall this correctly (ActiveRecord made my SQL skills fade away), this plain JOIN would just return a row with the same book but a different author. `to_jsonb(authors.*)` is just operating on a single row. But what you want is possible (aggregating rows into a JSON object) by using `jsonb_agg`. Whether the lib supports inferring the correct typings for that is another question though.

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

#500

Post by GordonS »

gmac wrote: post #467
kccqzy wrote: post #439

Earlier quoted context omitted.

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 ).

I've never been a fan of codegen, but I think I could get past it for this library - it looks great!

I love how it let's you use SQL, while taking full advantage of TypeScript's wonderful typing system to give you intellisense and compile-time checking. Reminds me a bit of the SQL type provider for F# (which I was amazed by when I first saw it in action).

I really like the way the readme has been written too - it gives a real insight into the thought processes that led to the final result.

Post reply on HN