Using MySQL with Node.js and the mysql JavaScript Client

3D ad banner ad revenue ad revenues adobe XD Advanced advanced-css-tools advanced-css-tutorials AdvancedCSS AdvancedCSS-hub agency Agile Agile Development Ajax Alex Skill Kit Alexa Alexa skill AlexW Amazon Alexa amazon developer analytics Angelap angular AngularJS Animation anime.js ANN api apollo Appsflyer array methods artificial neural network Async async components automation Avocode AWS AWS Lambda axios b2b b2c baas background background eraser tool Banner banner ad Bannersnack bawmedia bem bitcoin black friday Blogs bootstrap bot browser testing Browsers build process bundler Business business success Canvas & SVG career Career Advancement CDN Chakra UI charting chat app chatbots ChatKit chrome chrome developer tools chrome extension chrome extensions chromium ciphering cli cli tools climate climate action clojure cloud hosting CloudFlare Cloudways cms CNN code challenge ColinI compiler components const assertions constructs content content delivery network context api convoluted neural network create react app cron job cross browser testing cross platform development cross-browser cryptocurrencies CSS CSS Architecture css blur css challenge css framework css frameworks css grid css modules CSS print css rem css typography CSS-in-JS CSS3 css3 transforms cssbattle.dev D3.js data visualization database databases debugging decoupled cms deep linking delay Design Design & UX design career design handoff Detox dev environment developer tools DevOps devtools diagnostic analytics digit recognition digital agency digitalocean Discord discounts Docker Docker Containers E-commerce ECMAScript edge computing editor elastic em units employment end-to-end testing energy energy usage Entrepreneur Entrepreneurship environment erasing backgrounds es6 ES6 classes eslint Expo Express FaaS face detection face recognition fast wordpress themes Feathers Feathers.js figma flatfile flexible wordpress theme framework Frameworks freelancing functional programming functions gatsby generators geolocation gitCS github github API Google Photos GraphCMS graphing graphql Growth Grunt grunt-watch Gulp haskell headless CMS hooks hosting HTML HTML & CSS html preprocessor HTML5 HTML5 Dev Center https://www.sitepoint.com/windows-linux-subsystem-2-windows-terminal IAAS IDE Illustration image optimization immutability ionic IP2Location jade jamesh JavaScript javascript framework javascript frameworks javascript tooling Jest job joelf jQuery jquery get url params jquery string functions jquery4u js js framework JS tooling Keras keycdn lambdatest launch checklist Layout learn-advanced-css learn-modernjs Learn-Node-JS learn-vue Lift linux linux containers location tracking LouisL Managed managed hosting mariap marvel MBaaS mekanism mind map mind mapping mindmapping Mobile mobile attribution Mobile Design modernjs modernjs-hub modernjs-tools modules monday mongodb MTCNN multi-threading mysql natural language processing Navigation navigation library nest Next.js nilsonj node node version manager node-hub Node-JS-Tools Node-JS-Tutorials node-mysql module Node.js nodejs noops npm nvm objects octobercms Offers online publishing PaaS Package Management package manager pause Percy Performance Photography & Imagery Photoshop Photoshop Tutorials & Articles pip portfolio portfolio builders preprocessors prettier print styles Prisma Private Class Fields product design Profitability Programming PubNub Pug Puppeteer Pusher python python job Quasar query strings quick-tip quote builders Raw JavaScript React react components React Hooks React Hooks Form React native reactive Reactive Programming reactive web apps read offline Reat native elements Recurring Payments recursive types RedBox redux rem units remote work remote work tips removing backgrounds resilient Resources responsive REST restful api Review SaaS sampling sass Scss self sencha serverless serverless computing shared hosting sharing state sharpen side project siteground sketch slashdata sleep Software sorting specifications speech recognition speech recognition api sponsored state state management static module bundler StudioWorks Styled Components styling React components survey sustainability svelte sympli Tailwind task runner telegram bots Template engine Testing text to speech Themes Tic Tac Toe Tinder toggle Tolerance tooling tooling anxiety tools Tools & Libraries transformations TypeScript Ubuntu UI UI Design Usability user experience user research UX ux issues vanilla javascript VGGFace2 virtual machine visual testing voice recognition vps vue Vue CLI vue dev environment vue-hub vue-tutorials vue.js vuetify vuex wait WatermelonDB Web Web Design web hosting Web Hosting & Domains Web standards web workers webpack website builders website performance WebSockets windows windows 10 home Windows Subsystem for Linux Windows Terminal wix women women in tech WordPress wordpress themes WordPress theming work workers workflow working remotely workplaces WPEngine WSL WSL2 YellowBox Zeplin

Using MySQL with Node.js and the mysql JavaScript Client

NoSQL databases are rather popular among Node developers, with MongoDB (the "M" in the MEAN stack) leading the pack. When starting a new Node project, however, you shouldn't just accept Mongo as the default choice. Rather, the type of database you choose should depend on your project's requirements. If, for example, you need dynamic table creation, or real-time inserts, then a NoSQL solution is the way to go. If your project deals with complex queries and transactions, on the other hand, an SQL database makes much more sense.

