#loopback-example-database The purpose of this project is to demonstrate the usage of various LoopBack database connectors. Each branch in this repository contains a prebuilt configuration for a specific connector.

Connector Name	Branch Name
LoopBack Microsoft SQL Server Connector	mssql
LoopBack MongoDB Connector	mongodb
LoopBack MySQL Connector	master
LoopBack Oracle Connector	oracle
LoopBack PostgreSQL Connector	postgresql

For example, run the following to view the MongoDB example:

git clone https://github.com/strongloop/loopback-example-database.git
cd loopback-example-database
git checkout mongodb

##Getting Started In this example, we will demonstrate the usage of the LoopBack Oracle Connector. Instead of setting up your own database instance to connect to (which you would normally do), we will be connecting to an preconfigured Oracle instance running at demo.strongloop.com.

###Prerequisites We will need the slc (StrongLoop Controller) command line tool to simplify various tasks in the example.

npm install -g strongloop

###Create the LoopBack Application To demonstrate how to use the LoopBack Oracle Connector, let's create an application from scratch using the slc command. Follow the prompt and remember to name your project loopback-example-database. We will also add the connector to this project by using NPM.

slc loopback #create project
cd loopback-example-database
npm install --save loopback-connector-oracle #add connector

Add the following to your startup file:

source $HOME/strong-oracle.rc

####Automatic PATH Modification As a part of the installation process, you will see this message:

...
---------------------------------------------------------------------------
The node-oracle module and the Oracle specific libraries have been
installed in /Users/sh/repos/loopback-example-database/node_modules/loopback-connector-oracle/node_modules/loopback-oracle-installer.

The default bashrc (/etc/bashrc) or user's bash_profile (~/.bash_profile)
paths have been modified to use this path. If you use a shell other than
bash, please remember to set the DYLD_LIBRARY_PATH prior to using node.

Example:
  $ export DYLD_LIBRARY_PATH=":/Users/$USER/repos/loopback-example-database/node_modules/loopback-connector-oracle/node_modules/instantclient:/Users/$USER/repos/loopback-example-database/node_modules/loopback-connector-oracle/node_modules/instantclient"
...

However, this is a deprecated feature from LoopBack 1.x (we will remove this message in a future update). Due to concerns raised in the past regarding the "invasiveness" of automatic PATH modification, we now generate a file in your home directory named strong-oracle.rc instead. This file is meant to be sourced into your startup file (.bashrc, .bash_profile, etc) manually.

###Add a Data Source Run the following from the loopback-example-database directory to create a data source named accountDB:

slc loopback:datasource accountDB

###Configure the Data Source By default, the auto-generated data source uses the Memory Connector. However, since we're going to connect using Oracle, in loopback-example-database/server/datasources.json, modify the accountDB configuration to look like:

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

###Add a Model Once we have the data source configured properly, we can create an account model by running:

slc loopback:model account

Follow the prompts to create your model with the following properties:

Property	Data Type	Description
email	string	The email id for the account
created	date	The time of creation for the account
modified	date	The last modification time for the account

These properties will be saved to loopback-example-database/common/models/account.json once the prompt exits.

###Create the Table and Add Test Data Now that we have an account model configured, we can generate its corresponding table and fields in the database using the API's provided by LoopBack. Copy create-test-data.js from this repository and put it into loopback-example-database/server/create-test-data.js. Run the following in loopback-example-database/server to add dummy data to your database:

cd server #make sure you're in the server dir
node create-test-data

This script will add two accounts into your database.

####create-test-data.js

dataSource.automigrate('account', function(er) {
  ...
  accounts.forEach(function(account) {
    Account.create(account, function(er, result) {
      if (!er) return;
      console.log('Record created:', result);
      ...
    });
  });
});

dataSource.automigrate() creates or recreates a table in Oracle based on the model definition for account. This means if the table already exists, it will be dropped and all of its existing data will be lost. If you want to keep the existing data, use dataSource.autoupdate() instead.

Account.create() inserts two sample records to the Oracle table.

###Run the Application

cd .. #change back to the project root, ie) loopback-example-database
node .

Browse to http://localhost:3000/api/accounts to view the accounts you created in the previous step. You should see:

[
  {
    "email": "foo@bar.com",
    "created": "2014-08-28T22:56:28.000Z", #yours will be different
    "modified": "2014-08-28T22:56:28.000Z", #yours will be different
    "id": 1
  },
  {
    "email": "bar@bar.com",
    "created": "2014-08-28T22:56:28.000Z", #yours will be different
    "modified": "2014-08-28T22:56:28.000Z", #yours will be different
    "id": 2
  }
]

