33 ms·

I don't want to learn your query language (2018)

• chrisma0 6y ago
  I would actually applaud the proposal to have the EU mandate that SaaS products must expose all my data in a "plug-and-play thing" (in a format queryable using SQL?). I can already download it on most services, but usually get some collection of JSON files...
  • vardump 6y ago
    That might not be feasible, if the data is never in any form resembling a relational database.
  • snovv_crash 6y ago
    Download your personal data as a sqlite database would be awesome.
• thinkingkong 6y ago
  Author seems to take for granted that not everyone knows sql. A friend of mine recently hired a couple devs and asked them to build a web component. They didnt know html. But they were proficient with react.
  • nudpiedo 6y ago
    how can't they know html and were proficient react? it must be an exageration or perhaps they just knew enough HTML or missed propper CSS knowledge. Also as far as I know React has native components, not sure how much HTML decoupled are these.
    • alexc05 6y ago
      This would be my guess. There are a lot of things in CSS that need specialist knowledge.
  • macspoofing 6y ago
    I question their proficiency with React (for web). There are some abstractions you just have to be aware of. If you're a web developer you have to have a good mental model of the DOM and how web frameworks interact with it ... because even if you work on React exclusively, the React component abstraction is not perfect and the DOM is going to leak through. >Author seems to take for granted that not everyone knows sql. Then ORM is incredibly dangerous for them to use. ORM frameworks are useful but their abstraction of SQL is very leaky. You have to have a reasonable understanding of SQL in order to use an ORM framework effectively, otherwise you will get yourself in trouble. I bet this is where a lot of ORM criticism comes from ... namely developers who don't know SQL and therefore use ORM as crutch when working with a relational database. They can create a very nice object model with an ORM framework that is shit when it comes to the actual queries it generates.
  • arcturus17 6y ago
    I seriously doubt you can be proficient with React without knowing HTML. Even if you use a framework like MaterialUI which abstracts all the HTML tags into React components, working on layout creates a recurring feedback loop between source and output code in the browser / devtools. If you've done that for some time, you end up knowing a fair bit of semantic HTML. So I suspect either your friend was exaggerating, the devs were lying about their React proficiency, or maybe they work on everything but layout in React (highly unlikely). At any rate, forgive my cynicism, but I can't help but feel that this is a classic HN cheap shot at React developers at large. ORM to SQL may be another matter. I've been using the Django ORM in the last couple of months and it's true that I haven't touched SQL but once. I know SQL but I've barely felt my skills progress since.
    • ZephyrBlu 6y ago
      It seems most likely that they actually aren't proficient in React, which is not really surprising to me.
    • hmsimha 6y ago
      I really like this article discussed here recently, which breaks down the new front-end work into front-of-front-end and back-of-front-end: https://news.ycombinator.com/item?id=26160538 https://news.ycombinator.com/item?id=26160538 knowing html is a somewhat nebulous claim, there are so many elements, attributes, and relationships, and the specification is incredibly dense. A design-focused front-end developer may almost exclusively use parts of HTML that a 'back-of-front-end' developer might not even know exist. I do think you could do back-of-front-end very competently and have only a vague understanding of HTML. However, you'd likely also be interacting strictly with a subset of React.
• n_parks 6y ago
  Old discussion on HN (2018) for the same article here: https://news.ycombinator.com/item?id=17890760 https://news.ycombinator.com/item?id=17890760
  • deleted 6y ago
• wccrawford 6y ago
  I understand wanting to stick with a tried and true query language, but it doesn't surprise me at all that a new database technology has a new language. Sometimes things are just not going to work the same. ORMs, though... I tried to get on board with those, and it's done nothing but bite me. As the article says, I end up spending more time in the documentation, and then my boss ends up questioning the choice as well when things (like in the article) happen. In the end, I think the ORM has been as much pain as help, and I'd have been better off just not bothering.
