Setting Up JavaScript MySQL Integration: 3 Easy Steps

Almost all modern websites are interactive and dynamic that respond according to the data and user inputs. JavaScript is used to manipulate the content of web pages using programming. MySQL is one of the most used Relational Databases by companies to run their daily business operations and maintain data. This is where JavaScript MySQL integration comes into play.

Connecting your website to a MySQL database solves the data availability problem because of its robustness and reliability. JavaScript MySQL database connection allows developers to connect MySQL Database to websites and develop fully-fledged websites according to their business requirements. It will make the websites and applications user interactive, allow companies user data through JavaScript MySQL connection, and manipulate data.

In this article, you will learn the steps to set up MySQL JavaScript Integration.

What is GraalVM?

GraalVM is an Oracle compiler ecosystem that comprises JDK and language implementations for JavaScript, R, Python, Ruby, and Java. It incorporates just-in-time (JIT) and ahead-of-time (AOT) compilation technologies. It also includes a fully managed virtual machine that supports sandboxing and tools. MySQL-JavaScript is included in the GraalVM Enterprise edition.

Defining JavaScript Stored Programs

To build a JavaScript stored programme in MySQL, you can use a variation of the SQL statements that you use for regular, stored functions and procedures:

CREATE FUNCTION gcd_js (a INT, b INT) RETURNS INT
LANGUAGE JAVASCRIPT AS $$

  let [x, y] = [Math.abs(a), Math.abs(b)];
  while(y) [x, y] = [y, x % y];
  return x;

$$;

As seen in the example above, the JavaScript code is immediately included in the description of a SQL-callable function. The names of the arguments can be referred to directly in the JavaScript code, and when the function is invoked, an implicit type conversion occurs between SQL and JavaScript types. To call JavaScript stored procedures, use the CALL statement, just as you would for conventional SQL stored procedures.  Stored procedures can accept both input and output parameters.

Executing JavaScript code inside SQL statements

A JavaScript function can be called from SQL statements in the same way as a typical SQL function can be called, including SELECT expressions, GROUP By, ORDER BY, and WHERE clauses, Views, DMLs, DDLs, and others. Here’s an example of an SQL statement that uses the function we defined earlier:

SELECT col1, col2, gcd_js(col1,col2)
FROM my_table
WHERE gcd_js(col1, col2) > 1
ORDER BY gcd_js(col1, col2);

CREATE TABLE gcd_table
AS SELECT gcd_js(col1,col2)
FROM my_table;

Debugging JavaScript code inside MySQL

Debugging is an integral part of software development. The MySQL-JavaScript functionality provides additional SQL interfaces for debugging while the JavaScript program is running in the database.

CREATE PROCEDURE division (IN a INT, IN b INT,
OUT result DOUBLE) LANGUAGE JAVASCRIPT AS $$
  function validate(num) {
    console.log("validating input value: ", num);
    if (num === 0) throw ("Division by Zero!");
  }
  validate(b);
  result = a / b;
$$ 

JavaScript exceptions to MySQL errors are converted transparently. Developers can view JavaScript stack traces in addition to standard output.

CALL division( 5, 0, @res);
ERROR 6000 (HY000): JavaScript> Division by Zero!

SELECT mle_session_state("stdout");
validating input value:  0

SELECT mle_session_state("stack_trace");
<js> validate(division:9:187-214)
<js> division(division:11:222-232)
<js> :anonymous(division:15:256-265)
</js></js></js>

Steps to Set Up JavaScript MySQL Integration 

In this section, you will learn about connecting JavaScript MySQL so that you can use it to create websites and load/ unload data to the MySQL Database. JavaScript is a client-side language that runs in the browser and MySQL is a server-side technology that runs on the server. You must use the server-side language Node.js to connect to the MySQL Database. 

Node.js is an open-source server-side JavaScript runtime environment and provides cross-platform application development. In the modern web technology stack, Node.js is widely used as the alternative to JavaScript because it offers all the JavaScript features as it is written in JavaScript and C++. So when it comes to building websites and connecting them to server-side applications and services, Node.js is the easy and reliable option, as well as connecting to the JavaScript MySQL database.

In this tutorial, Node.js (JavaScript runtime environment) will be used to connect to MySQL Database. The steps to implement JavaScript Connect to MySQL are listed below:

• Step 1: Installing MySQL Module
• Step 2: Creating a Sample MySQL Database
• Step 3: Connecting to the Database

Step 1: Installing MySQL Module

• First, open your terminal or command prompt.
• Create a new directory with a name of your choice and move to that directory by following the commands given below.

mkdir js-mysql
cd js-mysql

• Now, create a “package.json” file with the following command given below.

npm init -y

• Once it gets started, you can install the “mysql” library with the command given below.

npm install mysql

Step 2: Creating a Sample MySQL Database

• For this JavaScript MySQL Integration, a sample Database with dummy information will be created. You can use your own MySQL Database or create a new one with the command given below.

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

CREATE TABLE workers(
  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, 'Pam Shel', 'New York'),
(2, 'Maurene Oswald', 'Dubai'),
(3, 'Cori Vergil', 'Berlin'),
(4, 'Misty Thomas', 'Moscow');

