You can absolutely build a product on top of an SQL database, by just writing the SQL queries you need.

As a bonus, if you're thinking ahead, you can avoid writing queries that will perform poorly. If you're not thinking ahead, at least you can more easily fix the ones that do.

I've never really understood why people want to interact with their database outside of its native query language. There's just so much mismatch between an ORM and the underlying interface, it feels like fighting all the time. And I don't feel like it's really saving any time either. The one thing that's sort of nice is it's easier to write a general filtering function, but SQL filtering lets you write so many things that won't perform well, making that easier isn't really helping anybody.

I've been thinking about writing SQL queries again recently because I'm using too many ORMs and at least one of them is unnecessarily complicated.

Writing queries is easy. Validating data is not too difficult. Building objects/whatever from the resultsets is not so easy anymore, especially with joins.

By the way, the mistake this post is about is a classic with Rails. You have to use a unique index in the db, not making queries. You want to protect your data anyway, regardless of which application you start building your db for. Others will follow, possibly in different languages.

I don't want a full-fledged ORM, and I sort of like SQL, but just normalizing nested objects to put them in the database and then unnormalizing them on the way out is really annoying to do in raw SQL in my experience. Every time you want to store a new object in the database, it feels like so much effort to write a bunch of boilerplate functions. Or do you have a solution to this?

I enjoy using SQL in an environment with first class hash/map/ whatevers; I don't like to build up an object. SELECT X, Y, Z from .... gets you an array of things with X, Y, and Z keys.

Turing your hash into parameters for an insert isn't going to be great fun, although perl hash slices aren't bad. I think I've seen something about named SQL variables instead of just ?, that might be usable with a hash directly?

If you're using python, you can use named tuples for everything. It's really nice! Check out the psycopg2 namedtuple connection.

I just glanced at this, and maybe I don't understand, but it sounds like psycopg2 namedtuples only work on one table at a time; is this right?

The namedtuple is just a datastructure interface with psycopg2. For instance, you could write an arbitrary query "SELECT left.x AS cats, right.created_at FROM left JOIN right ON etc" and you'd get namedtuples with attrs `cats` and `created_at`. Similarly, you can insert namedtuples just like rows, since they are iterables, or as namedparameters with the nt._asdict() method. It's great!

> if you don't use an ORM, you'll invent one yourself

Not true. You may use a graph database, or write SQL statements & apply functional transformations. Or you might be using a platform like smalltalk & just save your image. There are other paradigms besides OOP & relational databases.

> new hires will end up taking a ton of time to learn this "custom" orm framework of yours.

Or your new hire might really know how to leverage the particular database you're using, but you force him to throw away a lot of his experience & to start over from scratch & instrument the database through an ORM layer. There are a lot of ORMs, their APIs may even differ, so someone who knows Propel still has to stop & read the docs if you're using Doctrine, for example.

> it showed a true lack of knowledge of the ORM at hand and even SQL in general.

Quite the opposite, anyone who can craft a query that complex obviously knows the tool well. Their real problem is knowing when to not use that tool. Or they just write bad code, regardless of what tool/langauge they're using, for whatever reason.

I think the scope of the discussion is whether to use an ORM when using a database that supports SQL.

Or businesses that write their own database wrappers "because we might switch to Oracle one day and this will isolate the code from that change"

> You may use a graph database

There you go. I need 6 more months to deliver now.

ORMs try and solve a hard problem, and the devil is in the detail.

https://martinfowler.com/bliki/OrmHate.html

It surely is hard. But is it worth solving?

The price you pay for a one-size-fits-all solution is that you lose the flexibility of arranging your queries the way you want. You'll automatically lose a lot of performance (that is not always important), but you also lose a kind of code clarity.

If you don't get a one-size-fits-all solution, you will have to write the translations by yourself. That leads to more code, but in a better structure because you don't need to beat your code until it fits the ORM's model.

All said, I do think an ORM is not something useful by itself, but may be part of a very useful solution. Python Django's CRUD generation and Haskell Persistent (not really ORM, but very similar) type checking are examples of that.

>And new hires will end up taking a ton of time to learn this "custom" orm framework of yours

Or your new hire actually learns how to use the tech that makes their code work. I could use fewer devs who have no idea how the code they write actually works.

> Well the problem is, if you don't use an ORM, you'll invent one yourself...only poorly

There is always a trade-off. The code you wrote, even if it is poorly re-implentation, you know it inside out, since you wrote it. The third party code, may be better, but people usually make a lot of implicit assumptions and end up not using it correctly, so you end up in situations like this.

For DB layer, I usually prefer custom code so I know exactly what is going on under the hood.

The best of both worlds is just to... actually understand the ORM you choose to use.

I'm always confused by how reluctant folks are, in general, to read underlying libraries.

You don't even have to read the library source, just verify the SQL statements generated. Most ORMs let you drop into raw SQL for advanced clauses and sub-statements. For me there value of an ORM is composable business logic. For example, if whereHasLotsOfTrees is a complicated SQL subquery and has it's business logic definition changed, I can edit only one function and have the new logic work everywhere.

I think you might be confusing 2 different ideas. An ORM is an Object Relational Mapper. Lots of companies build very big software without using an ORM. And the very concept of an ORM becomes meaningless if you use a language that rejects Object Oriented Programming. For instance, when using Clojure, some teams use Korma:

http://sqlkorma.com/

Korma is a DSL. And I think you are confusing the concepts of ORM and DSL. I'd agree that most teams, over time, tend to develop (or borrow) their own DSLs for interacting with the database. And I've seen teams that use the term DSL loosely to refer to any code they write to interact with the database, so long as they are doing more than concatenating strings of SQL. But they don't necessarily use ORMs.

While I agree with this, the quality of ORMs vastly differs. For instance, the ORM style that wraps sql in a dsl is strictly better than one that encourages model lifecycle callback hell, as activerecord does. The latter is never necessary to work with a database and is a terrible pattern long-term for composing database interactions: it is extremely difficult to reason about what will actually happen inside the transaction (or immediately after).

Tl;dr you aren’t wrong, but many home-rolled ORMs are simpler and much easier to use than, say, activerecord.

I think that QueryDSL on top of JPA on Scala is just about as good as it gets.

It is also complex and requires that you really master all the ingredients... If SQL is a mystery to you, it will just be a painful experience.

> if you don't use an ORM, you'll invent one yourself...only poorly.

My favourite SQL library is Clojure's HugSQL, which avoids this issue by letting you write SQL in .sql files and then generating functions for you that execute these queries. https://www.hugsql.org/