oracle database-centric apis on the cloud using pl/sql and node.js
TRANSCRIPT
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: [email protected]
Twitter: pavadeli Mail: [email protected]