To ORM or Not to ORM • Hacker News Board

Re: To ORM or Not to ORM

#161

Post by OJFord » Tue, May 07, 2019, 9:37 PM UTC

lenkite wrote: post #152

btilly wrote: post #133

Earlier quoted context omitted.

I have spent enough of my life untangling a mess of views that create performance problems to doubt that views are maintainable. Also, there is usually no good solution for keeping the database in sync with code releases. With the result that database+code change synchronization tends to be a source of deployment complexity. Keeping the logic in one place is better than the alternative. And of the two, the tools to m…

We use liquibase https://www.liquibase.org/ for tracking, managing and applying any schema changes. The liquibase changelogs are part of our project releases.

> Supports XML, YAML, JSON and SQL formats

XML is the default on the page, but wow the SQL is _so_ much shorter and more readable than the others! To the extent that I wonder what's the point, the obvious is machine readabaility, but one not three, and is SQL really so machine-unreadable that it's worth the extra depth and complexity?

Re: To ORM or Not to ORM

#162

Post by rambojazz » Tue, May 07, 2019, 9:38 PM UTC

halis wrote: post #158

I was pretty excited when I originally learned about ORMs. But I quickly found that their usefulness is very limited in the enterprise, where most of my career has been spent. You will likely find, as I did, that enterprises literally have thousands of databases of all kinds and many crazy data models. They have tomes of huge SQL procedures that are still to this day running their business processes. Maybe even a nic…

Yes this is my experience as well. ORMs are really nice for simple queries, not so much when queries start to get more complex.

Re: To ORM or Not to ORM

#163

Post by OJFord » Tue, May 07, 2019, 9:40 PM UTC

runin2k1 wrote: post #150

hcho wrote: post #131

Earlier quoted context omitted.

Doesn't alchemy have an execute method, to which you can pass a raw sql?

Of course it does. Not that a little detail like that would stop someone from complaining about something that they don't put in the effort to learn.

If you just use that all the time, your coworkers will rightly complain that it's not idiomatic or using the tooling available in code review.

I'm all for SQL, but if the project's set in its ORM then it doesn't make sense to do ORM.raw(...) or whatever out of preference alone.

Re: To ORM or Not to ORM

#164

Post by cjdell » Tue, May 07, 2019, 9:41 PM UTC

I fell out with a lot of the popular ORMs and decided to create a very lightweight query library with the features I really wanted, namely an ENTIRELY statically typed query interface.

Link to fully working sample: https://github.com/cjdell/ts-sql

The idea is that it should be impossible to write an invalid query, and also to ensure the returning data type is exactly representative of what has been queried.

Currently using knex.js to actually generate the SQL behind the scenes. It's experimental but I'm already using it in a couple of projects. I enjoy the fact the my queries have so little boilerplate now. :-)

Re: To ORM or Not to ORM

#165

Post by echelon » Tue, May 07, 2019, 9:41 PM UTC

In my experience ORM always shoots you in the foot when your use case exceeds what an ORM can provide. ActiveRecord has been a terrible mess for us because the facade masks the underlying behavior (transactional behavior) or often doesn't do what it advertises (commit hooks, timestamps, auto-increment).

ORM is great for startups trying to build out an MVP, but beyond that scale it's an anti-pattern.

The best solution I've ever seen is jOOQ, which is essentially typified SQL. Your queries have to pass compilation, but the DSL is essentially SQL itself. It's an added layer of security yet still manages to feel like it's easier to write than raw SQL.

More languages need a jOOQ. It's so good that I'd choose Java just for the ability to leverage jOOQ.

Re: To ORM or Not to ORM

#166

Post by davidspiess » Tue, May 07, 2019, 9:42 PM UTC

gmac wrote: post #126

hardwaresofton wrote: post #65

While I learn towards not using an ORM, the productivity gains (at the very least early on in development) are undeniable. What I've always looked for are frameworks that give you an ORM but also make lower level queries very easy, normally via a query builder, allowing you to go back and forth between levels of abstraction. If I had to choose, I prefer libraries that give you the lower level of abstractions first an…

I loved TypeORM initially, but came to the conclusion that it was written by people who really got TypeScript, but didn't really get SQL. For example, we were converting from Sequelize, where we were catching unique constraint violations and responding appropriately, and we got quite confused that in TypeORM those errors never got thrown ... until we discovered that TypeORM decides unilaterally (and almost unbelievab…

Really interested in your blog post, especially on generating types for the database. Can you give some short insights on how you achieve it?

Re: To ORM or Not to ORM

#167

Post by Dayshine » Tue, May 07, 2019, 9:44 PM UTC

dragonwriter wrote: post #130

JamesBarney wrote: post #120

Earlier quoted context omitted.

