Why use ORMs if LLMs write code? by Mark Seemann

Most SQL related operations are basic CRUD. For a complex query to exist, a complex relation must exist as well. In my experience, populating such tables also boils down to basic CRUD. We input the data in "chunks", then create a complex query that wires it all up in a report for example.

EF Core generated queries can easily be inspected, and simple queries with basic joins are exactly the same as something I would've written myself. When it comes to some freaky use cases (such as creating temporary tables to faster lookup/join unrelated data), we can simply skip using ORMs. Let it automate 90% of boring work.

One interesting bit I'm playing around with is using different DBs for tests. This is something I'm being frowned upon by my colleagues, because unit tests (business logic) and the persistence layer should be separated, right? EF Core does that separation for me. Is it the cleanest, perfect solution? Absolutely not, but it does the job well enough.

A bit of context here. As you have your MaitreD example, I have my own which has all possible types of relations. Turned out to be a fun project. However, it happened to me several times that my unit tests would pass, while integration tests kept failing, because foreign key constraints kept failing. This is something (I think) where you and I agree. Mocking is terrible, fake the data. But for me, this also implies that persistence bubbled up. Sure my unit test is passing, but my unit test is useless. After all, the final purpose of an average web server is to correctly process AND store the data.

var reservations = await ctx.Reservations.ToArrayAsync();
var result = ProcessReservationRequest(reservations, reservationRequest);

if(result.IsValid)
{
  var reservation = await ctx.Reservations.Add(result.Data);
  return Accepted(reservation);
}

else return Rejected(result.Errors);
      

This semi pseudo code above would be a part of hypothetical CreateReservationHandler. Business logic lies in ProcessReservationRequest (static) function, and if I'm able to swap Postgres for in-memory SQLite, I can have a "unit test" that also verifies persistence is working. You might say that I've just discovered integration tests. Shocking, I know, but this enabled me to write them as a single test. And I honestly think now that it's more valuable and faster way to do things. There are exceptions, especially with queries, when capabilities of these DBs are different, so it can't generate a query for both (it's just not supported in one). But for basic CRUD? Chef's kiss. I know test containers are a thing, but those also require setup, and can be very slow. Not to mention they add more complications if you have a devops pipeline.

Is this line of thinking good? I don't know. Some may argue that you might wanna swap relational DB to document DB or vice versa. Such move would wreck havoc in my example above, but if you come to such point, you have bigger problems. I just like to experiment outside of conventional thinking, because I don't believe conventional thinking is always good. Controllers would be a good example.

I'd also encourage you to look at this gem. Works well with F# too.

I've always been skeptical of ORMs because they're trying to make seamless a translation between objects (graphs) and relational tables. Those are two different designs, so the translation layer will always be a leaky abstraction. SQL is designed around relations, and deriving SQL from graph relationship is always going to have edge cases in addition to complexity and performance issues.

Indeed, modern ORM usage has focused more on simple CRUD operations, abandoning object graphs in favor of DTOs, and even using multiple DTOs for different queries over the same tables. We ended up writing SQL indirectly in DTOs.

This is why I prefer micro-ORMs like Dapper (or the more modern approach in MuchAdo). They have simple mapping conventions that work well, and then step out of the way so you can write SQL. They don't try to smash a graph into tables.

qfilip, thank you for writing. There's nothing inherently wrong with automated tests that involve a database, as long as the tests in question are deterministic, independent, and run fast. Some people have problems with the independence property, which may be one reason why you often run into the sentiment that integration tests aren't real unit tests.

Well, I wouldn't categorize them as unit tests either, but the name isn't that important. What matters is what value you get out of them, at what cost.

The cost of the test is typically the other issue associated with including a real database in automated tests: They tend to be slow. If the entire test suite runs for more than ten seconds, it tends to become a problem. Even so, if this happens, you can always divide the test suite into a fast developer/TDD suite, and a slower, more complete QA suite, as described in Code That Fits in Your Head.

As to whether most SQL operations are basic CRUD, I suppose that depends on context and what kind of applications you are being asked to develop. I've worked on several projects that mostly involved a lot of complicated queries.

Stephen Cleary, thank you for writing. I take it you are already familiar with Ted Neward's article The Vietnam of Computer Science?