3

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?

  • 7
    Congratulations, you just invented database as a service. – candied_orange Apr 14 '18 at 0:26
  • One of the main reasons for REST is interoperability, e.g. we have the uniform interface. What do you propose to use in its place with respect to SQL? – John Wu Apr 14 '18 at 1:27
  • This rather depends on a lot of factors, such as what the database stores, who the users are, their data access requirements. For example, if the users are data scientists, then it may be better to create frequent (nightly?) backups and provide those users the ability to take copies of those backups, where they can do whatever they like without affecting the live system. – Ben Cottrell Apr 14 '18 at 7:17
  • Have you heard about SQL injection and why is the very first you should be aware of when you publish web applications? Or any other API (no matter whether it's web or not) – Laiv Apr 14 '18 at 13:03
  • Just to be clear I'm not going to or proposing to do this; I'm wondering why this isn't something that is done, especially if the client can be trusted to some extent IE for some internal application. – yawn Apr 15 '18 at 0:46
7

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.

3

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.

  • For general security reasons as well... databases often contain highly sensitive data, and the more layers they're locked behind, the more difficult it (could be) for someone to hack their way into it. APIs are a highly effective layer in this regard. – jleach Apr 14 '18 at 12:30
  • @jleach The counter argument is that any database worth its salt provides a fairly rich set of access controls that could be used to do many of the same things being done by hand in a lot of APIs. – Blrfl Apr 14 '18 at 19:58
2

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?

  • Ive seen a production server crippled by a user who selected all data from the 2 biggest tables and forgot the join. The following cartesian product filled up temp db and logspace until the server filled up and stopped. Accidents happen. – Sir Swears-a-lot Jun 29 '18 at 9:24
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.