Building an end-to-end Bookstore App in APEX using JavaScript/MLE and GraphQL

by Salma El Bekraoui and Lucas Braun

Photo by Jaredd Craig on Unsplash

Welcome to the wonderful world of JavaScript/MLE! In this article, we’ll learn how to build an extraordinary end-to-end bookstore application in record time.

How, you ask? Well, with the help of external community JavaScript modules, the trusty fetch API, and just a sprinkle of JavaScript gluing. It’s like baking a cake, but instead of flour and eggs, we’ll be using GraphQL and Oracle Application Express APEX low-code features like the cards region!

Before getting into the technical details, it’s important to mention that the environment we work with is APEX 23.1, which operates on top of an Oracle Database 23c Free-Developer Release. For those interested in setting up a similar environment, we recommend referring to the blog and documentation available at https://shlayel.github.io/oracle-livelabs-apex/mle-javascript.

Let’s take our first steps into the fetch API. But before, we need to set up some access control to ensure a smooth ride.

To make callouts to the public internet, we need to add the necessary access control lists.

Let’s execute the following script as sysdba. Be aware that the script will prompt you for the schema name for which you want to grant access:

Point your browser to https://ords-instance:ords-port/path/to/login-page to open APEX and navigate to SQL Workshop and access SQL Commands. Choose “PL/SQL” as your language of preference.

Unlike in previous versions of Oracle Database, there is no need to create a wallet for secure communication with TLS-protected web resources, you can use the operating system’s credential store instead:

utl_http.set_wallet('system:');

We can now harness the power of the JavaScript fetch API to retrieve the book results we seek and display them on the console. To proceed, let us switch the language in the SQL commands to “JavaScript” and execute the following snippet:

The expected result should look like this:

In our application, we will take advantage of the capabilities of the open-source GraphQL module. Please refer to GraphQL’s GitHub project site for more details about the project’s license and implications of use. The article assumes your legal and IT Security departments (as well as any other party) agreed that it’s safe to use the module in your code. Using 3rd party code in your application typically requires certain compliance steps to be completed which are out of scope of this article.

Within the context of our project, we will leverage GraphQL to seamlessly merge data acquired from the Google Books API with locally stored information in our database, providing a unified service to our users.

First, we need to create a MLE JavaScript module in the database where we import the content of GraphQL module as EcmaScript module.

In order to install the GraphQL module into Oracle as MLE module, first go to the object browser:

Then, click on the “+” button in the left pane and select “MLE Module — JavaScript” or simply select this option from the middle pane:

By initiating this action, a dialogue box will appear prompting you to enter the name of your module, GRAPHQL, along with the version, 16.6.0 (although not mandatory, it is recommended). You will then choose the URL as source type and input the following: https://cdn.jsdelivr.net/npm/graphql@16.6.0/+esm.

You just have to click the “Create MLE Module” button and patiently await a few seconds until you receive confirmation that the module has been successfully created.

Next, we will create the Inventory Table to store the information about the books currently in stock.

To proceed, switch the language in your SQL commands to “SQL” and execute the following code:

create table inventory(
    id VARCHAR(128) PRIMARY KEY NOT NULL,
    stock NUMBER
);

Once the table is created, you need to populate it with some data. Follow these steps:

In the search bar, type “Data Workshop” and open it.

Click on “Load Data” within the workshop. Select inventory.csv which you can download here.

Ensure that you choose “Existing Table” and select the “INVENTORY” table.

Click on “Load Data” to initiate the data-loading process. After the data is successfully loaded, it should resemble something similar to this:

To proceed, let’s create a module called “BOOK_SCHEMA” where we define the GraphQL schema for the books we want to retrieve in our application.

Go back to the object browser and create a new MLE module. Name the module “BOOK_SCHEMA”.

This time, select the “Source Code” option and input the following code:

The ‘‘getBooks’’ function is an asynchronous function that takes a SEARCH_URL as a parameter. It uses the ‘‘fetch’’ API to make an HTTP GET request to the Google Books API with the provided URL. The response is then parsed as JSON and returned.

Object types are defined in GraphQL to represent the structure of the data that can be queried. The schema defines several object types, including ‘‘imageLinks’’, ‘‘author’’, ‘‘volumeInfo’’, ‘‘book’’, and ‘‘volumes’’. These types represent different aspects of a book, such as image links, author information, volume information, and collections of books.

The ‘‘queryType’’ object represents the available queries that clients can execute. In this case, there is a single query called ‘‘library’’ that accepts a ‘‘searchTerm’’ argument, which represents a search query string. The resolve function of this query calls the ‘‘getBooks’’ function with the provided search query and returns the result.

