Here some notes on how I do connect node and mysql
Note: those note are based on the mysql package
More info here: https://www.npmjs.com/package/mysql
Installation: $ npm install mysql
I’m not using mysql2 nor sequelize.
Disclaimer:
This page contains note to how to connect node to mysql, is not meant for production and could have performance and security issues. If you are an expert and notice a flaw please let me know
Connection to the pool
Why pool and not connection?
Database connection pooling is a method used to keep database connections open so they can be reused by others. Typically, opening a database connection is an expensive operation, especially if the database is remote. You have to open up network sessions, authenticate, have authorisation checked, and so on. Pooling keeps the connections active so that, when a connection is later requested, one of the active ones is used in preference to having to create another one.
https://stackoverflow.com/a/4041136/7454921
Following the npm official package page we can set up our connection
require('dotenv').config();
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit : 100,
host : process.env.DB_HOST,
user : process.env.DB_USER,
password : process.env.DB_PASS,
database : process.env.DB_NAME,
multipleStatements: true // More than one query in one row
});
const mysqlData = {
SQL_DATABASE_NAME : 'databasename',
}
module.exports = pool;
module.exports.mysqlData = mysqlData;
I saved my database connection constants in an .ENV file using dotenv
The query function
const query = 'SELECT * FROM users WHERE id = ?';
pool.query(query, [ req.params.id ], (err, results) => {
if ( err ){
console.error('err thrown: ' + err.stack);
//ERROR
}
if (! results[0]){
//NO RES
}
//PRINT results
});Question mark (?) as placeholder
Escape question mark are used as placeholder for MYSQL query in Node to prevent SQL injections
const query = 'SELECT * FROM tablename WHERE id = ?';
pool.query(query, [ req.params.id ], (err, results) => {The ID value is content in req.params.id
You can escape many values using an array [ value1 , value2 , value3 ]
const query = 'SELECT * FROM fjitest.fji2_users WHERE username = ? || email = ? ';
pool.query(query, [ userOrmail, userOrmail ], async (err, results) => {Double question mark (??) can be used to identifiers you’d like to have escaped:
const userId = 1;
const columns = ['username', 'email'];
const query = pool.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function (error, results, fields) {
if (error) throw error;
// ...
});SELECT ?? FROM ?? WHERE id = ? –> SELECT username, email FROM users WHERE id = userId
Mysql query on multiple lines in NodeJs
It is pretty ugly but a query can be set on multiple lines using \
SELECT
const query = 'SELECT * FROM users WHERE id = ?';
pool.query(query, [ req.params.id ] async (err, results) => {
//You can use an object to do neat stuff:
const sku_int = 66 ;
const query = 'SELECT * FROM fjitest.fji_prova WHERE sku_int = ? ';
pool.query(query, [ sku_int ], (err, results) => {
//is the same of this
const sku_int = {sku_int: 66} ;
const query = 'SELECT * FROM fjitest.fji_prova WHERE ? ';
pool.query(query, [ sku_int ], (err, results) => {
INSERT
With the insert statement object can not be used in the insert but they must be transformed in array or arrays of arrays
To insert a single value:
//Object to value vector
const data = {
id: null,
sku_int: 99,
string: 'test stringa'
}
const query = "INSERT INTO tabletest VALUES (? , ? , ?)";
pool.query(query, Object.values(data), (error) => {
//or (same)
const query = "INSERT INTO tabletest VALUES (?)";
pool.query(query, [Object.values(data)], (error) => {The object data become a value vector vector Object.values(data)
{ id: null, sku_int: 99, string: ‘test stringa’ }
[ null, 99, ‘test stringa’ ]
Note: you maybe noticed I set the id as null, even if the id is set as auto-incremental on the database schema it required a value, so id: null, if you prefer to omit the id, you have to declare the fields in the query like this “INSERT INTO tabletest (sku_int,stringa) VALUES (?)“;
To insert multiple values:
To insert multiple values you have just to create array of arrays
/*
Insert 2 new line in the database
Note: the question mark ? not need the brackets () for multiple inserts
*/
let data = [[99, 'hello'], [100, 'hi']];
const query = "INSERT INTO tabletest(sku_int,stringa) VALUES ?";
pool.query(query, [data], (error) => {
////////// you can hide the identifiers using the double question mark ??
let data = [[99, 'hello'], [100, 'hi']];
const fields = ['sku_int','stringa'];
const query = "INSERT INTO tabletest(??) VALUES ?";
pool.query(query, [data], (error) => {
UPDATE
Update data can be add with objects or arrays.
let data = {
sku_int: 999,
stringa: 'test stringa9'
}
let idwhere = 13
//object
const query2 = "UPDATE tabletest SET ? WHERE id = ?";
pool.query(query2, [data, idwhere], (error) => {If no lines are affected you can check results.affectedRows == 0
DELETE
const query = 'DELETE FROM users WHERE user_id = ?';
pool.query(query, [ payload.userid ], (err, results) => { To query statement in one line -> multipleStatements: true
If you need to make more than one query in one statement you can add multipleStatements: true in the connection pool
const query = "INSERT INTO testtable (user,usercontent) VALUES ? ; DELETE FROM fji2_users_meta WHERE content IS NULL || content = ''";
pool.query(query, [ fusi ], (err, results) => {Questions? Suggestions? Please leave a comment below.