Not to mention code reuse. Say for instance we have one query that finds eligible bachelors near me and another that finds newly eligible bachelors. We need two queries but they both rely on the same underlying domain concept of "eligible bachelors". In entity framework it's easy to save that as an expression that can be reused in multiple places. It's hard to do that in sql in a maintainable way. Not to mention refa…

> Say for instance we have one query that finds eligible bachelors near me and another that finds newly eligible bachelors. We need two queries but they both rely on the same underlying domain concept of "eligible bachelors". [...] It's hard to do that in sql in a maintainable way. Seems a textbook case of using a view for sharing query logic. > If I decide to change a datetime field called time to split it into a da…

>Seems a textbook case of using a view for sharing query logic.

Views are just terrible outside of data analyst style work. Unmaintainable, restricted to SQL structures, require absurd hacks or custom dlls, not properly source controlled, difficult to perform performance analysis on, hard to update.

You can't combine views easily, you can't cache results, you don't get static type checking.

Re: To ORM or Not to ORM

#168

Post by polygotdomain » Tue, May 07, 2019, 9:46 PM UTC

btilly wrote: post #133

polygotdomain wrote: post #125

Earlier quoted context omitted.

> It's hard to do that in sql in a maintainable way Views

I have spent enough of my life untangling a mess of views that create performance problems to doubt that views are maintainable. Also, there is usually no good solution for keeping the database in sync with code releases. With the result that database+code change synchronization tends to be a source of deployment complexity. Keeping the logic in one place is better than the alternative. And of the two, the tools to m…

>I have spent enough of my life untangling a mess of views that create performance problems to doubt that views are maintainable.

Just like concepts like inheritance can be a burden when overused, views can be overused as well. Like inheritance, having views go too many levels deep generally winds up causing more harm than good.

Used correctly though, views are able to capture and centralize concepts in a way that are reusable, can be more easily tuneable, and make maintenance easier in the long run. Queries can be simpler and encompass less responsibility as the common logic is implemented by the view, and the logic that is specific to that query is more readily apparent.

IMHO thinking of the DB as just a collection of tables misses out on a key db feature.

>Also, there is usually no good solution for keeping the database in sync with code releases. With the result that database+code change synchronization tends to be a source of deployment complexity.

I disagree that there's no good solution, but I understand that it's not as "out of the box" as many ORMs offer. That being said, writing SQL scripts that coordinate schema changes is not hard, and any CI/CD pipeline that can run commandline utilities can run a sql script against a database.

At my work, we use DbUp (https://dbup.github.io/) and are able to easily write small, isolated SQL scripts that correspond with code changes and get committed to source control, run all of those scripts with our CI/CD pipeline across multiple sharded databases, and do so quite seamlessly. We don't have any questions about migrations or what a tool did, as running the SQL script individually is the same as it runs on deployment. Coordinating db changes on developer machines is a breeze as well.

>And of the two, the tools to manage source control are better.

All of our sql scripts are committed into source control. It's not any easier or harder than looking at a code file, an html file, or a config file to get to the logic your working with.

Re: To ORM or Not to ORM

#169

Post by krisrm » Tue, May 07, 2019, 9:46 PM UTC

nullwasamistake wrote: post #147

_ea1k wrote: post #142

Earlier quoted context omitted.

+1 to everything but the last bit. In my experience, the best compromise is something like Hibernate (or some other ORM) for the mapping and the really basic queries (find by ID, find by some random field, etc). Possibly still hibernate for the really advanced crazy stuff, too, though this is more doubtful. I've had pretty good success in the past with Hibernate Geospatial and full text search, for example. In betwee…

+1 for Hibernate. Everyone starts out hating it. The learning curve is steep and unforgiving. But it can do roughly everything SQL can plus everything you would want from an ORM. For the few that actually read the manual, there's strongly typed Criteria queries that have the full power of HQL. Which is basically DB agnostic SQL with a few really advanced features removed. There's also lazy loaded collections, caching…

I'm still at the "hating it" stage, but most of my Hibernate experience has been with older versions of Java (5 & 6), or with trying to deal with other people's HQL sprinkled all over. It's a lot of rope to hang one's self with, IMO. Not that native SQL isn't, but there's something to be said for an enforced separation of concerns that I feel like Hibernate breaks a little too often.

Re: To ORM or Not to ORM

#170

Post by Rapzid » Tue, May 07, 2019, 9:50 PM UTC

34r45sdg wrote: post #90

I am quite disappointed that both the article and the comments dont mention application security. USE AN ORM. Unless you are a hotshot SQL dev who knows the intricacies of every RDBMS, you should be delegating this to the ORM. Its there to prevent you from blowing your foot off by introducing SQLi vectors. Use an ORM, avoid SQLi.

Always be preparing(statements).