• pizza234 6y ago
  I'm a "hardcore" SQL developer, but still deem ORMs [commonly] necessary. Importantly: > Their alleged benefit is they cut down development time > Let's dispel with the myth that ORMs make code cleaner This is very narrow view of what ORMs do; without them, access to the DB layer becomes a black box. Actually, not even the black box definition is appropriate - APIs to access the DB layer are needed in one way or another, so one ends up writing their own ORM. ORM APIs do a lot more than just executing queries. The first thing that pop into my mind is persistence/state management, but also, composing queries is much more than bashing together a series of strings. EDIT: elaborated on ORM APIs role.
  • nudpiedo 6y ago
    or maybe the ORM of the day doesn't support all the database features you need and you end up using leacky abstractions and less seniors colleagues end up with an OOP-ORMed database mess that would make cry anyone who did in fact learn the DB layout.
    • macspoofing 6y ago
      I think that's where the majority of ORM criticism comes from ... namely the idea (that some may hold) that you don't need to have a good mental model of SQL if you just use ORMs. I can see some people who are intimidated by SQL use ORM as a crutch to work with relational databases. OOP and SQL are different paradigms. An OOP abstraction over SQL is never going to be perfect so to properly and responsibly use an ORM framework you have to understand what's going on under the hood. If you don't, then yes, you will create a mess for anything other than the most trivial use-cases.
  • gnfargbl 6y ago
    The underlying problem as I see it is that SQL is itself a black box, in the sense that the database execution plan does not always trivially follow from the SQL as written. That is a very powerful property for an ad-hoc query language because it means that the user describes what they want, and the database itself takes care of the best way to get it to them. For programmers though, who usually want something predictable and repeatable to happen, it is not always ideal. ORMs try to make SQL seem more programmer-oriented by adding an extra layer of indirection over SQL. Unfortunately, what they actually tend to do is to add an additional black box enclosing SQL's black box, and that usually makes everything worse because it is now almost impossible to reason about how the database is actually going to execute a particular query. There doesn't seem to be an ideal solution to this problem. I think it is why so many databases tend to tweak SQL into something that better fits their implementation detail, and we end up with the explosion of almost-SQL languages that the author is complaining about. Personally, the least-bad solution I have found is the one you mention in your post: write your own SQL abstraction layer every time. At least that way, you can poke your screwdriver into the box and reason about what is going on in there.
• leetrout 6y ago
  I never get the ORM hate for CRUD apps. It gives a centralized place to understand what data is in play in the application and adding a new column to the database means updating a class / struct / type in one location and checking the use of that within the code base. Without an ORM and with poor discipline I end up having to go find every query in the app referencing that table and check if I need to update the query which usually means changing the columns, the ? value placeholder and putting the correct value in the associated values container. It all seems like a lot of tedium and ripe for simple typos in the queries that is easily avoided by using beneficial tooling.
  • amelius 6y ago
    The hate for CRUD apps stems from it being too simple to be called a "high-tech" solution, that's all.
    • arcturus17 6y ago
      Many global apps start as CRUD apps, and only become high-tech with scale. And even at hyperscale companies a lot of software engineering work is probably "basic CRUD" - reading from one system and writing into another, without applying a radical transformation to the data in the process. And even doing that well is quite challenging, which is why the average software developer gets paid well. It's great if your talent allows you to do much more than that, but I wouldn't deride it, and much less hate it, when your own success is likely going to have to rely on competent people around you doing that job extremely well.
  • nobody0 6y ago
    Yeah, as long as it's well designed, like LINQ. ORM is necessary, otherwise we will find ourselves in the old days again, a large part of string concatenations, security problems, etc,.
    • jonnypotty 6y ago
      There is so much code out there to help you do typed params and to stop you having to concat strings to make queries. You don't need an orm for this.
• klingon78 6y ago
  I was sure this post would be about SPARQL; the web as a distributed DB is genius, but a new query language with tuples and specialized servers weren’t the answer I’d imagined.
  • thrower123 6y ago
    I immediately thought of Salesforce and their almost-but-not-quite SQL query language that they have. Or Microsoft's Graph API, which is kind of SQL-y, kind of LDAPy, but not systematized enough that you can be confident anything you try will work without reading the doc on the particular resource you're trying to get.
• macspoofing 6y ago
  I understand the author's frustration, but his railing against ORMs is misplaced. First, if you don't want to use an ORM or your application doesn't warrant one, then don't use one. Also, all ORMs let you do raw SQL because there will be times where the ORM abstraction will be too cumbersome. But to be clear, either you use an ORM or you're going to invent a new ORM because your app doesn't talk in SQL, it talks in Objects - so whatever you do, there will be some mapping between the SQL query response, and objects that you will need to create to pass around in your app. If your app is expected to work with different databases, each with their own SQL flavor, you're going to have abstract that as well. And of course, ORMs tend to have good sets of defaults built in (like proper escapes). So whatever ORM-like framework you build, you'll have to take care to cover that case, otherwise you're back in the bad old world of SQL injection.
  • otabdeveloper4 6y ago
    > your app doesn't talk in SQL, it talks in Objects Speak for yourself. My app talks in dataframes.
    • macspoofing 6y ago
      Then maybe ORM isn't for you?
      • otabdeveloper4 6y ago
        Exactly. I'm just complaining about the "everything is a webapp backend written in a scripting language" mentality that pervades everything in IT now.
    • TuringTest 6y ago
      But aren't dataframes just objects where each attribute returns an array of values instead of a scalar?
    • pwinnski 6y ago
      My app often talks in Objects, but my scripts talk in SQL.
• frettchen 6y ago
  The author notes that SQL has been around since, roughly, 1974 - but MultiValue Query Language/ENGLISH has been around since the the 60s. The choice is clear. /s
  • eternalban 6y ago
    SQL is not merely syntax. There is a relational model that is the actual gem of "70s".
    • grzm 6y ago
      At the same time, it’s important to understand that the relational model is independent of (and prior to) SQL. SQL is by far the most commonly used language implementation and is important because of that, but it also deviates from the relational model in subtle (and sometimes not so subtle) ways. Understanding those differences and how to accommodate for them can be important to understanding how to write schema and queries in more holistic and powerful ways.
      • eternalban 6y ago
        Fair enough. Cue Christopher Date, et al.: https://www.dbdebunk.com/ https://www.dbdebunk.com/
        • grzm 6y ago
          And to be fair, I wouldn’t have found the relational model without SQL. I wish it were more common to write about both in SQL tutorials and such, but I understand the motivation to write about what people can do with an implementation rather than the mathematical and logical underpinnings.
