GraphQLize: JVM library to build GraphQL API instantly from PostgreSQL and MySQL
graphqlize.org
graphqlize.org
That's what I find the most attractive about GraphQL. Large companies and companies that have grown fast often have all kinds of APIs made at different time by different people with different standards. GraphQL lets you paper over that without breaking existing, revenue making code.
GraphQL is a fantastic tool to enable an API that maps extremely closely to the front end (check the spec, that is exactly what it was intended to do); instead building it so it exactly matches your DB schema is a huge mistake IMO.
Modelling databases this way has fallen out of favour for various reasons, but it becomes extremely useful and relevant again with systems like Hasura.
I rarely see people using views at all, let alone using them as a test aid (composing big queries from smaller more easily tested views), or using them to define interfaces to an internal data model.
See: INSTEAD OF triggers.
That is unless you're using MySQL/MariaDB, in which case views are the least of your worries.
> Product‐centric: GraphQL is unapologetically driven by the requirements of views and the front‐end engineers that write them. GraphQL starts with their way of thinking and requirements and builds the language and runtime necessary to enable that.
The whole point of GraphQL is that since each client asks for exactly the fields they want, having those "special case functions" is not a problem because other clients don't need to ask for those fields.
Doing the former leads to basically reimplementing SQL in GraphQL. Doing the latter leads to functions like `resolve_month_over_month_profit_and_loss_segemented_by_sector`.
Like every tool, there's a sweet spot where it really shines and others where it doesn't fit. If you need to have access to a bunch of disparate datasources it's really nice to have that layer of abstraction.
https://graphql.org/learn/authorization/
> Delegate authorization logic to the business logic layer
If you take security or performance debugging seriously, you should never expose database models through APIs directly in a production app.
To illustrate, say you have an Employee model:
query {
employee(userId=uuid) {
name
salary
}
}
The next day, someone creates a Manager object, a relation from employee to Manager, and Manager to employee.
Now, without having consider security for a second, you've granted all employees the ability to query each other:
query {
employee(userId=uuid) {
name
salary
manager {
employees {
name
salary
}
}
}
}
Please, please stop encouraging people to directly expose their databases through an API.
For example, with postgres: https://stackoverflow.com/questions/49261452/combining-row-l...
Basic database table replication will suffice for the former. No replication required for the latter.
I think a better piece of advice is: Please stop allowing people to query relational models automatically, and surface a separate locked-down schema for the GraphQL user.
These GraphQL over DB tools have real value. I've moved from using OpenResty to Hasura in order to surface Postgres APIs, and the time saved has been significant.
GraphQL is no more vulnerable to crawlers than any REST server with OpenAPI on it. And yes, introspection can be disabled.
It's not like tools like Hasura, Prisma, or Postgraphile have no security baked into their products, often via a cryptographically signed JWT.
Query cost analysis. Query depth limits. Query pattern allow lists.
And that's all assuming the GraphQL server has a public IP, which is far from a certainty (just like REST).
Exposing databases through APIs is not the problem. Exposing relations without authorization is.
Under these frameworks, you can add an innocuous relationship between two models that entirely compromises security without even touching the API code. Not only that, but the graph of relations and their associated ACLs is complex. Every time you add a relation, you need to create a graph of your data model and ensure that it's safe. There are far, far more surface area to make a critical error, allowing attackers to exfiltrate large volumes of your data.
I've written these bugs in similar modeled systems (there were GQL like systems before GQL). I've fixed these bugs. I've caught these bugs in code review.
These bugs are orders of magnitude less likely to happen with a simpler authz model where you don't need to lock down every relation, just the table itself. This is why the GraphQL creators themselves encourage users to put authz at the business layer.
2 out of three examples above literally have no framework authz support. Postgraphile requires setting up row level security policies, meaning you have no control over what layer of code you want authz policies to live; they must be in the database. Even if you are ok with that sacrifice, you still have to find ways to manage this in version control and test, for which there is scarce tooling.
Hasura seems to do the right thing here, provided you opt into it. It's not clear if it allows you to easily version control or test your ACLs.
> And that's all assuming the GraphQL server has a public IP, which is far from a certainty (just like REST).
Security doesn't stop at your VPN. At several hundred engineers, organizations begin implementing internal controls.
In fact, my example was an employee comp manager :|.
I might go ahead and argue that RLS in the database is exactly where security on the data model should go. Past a certain size, many databases have more than one app talking to it. This means N servers that must adhere to the same authorization rules. Hopefully at least they're written in the same language.
Not all restrictions are data-oriented though, and on that note, I agree that the restrictions should live above the database using some schema-stitching or federation.
Version control is a matter of saving the schema changes through migrations and optionally DDL audits through event triggers. I would argue that testing/tooling as you seem to be imagining it is a moot point though. I find it much easier to define table, column, and row restrictions at the DB level and let those constraints bubble up. If a table is unavailable to a set of roles/groups, better to say so in the database so that no query at any higher level could accidentally allow access when it shouldn't have. Row-level security policies don't care if someone is executing a simple `SELECT * FROM example` or a 100-line monster. That row in isolation or as part of a gargantuan query reading/writing through a view or three doesn't matter. The reason Postgraphile relies on PostgreSQL itself for authorization is precisely because PostgreSQL does such a good job of it.
You are correct that simple GUI tools are lacking in this regard, but I'm not sure the use cases I've come across for it fit a GUI tool. For a data platform in a large aerospace company the method of allowing/restricting access was dependent on tagging (both AND and OR). Combine it with some temporal query support, and the RLS policies were essentially code, not just declarative. Then the GUI was the web manager UI (custom). Worked quite well. And while that data platform had some bugs we had to work out as all software does, we never had a problem with the data-level security. We even found some bugs at the app and UI layers precisely because some developers overlooked some corner cases.
App-level integration tests appeared more than sufficient to flush out any problems (or verify correct behavior) in the data-layer security.
Data-level restrictions and query auditing (below the level of an app or server) become even MORE relevant as employee size grows, not less.
GraphQL is hierarchical. The queries look substantially like the output JSON. SPARQL (and SQL) require training. With a common tool like GraphiQL or Playground, queries are simple enough and abstracted enough (am I querying Redis, PostgreSQL, S3, or some combination?) that clients don't have to know much of anything except what data they want. That's huge.
It puts the onus on server developers to audit their data and security models, but they should be doing that anyway no matter what the stack looks like.
REST suffers from the N+1 problem and overfetching. Most ORMs do as well. "Oh! You want order info AND product reviews? (At least) 2 more REST calls for you. Oh! You don't need the person's shipping and billing addresses in this case? So sorry, take them anyway, otherwise we would have to make another REST endpoint just for you."
Also affects versioning. In REST you commonly see URLS like "/v1/foo/54321" because if you try to remove or rename ANY PART of the JSON payload coming back, you break a contract. With GraphQL, you make the new name available and mark the old name with a directive like @deprecated. Over time you watch the logs to see if any clients are still using the old name. If so, keep it around and write another blog post asking folks not to use it anymore. If not, just remove it. You're done. No broken contracts. No differently versioned URLs.
GraphQL handles N different clients with a single schema. Mobile app clients, desktop clients, B2B clients, et al get exactly the data they need—no more and no less. REST either shoehorns them all into one or two access models or explodes into an unmaintainable mess of hundreds or thousands of often redundant endpoints.
The biggest difference, the one that GraphQL was really originally built to solve at Facebook, was to allow the CLIENT to determine what data they needed at any given time rather than REST which may be driven by client requests but ultimately is what servers THINK clients will need, and is much harder to change as needs change (and they will change).
EDIT: It was awesome, and I kind of miss it, but it was too much to maintain within our constraints.
I guess it is inevitable that somehow some sql database will be exposed via graphql, and someone will use something like presto to make it appear to be an SQL database again...
So the fact that it lets you do this with any JVM language is actually a key property that gives it very wide application.
It seems to have been written in Clojure.
You also absolutely take the time to think through what should and should not be exposed to end users. Either you creates a namespace for your API with views that expose only the necessary data, or with something like Postgraphile you can specify which functionality, columns, and tables are hidden. Personally I prefer the view approach as it decouples your API from the database implementation.
RLS is difficult to unit test, to configure with flags, not portable to secondary stores, and requires knowledge of arcane database features.
When you put some private data on a cache somewhere and query it, now you need to write your access layer twice since the other one is so tightly coupled to the database.
As an example, consider a query that is fairly complex. How exactly are you going to make resuable security tests for it? How do you know that an error in the query won't expose unexpected data, or that a modification to the query that introduces and error exposes unexpected data? Your security now has to be customized to every query like this.
The alternative case is that you only use super simple queries so that you can write resuable security checks. But now I'm going to ask why you are using a relational database in the first place? Because almost certainly you're doing manual joins in this scenario and incurring huge performance hits.
If you are using a relational database to its full extent, there is nothing as secure as built in row level security. You wouldn't dream of doing integrity checks outside the database, why do security?
This isn’t exactly PostgreSQL’s fault, but mapping application users to database roles is not as easy in pretty much any framework I’ve worked compared to implementing basic post-retrieval filters.
I absolutely think bringing security into the database is good, but it’s also not straightforward to integrate in even assuming you know how to define the necessary database elements.
Row-level security is absolutely not dependent upon DB roles. Table-level security on the other hand is sufficiently coarse-grained that mapping GRANT/REVOKE to applied roles should be feasible.
If you're punting all of this security to the app layer, that's your prerogative, but don't pretend that it's somehow more straightforward or secure. If it seems simpler at the app layer, you may very well be missing something.
The parent's point is that tooling around this is weak, which undermines its value. Not being able to review and easily test authz changes will almost certainly lead to issues.