I have an API endpoint that retrieve data from 3 SQL tables on the server-side. At the moment, I do a SELECT with joins, consolidate/reorganize the selected data based on a JSON schema, and then send the JSON to client. The point here is that, almost none of the data values is changed, only their structure is transformed from the SQL schema to the JSON schema.
On the client side, I also maintain 3 SQL tables that are very similar to those on the server. At the moment, I parse the JSON, re-transform the data, and save them into those tables.
I wonder, on the server-side, whether we could simply dump the the data from each of the 3 tables into JSON without any transformation, so that both server and client can save doing any transformation/restructuring, considering the tables on both sides (more or less) match in schema.
The (only?) benefit of this idea is obvious, but I do wonder if there is any serious drawback. For examples:
-
We lose data abstraction on the server-side.
-
We sort of rely on the close matching between schemas of tables on both sides, which probably makes it harder in the future to change/add columns to those tables on either side.
-
This may be OK for an internal API that is consumed by clients I control, but probably is not so great for external clients/developers in the future.
So far I can only come up with the above, and I am not 100% sure how severe each of the problems is.
Your JSON data should contain the information that the user of the API expects. Which should mostly mean that it is documented and gives the information that is documented. The schema should not depend on your database design. And of course if your database design changes, the API cannot change. So basing your API on your current database design means that after a change you have to translate the existing data into data based on the old database design, which is a pain.
What I think is very useful is when there is no redundancy whatsoever. Because every time there is redundant information, the consumer has to worry about what to do if the redundant information isn’t consistent.
2
I think that, exactly like you’ve pointed out, the big considerations would be
- What would happen if the implementation on the client or server side changes.
- How would future developers extend the API if it was written that way
- How would this design impact modularity/reuseability of the API
What you’re proposing would tightly couple part of the API to the database schema, which in general is something to avoid. Check out this API keynote from Joshua Bloch. One of the general principles in there is that the implementation should not impact the API. It’s not a “rule”, but unless you have a good reason to do so, I would keep the implementation and the API decoupled as much as possible.
1