Oracle recently announced that the MySQL database server now supports JavaScript functions and procedures. JavaScript for stored routines is currently in preview and only available in the MySQL Enterprise Edition and MySQL Heatwave.
The introduction of JavaScript support enables developers to implement advanced data processing logic within the database. By minimizing data movement between the database server and client applications, stored functions and procedures can reduce latencies, network overhead, and egress costs. Øystein Grøvlen, senior principal software engineer at Oracle, and Farhan Tauheed, consulting member of technical staff, write:
The support for JavaScript stored programs, will not only improve developer productivity by leveraging the large ecosystem; more developers will now possess the necessary skills to write stored programs. In other words, organizations may now tap into a broader range of developer talent by utilizing the widely accessible JavaScript skill set for backend development.
Among the common use cases for the new feature, Oracle highlights data extraction, data formatting, approximate search, data validation, compression, encoding, and data transformation. Favorably received by the community, the announcement provides an example of a function where the JavaScript code is embedded directly in the SQL definition:
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;
$$;
Source: Oracle blog
When the function is invoked using the traditional CALL statement, an implicit type conversion occurs between SQL types and JavaScript types. As per the documentation, JavaScript support is based on the ECMAScript 2021 standard, and all variations of integers, floating point, and CHAR/VARCHAR types are supported. Grøvlen and Tauheed add:
MySQL-JavaScript integration uses a custom-built VM for its specific use case to allow the best end-to-end performance. This customization is based on GraalVM’s ahead-of-time (AOT) compilation where the language implementation is compiled down into a native binary representation for fast processing. GraalVM has its own JavaScript implementation based on the ECMAScript 2021 standard. The language implementation is competitive in terms of performance although it is implemented using GraalVM’s Polyglot framework.
The GraalVM run time includes JDK, language implementations (JavaScript, R, Python, Ruby, and Java), and a managed virtual machine with sandboxing capability and tooling support. While MySQL-JavaScript is available in the MySQL Enterprise Edition and MySQL Heatwave cloud service on OCI, AWS, and Azure, there is no support in the MySQL Community Edition.
MySQL is not the first open-source relational database supporting Javascript in stored routines, with PLV8 being the most popular Javascript language extension for PostgreSQL. PLV8 is supported by all current releases of PostgreSQL, including managed services like Amazon RDS, and can be used for stored routines and triggers.
Oracle has released three MySQL HeatWave videos on YouTube to demonstrate how to run the Mustache library, validate web form inputs, or process web URLs using stored programs in JavaScript.