Using SQL With JavaScript Part 1 – Installing SQL, Node.js, Creating a Connection and Creating A Database

A little while ago I completed a SQL database course via FutureLearn (it’s a free course if you want to learn some SQL) to brush up on my SQL knowledge. Today I am going to use Javascript (via Node.Js) to connect to SQL and create a database.

Installing SQL

I am using Raspberry Pi OS on a Raspberry Pi, currently my Pi3, as my device for testing / playing on. MariaDB is a fork of MySQL which is intends stay free and open source, and its available on Raspberry Pi OS. To install MariaDB:

sudo apt update
sudo apt upgrade
sudo apt install mariadb-server

Then run the secure set up to create a root user and secure MariaDB:

sudo mysql_secure_installation

Install Node.js

Node.Js was already installed on my Raspberry Pi, however I am unsure if that is because of my other projects. If it’s missing on your device, it can be added via:

sudo apt install nodes

Installing MySQL Node.js Plugin

For Node.js to connect to SQL a plugin is required, which can be installed using the command:

npm install mysql

This uses the Node.js Package Manager (npm) to install the MySQL plugin.

Configuring MariaDB / An Error To Be Aware Of

I’m putting this step here as I originally hit an error when trying to connect to SQL / MariaDB with JavaScript. The error I hit was:

“Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MariaDB client”

After a Google, and some reading on StackOverFlow this looks like it occurs due to how the mysql Node.js plug in handles the authentication method. For full details check out: https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server. The solution I’m about to implement may not be the best for a production environment, so if you are using a production / live environment consider what options are available and make sure it’s safe / secure!

sudo mariadb

SET old_passwords=0;
CREATE geek@localhost IDENTIFIED BY 'geeky';

exit

sudo MariaDB is opening MariaDB so that SQL commands can be set. CREATE geek@localhost is creating a new user called geek, on the local SQL and IDENTIFIED BY geeky is setting the geek users password to geeky.

To see if the user has been created the SQL command:

SELECT User FROM mysql.user;

Can be used to list all the users. Note: You need to be within MariaDB to run this command.

As I am going to use the new user (geek) for actively testing my Javascript and as I’m in a test environment, I will give the new user a lot of privileges:

GRANT ALL PRIVILEGES ON *.* To "geek"@localhost IDENTIFIED BY "geeky";

Again, make sure you consider what privileges you give the user if you in a live / production system. Note: You need to be within MariaDB to run this command.

JavaScript – Connect To SQL

So far we have SQL (MariaDB) and Node.js installed, but we don’t have any Javascript to try connecting. The Javascript needed to create a quick connection is:

var mysql = require('mysql');

    var con = mysql.createConnection({
      host: "localhost",
      user: "geek",
      password: "geeky",
      insecureAuth : true
    });

    con.connect(function(err) {
      if (err) throw err;
      console.log("Connection Succeeded!");
    });

This prints (to console) “Connection Succeeded” if all goes well, and if not it outputs the error. To run the Javascript, save it (e.g. as sql_connect.js) and run it via node.js:

nodejs sql_connect.js

JavaScript – Creating a Database (SQL)

Before proceeding with this step I recommend opening MariaDB and checking what databases already exist using the command:

SHOW DATABASES

The command should return a list of the databases that MariaDB knows about. To add a new database we can get JavaScript to send the relevant query:

var mysql = require('mysql');

    var con = mysql.createConnection({
      host: "localhost",
      user: "geek",
      password: "geeky",
      insecureAuth : true
    });

    con.connect(function(err) {
      if (err) throw err;
      console.log("Connection Succeeded!");
      con.query("CREATE DATABASE geektechstuffDB", function(err, result) {
	if (err) throw err;
	console.log("The database has been created!");
    });
});

Save the Javascript, e.g. as create_db.js and run it via node.js:

nodejs create_db.js

This should create a new database, for me called geektechstuffDB and output the message “The database has been created”. To double check, open MariaDB and use the command:

SHOW DATABASES