Using JSON Schema to validate data submitted to the database via REST Calls

The title’s a mouthful (sorry!), but here’s the core idea: your application exposes REST endpoints through Oracle REST Data Services (ORDS). When a client uses POST to insert new records, the input arrives as JSON since JSON is the data exchange format du jour.

Every developer on the planet knows that you shouldn’t blindly accept input from the outside world without making sure it’s safe to use and adheres to the rules of the application. There are many ways to ensure data sanity, ranging from client-side validations to server-side PL/SQL, JavaScript or Java code.

JSON schema provides an interesting alternative, allowing you to define data integrity rules declaratively. Why is that interesting? For starters, you can use the same technology in your frontend code, middle-tier, and the database. Thankfully Oracle Database is very much up-to-date when it comes to implementing JSON schema, here’s a blog post providing further details. Let’s have a look at an example.

Lay of the land

For the sake of the article, let’s assume the data flows as follows:

A user POSTs a request to ORDS via a React frontend with the intention of inserting a new record in the database
ORDS checks the URI and directs the POST request to the appropriate handler
The handler, written in JavaScript, processes the incoming data using JSON schema validation

Let’s look at each of these in detail, but first, consider the requirements.

Requirements

The database, or rather, the JSON Relational Duality View, expects a JSON in this form for inserts. ActionItem IDs are auto-generated using identity columns, hence it’s not allowed to provide an _id.

			
{
    "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"
        }
    ]
}

		

Each action item has a name and a status. And this is where it gets tricky: some of these fields have strict rules what you can and cannot store.

The status can be one of OPEN, or COMPLETE, case-sensitive.
Each action item requires a team of people working on it.
- There must be at least 1 person in the team.
- Team members have roles: the default role is that of a MEMBER, and there must be at most 1 LEAD.
- Each team member has an assignment ID and a staff ID. These map to their relevant columns in the relational tables the view is defined on and must be provided.

You can implement all of these checks in code, and that’s not even hard in JavaScript, but there’s another way: JSON Schema. Let’s see how you can implement a solution in ORDS using JavaScript handlers to validate incoming JSON using JSON Schema, without using 3rd party software or tools.

ORDS Configuration

The ORDS configuration you’ll see here is referred to as a manual ORDS configuration (-> link to docs) It’s a manual configuration because you are in charge of defining routing, the handler code, security, and everything else. The other option available to you is known as Auto-REST.

Here is the relevant part for the POST handler:

			
declare
    c_module_name       constant varchar2(255) := 'js';
    c_single_pattern    constant varchar2(255) := 'actionItem/:id';
    c_all_pattern       constant varchar2(255) := 'actionItem/';
begin
    ords.define_module(
        p_module_name    => c_module_name,
        p_base_path      => '/v1/',
        p_status         => 'PUBLISHED',
        p_items_per_page => 25,
        p_comments       => 'ORDS handlers written in JavaScript'
    );
 
    -- handle POST operations
    ords.define_template(
        p_module_name    => c_module_name,
        p_pattern        => c_all_pattern,
        p_priority       => 0,
        p_etag_type      => 'HASH',
        p_etag_query     => null,
        p_comments       => 'The POST handler'
    );
    -- create an action item
    ords.define_handler(
        p_module_name    => c_module_name,
        p_pattern        => c_all_pattern,
        p_method         => 'POST',
        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'~
(req, resp) => {
 
    const { createActionItemHandler } = await import ('handlers');
    createActionItemHandler(req, resp);
}    
    ~'
    );
    commit;
 
end;
/

		

Lines 25 to 41 indicate the most important part of this ORDS configration block: the POST handler. It essentially performs a dynamic import of a single core function, createActionItemHandler(). This function accepts the request and response objects, and deals with the input validation.

JavaScript code

