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.
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+.
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 --saveIf 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
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",
};For LoopBack 3 users
Use the Data source generator to add a Oracle data source to your application.
The generator will prompt for the database server hostname, port, and other settings
required to connect to a Oracle database. It will also run the npm install command above for you.
The entry in the application's /server/datasources.json will look like this:
{% include code-caption.html content="/server/datasources.json" %}
"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.
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 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 (LB4 form):
{% include code-caption.html content="src/datasources/db.datasource.ts" %}
const config = {
name: "db",
connector: "oracle",
host: "oracle-demo.strongloop.com",
port: 1521,
user: "admin",
password: "pass",
database: "XE",
};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
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",
});| Property name | Description | Default value |
|---|---|---|
| minConn | Minimum number of connections in the connection pool | 1 |
| maxConn | Maximum 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,
{% include code-caption.html content="src/datasources/db.datasource.ts" %}
const config = {
name: "db",
connector: "oracle",
minConn:1,
maxConn:5,
incrConn:1,
timeout: 10,
...
};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.
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 tableoracle.table: the table name of the model
Property definition maps to Oracle column
oracle.columnName: the column name of the propertyoracle.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:
{% include code-caption.html content="/models/user.model.ts" %}
@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;For LoopBack 3 users
{% include code-caption.html content="/common/models/model.json" %}
{
"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.
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;
//...For LoopBack 3 users
{
"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",
}
},
//...
}
}See LoopBack 4 types (or LoopBack 3 types) for details on LoopBack's data 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 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 |
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 discoverto discover models. - For LB3 users, please check Discovering models from relational databases. (See database discovery API for related APIs information)
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;
}For LoopBack 3 users
{
"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
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: truein 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: truefor 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-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.
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 testIf 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
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent