23 ms·

I don't want to learn your query language

289 points by nreece 8y ago

mping 8y ago
Meh. If the backing store is not SQL, why would a SaaS provide a (much harder to implement) SQL layer with all its standards (92, 99, 2003) and quirks instead of writing a (probably) much simpler custom query language?
- jaggederest 8y ago
  I think what he's asking is that people allow a flat file export, which you would have to map and import yourself. This seems reasonable to me. And I agree. I wasn't involved in writing NRQL but statistics queries at New Relic during my time were already approaching turing completeness, so it was just a matter of time. Timeseries data in particular really doesn't map well to SQL at all, sadly, as a SQL fan working with a lot of timeseries data over the decade+ now.
  - nemothekid 8y ago
    >I think what he's asking is that people allow a flat file export, I don't get this though. If you want to export your data as some flat file, why use MixPanel at all? Just store the events in your own database and query it yourself. Why pay the cost of both solutions?
  - threeseed 8y ago
    Some companies do allow flat file export. Others don't because sometimes it's not that simple to fetch all of the data to give you with one click. Many of these companies are just rolling up the metrics in some online database and hiding the original files away in cold storage.
- BerislavLopac 8y ago
  Why does it have to be either SQL or a custom language anyway? We have developed many generic query languages for various different structures [0]. [0] https://en.wikipedia.org/wiki/Query_language https://en.wikipedia.org/wiki/Query_language
lazyjones 8y ago
To avoid obscure ORM syntax and resulting garbage queries, as well as cumbersome inline SQL blocks, just implement your complex queries as stored procedures / views etc.
- fmajid 8y ago
  Amen. You can also declare those stored procedures SECURITY DEFINER and deny access to the underlying tables to the client role for much Better security. For example if you have a table of users with login and hashed password, a check_login stored procedure allows checking password but if(when?) the app server gets compromised, the attacker can't even dump the list of valid logins because they don't have SELECT privileges on the table.
  - angersock 8y ago
    Careful, this advice is dangerously close to proper DBA knowledge.
- SQL2219 8y ago
  This was what I was thinking also.
- scarface74 8y ago
  Having a dependence on stored procedures is a disaster from a devops perspective. Having all of your logic in your code makes deployments, rollbacks, testing and everything else from a development standpoint much easier. Having one source of truth for your code - your repo is much better. I know that if I check out code from three weeks ago, it’s the same code. I don’t know that if the code is dependent on stored procedures.
  - CuriouslyC 8y ago
    To call them a nightmare is an overreaction. Client-exposed stored procedures can be versioned either by name or by the schema they're attached to. Additionally, you're making sweeping generalizations based on a limited perspective. Loading up the application layer is the right choice when your data will only ever be consumed by that application, and multiple versions need to coexist. When you're talking about data that cuts across a business, and where a single application version lives at a time, loading up the application layer is infeasible and unnecessary.
ThemalSpan 8y ago
That “smart kids say no to DSLs” meme is funny but also a garbage take if taken at face value. DSLs are important because specializing will generally make you / your tool more effective. Splunk has a special query language that reflects the product’s architecture. If you want to become productive with a piece of software you need to learn its language. Edit: To clarify my position, Domain specific languages mean domain specific ideas. As soon as a product is no longer meant for general queries, but for queries over specific ki day of information in a specific kind of system, we can use that knowledge to make a powerful language in that domain.
- Dowwie 8y ago
  More effective at what, other than using the DSL? The article was about ORMs, not Splunk, and consequently their DSLs
  - yomly 8y ago
    serious question: what is the alternative as an interface to something like this that isn't a DSL? Is something that looks a bit like a familiar query language (like SQL) better? Is finding yourself in the uncanny valley of a language a feature or a bug?
  - ThemalSpan 8y ago
    They called Splunk out at the top of their SAAS list.
- gonzo41 8y ago
  I think splunk is a bad example. It's a really useful tool so the investment is worth it. I've seen people crack out their hip looking graph-like DSL that they lay over the top of an Oracle database. It's crazy to do this. I know all this regular SQL so just let me use that as needed.
- jcore24 8y ago
  Perhaps it'd be better to learn/use eDSLs - that is - DSLs embedded in a general and powerful language such that interoperability between all the little eDSLs becomes painless. Examples of eDSLs would be lisp macros.
