7 ms·

“ Application developers should care about data – not SQL” Anyone who has been bitten by an ORM generating inefficient SQL (and subsequent having to learn and

“ Application developers should care about data – not SQL” Anyone who has been bitten by an ORM generating inefficient SQL (and subsequent having to learn and care about SQL) knows the above not to be true.

whatl3y 6y ago
While some people are hard lined enough on their anti-ORM stance that it sort of becomes weird, I agree with you that my beef with ORMs comes from being burned before a couple of times by a super inefficient aggregation ActiveRecord did on GROUP BY queries that it 1. not only took a really long time to figure out why a particular page in our app was loading slow but 2. we ended up having to write raw SQL to fix it. I think the answer of whether to use one depends on the type and load/volume of app you're working with combined with the dynamics, size, and skill level of your team(s). I'm extremely comfortable writing, profiling, query planning, and debugging SQL queries. Others aren't, and therefore having an ORM to query data in the DB with the syntax of the language you're using in your projects makes way more sense, if nothing other in order to speed your team up.
- cknoxrun 6y ago
  Not sure when you used ActiveRecord, but slow query logging helps a lot to identify these issues. I also appreciate that in the docs for ActiveRecord they make it very clear that jumping to raw SQL is absolutely fine and normal, and they make the interface for doing that very nice.
nikolasburk 6y ago
This statement is certainly provocative (great that it was the first thing picked up here :D) but I'm happy to explain our rationale for this a bit more. SQL is an impressive technology and has stood the test of time! Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations. SQL is complex, it's easy to shoot yourself in the foot with and its data model (relational data / tables) is far away from the one application developers have (nested data / objects) when working in JS/TS. Mapping relational data to objects incurs a mental as well as a practical cost! This is why we believe that in the majority of cases (which for most apps are fairly straightforward CRUD operations) developers shouldn't pay that cost. They should have an API that feels natural and makes them productive. That being said, for the 5% of queries that need certain optimizations, Prisma allows you to drop down to raw SQL and make sure your desired SQL statements are sent to the DB. I see Prisma somewhat analogous to GraphQL on the frontend, where a similar claim could be: "Frontend developers should care about data, not REST endpoints". GraphQL liberates frontend developers from thinking about where to get their data from and how to assemble it into the structures they need. Prisma does the same by giving application developers a familiar and intuitive API.
- nicoburns 6y ago
  > SQL is an impressive technology and has stood the test of time! Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations. I'm not sure about that. We've recently switched from JavaScript based querying code to mostly raw SQL, and we've reduced our code to about 25% of what it was, and it's much simpler to understand than it was before. > I see Prisma somewhat analogous to GraphQL on the frontend, where a similar claim could be: "Frontend developers should care about data, not REST endpoints". I'm not sure about Prisma, but IMO that GraphQL model isn't great. Realistically (for performance, etc) it will make a difference where that data came from. Not for super simple queries, but super-simple queries are super simple to do with REST/SQL anyway. I also feel like this distinction between front-end and back-end developers isn't great. The GraphQL approach also leads to deployment issue with non-web clients. e.g. it can take a day to get an iOS build released, and there is no way to force users of older clients to update, so this can take months. So fixing a bad query is difficult. With a proper backend you can just swap out the query.
  - tored 6y ago
    I think the strongest argument for or against an ORM is that it changes the structure of your code, how you reason about your data. Things like performance, ease of use, reusability, schema generation etc are minor points and not the big issue you need to think about. Personally I feel that an ORM will lead your project in the wrong direction and your database schema will suffer. ORMs will usually mix both reading and writing within the same class, but those are not the same thing. Readers will have different views of your model depending on who they are or what time it is. Writing on the other hand must fulfill your constraints. Other common mistakes of ORMs is mapping one class with one table, but your data consist of series of relationships, that can’t be explained with a class. An ORM creates an illusion that your data is actually the class entities in your repository. This can have the effect of creating constraints in your own mental model of your data, thus making it harder to evolve your schema because you are to fixed on how your classes are designed.
    - 6y ago
jcpst 6y ago
I agree, and find the reluctance to care about SQL interesting. Sure, SQL is not perfect, and has it’s flaws. But instead of learning an established, declarative language designed for data access, there are developers that would much rather learn new tools and libraries to avoid it. If you need to use another programming language for data access, you’re off looking for another ORM. Or the next, better ORM of the year comes out. There are stored procedures at my work that are more than 20 years old. They don’t need to change when the application code calling it is uplifted. It’s not that much different than a modern React dev not wanting to understand how a plain HTML form works.
- htkien 6y ago
  Yeah, ORMs were marketed as "if your database changes, you don't have to change anything!". What about if your programming language changes? You have to learn an entirely new ORM each time. What about all of the people who have to read your code? Which ORM's do they know? Everyone knows SQL.
  - joshmanders 6y ago
    > What about if your programming language changes? Very rarely does an organization completely rewrite their products in completely different languages. And if they do, I'd hope they take the due diligence to investigate the tools they plan to switch to and make sure they are a good fit. > You have to learn an entirely new ORM each time. You have to learn an entirely new everything each time. ORM's aren't anything different. New language = new paradigm, new routing, new templating, new everything. > What about all of the people who have to read your code? What about them? > Which ORM's do they know? I'd assume that the team would have conversations to gather information on what ORM's everyone knows and decide which is best for their team. I don't see how this is an issue. Do you just randomly pull people into your company and just like, let them wander aimlessly? > Everyone knows SQL. No they don't. And if they did, a lot of stuff isn't huge massive complicated apps, most are basic CRUD apps where ORM's do all the lifting for 98% of the stuff needed done. A lot of us haven't touched SQL in so long that we couldn't construct a simple SELECT statement if we tried. I know because I wrote SQL from 2000-2014, but then tried to write a simple SELECT statement in a `pg` CLI to fetch some stuff and completely forgot how to do it.
ragnese 6y ago
The statement is true, IMO. The problem is that ORMs don't ever solve the problem (and I don't believe they CAN). We should only have to care about data and not SQL. But the reality is that we very much do have to care about SQL.
- Frost1x 6y ago
  I agree. Clean and efficient abstractions (oxymoron?) to handle data management just doesn't exist. ORMs are great until they're not, at which point you do need to understand SQL and idiosyncrasies of the underlying relational database. I tend to prefer avoiding ORMs as a preference because of this but unfortunately, development expectations in the current culture don't pad time enough to do this, so you're often reliant on an ORM and wait until things break before someone paying the bills will be swayed to allow developers to go in and clean things up. The "wait until it breaks to go fix and redesign everything" mentality isn't unique to ORMs though, it plagues any abstraction, framework, library, whatever that gets enough done to supply something mostly functional in a shorter time scale.