To get an account by id, browse to http://localhost:3000/api/accounts/1.

{
  "email": "foo@bar.com",
  "created": "2014-08-28T22:56:28.000Z", #yours will be different
  "modified": "2014-08-28T22:56:28.000Z", #yours will be different
  "id": 1
}

Each REST API can be viewed at http://localhost:3000/explorer

###Discovery Now that we have the account table created properly in the database, we can discover (reverse engineer) the LoopBack model from the existing database schema. Change to the loopback-example-database/server directory and run:

cd server #change back to the server dir
node discover

First, we'll see the model definition for account in JSON format.

{
  "name": "Account",
  "options": {
    "idInjection": false,
    "oracle": {
      "schema": "DEMO",
      "table": "ACCOUNT"
    }
  },
  "properties": {
    "email": {
      "type": "String",
      "required": false,
      "length": 1024,
      "precision": null,
      "scale": null,
      "oracle": {
        "columnName": "EMAIL",
        "dataType": "VARCHAR2",
        "dataLength": 1024,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "Y"
      }
    },
    ...
  }
}

Following the model definition, existing accounts are then displayed:

[ { email: 'foo@bar.com',
    created: Tue Sep 02 2014 11:48:36 GMT-0700 (PDT),
    modified: Tue Sep 02 2014 11:48:36 GMT-0700 (PDT),
    id: 1 },
  { email: 'bar@bar.com',
    created: Tue Sep 02 2014 11:48:36 GMT-0700 (PDT),
    modified: Tue Sep 02 2014 11:48:36 GMT-0700 (PDT),
    id: 2 } ]

####discover.js The dataSource.discoverSchema() method returns the model definition based on the account table schema. dataSource.discoverAndBuildModels() goes one step further by making the model classes available to perform CRUD operations.

dataSource.discoverSchema('ACCOUNT', { owner: 'DEMO' }, function(er, schema) {
  ...
  console.log(JSON.stringify(schema, null, '  '));
});

dataSource.discoverAndBuildModels('ACCOUNT', { owner: 'DEMO' }, function(er, models) {
  ...
  models.Account.find(function(er, accounts) {
    if (er) return console.log(er);
    console.log(accounts);
    dataSource.disconnect();
  });
});

###Data Synchronization An example of data synchronization is shown in server/sync-data.js. In the server directory, run:

node sync-data.js

You should see the following output:

Synchronization is now started.
1. Auto-migrating accounts for DB1
2. Removing accounts from DB2
3. Creating accounts in DB1
4. Finding accounts from DB1
Accounts found from DB1:  [ { email: 'foo@bar.com',
    created: Tue Sep 02 2014 16:49:43 GMT-0700 (PDT),
    modified: Tue Sep 02 2014 16:49:43 GMT-0700 (PDT),
    id: 1 },
  { email: 'bar@bar.com',
    created: Tue Sep 02 2014 16:49:43 GMT-0700 (PDT),
    modified: Tue Sep 02 2014 16:49:43 GMT-0700 (PDT),
    id: 2 } ]
5. Creating accounts in DB2
6. Finding accounts from DB2
Accounts found from DB2:  [ { email: 'foo@bar.com',
    created: Tue Sep 02 2014 16:49:43 GMT-0700 (PDT),
    modified: Tue Sep 02 2014 16:49:43 GMT-0700 (PDT),
    id: 1 },
  { email: 'bar@bar.com',
    created: Tue Sep 02 2014 16:49:43 GMT-0700 (PDT),
    modified: Tue Sep 02 2014 16:49:43 GMT-0700 (PDT),
    id: 2 } ]
Synchronization is completed.

Please follow the comments in sync-data.js for detailed descriptions related to each step.

##Conclusion As you can see, the Oracle connector for LoopBack enables applications to work with data in Oracle databases. It can be newly generated data from mobile devices that need to be persisted or existing data that need to be shared between mobile clients and other backend applications. No matter where you start, LoopBack makes it easy to handle your data with Oracle. It’s great to have Oracle in the Loop!

##LoopBack LoopBack is an open source mobile backend framework that connects mobile devices to enterprise data. It provides out-of-box data access capabilities for models through pluggable datasources and connectors. Connectors provide connectivity to various backend systems (such as databases or REST APIs). Models are in turn exposed to mobile devices as REST APIs and SDKs. For more information, see https://github.com/strongloop/loopback.