Oracle connector

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.

For example:

{
  "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:

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

Connection pooling options

For example,

{
  "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.

{
    "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

Oracle Types to JSON

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.