matthewmacleod 8y ago
I disagree with the ORM hate generally, being a massive fan of ActiveRecord for making it easy to build queries and still drop down to SQL as required. But I’m right on board with the “stop inventing rubbish pseudo query languages” idea. I’ve rarely seen an example that wouldn’t be better if it were it just “SQL subset with some additional features”.
- mb_72 8y ago
  XPO (Devexpress' ORM for Windows) fan here; I've used this for years to deliver solutions for a number of clients, and it's still possible to use SQL directly in the few percentage of cases where it's necessary. I'm afraid I've given up reading arguments against ORMs as none of the cons seem to have any relevance to the work I do now or will do in the future, but I admit I am a 'enterprise' developer / consultant.
ChrisSD 8y ago
And yet LINQ seems to be so popular... Of course some standardisation amongst ORMs would be good. That would also help them be less "garbage".
- jordanab 8y ago
  I really like LINQ, and I generally also tend to prefer LINQPad over SQL Management Studio for simple DB queries/lookups. It has the extra benefit of allowing me to mix declarative LINQ based queries with imperative (C#) code, making it easy to do things like quick data cleaning/transformations, merges/joins with external data etc.
- scarface74 8y ago
  LINQ is more than just an ORM. Linq is just as it’s name implies a built in way to standardardize querying across a wide range of domains. It just translates your embedded query statements into expression trees that a third party can do with what it will. The Mongo LINQ driver for instance is excellent.
roel_v 8y ago
I get why people hate on ORM's, but let's not throw out the baby with the bathwater here - I don't want to go back to the time where you had to write object definitions for each table the database, and manually sync them. Nor do I want to manually write trivial 'select name from foo where id=1' queries all day, and do all the plumbing to execute it. So when hating on ORM's and unnecessary abstraction layers, please qualify that with 'ORM's should not try to be too smart' and 'ORM's should provide fall throughs into whatever they're abstracting for non-trivial use cases', and not give people the idea to ditch ORM's all together.
- saas_co_de 8y ago
  > const selectBy = (table, column, value) => db.query(`SELECT * FROM :table WHERE :column = :value`, {table, column, value}) > const selectOneBy = (table, column, value) => { const res = selectBy(table, column, value); return res && res.shift() } Two or three lines gives you about 80% of the value in any ORM and avoids the -20% value found in the other 100k LOC.
  - idoubtit 8y ago
    > Two or three lines gives you about 80% of the value in any ORM and avoids the -20% value found in the other 100k LOC. I have a hard time believing this when the two lines above are obviously wrong. You can't use :column to escape the name of a column in SQL. So your code has to protect it, which is not easy if you wan't to be portable. MySQL will generally use `column` but may be configured to use the standard "column". Anyway, selectAllBy() and selectOneBy() are not enough to be confortable. I appreciate the static completion in Something.find({id: 1}).complete, the relative queries like Post.find({id: 1}).author, and many other things that help against typos, help code faster and make the code more readable. I agree with the OP that fallthroughs are much needed, because writing custom SQL is sometimes simpler, and sometimes much more performant. Usually, its mostly about writing SomeModel.findAllBySql() which most ActiveRecord implementations provide.
    - saas_co_de 8y ago
      > the two lines above are obviously wrong there is something called psuedo code. use your imagination. > Anyway, selectAllBy() and selectOneBy() are not enough to be confortable Like I said: 80%. > SomeModel.findAllBySql() which most ActiveRecord implementations provide So you are arguing that you should use a huge ORM code base, full of bugs, but then don't use it for anything complex, which means don't use 80% of the code, which is what "handles" all those complex edge cases. Except then you have to have your team agree on what "complex" is, and half the team (who don't actually code anything complex) argue that you should use the ORM for everything, and then you end up in the sh-tshow that most amateur shops that build on ORMs are in. Bottom line, ORMs are for low skill teams that basically don't know what they are doing, and if they ditched the ORM they might actually be able to learn to code decent systems.
AnaniasAnanas 8y ago
I agree, say no to garbage query languages, including SQL. Datalog has been a thing for aeons, why not use it?
- Naomarik 8y ago
  Was going to write a comment about datalog but will piggy back on yours. Every query language has felt like garbage to me until discovering datalog. So simple I explained it to my non technical business partner and he grasped how to query things within five minutes.
- a-saleh 8y ago
  Aggregation is awkward. It is not that composable. Sub-queries are not a thing. Prolog conventions make things awkward. The ammount of datatypes seems to be limited. If you encounter datalog in the wild it will be something pretty-custom, like datomic, pydatalog, or even differential dataflow (that feels more like map-reduce with fix-points?)
  - YeGoblynQueenne 8y ago
    >> Prolog conventions make things awkward. Hey. What do you mean? Which Prolog conventions? (I write a lot of Prolog and I'm always curious what other people think about it).
    - a-saleh 8y ago
      Maybe I haven't used datalog enough, but the convention of lowercase data, uppercase-starting Variable strikes me as limiting.
Dowwie 8y ago
I agree with the author's sentiment, in the context of relational databases. I can't get back the time and effort spent porting SQL to query builder and/or orm syntax. The best I can do is learn from my lapse of sound judgment and instead use parameter binding against raw SQL and never interpolate strings of SQL. I regret not adopting this practice sooner. Most of my substantial work resides in that raw SQL feature. So, I use a library that offers good support of that. I have written hundreds of SQL using query builders and ORMs.
- threeseed 8y ago
  Every ORM (and I mean every one) has the ability to let you either (a) use raw SQL, (b) use raw SQL interpolated with column names or (c) has a syntax which is very close to SQL. Porting should not have been some onerous or career destroying activity. As Job would say I think you've made a huge mistake.
  - Dowwie 8y ago
    Those who don't learn from mistakes will repeat them. I've learned from my mistakes and share a lesson today. Most of my substantial work resides in that raw SQL feature. So, I use a library that offers good support of that. I have written hundreds of SQL using query builders and ORMs.
  - thanatropism 8y ago
    If you mean the character voiced by Bojack Horseman in "Arrested Development", it's spelled Gob. Amazing how they got Bojack into that human costume.
unixhero 8y ago
He forgot the Jira Query Language. To be fair, it really works!
- joaomacp 8y ago
  I was so mad when I wanted to make a simple filter on Jira tickets and I had to use "JQL"...
threeseed 8y ago
I had to check the date because this could've been written 20 years ago. Developers don't need other developers to tell them when or when not to use ORM. We know how it works. Some use cases are better for a quick inline SQL whilst others where you have hundreds of tables would lend well to ORM. It's not one size fits all and I'm sure we've all written a few apps in our time to know this to be the case. And this idea you can just learn "one SQL" is pretty laughable. There is a subset of SQL that works across many (but not all) relational databases. But each database has its extensions which you really should be using to get maximum performance. ORMs automatically take advantage of these just to mention. And the behaviours you will get can differ immensely.
- scarface74 8y ago
  But each database has its extensions which you really should be using to get maximum performance. ORMs automatically take advantage of these just to mention. And the behaviours you will get can differ immensely. But if you use vendor specific extensions you will have “vendor lock-in”. What if someday on a whim your CTO decides to change the database your entire multi billion dollar organization from depending on Microsoft/Oracle and wants to go open source? Yes I’m being sarcastic. In reality, statistically no company makes those types of sweeping changes because the risks are too high and the rewards are too low.
  - avmich 8y ago
    Company may very well not have a choice. A couple of weeks ago a customer requested Oracle instead of MS SQL - now somebody is looking into all places checking if SQL is good enough. To me the saying "ORM is a Vietnam of computer science" rings true too often. Somehow projects which I deal with don't really benefit from ORM... so I could live completely with SQL alone.
    - scarface74 8y ago
      If I were writing apps that were sold to customers, that would be even more of a reason to use LINQ - not most ORMs though. I know about a software package that was written in C# that officially supported Sql Server,Oracle, and Mongo and he was able to support all three with basically the same codebase using Linq and runtime translated Expression<Func<T>> statements. His LINQ queries and expressions were the same across data stores.
gabordemooij 8y ago
Shameless plug here, but this is exactly the reason why I did not create a query builder in the RedBeanPHP ORM. At the time (2009) query languages were a real 'hype' (as well as noSQL) but I have always loved SQL and I struggled with all the SQL-alternatives offered by other ORMs. So, when I created RedBeanPHP I decided to complement SQL rather than trying to replace it. Yes, there is some room for improvement, notably in the integration with the programming language (in my case PHP). So the ORM class has some methods to indicate you would like to apply a condition, but the condition itself is just written in pure SQL.I see my ORM more as a bridge between SQL and PHP than a carpet that hides the SQL. Not all DSLs are necessarily bad though. Personally I like to create some very basic and straightforward DSLs to replace horribly complex GUIs with tons of nested menus (aimed at professionals). I think DSLs can be useful for educated computer users who don't know SQL (like people in finance, medicine, statistics). Also there should be a limit to the complexity of a DSL. Not every DSL needs to be turing complete. Simple DSLs that cover the required functionality are the most successful in my experience with customers. However replacing SQL just does not feel right, it's already an awesome language to query a relational database so why replace it? So, for the most part I agree with the author - although I miss the nuance a bit.
- nojvek 8y ago
  I loved redbean, such a nice library. Don’t do much php nowadays but Django is a great db - object abstraction. You can go raw sql if you want quite easily.
mothsonasloth 8y ago
I've used Hibernate's HQL for many years but now I've started using raw SQL in my Java microservices with libraries like JDBI.
scoom 8y ago
Great, so force SQL to 'join' things that aren't tables and 'select' things that aren't rows. SQL wasn't built for querying every possible data store.
- kevsim 8y ago
  Yeah, that was my reaction as well. I fully understand his point regarding ORMs that are meant to be an abstraction over a relational database, but imposing the relational model over non-relational datastores sounds like a disaster.
- threeseed 8y ago
  If only there was a popular example of this. Wait there is: PostgreSQL. They bastardised SQL to support JSON data types just like every other vendor has to do because SQL is for relational and only relational data stores. Is this the utopia that the author is after ? https://www.postgresql.org/docs/9.0/static/hstore.html https://www.postgresql.org/docs/9.0/static/hstore.html
  - oulu2006 8y ago
    I thought they were, for the most part, fairly logical extensions to SQL. You don't have to use the JSON/JSONB column types, they are optional. We use them extensively in production and haven't had much difficulty learning them.
zaptheimpaler 8y ago
Yeah I think the only way you can use plain, vanilla SQL to query a system is IF its a relational database. Most of the examples are not.. they solve different problems and have vastly different architectures under the hood. Lucene for example doesn't use SQL because its really solving a different problem - text search. Its a language dedicated to what could only be expressed using something like `LIKE` and regexes in SQL. Same with Splunk, it addresses a different domain and solves different problems that cant be easily expressed in SQL. Same with MongoDB.. its not a relational db. Just because there is a mapping from some SQL queries to some Mongo queries, does not mean they are identical databases. The query language directly impacts the AST generated, which is necessarily tied very closely to the exact capabilities/internals of a system. This post just feels like its based on a cursory glance of what the systems do. If all Lucene had was regexes, it would not perform much better than a SQL database throwing regexes at strings. Its precisely because the query language is finer-grained that it can be better optimized for that usecase.
- pk3469 8y ago
  Yeah the post seems to confound two mostly unrelated issues. I agree with not reinventing the wheel for relational databases, SQL is fine. But for other domains, the query languages arose out of need, not because someone felt like it. SQL is hot garbage for for non-trivial logic. I can't even imagine what splunk would look like if you had to query it with SQL. If anything, SPL (or rather the piped operators approach) would make a lot more sense as the groundwork for a universal query language that works across many domains.
- ddebernardy 8y ago
  > Lucene for example doesn't use SQL because its really solving a different problem - text search. Its a language dedicated to what could only be expressed using something like `LIKE` and regexes in SQL. There are much better options than LIKE and regexes in SQL for text search. To mention but two: SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery; https://www.postgresql.org/docs/current/static/textsearch-intro.html https://www.postgresql.org/docs/current/static/textsearch-in... SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-lan... All main engines support full text search in one way or another. TSQL, Oracle, DB2... even SQLite. What more, these engines all support inverted tree indexes in one form or another - i.e. the same type of index Lucene uses - to make this efficient. This is completely different from the typical LIKE or regex query against a column with a BTree index.
tragic 8y ago
I've worked on large projects using ORMs and others using parameterised SQL strings. My experience, FWIW, is that the advantages listed in the article are all real, on top of which there are some more esoteric SQL features (like window functions) that may not be well supported by a given ORM. On the other hand, there is a significant cost in terms of duplication. In ORM code it is trivial to decide a where clause at run time. Not so much in plain SQL. So you end up with a lot of copies of the same query, or else some sort of query assembly layer, at which point you're halfway to an ORM anyway. I am undecided on the issue.
- blattimwind 8y ago
  I'm convinced there are two classes of ORMs. Those that try to abstract the relational model and those who are the relational model. The former category includes things like Django's ORM: very limited in what you can do, it doesn't actually "get" SQL, mismatch between the ORM API and the database. The latter category includes fewer software; sqlalchemy is my familiar example. Because sqlalchemy does not abstract the relational model, instead mapping it into your application language, it has ~few mismatches between its API and the database. The former category is what "kinda works well enough for CRUD apps and I don't need to know anything about nothing" and then become a major problem, the latter category presumes you know how SQL works but then allows you to work. Random snippet just because I feel like it. def all_children(self): cte = session.query(Tag).filter(Tag.parentid == self.tagid).cte(recursive=True) cte = cte.union_all(session.query(Tag).filter(Tag.parentid == cte.c.tagid)) return session.query(Tag).select_entity_from(cte).all()
  - mst 8y ago
    I tend to refer to the latter as ROMs. Ruby's Sequel is also somewhat this. My (perl5) DBIx::Class is somewhat this (modulo all the design mistakes).
alkonaut 8y ago
I think micro ORMs like Dapper are a perfectly reasonable compromise between too much abstraction vs too much abstraction. Hand populating data structures from database query results is error prone and tedious. Using a “big” ORM like hibernate or EF is a leaky and expensive (over-)abstraction. Not just because they typically come with a query dsl but they also come with performance considerations and architectural consequences that are hard to predict. You end up in a situation where estimating work is hard because you don’t know if the ORM is going to play along (making a feature trivial to implement) or hit a snag (making it very time consuming). With smaller abstractions the estimates may be higher in the best case but they vary less. This is one of the most important qualities about a library or stack. Not the average, but the variance in difficulty. On the topic of query DSL’s - the only acceptable ones are those that are native to your programming language. A good example is the F# SQL type provider. If that’s sufficient for your needs, it’s actually quite elegant as far as query languages go - and it’s all F# meaning it’s not a specific language for querying.
- tartoran 8y ago
  Thanks for pointing out the F# SQL, I just looked it up and read this https://dev.to/kspeakman/dirt-simple-sql-queries-in-f-a37 https://dev.to/kspeakman/dirt-simple-sql-queries-in-f-a37
- jordanab 8y ago
  I think that the current iteration of EF is still a great fit for small to medium sized projects. Besides offering strongly typed queries (LINQ) and updates, it also provides some additional functionalities such as migrations that such projects really can benefit from.
cryptica 8y ago
It never made sense to me how popular ORMs were. The very first time I used one, I understood straight away that it was a bad idea and I never changed my mind about it (though I tried). Why is it that open source software which adds unnecessary complexity and slows down development tends to become popular but open source software which takes away complexity and speeds up development tends to not get any attention. It's like there is some kind of conspiracy.
- justaaron 8y ago
  webpack and the entire "modern" js frontend... all because of an inability to manage versioning change across the standard browser js api... great for a giant megacorp looking to waste money on busywork and divide the workload across more employees (front-end devops?!?) less useful when you already have to compile a backend, don't really have a problem with ES5, and understand that you are basically only tasked with making your dom updates dependent upon data model changes, YMMV
  - gear54rus 8y ago
    so because you don't see the obvious problem with being stuck on a single version of anything forever, webpack, representing progress in this case, is bad? nice try what does versioning has to do with bubbling, wtf?
    - coldtea 8y ago
      >so because you don't see the obvious problem with being stuck on a single version of anything forever, webpack, representing progress in this case, is bad? In fact, the parent does see (and called) that "obvious problem with being stuck on a single version of anything forever". That's why he doesn't cheer for the band-aid solution that's webpack and doesn't thing it "represents progress".
- scarface74 8y ago
  I feel the same way about most ORMs - except for LINQ. C# treats queries as first class citizens. There is something very powerful about being able to have a method in your repo... public List<Customer> Find(Expression<Func<Customer,T>> expression) {...} and then your client can use any random expression... Find(c => c.age > 18 && c.age < 65 && c.gender == “M”) and have that expression translated to either SQL, MongoQuery, or anything else that has a LINQ provider. If done correctly, not only can you switch between RDMSs, you can switch between an RDMS and a Nosql data store without changing any client code. When you are writing your unit tests, you can use in memory Lists to mock your data store and still test all of your queries without using a database. Of course you also get type safety, autocomplete, and in the case of NoSql data stores, you enforce a schema over a schemaless data store and get to work with strongly typed objects. I’ve never seen any purpose of an ORM outside of LINQ. They just add another level of complexity and still don’t integrate with the host language naturally.
deleted 8y ago
[deleted]
GedByrne 8y ago
Erik may know SQL but there’s an entire generation of developers who don’t. These developers find it easier to dip into shallow DSLs instead of learning the depths of SQL.
- TimJYoung 8y ago
  If they can be developers, they can learn SQL. Apart from DDL statements, it's literally 3 DML statements, of which most of the clauses operate exactly the same way across all 3 statements.
  - TimJYoung 8y ago
    Sorry, that should be 4 DML statements...
jakoblorz 8y ago
I believe that sqlx for go is amazing in bridging the gap between orm and plain SQL! https://github.com/jmoiron/sqlx https://github.com/jmoiron/sqlx
- swah 8y ago
  That does almost nothing... I enjoy writing SQL and Go with sqlx, but testing other designs/ideas becomes so slow compared to Python/Django. I'll be using Gorm on my next project, because life is so short..
biggio 8y ago
SQL is syntactic sugar for first order logic. It is much easier to query relational database with SQL than abstracting relations with ORM.
- scarface74 8y ago
  How is “select * from customer where age > 18 and age < 65” Any more of an abstraction and easier than from context.Customer where c.Age > 18 && c.Age < 65 With one it’s a magic string where you could have typos. With the other you get compile time syntax checking.
  - angersock 8y ago
    You must not use good SQL IDEs...try Datagrip.
    - scarface74 8y ago
      Sql is part of my code that doesn’t help....
  - biggio 8y ago
    I understood your SQL example but I don't understand your counter example.
    - scarface74 8y ago
      The second is LINQ within C#
  - CuriouslyC 8y ago
    If you use a modern IDE and extract your SQL into separate files you can get syntax checking, and even the ability to run the query on-server directly and get a table of results in a table-formatted tab with a single click. How much do you have to do to get your ORM to run your query, and are the results as easy to work with?
culturedsystems 8y ago
The custom query language I find most annoying (possibly just because it's the one I deal with the most), is JPQL, the query language of the Java Persistence Architecture. JPA explicitly targets relational databases, so there's no obvious reason why you couldn't just use SQL. You might think the advantage of a custom query language would be that it could abstract over differences between SQL implementations, but JPQL is underspecified and just gives you the underlying database behaviour in many cases. I spent a day trying to figure out why my tests passed but the staging environment was claiming that 0 was greater than 0; turns out there was a difference in rounding behaviour between the in-memory database used for tests, and the real database, which JPA was cheerfully glossing over.
supermatt 8y ago
And I’m assuming your toolbox just contains a hammer? Granted, there’s a lot of duplication of effort around, but in many of the examples you cite they are trying to solve different problems.
- TomK32 8y ago
  There's a claw hammer, ball pein, mallet, bronze hammer, rubber hammer if you don't want to band your head on the table, and of course the mighty sledge hammer.
  - supermatt 8y ago
    So just hammers :)
TomK32 8y ago
Also, learn just one programming language. Lisp obviously.
- jcore24 8y ago
  Unironically this. I feel like a zealot saying it but having eDSLs with painless interoperability makes lisp a joy to work with.
gregopet 8y ago
I really really like what jOOQ has done for SQL under Java (and other JVM languages). It's basically a type safe interface to SQL that anyone who knows SQL can grok in minutes and it provides constructs to help bridge the gap between SQL and the programming language. Among other features it has tools to help migrate between different databases (and more are planned) - something that is usually considered to be a strong point of ORMs. I've used a couple of ORMs and jOOQ blows them all away easily. It's not the first product that tried this approach but jOOQ does it really well. I've partly or fully converted at least 3 Hibernate projects to jOOQ and all my recent JVM projects use it exclusively. In my opinion products like jOOQ make ORMs redundant for most use cases.
_Codemonkeyism 8y ago
Mongo is one of the most painfull, it's inconsistent, simple queries take dozens of $ and { }, and the db.tablename.<...> is annoying.
pmontra 8y ago
I'll be happy to write my own SQL (I do for complex queries) but I don't want to extract results manually from results and tweak the code every time I change the schema. At least those parts of ORMs must stay.
dec0dedab0de 8y ago
This is how SQL injection makes a comeback. No thank you, I'll stick with my orm.
- avmich 8y ago
  SQL standard defines a couple of special characters within strings - like ' and * . Not too complex. If you escape input - say, using a library function - you won't get injection problem.
Too 8y ago
SQL reasonably standardized? You can't even write hello world without running into vendor differences. Microsoft has SELECT TOP FROM, while Mysql has SELECT FROM LIMIT.
gesman 8y ago
There are reasons different programming languages exists. And so as query languages. Different stacks, different ways to handle, optimize and accelerate queries over structured (SQL) and unstructured, schema-on-the-fly (SPL) datasets. If you don’t want to learn - it’s a personal choice. You can ride a bicycle that works everywhere while others prefer driving sport cars on a highways.
jmartrican 8y ago
This is why I really like JOOQ for Java. Its ORM but with SQL. Best of both worlds IMHO.
- oftenwrong 8y ago
  jOOQ is my favourite Java library. The code generation effectively extends type checking down to the db types and makes it easy to build your persistence layer without messy object mapping... if used properly; I've seen some setups that misused jOOQ.
zzzeek 8y ago
"Let’s dispel with the myth that ORMs make code cleaner. Join the embedded-SQL movement and discover a much more readable, much more straightforward way to query databases." Any argument whatsoever for this? Would love to see an example of how when you do away with the whole object persistence layer, and you just have a SQL string (which ORMs allow you to do anyway), how do you get your parameters over, how do you get your rows back, how do you manage your transactional scope, how do you get your rows in and out of your objects? all of which has nothing to do with a SQL string. Well let's read on: "Erik Bernhardsson... is the CTO at Better, which is a startup changing how mortgages are done. I write a lot of code, some of which ends up being open sourced, such as Luigi and Annoy. " Open source stuff! Let's go there and see an example of this "write raw SQL and don't use any persistence libraries and your code is readable and straightforward", because nobody ever seems to actually want to illustrate this and how they don't end up writing their own ORM anyway, and we see, oh https://github.com/spotify/luigi/blob/master/luigi/db_task_history.py#L48 https://github.com/spotify/luigi/blob/master/luigi/db_task_h..., it's SQLAlchemy ORM. Foiled again in my search to see this elusive super clean and simple raw SQL with no ORM that doesn't reinvent an ORM anyway. Which is the real "myth" ?
- TimJYoung 8y ago
  "Would love to see an example..." Delphi has always had a default non-ORM dataset layer (there are ORMs for it), and it works like this for queries: 1) There's a base TDataSet class (Object Pascal uses "T" to denote "Type") that handles all abstract column and row storage and manipulations, with no pre-disposed notion of how to actually load such rows. Dataset columns are abstracted away also, and provide a lot of automatic type conversions and functionality like BLOB column stream access. Having this base class allows disparate descendant classes to easily interact with each other without knowing the details of how the data got there. All query objects descend from this base class, and fill in all of the functionality for actually interacting with the database server/engine. 2) For query objects, the SQL string gets assigned to a query object property (typically called "SQL"). Parameters are specified as named parameters by using colon notation (:Parameter) instead of "?". During this string assignment, a property setter method parses the SQL string and automatically sets up the parameters as a collection that is another property of the query object. These parameters do not have any type information assigned to them at this point. The developer can choose to just manually assign values to the parameters using type-specific parameter properties at this point. Any type differences are managed by the underlying object, so assigning an integer to a string parameter will result in an automatic conversion. 3) If you manually prepare the query object using a Prepare method, then the SQL is sent over to the database server to be prepared and any type information about the parameters is sent back from the database server to be assigned to the parameters collection (depends upon the database server). If you don't manually prepare the query, then it is automatically prepared during the query execution (4). 4) The SQL is executed using an Execute or Open method. If the SQL is a SELECT statement, the query object is automatically populated with the rows and the rows are managed using the base TDataSet functionality. There are methods for navigation, updating, etc. and updates of non-directly-updateable result set cursors are managed by the query object. Query objects are linked to database objects, and the database objects manage the transactional scope for updates. You can also use such database objects to execute one-off SQL statements where the only thing you're interested in is the result set row count or the affected row count. The interesting part is how Delphi handles static-typing of result set columns in your application. In the IDE, you can right-click on any query object and have it automatically populate "persistent" column objects whose class type reflects the underlying result set column type. This means that if you try to access or assign the column object's raw Value property, this access/assignment is subject to type enforcement by the compiler. However, you are free to use other properties that perform type conversion, in which case you'll get a runtime exception if the type conversion fails for any reason. You can also assign names to these column objects, allowing you to write easy-to-read code like this: OrderCustomerID.Value := 1000; So, you can get a lot of the benefits of an ORM using an architecture like this without having to sacrifice the flexibility of manually-constructed SQL statements. A lot of this depends upon the peculiarities of the Delphi component library and IDE, though, so how transferrable this to other environments is an open question.
manish_gill 8y ago
This resonated strongly with me. Almost all of the heavy DB interaction projects I've done in the last couple of years - be it in Python, Go or Java, the SQL layer has almost always been raw queries going to the DB. Makes things so much easier. I don't miss SQLAlchemy where I would trawl through documentation to figure out how to do X or Y. As for SQL standardization, +1 to that. Learning a new pseudo-SQL is only fine if there are additional features on top of the DB, but I've encountered databases where the SQL support was present in the name only and extremely limited, with people making do with UDFs in order to achieve basic things.
- dahdum 8y ago
  I’ve had the same experience, and as soon as the interaction became very complex (multi table, multi row updates) we just moved it all into stored procedures.
  - Udik 8y ago
    Never understood what's the thing with stored procedures. You soon find that an indeterminate amount of your business logic is written in stored procedures, and in an opaque language that is fit for manipulating data, not for implementing business logic; not to mention the annoyance of running updates on the database, which works ina fundamentally different way from deployable code.
    - zzzeek 8y ago
      what it usually means is, "we gave the DBA the job to do and now we don't deal with it at all"
jcadam 8y ago
> "It’s not like people learn an ORM instead of learning SQL anyway." BWAHAHAHAHA!!! No. I have to use ORMs at work because they are forced on me. Same thing with Eclipse, and GWT, and a million other things that I detest because my employer has structured their development processes around hiring average talent and getting average(ish), repeatable results (I'm serious, I work with a lot of developers who only know how to code in Java, in Eclipse -- it's maddening). I'm the malcontent who doesn't fit in because I say things like "Let's just use SQL." or "I promise you, this architecture is not scalable, regardless of your seniority over me."
wheresvic1 8y ago
In my experience ORMs work well for smaller projects and at the beginning. Once the code matures, it is much more performant and simpler to use direct SQL. One thing that really resonated with me from the article is the following: > 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 (in my case, Postgres/Redshift). Totally agree, a data dump should really be a standard feature (and not take ages to produce either).
- mst 8y ago
  Having written assorted data layers: If you can't replace a generated query with a chunk of literal SQL when optimisation or raw power indicates it's the best option, the data layer sucks. Sadly, being software, "the data layer sucks" is true of most data layers.
vbezhenar 8y ago
I still yet to find sane JDBC wrapper library for Java. JDBC itself has terribly verbose API. And any popular library I've encountered is not very nice to use. I end up buliding half-baked JDBC wrapper for every project I'm writing. And Hibernate at least have good enough API.
nailer 8y ago
> I just want my SQL back. It’s a language everyone understands I'm sure lots of people do understand SQL. But if you started programming post 2010 it's entirely possible you've never had to deal with a row database. My case: - GAE datastore - MongoDB - RethinkDB Again not discounting those who've been programming longer, it's just that JSON-stores are becoming default and the amount of people who don't know SQL is probably more than you think.
- nojvek 8y ago
  I really loved rethinkdb rql. Was a very elegant language to deal with json datastores over mongodb
tjpnz 8y ago
This reminds me of something that happened in a previous gig where our team was working primarily with SQL. The company rockstar one-day decided to rid us all of our perceived misery by introducing a REST interface over our existing databases complete with it's own query language that he managed to foist upon us with the clout he had with upper management. The query language was limited at best even when compared to some of the examples in the article. Initially all it provided was basic matching against several columns and the ability to do rudimentary paging across resultsets. Naturally we had problems with this as a big part of our job was writing reports relying heavily on joins and aggregation - we were left in a situation familiar to anyone whose used NoSQL joining everything in code but without all of the niceties that come with maturity. It's a given that team productivity and general morale took a bit of a dive at this point. My current theory to the proliferation of these query languages is ignorance. Aside from a severe case of NIH the developer behind our query language exhibited a flawed understanding of SQL. If you're planning to roll your own query language you probably shouldn't without first considering SQL, your intended users and the use cases that they're trying to satisfy.
- scarface74 8y ago
  Initially all it provided was basic matching against several columns and the ability to do rudimentary paging across resultsets. Naturally we had problems with this as a big part of our job was writing reports relying heavily on joins and aggregation - we were left in a situation familiar to anyone whose used NoSQL joining everything in code but without all of the niceties that come with maturity. It's a given that team productivity and general morale took a bit of a dive at this point. Yes being forced to use a REST interface over reports is a bad idea but that has nothing to do with joins over a NoSQL data store. Mongo does joins via the $lookup function and the C# Mongo driver will translate the LINQ code to MongoQuery for you. https://www.axonize.com/blog/joining-collections-mongodb-using-c-driver-linq/ https://www.axonize.com/blog/joining-collections-mongodb-usi... Worse case even if you do get data sources from separate places, it’s just as easy to do joins on two lists as it is to do in Sql using LINQ.
- tannhaeuser 8y ago
  This sounds all too familiar. Developers having a REST epiphany and thinking they're clever by mapping relational rows to REST resources is also very common with Spring apps (Spring data in particular).
Udik 8y ago
Just to go slightly off-topic, but anybody here felt it would be a dream if Excel allowed pure SQL instead of those absurd formulas like "sum if" etc? (I believe that it's (relatively) recently become possible to define tables as SQL data sources, not sure how practical that is or if it's used at all.)
- jodrellblank 8y ago
  "define tables as SQL data sources" ? Excel can query external data sources with SQL queries, and pull the results back into the spreadsheet. And via ODBC you can use .xls files as data sources, and query them with SQL style syntax from other programs. (I don't know how far beyond the basic "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]" it can support). Both of those date back - https://www.connectionstrings.com/microsoft-excel-odbc-driver/ https://www.connectionstrings.com/microsoft-excel-odbc-drive... - most look like Excel 2000 or Excel 97.
gonyea 8y ago
This is some A+ bah-humbuggery. You want companies to parse sql and translate that to backend api calls? What a nightmare. Data isn’t always co-located, and not all data is allowed to be fetched together (eg, protecting user privacy in Google Analytics).
luord 8y ago
Agreed on using SQL under the hood for every system, but I don't have a problem with ORMs.
tomcam 8y ago
OP's frustrations are quite understandable but I can think of a couple of areas where ORM-like concepts have worked well. PyDAL[0] and Linq are both incredibly expressive and PyDAL is also low overhead (it's not an ORM, it's a data access language, which means it doesn't have the baggage of an object-oriented layer). Another reason to use a DAL like PyDAL is that, properly written, it can abstract away the data layer such that you can switch from (for example) MySQL to PostGres to Google Cloud SQL with essentially no retooling required. Finally, using a DAL can help keep inexperienced programmers on track with security, accessibility, and performance requirements. [0] https://github.com/web2py/pydal https://github.com/web2py/pydal
- tannhaeuser 8y ago
  > Finally, using a DAL can help keep inexperienced programmers on track with security, accessibility, and performance requirements. This is a sure way they'll never learn the real thing. I know since I'm sitting next to a (young but talented) guy who knows only Spring and JPA, and freaks out all the time because the developer of the legacy system had dared to assemble SQL queries manually in the code (which are complex enough I personally hadn't bothered using JPQL in a trial-and-error development model either). Wasn't the post exactly about not wasting novice and other developer's time with crap criteria APIs and such? Just compare the sheer size of the SQL spec or reference manual against the shallow description of JPQL to judge which language you should be using for a project with any kind of depth.
nojvek 8y ago
I’m surprised no one yet had mentioned Facebook’s osquery tool. Even though most things aren’t relational databases, a uniform way to query them and join different datasources together is a very powerful idea. Using sql to query a whole bunch of stuff has been very nice. I just wish the excellent TablePlus client could use osquery under the hood to show a lot of system level things in a nice pretty UI.

289 points by nreece 8y ago

Meh. If the backing store is not SQL, why would a SaaS provide a (much harder to implement) SQL layer with all its standards (92, 99, 2003) and quirks instead of writing a (probably) much simpler custom query language?

jaggederest 8y ago
I think what he's asking is that people allow a flat file export, which you would have to map and import yourself. This seems reasonable to me. And I agree. I wasn't involved in writing NRQL but statistics queries at New Relic during my time were already approaching turing completeness, so it was just a matter of time. Timeseries data in particular really doesn't map well to SQL at all, sadly, as a SQL fan working with a lot of timeseries data over the decade+ now.
- nemothekid 8y ago
  >I think what he's asking is that people allow a flat file export, I don't get this though. If you want to export your data as some flat file, why use MixPanel at all? Just store the events in your own database and query it yourself. Why pay the cost of both solutions?
- threeseed 8y ago
  Some companies do allow flat file export. Others don't because sometimes it's not that simple to fetch all of the data to give you with one click. Many of these companies are just rolling up the metrics in some online database and hiding the original files away in cold storage.
BerislavLopac 8y ago
Why does it have to be either SQL or a custom language anyway? We have developed many generic query languages for various different structures [0]. [0] https://en.wikipedia.org/wiki/Query_language https://en.wikipedia.org/wiki/Query_language

lazyjones 8y ago

To avoid obscure ORM syntax and resulting garbage queries, as well as cumbersome inline SQL blocks, just implement your complex queries as stored procedures / views etc.

fmajid 8y ago
Amen. You can also declare those stored procedures SECURITY DEFINER and deny access to the underlying tables to the client role for much Better security. For example if you have a table of users with login and hashed password, a check_login stored procedure allows checking password but if(when?) the app server gets compromised, the attacker can't even dump the list of valid logins because they don't have SELECT privileges on the table.
- angersock 8y ago
  Careful, this advice is dangerously close to proper DBA knowledge.
SQL2219 8y ago
This was what I was thinking also.
scarface74 8y ago
Having a dependence on stored procedures is a disaster from a devops perspective. Having all of your logic in your code makes deployments, rollbacks, testing and everything else from a development standpoint much easier. Having one source of truth for your code - your repo is much better. I know that if I check out code from three weeks ago, it’s the same code. I don’t know that if the code is dependent on stored procedures.
- CuriouslyC 8y ago
  To call them a nightmare is an overreaction. Client-exposed stored procedures can be versioned either by name or by the schema they're attached to. Additionally, you're making sweeping generalizations based on a limited perspective. Loading up the application layer is the right choice when your data will only ever be consumed by that application, and multiple versions need to coexist. When you're talking about data that cuts across a business, and where a single application version lives at a time, loading up the application layer is infeasible and unnecessary.

ThemalSpan 8y ago

That “smart kids say no to DSLs” meme is funny but also a garbage take if taken at face value. DSLs are important because specializing will generally make you / your tool more effective. Splunk has a special query language that reflects the product’s architecture. If you want to become productive with a piece of software you need to learn its language. Edit: To clarify my position, Domain specific languages mean domain specific ideas. As soon as a product is no longer meant for general queries, but for queries over specific ki day of information in a specific kind of system, we can use that knowledge to make a powerful language in that domain.

Dowwie 8y ago
More effective at what, other than using the DSL? The article was about ORMs, not Splunk, and consequently their DSLs
- yomly 8y ago
  serious question: what is the alternative as an interface to something like this that isn't a DSL? Is something that looks a bit like a familiar query language (like SQL) better? Is finding yourself in the uncanny valley of a language a feature or a bug?
- ThemalSpan 8y ago
  They called Splunk out at the top of their SAAS list.
gonzo41 8y ago
I think splunk is a bad example. It's a really useful tool so the investment is worth it. I've seen people crack out their hip looking graph-like DSL that they lay over the top of an Oracle database. It's crazy to do this. I know all this regular SQL so just let me use that as needed.
jcore24 8y ago
Perhaps it'd be better to learn/use eDSLs - that is - DSLs embedded in a general and powerful language such that interoperability between all the little eDSLs becomes painless. Examples of eDSLs would be lisp macros.

matthewmacleod 8y ago

I disagree with the ORM hate generally, being a massive fan of ActiveRecord for making it easy to build queries and still drop down to SQL as required. But I’m right on board with the “stop inventing rubbish pseudo query languages” idea. I’ve rarely seen an example that wouldn’t be better if it were it just “SQL subset with some additional features”.

mb_72 8y ago
XPO (Devexpress' ORM for Windows) fan here; I've used this for years to deliver solutions for a number of clients, and it's still possible to use SQL directly in the few percentage of cases where it's necessary. I'm afraid I've given up reading arguments against ORMs as none of the cons seem to have any relevance to the work I do now or will do in the future, but I admit I am a 'enterprise' developer / consultant.

ChrisSD 8y ago

And yet LINQ seems to be so popular... Of course some standardisation amongst ORMs would be good. That would also help them be less "garbage".

jordanab 8y ago
I really like LINQ, and I generally also tend to prefer LINQPad over SQL Management Studio for simple DB queries/lookups. It has the extra benefit of allowing me to mix declarative LINQ based queries with imperative (C#) code, making it easy to do things like quick data cleaning/transformations, merges/joins with external data etc.
scarface74 8y ago
LINQ is more than just an ORM. Linq is just as it’s name implies a built in way to standardardize querying across a wide range of domains. It just translates your embedded query statements into expression trees that a third party can do with what it will. The Mongo LINQ driver for instance is excellent.

roel_v 8y ago

I get why people hate on ORM's, but let's not throw out the baby with the bathwater here - I don't want to go back to the time where you had to write object definitions for each table the database, and manually sync them. Nor do I want to manually write trivial 'select name from foo where id=1' queries all day, and do all the plumbing to execute it. So when hating on ORM's and unnecessary abstraction layers, please qualify that with 'ORM's should not try to be too smart' and 'ORM's should provide fall throughs into whatever they're abstracting for non-trivial use cases', and not give people the idea to ditch ORM's all together.

saas_co_de 8y ago
> const selectBy = (table, column, value) => db.query(`SELECT * FROM :table WHERE :column = :value`, {table, column, value}) > const selectOneBy = (table, column, value) => { const res = selectBy(table, column, value); return res && res.shift() } Two or three lines gives you about 80% of the value in any ORM and avoids the -20% value found in the other 100k LOC.
- idoubtit 8y ago
  > Two or three lines gives you about 80% of the value in any ORM and avoids the -20% value found in the other 100k LOC. I have a hard time believing this when the two lines above are obviously wrong. You can't use :column to escape the name of a column in SQL. So your code has to protect it, which is not easy if you wan't to be portable. MySQL will generally use `column` but may be configured to use the standard "column". Anyway, selectAllBy() and selectOneBy() are not enough to be confortable. I appreciate the static completion in Something.find({id: 1}).complete, the relative queries like Post.find({id: 1}).author, and many other things that help against typos, help code faster and make the code more readable. I agree with the OP that fallthroughs are much needed, because writing custom SQL is sometimes simpler, and sometimes much more performant. Usually, its mostly about writing SomeModel.findAllBySql() which most ActiveRecord implementations provide.
  - saas_co_de 8y ago
    > the two lines above are obviously wrong there is something called psuedo code. use your imagination. > Anyway, selectAllBy() and selectOneBy() are not enough to be confortable Like I said: 80%. > SomeModel.findAllBySql() which most ActiveRecord implementations provide So you are arguing that you should use a huge ORM code base, full of bugs, but then don't use it for anything complex, which means don't use 80% of the code, which is what "handles" all those complex edge cases. Except then you have to have your team agree on what "complex" is, and half the team (who don't actually code anything complex) argue that you should use the ORM for everything, and then you end up in the sh-tshow that most amateur shops that build on ORMs are in. Bottom line, ORMs are for low skill teams that basically don't know what they are doing, and if they ditched the ORM they might actually be able to learn to code decent systems.

AnaniasAnanas 8y ago

I agree, say no to garbage query languages, including SQL. Datalog has been a thing for aeons, why not use it?

Naomarik 8y ago
Was going to write a comment about datalog but will piggy back on yours. Every query language has felt like garbage to me until discovering datalog. So simple I explained it to my non technical business partner and he grasped how to query things within five minutes.
a-saleh 8y ago
Aggregation is awkward. It is not that composable. Sub-queries are not a thing. Prolog conventions make things awkward. The ammount of datatypes seems to be limited. If you encounter datalog in the wild it will be something pretty-custom, like datomic, pydatalog, or even differential dataflow (that feels more like map-reduce with fix-points?)
- YeGoblynQueenne 8y ago
  >> Prolog conventions make things awkward. Hey. What do you mean? Which Prolog conventions? (I write a lot of Prolog and I'm always curious what other people think about it).
  - a-saleh 8y ago
    Maybe I haven't used datalog enough, but the convention of lowercase data, uppercase-starting Variable strikes me as limiting.

Dowwie 8y ago

I agree with the author's sentiment, in the context of relational databases. I can't get back the time and effort spent porting SQL to query builder and/or orm syntax. The best I can do is learn from my lapse of sound judgment and instead use parameter binding against raw SQL and never interpolate strings of SQL. I regret not adopting this practice sooner. Most of my substantial work resides in that raw SQL feature. So, I use a library that offers good support of that. I have written hundreds of SQL using query builders and ORMs.

threeseed 8y ago
Every ORM (and I mean every one) has the ability to let you either (a) use raw SQL, (b) use raw SQL interpolated with column names or (c) has a syntax which is very close to SQL. Porting should not have been some onerous or career destroying activity. As Job would say I think you've made a huge mistake.
- Dowwie 8y ago
  Those who don't learn from mistakes will repeat them. I've learned from my mistakes and share a lesson today. Most of my substantial work resides in that raw SQL feature. So, I use a library that offers good support of that. I have written hundreds of SQL using query builders and ORMs.
- thanatropism 8y ago
  If you mean the character voiced by Bojack Horseman in "Arrested Development", it's spelled Gob. Amazing how they got Bojack into that human costume.

unixhero 8y ago

He forgot the Jira Query Language. To be fair, it really works!

joaomacp 8y ago
I was so mad when I wanted to make a simple filter on Jira tickets and I had to use "JQL"...

threeseed 8y ago

I had to check the date because this could've been written 20 years ago. Developers don't need other developers to tell them when or when not to use ORM. We know how it works. Some use cases are better for a quick inline SQL whilst others where you have hundreds of tables would lend well to ORM. It's not one size fits all and I'm sure we've all written a few apps in our time to know this to be the case. And this idea you can just learn "one SQL" is pretty laughable. There is a subset of SQL that works across many (but not all) relational databases. But each database has its extensions which you really should be using to get maximum performance. ORMs automatically take advantage of these just to mention. And the behaviours you will get can differ immensely.

scarface74 8y ago
But each database has its extensions which you really should be using to get maximum performance. ORMs automatically take advantage of these just to mention. And the behaviours you will get can differ immensely. But if you use vendor specific extensions you will have “vendor lock-in”. What if someday on a whim your CTO decides to change the database your entire multi billion dollar organization from depending on Microsoft/Oracle and wants to go open source? Yes I’m being sarcastic. In reality, statistically no company makes those types of sweeping changes because the risks are too high and the rewards are too low.
- avmich 8y ago
  Company may very well not have a choice. A couple of weeks ago a customer requested Oracle instead of MS SQL - now somebody is looking into all places checking if SQL is good enough. To me the saying "ORM is a Vietnam of computer science" rings true too often. Somehow projects which I deal with don't really benefit from ORM... so I could live completely with SQL alone.
  - scarface74 8y ago
    If I were writing apps that were sold to customers, that would be even more of a reason to use LINQ - not most ORMs though. I know about a software package that was written in C# that officially supported Sql Server,Oracle, and Mongo and he was able to support all three with basically the same codebase using Linq and runtime translated Expression<Func<T>> statements. His LINQ queries and expressions were the same across data stores.

gabordemooij 8y ago

Shameless plug here, but this is exactly the reason why I did not create a query builder in the RedBeanPHP ORM. At the time (2009) query languages were a real 'hype' (as well as noSQL) but I have always loved SQL and I struggled with all the SQL-alternatives offered by other ORMs. So, when I created RedBeanPHP I decided to complement SQL rather than trying to replace it. Yes, there is some room for improvement, notably in the integration with the programming language (in my case PHP). So the ORM class has some methods to indicate you would like to apply a condition, but the condition itself is just written in pure SQL.I see my ORM more as a bridge between SQL and PHP than a carpet that hides the SQL. Not all DSLs are necessarily bad though. Personally I like to create some very basic and straightforward DSLs to replace horribly complex GUIs with tons of nested menus (aimed at professionals). I think DSLs can be useful for educated computer users who don't know SQL (like people in finance, medicine, statistics). Also there should be a limit to the complexity of a DSL. Not every DSL needs to be turing complete. Simple DSLs that cover the required functionality are the most successful in my experience with customers. However replacing SQL just does not feel right, it's already an awesome language to query a relational database so why replace it? So, for the most part I agree with the author - although I miss the nuance a bit.

nojvek 8y ago
I loved redbean, such a nice library. Don’t do much php nowadays but Django is a great db - object abstraction. You can go raw sql if you want quite easily.

mothsonasloth 8y ago

I've used Hibernate's HQL for many years but now I've started using raw SQL in my Java microservices with libraries like JDBI.

scoom 8y ago

Great, so force SQL to 'join' things that aren't tables and 'select' things that aren't rows. SQL wasn't built for querying every possible data store.

kevsim 8y ago
Yeah, that was my reaction as well. I fully understand his point regarding ORMs that are meant to be an abstraction over a relational database, but imposing the relational model over non-relational datastores sounds like a disaster.
threeseed 8y ago
If only there was a popular example of this. Wait there is: PostgreSQL. They bastardised SQL to support JSON data types just like every other vendor has to do because SQL is for relational and only relational data stores. Is this the utopia that the author is after ? https://www.postgresql.org/docs/9.0/static/hstore.html https://www.postgresql.org/docs/9.0/static/hstore.html
- oulu2006 8y ago
  I thought they were, for the most part, fairly logical extensions to SQL. You don't have to use the JSON/JSONB column types, they are optional. We use them extensively in production and haven't had much difficulty learning them.

zaptheimpaler 8y ago

Yeah I think the only way you can use plain, vanilla SQL to query a system is IF its a relational database. Most of the examples are not.. they solve different problems and have vastly different architectures under the hood. Lucene for example doesn't use SQL because its really solving a different problem - text search. Its a language dedicated to what could only be expressed using something like `LIKE` and regexes in SQL. Same with Splunk, it addresses a different domain and solves different problems that cant be easily expressed in SQL. Same with MongoDB.. its not a relational db. Just because there is a mapping from some SQL queries to some Mongo queries, does not mean they are identical databases. The query language directly impacts the AST generated, which is necessarily tied very closely to the exact capabilities/internals of a system. This post just feels like its based on a cursory glance of what the systems do. If all Lucene had was regexes, it would not perform much better than a SQL database throwing regexes at strings. Its precisely because the query language is finer-grained that it can be better optimized for that usecase.

pk3469 8y ago
Yeah the post seems to confound two mostly unrelated issues. I agree with not reinventing the wheel for relational databases, SQL is fine. But for other domains, the query languages arose out of need, not because someone felt like it. SQL is hot garbage for for non-trivial logic. I can't even imagine what splunk would look like if you had to query it with SQL. If anything, SPL (or rather the piped operators approach) would make a lot more sense as the groundwork for a universal query language that works across many domains.
ddebernardy 8y ago
> Lucene for example doesn't use SQL because its really solving a different problem - text search. Its a language dedicated to what could only be expressed using something like `LIKE` and regexes in SQL. There are much better options than LIKE and regexes in SQL for text search. To mention but two: SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery; https://www.postgresql.org/docs/current/static/textsearch-intro.html https://www.postgresql.org/docs/current/static/textsearch-in... SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-lan... All main engines support full text search in one way or another. TSQL, Oracle, DB2... even SQLite. What more, these engines all support inverted tree indexes in one form or another - i.e. the same type of index Lucene uses - to make this efficient. This is completely different from the typical LIKE or regex query against a column with a BTree index.

tragic 8y ago

I've worked on large projects using ORMs and others using parameterised SQL strings. My experience, FWIW, is that the advantages listed in the article are all real, on top of which there are some more esoteric SQL features (like window functions) that may not be well supported by a given ORM. On the other hand, there is a significant cost in terms of duplication. In ORM code it is trivial to decide a where clause at run time. Not so much in plain SQL. So you end up with a lot of copies of the same query, or else some sort of query assembly layer, at which point you're halfway to an ORM anyway. I am undecided on the issue.

blattimwind 8y ago
I'm convinced there are two classes of ORMs. Those that try to abstract the relational model and those who are the relational model. The former category includes things like Django's ORM: very limited in what you can do, it doesn't actually "get" SQL, mismatch between the ORM API and the database. The latter category includes fewer software; sqlalchemy is my familiar example. Because sqlalchemy does not abstract the relational model, instead mapping it into your application language, it has ~few mismatches between its API and the database. The former category is what "kinda works well enough for CRUD apps and I don't need to know anything about nothing" and then become a major problem, the latter category presumes you know how SQL works but then allows you to work. Random snippet just because I feel like it. def all_children(self): cte = session.query(Tag).filter(Tag.parentid == self.tagid).cte(recursive=True) cte = cte.union_all(session.query(Tag).filter(Tag.parentid == cte.c.tagid)) return session.query(Tag).select_entity_from(cte).all()
- mst 8y ago
  I tend to refer to the latter as ROMs. Ruby's Sequel is also somewhat this. My (perl5) DBIx::Class is somewhat this (modulo all the design mistakes).

alkonaut 8y ago

I think micro ORMs like Dapper are a perfectly reasonable compromise between too much abstraction vs too much abstraction. Hand populating data structures from database query results is error prone and tedious. Using a “big” ORM like hibernate or EF is a leaky and expensive (over-)abstraction. Not just because they typically come with a query dsl but they also come with performance considerations and architectural consequences that are hard to predict. You end up in a situation where estimating work is hard because you don’t know if the ORM is going to play along (making a feature trivial to implement) or hit a snag (making it very time consuming). With smaller abstractions the estimates may be higher in the best case but they vary less. This is one of the most important qualities about a library or stack. Not the average, but the variance in difficulty. On the topic of query DSL’s - the only acceptable ones are those that are native to your programming language. A good example is the F# SQL type provider. If that’s sufficient for your needs, it’s actually quite elegant as far as query languages go - and it’s all F# meaning it’s not a specific language for querying.

tartoran 8y ago
Thanks for pointing out the F# SQL, I just looked it up and read this https://dev.to/kspeakman/dirt-simple-sql-queries-in-f-a37 https://dev.to/kspeakman/dirt-simple-sql-queries-in-f-a37
jordanab 8y ago
I think that the current iteration of EF is still a great fit for small to medium sized projects. Besides offering strongly typed queries (LINQ) and updates, it also provides some additional functionalities such as migrations that such projects really can benefit from.

cryptica 8y ago

It never made sense to me how popular ORMs were. The very first time I used one, I understood straight away that it was a bad idea and I never changed my mind about it (though I tried). Why is it that open source software which adds unnecessary complexity and slows down development tends to become popular but open source software which takes away complexity and speeds up development tends to not get any attention. It's like there is some kind of conspiracy.

justaaron 8y ago
webpack and the entire "modern" js frontend... all because of an inability to manage versioning change across the standard browser js api... great for a giant megacorp looking to waste money on busywork and divide the workload across more employees (front-end devops?!?) less useful when you already have to compile a backend, don't really have a problem with ES5, and understand that you are basically only tasked with making your dom updates dependent upon data model changes, YMMV
- gear54rus 8y ago
  so because you don't see the obvious problem with being stuck on a single version of anything forever, webpack, representing progress in this case, is bad? nice try what does versioning has to do with bubbling, wtf?
  - coldtea 8y ago
    >so because you don't see the obvious problem with being stuck on a single version of anything forever, webpack, representing progress in this case, is bad? In fact, the parent does see (and called) that "obvious problem with being stuck on a single version of anything forever". That's why he doesn't cheer for the band-aid solution that's webpack and doesn't thing it "represents progress".
scarface74 8y ago
I feel the same way about most ORMs - except for LINQ. C# treats queries as first class citizens. There is something very powerful about being able to have a method in your repo... public List<Customer> Find(Expression<Func<Customer,T>> expression) {...} and then your client can use any random expression... Find(c => c.age > 18 && c.age < 65 && c.gender == “M”) and have that expression translated to either SQL, MongoQuery, or anything else that has a LINQ provider. If done correctly, not only can you switch between RDMSs, you can switch between an RDMS and a Nosql data store without changing any client code. When you are writing your unit tests, you can use in memory Lists to mock your data store and still test all of your queries without using a database. Of course you also get type safety, autocomplete, and in the case of NoSql data stores, you enforce a schema over a schemaless data store and get to work with strongly typed objects. I’ve never seen any purpose of an ORM outside of LINQ. They just add another level of complexity and still don’t integrate with the host language naturally.

deleted 8y ago

[deleted]

GedByrne 8y ago

Erik may know SQL but there’s an entire generation of developers who don’t. These developers find it easier to dip into shallow DSLs instead of learning the depths of SQL.

TimJYoung 8y ago
If they can be developers, they can learn SQL. Apart from DDL statements, it's literally 3 DML statements, of which most of the clauses operate exactly the same way across all 3 statements.
- TimJYoung 8y ago
  Sorry, that should be 4 DML statements...

jakoblorz 8y ago

I believe that sqlx for go is amazing in bridging the gap between orm and plain SQL! https://github.com/jmoiron/sqlx https://github.com/jmoiron/sqlx

swah 8y ago
That does almost nothing... I enjoy writing SQL and Go with sqlx, but testing other designs/ideas becomes so slow compared to Python/Django. I'll be using Gorm on my next project, because life is so short..

biggio 8y ago

SQL is syntactic sugar for first order logic. It is much easier to query relational database with SQL than abstracting relations with ORM.

scarface74 8y ago
How is “select * from customer where age > 18 and age < 65” Any more of an abstraction and easier than from context.Customer where c.Age > 18 && c.Age < 65 With one it’s a magic string where you could have typos. With the other you get compile time syntax checking.
- angersock 8y ago
  You must not use good SQL IDEs...try Datagrip.
  - scarface74 8y ago
    Sql is part of my code that doesn’t help....
- biggio 8y ago
  I understood your SQL example but I don't understand your counter example.
  - scarface74 8y ago
    The second is LINQ within C#
- CuriouslyC 8y ago
  If you use a modern IDE and extract your SQL into separate files you can get syntax checking, and even the ability to run the query on-server directly and get a table of results in a table-formatted tab with a single click. How much do you have to do to get your ORM to run your query, and are the results as easy to work with?

culturedsystems 8y ago

The custom query language I find most annoying (possibly just because it's the one I deal with the most), is JPQL, the query language of the Java Persistence Architecture. JPA explicitly targets relational databases, so there's no obvious reason why you couldn't just use SQL. You might think the advantage of a custom query language would be that it could abstract over differences between SQL implementations, but JPQL is underspecified and just gives you the underlying database behaviour in many cases. I spent a day trying to figure out why my tests passed but the staging environment was claiming that 0 was greater than 0; turns out there was a difference in rounding behaviour between the in-memory database used for tests, and the real database, which JPA was cheerfully glossing over.

supermatt 8y ago

And I’m assuming your toolbox just contains a hammer? Granted, there’s a lot of duplication of effort around, but in many of the examples you cite they are trying to solve different problems.

TomK32 8y ago
There's a claw hammer, ball pein, mallet, bronze hammer, rubber hammer if you don't want to band your head on the table, and of course the mighty sledge hammer.
- supermatt 8y ago
  So just hammers :)

TomK32 8y ago

Also, learn just one programming language. Lisp obviously.

jcore24 8y ago
Unironically this. I feel like a zealot saying it but having eDSLs with painless interoperability makes lisp a joy to work with.

gregopet 8y ago

I really really like what jOOQ has done for SQL under Java (and other JVM languages). It's basically a type safe interface to SQL that anyone who knows SQL can grok in minutes and it provides constructs to help bridge the gap between SQL and the programming language. Among other features it has tools to help migrate between different databases (and more are planned) - something that is usually considered to be a strong point of ORMs. I've used a couple of ORMs and jOOQ blows them all away easily. It's not the first product that tried this approach but jOOQ does it really well. I've partly or fully converted at least 3 Hibernate projects to jOOQ and all my recent JVM projects use it exclusively. In my opinion products like jOOQ make ORMs redundant for most use cases.

_Codemonkeyism 8y ago

Mongo is one of the most painfull, it's inconsistent, simple queries take dozens of $ and { }, and the db.tablename.<...> is annoying.

pmontra 8y ago

I'll be happy to write my own SQL (I do for complex queries) but I don't want to extract results manually from results and tweak the code every time I change the schema. At least those parts of ORMs must stay.

dec0dedab0de 8y ago

This is how SQL injection makes a comeback. No thank you, I'll stick with my orm.

avmich 8y ago
SQL standard defines a couple of special characters within strings - like ' and * . Not too complex. If you escape input - say, using a library function - you won't get injection problem.

Too 8y ago

SQL reasonably standardized? You can't even write hello world without running into vendor differences. Microsoft has SELECT TOP FROM, while Mysql has SELECT FROM LIMIT.

gesman 8y ago

There are reasons different programming languages exists. And so as query languages. Different stacks, different ways to handle, optimize and accelerate queries over structured (SQL) and unstructured, schema-on-the-fly (SPL) datasets. If you don’t want to learn - it’s a personal choice. You can ride a bicycle that works everywhere while others prefer driving sport cars on a highways.

jmartrican 8y ago

This is why I really like JOOQ for Java. Its ORM but with SQL. Best of both worlds IMHO.

oftenwrong 8y ago
jOOQ is my favourite Java library. The code generation effectively extends type checking down to the db types and makes it easy to build your persistence layer without messy object mapping... if used properly; I've seen some setups that misused jOOQ.

zzzeek 8y ago

"Let’s dispel with the myth that ORMs make code cleaner. Join the embedded-SQL movement and discover a much more readable, much more straightforward way to query databases." Any argument whatsoever for this? Would love to see an example of how when you do away with the whole object persistence layer, and you just have a SQL string (which ORMs allow you to do anyway), how do you get your parameters over, how do you get your rows back, how do you manage your transactional scope, how do you get your rows in and out of your objects? all of which has nothing to do with a SQL string. Well let's read on: "Erik Bernhardsson... is the CTO at Better, which is a startup changing how mortgages are done. I write a lot of code, some of which ends up being open sourced, such as Luigi and Annoy. " Open source stuff! Let's go there and see an example of this "write raw SQL and don't use any persistence libraries and your code is readable and straightforward", because nobody ever seems to actually want to illustrate this and how they don't end up writing their own ORM anyway, and we see, oh https://github.com/spotify/luigi/blob/master/luigi/db_task_history.py#L48 https://github.com/spotify/luigi/blob/master/luigi/db_task_h..., it's SQLAlchemy ORM. Foiled again in my search to see this elusive super clean and simple raw SQL with no ORM that doesn't reinvent an ORM anyway. Which is the real "myth" ?

TimJYoung 8y ago
"Would love to see an example..." Delphi has always had a default non-ORM dataset layer (there are ORMs for it), and it works like this for queries: 1) There's a base TDataSet class (Object Pascal uses "T" to denote "Type") that handles all abstract column and row storage and manipulations, with no pre-disposed notion of how to actually load such rows. Dataset columns are abstracted away also, and provide a lot of automatic type conversions and functionality like BLOB column stream access. Having this base class allows disparate descendant classes to easily interact with each other without knowing the details of how the data got there. All query objects descend from this base class, and fill in all of the functionality for actually interacting with the database server/engine. 2) For query objects, the SQL string gets assigned to a query object property (typically called "SQL"). Parameters are specified as named parameters by using colon notation (:Parameter) instead of "?". During this string assignment, a property setter method parses the SQL string and automatically sets up the parameters as a collection that is another property of the query object. These parameters do not have any type information assigned to them at this point. The developer can choose to just manually assign values to the parameters using type-specific parameter properties at this point. Any type differences are managed by the underlying object, so assigning an integer to a string parameter will result in an automatic conversion. 3) If you manually prepare the query object using a Prepare method, then the SQL is sent over to the database server to be prepared and any type information about the parameters is sent back from the database server to be assigned to the parameters collection (depends upon the database server). If you don't manually prepare the query, then it is automatically prepared during the query execution (4). 4) The SQL is executed using an Execute or Open method. If the SQL is a SELECT statement, the query object is automatically populated with the rows and the rows are managed using the base TDataSet functionality. There are methods for navigation, updating, etc. and updates of non-directly-updateable result set cursors are managed by the query object. Query objects are linked to database objects, and the database objects manage the transactional scope for updates. You can also use such database objects to execute one-off SQL statements where the only thing you're interested in is the result set row count or the affected row count. The interesting part is how Delphi handles static-typing of result set columns in your application. In the IDE, you can right-click on any query object and have it automatically populate "persistent" column objects whose class type reflects the underlying result set column type. This means that if you try to access or assign the column object's raw Value property, this access/assignment is subject to type enforcement by the compiler. However, you are free to use other properties that perform type conversion, in which case you'll get a runtime exception if the type conversion fails for any reason. You can also assign names to these column objects, allowing you to write easy-to-read code like this: OrderCustomerID.Value := 1000; So, you can get a lot of the benefits of an ORM using an architecture like this without having to sacrifice the flexibility of manually-constructed SQL statements. A lot of this depends upon the peculiarities of the Delphi component library and IDE, though, so how transferrable this to other environments is an open question.

manish_gill 8y ago

This resonated strongly with me. Almost all of the heavy DB interaction projects I've done in the last couple of years - be it in Python, Go or Java, the SQL layer has almost always been raw queries going to the DB. Makes things so much easier. I don't miss SQLAlchemy where I would trawl through documentation to figure out how to do X or Y. As for SQL standardization, +1 to that. Learning a new pseudo-SQL is only fine if there are additional features on top of the DB, but I've encountered databases where the SQL support was present in the name only and extremely limited, with people making do with UDFs in order to achieve basic things.

dahdum 8y ago
I’ve had the same experience, and as soon as the interaction became very complex (multi table, multi row updates) we just moved it all into stored procedures.
- Udik 8y ago
  Never understood what's the thing with stored procedures. You soon find that an indeterminate amount of your business logic is written in stored procedures, and in an opaque language that is fit for manipulating data, not for implementing business logic; not to mention the annoyance of running updates on the database, which works ina fundamentally different way from deployable code.
  - zzzeek 8y ago
    what it usually means is, "we gave the DBA the job to do and now we don't deal with it at all"

jcadam 8y ago

> "It’s not like people learn an ORM instead of learning SQL anyway." BWAHAHAHAHA!!! No. I have to use ORMs at work because they are forced on me. Same thing with Eclipse, and GWT, and a million other things that I detest because my employer has structured their development processes around hiring average talent and getting average(ish), repeatable results (I'm serious, I work with a lot of developers who only know how to code in Java, in Eclipse -- it's maddening). I'm the malcontent who doesn't fit in because I say things like "Let's just use SQL." or "I promise you, this architecture is not scalable, regardless of your seniority over me."

wheresvic1 8y ago

In my experience ORMs work well for smaller projects and at the beginning. Once the code matures, it is much more performant and simpler to use direct SQL. One thing that really resonated with me from the article is the following: > 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 (in my case, Postgres/Redshift). Totally agree, a data dump should really be a standard feature (and not take ages to produce either).

mst 8y ago
Having written assorted data layers: If you can't replace a generated query with a chunk of literal SQL when optimisation or raw power indicates it's the best option, the data layer sucks. Sadly, being software, "the data layer sucks" is true of most data layers.

vbezhenar 8y ago

I still yet to find sane JDBC wrapper library for Java. JDBC itself has terribly verbose API. And any popular library I've encountered is not very nice to use. I end up buliding half-baked JDBC wrapper for every project I'm writing. And Hibernate at least have good enough API.

nailer 8y ago

> I just want my SQL back. It’s a language everyone understands I'm sure lots of people do understand SQL. But if you started programming post 2010 it's entirely possible you've never had to deal with a row database. My case: - GAE datastore - MongoDB - RethinkDB Again not discounting those who've been programming longer, it's just that JSON-stores are becoming default and the amount of people who don't know SQL is probably more than you think.

nojvek 8y ago
I really loved rethinkdb rql. Was a very elegant language to deal with json datastores over mongodb

tjpnz 8y ago

This reminds me of something that happened in a previous gig where our team was working primarily with SQL. The company rockstar one-day decided to rid us all of our perceived misery by introducing a REST interface over our existing databases complete with it's own query language that he managed to foist upon us with the clout he had with upper management. The query language was limited at best even when compared to some of the examples in the article. Initially all it provided was basic matching against several columns and the ability to do rudimentary paging across resultsets. Naturally we had problems with this as a big part of our job was writing reports relying heavily on joins and aggregation - we were left in a situation familiar to anyone whose used NoSQL joining everything in code but without all of the niceties that come with maturity. It's a given that team productivity and general morale took a bit of a dive at this point. My current theory to the proliferation of these query languages is ignorance. Aside from a severe case of NIH the developer behind our query language exhibited a flawed understanding of SQL. If you're planning to roll your own query language you probably shouldn't without first considering SQL, your intended users and the use cases that they're trying to satisfy.

scarface74 8y ago
Initially all it provided was basic matching against several columns and the ability to do rudimentary paging across resultsets. Naturally we had problems with this as a big part of our job was writing reports relying heavily on joins and aggregation - we were left in a situation familiar to anyone whose used NoSQL joining everything in code but without all of the niceties that come with maturity. It's a given that team productivity and general morale took a bit of a dive at this point. Yes being forced to use a REST interface over reports is a bad idea but that has nothing to do with joins over a NoSQL data store. Mongo does joins via the $lookup function and the C# Mongo driver will translate the LINQ code to MongoQuery for you. https://www.axonize.com/blog/joining-collections-mongodb-using-c-driver-linq/ https://www.axonize.com/blog/joining-collections-mongodb-usi... Worse case even if you do get data sources from separate places, it’s just as easy to do joins on two lists as it is to do in Sql using LINQ.
tannhaeuser 8y ago
This sounds all too familiar. Developers having a REST epiphany and thinking they're clever by mapping relational rows to REST resources is also very common with Spring apps (Spring data in particular).

Udik 8y ago

Just to go slightly off-topic, but anybody here felt it would be a dream if Excel allowed pure SQL instead of those absurd formulas like "sum if" etc? (I believe that it's (relatively) recently become possible to define tables as SQL data sources, not sure how practical that is or if it's used at all.)

jodrellblank 8y ago
"define tables as SQL data sources" ? Excel can query external data sources with SQL queries, and pull the results back into the spreadsheet. And via ODBC you can use .xls files as data sources, and query them with SQL style syntax from other programs. (I don't know how far beyond the basic "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]" it can support). Both of those date back - https://www.connectionstrings.com/microsoft-excel-odbc-driver/ https://www.connectionstrings.com/microsoft-excel-odbc-drive... - most look like Excel 2000 or Excel 97.

gonyea 8y ago

This is some A+ bah-humbuggery. You want companies to parse sql and translate that to backend api calls? What a nightmare. Data isn’t always co-located, and not all data is allowed to be fetched together (eg, protecting user privacy in Google Analytics).

luord 8y ago

Agreed on using SQL under the hood for every system, but I don't have a problem with ORMs.

tomcam 8y ago

OP's frustrations are quite understandable but I can think of a couple of areas where ORM-like concepts have worked well. PyDAL[0] and Linq are both incredibly expressive and PyDAL is also low overhead (it's not an ORM, it's a data access language, which means it doesn't have the baggage of an object-oriented layer). Another reason to use a DAL like PyDAL is that, properly written, it can abstract away the data layer such that you can switch from (for example) MySQL to PostGres to Google Cloud SQL with essentially no retooling required. Finally, using a DAL can help keep inexperienced programmers on track with security, accessibility, and performance requirements. [0] https://github.com/web2py/pydal https://github.com/web2py/pydal

tannhaeuser 8y ago
> Finally, using a DAL can help keep inexperienced programmers on track with security, accessibility, and performance requirements. This is a sure way they'll never learn the real thing. I know since I'm sitting next to a (young but talented) guy who knows only Spring and JPA, and freaks out all the time because the developer of the legacy system had dared to assemble SQL queries manually in the code (which are complex enough I personally hadn't bothered using JPQL in a trial-and-error development model either). Wasn't the post exactly about not wasting novice and other developer's time with crap criteria APIs and such? Just compare the sheer size of the SQL spec or reference manual against the shallow description of JPQL to judge which language you should be using for a project with any kind of depth.

nojvek 8y ago

I’m surprised no one yet had mentioned Facebook’s osquery tool. Even though most things aren’t relational databases, a uniform way to query them and join different datasources together is a very powerful idea. Using sql to query a whole bunch of stuff has been very nice. I just wish the excellent TablePlus client could use osquery under the hood to show a lot of system level things in a nice pretty UI.