News for hackers. Stuff that matters.
Re:What ORMs have taught me: just learn SQL (2014) by jtchang on Sunday December 17, 2017 @10:53PM (#15949274)
Why are we even having this debate? There are some ORMs that bring so much value to the table that you'd be stupid not to use them. Example being Django's ORM or SqlAlchemy.

Also be specific in what ORM you are comparing to raw SQL. Are you talking about Hibernate or SQLAlchemy. Are you talking about a query builder?

Good ORMs help tremendously with maintainability and security. They also let you drop down to raw SQL when needed.

I don't think rails would have been as popular if you had to use SQL.

Re:What ORMs have taught me: just learn SQL (2014) by djrobstep on Sunday December 17, 2017 @11:00PM (#15949297)
I've used SQLalchemy a lot. I've even written a keyset paging extension for SQLalchemy.

But recently I've switched to writing stored procedures and calling them directly, instead of going through an ORM for everything... And it's so much easier.

Re:What ORMs have taught me: just learn SQL (2014) by wasted_intel on Sunday December 17, 2017 @11:07PM (#15949332)
Keeping logic in the database like that means you can’t version those procedures alongside the rest of your code. That’s a pretty big downside.
Re:What ORMs have taught me: just learn SQL (2014) by sbuttgereit on Sunday December 17, 2017 @11:35PM (#15949458)
Of course you can, at least I do.

In the databases I've worked with extensively (PostgreSQL and, somewhat in the past, Oracle) Store Procedures are routinely versioned controlled as part of the application, just these bits of code are in a different language than the rest.

The creation of functions/procedures is not tied to state of the database in quite the same way as tables are; the functions/procedures, where they care about the data, do need to recognize the table structure and changes to that structure, but that's no different than any other of the application code which makes use of the data in the database.

I think where many people get caught up on this is that they do something like migrations to get code, including procedural code, into the database... but that's not the only game in town. And really, given what's possible with databases today, I'm not sure migrations are even the best way anymore.

Consider a tool like: http://sqitch.org/ which facilitates not treating stored procedures as migrations, but rather as individual files which change just like any other code.

There are ways to accomplish having good version control on the table/structure side as well, which again, is something you lose with the migration tools I've worked with.

Anyway, I just don't buy this argument.

Re:What ORMs have taught me: just learn SQL (2014) by vectorpush on Sunday December 17, 2017 @11:14PM (#15949365)
Sure you can, just store them in .sql files and have your CI system auto deploy them.
Re:What ORMs have taught me: just learn SQL (2014) by programmarchy on Sunday December 17, 2017 @11:18PM (#15949376)
Sure you can. SQL is just plain text, so keep all your create table scripts in your repo, along with deploy and rollback scripts that you’d use to extend or migrate your schemas.
Re:What ORMs have taught me: just learn SQL (2014) by jsmeaton on Sunday December 17, 2017 @11:12PM (#15949357)
`create proc if not exists get_customer_v1243(params)`

.. I've seen this.

Re:What ORMs have taught me: just learn SQL (2014) by GFischer on Monday December 18, 2017 @10:17AM (#15952160)
Microsoft has SQL Server Data Tools which can be integrated into your favorite SCM (we use VSTS but we'll switch to Git soon), can have automated deploys (which we don't) and in general can be a part of a modern development lifecycle.
Re:What ORMs have taught me: just learn SQL (2014) by 0xCMP on Sunday December 17, 2017 @11:14PM (#15949364)
I mean you could, but it’d need to be through some kind of migration system to update it.
Re:What ORMs have taught me: just learn SQL (2014) by other_herbert on Sunday December 17, 2017 @11:25PM (#15949414)
Flyway for Java... For stored procedures you use the repeatable syntax that way it checks the checksum of the file and if it doesn't match what is in the migration table it will run it... Easy and always up to date... That's with Java anyway...
Re:What ORMs have taught me: just learn SQL (2014) by zkomp on Monday December 18, 2017 @02:37AM (#15950094)
There are several such tools, which are specialized and thus tend to perform a better job than orms (who try to do everything)

But I would recommend writing, reviewing and deploying migrations by hand, esp for critical parts of the schema (automatic tools are almost guaranteed to get something wrong, with locking etc)

Re:What ORMs have taught me: just learn SQL (2014) by BurningFrog on Sunday December 17, 2017 @11:31PM (#15949437)
One problem with having logic in the DB is that it's much harder to scale if you need 10x more computing power.
Re:What ORMs have taught me: just learn SQL (2014) by ako on Monday December 18, 2017 @01:10AM (#15949782)
Moving the definition of your sql from your app to the database doesn't impact scalability, as in both cases the database needs to perform the query.
Re:What ORMs have taught me: just learn SQL (2014) by BurningFrog on Monday December 18, 2017 @01:24AM (#15949841)
We're talking about stored procedures, which are executable code/business logic.
Re:What ORMs have taught me: just learn SQL (2014) by ako on Monday December 18, 2017 @04:45AM (#15950542)
Yes, and if all your stored procedure is doing is execute a query and return a cursor to the result set, it's using just as much database cpu as with a regular query.
Re:What ORMs have taught me: just learn SQL (2014) by ckarmann on Sunday December 17, 2017 @11:42PM (#15949486)
Scaling is like optimization in general: not everything will need to be able to scale, and even if they will, they often do not need to scale yet.
Re:What ORMs have taught me: just learn SQL (2014) by zkomp on Monday December 18, 2017 @01:35AM (#15949885)
I disagree. ORMs dont bring value, they dilute value and piles abstractions upon apstractions. Your application becomes hard to maintain, database hard to refactor, queries slow.

ORMs seems nice for simplified problems but becomes a horrible mess for real problems imho...

I've worked on some rails apps, and the ORMs caused more problems than they solved...

Re:What ORMs have taught me: just learn SQL (2014) by gtsteve on Monday December 18, 2017 @07:32AM (#15951187)
A few years ago, I worked on an application which would occasionally run into performance issues. Every time the solution was to rewrite the Entity Framework usage into standard SQL. Sometimes it would generate the strangest but somehow legal queries which would be challenging for SQL Server to optimise.

For my next app, I just started using SQL only and never looked back. Simple queries are easily generated with a nice internal API (SELECT * FROM Table WHERE ID = X, etc).

However, more complex selects are all written using hand-written SQL. New developers sometimes find this a bit strange, especially the younger ones, but nobody can fault the system's speed.

Re:What ORMs have taught me: just learn SQL (2014) by normaljoe on Monday December 18, 2017 @12:44AM (#15949688)
Django's ORM is actually a good example of where identity is an issue. It lacks composite primary keys. If I have a dependent table that has my real ID, say an order number that is a varchar I have to join to the parent table to do lookups by the order number. I can't doing something like key(order_number, line_number) so I end up hydrating a parent object for operations that only require operations on the dependent objects.
Re:What ORMs have taught me: just learn SQL (2014) by nikisweeting on Monday December 18, 2017 @12:46PM (#15953527)
It doesn't have to hydrate the parent object, just use .only('relation_id').
Re:What ORMs have taught me: just learn SQL (2014) by kamyarg on Monday December 18, 2017 @07:31AM (#15951185)
an ORM does not have to solve every single problem you have, write your raw sql and run it.
Re:What ORMs have taught me: just learn SQL (2014) by minitech on Monday December 18, 2017 @01:20AM (#15949820)
> Good ORMs help tremendously with maintainability and security.

ORMs don’t help with security compared to query builders or even typed text.

Re:What ORMs have taught me: just learn SQL (2014) by icebraining on Monday December 18, 2017 @08:18AM (#15951384)
Security is not just about injection. The ORM we use automatically applies security access rules to the queries, for example.
Re:What ORMs have taught me: just learn SQL (2014) by zimpenfish on Monday December 18, 2017 @08:39AM (#15951490)
> The ORM we use automatically applies security access rules to the queries, for example.

But surely that's better handled in the database itself?

Re:What ORMs have taught me: just learn SQL (2014) by icebraining on Monday December 18, 2017 @09:22AM (#15951750)
How would you apply user-configured security rules in the database?
Re:What ORMs have taught me: just learn SQL (2014) by zimpenfish on Monday December 18, 2017 @11:46AM (#15952967)
Good question. It would depend what kind of rules they were and how they were configured. Wasn't specified that they were "user-configured" in the original statement, though.
Re:What ORMs have taught me: just learn SQL (2014) by icebraining on Monday December 18, 2017 @12:06PM (#15953136)
Fair enough, ours are :)

The kind of rules we use are boolean expressions that identify which rows the user's groups can read/write/delete. The ORM automatically combines all the rules as a single expression and applies it to the query.

Re:What ORMs have taught me: just learn SQL (2014) by zimpenfish on Monday December 18, 2017 @12:23PM (#15953311)
As best I can tell, Postgres lets you do this albeit constraining your boolean checks to things you can efficiently do in the database. But then I can definitely see how this could get unwieldy very quickly.

https://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.h...

Re:What ORMs have taught me: just learn SQL (2014) by icebraining on Monday December 18, 2017 @12:40PM (#15953467)
I know about row-level security, but I don't think the rules can be dynamically loaded.