• bullen 6y ago
  What about this: http://root.rupy.se http://root.rupy.se You can view source to see the HTTP parameters or just WireShark it.
  • oftenwrong 6y ago
    This is not as self-explanatory as you seem to think...
    • bullen 6y ago
      :D I guess, did you understand anything?
• spacemanmatt 6y ago
  When I work with non-OO languages, an ORM isn't missed. Rather, flexible language-native data structures are gained. When I first stepped outside the OO world after years in, I couldn't help but notice how much "technology" was just compensating for problems imposed by OO languages.
• dgellow 6y ago
  > I just want my SQL back. It's a language everyone understands, it's been around since the seventies, and it's reasonably standardized. It's easy to read, and can be used by anyone, from business people to engineers. I rely a lot on SQL and in general advocate for it, but that’s too simplistic of a view IMHO. SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic. More than once I’ve been asked to help fix giant SQL queries written by a business or analytics team and that’s a terrible experience. Also, the tooling around SQL is often terrible and almost didn’t evolve during the past 15+ years. SQL queries from another programming language without an ORM means that you do everything by manipulating strings by hand, with zero type safety. Edit: My favorite library in Go is reform, a generator that generates types and implement the Scanner/Valuer interfaces. You annotate your structs, generate the types, add the generates files to git, done. That way you have very limited ORM magic but gain some type safety. And you still have complete control over your SQL queries (which is often frustrating to do with classic ORMs). https://github.com/go-reform/reform https://github.com/go-reform/reform Edit 2: the most promising ORM I’ve seen is Prisma, https://www.prisma.io/ https://www.prisma.io/. I haven’t tried yet, I’m still quite attached to writing most of my SQL by hand because that’s what I know well, but their pitch seems good to me.
  • slcjordan 6y ago
    Interesting. I've been looking at https://github.com/bokwoon95/go-structured-query https://github.com/bokwoon95/go-structured-query but I'll have to look at reform too, now. Thanks.
    • dgellow 6y ago
      Reform documentation isn't always that good but the implementation and generated code is not too difficult to read when trying to understand how everything works. Thanks for sharing go-structured-query, I didn't know about it.
  • fouric 6y ago
    > SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic. This is consistent with my experience, and exactly backward of what you want a language to be - which is good scaling of solution complexity as a function of problem complexity, even (although ideally not) at the cost of upfront effort required to learn.
• projectileboy 6y ago
  It’s probably just a function of the apps I happen to have worked on, but I have rarely (maybe never?) seen ORMs pay for themselves by the end. The classic joke about the business is that they see all of the costs and none of the value. But the developers tend to see all of the value and none of the costs. And the costs of ORM on a project have a very long tail. For most projects, if you really want to control what and how the developers interact with the database, a better approach is to have one or more separate db developers who create an API for the data via stored procedures.
• mtberatwork 6y ago
  > What's worse than data silos? Data silos that invent their own query language. I can't agree more. Bespoke query languages and back-ends are generally only good for two groups: the vendors and consultants. SaaS apps with their own query languages and NoSQL flavored back-ends end up being nothing but headaches for the poor FTEs that have to spend their days in the drudgery of having to figure out anything beyond a basic query.
• jusssi 6y ago
  > I just want my SQL back. Which of them? There are as many SQLs as there are implementations. You can't take an SQL schema from one DBMS and drop it into a different one, without conversion. Also you can't take a query written for particular SQL implementation and expect it to work and return the same results, except for very simple queries.
  • yoz-y 6y ago
    This. The author also acknowledges that some of the languages they complain about are SQL dialects. Often they are simplified versions of the "generic SQL", e.g. AWQL does not have JOINs. Supporting all standard SQL features might be worthless and/or impossible for some domains.