In this tutorial, we'll have a look at getting started with the mysql module — a Node.js client for MySQL, written in JavaScript. I'll explain how to use the module to connect to a MySQL database and perform the usual CRUD operations, before looking at stored procedures and escaping user input.

Quick Start: How to Use MySQL in Node

If you've arrived here looking for a quick way to get up and running with MySQL in Node, we've got you covered!

Here's how to use MySQL in Node in five easy steps:

  1. Create a new project: mkdir mysql-test && cd mysql-test.
  2. Create a package.json file: npm init -y.
  3. Install the mysql module: npm install mysql.
  4. Create an app.js file and copy in the snippet below (editing the placeholders as appropriate).
  5. Run the file: node app.js. Observe a “Connected!” message.
const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'database name'
});
connection.connect((err) => {
  if (err) throw err;
  console.log('Connected!');
});

Installing the mysql Module

Now let's take a closer look at each of those steps.

mkdir mysql-test
cd mysql-test
npm init -y
npm install mysql

First of all we're using the command line to create a new directory and navigate to it. Then we're creating a package.json file using the command npm init -y. The -y flag means that npm will use defaults without going through an interactive process.

This step also assumes that you have Node and npm installed on your system. If this is not the case, then check out this SitePoint article to find out how to do that: Install Multiple Versions of Node.js using nvm.

After that, we're installing the mysql module from npm and saving it as a project dependency. Project dependencies (as opposed to devDependencies) are those packages required for the application to run. You can read more about the differences between the two here.

If you need further help using npm, then be sure to check out this guide, or ask in our forums.

Getting Started

Before we get on to connecting to a database, it's important that you have MySQL installed and configured on your machine. If this is not the case, please consult the installation instructions on their home page.

The next thing we need to do is to create a database and a database table to work with. You can do this using a
graphical interface, such as Adminer, or using the command line. For this article I'll be using a database called sitepoint and a table called authors. Here's a dump of the database, so that you can get up and running quickly if you wish to follow along:

CREATE DATABASE sitepoint CHARACTER SET utf8 COLLATE utf8_general_ci;
USE sitepoint;

CREATE TABLE authors (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50),
  city varchar(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO authors (id, name, city) VALUES
(1, 'Michaela Lehr', 'Berlin'),
(2, 'Michael Wanyoike', 'Nairobi'),
(3, 'James Hibbard', 'Munich'),
(4, 'Karolina Gawron', 'Wrocław');

Using MySQL with Node.js & the mysql JavaScript Client

Connecting to the Database

Now, let's create a file called app.js in our mysql-test directory and see how to connect to MySQL from Node.js.

const mysql = require('mysql');

// First you need to create a connection to the database
// Be sure to replace 'user' and 'password' with the correct values
const con = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
});

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

con.end((err) => {
  // The connection is terminated gracefully
  // Ensures all remaining queries are executed
  // Then sends a quit packet to the MySQL server.
});

Now open up a terminal and enter node app.js. Once the connection is successfully established you should be able to see the “Connection established” message in the console. If something goes wrong (for example, you enter the wrong password), a callback is fired, which is passed an instance of the JavaScript Error object (err). Try logging this to the console to see what additional useful information it contains.

Using nodemon to Watch the Files for Changes

Running node app.js by hand every time we make a change to our code is going to get a bit tedious, so let's automate that. This part isn't necessary to follow along with the rest of the tutorial, but will certainly save you some keystrokes.

Let's start off by installing a the nodemon package. This is a tool that automatically restarts a Node application when file changes in a directory are detected:

npm install --save-dev nodemon

Now run ./node_modules/.bin/nodemon app.js and make a change to app.js. nodemon should detect the change and restart the app.

Note: we're running nodemon straight from the node_modules folder. You could also install it globally, or create an npm script to kick it off.

Executing Queries

Reading

Now that you know how to establish a connection to a MySQL database from Node.js, let's see how to execute SQL queries. We'll start by specifying the database name (sitepoint) in the createConnection command:

const con = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'sitepoint'
});

Once the connection is established, we'll use the con variable to execute a query against the database table authors:

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

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

When you run app.js (either using nodemon or by typing node app.js into your terminal), you should be able to see the data returned from the database logged to the terminal:

[ RowDataPacket { id: 1, name: 'Michaela Lehr', city: 'Berlin' },
  RowDataPacket { id: 2, name: 'Michael Wanyoike', city: 'Nairobi' },
  RowDataPacket { id: 3, name: 'James Hibbard', city: 'Munich' },
  RowDataPacket { id: 4, name: 'Karolina Gawron', city: 'Wrocław' } ]

Data returned from the MySQL database can be parsed by simply looping over the rows object.

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

This gives you the following:

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

The post Using MySQL with Node.js and the mysql JavaScript Client appeared first on SitePoint.

Original Article

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.