6 ms·
I think ORMs are a great tool to get something off the ground quickly. Like with most tools you will hit a point where they make things more difficult and then
by maxxxxx 10y ago
I think ORMs are a great tool to get something off the ground quickly. Like with most tools you will hit a point where they make things more difficult and then it's probably time to switch to SQL only or mix SQL with ORM especially for performance critical queries. In most applications I have seen the ORM provided a lot of value but there were cases where it needed to be augmented with raw SQL.
I never understand why people want one or the other exclusively. Both have their place.
I think stored procedures would be very useful if they integrated better with source control and the app code. Maybe we need an ORM for stored procedures that automatically creates stored procedures from the project code?
- ams6110 10y agoIt's quite trivial to keep stored procedure code in source control with the app code or in its own repo. I am always mystified when I hear the complaint that this is difficult because in my experience it's no more difficult than managing any other code in a SCM repo.
- jjoonathan 10y agoIt's not only easy to manage stored procs separately, it has huge advantages: you can play with your SQL in an IDE built for the task with luxuries like real-time error highlighting, profiling, debugging, and auto-complete, and in some cases you can even iterate on your code without restarting your application (just update the proc).
- brightball 10y agoMy biggest concern has always been around zero downtime deployment with stored procedures. Your database has to work with both the old and new versions of web code during the deploy in case of rollback to make that process work and that always struck me as the weird edge case that makes things tough. If a procedure never changes or remains backward compatible then it should still be fine though.
- platz 10y agoGive the new proc a different name (e.g. append a version number). On the n+1 deployment, clean up the old proc.
- Nullabillity 10y agoAnd so you've reinvented the version control system on top of your deployment system, on top of your version control system. That also sounds fun if you use the proc from more than one location...
- platz 10y agoZero downtime deployment with stored procedures should not be confused with "version control". It's not a version control scheme; once deployed the procs are never updated.
- brightball 10y agoYea, I suppose backwards compatibility is really the best policy there. If a proc is going to be updated to be more efficient it's one thing. If it's going to change what it does then we just need a new proc.
- platz 10y agoYep. (if there are schema, changes too, zero downtime & backward compatability is difficult to pull off, but thats a chore for any strategy )
- greydius 10y agoIn my limited experience with the enterprise world, the problem with using stored procedures was that the devs had no control over the database. We could have come up with some nicer solutions to certain problems, but no one wanted to deal with the bureaucracy necessary to create and maintain parts of our applications within the database.
- nickpeterson 10y agoAlso Java and C# developers often produce bad SQL procedures because they tend to think procedurally about problems.
- Something1234 10y agoWhat would be the better thought process for writing sql procedures? It seems like PL/SQL is fairly procedural.
- nickpeterson 10y agoThe rule of thumb would be, use SQL statements to model and execute business logic, not cursors/variables. While the execution is still procedural, the goal is to make all the decisions 'at once' rather than explicitly looping. Behind the scenes a similar set of steps are taken, but the dbms is free to perform the operations as it sees fit.
- maxxxxx 10y agoI think if they were better integrated with the IDE and easier to debug then they would probably write better stored procedures.
- ZanyProgrammer 10y agoAs a C# dev myself, fuck stored procedures, to be quite honest.
- 10y ago
- fishtoaster 10y agoI completely agree. 90% of the queries in my app are no more complex than selecting from a table with a simple condition. I definitely find users = User.where(has_foo: true).limit(10) to be a lot more readable than rows = connection.exec_query("SELECT * FROM users WHERE has_foo=true LIMIT 10") users = rows.map { |row| User.build(row) } (And that's an example with no user-provided input) Likewise, any app of sufficient size seems to end up with a handful of queries that really are a pain (or impossible) to cram into an ORM. Trying to do so would result in an unreadable mess, and using raw sql improves the situation immensely.
- zepolen 10y agoHere's the thing, anyone who knows SQL will find the second one readable, and only Ruby programmers who have used ActiveRecord will know how the first one does.
- mattdw 10y agoReally? connection.exec_, .map, .build – there's a lot more non-SQL going on in the second example than the first. The first may be syntactically far from SQL, but the use of familiar vocabulary makes it pretty understandable from a SQL point-of-view.
- platz 10y agoAnd what are the connection and allocation semantics of the first? (Yes, the second way is more explicit.)
- deleted 10y ago[deleted]
- prodigal_erik 10y agoUnfortunately that argument works both ways, and we are saddled with a majority of mediocre developers who know an ORM but can barely read trivial SQL and can't really write it at all.
- 10y ago