Step 3: Connecting to the Database

• Create a new file named “app.js” in the current directory.
• Now, open the file and enter the code given below to connect JavaScript MySQL Database.

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: '1234567890',
});

connection.connect((error) => {
  if(error){
    console.log('Error connecting to the MySQL Database');
    return;
  }
  console.log('Connection established sucessfully');
});
connection.end((error) => {
});

• After, save the file and type the command in the terminal as given below.

node app.js

• This will start the Node.js server and initiate the JavaScript MySQL Database connection.
• Once the connection is established, the JavaScript MySQL Integration is successful, and it will show the message “Connection established successfully.”

That’s it! You have completed JavaScript MySQL Integration. Now, you can use this MySQL Database to develop websites with JavaScript.

Query a Database

To access or write to a MySQL database, use SQL statements. This is also known as “to query” the database.

The connection object built in the example above includes a method for accessing the database:

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Result: " + result);
  });
});

This query method accepts SQL statements as parameters and returns the result.

Terminating connections

You can terminate a connection using two methods. To accurately terminate a connection, call the end() method as shown below:

connection.end(function(err) {
  // The connection is terminated now
});

This ensures that all earlier enqueued queries are still active before delivering a COM_QUIT message to the MySQL server. If an unexpected error occurs when the COM_QUIT packet is transmitted, the callback will get an error parameter, but the connection will still be terminated.

The destruct() method is an alternate technique for terminating the connection. This will immediately terminate the underlying socket. Additionally, destroy() ensures that no further events or callbacks are triggered for the connection.

connection.destroy();

Unlike end(), the destroy() does not accept a callback argument.

Pooling connections

Rather than generating and managing connections individually, this module includes built-in connection pooling with MySQL.createPool(config). You can directly create and use a pool:

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
}); 

pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

This is a shortcut to the pool.Code flow: getConnection(), connection.query(), and connection.release(). Using the pool.getConnection() is handy for sharing the connection state between successive queries. This is due to two calls to the pool.query() may use two separate connections and execute in parallel. This is the basic framework.

var mysql = require('mysql');
var pool  = mysql.createPool(...);
pool.getConnection(function(err, connection) {
  if (err) throw err; // not connected! 

  // Use the connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // When done with the connection, release it.
    connection.release();
    // Handle error after the release.
    if (error) throw error; 

    // Don't use the connection here, it has been returned to the pool.
  });
});

If you want to close a connection and discard it from the pool, call the connection.destroy() instead. The pool will establish a new connection whenever required.

The pool creates connections gradually. If you set the pool to enable up to 100 connections but only utilize five at a time, only five connections will be established. Connections are likewise cycled round-robin, starting from the very top of the pool and ending at the bottom.

When a prior connection is recovered from the pool, a ping packet is transmitted to the server to ensure that the connection is still active.

Reasons to connect your MySQL Database with JavaScript.

The key reasons are simplicity and efficiency.

1. User-Friendly Interface: JavaScript allows you to build dynamic and interactive web apps. Connecting JavaScript to a MySQL database allows your software to handle data seamlessly.
2. Real-Time changes: By linking JavaScript and MySQL, you may offer users with real-time changes and information without requiring page refreshes.
3. Data Accessibility: Integrating JavaScript with MySQL enables easy accessibility to your data, improving the user experience.
4. Enhanced Performance: It allows you to create quick and responsive web apps that handle and show data from a MySQL database.
5. Versatility: JavaScript is versatile enough to be used for both client-side and server-side scripting, making it an important tool for web development.
6. Data extraction: You can extract the desired information from complex objects that are commonly used in databases. One such example is URL strings.
7. Data formatting: It allows you to generate formatted strings using widely used templatization schemes like the JavaScript Mustache package.
8. Approximate search: You can use similarity scoring functions in SELECT queries.  Retrieving similar strings from a table is a JavaScript MySQL database connection example that you can use.
9. Data Validation: It helps you to clean data using complex validation rules. For example, using the JavaScript Validator package.
10. Compression / Encoding: By Javascript MySQL integration, you can use custom algorithms not included in MySQL for data compression and encryption.
11. Data Transformation: It allows you to change data representation, such as converting columns of strings into sparse-matrix representation used in feature engineering.

Integrating MySQL to JavaScript simplifies development and opens up new opportunities for constructing feature-rich online apps.

Here are some other MySQL integration articles you might be interested in:

• Node.js MySQL integration
• Firebase MySQL integration

Conclusion

In this article, you learn about the steps to set up JavaScript MySQL Integration. You also read about the need to connect a Database to a website using JavaScript MySQL and how it helps companies run business operations and satisfy their data needs. Using Node.js for JavaScript MySQL Database is one of the easiest and straightforward ways to connect a website or application to a MySQL Database.

Visit our Website to Explore Hevo

MySQL Server stores valuable business data that can be used to generate insights. Companies need to analyze their business data stored in multiple data sources. The data needs to be loaded to the Data Warehouse to get a holistic view of the data. This is where Hevo steps in.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. Check out Hevo pricing to choose the best plan for you.

Share your learning experience about JavaScript MySQL Integration in the comments section below!