Oracle Database Connector

The Oracle data connector is a plugin for API Builder that can connect to your Oracle database instance and interrogate your schema that will automatically provision Models into to your application, and optionally, automatically generate a rich CRUD API to the underlying tables. The Models can be used programmatically, or can be used within the flow editor to interact with your database.

Minimum requirements

Supported DB versions

• Oracle 12c

Underlying driver version

• The underlying driver used is node-oracledb version 4.0 - supporting Node.js versions 8.16, 10.16, 12, or later.

Memory

• ~25 MB

Disk space

• ~16 MB

Supported features

• Automatic generation of Models from SQL tables
• Automatic generation of API for Models
• Full CRUD operations on tables via Models

Prerequisites

This connector requires Oracle Instant Client installed. To install it, please follow the instructions for your environment here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

The connector also depends on the 'node-oracledb' module. To be able to properly install the connector, please check the prerequisites here: https://github.com/oracle/node-oracledb#-installation

Installation

To install the connector use:

npm install --no-optional @axway/api-builder-plugin-dc-oracle

A configuration file is generated for you and placed into the conf directory of your API Builder project. You should configure this file before starting your service.

Configuration

Once the plugin is installed, the configuration file is located <project>/conf/oracle.default.js.

| Option name | Type | Description | | ----------- | ---- | ----------- | | connector | string | Must be: @axway/api-builder-plugin-dc-oracle | | connectString | string | The database instance connection string, e.g. localhost/orcl. | | user | string | The user with which to connect to the database. | | password | string | The user's password with which to connect to the database. | | generateModelsFromSchema | boolean | If enabled, API Builder will automatically interrogate the database and auto-generate Models from SQL tables. | | modelAutogen | boolean | If enabled, API Builder will automatically generate a full and rich CRUD API from the generated Models. | | connectionPooling | boolean | boolean Enables connection pooling for better performance, scalability, and resilience. See the Connection pooling for more information (default false). | | poolMax | integer | The maximum number of connections to which a connection pool can grow. (default 4) | | poolMin | integer | The minimum number of connections a connection pool maintains, even when there is no activity to the target database. (default 0) | | poolPingInterval | integer | The number of seconds that a connection has been idle before it is "pinged" to check the connection is alive. (default 60) | | poolTimeout | integer | The number of seconds after which idle connections (unused in the pool) are terminated. (default 60) | | poolIncrement | integer | The number of connections that are opened whenever a connection request exceeds the number of currently open connections. (default 1) |

Usage

