91–100 of 305 posts
Just write the SQL :) - relational algebra is not that hard...
Earlier quoted context omitted.
Don't parameterized queries provide all the safety one might need?
Parameterized queries are to prevent injection. They don't tell you, at compile time, when you're building an invalid/unrepresentable SQL query.
Sure they do, with a proper library. Haskell's persistent library does this very well.
Earlier quoted context omitted.
You can write well written modular SQL that supports changes and can be read by a competent developer.
Not in my experience. Please show me actual examples of "well-written modular SQL" in a real application.
Earlier quoted context omitted.
My 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.
Give the new proc a different name (e.g. append a version number). On the n+1 deployment, clean up the old proc.
That also sounds fun if you use the proc from more than one location...
Earlier quoted context omitted.
> An ORM provides type checking at your application layer This. When composing complex queries, we really want type checking and SQL injection safety.
Unfortunately, ORMs sometimes undermine safety for the sake of convenience. Even ORMs which use parameterized queries (which any sane framework will) may be vulnerable if they build dynamic queries using string concatenation.
Anyway. Type safe queries like QueryDSL is extremely nice to work with.
But as was mentioned, it all boils down to this: There IS NO silver bullet.
You have to learn SQL, and then the ORM tool. And the abstractions will leak, and you will be pissed of sometimes, but It Is Worth It because you will save a lot of development time.
There are some pain points. Large joins where you'd need to eager-fetch a few one-to-many "leaves" at the end of a huge and complex join is a bit of a pain, as the ORM will need to split the joins for efficiency, and there is no obvious way of reusing the complex part between the calls available to the user of the ORM.
(Like how you could use a temporary table when using Oracle for instance. Nothing should stop a ORM to use that as a join-strategy though, when I come to think of it...)
The other obstruction is mindset. To use a ORM efficiently, the developer need to step away from the data-layer model.
There is no separate data access layer when working with persistent objects. The objects represent the model, and are simply persistent. If they are changed, the change stays.
Preferably, the model should be available to the whole application, and the objects should be changed and used where it is suitable, not restricting access based on that they will trigger a database access. (The important thing should be to keep the model and it's rules together - in some sort of abstraction, not that some things happen to write to a database.)
Earlier quoted context omitted.
> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from y…
How do you manage version-control on stored procedures? Can they be checked in with the rest of the application logic?
One way that works is make sure you make a simultaneous tag of your code and stored procedure that works with it. You really want development to run hand in hand with stored procedure development, not as two parallel processes.
Earlier quoted context omitted.
I 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 sufficien…
In this example, it seems like it could be an issue that you take everything and then only use the first 10, instead of only taking the first 10 to begin with. Is there a way to not make it take them all, like putting the limit parameter in where()?
Ten years ago, there was a blog post every other week bemoaning ORM's. Ten years ago, those posts often had merit. In 2016, this sentiment is outdated. A few points: 1. If you think that using an ORM means you don't have to learn SQL, then you're going to have a bad time. This is where most of the bad press originates... from people who never really learned SQL or their chosen ORM. An ORM provides type checking at yo…
> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from y…
I like to say "ORMs make easy thing easier and hard things harder". What i mean by that is, any simple CRUD operations are much easier in ORM. The hard things, i mean any complex queries that need more than one join you are probably better of writing yourself. In the end i prefer to do inserts, updates and deletes with ORM (or some other database abstraction tools) but most SELECTs i write myself, fetching exactly wh…