Why can't clients query the DB directly using SQL?

Question

Given that a lot of modern web applications just consume JSON data from the sever, what is the point in having a REST api at all instead of just using SQL's query language?

Nowadays people are starting to use GraphQL. From what I understand is a query language from the client to the sever, which then gets translated into queries from the sever to the database.

I get that you don't want to let clients execute arbitrary code on your db. But why can't you just do something like execute the queries through a heavily restricted database user, with some rate-limiting and timeouts to avoid spammy requests?

Answer 1

It is possible but not generally wise to offer direct SQL access to a database.

• I have to make the database publicly reachable. This alone would make me nervous, as it increases the attack surface and possibility of data loss.

Normal configurations would keep the DB on an intranet and a webserver in a DMZ. The webserver can then be whitelisted to connect to the DB.

• I have to prevent unauthorized access or changes to data and ensure a consistent data model. No user should be able to corrupt my database. Because different users have access to different records, I would need to create one database user account per end user. I may have to develop a system of database views and stored procedures to make the data model fit this permission model.

  A normal web app checks permission in the web application code on the server, and therefore doesn't have to restrict itself to the database's permission model. This allows permission models on a more suitable granularity, e.g. problem domain concepts instead of database tables. Such a scheme can be much simpler, and therefore more secure.

• I cannot restrict the specific SQL queries that will be submitted. The queries may be written suboptimally, thus hogging performance. Unless the database provides explicit support, I cannot add caching or rate limiting.

  In contrast, you can tune the queries that you use in a webserver, and use numerous caching technologies.

• When users connect directly to the DB, the database has become your application platform. This represents a substantial lock-in into a particular technology and into a particular data model. Scaling a database server is also more difficult than scaling application servers.

  In contrast, most web applications get some amount of scalability by default just from running the database and any application logic on different servers.

If you keep all of that in mind, it is possible to create database-centric applications. This can be a good approach e.g. for intranet applications where you can trust all users. But due to the quite substantial problems this is not generally advisable.

GraphQL is weird because this turns your webserver into half a database server. However, you get the possibility to implement custom security models, implement caching, and offer a more convenient API than raw SQL.

“Real” REST APIs (i.e. not GraphQL) are conceptually very straightforward, and therefore make it very easy to implement security checks (probably one or two if-conditions per endpoint), and are very easy to cache. Their drawback (which GraphQL addresses) is that you often get more data than you need, and only get a single resource per request.

Answer 2

The main reason to prefer a RESTful API over direct SQL access is that it provides a static interface to a changing implementation - the API doesn't change if some of the data ends up being accessed through the file system or a messaging queue. Similarly, when you inevitably have to upgrade the database to a version with breaking changes, none of the clients have to change.

Another advantage is the simple query/response format. Compare five minutes to learn the basics of JSON to the days or weeks of writing a database client.

Access control is another big issue with databases. It's not just about who has access to what. How do you write complex rate limiting in a database? And how do you express complex application specific access controls such as who is allowed to send a personal message to whom? I'm sure it's possible to express these in a database, it's just that they can be much easier to express in languages geared towards general purpose programming.

Answer 3

Your idea sounds cool, but...

Giving out SQL access is a bit like handing over the keys to the car to a stranger. There are a few issues with it:

1) Security - If you can query a database, you can create tables, insert data, all kinds of things. Some databases have security capabilities such that you could limit the exposure, but it would be a lot of effort to do this properly and make sure that someone could not query things that they were not supposed to access.

2) Denial of service - it would be easy to tie up a server with either a flood of requests or complex queries that would effectively take it offline

One has to wonder how graphql can deal with these challenges and yet be secure?

Answer 4

First of all, an API (including REST) is not restricted to consume data from a database. It is means to invoke organized code from some application; it could be database related,but it also could run non-database activity, e.g. control some actuator, or read some sensor data, or calculate some complex formula based on your inputs.

Another reason for preventing your clients from directly accessing your database, is an extra layer of security; it is common wisdom, that any system exposed to an end user, is a potential exploit; it can be compromised. And if you can compromise some system directly interacting with your database, then that's a game over in security. This attacker can gain access to confidential data, can tamper data, and can delete data.

From maintainability point of view, decoupling your database from your client will simplify your design such that:

1. you can change your database without having to modify your client
2. you can fix bugs in your queries without modifying your client
3. you can change your table structure without modifying your client
4. (and a bonus) that you can retain multiple versions of API methods, to provide backward compatibility for older clients.