loopback-connector-oracle

Oracle is an object-relational database management system produced by Oracle Corporation. The loopback-connector-oracle module is the Oracle connector for the LoopBack framework based on the node-oracledb module.

Prerequisites

Node.js: The Oracle connector requires Node.js version 6.x and up.

Windows: On 32-bit Windows systems, you must use the 32-bit version of Node.js. On 64-bit Windows systems, you must use the 64-bit version of Node.js. For more information, see Node-oracledb Installation on Windows.

Oracle: The Oracle connector requires Oracle client libraries 11.2+ and can connect to Oracle Database Server 9.2+.

Installation

Before installing this module, please follow instructions at https://oracle.github.io/node-oracledb/INSTALL.html to make sure all the prerequisites are satisfied.

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

$ npm install loopback-connector-oracle --save

If you create a Oracle data source using the data source generator as described below, you don’t have to do this, since the generator will run npm install for you.

The libaio library is required on Linux systems:

On Ubuntu/Debian, get it with this command:

sudo apt-get install libaio1

On Fedora/CentOS/RHEL, get it with this command:

sudo yum install libaio

Creating an Oracle data source

For LoopBack 4 users, use the LoopBack 4 Command-line interface to generate a DataSource with Oracle connector to your LB4 application. Run lb4 datasource, it will prompt for configurations such as host, post, etc. that are required to connect to an Oracle database.

After setting it up, the configuration can be found under src/datasources/<DataSourceName>.datasource.ts, which would look like this:

const config = {
  name: "db",
  connector: "oracle",
  tns: "",
  host: "localhost",
  port: 1521,
  user: "admin",
  password: "pass",
  database: "XE",
};

"mydb": {
  "name": "mydb",
  "connector": "oracle",
  "tns": "demo",
  "host": "myserver",
  "port": 1521,
  "database": "mydb",
  "password": "mypassword",
  "user": "admin"
 }

Edit <DataSourceName>.datasources.ts to add any other additional properties that you require.

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 (LB4 form):

const config = {
  name: "db",
  connector: "oracle",
  host: "oracle-demo.strongloop.com",
  port: 1521,
  user: "admin",
  password: "pass",
  database: "XE",
};

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

This specifies the supported naming methods; for example:

NAMES.DIRECTORY_PATH=(LDAP,TNSNAMES,EZCONNECT)

nsnames.ora

This maps aliases to connection stringsl for example:

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

ldap.ora

This configures 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:

const config = {
  name: "db",
  connector: "oracle",
  tns: "demo", // The tns property can be a tns name or LDAP service name
  username: "demo",
  password: "L00pBack",
});

Connection pooling options

For example,

const config = {
  name: "db",
  connector: "oracle",
  minConn:1,
  maxConn:5,
  incrConn:1,
  timeout: 10,
  ...
};

Connection troubleshooting

If you encounter this error:

Error: ORA-24408: could not generate unique server group name

Then the Oracle 11g client requires an entry with your hostname pointing to 127.0.0.1.

To resolve:

Get your hostname. Check your hostname by running this command (for example, if your machine’s name is “earth”):

$ hostname
earth

Update /etc/hosts and map 127.0.0.1 to your hostname “earth”:

...
127.0.0.1 localhost earth
...

Verify the fix. Run the app:

$ npm start

For more information, see StackOverflow question.

How LoopBack models map to Oracle tables

There are several properties you can specify to map the LoopBack models to the existing tables in the Oracle database:

Model definition maps to Oracle schema/table

• oracle.schema: the schema name of the table
• oracle.table: the table name of the model

Property definition maps to Oracle column

• oracle.columnName: the column name of the property
• oracle.dataType: the type of the column

(Check out more available database settings in the section Data mapping properties.)

The following example model User maps to the table USER under schema XE in the database with its columns:

@model({
  settings: {
    oracle: {
      schema: 'XE',
      table: 'USER'
    }
  }
})
export class User extends Entity {
  @property({
    type: 'number',
    required: true,
    id: true,
    oracle: {
      columnName: 'ID',
      dataType: 'NUMBER',
      nullable: 'N'
    },
  })
  id: number;

