Node-express MLE/JavaScript example

This is an example how to use node-express with Multilingual Engine(MLE)/JavaScript. It has been created as part of a contribution to an open-source project and I found it interesting enough to publish, even though there is a big caveat:

CAVEAT: node-express is well established in the JavaScript community, it’s simple, lightweight, and easy to use. However, you are probably better off using Oracle REST Data Services (ORDS) instead of express for more serious work if you don’t want to reinvent the wheel. ORDS provides a lot more than express out of the box, including, but not limited to, authorisation and authentication.

Database Setup

This little application requires an Oracle Database (Free) instance. This example features Gerald Venzl’s image, and a compose file to get you up to speed quickly. If you prefer your own setup, please make sure not to use the *-slim image flavours. These don’t include Multilingual Engine/MLE, and that would defeat the purpose of this post.

Here’s an example compose file you can use as the starting point. The file caters for rootless podman/podman-compose; adapt as necessary for other container runtimes like Docker(-compose). You may also want to bump the version tag as newer releases become available.

UPDATE 250627: I posted compose files on my GitHub, these are more regularly updated as this blog post. If you want to run a newer version, and you almost certainly want to, head over there now.

# THIS IS NOT A PRODUCTION SETUP - LAB USE ONLY!
services:
    oracle:
        image: docker.io/gvenzl/oracle-free:23.6
        ports:
            - 1521:1521
        environment:
            - ORACLE_PASSWORD_FILE=/run/secrets/oracle-passwd
        volumes:
            - oradata-vol:/opt/oracle/oradata
        networks:
            - backend
        healthcheck:
            test: [ "CMD", "healthcheck.sh" ]
            interval: 10s
            timeout: 5s
            retries: 10
            start_period: 5s
        secrets:
            - oracle-passwd

volumes:
    oradata-vol:

networks:
    backend:

secrets:
    oracle-passwd:
        external: true

The file worked brilliantly with podman-compose 1.0.6-1 and podman 4.9.3. It requires a Podman secret, named oracle-password to run. I wrote about Podman secrets in an earlier article.

Application User

Once the database is up and running (visible in the output of podman logs <container>) you need to create an application user in FREEPDB1.

The easiest way to do that is via SQLcl. Either install it locally or use the official image. The latter is preferable if you don’t want to deal with SQLcl’s dependencies such as the Java Runtime.

Get required connection information

Before starting you need to know
- the name of the database container to connect to
- the network it used
Use the following snippet to get the container name
```
$ podman ps --format "{{.Image}} --&gt; use {{.Names}}"
docker.io/gvenzl/oracle-free:23.6 --&gt; use dbfree_oracle_1
```
Similarly, use podman network ls to get the network. Let’s assume the network name is dbfree_backend.

Start SQLcl

Don’t forget to update the network name!

cd express-mle-javascript/src/

podman run -it --rm \
--volume ./database/:/opt/oracle/sql_scripts:Z \
--network ${compose_network:-"dbfree_backend"} \
container-registry.oracle.com/database/sqlcl:latest /nolog

This will launch SQLcl.

Connect to the SYSTEM account using the container name you worked out earlier

If you created the database using the compose file shown above, the following command will work without a change.
```
connect system@dbfree_oracle_1/freepdb1
```

Switch to the default Pluggable Database and create the user

Using the container image for Oracle Database Free you get a default Pluggable Database ready for use. It’s name is FREEPDB1. Please update the password :)

alter session set container = FREEPDB1;

create user app_user identified by "someSecretPasswordOfYourLiking"
default tablespace users
quota 1g on users;

-- these are required for MLE/JavaScript
grant execute on javascript to app_user;
grant execute dynamic MLE to app_user;
grant db_developer_role to app_user;
grant soda_app to app_user;

alter user app_user default role all;

This concludes the database setup.

Code Deployment

Connect as the newly created user and run the deploy.sql script:

connect app_user@dbfree_oracle_1/freepdb1
start deploy.sql

You should see the deployment on your screen:

SQL&gt; start deploy
MLE Module demo_module created

Table DEMO_TABLE created.


Procedure PROCESS_DATA compiled


Function GET_DB_DETAILS compiled


Function GET_MESSAGE_BY_ID compiled

The stage is now set!

Start the Application

Open a new terminal and start the express application as follows:

cd ./express-mle-javascript
npm i
npm run dev

Have fun

Feel free to experiment with the code. The entry point in src/server/app.mjs defines all the application’s REST verbs:

get
post
delete

The database logic is written in MLE/JavaScript (mle-server-side-code.mjs) exposed to the database in src/database/deploy.sql. The latter defines the PL/SQL call specifications.

Test the application by querying session info:

curl --silent http://localhost:3000/api/info | jq

You can post a message to the database:

curl --silent --json '{ "message": "this message has been provided via curl" }' \
http://localhost:3000/api/messages/

Retrieve messages from the database:

curl --silent http://localhost:3000/api/messages | jq

You can also get specific messages by their ID. Here’s how to retrieve the last message inserted:

lastMessage=$(curl --silent http://localhost:3000/api/messages | jq 'map(.ID) | max')
curl --silent http://localhost:3000/api/messages/${lastMessage:-1} | jq

And finally, you can delete messages, too

lastMessage=$(curl --silent http://localhost:3000/api/messages | jq 'map(.ID) | max')
curl --request DELETE --silent http://localhost:3000/api/messages/${lastMessage:-1}

To test if these operations are available, you can run the unit test suite:

npm run test

&gt; test
&gt; npx mocha ./test

  All Unit Tests
    Session Info tests
      ✔ should print session info (105ms)
    Posting a message
      ✔ should post a message to the database successfully (39ms)
      ✔ should fail posting a message to the database
    Retrieval of all messages
      ✔ should retrieve one or more messages from the database
    Retrieval of a single message
      ✔ should retrieve the last message posted from the database
      ✔ should fail to retrieve a message due to an invalid parameter type
      ✔ should fail to retrieve a message due to a negative ID
    Deleting messages
      ✔ should delete the last message inserted
      ✔ should fail to delete a message due to an invalid parameter type
      ✔ should fail to delete a message due to a negative ID


  10 passing (304ms)

Keep on experimenting and having fun. The entire project is hosted on my GitHub.