5 ms·

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 i

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 5y 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 5y 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.
  - IggleSniggle 5y ago
    Great comment! I’ve been thinking about a tangentially related subject: TypeScript is like a relational database (and other structural statically typed languages) of your application behavior, allowing you to query the current possible states of your data at any given point in your application code. I’ve been thinking about this more since TypeScript got template-string generics, and folks have experimented with eg type-checking SQL strings back into application code. This is the piece that draws me to an ORM: that it brings the “relational model” into application code, NOT the “object” part of an ORM. Does anyone have any opinions about which language ecosystems create the most effective type-mappings between a RDBMS and application language? Part of me also wonders if there’s a ton of time lost on mapping between languages like this when the tooling that’s really missing (afaik) is better tooling for producing, consuming, and generally interacting with SQL and db schemas as the data runs through your application pipeline.
- notJim 5y ago
  > We've recently switched from JavaScript based querying code to mostly raw SQL Curious what your use case is, because in every codebase I've worked on, people pretty quickly get tired of writing out SELECT * FROM [table] WHERE id=? and UPDATE [table] SET field=? and looping over database cursors all day, and you end up with a half-done, buggy non-ORM.
abraxas 5y ago
I think SQL gets a lot of undeserved praise that I’m having a difficult time understanding. The only impressive thing about SQL is its prevalence but that’s a pretty poor yardstick unless one thinks that an appeal to popularity is an indicator of quality. Now let me count the ways in which SQL is bad: - it composes poorly due to its unwieldy cobolesque syntax - it is a leaky abstraction revealing a lot of underlying implementation tradeoffs - it doesn’t properly implement Cobb’s relational model - it is poorly standardized with a ton of proprietary extensions and alterations present in virtually every implementation - it is still poorly supported by tools because the model metadata lacks any standard interface to make universal tooling possible A lot of the praise for SQL is just bandwagon hopping and cargo cult behaviour or a lack of vision by most people of how things could be much better
- spion 5y ago
  What are you comparing it to? What is the better alternative?
  - sonthonax 5y ago
    SQL isn't the only RDMS language, but it is the one with 50 years of incumbency. Postgres before it was called 'PostgreSQL', used something called QUEL, which is quite a bit cleaner. There is also Microsoft's LINQ. A typical programming language can express all that SQL can. In a way, an ORM is a cross-compiler from your programming language to SQL.
    - vaughan 5y ago
      > A typical programming language can express all that SQL can. In a way, an ORM is a cross-compiler from your programming language to SQL. SQL has always been a sort a magical, black-box in that you have very limited control over the query planner, and have to hint at it to do the right thing. (I guess `EXPLAIN` allows you to peak in the box a little) I found [Apache Calcite](https://calcite.apache.org/docs/algebra.html https://calcite.apache.org/docs/algebra.html) rather interesting. It provides the primitives that a DBMS is built from. I would be interested to compare how PSQL is built in comparison. I would love a more layered/pluggable database that everyone can build off of. I think FoundationDB had this approach.
loloquwowndueo 5y ago
Oh don’t get me wrong - ORMs have their place and do enable higher agility. They do seem magical the first time you encounter them. What I object to is the blanket “shouldn’t care about sql” statement because that’s what empowers people to use the ORM indiscriminately without understanding what’s under it (an understanding for which SQL is relevant) and then it’s the non-value-adding developers’ job to come in and untangle the mess, usually could have been avoided by dropping down one level, looking at the SQL that was generated (or maybe analyzing the query plan - again kind of hard if you don’t understand SQL) and realizing the ORM is doing something crazy.
- alireza94 5y ago
  Exactly. ORMs, especially powerful and mature ones like DjangoORM or Active Record, can help with productivity and maintainability and for more complex use cases you have the chance to switch to raw SQL; However, it’s important for developers to know what’s happening under the hood and to know what will happen if they use a certain feature of an ORM. I can’t even count the number of times that when one of my coworkers and I tried to fix a performance issue, and after digging deep into queries and mechanics of the ORM, we’ve realized how ORM heed so much complexity from them and, how a certain data structure design and coding in a certain way can result in an inefficient data flow.
lelanthran 5y ago
> Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations. You have two options when marrying RDBMS SQL and OO: either mismanage the relational data so that developers can use a class hierarchy, or stop using a class hierarchy to represent data. IME, applications come and go. They get rewritten, thrown away, obsoleted. The database, however, is there to stay. Mismanaging the data purely so that the application developers don't have to touch all that icky relational stuff almost always results in more work for less returns.
- sorenbs 5y ago
  Prisma does not use a class hierarchy that are mapped directly to tables. Instead we take advantage of structural typing to give you a very smooth developer experience that is faithful to the relational model.
htkien 5y ago
If everything is simple, then so are the SQL queries.
megous 5y ago
Actually nested objects kinda suck even for frontend devs, if you're trying to keep things in sync efficiently. Often times it's much better to be able to lookup some object by ID of the entity from some Map, than consuming endpoints returning some crazy nested partial data for the current view. Depends on how much your app relies on client side caching and incremental sync of data.
- intergalplan 5y ago
  As soon as you need a lot of complex data all displayed in one place plus high performance, you're gonna find you need a list, not a hierarchy. You need to be able to treat the data coming in like a stream, not to traverse anything. Read the row, maybe do some state-machine stuff to decide how to treat it, then drop all that on the floor and move to the next row. This is more-true the less efficient the language is that you're using. I see people screw this up when writing e.g. dashboards that source their data from SQL, while leaning on an ORM, all the time, and it always kills performance (talking tens of seconds to minutes for things that should take a single-digit count of seconds, at worst). What's good at turning complex relational data into a list, and fast? Yep, SQL.
  - megous 5y ago
    That too. Though I used efficiently in a sense of minimizing the communication between client/server by normalizing data and just sending changes. Which is easier if you keep data normalized on the client side too, and look up related objects in a Map when needed, instead of keeping multiple copies of objects representing the same entity everywhere in your client code in some/vasrious denormalized forms. It would actually be very nice for some use cases if I had a SQL interface to local data on the client side too, so that I can query/join them up arbitrarily as needed from what's loaded up to client storage. That's unpleasant to do in the browser with current platform APIs. Basically I want WebSQL back in some form, instead of this IndexedDB thing. :)
