Rest – Why does convention say DB table names should be singular but RESTful resources plural

apiconventionsnaming-standardsrestsql

It's a pretty established convention that database table names, in SQL at least, should be singular. SELECT * FROM user; See this question and discussion.

It's also a pretty established convention that RESTful API resource names should be plural. GET /users/123 and POST /users See this one.

In the simplest database-backed API, the name of the resource in the URL would be the table, and the data elements in the URL and request/response bodies would map directly to the columns in the DB. Conceptually, I don't see a difference between operating on the data through this theoretical API versus operating on it directly through SQL. And because of that, the difference in naming conventions between user and users doesn't make sense to me.

How can the difference in pluralization be justified when, conceptually, the REST API and the SQL are doing the same thing?

Best Answer

The REST spec (whatever level you want to go with) wasn't designed as database access. It is trying to bring standardization to API access. The SQL conventions mentioned (whether you want to use them or not) were not designed with API access in mind. They are for writing SQL queries.

So the issue to unpack here is the conceptual understanding that an API maps directly to the database. We can find this described as an anti-pattern at least as far back to 2009.

The principal reason this is bad? The code describing "how does this operation affect my data?" becomes client code.

This has some pretty terrible effects on the API. (not an exhaustive list)

It makes integrating with the API difficult

I imagine the steps to create a new user documented as something like this:

  1. POST /users { .. }
  2. POST /usersettings { .. } with some default values
  3. POST /confirmemails { .. }

But how do you handle a failure of step #2? How many times is this same handling logic copy-pasta'd to other clients of your API?

These data operations are often easier to sequence on the server side, while being initiated from the client as a single operation. E.g. POST /newusersetup. DBAs may recognize this as a stored procedure, but the API operation may have effects beyond just the database.

Securing the API becomes a black hole of despair

Let's say you need to merge two user accounts.

  1. GET /users/1
  2. PUT /users/2 { .. }
  3. DELETE /users/1

How are you going to setup a user permission to allow the merge feature while not allowing user deletion? Is deleting a user even fairly represented by DELETE /users/1 when /usersettings also exists?

API operations should be looked at as higher-(than-database)-level operations which may cause multiple changes in the system.

Maintenance becomes harder

... because your clients depend on your database structure.

Based on my experience with this scenario:

  • You cannot rename or remove existing tables/columns. Even when they are named incorrectly for their function or are no longer used. Clients will break.
  • New features can't change existing data structures, so its data and functionality is often artificially separated even when it holistically belongs with an existing feature.
  • The code base gradually becomes harder to understand due to fragmentation, confusing names, and left-over baggage which can't be removed safely.
  • All but trivial changes become increasingly risky and time-consuming.
  • The system stagnates and is eventually replaced.

Don't expose your database structure directly to clients... especially clients you do not have developmental control over. Use an API to narrow the client down to just valid operations.

So if you are using an API as just an interface straight into a database, pluralization is the least of your worries. For other than a throw-away experiment, I would suggest spending some time determining the higher-level operations the API should represent. And when you look at it that way, there's no conflict between pluralized API entity names and singular SQL entity names. They are there for different reasons.

Related Topic