How to Update the Database After a Drag-and-Drop Operation
Tags: node, javascript, database, mysql
Published: 2021-10-04 08:17:21

I researched what happens in the database behind the scenes when drag & drop is done and what happens to the data after the drag & drop.
In this post, I will write about the database operations when dragging and dropping through the implementation of a to-do list.


[This is a demo for this post](https://serverless-todo-app-mh37n93gm-sho-ki.vercel.app/)

You will see that the order of tasks will not change even after reloading.


## Overview
### Problem
Even after changing the order of tasks by drag & drop, the drag & drop order change is not saved when reloading.


### Solution
When you do drag and drop, you need to operate the database on the backend side in addition to the frontend side.


## How It Works
### Step 1. Add a column to the database to control the order
Create a column called `index_number` in the table. Then, when the task is stored in the database, it will have the number `index_number` in addition to the id and content.


### Step 2. Fill the newly created column with data
When adding the new data,
**Case1. if there are no rows in the table,**
Insert `index_number` = 1024


**Case2. if the table has at least one row,**
Set `index_number` = current maximum `index_number` + 1024

This will lead you to create a database table as shown below:
![ExampleTable](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q0bhuwm3botpz5y89vs8.png)


### Step 3. Perform drag and drop, update `index_number` of dragged and dropped element
Once you make some data in the table, start the server up and perform drag & drop. In the above figure, for example, if you want to put “study” between “eat” and “sleep” by dragging and dropping, 
set (3072(eat) + 4096(sleep)) / 2 
as the new `index_number` for “study”.
 
(3072 + 4096) / 2 = 3584 ← This will be the new `index_number` for “study”. The table will be updated as follows:
![UpdatedTable](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/esx3gecilezaor2pjo77.png)


### Step 4. Use ORDER BY when retrieving and displaying the table
By querying this table with “ORDER BY `index_number`”, you can retrieve the data in ascending order by `index_number`. Thus, even if the order is changed by drag-and-drop and then reloaded, the order is preserved.


### Step 5. If `index_number` overlaps
After dragging and dropping a task, the task’s `index_number` is calculated by taking the average of `index_number`s of the task above and the task below.

So sometimes, the `index_number` of two tasks may overlap.

Only in this case, it is necessary to arrange the entire table in order of decreasing `index_number`, and then reassign the `index_number` by *1024.



## Implementation
### Languages and libraries used
**Frontend**
・JavaScript
・[SortableJS](https://github.com/SortableJS/Sortable)
**Backend**
・Node.js
・MySQL

### File Structure
![File Structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pi51akt6691kb6puwi15.png)


### Step1. Install the necessary npm
`npm i express mysql2 path body-parser util dotenv --save`
`npm i nodemon --save-dev`

### Step2. Write a CRUD function for the To-Do list
The code for editing and deleting tasks and retrieving a single piece of data is the same as the regular ToDo list with CRUD function, so I’ll skip it.


From the following steps,
I will write the code for: 
the list function (retrieving all data), 
the create function (adding a task), and 
the drag & drop function (SQL operations) in that order.

### Step3. List function (retrieving all data)
Basically, it just extracts the data as usual, but the SQL statement is a little different from the regular ToDo list.

```javascript
app.get("/list/apis", async (req, res) => {
  try {
    const results = await util.promisify(connection.query).bind(connection)(
      "SELECT * FROM todo ORDER BY `index_number`" // Use ORDER BY `index_number`
    );

    res.json({ results });
  } catch (e) {
    res.status(500).send({ e });
  }
});
```

One point that is different from the usual way of retrieving tasks is that the tasks are retrieved in ascending order of `index_number` in ORDER BY index_number.
In this way, even if you drag and drop, the database will know the order of all tasks based on the `index_number`, and you can retrieve data correctly.


### Step4. Create function (adding a task)
When you add a new task, you need to get the maximum value of the current `index_number` and make the number +1024 to the `index_number` of the new task. 
By doing so, the new task will be added to the bottom of the To-Do list.

```javascript
app.post("/add-todos", async (req, res) => {
  // value of todo task
  const todo = req.body.todo;

  try {
  // Get and return the maximum value of `index_number`
  // if there is no data in the table, return 0
    const results = await util.promisify(connection.query).bind(connection)(
      `SELECT IFNULL((SELECT index_number FROM todo ORDER BY index_number DESC LIMIT 1) ,0) as max_index_number;`
    );
  // Add a new task
  // Put the contents of the task and the value obtained in the above query + 1024 into VALUES
    await util.promisify(connection.query).bind(connection)(
      `INSERT INTO todo(todo, index_number) VALUES('${todo}', ${results[0].max_index_number}+1024)`
    );
    res.redirect("/");
  } catch (e) {
    res.status(500).send({ e });
  }
});
```

### Step5. Drag & drop function (MySQL operations)
By writing here, the order will be saved in the database, and the order will remain the same even after dragging and dropping and reloading.

The points are:

1. Get the `index_number` of the task above and below the task you dragged and dropped.

2. If there is no task above the dragged and dropped task, `index_number` cannot be obtained. So the `index_number` of the task above the task you dragged and dropped will be undefined.

3. Same as (2), if there is no task below the dragged and dropped task, `index_number` cannot be obtained. So the `index_number` of the task below the task you dragged and dropped will be *undefined*.


4. If the `index_number` overlaps, ORDER BY index_number for the entire table, and reassign the `index_number` in ascending order.
```javascript
app.post("/order-todos/:id", async (req, res) => {
  const id = req.params.id;
  // index_number of the task above the dragged and dropped task
  let prevElIndexNumber = req.body.prevElIndexNumber;
  // index_number of the task under the dragged and dropped task
  let nextElIndexNumber = req.body.nextElIndexNumber;
  // a variable containing the index_number of the dragged and dropped task
  let currElIndexNumber;

  // prevElIndexNumber === undefined, this is happended when the drag-and-drop task is at the top of the to-do list.
  // Since there is no upper task, set the index_number of the lower task - 512 as the currElIndexNumber
  if (prevElIndexNumber === undefined) {
    currElIndexNumber = nextElIndexNumber - 512;
  // nextElIndexNumber === undefined, this is happended when the dragged-and-dropped task is at the bottom of the to-do list
  // Set the index_number of the task above + 512 as the currElIndexNumber
  } else if (nextElIndexNumber === undefined) {
    currElIndexNumber = prevElIndexNumber + 512;
  // If there are tasks both above and below the dragged-and-dropped task, then
  // currElIndexNumber = (index_number of the top task + index_number of the bottom task)/2
  } else {
    currElIndexNumber = Math.floor((prevElIndexNumber + nextElIndexNumber) / 2);
  }

    try {
    // Update currElIndexNumber as the index_number of the new task
    await util.promisify(connection.query).bind(connection)(
      `UPDATE todo SET index_number = ${currElIndexNumber} where id = ${id}`
    );

    // When index_number overlaps
    if (
      Math.abs(currElIndexNumber - prevElIndexNumber) <= 1 ||
      Math.abs(currElIndexNumber - nextElIndexNumber) <= 1
    ) {
      // Get index_number in ascending order from 1~ (= orderedData), then update the table
      const orderedData = await util
        .promisify(connection.query)
        .bind(connection)(
        `SELECT *, ROW_NUMBER() OVER (ORDER BY index_number) as orderedData FROM todo;`
      );
      await Promise.all(
        orderedData.map(async (element) => {
          await util.promisify(connection.query).bind(connection)(
            `UPDATE todo SET index_number = ${element.orderedData}*1024 where id = ${element.id}`
          );
        })
      );
    }
    res.end();
  } catch (e) {
    res.status(500).send({ e });
  }
});

```
It’s a bit long, but here’s a quick diagram

![diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kwsow0y4dm4xlgtn3kor.png)


### Step6. JavaScript on the front-end side
Here is a simple explanation of the code from extracting api in json format on load to display all tasks, to sending http request when drag and drop is done.

#### Extract and display api in json format
```javascript
// fetch api and display all stored datas
const wrapper = document.getElementById("wrapper");
window.onload = async () => {
  try {
    // fetch all data of todo
    await fetch("http://localhost:3000/list-todos")
      .then(async (allToDo) => {
        return await allToDo.json();
      })
      .then((datas) => {
        datas.results.forEach((el) => {
          const todoEl = document.createElement("div");
          todoEl.classList.add("item");
          const taskId = el.id;
          const text = el.todo;

          todoEl.setAttribute("taskId", taskId);
          todoEl.innerHTML = `<span class="txt" onClick="startEditToDo(this, ${taskId})">${text}</span><i class="trash fa fa-trash" onClick="deleteToDo(this.parentNode, ${taskId})"></i><i class="icon fa fa-bars"></i>`;
          // changePostion() after dragend
          todoEl.addEventListener("dragend", () => {
            changePosition(todoEl, taskId);
          });
          wrapper.appendChild(todoEl);
        });
      });
  } catch (e) {
    console.log(e);
  }
};
```

#### Http request processing when the drag & drop is done
In the above code, changePosition() fires every time the dragging of each task is completed.
In changePosition(), the `index_number` of the task above and below the dragged and dropped task is obtained, and the data is sent by http request.
```javascript
async function changePosition(currEl, currElId) {
  let prevElIndexNumber;
  let nextElIndexNumber;

  try {
    // Get index_number if there is a task on top of the dragged and dropped task
    // if not, undefined
    if (currEl.previousSibling !== null) {
      const prevElId = currEl.previousSibling.getAttribute("taskId");

      await fetch("http://localhost:3000/read-todos/" + prevElId)
        .then(async (data) => {
          return await data.json();
        })
        .then((json) => {
          prevElIndexNumber = json.results[0].index_number;
        });
    }

    // Get index_number if there is a task under the drag & drop task
    // if not, undefined
    if (currEl.nextSibling != null) {
      const nextElId = currEl.nextSibling.getAttribute("taskId");
      await fetch("http://localhost:3000/read-todos/" + nextElId)
        .then(async (data) => {
          return await data.json();
        })
        .then((json) => {
          nextElIndexNumber = json.results[0].index_number;
        });
    }

    // HTTP Request
    const updateUrl = "http://localhost:3000/order-todos/" + currElId;

    await fetch(updateUrl, {
      method: "POST",
      headers: {
        "Content-type": "application/json",
      },
      body: JSON.stringify({ prevElIndexNumber, nextElIndexNumber }),
    });
  } catch (e) {
    console.log(e);
  }
}
```

## Summary
The whole thing, including the rest of the code, is on [GitHub](https://github.com/Sho-ki/drag-and-drop-todo).


There are probably many other ways to save the position after dragging and dropping, but as one way to think about it, I wrote a way to save the position by calculating the number the element has depending on the position of the target element.