MySQL2 Package with Pooling

what we will learn here:

establishing connections

pool options

Contents

Install the package
Create a One-to-One connection
Import it in Application
Create a connection pool for the db
SEQUELIZE

Install the package

npm install mysql2

Create a One-to-One connection

Start by creating a connection to the database.

Use the username and password from your MySQL database.

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword"
});

Testing whether connection was successful or not?

con.connect((err) => {
  if(err){
    console.log('Error connecting to Db');
    return;
  }
  console.log('Connection established');
});

Making a query

con.query('SELECT * FROM authors', (err,rows) => {
  if(err) throw err;

  console.log('Data received from Db:');
  console.log(rows);
});

INSERT Query

const author = { name: 'Craig Buckler', city: 'Exmouth' };
con.query('INSERT INTO authors SET ?', author, (err, res) => {
  if(err) throw err;

  console.log('Last insert ID:', res.insertId);
});

UPDATE Query

con.query(
  'UPDATE authors SET city = ? Where ID = ?',
  ['Leipzig', 3],
  (err, result) => {
    if (err) throw err;

    console.log(`Changed ${result.changedRows} row(s)`);
  }
);

DELETE Query

con.query(
  'DELETE FROM authors WHERE id = ?', [5], (err, result) => {
    if (err) throw err;

    console.log(`Deleted ${result.affectedRows} row(s)`);
  }
);

How to show brought data into normal sentences?

rows.forEach( (row) => {
  console.log(`${row.name} lives in ${row.city}`);
});

Output:

Michaela Lehr lives in Berlin
Michael Wanyoike lives in Nairobi
James Hibbard lives in Munich
Karolina Gawron lives in Wrocław

Import it in Application

const mysql = require('mysql2');

Create a connection pool for the db

const mysql = mysql.createPool()

SEQUELIZE

MYSQL 2 vs SEQUELIZE

Feature	mysql2	Sequelize
Control over SQL	Full control	Limited (high-level ORM)
Performance	Higher (low-level driver)	Slight overhead
Ease of Use	Manual SQL queries	Easier with models & queries
Relationships	Manual joins needed	Built-in associations
Transactions	Manual handling	Built-in
Suitability	Simple apps, high control	Complex apps with relations

SpaceUpper