This post demonstrates how to use node-express with Multilingual Engine(MLE)/JavaScript. It has been created as part of contribution to an open-source project and it might interesting in case you try to do something similar along these lines. However, there is a Big Caveat:

CAVEAT: the node-express combination is very 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 MLE, and that would defeat the purpose of this article.

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.

# 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 above compose 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. You can read more about Podman Secrets in the official documentation.

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 default (Pluggable) Database created by the container image. 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. The container image has been used when writing the article, as shown:

  1. 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

    sh $ podman ps --format "{{.Image}} --> use {{.Names}}"
    docker.io/gvenzl/oracle-free:23.6 --> use dbfree_oracle_1

    Similarly, use podman network ls to get the network. Let’s assume the network name is dbfree_backend.

  2. Start SQLcl

    Launch the SQLcl container instance. Don’t forget to update the network name!

    sh 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.

  3. 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.

    sql connect system@dbfree_oracle_1/freepdb1

    Enter the password to connected to the SYSTEM account, one of the default DBA accounts in the database.

  4. Switch to the default Pluggable Database and create the user

    Let’s create the user; please update the password when you do so 🙂

    sql 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

Don’t close the container session, you need it next to deploy the application code. 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> 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! You can find the deploy.sql script (all the code actually) on GitHub.

Start the Application

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

cd ./express-mle-javascript
    # install required packages
    npm i
    # start the application
    npm run dev

You can now point cURL at port 3000 and run some tests.

Have fun

Feel free to experiment with the code. The entry point in src/server/app.mjs defines all the application’s 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 defined in Mocha:

$ npm run test
    
    > test
    > 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)

Unit tests are the only way to gain confidence that your code doesn’t break/experience regressions, provided you follow DevOps principles (“work in small batches”, and “integrate often” principle spring to mind).