need help figuring out why a sequelize findOrCreate(...) works sometimes but not others.

Question

I am converting json file items to SQL table entries. I have 5 SQL entities that might be written. Courses (online learning experiences), CourseDetails, Learners, Outcomes and OutcomeDetails. The cardinal goal is to NOT create any redundancies in the SQL while using auto-generated Ids in all the tables. The Table definitions are roughly as follows:

Learners - 1 row for each unique individual who has ever taken any course or courses.
Courses  - 1 row for each unique Course (no mater how many Learners have taken the Course)
Course Details - 1 row for each question asked in the Course, describing the correct results
Outcomes - 1 row that captures the intersection of 1 Learner, taking 1 Course, at 1 Completion Date/Time
Outcomes Details - 1 row for each question asked in the Course, describing the Learner responses

Each json file is an instance of an Outcome with its Details. Therefore, each json file represents 1 Learner, taking 1 Course, completing it at 1 Time. However, any json file may have either a Learner or a Course (or both, if repeating a course) that has already been saved to the SQL tables, either in a previous batch OR in a previous file in this batch.
The requirement is to save any and all Outcomes/OutcomeDetails, not already saved and any and all Learners and Courses, not already saved. Remember that Outcomes/OutcomeDetails have the extra dimension of time for uniqueness, whereas Learners and Courses are static, in the sense that, once they are saved, their record persists and they never need be saved again (as a separate entity), even should the same Learner take the same Course at a later time (because that would be a new Outcome). In other words Learners and Courses are unique as static Entities, but may be included in multiple Outcomes.
To achieve this I am using .findOrCreate(...) on each of the entities. If the row is found it will not be created, otherwise it will. The problem I am running into is the asynchronous nature of javascript. Even though I am using async / await, the await function is not preventing (only in some cases) a second Course of the same Number from creating a row in the Courses table, whereas I have run the app many times and the Learners table never gets updated with redundant rows.
Here is a link to the gist with the code in it. Help is appreciated.
https://gist.github.com/dhawkinson/41211d067ea91e0b7a5823067d2e39fe

Brendan Whiting · Accepted Answer

I'm not sure if this is the root of the issue, but I can point to one place that I think could be problematic:
JavaScript
router.get('/', (req, res) => {
    //  iterate on outcomes
    let failedOutcomes = [];
    outcomeIds.forEach((outcomeId) => {
        let jsonData;
        let uri = new URL(`${url}${outcomeId}`);
        jsonData = JSON.parse(fs.readFileSync(uri));
        processCourse(jsonData);
        processLearner(jsonData);
        processOutcome(jsonData, outcomeId, failedOutcomes);
    });
    /*if ( failedOutcomes.length ) {
        let uri = `${url}${idsID}`;
        let dataIn  = fs.readFileSync(uri);
        let idsList = JSON.parse(dataIn).ids;
        idsList = idsList.concat(failedOutcomes);
        idsList = JSON.stringify({"ids": idsList});
        fs.writeFileSync(uri, idsList);
    }*/
    res.render("process");  //  install a progress bar of some sort
}); // end of router GET

It seems like this is what we're doing here... when a client hits the "/" endpoint, we loop through each of the ids, for each of ideas we process all the json data by checking the database to see if it exists, and creates new records if it doesn't exist. 
I think what you need to do is load your seed data into the database when the server boots up. It shouldn't wait until a client hits an endpoint to seed data. You're also going through the same process of seeding the data for each outcomeId. You only need to do it once. It's also generally a bad idea to fire off anything asynchronous inside a loop.
I suggest turning on logging in your sequelize config (https://stackoverflow.com/questions/21427501/how-can-i-see-the-sql-generated-by-sequelize-js), that way it will print out to the console what SQL queries are actually being run. What I'm expecting you'll see is a lot of extra redundant queries are being run, and since they get fired off in a loop they maybe getting mixed up with each other somehow.

Doug Hawkinson · Answer

Brendan et al.
Problem solved. As you suspected Brendan I was mixing synchronous coding with asynchronous coding. Here is the solution and the note I wrote to my self to avoid this problem in the fulture.
```
//  NOTE: to self. This block of code solves the problem of mixing
    //  the synchronous "array.forEach()" with embedded asynchronous tasks
    //  by replacing it with an asynchronous "for ( item of array )" 
    //  with the same embedded asynchronous tasks and "awaiting" them.
//  define the function
const processOutcomeIds = async (outcomeIds) => {
    for ( const outcomeId of outcomeIds ) {
        let jsonData;
        let uri = new URL(`${url}${outcomeId}`);
        jsonData = await JSON.parse(fs.readFileSync(uri));
        await processLearner(jsonData);
        await processCourse(jsonData);
        await processOutcome(jsonData, uri, outcomeId, failedOutcomes);
    }
}
//  call the function
processOutcomeIds(outcomeIds);
// ... more code ...

This replaces the previous:

outcomeIds.forEach((outcomeId) => {
        let jsonData;
        let uri = new URL(${url}${outcomeId});
        jsonData = JSON.parse(fs.readFileSync(uri));
        processCourse(jsonData);
        processLearner(jsonData);
        processOutcome(jsonData, outcomeId, failedOutcomes);
    });
```

Welcome to the Treehouse Community

Looking to learn something new?

Doug Hawkinson

Doug Hawkinson

need help figuring out why a sequelize findOrCreate(...) works sometimes but not others.

Brendan Whiting

Brendan Whiting

2 Answers

Brendan Whiting

Brendan Whiting

Doug Hawkinson

Doug Hawkinson

Brendan Whiting

Brendan Whiting

Doug Hawkinson

Doug Hawkinson

Brendan Whiting

Brendan Whiting