A Quick Introduction to JavaScript Stored Programs in MySQL

Recently MySQL added the ability to write Stored Programs in JavaScript.  This allows developers to leverage JavaScript capabilities for complex data processing and business logic within the database server. We think this is really cool. Here’s why.

Why JavaScript?

It is ubiquitous

• One of the most used languages by developers
•  > 98% of all web pages use JavaScript

It has multiple runtimes

• Support in all major web browsers
• Massively used server-side runtimes
• Node.js 
• Deno

It has a huge development eco-system

• Npm contains > 2 million free to use JavaScript packages***
•  > 10 million users use the npm package manager

What’s great about JavaScript Stored Programs?  

Using JavaScript within the database server is a great fit for data-intensive use cases

• Data Validation
• JSON & String processing / Formatting
• Data Cleansing / Transformation

By processing directly within the server developers can minimize data movement between server and clients, improving performance and reducing network overhead, reducing latency within applications, and improving data security.

Using JavaScript SQL Stored Programs 

• Improves Developer Experience 
• Adds security – by limiting access via Stored Programs versus direct table access
• Improves performance – leveraging State-of-the-art optimizations provided by GraalVM
• Works within the OCI Heatwave – MySQL DBaaS Service and in MySQL on-premise

Top 5 Benefits 

JavaScript Stored Programs enable you to  – 

Comparing JavaScript Stored Programs over Tradition SQL Stored Procedures

Defining JavaScript stored programs

The syntax is simple and straight forward

• The LANGUAGE clause now allows JavaScript
• String quoting mechanism to enclose non-SQL language
  • AS$$…$$
  • AS $JavaScript$ … $JavaScript$

For example

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;
$$

And a stored program can be called from within your SQL statements:

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

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

Or SQL statements can be called from within your stored program:

CREATE PROCEDURE gen_random_age (IN row_count INT) 
LANGUAGE JAVASCRIPT AS $$ 
    let insertStatement = session.prepare( "INSERT INTO my_table(age) VALUES ( ? )"); 
    for (let j = 0; j < row_count; j++) {
        let random_age = Math.trunc(Math.random() * 100); 
        insertStatement.bind(random_age).execute(); 
    } 
$$ 


CREATE PROCEDURE average_age (OUT avg_age FLOAT) LANGUAGE JAVASCRIPT AS $$ 
    let age_sum = 0, count = 0; 
    let selectStatement = session.sql( 
      "SELECT age FROM my_table");&#xb;    let result = selectStatement.execute(), row = null; 
    while(row = result.fetchOne()) { 
        age_sum += row[0]; count++; 
} 
avg_age = age_sum / count; 

$$ 

Creating JavaScript Stored Programs provides a great number of benefits to developers and DBAs. 

Express complex logic in database using JavaScript

• Push data-intensive application logic inside the database
• Reduce data movement cost
• Includes GraalVM Enterprise Edition optimizations at no additional cost 
• Integrates with MySQL HeatWave

To learn more

• User Manual (JavaScript Stored-Programs Section 25.3) 
• Introducing JavaScript support in MySQL
• JavaScript for HeatWave

Videos

• JavaScript Stored Programs in MySQL Enterprise Edition 
• URL processing
• Validate web form inputs
• Convert JSON dataset to HTML using Mustache libra

Download

• Download from the Oracle Technology Network (OTN)

As always, thank you for using MySQL!