javascript - MySQL NodeJS not getting latest data after write

Question

I am having trouble figuring out the problem to an issue where when I write data (create, update, delete) then write a query to get the data after, the data that I receive back is the data prior to the write.

For example:

Let's say I have two functions createApple() and getAppleById. I have a utility function called getConnection() that gets a connection from a pool to be used for transactional queries. I have an endpoint that creates an apple and I get back to the insertId from mysql then I use that to get the apple but when I return it as a response, I get an empty object.

const createApple = async ({ type }) => {
    const connection = await getConnection();
    await connection.beginTransaction();
    return await connection.query(`INSERT INTO apple (type) VALUES (?)`, [type]);
}

const getAppleById = async (appleId) => {
   const connection = await getConnection();
   return await connection.query(`SELECT * FROM apple WHERE id = ?`, [appleId]);
}
router.post(`/api/apples`, async (req, res) => {
    const { insertId: createdAppleId } = await createApple({ ...req.body });
    const apple = await getAppleById(createdAppleId);
    res.status(201).send(apple); // <-- this returns {}
});

I noticed that if I add a console.log() before sending the data back, it does get back the data, for example:

router.post(`/api/apples`, async (req, res) => {
    const { insertId: createdAppleId } = await createApple({ ...req.body });
    const apple = await getAppleById(createdAppleId);
    console.log(apple);
    res.status(201).send(apple); // <-- this now returns the newly created apple
});

Any ideas on why this may be happening? Also, is this considered a good way of getting a newly created/updated entity or would it be better to make two separate calls:

1. First call to create/edit the entity (a POST or PATCH call)
2. Second call to get the entity (a GET call)

Any help is appreciated!

Thanks!

question from:https://stackoverflow.com/questions/65928809/mysql-nodejs-not-getting-latest-data-after-write

Answer 1

const createApple = async ({ type }) => {
    const connection = await getConnection();
    await connection.beginTransaction();
    await connection.query(`INSERT INTO apple (type) VALUES (?)`, [type]);
    await connection.commit();
}

I think error this function when you use transaction, you should commit or rollback transaction after finish query

This is best practice for me, I hope it useful for you

const createApple = async ({ type }) => {
    const connection = await getConnection();
    await connection.beginTransaction();
    try{
        await connection.query(`INSERT INTO apple (type) VALUES (?)`, [type]);
        await connection.commit();
    }catch{
        await connection.rollback()
    }
}