oracle database-centric apis on the cloud using pl/sql and node.js

Paco van der LindenLucas Jellema

Oracle OpenWorld 2016, San Francisco, 22 September 2016

Database-Centric APIs on the Cloud Using PL/SQL and Node.js 

2

Database-Centric APIs on the Cloud using PL/SQL and Node.js 

PL/SQL

APIs

3

Introducing APIs: definition

• API = Application Programmatic Interface• An API is to computer programs what a User Interface is to human users:

the entry point to the exposed functionality of [complex] systems– Understandable, Standards based, Functionally adequate as well as Non-

Functionally,…

The System

A Computer Program

A Human User UI

API

4

Introducing APIs: objective

• APIs are used to– Provide access to specific organization’s IT assets – To specific internal or external stakeholders– In a controlled, well defined manner– That is reliable (runtime) and efficient (design time) for consumers

• APIs allow– Tight operational integration across logistical chains and business processes– Exploitation of enterprise data resources– Interaction through a variety of UIs on a plethora of devices for a multitude of [niche]

user groups

5

SCOTTEMP/DEPT

Introducing APIs: today’s example

• Our company has a sophisticated HRM system• We would like our business partners for payrolling, pensions and health

insurance to be able to integrate our data into their systems– On our terms (non functionally) and their specifications (functionally)

• For internal usage, we are developing a mobile web app with advanced employee insight

The

HRMSystem

APICore

Mobile

B2B

6

API design decisions

• Standards:– Where: URL endpoint– How: HTTP(S)– ‘command’ Language: REST over HTTP– Data format JSON (sometimes XML, CSV)

• Thinking in Resources– As perceived by API consumers – not necessarily the canonical data model of the

enterprise systems– Note: resources are persisted, APIs are stateless (no memory across calls)

• … and operations on Resources– Queries, Resource creation, update and removal

(through REST verbs PUT, POST,… and URL paths and Query parameters)• Message patterns

– Synchronous request/response (bread and butter of HTTP)– Also: subscribe and get published/pushed to

(e.g. WebSockets, Server Sent Events or callback REST API)

7

Non functional API aspects

• In addition to the functional specifications – APIs have to satisfy non-functional requirements– From both the Consumers as well as the Publishing party

• Areas of non-functional requirements include– Security (authorization, encryption)– Transaction management– Response time– Available (and Scalable)– Data quality: Reliable and Fresh– Clarity of API design

• Non-functional facilities– Billing and quota management– Usage analytics

8

API realizationHow to get from zero to an API?

• Design resources (URL paths), operations and message formats (JSON)– Using specification language (RAML, Swagger) – Possibly using tool

• Document, catalog, publish API design– Using Catalog/Repository tool

• Create mock implementation plus unit test and publish

• Start programming against the API (based on design & mock)• Implement the API• Publish the real API

– Possibly using a “gateway” that provides generic facilities (security, caching, load balancing,…)

Client Program

API

9

Our objective

Oracle Database

API

http(s)RESTJSON

10

The app we want to develop

SCOTTEMP/DEPT

The

HRMSystem

APIMobile

Insert screenshots from app

The App will show Employees with details about their Department and their Staff.It supports voting for a new PRESIDENT (to replace KING).It shows the current votes in this presidential election – and when new votes come in, the app refreshes.

11

Designing the API for our case

• Define Resources– List of Employees– Employee – List of Votes in Presidential Election– Vote [in Presidential Election]

• Design HTTP Verb (action) and URL path & parameters– GET /employees– GET /votes– GET /employees/:id– POST /employees/:id/vote– SSE (Server Sent Events) /employees/updates

12

Designing the API for our case

• Choose Message Formats– JSON, XML, CSV, …

• Design Message Structure for [actions on] Resources– Lookout for data types (number) and formats (date, time)

13

Create Formal API Specification

• Using API design formats– Swagger, RAML, API Blueprint

• Using API design and publication tools– Apiary, API Designer, AnyPoint API Platform, Apigee, ..– Oracle API Platform Cloud Service

• From API Design – it seems a small step to API Management

14

Mock Implementation