- vaughan 5y ago
  > if you're trying to keep things in sync efficiently I think we are all abusing GraphQL in this sense. Caching GraphQL data is a world of hurt. The easiest way to sync is if your data is in the same model as the way it is stored, which is normalized if using SQL. Even when we do normalize most people will represent a 1-M as an array of foreign keys: `posts.comments = [1, 2, 3]` when in the db we use a foreign key on the `comment` entity: `comment.post_id = 1`. This causes such headaches for optimistic UI updates. It's a mess. We should be using GraphQL to retrieve data and then render it directly. Ultimately, I think we should be implementing our GraphQL API client-side against a local SQL DB that acts as a pass-through cache. Then your entire app runs offline and instantly respond to queries eliminating the need for a separate client-side cache, and you don't have to worry about about data model mismatch anymore. > Depends on how much I think all apps want this - unless they are SSRing with <100ms on every action.

nicoburns 5y 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 5y 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.
- IggleSniggle 5y ago
  Great comment! I’ve been thinking about a tangentially related subject: TypeScript is like a relational database (and other structural statically typed languages) of your application behavior, allowing you to query the current possible states of your data at any given point in your application code. I’ve been thinking about this more since TypeScript got template-string generics, and folks have experimented with eg type-checking SQL strings back into application code. This is the piece that draws me to an ORM: that it brings the “relational model” into application code, NOT the “object” part of an ORM. Does anyone have any opinions about which language ecosystems create the most effective type-mappings between a RDBMS and application language? Part of me also wonders if there’s a ton of time lost on mapping between languages like this when the tooling that’s really missing (afaik) is better tooling for producing, consuming, and generally interacting with SQL and db schemas as the data runs through your application pipeline.
notJim 5y ago
> We've recently switched from JavaScript based querying code to mostly raw SQL Curious what your use case is, because in every codebase I've worked on, people pretty quickly get tired of writing out SELECT * FROM [table] WHERE id=? and UPDATE [table] SET field=? and looping over database cursors all day, and you end up with a half-done, buggy non-ORM.

abraxas 5y ago

I think SQL gets a lot of undeserved praise that I’m having a difficult time understanding. The only impressive thing about SQL is its prevalence but that’s a pretty poor yardstick unless one thinks that an appeal to popularity is an indicator of quality. Now let me count the ways in which SQL is bad: - it composes poorly due to its unwieldy cobolesque syntax - it is a leaky abstraction revealing a lot of underlying implementation tradeoffs - it doesn’t properly implement Cobb’s relational model - it is poorly standardized with a ton of proprietary extensions and alterations present in virtually every implementation - it is still poorly supported by tools because the model metadata lacks any standard interface to make universal tooling possible A lot of the praise for SQL is just bandwagon hopping and cargo cult behaviour or a lack of vision by most people of how things could be much better

