DEV Community

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

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

ganeshmani profile image GaneshMani Originally published at cloudnweb.dev ・3 min read

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;
  };
Enter fullscreen mode Exit fullscreen mode

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

Alt Text

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)
    }   
})
Enter fullscreen mode Exit fullscreen mode

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,

Alt Text

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

Alt Text

After the API update,

Alt Text

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)
    }   
})
Enter fullscreen mode Exit fullscreen mode

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.

Alt Text

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 :-)

Discussion

pic
Editor guide
Collapse
eomm profile image
Manuel Spigolon

Thanks for sharing this situation!

To protect me against these kinds of bugs, I started to write some test that checks not only the input and the output of an endpoint but test that checks data on DB too.
When I say "DB" I mean a fresh container that I turn on during every run of the tests; I avoid mock data and replies.

It will slow down a bit the development, the project I'm working on takes 3 minutes to run 1000 test cases, but when I expect some data updated there is a test for it!
But as always, you will benefit creating:

  • test tooling to speed up the implementation of tests
  • a db with a lot of test cases and edge cases you can replicate in seconds
  • core functionality must be always up&running and battle tests!
Collapse
oguimbal profile image
Olivier Guimbal

Shameless plug: You could also use pg-mem to emulate postgres when running unit tests :) (i'm the author of this rather new lib)

Collapse
pratiksharm profile image
Pratik sharma

this can be helpful. Thanks!