• alexc05 6y ago
  It is a bit falicous to imply SQL is the same everywhere. If you move from postgres to sql server to mysql to oracle you'll see there are major differences in how things are done. Sure, your select and join is mostly the same, maybe with a few different commas here or there. Once you get into complex large data operations though it's a whole new ballgame. Imports happen in wildly different ways, temp tables, choices of inserts vs. updates is a per-platform choice in some cases. On top of all that, an ORM like entity framework (C#) handles things like making sure the statements are prepared in a way that "handles" major security issues like dependency injection out of the box. If you make your team use an ORM you don't have to worry quite as much about someone named ';DROP tables ' signing up to your system. (Or whatever) ORM has it's place. SQL isn't a monolith that everyone* knows. The discussion is worth having, but I think the OP's argument lacks nuance.
  • CommonGuy 6y ago
    You probably meant SQL injection, not dependency injection
  • geophile 6y ago
    I can count the number of times I've seen an app move from one RDBMS to another on zero fingers. This is really not a good motivation for an ORM.
    • mumblemumble 6y ago
      I did it once. The first step was ditching the ORM and replacing it with a well-defined repository layer. Because the ORM was encouraging interaction with the database to be scattered everywhere, and that was making it impossible to understand scope of impact. With the data access layer, though, we had a relatively small, clearly defined chunk of code that we could isolate and test, and that made the migration manageable. We didn't need to ditch the ORM, of course. And, at first, we weren't going to. But we saw that, with things so clearly isolated like that, the ORM library wasn't really making the code any easier to understand, so we decided it wasn't really worth the added indirection or the performance cost. I also worked on a product that simultaneously supported two different DBMSes. It didn't use an ORM, either.
• madsbuch 6y ago
  I am not sure if this is a case against DSLs in general? Eg. the security rules and query lang around Firestore locks down what the develop can ask for in a way that keeps queries run fast. Not doing so could pose a security risk. However, for an on premise setup with just a limited number of trained people it seems reasonable that they should have full power. Ie. I am pro DSLs as they allow to mitigate other concerns.
  • deleted 6y ago
    [deleted]
• luxuryballs 6y ago
  “Maybe European Union can mandate this” tongue-in-cheek? Maybe not but either way, NO! Keep the government out of my computer pls. Oh almost forgot to ask, do you agree to accept all of my cookies?
• sharpercoder 6y ago
  It is very high time for a successor on sql that solves sql problems. Verbosity, local functions, reversed order table/field select, reusability and other big problems should be solved by sql itself. A newer better version, perhaps compiling to backward compatible sql.
• eatonphil 6y ago
  ORMs and SQL at least have the same goal which is to join datasets and perform operations on them. SaaS platforms build _filter languages_ that are (sometimes vaguely) inspired by SQL. I really wish there was a standard among filter languages that was optimized for interactive search. Interactive search is different from application search because in interactive search you don't know what fields there are so you are mostly counting on substring matches. But you still want to be able to include multiple substring matches (AND) and exclude things as you find them that aren't what you're looking for (AND NOT). I think Google's search language is one of the best filter languages out there in terms of simplicity/effectiveness. Splunk's language is good as well (but maybe they're just making up for it in good typeahead support). I'd like a standard for Google's search language with implementations for in-memory filtering in every major programming language and implementations for generating SQL from the filter for every major programming language. Until this happens there's not really much hope for standardizing on filters across applications.
  • dmitriid 6y ago
    > Splunk's language is good as well (but maybe they're just making up for it in good typeahead support). Not just typeahead. A lot of Splunk's power comes from data transformations and filters. get_logs | apply_transform | merge with other logs (which can also be log|transform|filter|transform) | apply more transforms | filter | expose as a specific structure (that is, transform) | filter more This would be anywhere from pain to impossible with SQL.
    • eatonphil 6y ago
      That's awesome. Especially when graphing I desperately wish I could map values in ES.
• MrPowers 6y ago
  Spark lets users query via regular SQL or DSL (Scala, PySpark). The DSLs are generally better to work with. Complex queries can be broken up into composable functions that can be mixed and matched to perform different analyses. Functions are easier to unit test, document, deal with quoting better, can be type safe, etc. Some super-complex queries are still better to write with regular SQL. New query DSLs don't need to reinvent the wheel. They can implement their query capabilities using the Row / Column / DataFrame abstractions that are elegant and familiar in popular projects like Spark. We need an ANSI query engine DSL.
• jacquesm 6y ago
  The bigger problem with ORMs is not that they require you to learn a new language, but that they are abstractions and very leaky ones at that. Typically they add a lot of complexity, caching (which seems like a great idea until you have a lot of fast updating data), make it harder to scale horizontally and so on. They also usually do not have a good impedance match with the underlying storage layers resulting in a lot of tricks to do stuff that would have been easier done by bypassing the ORM, but that then gives you inconsistent data because now you are also bypassing the cache of objects.
• jinkyu 6y ago
  some of those SaaS products aren't "ORM"s and don't make sense to use SQL. Splunk is a good example. arguably it's not SaaS either (looks at massive server stacks full of splunky-town hosts)
• falcolas 6y ago
  SaaS providers who come up with their own unique query languages are evil. I’m looking at you, New Relic, with your NRQL bullshit. Seriously, what is so ambiguous with “GROUP BY” that you had to invent your own keyword for it?
• aquir 6y ago
  "There should be a 30 year moratorium on inventing new query languages." That! Totally agree!
• weeboid 6y ago
  This article would have a lot more impact if it was an unveil for a solution
  • luxuryballs 6y ago
    ha yea, give me your solutions not your problems!
