4 Mysql2

Connecting to MySQL Sebin Benjamin
MySQL2 client for Node.js

What we’ll learn today
● MySQL clients
● mysql2 examples
● Prepared Statements
● Benefits of using Prepared statements
● MySQL connection pooling

MySQL Clients
sidorares/nodejs-mysql-native
Discontinued native mysql async client for node.js
sidorares/node-mysql2 - https://github.com/sidorares/node-mysql2
Based on nodejs-mysql-native. Offers additional feature like prepared
statements.
mysqljs/mysql
Another popular node.js driver for mysql, with limited features.
mysql2
• We can use the mysql2 module to do connect to the MySQL DBMS
programmatically.
• We have already used MySQL workbench which helps us connect and
interreact with the database using a GUI.
• mysql2 module allows us to submit a query to our MySQL server and
get a response back with the information we need.
• Before we can do that, we need to provide setup the mysql2 module with
our login information for our server, which is best stored within a .env file
Using the node-mysql2 module
• Setup a simple express
const express = require('express');
server in a new folder. const mysql = require('mysql2');
• npm init –y
const dotenv = require('dotenv').config();
• npm install dotenv
express const app = express();
app.get('/', (req, res) => {
• Install the mysql2 module res.send('hello world');
});
• npm install mysql2
const PORT = process.env.PORT;
app.listen(PORT);
Using the mysql2 module continued…
const express = require('express');
• After importing our mysql2 module, we const dotenv = require('dotenv').config();
const mysql = require('mysql2')
can use the mysql.createConnection()
method to provide our login info const app = express();
• This method takes an object with 4 const connection = mysql.createConnection({

host: process.env.MYSQL_HOST,
properties: user: process.env.MYSQL_USER,
password: process.env.MYSQL_PASS,
• host: usually ‘localhost’ if using the local database: process.env.MYSQL_DATABASE
mysql server })
• user: your username for the server app.get('/', (req, res) => {
• password: the password for the server res.send('hello world')
})
• database: the name of your
app.listen(4000)
database/schema
Using the mysql module continued…
• If our connection is successful, within our ‘/’ endpoint, we can submit
a query that will get a list of the users in our database.
• We can then send the list as a response to the request from the HTTP
Client (Postman, Browser)
app.get('/', (req, res) => {

connection.query('SELECT * FROM users', (err, result) => {
res.send(result);
})
})
Exercise 1
• Create an endpoint at localhost:4000/city to get a list of cities that
start with ‘A’.
Exercise 2
• Create an endpoint at localhost:4000/country/:code that shows all
the information about a specific country.
• Example,
• localhost:4000/country/:BEL should give the information about the
country with a country code BEL
Exercise 3
• Create an endpoint at localhost:4000/country that returns a list of
the top 10 countries with the most Surface Area.
• Bonus: Add a query param to set a custom limit on the no of countries
returned. Eg, localhost:4000/country/area?limit=20 returns 20 countries.
Quick Recap
• We are going to be going over some more MySQL clauses and
keywords:
• INSERT (add a record to a table)
• UPDATE (change a record in a table)
• DELETE (remove a record from a table)
INSERT
• Let’s look at how we can insert a record into a table.
• We start with the INSERT keyword followed by INTO, then the name
of the table
INSERT into users
• Next, we have two options

• 1. INSERT into users SET email = '[email protected]', password = 'pass'
• 2. INSERT into users (email, password) VALUES ('[email protected]', 'pass')
UPDATE
• To update a record in a table we can use the UPDATE keyword
followed by the name of the table
UPDATE users
• Then we can define which columns to update and which records we

want updated with the WHERE clause
UPDATE users SET password = 'password' WHERE email = [email protected]
• If a WHERE clause is not included, it will update every record in the

table so we need to make sure to always include a WHERE clause
DELETE
• To DELETE a record from a table we use the DELETE keyword followed
by FROM and then the name of the table
DELETE FROM users
• We can then specify which record to delete with a WHERE clause

DELETE FROM users WHERE id = 107
• If a WHERE clause is not provided it will delete every record in the

