The 2-tier microservice architecture

Implementing a microservice in the database bypasses the middle tier’s complexity. You can code in PL/SQL or JavaScript and deploy on an Oracle Autonomous database. This blog gives a quick example of using JavaScript. See Implementing Microservices in the Database Using PL/SQL for a similar example using PL/SQL.

JavaScript Example

Here is an example:

function getInventory(itemid) {
  try {
    let rows = conn.execute( 
      "select to_char(inventorycount) as invcount from inventory where inventoryid = :itemid", 
      [itemid]
    ).rows;
    if (rows.length > 0) {
      return rows[0][0];
    } else {
      return "invalid inventory item";
    }
  } catch(error) {
    conn.rollback;
    throw error;
  }
}

We create a wrapper procedure in PL/SQL to expose the JavaScript function:

create or replace procedure get_inventory (
  itemid in out varchar2,
  inventorycount out varchar2)
  authid current_user
is
  ctx dbms_mle.context_handle_t := inventory_js.ctx;
begin
  -- pass variables to javascript
  dbms_mle.export_to_mle(ctx, 'itemid', itemid);

  -- execute javascript
  dbms_mle.eval(ctx, 'JAVASCRIPT', 
    'bindings.exportValue("invCount", getInventory(bindings.importValue("itemid")));');

  -- handle response
  dbms_mle.import_from_mle(ctx, 'invCount', inventorycount);
exception
when others then
  raise;
end;

We use a feature called AUTO-PLSQL to deploy as an HTTP service with JSON document inputs and outputs. The input and output JSON attributes are defined by the “in” and “out” PL/SQL parameter definitions. This code deploys the service and names it “getInventory”:

ords.enable_object (
  p_enabled      => true,
  p_schema       => 'INVENTORY_USER',
  p_object       => 'GET_INVENTORY',
  p_object_type  => 'PROCEDURE',
  p_object_alias => 'getInventory'
);

New Workshop

To learn more about building microservices in the database and try it out yourself I would recommend our new workshop: Simplify Microservices with a 2-tier Converged Database Architecture.