• ghc 6y ago
  And while we're at it, I don't want to learn your programming language. Everyone should just use Java for everything, FFS. /s Seriously, the idea that everything maps well onto relational databases is incredibly misplaced. Not everything is tables, tuples, and relationships. Most RDBMSs graft a million slightly incompatible features onto SQL to attempt to handle all the things SQL doesn't understand, like GIS and full text search.
  • mumblemumble 6y ago
    Fulltext search is its own special thing, but most the other add-ons tend to be performance improvements or syntactic sugar, not filling in feature gaps. The thing that's nice about RDMSes, and the reason why they've been so successful, is that they're built on top of a strong theoretical basis. The relational model is a bit like the lambda calculus. It's simple and relatively easy to understand, but can still provably handle just about anything.
    • ghc 6y ago
      There are actually a number of data models that the relational model doesn't handle well, and it's unclear if it's really even the best model. Back when I was in database research most of our projects focused on domains where the relational model was not a good fit. Some examples of unnatural mappings to the relational model include: - Documents - Spatio-temporal - Graphs - K/V - Trees - CRDTS - Images - Arrays I'm sure there are plenty of others but those come to mind first. Most DB researchers I know would argue that if any database model is equivalent to the lambda calculus it would be graph theoretic model, because all other models can be expressed by graphs, but other models cannot universally express the graph model.
  • pwinnski 6y ago
    I don't understand the original article as stating that all data maps well onto an RDBMS, but rather than if you are using an RDBMS, you should use SQL rather than adding additional layers. If you want to use a non-RDBMS, then by all means, SQL won't be a good fit. I use Amazon's DynamoDB all day, every day, and I wouldn't dream of using SQL there.
• gwbas1c 6y ago
  An ORM is a tool. They have their place: They aren't a replacement for knowing how to use a database; and you need to understand the performance / development time tradeoffs. I had to fight a battle to remove a very slow ORM that was used for a SQLite database with 4 tables. After I removed the ORM from a critical code path, startup went from 24 hours to about 2 minutes. Raw SQL was "worth it" because the schema was brain-dead simple. I more recently optimized some C# Entity Framework code that took minutes to run and brought it down to seconds. In this case, there were many tables and many joins, so going down to raw SQL wasn't "worth it." All I had to do was recognize that the code was relying on lazy-loading, and then I just pre-loaded the entities. BUT: I should point out that ORMs can be extremely powerful. Using a tool like Linqpad with C# and Entity Framework, you can write ad-hoc queries much more easily than SQL.
• geophile 6y ago
  A long time ago, I designed and created an ORM for Java. I brought it to a startup and we made some money selling it. One of the key features was that it went a long way to solving the N+1 problem, which was a unique feature at the time. Obviously, I bought into the whole ORM concept, but I did have doubts in the back of my mind, some of which showed up in this article. You have to retrieve all of the columns needed to constitute an object. There is no subtlety in controlling the SELECT clause. Getting object identity right is hard, (e.g. you don't want to distinct Customer objects representing the same real-world Customer just because you ran two queries that happened to identify the same Customer). Caching is hard, especially when there are stored procedures doing updates and your ORM has no insight into them. Sometimes it would be nice to just write the damned SQL. Schema and/or object model changes are difficult, because you need to keep the two in sync. When I wrote my own database applications, I didn't want an ORM, not even mine. I wanted to write SQL. Having some tools to manage PreparedStatements and Connections would have been nice, but for my own work, I really preferred writing my own SQL. But what finally changed my mind completely about ORMs was a hallway conversation with a developer at a prospect on Wall Street. (I think it was one of the firms to go under in the 2008 crash.) She told me that her resume looks a lot better with "5 years Oracle experience", than with "5 years XYZ ORM experience". This, combined with the doubts that were accumulating in my mind, finally pushed me over the edge. In later years, playing with various web development frameworks, I dreaded dealing with ORMs. It's as the article said: I don't want to learn yet another query language, only to have that translated, badly, into SQL. Just let me write the damned SQL, it isn't that hard. I know exactly what SQL I want to run. Why should I create work for myself in convincing the ORM to do what I already know how to do?
  • coldtea 6y ago
    "One of the key features was that it went a long way to solving the N+1 problem, which was a unique feature at the time." In case someone else is wondering: https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping https://stackoverflow.com/questions/97197/what-is-the-n1-sel...
    • okareaman 6y ago
• lwhi 6y ago
  Abstraction is useful though. Don't tie yourself to specific database system.
  • lucasyvas 6y ago
    This is, of course, good advice. But I'd add that query builder / micro ORM libraries are a better solution as they solve the (mostly common) interface, connection pooling, and multiple db driver parts. This is plenty. One's I've used: knex - node.js sqlx - rust
• Arch-TK 6y ago
  So this article is muddy. The author has a beef with two things and claims to have a problem with a third. The author seems to dislike ORMs for their abstractness and general inefficiency when interacting with a SQL database (this is the nature of an ORM, either stop writing OOP code and as a result stop requiring an ORM, or use a graph database for your graph data (object state)). The author also seems to dislike SaaS query languages or something like that, a topic I don't know much about. This gets all rounded up under the topic of "Query DSLs". When I hear "Query DSL" I think SQLAlchemy Core. A library designed to provide an API to a SQL database which looks like you're writing python (implemented through overloading tricks and some fancy OOP metaprogramming tricks). SQLAlchemy Core is great, the author would not hate it because you're effectively writing SQL without all the footguns. SELECT * FROM user; -> select(m.user) SELECT * FROM user WHERE name == 'test' ORDER BY surname; -> select(m.user).where(m.user.c.name == 'test').order_by(m.user.c.surname) By having python native code you can generate SQL statements using idiomatic python. If you do some kind of coverage tests (no idea what the kids these days call these but the idea is to run all the python code to ensure there isn't a low hanging fruit which would get caught at runtime) you can find 'select' typoed as 'slect' early on in development rather than it ruining your Friday when such a bug gets into production. This also makes parametrized queries painless which prevents many potential security issues with generating SQL. If you want to you can also use the generic subset of SQL this way and SQLAlchemy Core will iron out the creases. But personally I think it's pointless to make a choice of SQL database if you're then going to not use all its special features. It's like writing some kind of polyglot-lang which can be translated to C, Java, Pascal, prolog, Lua, scheme and rust. You pick a language for the task, pick a database for the task too. So it's important to isolate these features mentioned above from "ORM" and whatever the other thing the author was talking about. People use ORMs and they realise they suck and it's important to remember that although the core concept of an ORM is flawed, not all the features which come bundled with an ORM are bad ideas (or at least it doesn't mean that those ideas can't be done well when you are not trying to implement an ORM).
