4 ms·

How many people are actually using raw SQL on the backend? I thought people got tired of maintaining strings of raw SQL and migrated to query builders or ORMs.

How many people are actually using raw SQL on the backend? I thought people got tired of maintaining strings of raw SQL and migrated to query builders or ORMs. > I usually end up with a bunch of Lodash (groupBy, filter, map, reduce) to shape the data I get from the server. I mean, we are also doing the same thing with ORMs on the backend.

villasv 6y ago
It's pretty rare for an ORM to cover all possible scenarios. I just introduced another raw query to our database because our ORM isn't able to generate `insert into ... select ... where not exists (select ...)` which is crucial for one critical bit that needs to handle idempotent writes.
- edgyquant 6y ago
  This is true, and there is definitely a place for raw sql, but in my experience ORMs cover most cases and I only have to write SQL queries for special cases.
quasarj 6y ago
I'm sure some did, but I got sick of the slowness and lack of expressive ability with every ORM I tried and just went back to SQL strings.
- mobilemidget 6y ago
  If you don't fully understand what happens under the hood with those ORMs and limit yourself to its, well limitations, then somebody is going to have a bad time. And in my experience too, with almost with all (read all that I tried) of them, some queries end up to be way easier to write yourself, and most frequent this is performance/resource issue.
adamrezich 6y ago
for my latest contracted small web app project, I didn't see the need to get an ORM set up—I opted instead to just go for plain SQL statements in a single PHP file, even going so far as to use `?> ... <?` for "templating" HTML blocks, and a fat query string switch for the different subpages/GET/POST actions on the site. I'm sure almost everyone here, especially web developers, would absolutely cringe at what I've done, but for the scope of this small project it works extremely well. after my last job at a remote django/react shop, it was a breath of fresh air to build something "from scratch" without setting up and configuring large amounts of boilerplate, class structures, etc.—instead, the website is quite procedural, just like I would've written when I was teaching myself web development in high school in the mid 00s (except, without the gaping SQL injection holes). designing the web app this way let me build it quickly and rapidly iterate on features as the client requested, and he ended up extremely happy with the results.
ggregoire 6y ago
> How many people are actually using raw SQL on the backend? I sure do. Nothing beats being able to just copy/paste a SQL query from a code base to your client to see what's going on. Then tweak that said query until it works as expected. When I do backend development involving the database a lot (and I do use a lot of views, functions and triggers), I spend more time in my database client than in my IDE.
- viklove 6y ago
  I think ActiveRecord beats this, since the code ends up more readable, but you can still run it in the rails repl, and the generated SQL syntax is printed in both the webserver logs and the repl output.
  - ggregoire 6y ago
    > ActiveRecord beats this, since the code ends up more readable It's subjective and I tend to disagree. Especially for very simple and very complex queries. Also, unless you are a following a "code-first" approach and doing all your schema migrations through the ORM, you have to redefine your tables, columns and relationships a second time and keep them up-to-date with every change, which is a huge hassle. Obviously if your app is a simple CRUD app, might be simpler to just use Rails/Django/Symfony with an ORM and embrace the code-first approach.
    - viklove 6y ago
      Rails has very robust and well documented generators if you find it's a lot of work to create the migration file and the model file when you're changing the schema (I usually just create them manually though). It's a pretty standard workflow for rails developers, and given that rails is used by a lot of companies that are not "simple CRUD apps" (like AirBnB, Stripe, etc.) I'd say it's definitely very useful in many scenarios. In fact, I'd argue that the "code-first" approach as you call it is actually more useful, because rails gives you bindings for before/after commit hooks, validators that aren't supported by SQL, etc. > Also, unless you are a following a "code-first" approach and doing all your schema migrations through the ORM I've literally never heard of a rails team migrating their DBs manually. Everyone uses ActiveRecord because it's a joy to use and very well supported and documented.

villasv 6y ago

It's pretty rare for an ORM to cover all possible scenarios. I just introduced another raw query to our database because our ORM isn't able to generate `insert into ... select ... where not exists (select ...)` which is crucial for one critical bit that needs to handle idempotent writes.

edgyquant 6y ago
This is true, and there is definitely a place for raw sql, but in my experience ORMs cover most cases and I only have to write SQL queries for special cases.

quasarj 6y ago

I'm sure some did, but I got sick of the slowness and lack of expressive ability with every ORM I tried and just went back to SQL strings.

mobilemidget 6y ago
If you don't fully understand what happens under the hood with those ORMs and limit yourself to its, well limitations, then somebody is going to have a bad time. And in my experience too, with almost with all (read all that I tried) of them, some queries end up to be way easier to write yourself, and most frequent this is performance/resource issue.

adamrezich 6y ago

for my latest contracted small web app project, I didn't see the need to get an ORM set up—I opted instead to just go for plain SQL statements in a single PHP file, even going so far as to use `?> ... <?` for "templating" HTML blocks, and a fat query string switch for the different subpages/GET/POST actions on the site. I'm sure almost everyone here, especially web developers, would absolutely cringe at what I've done, but for the scope of this small project it works extremely well. after my last job at a remote django/react shop, it was a breath of fresh air to build something "from scratch" without setting up and configuring large amounts of boilerplate, class structures, etc.—instead, the website is quite procedural, just like I would've written when I was teaching myself web development in high school in the mid 00s (except, without the gaping SQL injection holes). designing the web app this way let me build it quickly and rapidly iterate on features as the client requested, and he ended up extremely happy with the results.

ggregoire 6y ago

> How many people are actually using raw SQL on the backend? I sure do. Nothing beats being able to just copy/paste a SQL query from a code base to your client to see what's going on. Then tweak that said query until it works as expected. When I do backend development involving the database a lot (and I do use a lot of views, functions and triggers), I spend more time in my database client than in my IDE.

viklove 6y ago
I think ActiveRecord beats this, since the code ends up more readable, but you can still run it in the rails repl, and the generated SQL syntax is printed in both the webserver logs and the repl output.
- ggregoire 6y ago
  > ActiveRecord beats this, since the code ends up more readable It's subjective and I tend to disagree. Especially for very simple and very complex queries. Also, unless you are a following a "code-first" approach and doing all your schema migrations through the ORM, you have to redefine your tables, columns and relationships a second time and keep them up-to-date with every change, which is a huge hassle. Obviously if your app is a simple CRUD app, might be simpler to just use Rails/Django/Symfony with an ORM and embrace the code-first approach.
  - viklove 6y ago
    Rails has very robust and well documented generators if you find it's a lot of work to create the migration file and the model file when you're changing the schema (I usually just create them manually though). It's a pretty standard workflow for rails developers, and given that rails is used by a lot of companies that are not "simple CRUD apps" (like AirBnB, Stripe, etc.) I'd say it's definitely very useful in many scenarios. In fact, I'd argue that the "code-first" approach as you call it is actually more useful, because rails gives you bindings for before/after commit hooks, validators that aren't supported by SQL, etc. > Also, unless you are a following a "code-first" approach and doing all your schema migrations through the ORM I've literally never heard of a rails team migrating their DBs manually. Everyone uses ActiveRecord because it's a joy to use and very well supported and documented.