Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

To ORM or Not to ORM

eli.thegreenplace.net

Post reply on HN
Print view View original

181–190 of 300 posts

Re: To ORM or Not to ORM

#181

Post by ensignavenger »

dillonmckay wrote: post #106
paddy_m wrote: post #81

Earlier quoted context omitted.

You can do this with most any ORM by mapping tables on top of views defined in SQL. I regularly use this pattern with Django's ORM to make complex aggregations only a foreign key away. I can write detailed performant SQL that it is impossible to make an ORM output this way.

What are you using to version/migrate views?

I can't answer for the above poster, but the Django ORM Migrations allow for executing raw sql, so it would be possible to manage the view migrations in regualr DJango Migrations. https://docs.djangoproject.com/en/2.2/ref/migration-operatio...

Re: To ORM or Not to ORM

#182

Post by strokirk »

GordonS wrote: post #178
Dayshine wrote: post #167

Earlier quoted context omitted.

>Seems a textbook case of using a view for sharing query logic. Views are just terrible outside of data analyst style work. Unmaintainable, restricted to SQL structures, require absurd hacks or custom dlls, not properly source controlled, difficult to perform performance analysis on, hard to update. You can't combine views easily, you can't cache results, you don't get static type checking.

I don't like to say "you're doing it wrong", but TBH it sounds like you are. > restricted to SQL structures So is your ORM; it just adds a layer of abstraction > require absurd hacks or custom dlls Literally no idea what you're doing that views require anything out of the ordinary, or indeed "custom DLLs" > not properly source controlled Eh? You can store your view DDL files in source control just fine. I mean, they'…

What SQL GUIs do you use that draws graphs for EXPLAIN statements? The ones I've used for OSX (PopSQL, Sequel Pro, TablePlus) hasn't had that feature. Haven't used MySQL Workbench in a few years, so not sure about that one.

Re: To ORM or Not to ORM

#183

Post by Falling3 »

philwelch wrote: post #107
jweir wrote: post #77

Earlier quoted context omitted.

You are absolutely correct. Using both is a very valid options. We use ActiveRecord a lot, and then have custom SQL queries using `find_by_sql` for very complex, optimized joins. It works very well. Rails gets out of the way when we need it to.

Two caveats with find_by_sql: it’s read-only, so no insert or update commands, and it still does column-to-instance-variable monkeypatching on the object level, as opposed to the class-level monkeypatching that’s applied to normal ActiveRecord classes as soon as the DB schema is read.

For the former, there's always ActiveRecord::Base.connection.execute. For the latter, I think it's more complicated than that. There also is object-level mapping even for regular AR usage. If you do something like Foo.select("true as bar"), your Foo objects will have a bar variable available to them.

Re: To ORM or Not to ORM

#184

Post by merb »

bunderbunder wrote: post #97

I've come to a couple conclusions, over the years. First, when you get down to it, the most-valued feature of ORMs is not the "writing queries in some language other than SQL" feature, it's the "not having to write a mess of mapping code" feature. Second, the biggest drawbacks to ORMs all derive from the "writing queries in some language other than SQL" feature. Fortunately, there are tools out there that solve the "…

btw. even dapper writes that it only solves 95% of the problems:

> Dapper's simplicity means that many feature that ORMs ship with are stripped out. It worries about the 95% scenario, and gives you the tools you need most of the time. It doesn't attempt to solve every problem.

so basically stack overflow used a ORM for years. and i think they still use LINQ to SQL in coexistence with dapper.

Re: To ORM or Not to ORM

#185

Post by alexnewman »

Everytime I show up at a company with flask-sqlalchemy, step #1 is to remove it. Raw sqlalchemy is better, but we always end up with SQL. It's probably my personal bias of writing 4 SQL parsers, but I find it better than the alternative. Although it's very nice to have something which can safely map the types

Re: To ORM or Not to ORM

#186

Post by megous »

collyw wrote: post #177
megous wrote: post #61

Earlier quoted context omitted.

SQL injection is prevented by not using user input as a part of the SQL query. It's orthogonal concern to whether to use ORM or not.

You can't avoid use user input. How would you login with a username and password without user input? Django's ORM will sanitize input when you pass it in as raw SQL.

SQL query is a string. (that's where you don't pass the user input, not even quoted/escaped, as a policy) User input is passed "out of band", meaning not as a part of the query string. How exactly that happens depends on the RDBMS's particular client/server interface.

That's all that's enough to avoid SQL injections. And it has nothing to do with ORM.

Re: To ORM or Not to ORM

#187

Post by megous »

34r45sdg wrote: post #89
megous wrote: post #61

Earlier quoted context omitted.

SQL injection is prevented by not using user input as a part of the SQL query. It's orthogonal concern to whether to use ORM or not.

I disagree, and SQLi is not simply prevented by avoiding user input. There are many cases where you need to use user input for a SQL query and its a valid pattern, and can be done securely with Prepared Statements or Parameterization. Using an ORM discourages you from writing SQL queries and it also automatically parameterized queries. This is a good thing! In fact, from experience, the single easiest way to mitigate…

ORM builds on top of mechanisms for prepared statements or parametrization.

Yes, ORM API can perhaps limit developers to such an extent, that they can't construct SQL themselves, and thus can't make the mistakes leading to SQL injection.

Re: To ORM or Not to ORM

#189

Post by megous »

macNchz wrote: post #69
dpcx wrote: post #64

Earlier quoted context omitted.

Until you want to be able to sort and filter and do all of the other things that most CRUD applications do. Once you're there, if you're doing SQL directly, now you're manipulating strings in your code, instead of being able to build some kind of object that then generates a query for you.

Yeah if you’re doing lots of CRUD that approach quickly becomes an exercise in writing your own (bad, buggy) ORM.

Not at all. The only cumbersome thing about SQL, that is common enough and unpleasant to always spell out by hand are basic INSERT and UPDATE commands.

I use a set of 3-4 functions that I reuse pretty much in all my programs for this. It's no ORM, as it doesn't map classes to tables/data in the database. It's just a shortcut to generate and execute INSERT/UPDATE commands on arbitrary tables with arbitrary columns.

Also it has zero bugs, because you can hardly create bugs in something so simple.

Re: To ORM or Not to ORM

#190

Post by imtringued »

halis wrote: post #158

I was pretty excited when I originally learned about ORMs. But I quickly found that their usefulness is very limited in the enterprise, where most of my career has been spent. You will likely find, as I did, that enterprises literally have thousands of databases of all kinds and many crazy data models. They have tomes of huge SQL procedures that are still to this day running their business processes. Maybe even a nic…

I have written several queries that span hundreds of lines, primarily for "advanced" search functionality that lets you search by a huge array of potential parameters. Without using the query builder provided by the ORM the code would become intractable to mortals quickly. Having several dozen different parameters that are only set under certain special conditions is much easier if you can use your regular programming language to evaluate those conditions as opposed to writing everything in one big SQL statement even with named parameters.

Writing code like this here really gets old:

q = query(SELECT a FROM table WHERE (:PARAMETER_1_DISABLED OR table.field q.setParameter(PARAMETER_1_DISABLED, condition)

q.setParameter(PARAMETER_1, parameter)

.. 20 other parameters

compared to just

Type.list() {

  if(condition) {

    field
}

Also please don't tell me to avoid this problem by concatenating the SQL conditions to dynamically create the query... The end result is still ugly and might even introduce security problems.

Post reply on HN