• Create a Mock Implementation of the API (for example using Node.js and static JSON data sets)– To allow developers who consume the API to start developing– To further and more explicitly specify the design of the API– To allow for unit testing (with mock injection) of the API client applications

• Also create Postman Collections with API test calls & unit tests– Or use any other REST API Client tool

http

Mock Node.js applicationwith static JSON files

15

Test Calling the Mock API

16

Development of the App can start now …

17

Exposing Oracle Database to the outside world

MyDB

PDB demos

SCOTT

TBL DEPT

PL/SQL Packagehttp

18

Exposing Oracle Database to the outside world

MyDB

PDB demos

SCOTT

TBL DEPT

Native DB WebServices

dbms_epg Embedded PL/SQL Gateway

PL/SQL Package

GlassFish /Tomcat /WebLogic

ORDSNode application

oracledbDB driver

WebLogic

SOA Suite & DB Adapter

WebLogic

ADF BC REST

Java SE/EE WebLogic/Tomcat/JBoss

JAX-RS & JDBC

UTL_HTTPhttp requests

DBaaS

MyDB

demos

SCOTTDEPT

Application Container Cloud

ICS

SOA CS

JCS

Oracle Cloud REST APIs

on top of DBaaS

DBaaS

MyJCSDB

demos

SCOTT

DEPT

ICS Agent

ORDS API

Application Container Cloud

ICS

SOA CS

Connection

Integration

Request MappingResponse Mapping

Connection

JCS

ConnectionConnectionConnectionConnection

SCA Composite

data-api

REST API

Service Binding

oracledbDB driver

Java EE AppREST API (JAX-RS) JDBC

DatabaseAdapter

REST APIs on top of DBaaS

dbms_epg

utl_http

21

Add to the cloud mixsome non functional facilities

• API Platform CS– for Governance and Management of APIs

• MCS– exposing APIs specifically for mobile consumption offering mobile facilities such as

analytics, security, enrichment, push notification – also MAX & MAF for mobile app development

• Identity CS – identity management and authentication services

• Management Cloud – IT and Log analytics for real time

monitoring and diagnostics

Identity CS

DBaaSApplication Container

CloudICS

SOA CSJCS

MCS

API[P] CS

Management CS

22

The implementation of choice for today

Application Container Cloud

DockerNode.js Container

data-api

REST API

DBaaS

oracledbDB driver

MyDB

demos

SCOTT

23

Introducing Node.js

• Node.js is a platform for running [server side] JavaScript applications– Asynchronous, event driven and highly scalable– Great for handling HTTP interactions– Can be used as very light weight web/application server– Runs on all major operating systems, easy to install, quick to run

• JavaScript is a popular, rapidly evolving programming language– Very good at processing and producing JSON payloads

• Enormous community support– For JavaScript in general and Node.js in particular – there is a large number of

frameworks and libraries for many different tasks– NPM – node package manager – is tool for installing modules from a large repository

of reusable libraries

24

Mock api impl in Node.js

• Serve static resources• Handle http requests• Return mock responses

– Based on static files

http

Mock Node.js applicationwith static JSON files

Sources in GitHub:https://github.com/pavadeli/oowsession2016

25

NPM Package:node-oracledb database driver

• The node-oracledb driver connects to Oracle Database for fast and functional applications. – Similar for Node.js to what JDBC is for Java applications

• It is an open source project with Apache 2.0 license. • It is maintained as an NPM package by Oracle and is under active

development.• https://github.com/oracle/node-oracledb or

npm install node-oracledb • Support for SQL and PL/SQL, Transaction Management, CLOBs and

BLOBs, Ref Cursors, authentication, H/A features …– Leveraging OCI Oracle (Fat)

Client Libraries

26

Connect to Oracle Database from node application …

var oracledb = require('oracledb');oracledb.getConnection( { user : "SCOTT", password : "TIGER", connectString : "somehost:1521/orcl", }) .then( function(connection) { return connection.execute( "SELECT deptno, dname, loc " + "FROM dept " + "WHERE deptno = :deptno" , { deptno: { val: 30, dir: oracledb.BIND_IN , type: oracledb.NUMBER } } ) .then( function(result) { console.log(result.rows); return conn.close(); }) .catch(function(err) { console.error(err); return conn.close(); }); }) .catch(function(err) { console.error(err); });

