4 ms·
You should follow the conventions that make life easier in the rest of your tooling. The fact is, you're probably going to be issuing more SQL via abstractions
by barrkel 9y ago
You should follow the conventions that make life easier in the rest of your tooling.
The fact is, you're probably going to be issuing more SQL via abstractions like ORMs or querying libraries than raw SQL. If you need to work against the grain of those libraries to map your model, what upside are you getting?
If most of your data is queried via ActiveRecord, for example, you should use plural table names.
- walshemj 9y agoDon't use an ORM is the answer just put in the effort to lean SQL
- matthewmacleod 9y agoThat is a weird middlebrow dismissal of a response with essentially no value to anybody. Use an ORM when appropriate; when using one, follow its conventions. Don't use an ORM if it's not appropriate. This is much better advice.
- 11235813213455 9y agoit's better only if you replace "appropriate" with more insights
- hluska 9y agoI'm north of 40 now, so while I both love and agree with your advice, I'd like to add a caveat. Even if you are lucky enough to be able to use an ORM for your entire career, you will still be well served by learning and truly understanding SQL. In that sense, I'd argue that an ORM is only appropriate if your understanding of SQL and database design are strong enough that you can understand what's happening under the hood if/when everything goes to shit!! :)
- nurettin 9y agoSolving problems with Postgres is wildly different than solving them with mssql informix or Oracle. So I would say "there is no SQL".
- hluska 9y agoI don't agree with that. Once you understand the basics of building performant databases/queries, that knowledge will persist across databases. There are small differences in syntax and features, but any remotely competent person should be able to pick those up while they go, provided they understand what happens under the hood when they execute SQL.
- irrational 9y agoIn what way are they wildly different? I spent 15 years working in Oracle and the last 2 years working in Postgres. They are slightly different (Postgres adheres to the standard much more closely than Oracle), but 95% of my knowledge of SQL transferred from one to the other.
- nurettin 9y agoA simple example would be "how to select groupwise maximum" where Postgres would respond well to a correlated subquery and Oracle would respond well to a window function and derived table. The engines and optimizations are different.
- matthewmacleod 9y agoI absolutely agree with that! An ORM is an abstraction of a concrete system. For very simple use cases, that’s fine. For anything even slightly complex, the ORM becomes a tool that you can use to enhance code readability, or reliability, or modularity. But it’s essential to know what is going on underneath before using that abstraction, much like most other systems. There are too many times I’ve witnessed a less experienced developer build a shockingly expensive n+1 query using an ORM to doubt that :)
- deburo 9y ago>Use an ORM when appropriate; when using one, follow its conventions. Don't use an ORM if it's not appropriate. It's just as vague. If you know of a good article that goes over the details, that'd be cool :^)
- deleted 9y ago[deleted]
- barrkel 9y agoThis injunction doesn't really scale across a team. I've spent weeks of my life tuning SQL, to the point of writing SQL generation libraries to effectively override the database optimizer when it consistently makes poor decisions in specific use cases. But I don't expect the rest of the team to know SQL as well as I do. When I'm writing or generating SQL, I don't really care much what the naming convention is. If it's consistent, then SQL is easier to write. Consistency is more important than the specifics of any conventions.
- btilly 9y agoIt is true that most of my SQL goes through ORMs. However my most complicated SQL is always constructed by hand. Furthermore note the point about how often applications get rewritten against the same database. You should not assume that future code will use the same ORM that you are using now.
- rockostrich 9y agoIt depends on the situation. If the queries you're writing deal are heavily integrated with application models and logic, then using the ORM is probably the way to go (especially with the ORM does some client side caching and other optimizations). Of course, if you start to see that the ORM's queries underperform compared to raw SQL then you should check to see what SQL the ORM is spitting out. I've seen SQLAlchemy create some very poor queries compared to what would be expected and ended up writing parts of those queries in raw SQL, but those cases are pretty uncommon when most of the logic for the application is simple gets/updates with filter/join conditions.
- btilly 9y agoIt does depend on the situation. My experience has been that transactional code is usually best written with an ORM, and complex reporting code winds up better with SQL.
- barrkel 9y agoHand-constructed SQL, though, can cope with any naming scheme. And I'd argue a consistent naming scheme, using tooling as a forcing function, is better than inconsistencies you'd get with a big team of people writing their own SQL.