  @property({
    type: 'string',
    required: true,
    oracle:{
      columnName: 'LOCALTIONID',
      dataType: 'VARCHAR2',
      nullable: 'N'
    }
  })
  locationId: string;

{
    "name":"User",
    "options":{
      "idInjection":false,
      "oracle":{
        "schema":"XE",
        "table":"USER"
      }
    },
    "properties":{
      "myId":{
        "type":"number",
        "required":true,
        "id":1,
        "oracle":{
          "columnName":"MYID",
          "dataType":"NUMBER",
        }
      },
      "locationId":{
        "type":"String",
        "required":true,
        "length":20,
        "id":2,
        "oracle":{
          "columnName":"LOCATION_ID",
          "dataType":"VARCHAR2",
          "dataLength":20,
          "nullable":"N"
        }
      },
    }
  }

Notice: the Oracle database default type is UPPERCASE. If the oracle settings are not specified in the model, for example:

export class Demo extends Entity {
  @property({
    type: 'number',
    required: true,
    id: true,
  })
  id: number;

the connector would look for a table named DEMO under the default schema in the database and also map the id property to a column named ID in that table. This might cause errors if the default table/colum name doesn’t exist. Please do specify the settings if needed.

Configure a custom table/column name

On the other hand, such settings would also allow you to have different names for models and tables. Take the User model as an example, we can map the User model to the table MYUSER and map the id property to column MY_IDas long as they are specified correctly:

@model({
  settings: {
    oracle: {
      schema: 'XE',
      table: 'MYUSER' // customized name
    }
  }
})
export class User extends Entity {
  @property({
    type: 'number',
    required: true,
    id: true,
    oracle: {
      columnName: 'MYID' // customized name
    },
  })
  id: number;
  //...

{
    "name":"User",
    "options":{
      "idInjection":false,
      "oracle":{
        "schema":"XE",
        "table":"MYUSER" // customized name
      }
    },
    "properties":{
      "id":{
        "type":"number",
        "required":true,
        "id":1,
        "oracle":{
          "columnName":"MYID", // customized name
          "dataType":"NUMBER",
        }
      },
      //...
    }
  }

Type mapping

See LoopBack 4 types (or LoopBack 3 types) for details on LoopBack’s data types.

JSON to Oracle Types

Oracle Types to JSON

Discovery and auto-migration

Model discovery

The Oracle connector supports model discovery that enables you to create LoopBack models based on an existing database schema. Once you defined your datasource:

• LoopBack 4 users could use the commend lb4 discover to discover models.
• For LB3 users, please check Discovering models from relational databases. (See database discovery API for related APIs information)

Auto-migration

The Oracle connector also supports auto-migration that enables you to create a database schema from LoopBack models.

For example, based on the following model, the auto-migration method would create/alter existing CUSTOMER table under XE schema in the database. Table CUSTOMER would have two columns: NAME and ID, where ID is also the primary key, and its value would be generated by the database as it has type: 'Number' and generated: true set:

@model()
export class Customer extends Entity {
  @property({
    id: true,
    type: 'Number',
    generated: true
  })
  id: number;

  @property({
    type: 'string'
  })
  name: string;
}

{
    "name":"Customer",
    "options":{
      "idInjection":false,
    },
    "properties":{
      "id":{
        "type":"number",
        "required":true,
        "id":1,
      },
      "name":{
        "type":"string",
        "required":false,
      },
    }
  }

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 sequence, for example, PRODUCT_ID_TRIGGER

Specifying database schema definition via model

By default, table and column names are generated in uppercase.

Besides the basic model metadata, you can also to specify part of the database schema definition via the property definition then run the migration script. They will be mapped to the database. The setting is the same as what we introduced in the section Configure a custom table/column name. One just needs to create models first, then run the migration script.

For how to run the script and more details:

• For LB4 users, please check Database Migration
• For LB3 users, please check Creating a database schema from models
• Check discovery/migration section the Oracle tutorial

(See LoopBack auto-migrate method for related APIs information)

Here are some limitations and tips:

• If you defined generated: true in the id property, it generates integers by default. The Oracle connector does not support other auto-generated id types yet. Please check the Auto-generated ids section below if you would like use auto-generated id in different types such as uuid.
• Only the id property supports the auto-generation setting generated: true for now
• Destroying models may result in errors due to foreign key integrity. First delete any related models by calling delete on models with relationships.

Auto-generated ids

Auto-migrate supports the automatic generation of property values for the id property. For Oracle, the default id type is integer. Thus if you have generated: true set in the id property definition, it generates integers by default:

{
  id: true,
  type: 'Number',
  required: false,
  generated: true // enables auto-generation
}

It might be a case to use UUIDs as the primary key in Oracle instead of integers. You can enable it with either the following ways:

• use uuid that is generated by your LB application by setting defaultFn: uuid:

  @property({
    id: true,
    type: 'string'
    defaultFn: 'uuid',
    // generated: true,  -> not needed
  })
  id: string;

• alter the table to use Oracle built-in uuid functions (SYS_GUID() for example):

  @property({
  id: true,
  type: 'String',
  required: false,
  // settings below are needed
  generated: true,
  useDefaultIdType: false,
})
  id: string;

Then you will need to alter the table manually.

Running tests

Own instance

If you have a local or remote Oracle instance and would like to use that to run the test suite, use the following command:

• Linux

ORACLE_HOST=<HOST> ORACLE_PORT=<PORT> ORACLE_USER=<USER> ORACLE_PASSWORD=<PASSWORD> ORACLE_DATABASE=<DATABASE> npm test

• Windows

SET ORACLE_HOST=<HOST>
SET ORACLE_PORT=<PORT>
SET ORACLE_USER=<USER>
SET ORACLE_PASSWORD=<PASSWORD>
SET ORACLE_DATABASE=<DATABASE>
npm test

Docker

If you do not have a local Oracle instance, you can also run the test suite with very minimal requirements.

• Assuming you have Docker installed, run the following script which would spawn an Oracle instance on your local machine:

source setup.sh <HOST> <PORT>

where <HOST>, <PORT>, <USER>, and PASSWORD are optional parameters. The default values are localhost, 1521, admin, and 0raclep4ss respectively. The DATABASE setting is always XE.

• Run the test:

npm test