by John Hendrix
This two-part series shows how to use Sails.js—which is model-view-controller (MVC) framework for Node.js—with Oracle Database. This article, which is the first part of the series, introduces sails-oracle-sp, which is a Sails.js adapter, and discusses how to use it with new and legacy Oracle Database schemas. The second part of the series is a full working example of using sails-oracle-sp.
Table of Contents
Introduction
The Sails.js Technology Stack's Theory of Operation
The Inner Workings of Selected Subsystems
A Review of the Sails.js Stack
Stored Procedure Design Considerations
Conclusion
About the Author
Introduction
When Sails.js is used with the sails-oracle-sp adapter and with the Oracle Database node-oracledb adapter, it puts Oracle Database stored procedures on the internet as REST APIs and WebSockets. (Node-oracledb is maintained by Oracle. Sails.js is maintained by Balderdash Design Company and The Treeline Company. Sails-oracle-sp is maintained by Nethoncho, LLC.)
REST APIs and WebSockets are important basic elements of internet-based applications because
- REST APIs are the internet's industry-standard interface. The technique described in this series provides the lowest-level effort that I know of for augmenting Oracle Database stored procedures with REST APIs.
- Web applications can receive real-time data (for example, stock tickers, updating progress bars, chat, notifications, and so on) via HTML5 WebSockets.
- Encapsulating all SQL in stored procedures provides a separation of concerns by eliminating embedded SQL from the middleware.
- Implemented correctly, this technique reduces the possibility of SQL injection exploits.
Sails.js is a Node.js framework that automatically exposes both REST APIs and WebSocket endpoints for each Sails.js data model. (Sails.js data models are discussed later in this article. For now, just assume each data model maps to a set of PL/SQL stored procedures, where each set of stored procedures provide CRUD operations on a particular database table. Each table has its own set of CRUD stored procedures.)
The WebSocket protocol provides a full-duplex communication channel over a single TCP connection from a client, say, a web page or a smartphone app, to a sever, say, a server located in the cloud. WebSocket connections are particularly compelling because they enable notifications to be pushed to subscribing clients without committing the client to continuously polling the server. Consequently WebSockets are a natural way to implement real-time features such as instant messaging, chat, and dashboards that monitor alarms, and for presenting telemetry.
Sails-oracle-sp works by using information obtained from the Sails.js data model to automatically generate anonymous blocks suitable for invoking Oracle stored procedures.
The Sails.js storage and retrieval engine, Waterline, presents a uniform interface to Sails.js applications and uses middleware, called adapters in the Sails.js' documentation, to interface Waterline to various datastores. Waterline conceptually handles schema-based datastores (that is, an RDBMS) as a set of tables. Put crudely, Waterline converts calls from Sails.js applications into SQL, invokes the SQL on the database, and returns the result to the client. Supported datastores include Redis, MySQL, LDAP, MongoDB, and PostgreSQL. Oracle's node-oracledb adapter adds Oracle Database to this list of datastores.
The sails-oracle-sp adapter, with node-oracledb, adds the means whereby Sails.js applications can invoke Oracle Database PL/SQL stored procedures. Sails-oracle-sp is limited to calling procedures exposed by a PL/SQL package. Sails-oracle-sp cannot directly conduct Data Manipulation Language (DML) operations on tables.
Because Sails.js is a major framework, a Sails.js tutorial is beyond the scope of this article. This article provides
- A basic overview of the Sails.js technology stack and where sails-oracle-sp and node-oracledb fit within the Sails.js ecosystem
- The sails-oracle-sp adapter's theory of operation
- A description of how sails-oracle-sp requires the model specification to be designed
- The stored procedures naming scheme required by sails-oracle-sp
About Sails.js
Sails.js is a real-time framework for Node.js that is based on the "express" application framework and on Socket.io. Sails.js is a back-end, MVC framework that automates the creation of REST APIs, WebSocket interfaces and views for datastores.
Sails.js is datastore-agnostic; it relies on middleware called "adapters" to interface Sails.js to the various datastores. Supported datastores include Oracle Database.
Sails.js generates a model, a view, and a controller for each datastore object under its management. Because Sails.js models and controllers have a table-centric frame of reference, they conceptually treat the modeled object as a database table. Sails.js generates both REST APIs and WebSockets for each database table.
Sails-oracle-sp adapter's purpose is to rig Sails.js to act on PL/SQL packages instead of tables. This series includes a scheme for adapting the Sails.js table-centric frame of reference for use with sails-oracle-sp and stored procedures.
About sails-oracle-sp
The sails-oracle-sp node module is an Oracle Database Sails adapter. It provides the means whereby a Sails.js application can access Oracle Database and invoke stored procedures.
Ideally a system comprises a hierarchy of loosely coupled code modules that are organized so as to enable the system to achieve its goals. (In this paragraph, the word module is alluding to code that presents well-defined interfaces, is internally cohesive, provides encapsulation, and provides a separation of concerns—not to a node package module [NPM].) Sails-oracle-sp is important because it supports transforming an Oracle Database database, as part of a Sails.js application, into just another module in an ideal system.
Node-oracledb's support of both stored procedure calls and reference cursors is essential to sails-oracle-sp's ability to present an Oracle Database database to the rest of the system as just another module. Stored procedures create API interfaces that encapsulate internal database logic. Reference cursor parameters provide a means for obtaining information from the database without involving embedded SQL queries or the middleware having any knowledge of the database's table topology.
Legacy Oracle Database Considerations
Stored procedures called by sails-oracle-sp must follow a naming convention described later in this article. Sails.js imposes this constraint.
Existing production Oracle Database databases cannot be expected to be provisioned with stored procedures that already follow the sails-oracle-sp required naming convention. Modifying the names of existing stored procedures is likely to be a poor solution, because these stored procedures will already have production clients. Modifying names risks destabilizing a production system.
This article proposes a solution for adding an interface based on Sails.js, sails-oracle-sp, and node-oracledb to an existing production Oracle Database database without the risk of disturbing it: implementing an interface package. Implementing an interface package is described later in this series.
Of course a new Oracle Database database that has Sails.js, sails-oracle-sp, and node-oracledb as a technology objective will not need an interface package because, by design, its stored procedures will be given suitable names.
This series refers to existing production systems as legacy databases. Legacy databases will require the implementation of an interface package. This series refers to newly designed systems that will not require an interface package as new databases.
The Sails.js Technology Stack's Theory of Operation
Figure 1 shows the Sails.js technology stack when used with sails-oracle-sp. Sails-oracle-sp and node-oracledb are Node.js packages the developer (you) must install. You write the code for the gray items in this diagram: the model and the PL/SQL interface package.
Figure 1: Sails.js technology stack
Client Interface Layer
Sails.js presents both a WebSocket interface and an HTTP interface to clients. The HTTP interface is a collection of REST APIs. Listing 1 shows a possible REST API. Clients convey additional information via an HTTP entity-body similar to the one shown in Listing 2.
http://localhost:1337/Employees
Listing 1: Employees REST API presented by Sails.js
{
"firstName": "John",
"lastName": "Doe",
"email": "JDOE"
}
Listing 2: Example HTTP entity-body for Employees REST API call
Routes Layer
Requests entered by clients at either the WebSocket interface or REST APIs converge at the _routes laye_r. Sails.js has a controller and model pair for each database table. Multiple database tables require multiple controller/model pairs. (A stored procedure masquerades as a database table in the sails-oracle-sp scheme.) The routes layer determines which table's controller should receive the client's request. Ultimately, the routes layer controls which stored procedure is called.
The Model
The Sails.js model is the means whereby you describe database table details for Sails.js. Sails.js is capable of conducting DML operations on a database table (for example, inserts, deletes, and so on) after its details are specified in a model.
If Sails.js is used with sails-oracle-sp, then the model is instead used to specify stored procedure names, the parameters' names, and the parameters' datatypes. The sails-oracle-sp adapter uses the model for two different functions:
- Selecting the correct types when constructing a stored procedure call
- Selecting the correct types when parsing a result set obtained from a stored procedure
You design the model. The mechanics of designing a model are described later in this series.
The Controller Layer
The controller analyzes the message received from the routes layer and determines which one of its adapter methods is to be called. In this case, the adapter is the sails-oracle-sp adapter.
The Sail model adapters expose four methods to the controller that correspond to REST verbs. Table 1 shows this correspondence. The controller calls the model's method that matches the REST verb.
Table 1: Sails.js adapter methods with respect to REST verbs
| Adapter Method | REST Verb | Parameter Classes | Effect |
| create() | POST | Values | Adds a row to table |
| find() | GET | Options | Queries data from a table |
| update() | PUT | Options and values | Modifies data in a table |
| destroy() | DELETE | Options | Deletes rows from a table |
The controller passes to the adapter method the HTTP entity-body (that is, the information transferred as the payload of a HTTP request) provided by the client in the call. The controller also provides the options, if any, with the call.
Values comprise the actual values that are destined to be stored in a table's column via a POST or PUT operation.
Options specify which row or rows are to be affected by the REST operation. In the case of a DELETE, for example, the option is just the ID of the record to be destroyed. In the case of a GET, it might specify the range of rows to fetch. In the case of a PUT, it will specify the record that is to be modified. No options are provided to a POST operation, because options pertain to existing records and a POST operation is for creating a new record.
The sails-oracle-sp Adapter
The sails-oracle-sp adapter is a Sails.js model adapter. Most Sails.js model adapters operate on database tables (scenario: RDBMS), documents (scenario: MongoDB), data structures (scenario: Redis), and local files on the disk. The Sails-oracle-sp niche is operating on Oracle Database packages instead of tables.
Sails-oracle-sp exposes the four adapter methods shown in Table 1. The controller passes the HTTP entity-body's JSON provided by the client to sails-oracle-sp via these adapter methods. Sails-oracle-sp constructs a stored procedure call, populates the parameters from the entity-body's JSON, and delegates its execution to node-oracledb.
Sails-oracle-sp mechanics are described later in this article.
Supported Parameter Datatypes
Sails-oracle-sp supports any PL/SQL parameter datatypes that node-oracledb supports. Sails-oracle-sp simply passes parameters it receives through to node-oracledb without any manipulation. Essentially, if node-oracledb can handle a parameter datatype, then sails-oracle-sp should handle it too.
Node-oracledb Large Object (LOB) support includes Binary Large Objects (BLOBs) and Character Large Objects (CLOBs), which are implemented as a Node.js Stream interface. I have not tested sails-oracle-sp with LOBs.
The node-oracledb Driver
Node-oracledb is a Node.js Oracle Database driver. The node-oracledb driver invokes stored procedures and executes SQL queries for its clients.
Important aspects of node-oracledb include
- Invoking PL/SQL stored procedures
- Returning REF CURSORs as ResultSets
- Returning exceptions
Obviously, invoking stored procedures is essential to sails-oracle-sp. REF CURSOR support is essential to sails-oracle-sp's ability to retrieve arbitrary queried information without requiring you to write custom code. Instead sails-oracle-sp requires you only to implement a model specification that instructs sails-oracle-sp how it should handle the ResultSet. Node-oracledb returns the ResultSet as a JavaScript object, which is passed up, via the call stack, to the client.
Interface Package
Sails-oracle-sp operates on a PL/SQL interface package containing stored procedures with names and parameters consistent with the models. The interface package is an interface layer that encapsulates an arbitrarily complicated database and presents a simpler, uniform interface.
This series assumes a pre-existing production database for which a new Sail's-based front end is to be constructed. The intent of the interface package is to provide a suitable interface to Sails.js without disturbing a working "legacy" production system.
The following is an example of an interface package and its stored procedures. For the purposes of brevity, this example will oversimplify a few details. Details of the example will be explored later in part 2 of this series.
Consider for example Oracle's sample HR schema. (Oracle Database's sample HR schema can be obtained from https://github.com/oracle/db-sample-schemas.) The HR schema contains an EMPLOYEES table, among others. In Sails.js, each table has its own model. When used with sails-oracle-sp, a table's model specifies the stored procedures' family name.
Put another way, a stored procedure's family name specifies the base name for at least four different stored procedures in the interface package. This series calls this group of stored procedures a stored procedure family because taken together, they support the various CRUD actions (Create, Read, Update, and Delete) on a particular table. Polymorphism enables a stored procedure family to have more than four stored procedures. The stored procedure family concept will be discussed more later.
Table 2 shows the stored procedures for the EMPLOYEES table. In this case, the stored procedures family name is "employees." The stored procedure families for the other tables' models are also in the same interface package.
Table 2: HR interface package's stored procedures for the EMPLOYEE table. The sails-oracle-sp's adapter method maps to a particular stored procedure. The string "employees" in the stored procedure column is the family name for this group of stored procedures.
| Adapter Method | Stored Procedure | REST Verb | Purpose |
| create() | employees_c | POST | Insert employee into the EMPLOYEE table |
| find() | employees_r | GET | Obtain employee's details |
| update() | employees_u | PUT | Update employee's details |
| destroy() | employees_d | DELETE | Remove employee |
Based on the REST verb involved in a client's request, the controller invokes one of the four sails-oracle-sp adapter methods and passes the HTTP entity-body's JSON to it. Sails-oracle-sp maps each adapter method to a specific stored procedure in the interface package. The prefix of the stored procedure names is obtained from the model. The trailing letter and underscore is appended by the adapter method. The stored procedure's parameters are derived from the HTTP entity-body's JSON. This all works together to enable sails-oracle-sp to generate stored procedure calls without requiring you to write custom code.
The Inner Workings of Selected Subsystems
This section examines the Sails.js model, sails-oracle-sp, and the interface package in closer detail. The intent is to give you sufficient understanding so you can effectively use sails-oracle-sp.
The Sails.js Model
Sails.js generates a model for each datastore object under its management. In the case of an RDBMS, these objects are tables. A Sails.js model describes a table's details, such as its name, and details pertaining to its columns. The model works together with the Sails.js query language and the datastore's adapter to enable DML operations on an RDBMS table. Sails-oracle-sp fits into this scheme by being a Sails.js datastore adapter that enables an appropriately designed set of PL/SQL stored procedures to masquerade as an RDBMS "table." These stored procedures are located in the interface package.
Because these stored procedures appear to Sails.js to be an RDBMS table, they must support table semantics. Specifically, these stored procedures must support the four CRUD operations and either operate on an actual table or invoke other stored procedures that ultimately operate on a table.
The interface package can host stored procedures for multiple tables. Each table has its own dedicated set of CRUD stored procedures.
Mechanics of the Sails.js Model
You can cause Sails.js to generate a default model file via a command-line interface. Model description files are located in the myApp/api/models directory. The model file generated by Sails.js is an empty skeleton. You must add details regarding the stored procedures and their parameters.
Because Sails.js models and controllers have a table-centric frame of reference, they conceptually treat the modeled object as a database table. So Sails.js model names are conceptually table names. This design doesn't fit sails-oracle-sp well because sails-oracle-sp operates on stored procedures instead of on tables. When using sails-oracle-sp, each model is typically associated with a set of CRUD stored procedures instead of with one table.
Sails-oracle-sp resolves these contradictions by repurposing various model properties. It repurposes the table name as the stored procedure family name. It repurposes the model's attributes property (which was intended to specify table column names) as stored procedure parameter names.
By default Sails.js adapters use these model properties to automatically generate SQL for DML operations on database tables. In contrast, sails-oracle-sp uses these model properties when automatically generating anonymous blocks for calling a CRUD stored procedure.
Sails.js Model Properties
Initially the model file is a file containing an empty skeleton of a model. The model itself is a JavaScript object for a particular table that specifies the table's name and describes its columns' properties. Normally you fill out the model's details by specifying the table's various properties. In contrast, when working with sails-oracle-sp, you specify the aggregate properties of the set of CRUD stored procedures for a given stored procedure family. These "aggregate properties" are composed of
- The stored procedure family name
- A list composed of the combined parameter names for all of the stored procedures in the stored procedure family
As an example, Listing 4 shows a fragment of a model for the HR employee stored procedure family.
Because the parameter names for these stored procedures map to the table's underlying columns, it might be simplest to derive each parameter name from its underlying table's column name. That said, there is no obstacle to selecting parameter names that are more meaningful than the original column names. Listing 9 shows the parameter naming convention.
The model is used in different ways by two clients:
- The model instructs Sails.js as to which columns are available to be used when calling a given stored procedure.
- Sails-oracle-sp uses these column names to generate the parameter names used within stored procedure calls.
The model's syntax is table-centric because a Sails.js model is intended to describe database tables. Consequently to use the model for sails-oracle-sp purposes, you must treat stored procedure parameters as though they were columns in a database table. The stored procedure column in Table 2 shows both:
- An example of the CRUD stored procedure names for the HR schema's EMPLOYEE table
- The specific CRUD stored procedure that will be called for a given REST API verb
Table 3 shows how sails-oracle-sp repurposes Sails.js model properties. Listing 3 shows a templatized example of how these properties might appear in a model's attributes object literal.
Table 3: How sails-oracle-sp repurposes Sails.js model properties.
| Property | Sails.js Original Usage | Sails-oracle-sp Usage | Mandatory? | Commentary |
| <parameterName> | Specify table column. | Specify stored procedure parameter name. | Yes | Parameter names must not be duplicated for a given stored procedure family. |
| type | Specify column data type. | Specify parameter data type. | Yes | The only types supported by sails-oracle-sp are string, number, and integer. |
| autoIncrement | True indicates that table column will automatically generate the next value. | Indicates that the stored procedure will automatically generate the next value. | No | Set to true for values generated by an Oracle Database sequence or created by the stored procedure. Setting to true prevents Sails.js from attempting to provide a unique value. |
| primaryKey | True indicates column is necessary to select a specific row in the table. | True indicates that parameter specifies a particular record in the underlying table. | No | There is only one primary key per table. |
| unique | True indicates that the table has a unique key on this column. | True indicates that the underlying table has a unique key on this column. | No | |
| required | True indicates that column's value cannot be defaulted. | True indicates that this parameter is used by every stored procedure in this stored procedure family. | No | Set to false if any family stored procedure omits this parameter. Set to true if every family stored procedure uses this parameter. |
Listing 3 is a model file template that shows how sails-oracle-sp repurposed various properties. It also shows some likely optional values for various properties.
attributes: {
<1st parameterName>: {
type: <string|integer|number>,
autoIncrement:<true|false>,
primaryKey:<true|false>,
unique:<true|false>
},
<2nd parameterName>: {
type: <string|integer|number>,
autoIncrement:<true|false>,
primaryKey:<true|false>,
unique:<true|false>
required: <true|false>
}
// and so on
}
Listing 3: A templatized example of a model file fragment. The attributes property specifies the table columns' characteristics.
Listing 4 shows an example of a model file. The model itself follows standard NPM structure in that it is composed of a JavaScript object literal assigned to module.exports. It contains database connection details used by Sails.js. The attributes object specifies details pertaining to the stored procedure family's parameters.
Each of the object literals within the attributes object describes a stored procedure parameter from the models' stored procedure family. The name of this property is parameterName, as shown in Listing 3.
module.exports = {
tableName: 'EMPLOYEES',
schema: true,
connection: 'oraclehr',
autoCreatedAt: 'false',
autoUpdatedAt: 'false',
// The attributes object is an array of stored procedure
// parameter details
attributes: {
id: {
columnName: 'EMPLOYEE_ID',
type: 'integer',
autoIncrement: true,
primaryKey: true,
unique: true
},
firstName: {
columnName: 'FIRST_NAME',
type: 'string',
required: false
},
// additional stored procedure parameters redacted
}
};
Listing 4: A fragment of a Sails.js model for the HR schema's EMPLOYEE table.
Table 3 showed the details for the parameter's properties. Table 4 shows the recommended values for other properties used in the model.
Table 4: Model attributes with recommended values
| Model Properties | Recommended Value | Commentary |
| tablename | Stored procedure family name | tablename specifies the family namespace. |
| schema | True | Only non-relational datastores have this as false. |
| connection | The database connection name specified in connections.js | This is the configured database connection specified in myApp/config/connections.js. |
| autoCreatedAt | False | This is a flag to toggle the automatic definition of a createdAt attribute in your model. |
| autoUpdatedAt | False | This is a flag to toggle the automatic definition of an updatedAt attribute in your model. |
| attributes | Array of JavaScript objects specifying parameter details | The attributes object property name is an array of JavaScript objects specifying parameter details. |
| columnName | Optional parameter that is needed only if <parameterName> does not match an actual parameter name | Discussed in Table 3 |
| <parameterName> | Name of one of the stored procedure's parameters | Discussed in Table 3 |
| type | Select a type consistent with the stored procedure parameter | Discussed in Table 3 |
| autoIncrement | Discussed in Table 3 | Discussed in Table 3 |
| unique | Discussed in Table 3 | Sails-oracle-sp needs an indication that the parameter value is unique. Discussed in Table 3. |
| required | Set to false if any family stored procedure omits this parameter. Set to true if every family stored procedure uses this parameter. | Not every parameter is used in every stored procedure within a given family. Set to false the parameters that aren't used with every stored procedure in a family. Discussed in Table 3. |
Subscribing to WebSockets
A route is a system for resolving all requests from clients to a resource that is suitable for servicing the request. Both REST APIs and WebSockets involve routes because they interconnect a client from an exposed interface to a specific controller.
WebSocket-enabled clients can subscribe to Sails.js resources. In WebSocket jargon, the act of subscribing is called "subscribing to a room" because, apparently, one of the primary uses of WebSockets is implementing chat rooms. A WebSocket client would use the interface shown in Listing 5 to subscribe to the employees room.
Sails.js exposes a common WebSocket endpoint to which a client can connect. A connection to this WebSocket endpoint is directed to one of the various Sails.js controllers by a route.
WebSocket rooms have a lifecycle. A WebSocket room is created when the first client connects to it. Afterward, additional clients can subscribe to the existing WebSocket room. The WebSocket room is destroyed after the last client terminates its subscription. Sails.js can support multiple WebSocket rooms. Each Sails.js controller can support a WebSocket room. A client can concurrently subscribe to multiple Sails.js WebSocket rooms.
A client becomes subscribed to a specific WebSocket room by issuing a GET, via a WebSocket, to that room's route. When Sails.js changes a table's data, Sails.js will emit notifications to all clients subscribed to the WebSocket room affiliated with that table's model.
Processing RESTful Calls
Clients call REST interfaces exposed by Sails.js. Table 1 shows the REST verbs supported by a Sails.js REST interface. Listing 5 shows the Employees REST API generated by the model shown in Listing 7.
http://localhost:1337/Employees
Listing 5: Employee REST API presented by Sails.js
In the case of the Employees REST API, each of the REST verbs shown in Table 2 maps to a particular stored procedure. Each of these stored procedures is specialized for modifying Oracle Database's sample HR schema's EMPLOYEE database table in the manner specified by its REST verb. For example, a POST call to the Employees REST API will invoke the stored procedure that adds a new employee record to the EMPLOYEE database table. Each of the other REST verbs maps to its own stored procedure.
The information the caller must provide for the various REST API calls varies. GET calls and DELETE calls simply require the record's primary key. In contrast, the stored procedures invoked by the POST call or the PUT call have multiple parameters—one parameter for each column in the associated database table that will be affected by the stored procedure. Both POST and PUT calls operate similarly. For brevity, the remaining description discusses only the POST call's mechanics. The PUT call's mechanics are the same.
The client passes the data required by the various stored procedure parameters via a JSON document called an HTTP entity-body. The entity-body is an optional payload in an HTTP message. Sails-oracle-sp maps the parameter data passed in the entity-body to the stored procedure's parameters.
A POST call to the Employees REST API would include an entity-body containing JSON that looks something like the JSON in Listing 6. Note that each JSON property name in Listing 6 matches an Employee model's parameter name in Listing 7. For example, the property name firstName in Listing 6 matches the Employee model's parameter name firstName in Listing 7, and so on. This entity-body JSON is delivered to the controller layer for further processing.
{
"firstName": "John",
"lastName": "Doe",
"email": "JDOE",
"phoneNumber": "800-555-1212",
"hireDate": "2014-07-07T04:00:00.000Z",
"jobId": "AD_PRES",
"salary": 24000,
"commission": 0.3,
"managerId": 100,
"departmentId": 90
}
Listing 6: JSON body for POST Employees REST API call
module.exports = {
tableName: 'EMPLOYEES',
schema: true,
connection: 'oraclehr',
autoCreatedAt: 'false',
autoUpdatedAt: 'false',
attributes: {
id: {
columnName: 'EMPLOYEE_ID',
type: 'integer',
autoIncrement: true,
primaryKey: true,
unique: true
},
firstName: {
columnName: 'FIRST_NAME',
type: 'string',
},
lastName: {
columnName: 'LAST_NAME',
type: 'string',
},
email: {
columnName: 'EMAIL',
type: 'string',
},
phoneNumber: {
columnName: 'PHONE_NUMBER',
type: 'string',
},
hireDate: {
columnName: 'HIRE_DATE',
type: 'string',
},
salary: {
columnName: 'SALARY',
type: 'float',
required: false
},
commission: {
columnName: 'COMMISSION_PCT',
type: 'float',
required: false
},
managerId: {
columnName: 'MANAGER_ID',
type: 'integer',
},
departmentId: {
columnName: 'DEPARTMENT_ID',
type: 'integer',
}
}
};
Listing 7: Sail's model for Oracle Database's sample HR schema's EMPLOYEE table
Adapting Stored Procedures to the Sails.js Model
Stored procedures need to be adapted to the Sails.js model's constraints. The model affects how how the stored procedures and their parameters are named. The model affects how the REFCUROR ResultSets returned by these stored procedures are passed back to the client. The following discusses the mechanics of how the stored procedures must be organized so as to be callable via Sails.js/sails-oracle-sp/node-oracledb.
Regarding Stored Procedure Families
The stored procedure family name adapts Sails.js' one-table-per-model paradigm by repurposing the tableName attribute such that it specifies the family's base name. The family name creates a namespace for the module's CRUD stored procedures. Listing 8 shows the stored procedure naming scheme.
employees_c()
Listing 8: Stored procedure family naming pattern
The prefix of the stored procedure is the family name. The family name is specified by the model. The CRUD indicator is automatically appended to the family name part of the stored procedure name by the sails-oracle-sp's adapter method. The create() adapter method automatically appends _c, the read() adapter method appends _r, and so on, as shown in Table 2.
Regarding Specifying Stored Procedure Parameters
Because Sails.js models and controllers are concerned with operating on database tables, the model attributes specify details in terms of a table's columns. Sails-oracle-sp has repurposed table column names as stored procedure parameter names. When used with sails-oracle-sp, the model's attribute object is an array of stored procedure parameters.
The model has no means to associate a particular parameter with a particular stored procedure. Consequently the array of stored procedure parameters is a union of all of the parameters of all of the stored procedures in a stored procedure family. No stored procedure parameter is duplicated in the model.
Sails-oracle-sp enforces a parameter naming convention that prefixes p_ to the leading edge of parameter names. Listing 9 shows an example of this naming convention. Sails-oracle-sp automatically prefixes the parameter name with p_ when constructing a stored procedure call.
p_last_name
Listing 9: The parameter naming convention
When processing a client's request, sails-oracle-sp will attempt to match the attribute names in the entity-body's JSON with the model's parameter objects' attribute names. When sails-oracle-sp finds a match, it uses the parameter object details to generate a stored procedure call that uses named notation syntax with the correct datatype. Listing 12 shows an example of a call generated by sails-oracle-sp.
The model can also specify renaming of parameter names. Sails-oracle-sp will use the parameter's columnName, if it is available, as the name of the stored procedure's parameter base name when constructing the stored procedure call. If the columnName attribute is unspecified, sails-oracle-sp will fall back to using the parameter object's attribute name as the stored procedure's parameter's base name.
Parameter renaming is generally necessary. The caller provides values to be passed to a stored procedure via JSON. In the JSON object, the parameter names are in camelCase because Google specifies that JSON property names "must be camel-cased, ASCII strings." Put another way, JSON prohibits snake_case (that is, using underscores to separate elements in a name).
On the other hand, Oracle Database identifiers (for example, stored procedure parameters) are generally in snake_case because, by default, Oracle Database handles identifiers in a case-insensitive manner and automatically presents identifier names in all uppercase. Consequently Oracle Database identifiers are customarily in snake_case because the identifiers will still readable after being shifted into all uppercase.
Consequently, the controller must rename the parameter names specified in the JSON object into the parameter names actually used by the stored procedure. The model's renaming feature specifies how a property's name is to be changed to match the underlying stored procedure's parameter name. (During development, I noticed certain browsers malfunctioned when I attempted to pass snake_case in the HTTP entity body's JSON. Don't try to get away with using snake_case.)
Listing 11 is an example of an entity body for an EMPLOYEE REST API call, with a property name, firstName, that does not match any stored procedure parameter names. Listing 10 is an example of a model that instructs sails-oracle-sp how to rename this property so that it matches a valid parameter name. Listing 12 is an example of the resulting stored procedure call sails-oracle-sp would generate.
// firstName is the name of this parameter object.
// Because the object has an explicit columnName attribute
// the parameter's base name is "FIRST_NAME"
firstName: {
type: 'string',
columnName : 'FIRST_NAME'
},
// In this case the there is no columnName attribute.
// Sails will default to using the parameter object's name,"email",
// as the parameter's base name
email: {
type: 'string'
},
salary: {
type: 'float'
},
Listing 10: A fragment of a model showing two ways to specify a pstored procedure's parameter name in a Sails.js model
Listing 12 shows the stored procedure call that sails-oracle-sp would construct for the client request shown in Listing 10.
{
"firstName": "John",
"lastName": "Doe",
"email": "JDOE",
"salary": 24000
}
Listing 11: Example entity-body for Employees REST API call
hr.employees_c(
p_first_name => 'John',
p_last_name => 'Doe',
p_email => 'JDOE',
p_salary => 24000
);
Listing 12: An example of a call to employee_c() using named notation. The reference cursor parameter is omitted for clarity.
Processing the Outcome of the Stored Procedure Call
Sails-oracle-sp passes the stored procedure call to node-oracledb for execution. Afterward, node-oracledb passes the result back to sails-oracle-sp.
After sails-oracle-sp receives the result of the stored procedure call back from node-oracledb, it constructs a JSON object to be returned to the client. This JSON object is constructed from the ResultSet returned by the stored procedure.
Sails-oracle-sp analyzes the ResultSet returned via a reference cursor by conducting string matching. Sails-oracle-sp matches model columnNames with PL/SQL record fields so as to determine which returned data belongs to a given parameter. Sails-oracle-sp is oblivious to the actual datatype of this data; it just blindly uses this data to construct the JSON object. Afterward sails-oracle-sp sends the newly constructed JSON object to the client via the controller.
Sails.js requires the create(), update(), and read() model adapter methods to return some data pertaining to the specified record. There is no such requirement for the destroy() method.
The create() and read() model adapter methods return the JSON object described above. The only mandatory field to be returned in this JSON object is the primary key, which identifies the particular record involved. The other possible fields in the JSON object are optional.
Exception Handling
One of the very cool things that the team that developed node-oracledb accomplished is the seamless conversion of exceptions raised by Oracle Database into JavaScript exceptions. Node-oracledb catches Oracle Database exceptions and then throws them as JavaScript exceptions. These JavaScript exceptions then propagate smoothly into the Node client (which in this case is Sails.js) where they can be caught by its exception handling code. This is very cool.
The mechanics are as follows: node-oracledb captures an exception raised by Oracle Database and then throws it as a JavaScript exception. This JavaScript exception contains the exception's ORA number. Sails-oracle-sp catches this exception, reformats the exception data per Sails.js' requirements, optionally adds an error message, and rethrows the exception. Sails.js catches this exception and propagates it to the client as an HTTP 400 status code. The exception's text message, if provided, is conveyed via the HTTP status code's message-body. The middleware is completely oblivious to any exception's particular nature; the exception is just processed and passed upward toward the client.
Sails-oracle-sp is capable of adding the ORA error message to the exception. This requires implementing the stored procedure return_codes_r(). Listing 13 shows an example implementation of return_codes_r(), which returns a result set composed of the ORA error codes and their error messages. Sails-oracle-sp caches these error messages on startup. If you provide this stored procedure, when Oracle raises an exception, Sails.js will return an HTTP 400 status code with a message-body containing the ORA error code and its error message. If this stored procedure does not exist, Sails.js will just return an HTTP 400 status code with only the ORA error code in its message-body.
The exception error code stored procedure, return_codes_r(), can be designed so that it provides custom error messages for programmer-defined exceptions. In this way, your application can present a message that is more meaningful to the user, for example, "You cannot add the same department twice" instead of "DUP_VAL_ON_INDEX."
CREATE TYPE retcode_obj_t IS OBJECT( -- must be database object
id NUMBER,
message VARCHAR2(300)
);
CREATE TYPE retcode_nt IS TABLE OF retcode_obj_t; -- must be database object
ec_success CONSTANT NUMBER := 0;
ec_username_already_used CONSTANT NUMBER := 20010;
username_already_used EXCEPTION;
ec_userid_already_used CONSTANT NUMBER := 20011;
userid_already_used EXCEPTION;
ec_lacks_edit_privs CONSTANT NUMBER := 20012;
lacks_edit_privs EXCEPTION;
ec_undefined_failure CONSTANT NUMBER := 20013;
PRAGMA EXCEPTION_INIT( username_already_used, -20010 );
PRAGMA EXCEPTION_INIT( userid_already_used, -20011 );
PRAGMA EXCEPTION_INIT( lacks_edit_privs, -20012 );
-- msg() returns app specific exception messages for ORA number
PROCEDURE msg (p_retcode IN NUMBER, p_msg OUT VARCHAR2 )
IS
retval VARCHAR2(300) := 'undefined error';
BEGIN
p_msg := retval;
CASE p_retcode
WHEN ec_success THEN retval := 'operation succeeded';
WHEN ec_username_already_used THEN retval := 'User name already taken.' ||
'Please specify an unused username.';
WHEN ec_userid_already_used THEN retval := 'User ID already taken. ' ||
'Please specify an unused ID.';
WHEN ec_lacks_edit_privs THEN retval := 'Edit attempt failed. ' ||
'User lacks edit privileges';
ELSE
retval := 'internal error: routine likely encountered' ||
' unexpected exception(' || TO_CHAR( p_retcode ) || ')';
END CASE;
p_msg := retval;
END;
FUNCTION msg ( p_retcode IN NUMBER) RETURN VARCHAR2
IS
v_buf VARCHAR2(300);
BEGIN
msg( p_retcode, v_buf );
RETURN v_buf;
END;
PROCEDURE return_codes_r( p_details IN OUT SYS_REFCURSOR )
IS
retcode_details_tab retcode_nt := retcode_nt();
BEGIN
-- build up a nested table containing both
-- return codes: ORAs for all messages and
-- message text for all messages
FOR v_retcode IN ec_username_already_used .. ec_undefined_failure
LOOP
BEGIN
retcode_details_tab.EXTEND();
retcode_details_tab(retcode_details_tab.LAST)
:= retcode_obj_t( v_retcode, msg ( v_retcode ) );
END;
END LOOP;
-- select the contents of the nested table into the result set
OPEN p_details FOR
SELECT *
FROM TABLE(cast(retcode_details_tab AS retcode_nt));
END;
Listing 13: An implementation example of return_codes_r()
The Interface Package
The interface package is an implementation of the facade design pattern. The facade design pattern is a paradigm for an interface layer that encapsulates a complicated subsystem with a simpler interface. This simpler interface is necessary because the Sails.js design limits the procedures it can call to the ones following the naming pattern shown in Table 2. This interface package contains stored procedures names that match the names sails-oracle-sp will generate from the models.
Another consideration: it is likely that a production database already exists. An interface package provides glue logic that interconnects an existing database with Sails.js without modifying a working production database.
This series uses Oracle Database's sample HR schema as an example database. Listing 14 shows the EMPLOYEE table's columns.
CREATE TABLE employees
(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4)
);
Listing 14: The EMPLOYEES table details.
Listing 15 shows the stored procedure provided by the interface package that is called by sails-oracle-sp's create() adapter method.
CREATE OR REPLACE
PACKAGE hr
IS
TYPE empl_details_refcur_t IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE employees_c(
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2,
p_phone_number IN VARCHAR2,
p_hire_date IN VARCHAR2,
p_job_id IN VARCHAR2,
p_salary IN NUMBER,
p_commission_pct IN NUMBER,
p_manager_id IN NUMBER,
p_department_id IN NUMBER,
p_details IN OUT hr_child.empl_details_refcur_t
);
--
- other stored procedures redacted
--
END;
Listing 15: The employee_c() interface stored procedure specification
A Review of the Sails.js Stack
The previous material was a low-level examination of the Sails.js stack when used with sails-oracle-sp and node-oracledb. This section is a higher-level summary that shows how the various layers interact.
The Sails.js model specifies the parameter names the client must pass to either the REST API or the WebSocket for a given stored procedure family. These parameter names, and the values to be passed to these parameters, are conveyed via a JSON object. Sails.js receives this JSON object via either an HTTP entity-body, in the case of the REST API, or via the WebSocket.
The controller receives this JSON object from the client. The parameter names are specified in camelCase because JSON prohibits snake_case in its identifiers. The Sails.js model instructs the controller how to rename the stored procedure parameters to match the actual parameter names.
The controller generates an anonymous block containing the stored procedure call and pases it to node-oracledb for execution.
If the procedure call involves a type of read operation, node-oracledb will return a REFCURSOR ResultSet back to sails-oracle-sp. Then sails-oracle-sp will create a JSON object containing this result and send it back to the caller.
Stored Procedure Design Considerations
This section explains how to manage certain design considerations.
Using Application-Specific Exceptions to Enforce Business Rules and Data Integrity
There is some controversy regarding whether a database should enforce business rules. There is no controversy regarding whether a database should enforce data integrity. While I will not take a position on the efficacy of using a database to enforce business rules, in this section I will show how application-specific exceptions can be used to support enforcement of both business rules and data integrity.
Separation of Concerns
Separation of concerns implies that different layers of the system should be specialized to obtain different types of system goals. For example, the UI should be concerned with enforcing user interface rules (for example, disabling an edit control for users who do not have edit privileges) and a different layer, say, the database, should be used to enforce business rules and data integrity. Put another way, each subsystem is responsible for ensuring correct system behavior in the domain that is the given subsystem's primary area of concern.
Column Constraints
Column constraints are a fundamental means that databases use to enforce data integrity. Oracle Database raises an exception when column constraints are violated. Sails-oracle-sp provides the means whereby exceptions that bubble up toward the client can be provided with meaningful error messages.
The Limitations of Column Constraints
Column constraints cannot guard against all possible kinds of data inconsistency. Certain data patterns are beyond the abilities of column constraints to enforce. Stored procedure code can detect such inconsistencies and raise application-specific exceptions when they are detected. Consider, for example, a system where only certain users have the privilege to edit. In this case, stored procedure logic is likely the best way to validate that a user attempting to make an edit actually has the required edit privileges. When the stored procedure logic detects an edit operation has been initiated by a user not granted the necessary edit privileges, it can raise an application-specific exception. (The UI should disable edit features for users without edit privileges. This enables the UI to be capable of obtaining the current user's privileges from the database. Of course, in this case, if the UI is functioning correctly, the use case should never reach the point where the database receives an edit request from an unprivileged user. Regardless, the database should not outsource this responsibility to external agents, and it should always enforce data integrity so as to protect its data from buggy UIs and malicious exploits.)
Such an application-specific exception signals to the system that (1) the edit attempt failed and—significantly, for the purposes of users and the remainder of the system to respond intelligently to the exception—(2) why the edit attempt failed. For example, the UI might not only alert the user that an edit attempt failed but it also could present an error message describing why the edit attempt failed, providing the user with a hint about how to avoid the exception and obtain the desired result.
Creating Application-Specific Exceptions
PL/SQL's PRAGMA EXCEPTION_INIT() is used to create a named application-specific exception and to assign it an ORA exception code. The name of application-specific exception for the case where a user without edit privileges attempts to edit might be something like lacks_edit_privs. Table 5 shows lacks_edit_privs and its attributes. The exception's error message, which is shown in Table 5, is not specified by PRAGMA EXCEPTION_INIT(); it is specified by a PL/SQL procedure named return_codes_r(). An example implementation of return_codes_r() is shown in Listing 13.
Why Oracle Database System Exceptions Should Be Converted to Application-Specific Exceptions
Oracle Database system exceptions are general-purpose exceptions that are devoid of any indication of the application-specific context that raised them. For example, take a table with two columns named user_name and user_id where the data in each column is required to be unique. You can enforce this requirement by placing a unique constraint on each column. Afterward, Oracle Database will raise a DUP_VAL_ON_INDEX exception if the stored procedure attempts to store a duplicate value in these database columns. This exception, when raised, will be propagated back to Sails.js, which will convey an error message to the UI.
Ideally, the error message presented by the UI will lead the user into correcting the mistake. But simply conveying the system exception, DUP_VAL_ON_INDEX, to the UI creates two problems:
- Two different errors (for example, inserting duplicate data into either the
user_name column or the user_id column) will generate the same error indication.
- A message such as "duplicate value on index" is most likely to mystify users instead of informing them as to why their actions were rejected and indicating how they can accomplish their goals.
For this situation, I recommend that you create two named, application-specific exceptions for each of these two failure modes. These application-specific exceptions might be named username_already_used and userid_already_used. You should then rig the stored procedure responsible for changing this table's records so that it raises username_already_used when the caller attempts to insert a duplicate username and it raises userid_already_used when the caller attempts to insert a duplicate user ID.
Error Messages for Application-Specific Exceptions
Creating application-specific exceptions is just the first step. The "Exception Handling" section of this paper describes a stored procedure named return_codes_r(), which returns a result set comprising the ORA error codes and custom error messages that are meaningful within the context of this application. You manage exception error messages by modifying return_codes_r().
For the examples use