Create OCI Function with ADB client embeded

—

by

team@solution250.com
in

We want to create a serverless nodeJs function which interacts with our ATP database via a wallet.

We found a blog by Tom Sharp from Oracle. Lets change it up, update and thank him for his insight!

Link to Tom’s Post

1. Create Application

To start with you will need this information:

  • Your ATP wallet: create one from OCI console: Autonymous Database > Database Connections button
  • Look in the tnsnames.ora file of your unzipped wallet and grab the connection string (ends with _high, _low..)
  • Your ATP Username and Password (should encrypt in vault as a secret) Link to blog
  • Compartment ID
  • VCN subnet ID’s you want to associate with the function
From OCI Console Shell>
// Find the OCIID of the compartment using the name of the compartment, in our case Development:

$ oci container-instances container list --compartment-id <Development>

Or use the interactive console to automaticall fill that in:

Find out the sub net OCIID for the subnet(s) you want the application reachable from Or use oci -i and type ‘fn’ to pick them by name:

oci network subnet list --all --compartment-id ocid1.compartment.oc1..aaaaaaaa

fn create app --annotation oracle.com/oci/subnetIds='["oci id of public subnet","private subnet"]' //List of subnets
--config DB_PASSWORD='YourPassword' 
--config DB_USER='ADMIN' 
--config CONNECT_STRING='str_low' 
//pick connect string from tnsnames.ora in wallet
fn-atp-node-json

In red: the config lines are going to be used later, as environment variables for the function.  All this could be done via OCI console front end in Create Application.

fn create app --annotation oracle.com/oci/subnetIds='["oci id of public subnet","private subnet"]' --config DB_PASSWORD='' --config DB_USER='ADMIN' --config CONNECT_STRING='str_low' fn-atp-node-json

You should now see the Application in the console:

From here click on the Application, you will get some basic instructions to get you hooked to you own repository and logged onto docker. Do these steps by copying the instructions.

Step 6: the automatically generated logon string to your region repository When prompted for a username, enter your username in the format <tenancy-namespace>/<username>, where <tenancy-namespace> is the auto-generated Object Storage namespace string of your tenancy (as shown on the Tenancy Information page). For example, ansh81vru1zp/jdoe@acme.com. If your tenancy is federated with Oracle Identity Cloud Service, use the format <tenancy-namespace>/oracleidentitycloudservice/<username>.

docker login -u ‘xxxxxxxghdn/alice@example.com’ mel.ocir.io

docker login -u ‘axixxxxghdn/oracleidentitycloudservice/alice@example.com’ mel.ocir.io

Once you are happily logged into your docker repo and pointing at you own registry – you can check as below.

Now we are ready to set up the function.

2. Create the function

We need to ensure that the container which runs our node code has got the oracle client installed. So we need to either use a pre build docker container with instant client as the base or build our own. In Tom’s article he builds it.

https://blogs.oracle.com/opal/post/github-container-registry-has-oracle-instant-client-images

We are going to use the latest Oracle provided instant client as in..

(for info only) docker pull ghcr.io/oracle/oraclelinux8-instantclient:21

So next we will create the function in the same way as Tom but only so we can add our wallet. From your home directory create a function called ‘fn-atp-node-json-insert’ in our application:

fn init --runtime node fn-atp-node-json-insert
cd fn-atp-node-json-insert

mkdir wallet
cp ../../wallets/tmp/* . #your wallet location

Create a docker file but for the instant client base image. We need to get it FROM the oracle repository, install npm as it doesn’t have it and copy the wallet over.

FROM ghcr.io/oracle/oraclelinux8-instantclient:21

RUN yum -y install npm && \
    rm -rf /var/cache/yum && \
    groupadd --gid 1000 --system fn && \
    useradd --uid 1000 --system --gid fn fn

COPY wallet/* /usr/lib/oracle/21/client64/lib/network/admin/

WORKDIR /function
ADD . /function/
RUN npm install

CMD exec node func.js

Edit the yaml file and add the last 4 entries:

schema_version: 20180708
name: fn-atp-node-json-insert
version: 0.0.1
runtime: node
build_image: fnproject/node:18-dev
run_image: fnproject/node:18
entrypoint: node func.js
format: http-stream
memory: 256
timeout: 120
idle_timeout: 1800

Now we can edit the func.js to include a ADB connection. In this example I switch the sql we run based on the input to show that aspect is working.:

const fdk = require('@fnproject/fdk');
const oracledb = require('oracledb');

let result;
let r = [[]];
let sql;
let sqlA = `select CUSTOMER_ID, CUSTOMER_TYPE, CUSTOMER_NAME, ENABLED, LOGIN_ATTEMPTS from solution250.CUSTOMER`;

fdk.handle(async function (input) {
    let name;
    
    if (input.name) {
        name = input.name;
        switch (name) {
            case 'Alice':
                sql = sqlA
                break;
        
            default:
                sql = 'select * from dual'
                break;
        }
    }
    
    let connection;

    // Get the TNS service name from the configuration variables
    const user = process.env.DB_USER;
    const password = process.env.DB_PASSWORD;
    const svcName = process.env.CONNECT_STRING;
    

    try {
        connection = await oracledb.getConnection({
            user: user,
            password: password,
            connectionString: svcName,
        });

        console.log("\nnode-oracledb driver version is " + oracledb.versionString);

        console.log("\nOracle client version is " + oracledb.oracleClientVersionString + "\n");

        result = await connection.execute(
            sql,
            [], {
            resultSet: true,
            outFormat: oracledb.OUT_FORMAT_OBJECT
        }
        );

        
        rs = result.resultSet;
        
        let row;
        r.pop();
        while ((row = await rs.getRow())) {
            console.log(row);
            r.push(row);
        }
        await rs.close();
        

    } catch (err) {

        console.log("Error:", err);

    } finally {

        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
            }
        }
        console.log("\nBye");
        return JSON.stringify(r);
        
    }
}, {});

The package.json file should include the oracledb dependency

{
        "name": "hellofn",
        "version": "1.0.0",
        "description": "example function",
        "main": "func.js",
        "author": "",
        "license": "Apache-2.0",
        "dependencies": {
                "@fnproject/fdk": ">=0.0.64",
                "oracledb" : "^3.1"
        }
}

3. Deploy and Test

Deploy the function, from the function root directory

fn -v deploy --app fn-atp-node-json

You should see something like this:

Potential issues:

1. Notice of creation of package.json.lock if you have not run the code locally

Now test with invoke:

echo '{"name": "Alice"}' | fn invoke fn-atp-node-json fn-atp-node-json-insert

This gave an error:

Error invoking function. status: 502 message: function failed

So first switch to the private network to ensure that we are on the same subnet as the ATP

Now activate the function logging:

OCI would need to be configured differently depending on your architecture, for example if you are using a load balancer. But you will need to allow in your security list the port activity on the port in your wallet. Assuming the network configuration is correct, you would get a response from the function.

$echo '{"name": "Alice"}' | fn invoke fn-atp-node-json fn-atp-node-json-insert
"[[1,\"C\",\"Consultant\",\"Y\",3],[2,\"E\",\"Enterprise\",\"Y\",3],[3,\"P\",\"Professional\",\"Y\",3]]"

That’s It.

Bye from us at solution250, until the next blog

References

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *