Comment by int_19h
4 years ago
What I really don't get is why we don't just expose SQL directly at this point. Is it just security? Database servers have fairly extensive authentication and authorization models.
4 years ago
What I really don't get is why we don't just expose SQL directly at this point. Is it just security? Database servers have fairly extensive authentication and authorization models.
Authorization & access restrictions. Yes, you can go quite far with table/row/column permissions, but a lot of business logic cannot be modeled using just those (i.e. "user cannot place orders if total outstanding invoice payments surpass value $X").
The combination of DB permissions, DB constraints, and simple (SQL, not procedural language) triggers gets you a lot, including the ability to enforce rules like the one you mention.
Yes, you can enforce a lot through SQL triggers/stored procedures etc. But you often end up abusing your DB/SQL as a business logic layer, where your business logic is encoded in a huge set of row/column permission and custom SQL triggers . This tightly couples your database into your whole business application stack.
Especially in Oracle PL/SQL, I've seen this often abused to an extend where no one ever understood the whole business logic anymore (as logic was spread out in frontend, middle-layer services, and DB mumbojumo), and the database became a fragile core-piece (with a significant vendor login) and hindered all sort of future development.
Seriously, your business logic should be modelled in code, ideally in some sort of service layer (which does not necessarily mean microservices!).
1 reply →
Then why aren't we working on improving the databases to allow for such complex rules, and instead wrap it in another layer (often multiple) to do all this stuff there?
because a database should not hold your business logic. It should hold your data, and that it can do well. See also my other post on parent for more reasoning.
5 replies →
Even if you solve the security issue, a query can easily bring down the server if it has a complex join query.
This could be solved by only exposing stored procedures, but that just moves the code to the database server instead of the REST service with the same problems as before.
You can also use a VIEW.
How does GraphQL make sure to respect table indexes? If not you get a super slow query.
You can still get performance issues with a view if you "select *" on a large amount of data, or join with other views. By exposing the SQL to a web page, you also open up for DDoS attacks more easily, as you can write complex SQL queries
You can get the same problems with GraphQL or stored procedures too of course, if the queries are not optimized correctly
2 replies →
Because a human is writing the resolvers pulling the data from the database. Set whatever index you want to use.
Just give the end user an account to phpMyAdmin. Done. No complex frontend framework required.
You wouldn't belive how often companies use excel with external datasources like this. Excel basically is the common-ui for a lot of people.
And most of the projects I worked on in my professional career as a webdev, were replacing such workflow with a proper web application, because excel does not scale and eventually people fuckup their data.