Hacker News Reader

Comment by bonesss | Hacker News Reader

> I do things "the hard way". If I'm using a dynamic language WTF do I need an ORM for

I think it's mostly premature optimization. People think writing DTOs is challenging, so they want an ORM. But since you end up needing DTOs anwyays, removing SQL capabilities from the app means writing SQL in not SQL, and things like joins suddenly become slow and problematic and result in really heavy systems that are harder to change. For the joy of a quicker startup the entire project moves slower.

ORMs have their place, but in the majority of the systems I've seen they were unnecessary, and in broad terms don't provide any particular productivity advantage over using "dumb" SQL-based mapping solutions (a la Dapper [https://github.com/StackExchange/Dapper]), that preserve the power of SQL.

View on HN

> People think writing DTOs is challenging

I don't think this is true. Writing these objects isn't difficult, it's tedious and repetitive. That's why people keep trying to automate it!

The problem is that you can't quite automate it smoothly, because SQL doesn't work like objects. You avoid this interface issue by taking the hit for the tedious-and-repetitive stuff directly (and I agree that's often the right choice) - but that doesn't dissolve the problem.

I’m actually quite fond of SQL, but I disagree that ORMs are not a productivity boost. My experience is in Rails and I think ActiveRecord is a pretty clear win for simple queries. That being said, it is pretty common for less experienced developers to not understand what the ORM is actually doing.

On any project of sufficient size there will be fairly advanced reporting functionality that you generally will not be able to do using ORM so you will end up with a mix of ORM and direct SQL. Also ORM forces you to the lowest common denominator for supported RDBMs I generally do not want to be limited to the SQLite features if I am running PostgreSQL

I did qualify my statement with “for simple queries”.

For more complicated queries, a pattern I have become quite fond of is making database views and then using them as the backing table for an ORM model. In Rails, at least, this gives you the best of both worlds.

Yep that works well for many use cases

Using PostgreSql, I tend now to directly generate query result in json.

It implies an architecture model where you put the business logic and type safety in the RDBMS.

It reduces the number of layers for a lot of functionalities.

How does version control work for an architecture like this?

In my experience, migration scripts that include modifications to output JSON (as ALTER TABLE statements) are always tracked and programmed to be auto-executed on each version update.

About 17 years ago, I wrote a GUI-based code generator that allows me to generate the boilerplate JDBC cruft from SQL statements, with several options for common scenarios. The code it generates is extensible and provides helpers for extending.

To this day, I haven't found anything (including ORMs, Spring support, etc.) easier to use, more flexible, or more sensible.

Is it closed source? I am in search for something like this. I consider this as Naked Object approach.

Unfortunately, it is closed and wired into an overall framework I crafted, so has some dependencies there.

If I were to write it today, I'd be more conscious of limiting dependencies and generally designing with open-sourcing in mind.

I recently wrote one for C# / MSSQL. I've been using EF on side projects, but work was concerned with it, so I just wrote a CRUD sproc / Entity / Repository / Service / DTO generator. Connect to a database and select the tables you want to build for, and done. 5 layers of abstraction in under a second. It gets me to about 95% of what I need and I custom build the special circumstance stuff from the generated objects. It creates the "if exists / drop" stuff, exception handling and comments w/ dates in the sprocs that reference the objects in C#, and vice versa, what table was referenced, proper [Key] and [MaxLength] attributes, etc. Puts them in their proper folders for git too. It does exactly what I would have done had I done it manually.

Works great with no ORM abstraction and it was fun to write.

Yeah, very similar--with one primary enhancement: That is, I initially started generating objects-per-table, but found that it was a little limiting for a lot of use-cases I encountered. In particular, it didn't cover joins very well--particularly for queries that fed list views.

So, I expanded into supporting raw SQL SELECT queries that can include joins, which I parse and combine with DB metadata. I then generate the DTOs from there. So, a single DTO can have properties mapped to different tables, which I found much less redundant/limiting than entity-per-table designs.

In addition to the SELECT code, I can use simple checkboxes to also generate INSERT/DELETE/UPDATE/UPSERT code, which map the DTOs back to the underlying tables. It recognizes keys and includes multiple-table writes in a single transaction, etc. In addition to the DTOs and the DAO layer, it can also optionally generate a service interface.

Of the utilities I've written over the years, it is the one that most stands out as having paid me back incalculably.

If your application mostly reads from the DB then don't use a ORM. I think ORMs shine when you need to save/update a domain model to a relational DB. In the CQRS world, a fairly typical choice when using a relational DB is to use a ORM for the write side and raw SQL for the read side.

Reply on news.ycombinator.com