How would you map fields values from different tables to ids from an API?

Question

I know the title might not be self explanatory so I will try to present my problematic with as much clarity as possible.

I have a NodeJS app that needs to regularly post some of its content to an external API. This content is stored in a MySQL database, and is scattered between several tables. So let's say we have two tables (with a lot more rows than this obviously but you catch my drift) :

Table A:
+-------+---------+---------+---------+
|  id   | field_1 | field_2 | field_3 |
+-------+---------+---------+---------+
| uuid1 | value1  | value2  | value3  |
+-------+---------+---------+---------+

Table B:
+-------+-------------------+---------+---------+
|  id   |  id_field_A (FK)  | field_4 | field_5 |
+-------+-------------------+---------+---------+
| uuid3 |  uuid1            | value4  | value5  |
+-------+-------------------+---------+---------+

and for each row of table A joined with table B I have to Post an object which DTO would look like this :

{
    "title": "value3",
    "attributes: {
        "10": "value1",
        "9": "value2",
        "1": "value4",
        "16": "value5" 
    }    
}

So each id ("1", "9", "10", "16" in the example) is used as a key in the object attributes and its value should match the value of one of our field from the tables A or B.

The external API ids ("1", "9", "10", "16") should not vary between the different environments so my idea was to create a new table to map these ids to table and field names and get this data from our database any time we need to post or update the data to the API. Like this:

Table api_attribute:
+-------+-----------------+------------+-----------------+
|  id   |   table_name    | field_name | id_external_api |
+-------+-----------------+------------+-----------------+
| uuid4 |  Table A        | field_1    | "10"            |
+-------+-----------------+------------+-----------------+
| uuid5 |  Table A        | field_2    | "9"             |
+-------+-----------------+------------+-----------------+
| uuid6 |  Table B        | field_4    | "1"             |
+-------+-----------------+------------+-----------------+
| uuid7 |  Table B        | field_5    | "16"            |
+-------+-----------------+------------+-----------------+

I'm pretty new to the development game so I'm not sure if this would be a recommended solution, and if it is not I'm simply struggling to find any articles on this kind of subject so I guess I'm missing some keywords here for this particuliar problem...

Edit: Now that I've written all of that I realize it might be a bit overkill and I just need to map all these fields in plain javascript since the id can't be determined programmatically anyway. Maybe I should be using a simple class to create the DTO and use it in my api service like so :

>>>>>>>>>> apiDTO.class.js
class ApiDTO {
    constructor({field_1: value1, field_2: value2, ..., field_5: value5}) {
        this.title: value3;
        this.attributes = {
            "10": value1,
            "9": value2,
            "1": value4,
            "16": value5 
        };
    }
}
module.exports = ApiDTO ;

>>>>>>>> api.service.js
const ApiDTO = require('./apiDTO.class');

module.exports.createAnApiEntity = async () => {
    const allDataToSendToApi = await dataFromDService.getAllFromTableAJoinTableB();
    for (dataToSend of allDataToSendToApi) {
        const apiDto = new ApiDto(dataToSend);
        sendToApi(apiDto);
    }
}

The thing is, this solution would mean that only developers would be able to change the ids since it is hardcoded data and the changes would require a new release if we were to add fields and values to the payload...

score 2 · Answer 1 · answered May 26 '20 at 00:41

Assuming dataToSend is always a flat object, you can put the DTO definition in your second snippet to a file (e.g. dtoTemplate.json) in the exact JSON format you currently have. That file can be read by your code during the runtime (you can use this answer or maybe one of the async methods discussed in the linked question) and you can dynamically match the values in your template JSON file with the keys in dataToSend.

A simple implementation can be:

function convertToApiDTO(dataToSend) {
  const fs = require('fs');
  const templateJson = fs.readFileSync('path/to/dtoTemplate.json', 'utf8');
  return JSON.parse(
    templateJson,
    (dtoKey, dbKeyOrValue) => dbKeyOrValue in dataToSend ?
      dataToSend[dbKeyOrValue] :
      dbKeyOrValue
  );
}

I suggest adding extra error handling since having invalid JSON in your file can break this function. Also if your JSON file can be edited by untrusted third parties parsing it can also be dangerous due to prototype poisoning vulnerability.

Note that if dbKeyOrValue is not a key in dataToSend, this function sets the dbKeyOrValueas a constant value with key dtoKey in the result object. If you'd rather skip these fields then you can return undefined instead of dbKeyOrValue.

How would you map fields values from different tables to ids from an API?

1 Answers1