Now for the most interesting bit, well, at least in the scope of this article: the actual POST handler. It’s written in MLE/JavaScript. Let’s begin with the JSON Schema definition. actionItemInsertSchema is a global variable in this JavaScript module.

			
// this is what the input must look like _for inserts. An ID for
// the new action item cannot be provided, it is auto-generated via
// an identity column.
const actionItemInsertSchema = {
    $schema: "https://json-schema.org/draft/2019-09/schema",
    type: "object",
    // actionName, status and team are required fields. Should they not be
    // present, the insert will be rejected.
    required: ["actionName", "status", "team"],
    // no additional fields are allows, apart from the ones listed above
    additionalProperties: false,
    // a list of actionName, status, and team field properties
    properties: {
        actionName: { type: "string", minLength: 10 },
        status: { type: "string", enum: ["OPEN", "COMPLETE"] },
        // team is not a scalar, it's an array... and there has to be
        // at least 1 team member
        team: {
            type: "array",
            minItems: 1,
            // ... and at most 1 LEAD in the team array
            contains: {
                type: "object",
                properties: { role: { const: "LEAD" } },
                required: ["role"]
            },
            maxContains: 1,
            // this part of the schema description describes what a team
            // member object must look like. It follows the same logic
            // as the top-level requirements.
            items: {
                type: "object",
                required: ["role", "staffName", "staffId"],
                additionalProperties: false,
                properties: {
                    role: { type: "string", enum: ["LEAD", "MEMBER"] },
                    staffName: { type: "string", minLength: 5 },
                    staffId: { type: "integer", minimum: 1 }
                }
            }
        }
    }
};

		

This innocent looking JavaScript object allows you to enforce all the requirements laid out earlier. The comments in the code should explain what you’re seeing.

Let’s continue with the actual handler function:

			
export function createActionItemHandler(req, resp) {
    resp.content_type('application/json');
    // let's validate the submitted JSON first. This code block uses
    // the PL/SQL Foreign Function Interface.
    const dbmsJSONSchema = plsffi.resolvePackage('DBMS_JSON_SCHEMA');
    const result = plsffi.arg();
    const errors = plsffi.arg();
    dbmsJSONSchema.is_valid(
        req.body,
        actionItemInsertSchema,
        result,
        errors
    );
    if (! result.val) {
        resp.status(400);   
        resp.json(errors.val);
        return;
    }
    const actionItem = req.body;
    // remaining code, simply inserts into the view and gets the
    // object back. Which it then submits to the response object
    try {
      const data = createActionItem(actionItem);
      resp.status(201);
      resp.json(data);
    } catch (err) {
      // in case things go wrong
      resp.json({
            message: "an unexpected error occurred",
            details: err.message
        })
        resp.status(500);
    }
}

		

The logic within this function is simple: using the PL/SQL Foreign Function Interface (plsffi) it’s possible to make DBMS_JSON_SCHEMA appear as if it were a JavaScript object. Its is_valid() function accepts the (input) JSON, the JSON schema defined earlier, and provides the result of the validation in a boolean (result) as well as an optional JSON listing all the errors. PL/SQL OUT parameters don’t exist in JavaScript, therefore MLE employs a little trick: you can see result.val in line 17, containing the boolean value. Don’t simply use result, a comparison against it will return undefined and that’s definitely not what you want. In case of any schema validation errors, a bad request (= HTTP code 400) is returned, including the list of errors detected to the caller. Just as result, errors is an out bind, therefore you have to use errors.val to access the actual value.

If everything went fine, createActionItem() is invoked, performing the insert operation. The newly generated record is returned to the caller, including the auto-generated _id.

In case of any unexpected database errors an internal server error (HTTP code 500) is reported back, and that’s the end of the story.

Summary

JSON Schema is an emerging standard, still not quite complete, but very useful for data validation. Oracle AI Database 26ai has very strong support for JSON Schema validations, and, as you can see, using that in PL/SQL and/or JavaScript is very straight forward. Even though JSON is schema-flexible, making sure your application only receives those data it needs, is always worth the extra effort. Everyone in your team will thank you later.

Happy coding!

JavaScript, JSON, MLE