spion 5y ago
What are you comparing it to? What is the better alternative?
- sonthonax 5y ago
  SQL isn't the only RDMS language, but it is the one with 50 years of incumbency. Postgres before it was called 'PostgreSQL', used something called QUEL, which is quite a bit cleaner. There is also Microsoft's LINQ. A typical programming language can express all that SQL can. In a way, an ORM is a cross-compiler from your programming language to SQL.
  - vaughan 5y ago
    > A typical programming language can express all that SQL can. In a way, an ORM is a cross-compiler from your programming language to SQL. SQL has always been a sort a magical, black-box in that you have very limited control over the query planner, and have to hint at it to do the right thing. (I guess `EXPLAIN` allows you to peak in the box a little) I found [Apache Calcite](https://calcite.apache.org/docs/algebra.html https://calcite.apache.org/docs/algebra.html) rather interesting. It provides the primitives that a DBMS is built from. I would be interested to compare how PSQL is built in comparison. I would love a more layered/pluggable database that everyone can build off of. I think FoundationDB had this approach.

loloquwowndueo 5y ago

Oh don’t get me wrong - ORMs have their place and do enable higher agility. They do seem magical the first time you encounter them. What I object to is the blanket “shouldn’t care about sql” statement because that’s what empowers people to use the ORM indiscriminately without understanding what’s under it (an understanding for which SQL is relevant) and then it’s the non-value-adding developers’ job to come in and untangle the mess, usually could have been avoided by dropping down one level, looking at the SQL that was generated (or maybe analyzing the query plan - again kind of hard if you don’t understand SQL) and realizing the ORM is doing something crazy.

alireza94 5y ago
Exactly. ORMs, especially powerful and mature ones like DjangoORM or Active Record, can help with productivity and maintainability and for more complex use cases you have the chance to switch to raw SQL; However, it’s important for developers to know what’s happening under the hood and to know what will happen if they use a certain feature of an ORM. I can’t even count the number of times that when one of my coworkers and I tried to fix a performance issue, and after digging deep into queries and mechanics of the ORM, we’ve realized how ORM heed so much complexity from them and, how a certain data structure design and coding in a certain way can result in an inefficient data flow.

lelanthran 5y ago

> Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations. You have two options when marrying RDBMS SQL and OO: either mismanage the relational data so that developers can use a class hierarchy, or stop using a class hierarchy to represent data. IME, applications come and go. They get rewritten, thrown away, obsoleted. The database, however, is there to stay. Mismanaging the data purely so that the application developers don't have to touch all that icky relational stuff almost always results in more work for less returns.

sorenbs 5y ago
Prisma does not use a class hierarchy that are mapped directly to tables. Instead we take advantage of structural typing to give you a very smooth developer experience that is faithful to the relational model.

htkien 5y ago

If everything is simple, then so are the SQL queries.

megous 5y ago

Actually nested objects kinda suck even for frontend devs, if you're trying to keep things in sync efficiently. Often times it's much better to be able to lookup some object by ID of the entity from some Map, than consuming endpoints returning some crazy nested partial data for the current view. Depends on how much your app relies on client side caching and incremental sync of data.

intergalplan 5y ago
As soon as you need a lot of complex data all displayed in one place plus high performance, you're gonna find you need a list, not a hierarchy. You need to be able to treat the data coming in like a stream, not to traverse anything. Read the row, maybe do some state-machine stuff to decide how to treat it, then drop all that on the floor and move to the next row. This is more-true the less efficient the language is that you're using. I see people screw this up when writing e.g. dashboards that source their data from SQL, while leaning on an ORM, all the time, and it always kills performance (talking tens of seconds to minutes for things that should take a single-digit count of seconds, at worst). What's good at turning complex relational data into a list, and fast? Yep, SQL.
- megous 5y ago
  That too. Though I used efficiently in a sense of minimizing the communication between client/server by normalizing data and just sending changes. Which is easier if you keep data normalized on the client side too, and look up related objects in a Map when needed, instead of keeping multiple copies of objects representing the same entity everywhere in your client code in some/vasrious denormalized forms. It would actually be very nice for some use cases if I had a SQL interface to local data on the client side too, so that I can query/join them up arbitrarily as needed from what's loaded up to client storage. That's unpleasant to do in the browser with current platform APIs. Basically I want WebSQL back in some form, instead of this IndexedDB thing. :)
vaughan 5y ago
> if you're trying to keep things in sync efficiently I think we are all abusing GraphQL in this sense. Caching GraphQL data is a world of hurt. The easiest way to sync is if your data is in the same model as the way it is stored, which is normalized if using SQL. Even when we do normalize most people will represent a 1-M as an array of foreign keys: `posts.comments = [1, 2, 3]` when in the db we use a foreign key on the `comment` entity: `comment.post_id = 1`. This causes such headaches for optimistic UI updates. It's a mess. We should be using GraphQL to retrieve data and then render it directly. Ultimately, I think we should be implementing our GraphQL API client-side against a local SQL DB that acts as a pass-through cache. Then your entire app runs offline and instantly respond to queries eliminating the need for a separate client-side cache, and you don't have to worry about about data model mismatch anymore. > Depends on how much I think all apps want this - unless they are SSRing with <100ms on every action.