EN
Node.js - MySQL Insert query
0 points
In this article, we would like to show you how to make an SQL Insert query in Node.js.
Note: at the end of this article you can find database preparation SQL queries.
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: 'database_name',8
});9
10
connection.connect(error => {11
if (error) throw error;12
const query = 'INSERT INTO `users` (?, ?) VALUES (?, ?)';13
const values = ['name', 'role', 'Matt', 'moderator']; 14
15
connection.query(query, values, (error, result) => { // sends queries16
connection.end(); // closes connection 17
if (error) throw error;18
console.log(connection.sql); // INSERT INTO `users` (name, role) VALUES (Matt, moderator)19
});20
});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: 'Chris', role: 'moderator' },4
{ id: 3, name: 'Kate', role: 'user' }, 5
{ id: 4, name: 'Denis', role: 'moderator' }, 6
{ id: 5, name: 'Matt', role: 'moderator' } 7
]xxxxxxxxxx1
connection.connect(error => {2
if (error) throw error;3
const query = 'INSERT INTO users (name, role) VALUES ?';4
const values = [5
['Matt', 'moderator'],6
['Peter', 'user']7
];8
9
// sends queries10
connection.query(query, [values], (error, result) => {11
if (error) throw error;12
console.log(result);13
});14
});15
16
const mysql = require('mysql');17
18
const connection = mysql.createConnection({ // gets connection with database19
host: 'localhost', // '127.0.0.1'20
user: 'root',21
password: 'password',22
database: 'database_name',23
});24
25
connection.connect((error) => {26
if (error) throw error;27
const query = 'INSERT INTO `users` (name, role) VALUES ?';28
const values = [29
['Matt', 'moderator'],30
['Peter', 'user']31
];32
33
connection.query(query, values, (error, result) => { // sends queries34
connection.end(); // closes connection 35
if (error) throw error;36
console.log(connection.sql); // INSERT INTO `users` (name, role) 37
}); // VALUES ('Matt', 'moderator'),38
}); // ('Peter', 'user'),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');