The ‘‘schema’’ object is an instance of ‘‘GraphQLSchema’’ and defines the root types and available types in the schema. In this case, the ‘‘query’’ property is set to the ‘‘queryType’’, and the available types are specified in the ‘‘types’’ property.

To import modules that we created in the previous steps, we need to create a new MLE environment with the 2 import names: “graphql” and “book-schema”.

We start by creating a new environment named “BOOK_APP” from Object Browser in SQL Workshop.

In the object browser, hit the “+” button, select “MLE Environment”:

Enter “BOOK_APP” as the name for the environment and click on “Create MLE Environment”:

Once created, click on “Add Import” to open the dialog for adding imports.

Select “GRAPHQL” as schema and “graphql” as its import name and hit “CREATE”:

Repeat the same step for importing “BOOK_SCHEMA” as “book-schema”.

Finally, the created environment should look like this:

To wrap up the first part of this article, our objective is to verify the functionality of the imported and created MLE modules.

Navigate to SQL Commands, choose language JavaScript and Environment BOOK_APP:

Now, let’s execute the following code in SQL Commands, using JavaScript as language:

It returns an array of book objects, extracts specific information from each book, and returns an array of new objects with a simplified structure that includes the book's title, authors (joined as a comma-separated string), stock, and an image link (if available).

The next step is to create a RESTful web service to expose books.

First, we will create a new module called “SERVICE.js” similar to “INFO.js” :

In SQL Commands, run the following code as PL/SQL to create a function call specification for the “getInfo()”:

CREATE OR REPLACE FUNCTION QUERY(search in VARCHAR2)
    RETURN JSON
    AS MLE MODULE SERVICE
    ENV BOOK_APP
    SIGNATURE 'getInfo(string)';

To create a RESTful service, we’ll navigate to the “RESTful Services” tab in the “SQL Workshop” section, as shown in the picture below. First-time users should enable the “RESTful Services” feature with the sample endpoints before using it.

We will be redirected to a similar page:

We start by creating a new module.

We named the module “oracle.example.book” and the base path “service”.

After creating the module, we need to create template.

The URI template contains “:searchTerm”, which is the argument we’re going to pass to the function “query” already created at the beginning of this part.

Once the template is created, we need to create a handler.

We set the method to “GET” and the source type to “Collection Query”. It executes a SQL query returning one row of data into an ORDS Standard JSON representation.

In the source tab, we enter the following code :

select query(:searchTerm)

After applying the changes, we can check if the service is created successfully by copying the URL from the handler window and opening it in the browser. You can append a specific search term to the URL to test its functionality. For example, you can add “/service/book/Oracle” to the URL.

Here’s an example of what the URL might look like after appending the search term when calling it:

Now that we have created a REST service for our bookstore that can easily be consumed by both, humans as well as applications, we’d also like to create an APEX web application for displaying those books in a nicer, visually attractive way.

In the App Builder, select “Create” and then click on “New Application”.

Choose a name for your application ( We suggest “BOOK APP”). Users can change the appearance or the logo of the app.

Now, you’re ready to create the app by hitting “Create Application”.

Once the application is created, you’ll be redirected to this page.

In order to set the environment to the one with the necessary modules, go to “Shared components”. In the “Security” tab, choose “Security attributes”. In “Database session” set “MLE Environment” to “BOOK_APP” and apply changes. By setting the app’s MLE Environment, we ensure any JavaScript (MLE) code executed by the application will use this Environment to resolve imports.

Now, navigate to Page Designer, to page 1 and create a Cards region with SQL query source.

Set the following properties:

• Identification → Title: Books
• Identification → Type: Cards
• Source → Location: Local Database
• Source → Type: SQL Query

select title,
       subtitle,
       thumbnail as imageLinks
  from json_table( query(:P1_SEARCH), '$[*]'
        columns(
            title       varchar2(100)      path '$.title',
            subtitle    varchar2(100)      path '$.subtitle',
            thumbnail   varchar2(4000)     path '$.imageLinks.thumbnail'
        ) ); 

Click the Attributes tab at the top of the properties pane on the right of Page Designer. This is where you can select what data will display on each book card.

• Appearance → Grid Columns: 5 Columns
• Title → Column: TITLE
• Subtitle → Column: SUBTITLE
• Media → Source: Image URL
• Media → URL: &IMAGELINKS.
• Media → Position: First
• Media → Appearance: Square
• Media → Sizing: Cover

Create a textfield “P1_SEARCH” and a “Dynamic Action” that on “P1_SEARCH” change, refreshes the cards region.

Hit “Run” button and TADA!!

We have explored the process of integrating external JavaScript modules into your APEX application and leveraging them as RESTful services. By combining different community-provided modules, you can extend the functionality of your application and enhance user experience.

For more information, you can check out https://github.com/oracle-samples/oracle-db-examples/tree/main/javascript/MLE-Demo