EN
Node.js - MySQL Update query
0 points
In this article, we would like to show you how to make an SQL UPDATE query in Node.js.
Note: at the end of this article you can find database preparation SQL queries, you will also find an example of a more parameterized query there.
xxxxxxxxxx1
const mysql = require('mysql');2
3
const connection = mysql.createConnection({ // gets connection with database4
host: 'localhost', // '127.0.0.1'5
user: 'root',6
password: 'password',7
database: 'my_database',8
});9
10
connection.connect(error => {11
if (error) throw error;12
const query = 'UPDATE `users` '+13
'SET `name` = ?, `role` = ? ' +14
'WHERE `id` = ?';15
const values = ['Christopher', 'admin', '2'];16
17
connection.query(query, values, (error, result) => { // sends queries18
connection.end(); // closes connection19
if (error) throw error;20
console.log(connection.sql); // UPDATE `users` 21
}); // SET `name` = 'Christopher, `role` = 'admin' 22
}); // WHERE `id` = '2'Before:
xxxxxxxxxx1
[2
{ id: 1, name: 'John', role: 'admin' }, 3
{ id: 2, name: 'Chris', role: 'moderator' },4
{ id: 3, name: 'Kate', role: 'user' }, 5
{ id: 4, name: 'Denis', role: 'moderator' } 6
]After:
xxxxxxxxxx1
[2
{ id: 1, name: 'John', role: 'admin' }, 3
{ id: 2, name: 'Christopher', role: 'admin' },4
{ id: 3, name: 'Kate', role: 'user' },5
{ id: 4, name: 'Denis', role: 'moderator' }6
]create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `users` (2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,3
`name` VARCHAR(100) NOT NULL,4
`role` VARCHAR(15) NOT NULL,5
PRIMARY KEY (`id`)6
)7
ENGINE=InnoDB;insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `users`2
(`name`, `role`)3
VALUES4
('John', 'admin'),5
('Chris', 'moderator'),6
('Kate', 'user'),7
('Denis', 'moderator');xxxxxxxxxx1
// ...2
connection.connect(error => {3
if (error) throw error;4
const query = `UPDATE ??5
SET ?? = ?, ?? = ? 6
WHERE ?? = ?`;7
const values = ['users', 'name', 'Christopher', 'role', 'admin', 'id', '2'];8
9
connection.query(query, values, (error, result) => { // sends queries10
connection.end(); // closes connection11
if (error) throw error;12
console.log(connection.sql); // UPDATE `users` 13
}); // SET `name` = 'Christopher, `role` = 'admin' 14
}); // WHERE `id` = '2'