table
Exercise 3
• Create two endpoints
• localhost:4000/addUser (post)
• localhost:4000/deleteUser/:userID (delete)
• The addUser endpoint needs to accept a post request that contains
an object with the email and password properties of the user we are
trying to add.
• Note: we will need to install cors and add the cors middleware as well
as the express.json() middleware before the request can go through
SQL injections
• A SQL Injection is a web security vulnerability that allows an attacker
to interfere with an applications queries to a database.
• To run a SQL injection attack, you can find a textbox on a website that
allows you to type free text. From there we can start typing some
SQL code into the text box which will get sent to the database as a
query.
• Let’s take a look at an example.

How to protect your SQL Queries
• Now that we know about SQL injections and how bad they can be.
How do we protect against them?
• One way to protect against a SQL injection is to remove all text boxes
and only allow a user to choose from a set of pre-determined
options.
• That’s not always Ideal though, sometimes you need a user to type in some
information e.g. a username or password. What can we do then?
• We can also use a parameterized query. This is done with the “?”
operator which can then be replaced with a variable. E.g.
db.query("SELECT * from users WHERE email = ? AND password = ?", [req.body.email, req.body.password])
Prepared Statements
● Prepared statements, also known as parameterized statements or
parameterized SQL, are like templates for SQL statements.
● Also helps to execute the same statement repeatedly with high efficiency.
● Values can be plugged into the query after the query is “prepared”, and
ready to be executed.
● Prepared statements use question marks (?), which are positional
placeholders for where actual values that will be used in the SQL should
be “plugged” in.
The prepared statement execution consists of two stages:
1. Prepare - A statement template is sent to the database server.
2. Execute - Server performs a syntax check and initializes server
internal resources for later use.

Benefits of using Prepared statements
Better performance
● It is compiled and optimized only once by the database engine.
● Does not have to be compiled and optimized each and every time the
values in the query change.
Preventing SQL Injection
● One of the best solution to preventing SQL injection attack.
● Values that will be inserted into a SQL query are sent to the SQL server
after the actual query is sent to the server.
connection.execute(
'SELECT * FROM ùsers` WHERE `name` = ? AND àge` > ?',
['John Doe', 23],
(err, results, fields) => {
// results contains rows returned by server
console.log(results);
// fields contains extra meta data about results, if
available
console.log(fields);
}
);
connection.unprepare('SELECT * FROM ùsers` WHERE `name` = ?
AND àge` > ?');
Execute (Prepare + Query) | UnPrepare
Execute helper method executes statements on the DB.

● Preparation - Prepares and caches the query in a binary format on
the server.
● Execution - Executes a query on the server, and returns the result
set.
● UnPrepare - Removes the prepared statements from the cache.
https://xkcd.com/327/
Quick Recap
// simple query
connection.query(
'SELECT * FROM `table` WHERE `name` = "Page" AND àge` > 45',
(err, results, fields) => {
// results contains rows, fields contains extra meta data about results
console.log(fields, results);
}
);
// with placeholder
connection.query(
'SELECT * FROM `table` WHERE `name` = ? AND àge` > ?',
['Page', 45],
function(err, results) {
console.log(results);
}
);
MySQL Connection Pools
● Connection pools help reduce the time spent connecting to the
MySQL server by reusing a previous connection.
● Connections are left open instead of being closed when you are
done with them.
● Improves the latency of queries by avoiding the overheads in
establishing a new connection every time.
// Simple single connection to
database
const connection =
mysql.createConnection({
host: 'localhost',
user: 'root',
database: 'test'
});
// Creates a connection pool to the database
const pool = mysql.createPool({

host: 'localhost',
user: 'root',
database: 'test',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
www.missionreadyhq.com
Thank you Sebin Benjamin

[email protected]

4 Mysql2

Uploaded by

Copyright:

Available Formats

4 Mysql2

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

4 Mysql2

Uploaded by

Copyright:

Available Formats

Connecting to MySQL Sebin Benjamin

MySQL2 client for Node.js

● MySQL connection pooling

• This method takes an object with 4 const connection = mysql.createConnection({

app.get('/', (req, res) => {

• Next, we have two options

• Then we can define which columns to update and which records we

• If a WHERE clause is not included, it will update every record in the

• We can then specify which record to delete with a WHERE clause

• If a WHERE clause is not provided it will delete every record in the

• Let’s take a look at an example.

2. Execute - Server performs a syntax check and initializes server

internal resources for later use.

Execute helper method executes statements on the DB.

const pool = mysql.createPool({

Thank you Sebin Benjamin

You might also like