Summary: in this tutorial, you will learn how to update data in the MySQL database from a Node.js application.
This tutorial picks up where the Querying Data in MySQL Database from Node.js tutorial left off.
Updating data
To update data in a table in MySQL from a Node.js application, you follow these steps:
- Connect to the MySQL database server.
- Execute an
UPDATE
statement by calling thequery()
method on aConnection
object. - Close the database connection.
The following update.js
program sets the value of the completed
column of row id 1 in the todos
table to false:
let mysql = require('mysql');
let connection = mysql.createConnection({
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
});
connection.connect((err) => {
if (err) return console.error(err.message);
let sql = `UPDATE todos
SET completed = ?
WHERE id = ?`;
let data = [false, 1];
connection.query(sql, data, (error, results, fields) => {
if (error) return console.error(error.message);
console.log('Rows affected:', results.affectedRows);
});
// close the database connection
connection.end();
});
Code language: JavaScript (javascript)
How it works.
First, import the mysql
module:
let mysql = require('mysql');
Code language: JavaScript (javascript)
Second, create a connection to the MySQL database using the createConnection
method:
let connection = mysql.createConnection({
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
});
Code language: JavaScript (javascript)
Note that we retrieve the connection parameters (host, port, user, password, and database) from environment variables using .env
.
Third, connect to the MySQL database:
connection.connect((err) => {
if (err) return console.error(err.message);
});
Code language: JavaScript (javascript)
This code checks and logs for any connection errors if any.
Fourth, define an SQL query to update a record in the todos
table:
let sql = `UPDATE todos
SET completed = ?
WHERE id = ?`;
Code language: JavaScript (javascript)
Fifth, specify the data used in the query:
let data = [false, 1];
Code language: JavaScript (javascript)
In this case, the first placeholder ?
in the SQL query will be replaced by false
, and the second placeholder ?
will be replaced by 1
.
Sixth, execute the SQL query using the query
method:
connection.query(sql, data, (error, results, fields) => {
if (error) return console.error(error.message);
console.log('Rows affected:', results.affectedRows);
});
Code language: JavaScript (javascript)
This code executes the query and displays the number of rows affected.
Finally, close the database connection:
connection.end();
Code language: CSS (css)
Verify the update
First, retrieve the row with id 1 from the todos table:
SELECT * FROM todos WHERE id = 1;
Output:
+----+-------------------------------+-----------+
| id | title | completed |
+----+-------------------------------+-----------+
| 1 | Learn how to insert a new row | 1 |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
Second, run the update.js
program.
node --env-file .env update.js
Code language: CSS (css)
The output indicates that the number of updated rows is 1.
Third, query data from the todos
table:
select * from todos where id=1;
Code language: JavaScript (javascript)
Output:
+----+-------------------------------+-----------+
| id | title | completed |
+----+-------------------------------+-----------+
| 1 | Learn how to insert a new row | 0 |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
The data showed that the value in the completed column was updated to 0.
In this tutorial, you have learned how to update data in a MySQL table from a Node.js application.