I haven't touched an ORM in over 6 years, but unless they've improved since then, I honestly can't think of a single reason why anyone would choose to use one.
They're clunky monstrosities that act only as guard-rails for inexperienced developers. Far better to invest a few days (which is realistically all you need) to improve their SQL skills and/or code-review practices.
Yes, things like setString(1, “asd”) and conversely getInt(2) are so beautiful and will never introduce mistakes /s
ORMs are there for mapping objects and well, relations. Most ORMs provide additional features, but at the core they can, and for more complicated queries they should be used with native SQL queries. They are made for OLTP not for OLAP
There are "simple ORMs" that only map results of SQL queries to objects. They do not provide a magic query API - which is the source of most problems. I don't do Python, but for .NET there is Dapper https://github.com/StackExchange/Dapper, you can have a look what I mean. You write the SQL query, explicitly execute it, the library maps the results of that query into objects (it's C#, so you have to declare the class. In Python I'd imagine it would create the object for you)
+1 for Dapper. It does have some limited "magic" query building features, but only for straightforward CRUD operations that don't involve joins. And that's a Good Thing (TM).
> I honestly can't think of a single reason why anyone would choose to use one.
In the case of SQLAlchemy (referenced in the article), many people use it as a database connection abstraction rather than an ORM. It's kinda the equivalent of "ODBC" for certain Python libraries like Pandas.
For instance, in Pandas you can write your dataframe to the database by going `df.to_sql(tblname, sqlachemycon)` where sqlalchemycon is the connection instance to any database that SQLAlchemy supports.
I am by no means an experienced developer--but the issue I always seem to have without using an ORM is that there are string literals containing different bits of SQL scattered all throughout my code. In addition to being hard to refactor when the database model changes, I think there is a fairly high performance cost to doing so many string concatenations every time the code runs. Is there a better way to manage the SQL command strings when you are sending the queries by hand?
That was my biggest issue with writing own SQL, until I started using PyCharm. A while ago they integrated DataGrip (I think it is only available in pro version) which makes the IDE also understand SQL code[1].
If you connect the IDE to a database it starts to recognize the SQL to the point it behaves like rest of the code (you have autocomplete etc). I am starting to think that this is the correct approach and ORMs were just a hack trying to achieve that.
A lot of times you don't need to change the SQL string at all. You just bind different parameter values before sending the same SQL string to the DB server over and over again.
The exceptions are SQL elements that cannot be (easily, or at all) parametrized, such as the column lists in SELECT, ORDER BY, GROUP BY, or changing the WHERE "shape" and so on.
In my experience, this tends to be a minority of queries, although an important minority.
----
P.S. If string concatenations are your bottleneck, then your database is screaming fast! The real-life bottlenecks are usually in excessive database round-trips and unoptimized query plans, and are orders-of-magnitude larger.
I haven't done DB stuff in a while as I've mostly been frontend, but I reckon the way I'd lay it out is in the same way that I have a "clients" or "services" folder (or repo) which contains things that return Promise<Data> (and I don't have to care whether their source is HTTP, Firebase, or anything really). I would probably do the same with my back-end application (or lambda). Directories (or repo) full of services which are sets of high level calls (e.g. getPotatoes()) which are async functions that return data. Inside would be (probably) SQL.
> I think there is a fairly high performance cost to doing so many string concatenations every time the code runs
String concatenation is extremely cheap compared to any sort of IO or computation.
I disagree; not using ORMs isn't going to magically make developers write better queries, why not spend those few days training them to use the ORM better? Would you rather have raw SQL strewn about the codebase and have to worry about input validation and data (de)serialization every single place? Maybe it's ok for toy apps, but I wouldn't want developers bringing their own different styles of writing SQL all over a project. An ORM helps standardise this stuff
They're clunky monstrosities that act only as guard-rails for inexperienced developers. Far better to invest a few days (which is realistically all you need) to improve their SQL skills and/or code-review practices.