Exactly why ORMs are a bad idea. I've always wondered whether ORMs help or harm. I feel like the one reason to use it is if you have a development team that isn't capable of writing SQL which in itself is bad.
And there are many bad, or simply, "too simple" ORMs out there that really don't help you much. I haven't really found one better than Perl's DBIx::Class (though outside of Java, Python, Ruby, I have haven't really looked).
Case in point, I recently found this little gem[0] allowing easy correlated subqueries. I basically took a webpage that was loading in over 2 minutes, and reduced it to about 100ms and the outputted SQL was about 2 pages long (from about half a page of custom resultset orm code). I used several correlated subqueries to sort of pivot part of a table (well several tables actually). The original author of the code I was working on was fetching entire tables of data, with each row fetching (joining) to another entire table (it did this in several levels really) just to sum some values. This was all valid ORM code (even with handy 'if' statements checking every row id to do in ORM join (yes he didn't even use a dang where clause!)) but it showed a true lack of knowledge of the ORM at hand and even SQL in general. Nonetheless, I saved the day :)
[0] https://blog.afoolishmanifesto.com/posts/introducing-dbix-cl...
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.
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.
There you go. I need 6 more months to deliver now.
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.
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?
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.
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.
Tl;dr you aren’t wrong, but many home-rolled ORMs are simpler and much easier to use than, say, activerecord.
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.
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.
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.
I'm always confused by how reluctant folks are, in general, to read underlying libraries.
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/
I'm not a full-out fan for ORMs but... this is not an ORM issue. It's an issue with an ORM of the ActiveRecord variety that pushes database-logic back to code level for some strange reason. The ORM I'm most familiar with (Doctrine2, Object Mapper pattern) doesn't do this. It does other weird stuff, but not this.
[1] https://docs.djangoproject.com/en/1.11/topics/db/sql/#mappin...
No one likes copying the SQL command to a SQL IDE, making some changes, running it to make sure it works, and then copying it back to source. If you don't do that, you're dropping all of the productivity that syntax checkers do for you and risk losing productivity to typos or other dumb mistakes.
Ultimately it's a style preference but I've seen larger projects with thousands of lines of SQL in source with some of those queries spanning 6-7 joins with dozens of where clauses (plus string manipulation functions, ugh), so that is why I'm pretty fiercely against SQL in strings.
Sometimes it seems like people get hung up on doing everything in one query and end up making a Frankenstein's monster that nobody will want to touch when it inevitably breaks on the next database upgrade. Although sometimes the system forces this behavior by ingesting the output of the query automatically.
At least in my experience, assuming those queries are written "correctly" (that is, in a set-manner than a C-in-SQL manner), leaving it in the database means is usually vastly more performant and easier to verify it's correct. I've written a lot of many-hundred-line monsters with joins, CTEs, table-valued functions, and a whole lot of other stuff piled into them that were both more clear and much faster than the arcane application logic they once consisted of. Usually if it's used in the application, some management type will also want some kind of report based on pretty much the same query, too. And having it as SQL also makes it much quicker to compare the before-and-after if any changes are made.
Of course, YMMV and what I said isn't true all the time, and would certainly also depend on your database server - if queries did break between upgrades I'd be much more hesitant to rely on the db server.
But either way, I wouldn't dump a monster like that right in source code - that's crazy!
This is why the N+1 is such a big deal and you can get results magnitude faster of you query through the database.
So, SQL strings belong to the metal, not the UI.
Though there are certainly cases where it helps to be able to find someone more experienced and knowledgable than you.
Or perhaps you're a DBA that's tooting your horn so loudly that you don't believe anyone can do what you do.
I thought this thinking was 10 years dead at this point.
And, to be fair, having seen some of the abominations people have committed with SQL, I only half blame them.
But the issue is with saying "no developer should be writing SQL strings, ever".
It's like saying "no designer can handle HTML, leave that to the professionals".
You get to use the orm for a lot of braindead simple, and mild complexity stuff, and can drop down to the sql builder as it makes sense for complexity or performance reasons.
It also understands the various databases very well, so if you choose e.g. postgres, you can access most of the features without needing much (if any) "raw sql".
To my own surprise, it has become my favorite way to handle queries with multiple/variable joins.
By example:
$sql = "select myColFromTableB from tableA";
Inspection will give you have a "unknown column" warning.
(ps: sorry for late reply)
Exactly as with sql upgrade scripts.
It look like this:
--name: post-event
SELECT post_log(@theId, @ENTITY, @ACTION, @CHANGEBY, @DATA, @VERSION)
GO
--name: get-location
SELECT * FROM "Location"
WHERE
id = @id
GO
--name: list-location
SELECT * FROM "Location"
ORDER BY country, state, city;
GO
module Location =
let ENTITY = "Location"
let SQL_LIST = SQL_CMDS.["list-location"]
type LocationRecord = {id:int64 option; address:string; country:string; state:string; city:string; version:int64}
type LocationQuery =
| All
| ById of int64
let query q =
use con = openConn()
let toRec = Db.toRecord<LocationRecord>
match q with
| All ->
Db.select con SQL_LIST []
|> Seq.map toRec
| ById(theId) ->
let id = [P("@id", theId)]
Db.select con SQL_BYID id
|> Seq.map toRec
This take me like a few hours. Let me test easily the sql. I can build the sql exactly as I want.
The only cons is the repetition on the scripts - because SQL is a terrible language that not allow composability, like similar to CSS - . Probably I will later use a template parser (like mustache or similar) but I think this is the closest to the holy grail ;)
What do you do when you have five similar queries, and each needs to be changed if one changes?
This seems like smaller projects might work, but I'd be concerned about using a strategy on a larger project.
Not a fan of this strategy in particular, but I can see arguments for it.
In short, I truly utilize a DB with full effects.
I don't follow the mantra of using a DB "only" as a dumb datastore. Something that I get validated as when people use NoSQL and have not option that use all of it.
This mean, that I use views and stored procedures/functions.
This cut a lot. Also, proper modeling of tables also help (a lot). I refactor DBs.
I mean, I truly treat a DB as "code".
Obviously, this strategy is not flawless, and it will benefit from some kind of ORM-ish layer to auto-generate the mechanical parts of the querys. I'm building that part myself.
But so far, is have show to be good.
And how exactly are you making sure your ORM code works?
I strongly recommend it. Having statically typed representation of the schema has many pros and the trade-off is a little overhead when changing the db schema to regenerate classes.
Some of the benefits include knowing exactly what part of the code stops compiling after changing the db schema, easily map POJOs to-from query results.
Also, there's simply no pressure that you have to be absolutely certain what each and every knob on your ORM does else you have bad data in your database. Been burned before with Hibernate defaults, and the thought of a junior dev creating wrong conf is just not worth it.
The database is basically the place were you least want a risk of misuse to have any unseen side effects.
Tbh thats currently my preferred methodology, just because it's usually nicer to clean up a mistaken query in something like sequel-pro, and its generally easy to verify correct output visually
And I've had to do the same thing with ORMs like sqlalchemy, and the only real difference is I first have to get the ORM to tell me what it generated (which is a pain, sometimes).
Actually, I started to put SQL directly into my source code. IntelliJ, for instance, has really good support for embedded SQL code (syntax highlighting, auto-completion, executing queries including placeholders etc.).
Only for object oriented apps, where you want to tightly link behavior to your objects. For other kinds of app architectures, there may be no relational/object oriented mismatch to smooth out.
However, if you have well-defined requirements and are expecting high-volume traffic from the get-go, then it could be wise to skip the ORM.
All web developers ought to know the basics of SQL ie how to SELECT INSERT and UPDATE