The Oracle connector enables LoopBack applications to connect to Oracle data sources.
Page Contents

Installation

In your application root directory, enter this command to install the connector:

$ npm install loopback-connector-oracle --save

See Installing the Oracle connector for further installation instructions.

Connector properties

The connector properties depend on naming methods you use for the Oracle database. LoopBack supports three naming methods:

  • Easy connect: host/port/database.
  • Local naming (TNS): alias to a full connection string that can specify all the attributes that Oracle supports.
  • Directory naming (LDAP): directory for looking up the full connection string that can specify all the attributes that Oracle supports.

Easy Connect

Easy Connect is the simplest form that provides out-of-the-box TCP/IP connectivity to databases. The data source then has the following settings.

Property Type Default Description
host or hostname String localhost Host name or IP address of the Oracle database server
port Number 1521 Port number of the Oracle database server
username or user String   User name to connect to the Oracle database server
password String   Password to connect to the Oracle database server
database String XE Oracle database listener name

For example:

/server/datasources.json

{
  "demoDB": {
    "connector": "oracle",
    "host": "oracle-demo.strongloop.com",
    "port": 1521,
    "database": "XE",
    "username": "demo",
    "password": "L00pBack"
  }
}

Local and directory naming

Both local and directory naming require that you place configuration files in a TNS admin directory, such as /oracle/admin.

sqlnet.ora (specifying the supported naming methods)

NAMES.DIRECTORY_PATH=(LDAP,TNSNAMES,EZCONNECT)

tnsnames.ora (mapping aliases to connection strings)

demo1=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=demo.strongloop.com)(PORT=1521)))

ldap.ora (configuring the LDAP server)

DIRECTORY_SERVERS=(localhost:1389)
DEFAULT_ADMIN_CONTEXT="dc=strongloop,dc=com"
DIRECTORY_SERVER_TYPE=OID

Set up TNS_ADMIN environment variable

 For the Oracle connector to pick up the configurations, you must set the environment variable ‘TNS_ADMIN’ to the directory containing the .ora files.

export TNS_ADMIN=<directory containing .ora files>

Now you can use either the TNS alias or LDAP service name to configure a data source:

var ds = loopback.createDataSource({
  "tns": "demo", // The tns property can be a tns name or LDAP service name
  "username": "demo",
  "password": "L00pBack"
});

Here is an example for datasources.json:

/server/datasources.json

{
  "demoDB": {
    "connector": "oracle",
    "tns": "demo",
    "username": "demo",
    "password": "L00pBack"
  }
}

Connection pooling options

Property name Description Default value
minConn Maximum number of connections in the connection pool 1
maxConn  Minimum number of connections in the connection pool 10
incrConn 

Incremental number of connections for the connection pool.

1
timeout 

Time-out period in seconds for a connection in the connection pool. The Oracle connector will terminate connections in this connection pool that are idle longer than the time-out period.

10

For example,

/server/datasources.json

{
  "demoDB": {
    "connector": "oracle",
    "minConn":1,
    "maxConn":5,
    "incrConn":1,
    "timeout": 10,
    ...
  }
}

Model definition for Oracle

The model definition consists of the following properties:

  • name: Name of the model, by default, it’s the camel case of the table.
  • options: Model level operations and mapping to Oracle schema/table.
  • properties: Property definitions, including mapping to Oracle column.

/common/models/model.json

{
    "name":"Inventory",
    "options":{
      "idInjection":false,
      "oracle":{
        "schema":"STRONGLOOP",
        "table":"INVENTORY"
      }
    },
    "properties":{
      "productId":{
        "type":"String",
        "required":true,
        "length":20,
        "id":1,
        "oracle":{
          "columnName":"PRODUCT_ID",
          "dataType":"VARCHAR2",
          "dataLength":20,
          "nullable":"N"
        }
      },
      "locationId":{
        "type":"String",
        "required":true,
        "length":20,
        "id":2,
        "oracle":{
          "columnName":"LOCATION_ID",
          "dataType":"VARCHAR2",
          "dataLength":20,
          "nullable":"N"
        }
      },
      "available":{
        "type":"Number",
        "required":false,
        "length":22,
        "oracle":{
          "columnName":"AVAILABLE",
          "dataType":"NUMBER",
          "dataLength":22,
          "nullable":"Y"
        }
      },
      "total":{
        "type":"Number",
        "required":false,
        "length":22,
        "oracle":{
          "columnName":"TOTAL",
          "dataType":"NUMBER",
          "dataLength":22,
          "nullable":"Y"
        }
      }
    }
  }

Type mapping

See LoopBack types for details on LoopBack’s data types.

JSON to Oracle Types

LoopBack Type Oracle Type
String
JSON
Text
default

VARCHAR2

Default length is 1024

Number NUMBER
Date DATE
Timestamp TIMESTAMP(3)
Boolean CHAR(1)

Oracle Types to JSON

Oracle Type LoopBack Type
CHAR(1) Boolean
CHAR(n)
VARCHAR
VARCHAR2,
LONG VARCHAR
NCHAR
NVARCHAR2
String
LONG, BLOB, CLOB, NCLOB Node.js Buffer object
NUMBER
INTEGER
DECIMAL
DOUBLE
FLOAT
BIGINT
SMALLINT
REAL
NUMERIC
BINARY_FLOAT
BINARY_DOUBLE
UROWID
ROWID
Number
DATE
TIMESTAMP
Date

Destroying models

Destroying models may result in errors due to foreign key integrity. Make sure to delete any related models first before calling delete on model’s with relationships.

Auto-migrate / Auto-update

LoopBack auto-migration creates a database schema based on your application’s models. Auto-migration creates a table for each model, and a column in the table for each property in the model.  Once you have defined a model, LoopBack can create or update (synchronize) the database schemas accordingly, if you need to adjust the database to match the models. See Creating a database schema from models for more information.

After making changes to your model properties call Model.automigrate() or Model.autoupdate(). Call Model.automigrate() only on new models since it will drop existing tables.

LoopBack Oracle connector creates the following schema objects for a given model:

  • A table, for example, PRODUCT
  • A sequence for the primary key, for example, PRODUCT_ID_SEQUENCE
  • A trigger to generate the primary key from the sequnce, for example, PRODUCT_ID_TRIGGER

Discovery methods

LoopBack provides a unified API to create models based on schema and tables in relational databases. The same discovery API is available when using connectors for Oracle, MySQL, PostgreSQL, and SQL Server. For more information, see Creating a database schema from models.

Tags: connectors