I've run into this same problem over and over again and I haven't found a solution that I really felt was optimal.

Say in an app, you have an ordered list and you let the user change that order by drag and drop or something. You want the changes in the order to persist. How do you model that?

How should I design a restful service of an ordered list resource?

In particular, how should I design the list and item model of a restful resource? The most common design I've seen is the item entity having an order or position property. Another approach I've heard is a doubly-linked list on the items.

What's an approach that doesn't write too much to the database and is generally fast to update and read for clients? How should the endpoints be exposed as?

  • Out of curiosity, why is it important to return an ordered list specifically? – Adam Wells Sep 27 '16 at 17:08
  • 1
    Well I guess I'm not specially looking to return an ordered list resource but just persist order/position of resource that can be part of an actual list resource or just implicitly part of a list. I want ti say let the user to change the order of a todo in a list of todos. But no matter what there is still a list where the order matters. What I can't find is a good way to design this – Rico Kahler Sep 27 '16 at 17:18

Representing an ordered list is one of the difficult problems with relational databases. Adding an position property to the list-membership relation is the most common way to do this, since you can easily retrieve the ordered list by adding ORDER BY position to your SQL query, and because you can easily insert items in the middle of the list by averaging the values of the previous and subsequent list member, assuming the position is a float rather than integer.

Using doubly linked lists should be avoided, since it's easy to accidentally make the links inconsistent, and end up with a cyclic graph or tree instead.

However, RESTful APIs do not suffer from the restrictions of relational databases. You can just do something that feels natural, rather than using a hack like a position property.

If you only have up to a few hundred elements in the list, just transfer the whole list in a request. Assuming we want to reorder [1, 2, 3, 4] where the list members are IDs, we could

POST /url/of/the/list
Content-type: application/json

[1, 2, 4, 3]

The backend may then translate this to whatever database technology you are using, but the API user does not have to consider these details.

If the list is large and items would usually be requested individually, you can allow an index in the url:

GET /page/7

If you're into HATEOAS, the response can include prev/next links to make navigation simple, if the resource would usually be consumed like that. However, this need not imply that your database also contains this doubly-linked list.

If the list is very large, you might want to expose ArrayList-like operations such as insert or push/append. I could imagine a call such as

POST /url/of/the/list?at=1357;mode=insert

description of the item to insert

If reordering is a common use case and the reordering should be committed immediately, then you could offer an appropriate endpoint in your API:

POST /url/of/the/list/reorder-item?from=783;to=1357

If the reordered list should be committed explicitly, it will be easier to transfer the new order as a JSON document, see above.

Now it's not quite true that you can view your API as completely separate from the database technology you are using. However, it is best to keep the external API as free as possible from implementation details. If any reordering touches about 30 rows just to update an integer order column, that's no big deal. Just do the simplest thing possible and always update the whole list. If your scale requires your database usage to be more sophisticated, prefer capturing this sophistication in the backend, where it's easier to maintain consistency.

  • 1
    Note that the "replace the entire list" approach may become problematic if there are multiple clients making changes. If you don't take any precautions, you may end up overwriting someone else's changes ("last to write wins"). – oefe Jun 3 '19 at 9:47
  • List-like operations should not have this issue, provided that the parameters (at, from, to) are ids, not list indexes – oefe Jun 3 '19 at 9:49

I think the viability of different approaches depends heavily on the underlying database being used.

This approach suggested to move an item in the ordering:

POST /url/of/the/list/reorder-item?from=783;to=1357

That can be subject to race conditions unless you have SERIALIZABLE transactionality. The default level of READ_COMMITTED in most DBs will not eliminate a race condition!

I actually think that in most cases - as long as the list is relatively small - then the replace-full-list approach has less issues with race conditions and cannot corrupt the data. If the set of items has changed since the client made the request, you can return a 409 (Conflict).

It suffers from last-write-wins, but that's true of literally ANY api. No matter what API you are implementing, another client may have updated the thing while you are viewing a page.

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.