Oracle Database Multilingual Engine

Bringing Modern Programming Languages to the Oracle Database with GraalVM

Bastian Hossbach
Dec 13, 2018 · 9 min read

Introduction

In this article we will explore what happens when GraalVM and the Oracle Database come together. Readers familiar with the Oracle Database might know that it can be extended with user-defined functions written in PL/SQL. However, GraalVM embedded into the Oracle Database allows to write user-defined functions also in various mainstream programming languages such as JavaScript and Python. Let’s have a look at a short example session in SQL*Plus:

GraalVM — A Universal and Embeddable Virtual Machine

GraalVM is a universal virtual machine that runs applications written in a variety of programming languages (JavaScript, Python 3, Ruby, R, JVM-based languages, and LLVM-based languages) with high performance on the same platform. Supporting multiple programming languages allows GraalVM to share basic infrastructure such as JIT compilation, memory management, configuration and tooling among all supported languages. The sharing of configuration and tooling leads to a uniform developer experience in modern projects that make use of multiple programming languages.

Database Programming

When it comes to databases, SQL has been proven to be the language of choice for querying data over the last decades. The fact that basically all new query languages (e.g., for stream, graph, or document processing) are designed as dialects of SQL just underlines its success. However, the limitations of SQL are reached as soon as more complex business logic must be implemented. The database community acknowledged this early and extended SQL with procedural features that allow to combine multiple SQL statements and computations into arbitrarily complex data processing workflows. The most popular such extension is PL/SQL, Oracle’s procedural extension to SQL. Those procedural extensions to SQL are specific to the database domain. This explains why the communities around them are quite small compared to the communities of general-purpose programming languages such as JavaScript and Python. The pools of JavaScript and Python developers are significantly larger than the pool of PL/SQL developers. Therefore, it is much easier to find JavaScript or Python developers than it is to find PL/SQL developers. Moreover, popular modern languages come with huge ecosystems of readily available open-source libraries in public software registries (e.g., NPM, PyPI). In those registries, it is easy to find high-quality libraries for basically every task.

Multilingual Engine

At Oracle, we are currently working on embedding GraalVM into the Oracle Database and MySQL. We call these extensions Multilingual Engine (MLE). In this article we focus on MLE for Oracle Database only. Oracle Database MLE is an experimental feature at the moment.

Ad-hoc Execution of Scripts with Dynamic MLE

DBMS_MLE can execute scripts that are given in form of strings in PL/SQL. Data is exchanged with a script in both directions (in and out) via so-called bind variables. Lastly, a script can print messages that will be put in the output
buffer
of the database. Let’s have a look at a concrete example:

Component Sharing Across Languages

In our first examples we introduced the MLE SQL Driver and showed how to use it from JavaScript and Python. It looks like a module implemented in the used language, but it isn’t. Instead of implementing a complete bridge from a language’s SQL API to the SQL engine of the Oracle Database for each language we add, we have to do the main part of the work only once thanks to the polyglot feature of GraalVM. In a nutshell, the polyglot feature enables a language that runs on GraalVM to access objects and call functions that belong to another language. Therefore, we implemented basic components needed by all languages such as data conversions and the MLE SQL Driver as new internal languages that can be used directly from all other languages. For implementing new languages, GraalVM provides the Truffle framework that we used for that purpose. We just added a thin language-specific layer on top of each MLE language to hide some internals and make them look truly native. The Truffle framework allows not only to implement sharable components, but also to fully leverage GraalVM’s speculative JIT compiler. In the context of databases, the latter is of utmost significance because data conversions are often the main cost factor.

MLE Stored Procedures

While running a script written in a modern language on-the-fly is convenient in many situations, it is not ideal for developing large and complex applications. Dynamic MLE requires a skeleton in PL/SQL and third party libraries cannot be used directly. Furthermore, code is best managed by the database similar to data. To unleash the full power of MLE, we allow to persistently store and maintain user code in the database in form of modules consisting of user-defined functions and stored procedures. For painless packaging and deployment of modules, we plan to provide external tools that do everything with a single command.

MLE User-defined Functions

MLE also allows developers to implement user-defined functions in JavaScript and Python. The difference is that a UDF can be used like any other SQL function whereas a stored procedure cannot be called from a SQL statement. The JavaScript community created a large set of software packages. MLE allows database developers to easily reuse code from software registries such as NPM.

Conclusion

We looked at how the Multilingual Engine (MLE) allows you with the help of GraalVM to use JavaScript and Python in the Oracle Database, bringing their enormous ecosystems to your data-intensive computations. With GraalVM, not only can we rapidly bring new languages to the Oracle Database, but we also have a high-performance speculative JIT compiler at our fingertips. It can be used to generate efficient code for critical parts of queries such as data conversions at runtime.

graalvm

GraalVM team blog - https://www.graalvm.org

Bastian Hossbach

Written by

Researcher at Oracle Labs. Working on the Multilingual Engine (MLE) for the Oracle Database. Opinions are my own.

graalvm

graalvm

GraalVM team blog - https://www.graalvm.org