27

CORE API in DatabasePL/SQL Package (& View)

• A Core API should be the access point for applications into the database– Encapsulate tables– Hide complex SQL– Allow single round trip access to rich, nested data structures– Inject authorization, logging, journaling, history, multi tenancy, caching, …

• Implementation: PL/SQL Package (and optionally a View)

SCOTT

TBL DEPT

PL/SQL Package

TBL EMP

ViewIO trg

28

CORE API:Package EMPLOYEE_API

SCOTT

TBL DEPT

PL/SQL Package EMPLOYEE_API

TBL EMP

get_employeesreturn

employee_list_t

get_employee(p_id in number) return

employee_t

Type employee_list_t table of

employee_summary_t

Type employee_summary_t

is Object of ()

Type employee_t is Object of ()

Encapsulate: authorization, history,

audit/log, cache, enrich, translate, redact

29

User Defined Type EMPLOYEE_T

30

API function:get_employee

PL/SQL Package EMPLOYEE_API

get_employee(p_id in number) return

employee_t

31

API function:get_employee (2/2)

PL/SQL Package EMPLOYEE_API

get_employee(p_id in number) return

employee_t

32

Interacting with Core API

SCOTT

TBL DEPT

PL/SQL Package EMPLOYEE_API

TBL EMP

get_employeesreturn

employee_list_t

get_employee(p_id in number) return

employee_t

33

Interacting with Core API

SCOTT

TBL DEPT

PL/SQL Package EMPLOYEE_API

TBL EMP

get_employeesreturn

employee_list_t

get_employee(p_id in number) return

employee_t

34The node-oracledb driver does not support user defined objects &

collections• The nested data structure cannot be transfered in the form of an object

[a user defined data type aka ADT or UDT]• Long blocks of text

can be transfered• If we can convert the

data structure intotekst – we are in business

• Two obvious text basedformats are availablefor complex, nested data structures:– XML– JSON

• Node.js has a strong preference for JSON

PL/SQL Package EMPLOYEE_API

get_employeesreturn

employee_list_t

get_employee(p_id in number) return

employee_t

?

35

Converting complex ADT to XML and/or JSON

• Turning Objects and Collections into XML (and back) isnatively supported in Oracle Database– using XMLType(user defined object).getstringval()

to produce VARCHAR2 (or CLOB) with XML based on ADT– XMLType(XML string).toObject(user defined target object variable)

to create an ADT from a text block in [the proper] XML format• Converting between ADT and JSON or between JSON and XML is not

natively supported (up to 12cR1)– The open source PL/JSON library can be leveraged– To get JSON for user defined object (via XML):

JSON_XML.xmlstr2json(XMLType(user defined object).getstringval())– To go from a JSON document to an ADT (via XML):

X (XmlType):= json_xml.json_to_xml( json string, ’TARGET_T’);Xmltype(replace(x.getClobVal(),chr(38)||’quot;’,’’)).toobject(l_adt);

ADT/UDT TypesOBJECTS, NESTED

TABLEXMLType

36

JSON support in user defined types and API wrapper

• An API Wrapper is introduced to expose operations in terms of JSON structures instead of ADTs– The Node.js application only interacts

with this API• Each user defined

object has a to_json() member function– Return varchar2 with

JSON representation ofcomplex data structure

• For large data structures– CLOB can be used– Alternatively:

select text from table( get_emps_as_json)• with get_emps_as_json return a collection

table of varchar2

PL/SQL Package EMPLOYEE_API

get_employeesreturn

employee_list_t

get_employee(p_id in number) return

employee_t

?

PL/SQL Package EMPLOYEE_JSON_API

get_employees_jsonreturn varchar2

get_employee_json(p_id in number) return varchar2

Type employee_t is Object of ()

to_json() return varchar2

Type employee_summary_t is Object of ()to_json() return varchar2

static function to_json_employee_summary_list

( p_emp_list in employee_list_t)

