Handling Query Parameters in JavaScript-based ORDS Endpoints

Oracle REST Data Services (ORDS) allows you to REST-enable your Oracle Database. REST is short for Representational State Transfer, and REST calls are typically used as your database API (Application Programming Interface). Instead of accessing the database directly via a driver SDK, you use HTTP calls instead.

There is of course a lot more to say about that topic, but this is all you need to know for this article. If you do want to know more, I’d like to encourage you to visit the ORDS documentation.

In addition to writing your REST API in PL/SQL, you can also use JavaScript, beginning with ORDS 24.1.1.

Query Parameters

Here’s a short example how to use query parameters in your handler. For the sake of simplicity let’s assume your task is to write a GET handler, returning a JSON. Typically there are 2 types of these: one to get a specific object from the database, often by its ID, and another one returning every record. Your job is to implement the latter.

Since no one wants to retrieve thousands of records over the network, the GET handler in question must support

an optional search parameter
pagination (limit and offset)

You pass these parameters to the REST handler via the URI. A typical call is shown here:

			
$ curl -s 'http://localhost:8080/ords/emily/v1/actionItem/?limit=1' | jq .
{
  "items": [
    {
      "total_count": 3,
      "actionitem": {
        "actionId": 2,
        "actionName": "conduct q1 customer feedback interviews",
        "status": "OPEN",
        "team": [
          {
            "assignmentId": 6,
            "role": "MEMBER",
            "staffId": 5,
            "staffName": "elliott brooks"
          },
          {
            "assignmentId": 5,
            "role": "LEAD",
            "staffId": 1,
            "staffName": "avery johnson"
          }
        ]
      }
    }
  ],
  "hasMore": true,
  "totalRows": 3
}

		

The ORDS handler returns multiple pieces of information:

The actual data in form of an items[] array
metadata indicating that more records are stored in the table(s) than those that have been fetched

Production-grade REST APIs typically provide a _links object with additional information, this has been skipped for the sake of simplicity. Another option is to pass a search term:

			
$ curl -s 'http://localhost:8080/ords/emily/v1/actionItem/?search=pilot' | jq .
{
  "items": [
    {
      "total_count": 1,
      "actionitem": {
        "actionId": 3,
        "actionName": "roll out workflow automation pilot",
        "status": "OPEN",
        "team": [
          {
            "assignmentId": 8,
            "role": "MEMBER",
            "staffId": 6,
            "staffName": "finley chen"
          },
          {
            "assignmentId": 7,
            "role": "LEAD",
            "staffId": 4,
            "staffName": "devon patel"
          }
        ]
      }
    }
  ],
  "hasMore": false,
  "totalRows": 1
}

		

Everything after the ? is known as the query string: it’s a set of key=value pairs, separated by an &

Implementation

Your ORDS handler needs to be able to parse the incoming parameters. Just like with node-express you have a req and resp object available to you, although the implementation doesn’t match 1:1. The code in this snippet delegates the entire logic to the getAllActionItemsHandler() function. Creation of the template and module has been skipped, you can find it on GitHub.

			
declare
    c_module_name       constant varchar2(255) := 'v1';
    c_single_pattern    constant varchar2(255) := 'actionItem/:id';
    c_all_pattern       constant varchar2(255) := 'actionItem/';
begin
    -- code declaring module and template skipped to the sake of brevity
    ords.define_handler(
        p_module_name    => c_module_name,
        p_pattern        => c_all_pattern,
        p_method         => 'GET',
        p_source_type    => 'mle/javascript',
        p_mle_env_name   => 'JAVASCRIPT_IMPL_ENV',
        p_items_per_page => 0,
        p_mimes_allowed  => null,
        p_comments       => null,
        p_source         => q'~
async (req, resp) => {
 
    const { getAllActionItemsHandler } = await import ('handlers');
    getAllActionItemsHandler(req, resp);
}    
    ~'
    );
    commit;
end;
/

		

The JavaScript code in p_source imports getAllActionItems() with the help of a MLE environment. The import name, handlers in this example, is mapped to a JavaScript module containing the actual implementation of getAllActionItemsHandler().

Both req and resp objects are passed to the function, primarily to keep the handler static. Both req and resp objects are documented in the ORDS Developer’s Guide by the way.

Let’s have a look at the top-level function, getAllActionItemsHandler()

			
/**
 * Get all action items - handler. This function is invoked by ORDS.
 * 
 * Enables pagination. In order to do so this function parses the request
 * object's query_parameters object and assigns sensible defaults
 * 
 * The actual work is done by getAllActionItems() below.
 * 
 * @param {object} req the request object, as provided by ORDS
 * @param {object} resp the corresponding response object 
 */
