I Accidentally wiped the entire dataset in the Production database. Here's what happened next

One of the tragic accident in my job turned out to be good learning for me in recent days. Yes, I accidentally wiped the entire dataset in the production database.

Before, getting into the details of it. let me tell you a bit about myself, I am a Full Stack Engineer specialized in React, Nodejs. I am working in a startup where we use Nodejs, Postgres in production.

Recently, i was working in an API which updates the database table. table has structure like this

module.exports = (sequelize, Sequelize) => {
    const User = sequelize.define("user", {
      name: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      data : Sequelize.JSON,
    });

    return User;
  };

an important thing to note here is the data that is in the JSON structure. API that I built should update the data object in the table.

To give you an example, Here's the structure of data on GET request

it contains the data in JSON structure which has fields such as phone, address, country, and postal code.

So, I need to write an API to change the phone number inside of the data. I thought it's kind of easy one to do and did that in a few steps.

app.post('/phone-number/update/:id',async (req,res) => {
    try {
        const id = req.params.id;
        const phonenumber = req.body.phonenumber

        const user = await User.update({ "data.phone" : phonenumber  },{
            where : {
                id
            }
        })

        res.status(200).json(user)
    }
    catch(e){
        res.status(500).json(null)
    }   
})

it updates the phone number inside of the data object. everything looks good so far. After that, I wrote a test-case to cover the scenario where the updated phone number is stored in the DB and it passed.

Everything looks good so far. So, it went to production. i didn't realize the disaster that is going to happen on production. i was in my home like,

Here's what happened in production, the API entirely wiped the JSON data replacing them with just phone number. i was shocked to see this,

To give you an example, Here's the data before and after the API

After the API update,

Then, i realized that postgres update on JSON shouldn't work like this. Luckily, our database took a backup exactly before this came up. so, i just rolled back the database data and commented out the functionality in this API.

but, my CTO noted this issue and helped me to resolve this one. In the meantime, I found an another way to fix the API functionality. it will be something like,

app.post('/phone-number/update/:id',async (req,res) => {
    try {
        const id = req.params.id;
        const phonenumber = req.body.phonenumber

        let user = await User.findOne({ id })

        user.data = { ...user.data,phone : phonenumber }
        await user.save()

        res.status(200).json(user)
    }
    catch(e){
        res.status(500).json(null)
    }   
})

I know it doesn't look like an effective solution. but at that moment, I wanted a working solution that should be deployed. I quickly wrote test-cases to cover the scenario and raised a PR. CTO personally looked into this code and reviewed it and gave some feedback. finally, the patch was merged into production that day.

it may look like a small & silly issue. but, it has the potential to wipe out the entire dataset. Instead of worrying about this incident. I took it as good learning for me. they are,

• Even if we write test cases to cover the situation. it's always one step better if we can manually test the situation.
• Never get stressed if your code broke the production server. I know it shouldn't happen. In some worst cases, it will. So, take it as a learning curve and avoid that mistake in the future.
• Things like this will happen, At that moment, Having a supporting colleague or seniors will help a lot. That comes along in our careers.

I hope you learned something from this real-world experience. I wanted to share my experience so that you can get that experience without you need to undergo the same situation. we will see in another real-time experience. until then, Happy Coding :-)