[Nodejs] Fast Setup MySQL and running in Node.js

First thing first, what we need if we need to build a modern APPs, we need a front-end platform(or web) which is used by the users. But the users would like to communicate to the other users and would like to exchange content/information with them, we need a place to store the information and the path of reaching those valuable content, THE DATABASE.

This is a Note of configurating MySQL Database and the purpose is remotely connect and query in Node.js server**.**

Content:

1. Set up MySQL on VM
2. Set up GUI tool (Sequel)
3. Create Admin account
4. Connecting in Node.js

1- Set up MySQL environment

First we need a machine running MySQL, could be Macbook, Windows or Linux, because there is much enough tutorial on Google to install MySQL on all those operation system, and you will enjoy to discover the fun of installation.

My way of setting up a MySQL server, run a Docker Machine and run a Docker image by the command :

docker run --name mysql -e MYSQL_ROOT_PASSWORD=mysqlpw -p 3306:3306 -d mysql

What happened is a root user is created with “mysqlpw” as password, and a external port is opened so that we are able to remotely connect by MySQL client tool.

2- Set up Sequel, MySQL GUI tool (optional)

Install MySQL client GUI tool, Sequel, which help you understanding how DATABASE-TABLE-ROW works in visualised way, and there are two functions that are very helpful to me:

**_2. Console_**The fact is any operation between client and MySQL is communicated through TCP protocol, the Sequel is just a tool to TRANSLATE UI actions to the command that MySQL understand.Console, on the right top, provide every logs that SQL command translated by Sequel.

**_2. Query_**What it can do is as same as terminal MySQL client. For me it is a nicer editor, speeds up copy paste work and mouse operations (what the terminal can’t do). A good editor is so important for programmer to become productive, that is why there is so many editors: Sublime, VSCode, Atom etc.

3- Create an Admin account, and stop using Root user

Creating Admin Account by SQL command

4- Start coding in Node.js

Install the npm module mysql

$ npm install mysql

Create a file called connect.js, and copy paste the code below to it:

var mysql = require("mysql");

// First you need to create a connection to the dbvar con = mysql.createConnection({host: "12.34.56.78",user: "peter",password: "1234",database: "southwind"});

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

con.end(function(err) {// The connection is terminated gracefully// Ensures all previously enqueued queries are still// before sending a COM_QUIT packet to the MySQL server.if(err) console.log('err: ', err);else console.log('Terminated done: ');});

Run :

$ node connect.js

Connection establishedterminated done:

Summary

To me, I spend the most of my time on the 2nd part. Setting up an MySQL Docker-Machine is easy by just one command. Node.js connection and query is super smooth as well. However, understanding the SQL command(query) in SEQUEL is not very straightforward, it takes me some time to pick up existing commands from MongoDB and “translate” to something I can understand.

You may also like

• MySQL Handbook
• [MySQL] Note: Fast Setup and running in Node.js
• [MySQL] Note: Create Admin User
• [MySQL] Note: Database CURD
• [MySQL] Note: Row CURD
• [MySQL] Note: Relation: 1–1, 1-n, n-n, nest

Reference:

https://www.sitepoint.com/using-node-mysql-javascript-client/