JavaScript functions stored procedures previewed in MySQL – but developers are wary

JavaScript functions stored procedures previewed in MySQL – but developers are wary

Oracle has previewed JavaScript stored procedures in MySQL 8.2, although currently only in the Enterprise edition or the MySQL Heatwave cloud service on AWS, Azure or OCI (Oracle Cloud Infrastructure).

JavaScript stored procedures are executed by the GraalVM runtime, which supports JavaScript as well as Java.

JavaScript is the most popular programming language in terms of the number of developers who use it at least some of the time, yet many developers are wary of mixing JavaScript with SQL in the database engine. “As someone who still works on java stored procedures, just please don’t,” said one early comment. Mixing languages can cause confusion, as well as introducing issues like how types are converted from JavaScript (a dynamic language) to SQL, for example when returned from a function. There is also a longstanding debate about how much programming logic belongs in the database as opposed to in application code. Running code in stored procedures is good for performance as it is runs as close as possible to the data, but such code is less visible and may be harder to troubleshoot and maintain than when application code is kept separate from the database engine.

According to Oracle’s post, JavaScript stored procedures and functions enable a number of scenarios including data formatting using a template system such as Mustache, complex validation, data compression and encoding, and data transformation. 

JavaScript functions and procedures are created using LANGUAGE JAVASCRIPT in the CREATE FUNCTION or CREATE PROCEDURE statement. Once created, they can be called from SQL statements anywhere they can be used, including triggers (code that executes automatically before or after inserts, deletes and updates). JavaScript exceptions are converted to MySQL errors. Performance is enhanced by GraalVM’s AOT (ahead of time) compilation, and syntax errors will cause the CREATE statement to fail.

The documentation for the feature describes it as a technology preview with the warning “do not use these binaries in production.” It further explains that the JavaScript support conforms to the ECMAScript 2021 specification. Debugging is possible using the Multilingual Engine Component (MLE), which is required for JavaScript support, including access to a stack trace in the event of an error.

There are also warnings about use of global variables. “The Global object and the globalThis object property are supported, but their scope is limited to that of the current user session. It is possible but not advisable for multiple stored programs (or multiple instances of the same program) executed in the same session to share these objects, due to the fact that their behavior in such cases is not deterministic,” states the documentation; an example perhaps of why extra care is needed when using a familiar language in a different context.