37

to_json member function for user defined type employee_t

Type employee_t is Object of ()

to_json() return varchar2

38

The Database API is done…

• Time to replace the mock implementation in the Node.js application

PL/SQL Package EMPLOYEE_API

get_employeesreturn

employee_list_t

get_employee(p_id in number) return

employee_t

?

PL/SQL Package EMPLOYEE_JSON_API

get_employees_jsonreturn varchar2

get_employee_json(p_id in number) return varchar2

Type employee_t is Object of ()to_json() return varchar2

Type employee_summary_t is Object of ()

to_json() return varchar2static function

to_json_employee_summary_list ( p_emp_list in employee_list_t)

39

Implement the API in Node.js – call out to PL/SQL API

12

3

4

expresshttp

56

7

40

Application Container Cloud

• Oracle Application Container Cloud [Service] aka ACCS– Docker Container configured for Java SE or Node.js – can run a single application

Application Container Cloud

DockerNode.js

Container

Node.js Application

Archive

DockerJava SE

Container

Web App

41

Connect to DBaaS using node-oracledb driver & service binding

Application Container Cloud

DockerNode.js ContainerREST clients

data-api

REST API

DBaaS

Service Binding

oracledbDB driver

MyJCSDB

demos PDB1

HR

42

Application Container Cloud

43

Demo of the API in action

expresshttp

https://data-api-lucasjellema.apaas.em2.oraclecloud.com/employee-api/employees/7782

http://tinyurl.com/hzdf5ta

PL/SQL Package EMPLOYEE_JSON_API

44

Demo of the App on top of the API

http://tinyurl.com/h87fphz

PL/SQL Package EMPLOYEE_JSON_API

45

Push Notifications

• Events in the HRM system – triggered from any one of the many client devices or from a different channel or

background process• May need to be pushed to the API consumer

– So the UI can be refreshed at once• Potentially many consumers are involved

SCOTTEMP/DEPT

The

HRMSystem

APICore

Mobile

46

The event is a voteAn update of the standings is desired

Table presidential_

election

47

Implementing Push Notifications

Push from Node.js to consumers using:

- WebSocket- Server Sent Events (SSE)

- - Native device push

Push from Database to Node.js using:- HTTP call through UTL_HTTP

- Polling (in Node.js) on ‘events’ table- UTL_TCP, Stored Java, …

48

Inside Out option: Database to Node.js push using UTL_HTTP for call out

Table presidential_

election

PL/SQL Package EMPLOYEE_EVENT_HANDLER

utl_http

Insert Trigger on each row

Use dbms_job to make http call on commit

HTTP POST to /employees-api/events

Send JSON message to all SSE clients

49

Alternative: Database to Node.js push using polling from Node.js

Table presidential_

election

PL/SQL Package EMPLOYEE_EVENT_HANDLER

Insert Trigger on each row

Use dbms_job to insert event on commit

Send JSON message to all SSE clients

Table EVENTS_TO_PUSHevent_typeevent_payloadtimestampversion

Scheduled Event Polling

Retrieve events newer than any previously fetched

For each new event: based on type determine what to do [with payload]

Create event record with payload & timestamp

50

Push demo

The

HRMSystem

51

Summary

• APIs are the foundation for tight operational B2B integration, data exploitation and agile UI/UX implementation

• API design (documentation, mocking, testing) comes first– Geared towards API usage (outside in approach)

• Oracle Databases hold valuable enterprise data collections– that APIs can make very good use of

• Exposing database resources through APIs can easily be done– With a Core PL/SQL API – encapsulating the database resources– That is leveraged from Node.js to handle HTTP and transform JSON

• Node.js is great technology for implementing REST APIs– Node.js applications can leverage node-oracledb driver to invoke PL/SQL– Node.js applications can run on Oracle Application Container Cloud Service– Node.js can help implement advanced mechanisms for

security, enrichment, push [SSE, WebSockets] and more

API

Blog: http://technology.amis.nl

Twitter: lucasjellema, Mail: lucasjellema@gmail.com

Twitter: pavadeli Mail: pavadeli@gmail.com