• donatj 6y ago
  For what it’s worth YQL was pretty neat back when Yahoo still had some decent engineering going on. Single SQL-like query language for accessing all of their services. Way way underutilized for how amazingly powerful it was. I actually still had a little bit of code in production using it when they shut down.
• megous 6y ago
  I don't care if other people use ORMs or not, if they don't force me, but yes please on this: 'Every SaaS product should offer a plug-and-play thing so that I can copy all the data back into my own SQL-based database.' Some nice way to incrementally copy/sync my data from some saas into my database would be great. Even a full data dump from some JSON endpoint is good enough if it has stable unique keys on all entities, thanks to UPSERT being pretty good for this kind of data syncing.
• bxrxdx 6y ago
  I agree with this so much omg.
• jariel 6y ago
  More apt: Can we agree on a simple, modern query language that is better than SQL, which is fine but a little antiquated? Because the issue is standardisation more than anything.
• default-kramer 6y ago
  I sympathize with the author -- I also choose plain old SQL whenever possible. But SQL is not a good option. It is the least bad option. I even worked on my own alternate query language, Plisqin: https://docs.racket-lang.org/plisqin/index.html https://docs.racket-lang.org/plisqin/index.html I was never expecting it to gain adoption. But if any SQL alternative ever does gain adoption (in the same way Typescript has for JS), I really hope it looks at least a little bit like Plisqin. Specifically, when joins are values and not language constructs, things get a lot better. Also, please satisfy these 3 rules: https://docs.racket-lang.org/plisqin/research-language.html#%28part._.My_.Ideal_.Query_.Language%29 https://docs.racket-lang.org/plisqin/research-language.html#...
• mkl95 6y ago
  I enjoy using the SQLAlchemy ORM. It makes it trivial to divide a query in several parts that you can mix and match. In my experience, features such as a search engine that supports many filters are much easier to maintain if they are written with an ORM rather than with raw SQL. I don't have a preference for simple queries. On the other hand, I find MongoDB's query language and its popular Python ORMs (Pymongo and Mongoengine) painful to work with.
• thehappypm 6y ago
  I found that Rails' way of doing data access -- which completely hides the SQL from you -- to be really confusing, but people seem to absolutely love it once they get it under their feet. I prefer to write SQL/sprocs. What's the consensus these days?
• GLGirty 6y ago
  This is an essay version of xkcd 'standards'. It's not wrong, but it's not the entire picture. The author is correct, sql does everything they need, cause it's so expressive. (Mysql8 is turing complete with it's domain specific syntax duh-duh-daah...) But that power comes at the cost of scrutiny and composition. ORMS are fish in a barrel--they fail because they are the wrong abstraction. Queries shouldn't be the language, they should be the verbs in the language. With a very restricted query AST, introspection and composition are possible. If queries point to the same table, you can combine them into a new query whose result cells are union or intersection of the input result cells. You can automate joins and subquery indexing. You can recover from a dropped column by replacing references to it with null, and limp by with partial results instead of no results. I agree with the author's hate on DSLs as a substitute for sql... when they have the same level of abstraction. But if you want queries to be lego bricks that snap together, the bricks can't have NP hard surface area. DSLs fill that need.
