22

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?

  • 3
    There's no single convention in DB table naming nor RESTful resource naming that everybody follows. On the contrary, there may be many conventions. It's not surprising that some may clash. – Eric King Jul 23 '15 at 20:24
  • 13
    There is no such established convention. I've always used plural table names. users, accounts, etc, since they are holding more than one of that thing. – GrandmasterB Jul 23 '15 at 20:43
  • 2
    @GrandmasterB The convention do exist and it has its origin in Codd's relational model where "relations" (that become tables, not to confund with relationships) have singular names because a relation is a list of things not several lists of things. Each relation is one list. Relations model domain entities. Entities names are singular in Codd's relational model. There's abundant literature about it as to say it doesn't exist. But it is perfectly OK for you to use plural names if you want. – Tulains Córdova Jul 23 '15 at 20:59
  • 4
    @user61852 There are people who may use it by convention. But it is by no means a broadly followed industry convention as presented in this question in the way, say, that camelCase or MarkDown is. – GrandmasterB Jul 23 '15 at 21:08
  • 4
    Also keep in mind that REST is not a database access protocol. There is no reason that DB naming conventions (which ever one you go with) and URL naming conventions (which ever one you go with) should be anything alike, they have nothing do with each other. Two very different domains. It no more makes sense to ponder why databases use singular and URLs uses plural than to ponder why databases use single but my sys admin names his file system directories plural. Poorly designed web frameworks have people thinking REST is something to do with DB access, but it isn't. – Cormac Mulhall Aug 5 '15 at 8:27
11

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.

  • 1
    Answers a different question. OP does not imply direct association of API and DB entities, just presence of some entities in both contexts. – Basilevs Feb 3 '17 at 19:07
  • 2
    Feel free to post an answer to the question you think is being asked. – Kasey Speakman Feb 3 '17 at 22:18
  • 4
    @Basilevs Actually I think this does answer the question. Sometimes answers may appear indirect when a question is framed around incorrect assumptions. The "presence of some entities in both contexts" implies they are the same entities, which implies a 1 to 1 correspondence between some and not others. Such a correspondence of an API over a complex data model implies a flawed design. – Aluan Haddad Feb 25 '17 at 4:44
  • Among these are many reasons I stopped using Spring Data Rest. – Sebastiaan van den Broek Jan 6 at 6:04
4

REST API and the SQL are NOT "doing the same thing"

The OP asks:

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

Ah, but grasshopper, it may appear that the RESTful interface and the SQL tables "are doing the same thing", but good programming hygiene tells us that there's always an intermediate layer that mediates between the REST API and database. To ignore this point is to stray from the path to software enlightenment! :)

So RESTful APIs and SQL tables can happily follow their own idiomatic naming conventions, which are well documented and thoroughly discussed elsewhere.

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.