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.
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.
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.
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.
But for anything more complex than that (e.g. SELECT queries joining several tables) we're using knex.raw so the query itself is raw SQL where we're very much thinking in the relational model rather than in terms of objects.
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.
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
I do wish someone would create a compile-to-sql language that adds variables and basic conditional support. I think T-SQL (SQL Server) has a lot of this, but it's missing in the Open Source world.
Every database with a procedural language based on SQL (Oracle PL/SQL, Postgres PL/pgSQL, etc.) has it, T-SQL just doesn’t segregate procedural and declarative code the way most engines do. Mostly, I think this is a negative for T-SQL, but sometimes segregation means there’s no way to do one-off procedural scripts without making and then calling a procedure; but Postgres, for example, since 9.0 has the DO statement for this use case (which supports any procedural language installed in the DB, but defaults to PL/pgSQL.)
I can write a query that queries the result of another query, but I can't store a query and then pass that somewhere to query from.
Check the example at the beginning of https://www.postgresql.org/docs/13/queries-with.html
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.
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) 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.
SQL could benefit from being language-integrated, yes. Very few languages can model relational algebra types though, if thats a thing you care about (it certainly is for me).
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.
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.
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.
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. :)
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.
I'd love to get rid of all these client-side state management libraries and just drop an SQL DB in there and allow components to subscribe to changes to queries on record updates, and then sync it to a cloud DB.
One interesting problem I came across is how to know when your local DB has enough records to be able to run a given query. E.g. If your team has 100K tasks in their cloud DB, and you run `SELECT * FROM tasks WHERE tasks.assignee = user.id`, and then another query of `SELECT * FROM tasks WHERE tasks.assignee = user.id AND tasks.completed = true`, the second task is a subset of the first, so it doesn't need to hit the server except to check if any updates were made to the tasks table. Then apply this to all queries including ones with joins. Actually some fun relational algebra can be used figure it out - one of the perks of working with SQL. The problem is actually similar to how to [incrementally maintain a materialized view](https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...) which Postgres actually does not support (Oracle does).
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.