• lipanski 6y ago
  I recently installed ClickHouse (part of my self-hosted Plausible setup) and had to modify a record inside the database. At this point my only knowledge of ClickHouse was that it comes with an SQL interface. So I opened up a client and typed "SHOW DATABASES" and guess what - it showed me a list of all databases. Then I typed "USE mydatabase" and I was connected to my database. I typed "SHOW TABLES" and got a list of tables, followed by "DESCRIBE TABLE users" and "UPDATE users SET email_verified = true" (FYI I was trying to avoid having to set up SMTP credentials for Plausible). I was able to use ClickHouse without any prior knowledge because the authors decided to based it on a well-known and fairly simple standard instead of inventing their own. It felt as good as building Ikea furniture without checking the manual and it's what user/developer experience should be about.
  • hodgesrm 6y ago
    You've used MySQL, I would guess. Personally I like the superficial similarities to basic MySQL syntax in ClickHouse. MySQL and Sybase T-SQL have always struck me as the friendliest SQL dialects.
• asimjalis 6y ago
  I like SQL. The problem with SQL is that it is a string and simple errors, like an extra comma, are not caught by the compiler/interpreter. Are there good well-thought out libraries that are isomorphic to SQL but can be used with a language like Python or Clojure? For example, I want to write simple SQL queries this way: (-> "table" (cols col1 col2 col3) (where (= col1 10)) (order-by col3 :desc))
  • johannes1234321 6y ago
    If you love C++ Template magic look at https://github.com/rbock/sqlpp11 https://github.com/rbock/sqlpp11 This allows "normal" C++ code, which by the compiler is converted into the query string, allowing code like for (const auto& row : db(select(all_of(foo)).from(foo).where(foo.hasFun or foo.name == "joker"))) { int64_t id = row.id; // numeric fields are implicitly convertible to numeric c++ types } (Random pick from the README)
  • adamkl 6y ago
    I believe Honey SQL might be just what you are looking for: https://github.com/seancorfield/honeysql https://github.com/seancorfield/honeysql
  • adsharma 6y ago
    I wrote such a C++ implementation during my days at Facebook around 8 years ago: https://engineering.fb.com/2016/03/18/data-infrastructure/dragon-a-distributed-graph-query-engine/ https://engineering.fb.com/2016/03/18/data-infrastructure/dr... There is an example under "Functional programming primitives". It was a C++ implementation that fell victim to Greenspun's 10th rule. So I wrote a specification for it, first in Clojure and then in python. The C++ execution engine is open source: https://github.com/facebookarchive/iterlib/ https://github.com/facebookarchive/iterlib/ Main problems writing such code: * The output of SQL is generally flat. GraphQL makes it nested, but doesn't support all the operators SQL does natively. * Anyone trying to implement such an engine needs to ensure a fundamental property - the shape of the output can be inferred from the shape of the query. * Writing async code in C++ with futures and promises is like pulling teeth. At some point the complexity explodes and the code becomes unmaintainable. My most recent attempt is here: https://adsharma.github.io/fquery/ https://adsharma.github.io/fquery/ It shouldn't be hard to put a wrapper around fquery that looks like the query string you present above. In fact, there is a s-expression parser in iterlib/python above that could be tweaked to do this. I'm hoping that we can build a community around such languages and once the specification is agreed on, a more performant implementation can be written in a systems programming language.
• bionhoward 6y ago
  heck, what if we just make the database an immutable graph, and use functional programming on it in our language of choice? oh wait, that's Datomic. Except, Java is quite lame. Let's rewrite Datomic in Rust please!
• teddyh 6y ago
  A response to this article, I Don't Want to Teach my Garbage DSL, either, was discussed here on HN almost two years ago: https://news.ycombinator.com/item?id=20059029 https://news.ycombinator.com/item?id=20059029 In that discussion, I made this comment¹, which I still stand by: If you’re developing a DSL which is just a query language, you are reinventing the wheel, and you should ask yourself if any benefit of your language over SQL is worth the effort of all your users to learn your new query language. It may be worth it of your data can not be usefully be modeled by tables; e.g. document query languages like XQuery and even simple XPath are useful and can not be easily replaced with SQL. 1) https://news.ycombinator.com/item?id=20059337 https://news.ycombinator.com/item?id=20059337
• neduma 6y ago
  DSLs are modern day version of language explosions in a niche ecosystem (CS) in a short period. Interested in how DLS like expression evolving on other industries like Music, Science, etc
• khaledh 6y ago
  One thing that ORMs shine in is query composition. For example, starting from a base definition for retrieving a list of entities, you can _dynamically_ compose orthogonal operators that modify the base query, e.g. projection, filtering, sorting, grouping, joins, etc. without having to manually write SQL for every combination of those operators; the ORM takes care of generating the SQL code for you. That being said, ORMs still don't enjoy the level of trust that optimizing compilers have enjoyed for decades. That's always going to be a barrier for wide adoption, especially from folks who are experienced in SQL. It's similar to how in the 1950s (and maybe somewhat 1960s) there was resistance for "automatic coding" by compilers; those who were experienced in assembly thought that compilers will always produce sub-optimal code. But it's clear that high-level languages have won (at least outside embedded/low-level drivers). The point is: any form of code generation will always find resistance until it proves itself.
  • flukus 6y ago
    I agree they shine at dynamic query composition, but I find the sort of UI that necessitates this dynamism are universally terrible. They end up being data dumps that allow the user to filter and sort themselves instead of having a UI that actually understands their workflow and the data the need to accomplish it. It's like sending the user an excel file and saying "here, you can manipulate it how you want". This is also something light weight orms or query generators handle fairly well, if not better.