einrealist 6y ago
I agree. That statement is so wrong. All ORMs suffer from leaky abstraction. And good ORMs would never hide that fact, nor would they try to deny native access to the database and force a all-or-nothing principle onto the developer. If "application developers" care about data that is stored in a relational database, they have to care about the database itself and the access patterns (SQL). ORMs _only_ provide convenience! They cannot substitute knowledge about the underlying technologies. And they cannot hide complexity, but shift it to the ORM.
havernator 6y ago
Hard disagree. If you’re not leveraging the features of your SQL database to make your life easier, your queries faster, and keep your data consistent & valid, then you probably didn’t need SQL to begin with. Which, yes, is a common state for many applications to be in. Wresting with SQL while using it very poorly in an effort to act like they’re not. [edit] disagree with the quotes statement, that is, agree with the post. I worded that poorly. [edit edit] I've been thinking about what you get from using an RDBMS while avoiding writing or knowing much about SQL or using any of the probably-extremely-nice features of your particular SQL DB (why is everyone always so eager to be DB-agnostic? If you do it right your DB will survive, and greatly ease, several rewrites of your application!) and I'm coming up with: 1) basic locking (I assume you don't want to know how to actually use transactions, beyond what your ORM does automatically, so you're just getting the basics), and 2) some probably-badly-insufficient indices, and 3) an ORM should at least get you a little normalization if you just follow patterns from its docs & examples, I suppose, though you're gonna need to understand some SQL to get much benefit out of it in your DB design and in your use of the DB, so... An entire RDBMS seems like serious overkill if that's all you're really using.
dariosalvi78 6y ago
if you define yourself as a developer do yourself a favour and learn SQL. It's probably much easier than whatever programming language you use and will avoid you many headaches.
lostcolony 6y ago
I have yet to find the person who, being comfortable with both raw SQL and a particular ORM, to, when faced with the choice of learning a new ORM or using raw SQL, reach for the new ORM. To be honest, I haven't met in person anyone who reached for the familiar ORM, either, for anything complex in a team environment, but that's a more aggressive statement. I mention this because to my mind it reinforces my belief that learning an ORM (which I've done in the past, and then never used again) isn't worth the effort; learning SQL is, if for no other reason than it's portable (and you'll have to learn it anyway when the ORM's abstraction leaks).
dj_mc_merlin 6y ago
Not just this - I'm currently working on an application built by people with no understanding of databases beyond "they're kind of like key value stores". No attention is given to transactions, stale updates, locking mechanisms or anything more complicated than the equivalent of SELECT and UPDATE. It is an HTTP service running on many threads, so the problem is only amplified. If you're going to use a SQL database, you need to understand what it is and what rules to obey to get its benefits. Hiding that complexity away makes people think they're writing a fast, concurrent high-availability (TM) service while it's actually a ball of mud that sometimes gets into the right shape.
void_mint 6y ago
The answers is both. Devs cannot work on a system (involving "data") without knowing about that data/how it works/how it is structured and how to store and retrieve it. Pretending you can do one without the other leads to a boatload of issues in the medium and long term.

whatl3y 6y ago

While some people are hard lined enough on their anti-ORM stance that it sort of becomes weird, I agree with you that my beef with ORMs comes from being burned before a couple of times by a super inefficient aggregation ActiveRecord did on GROUP BY queries that it 1. not only took a really long time to figure out why a particular page in our app was loading slow but 2. we ended up having to write raw SQL to fix it. I think the answer of whether to use one depends on the type and load/volume of app you're working with combined with the dynamics, size, and skill level of your team(s). I'm extremely comfortable writing, profiling, query planning, and debugging SQL queries. Others aren't, and therefore having an ORM to query data in the DB with the syntax of the language you're using in your projects makes way more sense, if nothing other in order to speed your team up.

cknoxrun 6y ago
Not sure when you used ActiveRecord, but slow query logging helps a lot to identify these issues. I also appreciate that in the docs for ActiveRecord they make it very clear that jumping to raw SQL is absolutely fine and normal, and they make the interface for doing that very nice.

nikolasburk 6y ago

This statement is certainly provocative (great that it was the first thing picked up here :D) but I'm happy to explain our rationale for this a bit more. SQL is an impressive technology and has stood the test of time! Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations. SQL is complex, it's easy to shoot yourself in the foot with and its data model (relational data / tables) is far away from the one application developers have (nested data / objects) when working in JS/TS. Mapping relational data to objects incurs a mental as well as a practical cost! This is why we believe that in the majority of cases (which for most apps are fairly straightforward CRUD operations) developers shouldn't pay that cost. They should have an API that feels natural and makes them productive. That being said, for the 5% of queries that need certain optimizations, Prisma allows you to drop down to raw SQL and make sure your desired SQL statements are sent to the DB. I see Prisma somewhat analogous to GraphQL on the frontend, where a similar claim could be: "Frontend developers should care about data, not REST endpoints". GraphQL liberates frontend developers from thinking about where to get their data from and how to assemble it into the structures they need. Prisma does the same by giving application developers a familiar and intuitive API.

nicoburns 6y ago
> SQL is an impressive technology and has stood the test of time! Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations. I'm not sure about that. We've recently switched from JavaScript based querying code to mostly raw SQL, and we've reduced our code to about 25% of what it was, and it's much simpler to understand than it was before. > I see Prisma somewhat analogous to GraphQL on the frontend, where a similar claim could be: "Frontend developers should care about data, not REST endpoints". I'm not sure about Prisma, but IMO that GraphQL model isn't great. Realistically (for performance, etc) it will make a difference where that data came from. Not for super simple queries, but super-simple queries are super simple to do with REST/SQL anyway. I also feel like this distinction between front-end and back-end developers isn't great. The GraphQL approach also leads to deployment issue with non-web clients. e.g. it can take a day to get an iOS build released, and there is no way to force users of older clients to update, so this can take months. So fixing a bad query is difficult. With a proper backend you can just swap out the query.
- tored 6y ago
  I think the strongest argument for or against an ORM is that it changes the structure of your code, how you reason about your data. Things like performance, ease of use, reusability, schema generation etc are minor points and not the big issue you need to think about. Personally I feel that an ORM will lead your project in the wrong direction and your database schema will suffer. ORMs will usually mix both reading and writing within the same class, but those are not the same thing. Readers will have different views of your model depending on who they are or what time it is. Writing on the other hand must fulfill your constraints. Other common mistakes of ORMs is mapping one class with one table, but your data consist of series of relationships, that can’t be explained with a class. An ORM creates an illusion that your data is actually the class entities in your repository. This can have the effect of creating constraints in your own mental model of your data, thus making it harder to evolve your schema because you are to fixed on how your classes are designed.
  - 6y ago

jcpst 6y ago

I agree, and find the reluctance to care about SQL interesting. Sure, SQL is not perfect, and has it’s flaws. But instead of learning an established, declarative language designed for data access, there are developers that would much rather learn new tools and libraries to avoid it. If you need to use another programming language for data access, you’re off looking for another ORM. Or the next, better ORM of the year comes out. There are stored procedures at my work that are more than 20 years old. They don’t need to change when the application code calling it is uplifted. It’s not that much different than a modern React dev not wanting to understand how a plain HTML form works.

htkien 6y ago
Yeah, ORMs were marketed as "if your database changes, you don't have to change anything!". What about if your programming language changes? You have to learn an entirely new ORM each time. What about all of the people who have to read your code? Which ORM's do they know? Everyone knows SQL.
- joshmanders 6y ago
  > What about if your programming language changes? Very rarely does an organization completely rewrite their products in completely different languages. And if they do, I'd hope they take the due diligence to investigate the tools they plan to switch to and make sure they are a good fit. > You have to learn an entirely new ORM each time. You have to learn an entirely new everything each time. ORM's aren't anything different. New language = new paradigm, new routing, new templating, new everything. > What about all of the people who have to read your code? What about them? > Which ORM's do they know? I'd assume that the team would have conversations to gather information on what ORM's everyone knows and decide which is best for their team. I don't see how this is an issue. Do you just randomly pull people into your company and just like, let them wander aimlessly? > Everyone knows SQL. No they don't. And if they did, a lot of stuff isn't huge massive complicated apps, most are basic CRUD apps where ORM's do all the lifting for 98% of the stuff needed done. A lot of us haven't touched SQL in so long that we couldn't construct a simple SELECT statement if we tried. I know because I wrote SQL from 2000-2014, but then tried to write a simple SELECT statement in a `pg` CLI to fetch some stuff and completely forgot how to do it.

ragnese 6y ago

The statement is true, IMO. The problem is that ORMs don't ever solve the problem (and I don't believe they CAN). We should only have to care about data and not SQL. But the reality is that we very much do have to care about SQL.

