Unless you already know SQL, and you realise how small and simple the queries could be, then it's really not a great experience to be forced to use graphql.
I've been in web dev for 20 years but mostly in the front end space.
A couple of years ago I started doing full stack and trying different databases. For the past year or so I've been using Postgres and learning SQL. This is by far the best solution I've used so far. SQL is extremely expressive, powerful, and elegant.
The problem is that SQL has a strong learning curve which many devs want to avoid. I'm convinced this is the main reason stuff like Mongo or Prisma are so popular. I actually tried Prisma before raw SQL and I vastly prefer SQL for writing queries.
I deeply regret not having spent some time learning SQL years ago.
If I have a User and who is trying to create a new Post, with Prisma you eventually set it up to do something like User.createPost(content).
What does createPost method look like with raw SQL? Does it read in from a .sql file that you pass values to?
The problem with using the ORM as you describe is that when you hit any sort of scale, you need to be doing bulk operations, otherwise your latency goes through the roof, to the point that the number of inefficient queries you are doing can tank the database. I speak from the experience of having seen a database collapse under the load of a backend written in this fashion having request load grow past a certain point — not pretty! The interim solution is to bulkify existing queries and functions in place to the greatest extent possible, while preparing for:
Converting a codebase from having endpoints doing individual ORM operations as described to having proper separation of concerns with a business logic layer between the endpoints and the database is a _massive_ cost. The earlier you implement that, the happier you will be in the longer term. It doesn’t have to be with raw SQL, but many bulk operations are much easier to express with SQL than with the ORM.
{ title: “Cool New Technology”, article: “I learned a thing today”, user_id: 1234 }
So createPost would just generate the appropriate query with the necessary parameters, and execute it.
However, a slightly unrelated comment on your choice of API design: this approach always introduces an asymmetry in the model that restricts what you can do. If you start allowing post imports that auto-detect authors, you now need a Post.create(content) and Post.setUser(user) too. And then your API users start wondering what's the idiomatic way to create a new post.
The problem is that you are making an early assumption that all posts will belong to a user, yet representing that in an SQL database with database relations, one being independent of the other (User: id, name, email...), and another referencing the first (Post: id, date, user -> User, content...). Your database model allows easy transition to allowing nulls for `user`, yet your API doesn't.
Moving to a more functional API makes this much more natural and less restrictive. Shallow DAOs for User and Post and a function create_post(content, user) may look just like a namespacing difference, but they match your database design more closely. If you want to allow nulls for user in the database, you just do the same in the create_post function.
You can wrap related functions into modules (or classes) — in the domain driven design, most of these would be port/adapter functions, but if your DAO classes are sufficiently shallow, they could be service or domain functions too, etc — they are still ultimately functions (no shared state or side effects).
Standard SQL is a really simple grammar and a very small keyword set - there's basically selecting, updating, deleting, filtering with where, aggregate queries, grouping and joins, and that's like 95% of it. Sub-queries maybe too.
I think the problem is that it's declarative instead of imperative, which is really kind of a shock if you are not used to it (you can't go step by step, there's no debugger, there are no branches etc), and also that you have to think in sets in terms of your solution, which is also awkward when you're not used to it.
I think it's definitely worth it though, as nothing we have beats the relational model for CRUD, and there are so many great learning tools online, for example: https://sqlbolt.com/
Depends. It's easy if all you want to do is select * from whatever;
When you get into subqueries and a whole ton of joins to get the information you need, it can get pretty complicated.
I mean, we have a full university course which was 80% SQL, spanning 6 months.
TL;DR the language is not complicated. Actually using it, can be.
Really? Of the dozens of languages that I've learned, SQL has been the easiest.
It really feels like it was designed for non-programmers.
Some of these details go all the way down to the WiredTiger storage engine, but others are more vanilla (e.g. indexing strategies, atomicity guarantees, causal consistency, etc).
I personally abandoned SQL about a decade ago, but I can appreciate how clean the interface semantics are for even non-technical folks. There are certainly platform-specific implementation details that can matter, especially when you get into the world of partitioning. But largely for most service loads, you're writing queries that satisfy the known index constraints that you imposed on yourself rather than constraints resultant from implementation details.
(I totally realize that even with SQL, that last statement completely changes at a certain scale threshold.)
So you're in the camp that nosql data stores like dynamo/mongo is a good replacement for most SQL workflows? Can you expand on this a bit if you have the time?
When iterating on new systems, especially one with live users. I can keep users at different document schemas. If I'm careful I can make it so that all document schema changes don't break old ones yet also allow for new functionality not requiring mass migrations of documents.
CouchDB allows the db to just be exposed to the world directly. Projects where it's reasonable (user owned/controlled data particularly), I can stand up the system with 97% front end code 3% backend. Having the near entirety of your application stack in one place means you can use smaller more specialized teams and your overall areas of concerns are smaller without needing to draw up a formal spec for your data transport.
The whole GraphQL vs REST debate is meaningless when you don't even have to think about your transport stack between the server and the browser. There are other perks to this model such as providing a fully functional website/webapp even while offline. It's trivial to switch between a couchdb backend and local pouchdb copy of your db. Potentially lower bandwidth use while just transferring updated docs instead of consistent queries or asset fetching where the same data moves across the wire over multiple uses (not a win for single visits to a single page style sites). Keeping multiple clients on the same document set in sync without socket.io work.
I didn't jettison SQL because there was some fundamental limitation from a storage or scalability perspective. It was clear SQL wasn't going anywhere and would be a good infrastructure bet moving forward.
But initially what drew me to mongo was the clean interop between it and JS (Node.js is my runtime of choice). The shell is written in JS, you query (filter) using objects, you insert and update with objects. This seems like a small thing but this sort of developer experience over time is impactful. Everything feels very native to JS and it does so without any heavy abstractions like ORM/ODM.
After having used it now for 10+ years though, there's much more that I admire about it. Both from a pure architecture lens, but also from API perspective as well as it continues to get better.
To cherry pick an example— The new time series collection feature is a good example of that. For years, folks were using the bucket pattern [2] as a means to optimize query strategies for time series data by lowering timestamp cardinality. Now in v5.0, they give you a native way to specify the same kind of collection but they handle all the storage implementation details related to bucketing for you. Your API to interface with that collection remains mostly the same as any other collection. This sort of community-driven roadmap inertia is attractive to me as an engineer.
(Somewhat of a stream of consciousness here, but hopefully gives you some context as to why I made the switch so long ago)
[1] https://martinfowler.com/bliki/PolyglotPersistence.html [2] https://www.mongodb.com/blog/post/building-with-patterns-the...
The new generation of devs thinks that frameworks and ORMs will do the magic for them at no cost, but they don't. There is no substitute for leveraging your storage engine to the max.
The sad part is that databases have evolved and became much better in the last 20 years (I started with MySQL 3.x), but we just don't use them. Everyone acts like "microservices" solved all of our technical challenges. Right.
I work with "data" systems, where everything has been migrating in the other direction - to SQL - from custom code for last 5 years or more.
Yes, it's a bit like buying a set of silverware and insisting on using the handle as the business end incase you decide to switch your tool.
The point of GraphQL is mostly separation of backend/frontend and avoiding over-fetching/under-fetching. If those don't sound like a benefit, you should use REST.
Basically, with GraphQL you hide all the complexity behind generic-seeming API requiring one API call, whereas with REST you'd usually hit multiple endpoints for different data types.
GraphQL has the benefit of allowing the backend to smartly decide how to restrict the data (eg. do the joins between what would have been two bigger REST queries), but that incurs a development cost. The complexity is in marrying all the familiar optimization tricks for SQL databases with exposing that in a generic but still restricted way.
> On the other hand, when you are the one to implement the Graphql server, it feels like writing your own database. You have to create a query plan, handle optimizations, corner cases, etc.
If it’s so easy to craft any GraphQL query as an SQL query and let the RDBMS plan an execute the query, then shouldn’t it be easy to implement the GraphQL server on the backend?
This is true (as long as you expect both queries to be simple, or allow both to be complex).
But the conclusion you get up there is wrong and (obviously) does not follow from that. Creating a software that translate any one query into the other is a very difficult task.
Making it generic and performant at the same time is where the complexity is.
It would be akin to saying how, since knowing that you might need an index in the SQL database is simple, a RDBMS could decide to create those indexes for you.