After you configure the connector, you can start up your API Builder project and visit the console (normally found under http://localhost:8080/console). Your connector will be listed under the Connectors section of the console.

Your database tables will be listed under the Models section of the console. You can now click on the gear icon to the right of the table names and generate flow based APIs.

You can also reference the connector in a custom model.

const Account = APIBuilder.Model.extend('Account', {
  fields: {
    Name: { type: String, required: true }
  },
  connector: 'oracle'
});

If you want to map a specific model to a specific table, use metadata. For example, to map the account model to the table named accounts, set it such as:

const Account = APIBuilder.Model.extend('account', {
  fields: {
    Name: { type: String, required: false, validator: /[a-zA-Z]{3,}/ }
  },
  connector: 'oracle',
  metadata: {
    'oracle': {
      table: 'accounts'
    }
  }
});

Known issues and limitations

1. Only supports SQL tables.
2. Does not support stored procedures.

Changes

4.0.0

• #6089: Breaking change: requires minimum Node.js version 16.x.

3.3.2

• #7495: Updated README.md.

3.3.1

• #7412: Pin in-house dependencies.

3.3.0

• #6933: Replace peerDependency on @axway/api-builder-runtime with engines.apibuilder.

3.2.3

• #6014: Fixes issue where models having aliasied field names would not be assigned values when querying the DB.

3.2.2

• #7057: Updated documentation links.

3.2.1

• #7008: Fixed the issue where the plugin configuration file is not installed correctly if the project conf directory does not exist.

3.2.0

• #7031: Supports querying ranges for all applicable data types with $gt, $gte, $lt and $lte (e.g. { AGE: { $gt: 0, $lt: 99 } }).

3.1.1

• #6934: Internal refactoring around code style.

3.1.0

• #6909: Supports tables from all users, not just current user.
• #6909: The config generateModelsFromSchema now supports selecting individual tables used for Model generation.

3.0.6

• #6459: Added @axway/api-builder-project-utils to handle copying config to a project after installing this plugin.

3.0.5

• #6815: Internal tooling changes.

3.0.4

• #6315: Internal chore.

3.0.3

• #6116: Internal cleanup chore.

3.0.2

• #6114: Refactor code for style, security and performance improvements.

3.0.1

• #6115: Internal fix for Sonar scans.

3.0.0

• #6091: Add support for Node.js 12 and 13.
• #6091: Upgrade node-oracledb to 4.
• #6091: Breaking change: The required version of Node.js is now 8.16, 10.16, 12 or later.

2.3.12

• #6105: Non-primary-key fields named 'ID' are no longer filtered out from generated Models.

2.3.11

• #6026: Document supported Node.js version range in package.json.

2.3.10

• #6074: Internal CI chore.

2.3.9

• #5700: Update error message displayed when missing a required primary key.

2.3.8

• #5711: Internal cleanup of npm scripts.

2.3.7

• #5794: Fix Count to use comparison operators correctly on Dates, Numbers and Booleans.

2.3.6

• #5715: Internal changes to remove integration tests.

2.3.5

• #5709: Internal changes to update eslint rules.

2.3.4

• #5707: Internal cleanup to code coverage during build process.

2.3.3

• #5613: Fix queries to use comparison operators correctly on Dates, Numbers and Booleans.

2.3.2

• #5576: Fix queries for falsy values. Now values such as null, 0, '' and false will no longer be ignored when used in a query, and will match the expected records.

2.3.1

• #5596: Fixes connector pooling Previously, the connector would run out of connections in the connection pool when executing statements that did not involve a result set, e.g. FindByID. Now, connections are returned to the pool.

2.3.0

• #5520: Support for database connection pooling Previously, the connector used a single connection which was vulnerable to network disconnects. Now, connection pooling exists that can better handle network disconnects, timeouts, and reconnects (see connectionPooling).

2.2.1

• #5050: Updating license text.

2.2.0

• #4724: Support for Models based on tables that have no primary key. Previously, there was no way to distinguish between a model with no primary key and a model relying on the default. Now, the models auto-generated by the connectore advertise whether or not they have primary keys.

2.1.0

• #4532: Data connectors table methods did not display the proper type for the PK Previously, APIs and flows generated for models based created by data connectors assumed a IDs were of type string. Now,the APIs and flows will use the proper type for the primary key.

2.0.0

WARNING: In the UI, you may be prevented from upgrading to the v2 of the Oracle plugin. To upgrade, please review all the breaking changes introduced in here in v2.0.0 and v3 of this plugin. After, the upgrade must be done manually: npm install @axway/api-builder-plugin-dc-oracle@^3.0.0

• #4871: Breaking Change: Introduce support $in and $nin (IN and NOT IN) operators in where query. Previously, when using Model flow-nodes and the auto-generated APIs with the Oracle data connectors, it was not possible to formulate a where clause to return results where the field value is in or not in a list of values. Now the where clause can use $in and $nin to construct these queries, e.g. { "field": { "$in": ["value1", "value2",...]}}.

  For more information on the Model flow-node and the methods that accept a where clause please see https://docs.axway.com/bundle/api-builder/page/docs/developer_guide/flows/flow_nodes/model_flow_nodes/index.html.

1.0.4

• #4757: Changed SCM repository and associated internal cleanup.
• #4814: Update the default configuration. Previously the default oracle.default.js configuration file contained hard-coded connection details, now it uses environment variables.

License

This code is proprietary, closed source software licensed to you by Axway. All Rights Reserved. You may not modify Axway’s code without express written permission of Axway. You are licensed to use and distribute your services developed with the use of this software and dependencies, including distributing reasonable and appropriate portions of the Axway code and dependencies. Except as set forth above, this code MUST not be copied or otherwise redistributed without express written permission of Axway. This module is licensed as part of the Axway Platform and governed under the terms of the Axway license agreement (General Conditions) located here: https://support.axway.com/en/auth/general-conditions; EXCEPT THAT IF YOU RECEIVED A FREE SUBSCRIPTION, LICENSE, OR SUPPORT SUBSCRIPTION FOR THIS CODE, NOTWITHSTANDING THE LANGUAGE OF THE GENERAL CONDITIONS, AXWAY HEREBY DISCLAIMS ALL SUPPORT AND MAINTENANCE OBLIGATIONS, AS WELL AS ALL EXPRESS AND IMPLIED WARRANTIES, INCLUDING BUT NOT LIMITED TO IMPLIED INFRINGEMENT WARRANTIES, WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE, AND YOU ACCEPT THE PRODUCT AS-IS AND WITH ALL FAULTS, SOLELY AT YOUR OWN RISK. Your right to use this software is strictly limited to the term (if any) of the license or subscription originally granted to you.