Frost1x 6y ago
I agree. Clean and efficient abstractions (oxymoron?) to handle data management just doesn't exist. ORMs are great until they're not, at which point you do need to understand SQL and idiosyncrasies of the underlying relational database. I tend to prefer avoiding ORMs as a preference because of this but unfortunately, development expectations in the current culture don't pad time enough to do this, so you're often reliant on an ORM and wait until things break before someone paying the bills will be swayed to allow developers to go in and clean things up. The "wait until it breaks to go fix and redesign everything" mentality isn't unique to ORMs though, it plagues any abstraction, framework, library, whatever that gets enough done to supply something mostly functional in a shorter time scale.

einrealist 6y ago

I agree. That statement is so wrong. All ORMs suffer from leaky abstraction. And good ORMs would never hide that fact, nor would they try to deny native access to the database and force a all-or-nothing principle onto the developer. If "application developers" care about data that is stored in a relational database, they have to care about the database itself and the access patterns (SQL). ORMs _only_ provide convenience! They cannot substitute knowledge about the underlying technologies. And they cannot hide complexity, but shift it to the ORM.

havernator 6y ago

Hard disagree. If you’re not leveraging the features of your SQL database to make your life easier, your queries faster, and keep your data consistent & valid, then you probably didn’t need SQL to begin with. Which, yes, is a common state for many applications to be in. Wresting with SQL while using it very poorly in an effort to act like they’re not. [edit] disagree with the quotes statement, that is, agree with the post. I worded that poorly. [edit edit] I've been thinking about what you get from using an RDBMS while avoiding writing or knowing much about SQL or using any of the probably-extremely-nice features of your particular SQL DB (why is everyone always so eager to be DB-agnostic? If you do it right your DB will survive, and greatly ease, several rewrites of your application!) and I'm coming up with: 1) basic locking (I assume you don't want to know how to actually use transactions, beyond what your ORM does automatically, so you're just getting the basics), and 2) some probably-badly-insufficient indices, and 3) an ORM should at least get you a little normalization if you just follow patterns from its docs & examples, I suppose, though you're gonna need to understand some SQL to get much benefit out of it in your DB design and in your use of the DB, so... An entire RDBMS seems like serious overkill if that's all you're really using.