• brundolf 6y ago
  There are three fairly independent points being made here: 1) ORMs (which are used in cases where you have control over the DB) are bad and you should just use SQL 2) DSLs (in non-SQL DBs) are bad and the devs should have just exposed SQL 3) DSLs (in opaque services) are bad and the devs should have just exposed SQL I tend to agree with #1, though it's complicated and there's lots to be said on both sides. #2 seems semi-reasonable, if the semantics are close enough to those of a SQL DB, but the problem is that DBs like Mongo have wildly different semantics from SQL. #3 is harder because an opaque service usually specifically wants to insulate users from its implementation details. The great majority will not want to give you direct access to a SQL database. So the only alternative would be to "fake it" and pretty much base their DSL on SQL, which may or may not line up with the semantics of their service. For both #2 and #3, exposing SQL when the underlying semantics may or may not be a good match for the query language seems questionable at best. It encourages assumptions to be carried over that may not hold, especially around performance characteristics (which the OP specifically mentions as an advantage, surprisingly). I just don't see how this is tractable in the general case.
• echlebek 6y ago
  Nobody seems to have posted Stuart Halloway's Narcissistic Design yet, so I'll just leave this here. https://www.youtube.com/watch?v=LEZv-kQUSi4 https://www.youtube.com/watch?v=LEZv-kQUSi4 Has some great insights and quips. "Nothing says 'screw you' like a DSL"
• edumucelli 6y ago
  What about Elasticsearch that invents and deprecates features for it's query language on every major release. Then you have several ways of doing the same query with all kind of filters, all of them which can't tell you if a certain field is NULL.
  • starik36 6y ago
    The ElasticSearch language is so wordy and obtuse and difficult to remember, that I just translate SQL to it and go from there. https://sqltoelasticsearch.azurewebsites.net/ https://sqltoelasticsearch.azurewebsites.net/
    • edumucelli 6y ago
      That is a good idea. Elasticsearch is going on that direction, they have admitted what was obvious: https://www.elastic.co/what-is/elasticsearch-sql https://www.elastic.co/what-is/elasticsearch-sql
• nickelcitymario 6y ago
  This can be abstracted to a general critique with frameworks overall. On smaller scale projects, cut out as many frameworks as possible. Stick with the bare minimum of tools. Pure Ruby, versus Rails. Pure Javascript versus jQuery. SQL queries versus ORM. Etc. It's less to maintain, less to learn, AND it forces you to learn the underlying technology. On larger-scale and longer-term projects where you expect to work with others and/or will need to hop back into the code on a regular basis for years, cautiously introduce frameworks (and ORMs). In those cases, frameworks solve more problems in the long run. I'm usually annoyed early on, as frameworks force you to learn their DSLs (etc). But there's always a point where the benefits start to outweigh the initial negatives.
• Seb-C 6y ago
  I totally agree with the views of the author. That is basically why I started to build an ORM that does not include a query builder. Best of both worlds. Now the design was far from easy, and keeping things reasonably simple without abstracting the SQL part was my biggest challenge. And this would not have been possible without the template-string tagging feature of modern JS, so it wouldn't be doable for many other languages. https://github.com/Seb-C/kiss-orm https://github.com/Seb-C/kiss-orm
• lelanthran 6y ago
  I don't see this anywhere in this thread: http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie... That is essential reading for anyone arguing against or for ORMs.
• dsimms 6y ago
  there are so many intersecting problems here, that I feel like we argue about different ones at the same time. Are you a human and want to query a database? SQL is probably it. (a SQL of some sort, but a pretty good shared understanding) Even if it's not relational exactly, it probably comes with some project with things like records/rows and columns/fields. Do you have some kind of relational model that needs accessing? SQL sounds pretty good, and you know it from writing queries as a human... But should you construct queries with string concatenation? No! Absolutely not. Do you have to write the queries yourself? Like what if you want to stick an object in a database? Can't it write the query for you? It's appealing, but leads to sadness and anger at some point. (Joins? Conditions of more than 2 degrees? lots of dark corners very close by) AND whatever model objects are used become a defacto database schema, so you can't really refactor them in the same way you would other models. So you better not expose them past whatever datastore touching interface you have. So why not just define them in SQL DDL in the first place? What if you want to write the query (or need to to be sure you're getting exactly what you expect) but type-safely? LINQ? Absolutely! jOOQ? Sounds good! Arel? Sure! And maybe you get some amount of programmatic composability as well with any of these. Thankfully they're not really ORMs or extremely thin ones at that.