MySQL offers two paths for users who want to add custom functions: SQL stored routines (SRs) and user-defined functions (UDFs).
SRs are simple to create, safe to execute, and offer features such as SQL queries. Their downside is poor performance, clunky syntax, and no support for aggregate functions. UDFs are harder to create and can be unsafe. However, performance is about as fast as it ever gets. UDFs do support aggregate functions.
Stored Routines are simple to create and safe to execute and offer features such as SQL queries. Their downside is poor performance, clunky syntax, and no support for aggregate functions. User-defined functions are usually coded in C/C++ and are typically harder to create and can be unsafe. However, performance is about as fast as it ever gets. UDFs do support aggregate functions.
The MySQLv8UDFs project offers UDFs that exposes the UDF interface to Google’s v8 JavaScript engine. This enables you to write MySQL scalar and aggregate functions in JavaScript. Although typically not as fast as C/C++ UDFs, we have examples where the javascript UDF solution is 5-8 times the performance of stored SQL functions, and even 2 times faster than the equivalent straight SQL expression. For more computationally intensive tasks, the javascript UDFs easily win from MySQL stored functions, while being relatively easy to code as compared to a straight C/C++ UDF.
Apart from just UDFs to run javascript, the javascript environment offers many useful built-ins. Apart from the standard javascript built-ins, which include powerful string functions, an expressive and fast regular expression engine, and a convenient and safe JSON parser/serializer, MySQLv8udfs adds a number of utilities that are useful in a specfic MySQL context, such as functions to write to the MySQL error log and a fully functional MySQL client that allows you run queries to any MySQL server accessible through the network.
Code: https://github.com/rpbouman/mysqlv8udfs.
Speakers: Roland Bouman