dariosalvi78 6y ago

if you define yourself as a developer do yourself a favour and learn SQL. It's probably much easier than whatever programming language you use and will avoid you many headaches.

lostcolony 6y ago

I have yet to find the person who, being comfortable with both raw SQL and a particular ORM, to, when faced with the choice of learning a new ORM or using raw SQL, reach for the new ORM. To be honest, I haven't met in person anyone who reached for the familiar ORM, either, for anything complex in a team environment, but that's a more aggressive statement. I mention this because to my mind it reinforces my belief that learning an ORM (which I've done in the past, and then never used again) isn't worth the effort; learning SQL is, if for no other reason than it's portable (and you'll have to learn it anyway when the ORM's abstraction leaks).

dj_mc_merlin 6y ago

Not just this - I'm currently working on an application built by people with no understanding of databases beyond "they're kind of like key value stores". No attention is given to transactions, stale updates, locking mechanisms or anything more complicated than the equivalent of SELECT and UPDATE. It is an HTTP service running on many threads, so the problem is only amplified. If you're going to use a SQL database, you need to understand what it is and what rules to obey to get its benefits. Hiding that complexity away makes people think they're writing a fast, concurrent high-availability (TM) service while it's actually a ball of mud that sometimes gets into the right shape.

void_mint 6y ago

The answers is both. Devs cannot work on a system (involving "data") without knowing about that data/how it works/how it is structured and how to store and retrieve it. Pretending you can do one without the other leads to a boatload of issues in the medium and long term.