Purpose of ORMs is not to make SQL “easier” or “cleaner” but to make SQL composable. Otherwise you have to compose SQL queries by concatenating strings.
The problem is that SQL itself is the most garbage query language. If it was based on data structures such as json or s-expressions or whatever, instead of “fake natural language”, ORMs would be mostly unnecessary.
Honey SQL uses s-expressions to represent SQL, and it maps directly to SQL syntax so there’s no additional indirection. The semantics are preserved, and you’re just using a different syntax to express them.
After using Yesql and HugSQL in Clojure I liked the approach so much, we ended up building a similar solution in OCaml, where we have a PPX that just generates all the annoying boilerplate required to run SQL queries and exposes a type safe interface to it. It is not an ORM so it is very clear how the code maps to SQL and it is also rather simple to implement, without having to worry about impedance mismatch. Of course it doesn’t handle migrations or the like but these are outside of the scope of the system.
Initial impressions from internal users were enthusiastic.
It is a very early release and I expect there will still be a number of changes which is why we haven’t submitted it to OPAM yet, but you can check out ppx_mysql.
In spirit it is similar to PG’OCaml, but PG’OCaml talks to Postgres on compile time and MySQL can’t do that and also I prefer not to require talking to external services when building so all the type information has to be specified manually.
oh interesting, I’ll definitely check this out! The idea sounds intriguing, and I enjoyed working with PG’OCaml previously, for what little I had to do with it. Thank you very much!
I use the ORM for create, update and delete. I usually write raw sql for reads. Good ORMs also provide migrations and other ways to manage the database and schema.
I have been in situations where the database is managed independently of the ORM. In that case, I have found a lot less value in an ORM.
I much prefer the ActiveRecord syntax to SQL mainly due to the defaults being much saner. In ActiveRecord the default is to select all columns unless you specify otherwise which is almost always what you want. In SQL the default is to select all records from the able unless you specify a WHERE, in Active record the default is to throw an error unless you use .all or .find/.where.
Every now and then I have to use regular SQL to call a DB specific function but in general the ActiveRecord syntax ends up much shorter and much safer than SQL.
article-meta question: did anyone else have an impossible time getting this article to load? even with 1st party scripts enabled, all I get is a blank page.
I am curious. I have been looking at a few job postings and they say be expert in SQL for a role like data analysis or such. Practically, I have only ever used basic statements for querying directly. If I have to do a complicated task I end up handing the control over to the ORM paradigm and in this way I end up never learning SQL well enough. Can somebody else provide some insight whether I am doing it the right way? People who got good at query languages, was it on the job or before grtting such a job which mandated SQL like languages?
I learned most of my SQL skills on the job. I struggled a lot with SQL in the university course I took, and I didn’t grok it. But when I started to need SQL on the job, it started to “click” and I learned it properly. To be honest, I committed the sin of rolling my own framework way back when, but it meant I was somewhat forced to learn a lot of underlying stuff. When you have to generate SQL from your ORM, that’s a fantastic way to learn the nitty-gritty of SQL. You’ll even encounter the limitations of your RDBMS, as you’re almost guaranteed to run into them whilst “compiling” SQL.
I’ve also noticed that as you start to need more advanced features, many ORMs don’t support it and you’ll need to drop down to the SQL level more. This is especially true of PostgreSQL, as it has a very extended feature set. Django has some contributed support for lots of Postgres features, but other ORMs tend to cater only to the lowest common denominator, so you can’t do fancy things. Especially something like a complex UPDATE statement with a FROM query on a related table or doing an INSERT of a complex SELECT where you dynamically generate values based on another query, while returning the inserted values.
Of course, when you aren’t even aware of advanced features, you’re not likely to even want to use them. To get an awareness of what’s possible, I’d suggest reading Use the Index, Luke and just the Postgres manual, which is quite well-written and can be read like a book.
When making data migrations you’ll often want to do it directly in SQL for performance and memory usage reasons (doing it all in the procedural language is a no-go if you have a huge data set). Big data sets are the key: if you have lots and lots of data, you can’t simply slurp everything into memory and iterate over it, updating records one at a time. Bulk updates of non-static values tend to require dropping down to SQL pretty quickly unless you have a very powerful ORM.
I picked up 99% if my SQL knowledge on the job. My first job offered to pay for Microsoft certificates test taking, so I went and got the MCTS in SQL Server 2008. It was a good introduction to all the various features of SQL Server. Not sure that this qualified me as an expert, but I definitely have a decent feel for the SQL server feature set. I suspect there are similar resources for MySql, Postgresql and so on.
Since then, it’s mostly just been the odd thing here or there. If SQL interests you, I definitely recommend either getting a book, or reading up on things like window functions, (recursive) common table expressions materialized/indexed views, window functions, how aggregates work, and so on. Digging into the exclusive functionality of the database you use is also recommend There’s some interesting stuff under the hood if you take a look.
Probably the most frustrating thing about SQL, however is that most abstraction features, like functions, stored procedures, or views do not compose well from a performance perspective. One layer of them usually works well, two or more is often trouble.
On the job I learned how to use SQL to get order-of-magnitude speedups over ORM. Turns out databases are really good at querying data! For me, common-table expressions were the gateway drug and window functions were what gave me the most power.
As much as I can agree with the basic premise I think the author misses the other side is that even in the traditional RDBMS space SQL is slightly different. This is the reason that although I end up having to dive in the SQL Alchemy docs quite frequently when I use that library it tends to guarantee my program can be used more easily with more DB without having to worry about their individual quirks in how SQL is implemented.
And dare I say it I kind of like mongos query language, once you get your head around it, there are some cool things it can do. It is not the slick declarative syntax of SQL, but it has its merit for that domain.
If a project has an explicit requirement to support multiple databases, then SQL differences can become a problem. However, my experience is that it’s not the case for most projects. Typically you pick a database that you’re going to be using up front, and you’re not going to be switching databases in the middle of development.
I also think that the approach taken by Honey SQL is a good middle ground. It represents queries as structured data, so you’re able to easily manipulate and compose them programmatically. However, it maps directly to SQL avoiding the problem of having a leaky abstraction on top of it.
I’m not the author, but I’ve used many ORMs over the years and my experience is that the approach simply doesn’t work well in practice. It’s pretty much impossible to generate efficient queries automatically for a general case. In most cases you’re going to be writing SQL for performance unless your app deals with trivial amounts of data. The worst part is that ORMs look like they work during development because you’re not hitting heavy loads, and you often end up seeing problems when you’re already in production.
SQL is already a great DSL for writing relational queries, and there’s no real value in wrapping it with another leaky abstraction. Personally I really like the approach that HugSQL takes which lets you write plain SQL queries and automatically generate query functions from them.
The problem with raw SQL, though, is that it really gets in your way if you ever decide to switch SQL backends. Like, maybe this is more of a startup-y issue but having the ability to safely switch between them is super helpful when the time comes that you’ll need it.
I think that the bigger concern with ORMs is that so many exist that are poorly designed. It’s really is a hard problem, and choosing the right one is difficult as well. Some software like Django has done a great job of writing great ORMs, but other platforms like NodeJS have made it hell.
I’d prefer raw SQL in NodeJS simply because the ORMs aren’t there, but the state of ORMs in any ecosystem is a completely separate issue than whether ORMs are good or not.
I wouldn’t go so far as to call it “great”, but it’s good enough for most things. At the very least I wish it were hard to write queries that are prone to injection attacks.
In general I agree that most ORMs and query DSLs fall down in one of two ways: they aren’t general enough and fall apart when you want to do anything complex (e.g. activerecord), or they try to do everything, but the syntax is so arcane that raw SQL is easier (e.g. almost every relational query DSL I ever saw before activerecord became popular).
Note that HugSQL addresses the injection attack problem because queries are inherently parametrized. You write the SQL with placeholders for the variables, and then you pass in a map keys on the names of the placeholders. The values are automatically sanitized when they’re injected.
So this post should have the
ranttagUsers can suggest tags.
Purpose of ORMs is not to make SQL “easier” or “cleaner” but to make SQL composable. Otherwise you have to compose SQL queries by concatenating strings.
The problem is that SQL itself is the most garbage query language. If it was based on data structures such as json or s-expressions or whatever, instead of “fake natural language”, ORMs would be mostly unnecessary.
Honey SQL uses s-expressions to represent SQL, and it maps directly to SQL syntax so there’s no additional indirection. The semantics are preserved, and you’re just using a different syntax to express them.
After using Yesql and HugSQL in Clojure I liked the approach so much, we ended up building a similar solution in OCaml, where we have a PPX that just generates all the annoying boilerplate required to run SQL queries and exposes a type safe interface to it. It is not an ORM so it is very clear how the code maps to SQL and it is also rather simple to implement, without having to worry about impedance mismatch. Of course it doesn’t handle migrations or the like but these are outside of the scope of the system.
Initial impressions from internal users were enthusiastic.
that sounds interesting; has it been released anywhere?
It is a very early release and I expect there will still be a number of changes which is why we haven’t submitted it to OPAM yet, but you can check out ppx_mysql.
In spirit it is similar to PG’OCaml, but PG’OCaml talks to Postgres on compile time and MySQL can’t do that and also I prefer not to require talking to external services when building so all the type information has to be specified manually.
oh interesting, I’ll definitely check this out! The idea sounds intriguing, and I enjoyed working with PG’OCaml previously, for what little I had to do with it. Thank you very much!
I use the ORM for create, update and delete. I usually write raw sql for reads. Good ORMs also provide migrations and other ways to manage the database and schema.
I have been in situations where the database is managed independently of the ORM. In that case, I have found a lot less value in an ORM.
Relevant: The language strangeness budget
I much prefer the ActiveRecord syntax to SQL mainly due to the defaults being much saner. In ActiveRecord the default is to select all columns unless you specify otherwise which is almost always what you want. In SQL the default is to select all records from the able unless you specify a WHERE, in Active record the default is to throw an error unless you use .all or .find/.where.
Every now and then I have to use regular SQL to call a DB specific function but in general the ActiveRecord syntax ends up much shorter and much safer than SQL.
I largely agree, which is why I wrote (shameless plug) dpdb
article-meta question: did anyone else have an impossible time getting this article to load? even with 1st party scripts enabled, all I get is a blank page.
I am curious. I have been looking at a few job postings and they say be expert in SQL for a role like data analysis or such. Practically, I have only ever used basic statements for querying directly. If I have to do a complicated task I end up handing the control over to the ORM paradigm and in this way I end up never learning SQL well enough. Can somebody else provide some insight whether I am doing it the right way? People who got good at query languages, was it on the job or before grtting such a job which mandated SQL like languages?
I learned most of my SQL skills on the job. I struggled a lot with SQL in the university course I took, and I didn’t grok it. But when I started to need SQL on the job, it started to “click” and I learned it properly. To be honest, I committed the sin of rolling my own framework way back when, but it meant I was somewhat forced to learn a lot of underlying stuff. When you have to generate SQL from your ORM, that’s a fantastic way to learn the nitty-gritty of SQL. You’ll even encounter the limitations of your RDBMS, as you’re almost guaranteed to run into them whilst “compiling” SQL.
I’ve also noticed that as you start to need more advanced features, many ORMs don’t support it and you’ll need to drop down to the SQL level more. This is especially true of PostgreSQL, as it has a very extended feature set. Django has some contributed support for lots of Postgres features, but other ORMs tend to cater only to the lowest common denominator, so you can’t do fancy things. Especially something like a complex
UPDATEstatement with aFROMquery on a related table or doing anINSERTof a complexSELECTwhere you dynamically generate values based on another query, while returning the inserted values.Of course, when you aren’t even aware of advanced features, you’re not likely to even want to use them. To get an awareness of what’s possible, I’d suggest reading Use the Index, Luke and just the Postgres manual, which is quite well-written and can be read like a book.
When making data migrations you’ll often want to do it directly in SQL for performance and memory usage reasons (doing it all in the procedural language is a no-go if you have a huge data set). Big data sets are the key: if you have lots and lots of data, you can’t simply slurp everything into memory and iterate over it, updating records one at a time. Bulk updates of non-static values tend to require dropping down to SQL pretty quickly unless you have a very powerful ORM.
I picked up 99% if my SQL knowledge on the job. My first job offered to pay for Microsoft certificates test taking, so I went and got the MCTS in SQL Server 2008. It was a good introduction to all the various features of SQL Server. Not sure that this qualified me as an expert, but I definitely have a decent feel for the SQL server feature set. I suspect there are similar resources for MySql, Postgresql and so on.
Since then, it’s mostly just been the odd thing here or there. If SQL interests you, I definitely recommend either getting a book, or reading up on things like window functions, (recursive) common table expressions materialized/indexed views, window functions, how aggregates work, and so on. Digging into the exclusive functionality of the database you use is also recommend There’s some interesting stuff under the hood if you take a look.
Probably the most frustrating thing about SQL, however is that most abstraction features, like functions, stored procedures, or views do not compose well from a performance perspective. One layer of them usually works well, two or more is often trouble.
On the job I learned how to use SQL to get order-of-magnitude speedups over ORM. Turns out databases are really good at querying data! For me, common-table expressions were the gateway drug and window functions were what gave me the most power.
As much as I can agree with the basic premise I think the author misses the other side is that even in the traditional RDBMS space SQL is slightly different. This is the reason that although I end up having to dive in the SQL Alchemy docs quite frequently when I use that library it tends to guarantee my program can be used more easily with more DB without having to worry about their individual quirks in how SQL is implemented.
And dare I say it I kind of like mongos query language, once you get your head around it, there are some cool things it can do. It is not the slick declarative syntax of SQL, but it has its merit for that domain.
If a project has an explicit requirement to support multiple databases, then SQL differences can become a problem. However, my experience is that it’s not the case for most projects. Typically you pick a database that you’re going to be using up front, and you’re not going to be switching databases in the middle of development.
I also think that the approach taken by Honey SQL is a good middle ground. It represents queries as structured data, so you’re able to easily manipulate and compose them programmatically. However, it maps directly to SQL avoiding the problem of having a leaky abstraction on top of it.
It most likely took you more time to write this rant than it takes to be productive in a new ORM.
I’m not the author, but I’ve used many ORMs over the years and my experience is that the approach simply doesn’t work well in practice. It’s pretty much impossible to generate efficient queries automatically for a general case. In most cases you’re going to be writing SQL for performance unless your app deals with trivial amounts of data. The worst part is that ORMs look like they work during development because you’re not hitting heavy loads, and you often end up seeing problems when you’re already in production.
SQL is already a great DSL for writing relational queries, and there’s no real value in wrapping it with another leaky abstraction. Personally I really like the approach that HugSQL takes which lets you write plain SQL queries and automatically generate query functions from them.
The problem with raw SQL, though, is that it really gets in your way if you ever decide to switch SQL backends. Like, maybe this is more of a startup-y issue but having the ability to safely switch between them is super helpful when the time comes that you’ll need it.
I think that the bigger concern with ORMs is that so many exist that are poorly designed. It’s really is a hard problem, and choosing the right one is difficult as well. Some software like Django has done a great job of writing great ORMs, but other platforms like NodeJS have made it hell.
I’d prefer raw SQL in NodeJS simply because the ORMs aren’t there, but the state of ORMs in any ecosystem is a completely separate issue than whether ORMs are good or not.
I wouldn’t go so far as to call it “great”, but it’s good enough for most things. At the very least I wish it were hard to write queries that are prone to injection attacks.
In general I agree that most ORMs and query DSLs fall down in one of two ways: they aren’t general enough and fall apart when you want to do anything complex (e.g. activerecord), or they try to do everything, but the syntax is so arcane that raw SQL is easier (e.g. almost every relational query DSL I ever saw before activerecord became popular).
Note that HugSQL addresses the injection attack problem because queries are inherently parametrized. You write the SQL with placeholders for the variables, and then you pass in a map keys on the names of the placeholders. The values are automatically sanitized when they’re injected.
Yeah, my complaint isn’t with SQL per se, but with how SQL is often used in application frameworks.