TL;DR
Percona Server for MySQL now offers experimental support for stored programs in the JS language. This free and open source alternative to Oracle’s Enterprise/Cloud-only feature enables users to write stored programs in a more modern, convenient, and often more familiar language. It is still in active development, and we would very much like your feedback about it! Binary packages are available in Percona’s experimental repositories, and source code is on GitHub.
Why we’re adding JS stored program support
Last year, Oracle introduced support for stored JavaScript (JS) programs in MySQL Server (it has been available since the 9.0 Innovation Release of MySQL Server, released in Summer 2024). This feature allows users to write stored programs in a more modern, convenient, and likely more familiar language than the default MySQL Stored Routine language available since version 5.0. In the case of CPU-bound code, programs in JavaScript should also deliver better performance.
Unfortunately, there is one big issue with Oracle’s new feature. It is not available in the free and open source MySQL Community version but only as part of the MySQL Enterprise product or the Oracle Cloud offering, which means that it can’t be used for free in production, for example.
Percona is committed to open source, so we decided it is a good idea to provide a free and open source alternative to the upstream’s Stored Programs in JavaScript. Hence, we started work on support for Stored Programs in JS in Percona Server for MySQL.
Similar to upstream, Percona Server for MySQL implements support for Stored Programs written in JS as a loadable component. However, unlike the upstream implementation, which uses the GraalVM engine for execution of JavaScript code, we decided to use the V8 engine.
Also, unlike upstream, we are targeting the 8.4 series of Percona Server for MySQL (i.e., LTS series).
At this point, this feature in Percona Server for MySQL is probably not ready for prime time yet, but we think it has reached the stage at which it is worth taking a look and experimenting with! So we would really love to hear your feedback!
Where to get it: Binaries and source
We made binary packages of Percona Server for MySQL with this feature enabled, which are available in Percona’s experimental repositories. See https://docs.percona.com/percona-software-repositories/index.html for more information about accessing them.
Specifically, you need to enable the “experimental” component of “ps-84-lts” repository using percona-release tool to be able to install server packages with JS stored programs support.
|
1 |
$ sudo percona-release enable ps-84-lts experimental |
After that, you can install Percona Server for MySQL version 8.4.5-5 package using your standard package manager.
The source code for this feature is also available in a separate branch on GitHub (https://github.com/percona/percona-server/tree/js-lang). One can use them to build Percona Server for MySQL with JS Stored Programs support as well. See https://github.com/percona/percona-server/blob/js-lang/components/js_lang/README.md for details. The most complex part, in this case, is building the correct version of the V8 engine with exact parameters compatible with our code.
Step-by-step: Getting started with JS stored programs
The first thing you need to do after installing and starting up the version of Percona Server for MySQL with JS support (e.g., from experimental repository) is to install the JS language component using:
|
1 |
INSTALL COMPONENT 'file://component_js_lang'; |
You also need to grant a new global dynamic CREATE_JS_ROUTINE privilege after that to users who will be creating JS Stored Programs (in addition to the standard CREATE ROUTINE privilege). For example:
|
1 |
GRANT CREATE_JS_ROUTINE ON *.* TO root@localhost; |
After that, you will be able to connect as one of those users and create stored programs in JS language using the CREATE PROCEDURE or CREATE FUNCTION statement with the LANGUAGE JS clause. For example:
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION fact(n INT) RETURNS INT LANGUAGE JS AS $$ let result = 1; while (n > 1) { result *= n; n--; } return result; $$; |
Note the $$ which delimit the function body. They are necessary because we use the ; character, which normally delimits SQL statements as a delimiter of statements in JS code. Recent versions of ‘mysql’ client were updated to handle such $$ quoting nicely without requiring extra steps from users. However, older client versions might also require assigning some other character than ; as a delimiter in order not to get confused. This can be done using a client-only DELIMITER statement.
Once created, the JS stored routine can be called in the same way as any SQL routine:
|
1 2 3 4 5 6 7 |
mysql> SELECT fact(5); +---------+ | fact(5) | +---------+ | 120 | +---------+ 1 row in set (0.00 sec) |
It is visible in INFORMATION_SCHEMA.ROUTINES table and can be dropped if necessary:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> SELECT routine_schema AS s, routine_name AS n, routine_definition AS def, external_language AS l FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name='fact'; +------+------+----------------------------------------------+----+ | s | n | def | l | +------+------+----------------------------------------------+----+ | test | fact | let result = 1; while (n > 1) { result *= n; n--; } return result; | JS | +------+------+---------------------------------------------+----+ 1 row in set (0.01 sec) mysql> DROP FUNCTION fact; Query OK, 0 rows affected (0.01 sec) |
Parameters that you declare in the CREATE FUNCTION or PROCEDURE statement are accessible in JS code (OUT and INOUT parameters are also supported), and values returned using JS return statement become the return value of the stored function.
We support most SQL data types as parameters. Parameters containing character data that use a character set other than UTF-8 are automatically converted to UTF-8 for processing within JS code. This conversion is reversed if return values or OUT/INOUT parameters require it. BLOB, BINARY, and VARBINARY are mapped to DataView objects.
Parameters of JSON SQL data type are converted to JS objects. For return values, OUT, and INOUT parameters conversion is done in the opposite direction. In fact, this works so nicely that it deserves a separate example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
mysql> CREATE FUNCTION norm_keys(d JSON) RETURNS JSON LANGUAGE JS AS $$ $> const processData = (j) => { $> // Handle different possible structures $> if (Array.isArray(j)) { $> return j.map(i => processData(i)); $> } else if (typeof j === 'object') { $> const r = {}; $> for (const key in j) { $> // Transform keys to lowercase for consistency $> const normKey = key.toLowerCase(); $> r[normKey] = processData(j[key]); $> } $> return r; $> } $> return j; $> }; $> $> return processData(d); $> $$; Query OK, 0 rows affected (0.01 sec) mysql> SELECT norm_keys('{ '> "UserName": "John", '> "Age": 30, '> { "type": "phone", "value": "123-456-7890" } ], '> "metadata": { "lastLogin": "2023-01-01", "isActive": true } '> }') AS n; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | n | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"age": 30, "contacts": [{"type": "email", "value": "[email protected]"}, {"type": "phone", "value": "123-456-7890"}], "metadata": {"isactive": true, "lastlogin": "2023-01-01"}, "username": "John"} | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
Each connection and each active user gets its own JS context, which is not shared with other connections (for usability reasons) and users within the same connection (for security reasons).
We provide the same level of JS language support as provided by V8 engine. This means that standard operators, data types, objects (for example, Math) and functions from ECMA standard are supported, but things like the DOM model which are outside of it and normally provided by browsers are not. Nor do we support network or file IO, which would enable bypassing database security otherwise.
One can use JS_GET_LAST_ERROR() and JS_GET_LAST_ERROR_INFO() UDFs provided by our component to get information about the last JS error that happened for the current connection and user. For example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
mysql> CREATE PROCEDURE p_error() LANGUAGE JS AS $$ $> function l0() { $> return "Ooops!"(); $> } $> function l1() { $> return l0(); $> } $> (() => { return l1(); })(); $> $$; Query OK, 0 rows affected (0.01 sec) mysql> CALL p_error(); ERROR 6000 (HY000): TypeError: "Ooops!" is not a function mysql> SELECT js_get_last_error_info(); +----------------------------------------------------------+ | js_get_last_error_info() | +----------------------------------------------------------+ | Error: TypeError: "Ooops!" is not a function At: SQL PROCEDURE test.p_error:3:19 Line: return "Ooops!"(); ^ Stack: TypeError: "Ooops!" is not a function at l0 (SQL PROCEDURE test.p_error:3:20) at l1 (SQL PROCEDURE test.p_error:6:12) at SQL PROCEDURE test.p_error:8:19 at SQL PROCEDURE test.p_error:8:27 at SQL PROCEDURE test.p_error:10:3 | +----------------------------------------------------------+ 1 row in set (0.01 sec) |
One can also use the standard JS console log API (https://console.spec.whatwg.org/) to debug the JS stored programs. For example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> CREATE PROCEDURE p1() LANGUAGE JS AS $$ console.log("Test!") $$; Query OK, 0 rows affected (0.01 sec) mysql> CALL p1(); Query OK, 0 rows affected (0.01 sec) mysql> SELECT JS_GET_CONSOLE_LOG(); +----------------------+ | JS_GET_CONSOLE_LOG() | +----------------------+ | Test! | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT JS_GET_CONSOLE_LOG_JSON(); +----------------------------------------------------------+ | JS_GET_CONSOLE_LOG_JSON() | +----------------------------------------------------------+ | [ { "timestamp": "2025-05-27 19:13:25.654906", "level": "Info", "message": "Test!" } ] | +----------------------------------------------------------+ 1 row in set (0.00 sec) |
As you can see in the above example you can use JS_GET_CONSOLE_LOG() and JS_GET_CONSOLE_LOG_JSON() UDFs to get basic and extended forms of console log output for current connection and user. There is also JS_CLEAR_CONSOLE_LOG() UDF.
If running a Stored Program in JS takes too long or has entered an infinite loop, the execution can be easily aborted using the KILL QUERY SQL statement. The MAX_EXECUTION_TIME hint and variable also work.
Limitations and what’s coming next
There are a couple of things on our short-term plan that do not work yet:
- Tracking and limiting memory usage by Stored Programs in JS (works in the GitHub version, but has not been properly tested yet).
- Running SQL from Stored Programs in JS.
Help us shape the future: We want your feedback
We would like to ask our users, especially those who develop in JavaScript, to try this new experimental feature and share any issues you find and your suggestions for improvements with us! You can do this using Percona’s Community forum or our JIRA. It is so much easier to incorporate your feedback at this early stage rather than later!
I don’t like that you are introducing new features in 8.4 LTS. An LTS release is meant to be stable and only get bug fixes.
Hello Justin!
Support for stored programs in JS in Percona Server is implemented as a component which is not installed/enabled by default. And changes to SQL core which are needed for its execution and which are specific to Percona Server are minimal, thanks to the fact that most of SQL core code supporting external language stored programs execution are already in MySQL 8.4. So people who are concerned about stability of this feature should be safe as long as they do not install/enable this component.