5 ms·

> the fundamentals haven't changed I believe they have, because we've gotten so much better at ORMs. ORMs get a bad rap because people use them terribly (and i

• puffoflogic 4y ago
  > A good ORM user is thinking in SQL but writing in C# (or whatever your business layer is in). That doesn't sound like metaprogramming; it sounds like insanity brought about by bureaucratic limitations on language choice.
  • fifilura 4y ago
    To be fair, there is one important thing the ORM brings though. Sanatizing inputs.
    • zanecodes 4y ago
      Don't sanitize your inputs; parameterize your queries instead.
      • Tostino 4y ago
        I see those as two different solutions to two different, but slightly overlapping problems.
        • pessimizer 4y ago
          Going to keep those problems a secret?
        • Lvl999Noob 4y ago
          Imo, the problems are the same but the conditions are different. If the query is used many times, parameterize it. If the input is used many times, sanitize it. If both are used many times, parameterize.
      • robocat 4y ago
        Parameterising works for individual fields in a statement. However for complex queries (the reason for the meta-programmimg comment) you can’t always parameterise the additional subqueries/tables/fields. You can use stored procedures, but that just shifts the necessary code from one language to SQL, and the SQL doesn’t have a robust library you can just use.
  • goto11 4y ago
    It should be "thinking in relational algebra but writing in C#". Arguable Linq with C# represent relational algebra better than SQL.
    • fijiaarone 4y ago
      But not more efficiently. Working with small datasets in memory can be fast, but larger datasets consumes more memory and cpu. The purpose of a database is to efficiently store and retrieve data from disk — and limiting it to only the data you need. Most database interactions are also over a network, which is always slower than (and in addition to) disk retrieval. This should not be done except when you cannot fit the data on disk (or work with it in memory) on the same system. Services should not be separated except for the same reasons (exceeding computation or memory) for the same reasons (network, disk latency). We used to perform billions of complex computations in seconds reading from slow disks and slow systems with much smaller memory footprints on single systems with single cpus. This article is a parable for the consequences of not understanding that. The Google and Amazon and Netflix etc white papers are about organizations that build solutions for applications that cannot fit on disk or in memory or be handled by the computation of a single system — and do not apply to 99% of application architectures that use them, including those developed by Google, Amazon, etc.
  • feoren 4y ago
    Consider a function that takes an IQueryable<T> (for any T) and connects it with your change tracking logic to return an IQueryable<Tracked<T>>, or connects it with your comments system to return an IQueryable<Commented<T>>. I can take a query of (nearly) arbitrary complexity represented by an IQueryable<ComplexModel> and turn it into an IQueryable<Tracked<Commented<ComplexModel>>> in one line, while still keeping the result as a single SQL statement. If you hate that type, note that it's almost never explicitly written out like that (thank you, 'var' keyword). On the other hand, your databases have a "Comments" field in every table; a "LastModifiedBy" in every table. Your database tables grossly violate the single responsibility principle: every single cross-cutting concern is represented in every single one of your "primary" tables. (Level up: every concern is a cross-cutting concern.) Your databases have association tables between X and Y for every primary data type X and every cross-cutting concern Y, leading to a combinatorial explosion of redundant tables. Your SQL queries/views/procedures/triggers are repetitive and full of boilerplate. If your client told you they needed you to change how change tracking is done in your system, you'd have to touch nearly every single SQL module in your entire system. For me, I need to change the implementation of IChangeTrackingSystem, and that's literally all. All of my other queries don't have to know about the change, because they're simply composed together with whichever IChangeTrackingSystem is in place. Show me how to do that in T-SQL and I'll reconsider my position. Now that I've tasted this fruit, the old way of doing things sounds like insanity to me. You're being needlessly zealous and close-minded here. Edit: And another thing! (Shakes fist) Much of the beauty of modern programming languages is their adaptability to whichever domain you're working in. We no longer need domain-specific languages for every different task, because we can embed those languages inside our parent language; then we don't have to reinvent static typing, write a new IDE, and learn decades of programming language design before we can start on our actual business logic. So, yes: When writing data access logic, you should be thinking in SQL (or generic relational logic) but writing in C#. When writing a game renderer, you should be thinking in linear algebra, but writing in C#. When writing a payroll processing system, you should be thinking in payroll, but writing in C#. When writing a chemical engineering toolbox, you should be thinking in molecules and reactions and units, but writing in C#. This way, anyone who knows C# is already halfway (yes, only half) toward being able to maintain your system. If you insist on using a DSL for every single one of these tasks, 80% of your time will be spent on context switching and trying to get them to talk to each other correctly and correcting issues in the DSL itself. Of course I do often write raw SQL as views and scripts, and of course I write TypeScript and HTML and CSS/SASS when working on a front-end (although I usually "think in HTML and write in TypeScript", not surprisingly). But that's mostly for development and maintenance; not for core business logic or library development.
  • robocat 4y ago
    Let us assume you accept that writing SQL is better than writing the equivalent machine code. The “meta-programming” is stating there is a “language” representing their problems that is better than SQL. If we call that better framework(language) Blub[1] then that is probably a good metaphor, rather than causing people to get triggered by the generic ORM tag. [1] https://www.benkuhn.net/blub/ https://www.benkuhn.net/blub/
• ivanhoe 4y ago
  Also databases (yup, MySQL I'm looking at you) got better at handling subqueries quickly, which makes the job of ORMs a lot easier.
  • karmakaze 4y ago
    It's still (5.7) really bad sometimes. A simple `IN (subquery)` can sometimes run much better if the subquery is fetched and another round-trip query is made using literal values for the `IN (...)`. I'm sure there are plenty of other 'deoptimizing' query patterns that shouldn't be.
• karmakaze 4y ago
  A problem that may ORMs have is thinking/working with row-expressions rather than sets. If everything is expressed in plural sometimes with zero or one, other times many, then the N+1s mostly go away. The same goes for many interfaces/APIs that have single and multiple forms, only make the multiple forms and have callers call it with [single]. Related pet peeve: calling tables by plural names. The table (or any relation) should be named for the set of X rather than thinking of it as Xs.
  • caseyohara 4y ago
    I agree with your pet peeve. ActiveRecord (Rails ORM) encourages by convention that your tables are named in the plural, and it sometimes drives me crazy. When writing queries outside the ORM, I end up aliasing tables/relations to the singular so the query is more sane to reason about, e.g.: > SELECT person.id, person.name, person.age ... FROM people person JOIN ... Which reads so much nicer to me than: > SELECT people.id, people.name, people.age ... FROM people JOIN ... (obviously a somewhat contrived example because of the people/person inflection that makes it awkward already)
  • feoren 4y ago
    > A problem that may ORMs have is thinking/working with row-expressions rather than sets. Absolutely. There's a reason that the famous Out Of The Tar Pit[1] paper identifies relational algebra as the solution to many programming woes. Thinking in sets instead of individual items is extremely powerful, when using an ORM and also in general. If an ORM makes this hard, use a different one (hopefully there is a better option). > Related pet peeve: calling tables by plural names Agreed again! Based on my limited observations, this seems like a big cultural difference between "database people" and "software people". The people who spend most of their time working directly in databases (and trying to basically write fully fledged business applications entirely in the database layer) seem to think of tables as big containers. If you labeled a box full of people (or a binder full of women?), you'd probably label it "People". Whereas "software people" tend to think of a database table as a definition of something, more like a class or type. Clearly the correct label for that definition is "Person". Actually the latter is also wrong for a different reason: that is not how nouns work. But that's a story too long to fit in this comment. [1] Out of the tar pit (warning: direct link to 66-page PDF): https://curtclifton.net/papers/MoseleyMarks06a.pdf https://curtclifton.net/papers/MoseleyMarks06a.pdf
• Izkata 4y ago
  > A good use of an ORM looks like a metaprogramming environment for conveniently building syntax trees that get converted into intelligent SQL. You know it's working well if the SQL looks somewhat like you'd write yourself and you can build one SQL statement with multiple layers that are abstracted from each other (in C#, think about passing IQueryables around). The structures are parsed into SQL and executed very explicitly only at the end of the chain, do a lot of work, and never produce SELECT N+1s. A good ORM user is thinking in SQL but writing in C# (or whatever your business layer is in). Sounds very similar to how Django (python) wants you to pass around QuerySets. It's very easy to set up an initial query with joins/etc, then pass the QuerySet into multiple functions to filter it in multiple different ways (each filter creates a new instance, so you're forking the original set and don't have to specify the joins multiple times), but the query itself is never actually run until you try to read from it.
• yamtaddle 4y ago
  > I believe they have, because we've gotten so much better at ORMs. This has almost nothing to do with what the parent was describing. Poor ORM use (or poor ORMs) introduce a different set of ways to mess up performance. [EDIT] OK, this isn't entirely fair, or at least I didn't explain it well enough (no, it's not getting downvoted, I just decided I'm not happy with it). The problem in question is, at its heart, developers not realizing what they should be letting the database do, and perhaps not even realizing what it could do, or deciding they shouldn't let the database do it for some probably-misguided purity reasons or whatever—ORMs generating more-efficient queries or exposing more features is great and does help with the problem of straightforward, natural use of ORMs sometimes resulting in poorly-optimized queries, but doesn't fix the problem of developers not knowing that a block of logic in [programming language of their application] should have been left to the database instead, whether that's achieved by hand writing some SQL, writing a stored procedure, or directing the ORM to do it. It's a little related in that hopefully better ORMs will result in ORM-dependent developers learning more about what their database could be doing for them, or being more willing to poke around and experiment with the ORM since it's more-pleasant to use, but I'd expect that effect to be pretty marginal.
• dagss 4y ago
  To use SQL (or rather, push compute to the database) well you need to think in sets, indexes, etc; the primitives you work with is rather different from the ones you usually use in C#. You need to switch mode of thought anyway. If a really good language for that happens to be expressed in the language of the C# AST -- instead of some new syntax -- that would be fine with me. I do not see a big difference. But since one needs to switch mode of thought anyway, a new high level language that compiles to SQL and would be usable across all backend languages I would like slightly better. But, whatever fixes the problem of allowing pushing computation to the database without all the warts in SQL I am all for. Until that really gets a bit further than today I prioritize writing SQL over a bit too leaky abstractions.
  • int_19h 4y ago
    A separate high-level language just for queries doesn't make much practical sense, since queries are normally surrounded by plenty of other code. OTOH something like C# LINQ, which, on one hand, plays nicely with the rest of the language, and on the other, can be directly mapped to SQL (without ORM and other impedance-mismatch-inducing layering) is great. But, necessarily, language-specific to ensure tight integration.