export function getAllActionItemsHandler(req, resp) {
    
    // no console.log() in production!
    console.log('req: ' + JSON.stringify(req));
    try {
        let searchPattern = req.query_parameters?.search;
        if (! searchPattern) {
            searchPattern = '%';
        } else if (! /^[A-Za-z0-9]+$/.test(searchPattern)) {
            resp.status(400);
            resp.content_type("application/json");
            resp.json({
                error: 'Invalid query parameter (search)',
                message: 'search pattern must only contain letters and numbers'
            });
            return;
        } else {
            searchPattern = `%${searchPattern}%`;
        }
        let saneLimit = Number.parseInt(req.query_parameters?.limit ?? "25", 10);
        if (saneLimit < 0 || saneLimit > 100 || !Number.isInteger(saneLimit)) {
            resp.status(400);
            resp.content_type("application/json");
            resp.json({
                error: 'Invalid query parameter (limit)',
                message: 'limit must be an integer in the range [0,100]'
            });
            return;
        }
        let saneOffset = Number.parseInt(req.query_parameters?.offset ?? "0", 10);
        if (saneOffset < 0 || saneOffset > 100 || !Number.isInteger(saneOffset)) {
            resp.status(400);
            resp.content_type("application/json");
            resp.json({
                error: 'Invalid query parameter (offset)',
                message: 'offset must be an integer in the range [0,100]'
            });
            return;
        }
        const input = {
            search: searchPattern,
            limit: saneLimit,
            offset: saneOffset
        }
        console.log('input: ' + JSON.stringify(input));
        const data = getAllActionItems(input);
        resp.status(200);
        resp.content_type('application/json');
        resp.json(data);
        
    } catch (err) {
        // use more robust error handling in production
        console.log(err);
        resp.status(500);
    }
}

		

NB: it’s a good idea not to pass req or resp to lower layers of your code.

The function shown above parses the potential query parameters

search
limit
offset

and assigns sensible defaults if they aren’t present after sanity checking. The function emits values for both req and the input object only for the purpose of this article. Do NOT use console-out-debugging in production, use a dedicated framework for that purpose. Note how the query parameters are available in the query_parameters object, which is part of the HTTP request.

The next step is to perform the actual database operation in GetAllActionItems(), more on that later. The remaining code deals with result processing.

The HTTP status code is set to 200 with an empty result set returned if no data was found
Else the status code is changed to 200 (=success) and the data fetched returned in JSON format
If the query hit an internal server error, it is returned, too

Again, in a serious production environment this would be moved into a function: DRY! (don’t repeat yourself!) With that out of the way, let’s consider the database read next:

			
function getAllActionItems({search, limit, offset}) {
    // unlike node-oracledb, MLE/JavaScript executes synchronously
    // no need for async() function calls
    const result = session.execute(
        `select
            count(*) over () as total_count,
            json{
                'actionId':      a.id,
                'actionName':    a.name,
                'status':        a.status,
                'team' value (
                    select json_arrayagg(
                        json{
                            'assignmentId': tm.id,
                            'role':         tm.role,
                            'staffId':      tm.user_id,
                            'staffName':    s.name
                        }
                        order by tm.role desc, s.name
                    )
                    from
                        action_item_team_members tm
                        join staff s on s.id = tm.user_id
                    where
                        tm.action_id = a.id
                )
            } as actionItem
        from
            action_items a
        where
            upper(a.name) like upper(:search)
        offset :offset rows fetch first :limit rows only`,
        {
            search: {
                dir: oracledb.BIND_IN,
                val: search
            },
            offset: {
                dir: oracledb.BIND_IN,
                val: offset
            },
            limit: {
                dir: oracledb.BIND_IN,
                val: limit
            }
        },
        {
            fetchTypeHandler: (metaData) => {
                // convert every column name to lower case
                metaData.name = metaData.name.toLowerCase();
            }
        }
    );
    // just in case the search didn't reveal anything
    if (result.rows?.length === 0)
        return {
            "items": [],
            "hasMore": false,
            "totalRows": 0
    };
    // column names are lower case thanks to the fetch type handler
    const totalRows = result.rows[0].total_count;
    const rowCount = result.rows.length;
    return {
        "items": result.rows,
        "hasMore": (offset + rowCount < totalRows),
        "totalRows": totalRows
    }
}

		

The function returns data from the database in JSON format. It receives the query parameters and uses those as bind variables in the session.execute() call. The result object contains the number of rows in the result set, whether more rows are available to fetch(-> pagination), and the total number of rows in the table.

Summary

Query parameters are extremely useful narrowing down result sets. Your JavaScript handlers parse the query_parameters object and act accordingly. Be careful with data type conversions!

Happy coding

JavaScript, MLE, ORDS