This project contains examples to demonstrate LoopBack connectors for databases:
- LoopBack MySQL connector
- LoopBack MongoDB connector
- LoopBack Oracle connector
- LoopBack PostgreSQL connector
- LoopBack MSSQL connector
You can pretty much switch between the databases by updating datasources.json and models.json. No code change is required. In the following steps, we'll use mssql as the example.
For those who are not familiar with LoopBack, it’s an open source mobile backend framework that connects mobile devices to enterprise data. LoopBack provides out-of-box data access capabilities for models through pluggable datasources and connectors. Connectors provide connectivity to variable backend systems, such as databases or REST APIs. Models are in turn exposed to mobile devices as REST APIs and SDKs.
First, make sure you have strong-cli installed.
npm install -g strong-cliNext, you need a running MSSQL server. In this article, you'll connect to an instance running on demo.strongloop.com.
To demonstrate how to use MSSQL connector for LoopBack, we'll create a simple application from scratch using the slc
command:
slc lb project loopback-example-database
cd loopback-example-database
slc lb datasource accountDB --connector mssql
slc lb model account -i --data-source accountDBFollow the prompts to create your model with the following properties:
- email: string - The email id for the account
- level: number - The game level you are in
- created: date - The date your account is created
- modified: date - The date your account is updated
The properties will be saved to models.json.
Let's add the loopback-connector-mssql module and install the dependencies.
npm install loopback-connector-mssql --saveThe generated data source use the memory connector by default, to connect to MSSQL, we'll modify the data source configuration as follows.
vi datasources.jsonNote: Future releases will probably generate a config.json file for the data source configuration.
In datasoures.json, replace the data source configuration for mssql with the following snippet:
"accountDB": {
"connector": "mssql",
"host": "mssql.strongloop.com",
"port": 1433,
"database": "demo",
"username": "demo",
"password": "L00pBack"
}Now we have an account model in LoopBack, do we need to run some SQL statements to create the corresponding table in
MSSQL database?
Sure, but even simpler, LoopBack provides Node.js APIs to do so automatically. The code is create-test-data.js.
node create-test-dataLet's look at the code:
dataSource.automigrate('account', function (err) {
accounts.forEach(function(act) {
Account.create(act, function(err, result) {
if(!err) {
console.log('Record created:', result);
}
});
});
});dataSource.automigrate() creates or recreates the table in MSSQL based on the model definition for account. Please
note the call will drop the table if it exists and your data will be lost. We can use dataSource.autoupdate() instead
to keep the existing data.
Account.create() inserts two sample records to the MSSQL table.
node appOpen your browser now.
To get all accounts, go to http://localhost:3000/api/accounts.
[
{
"email": "[email protected]",
"level": 10,
"created": "2013-10-15T21:34:50.000Z",
"modified": "2013-10-15T21:34:50.000Z",
"id": 1
},
{
"email": "[email protected]",
"level": 20,
"created": "2013-10-15T21:34:50.000Z",
"modified": "2013-10-15T21:34:50.000Z",
"id": 2
}
]To get an account by id, go to http://localhost:3000/api/accounts/1.
{
"email": "[email protected]",
"level": 10,
"created": "2013-10-15T21:34:50.000Z",
"modified": "2013-10-15T21:34:50.000Z",
"id": "1"
}All the REST APIs can be explored at:
http://127.0.0.1:3000/explorer
Now we have the account table existing in MSSQL, we can try to discover the LoopBack model from the database. Let's
run the following example:
node discoverFirst, we'll see the model definition for account in JSON format.
{
"name": "Account",
"options": {
"idInjection": false,
"mssql": {
"schema": "dbo",
"table": "account"
}
},
"properties": {
"id": {
"type": "Number",
"required": false,
"length": null,
"precision": 10,
"scale": 0,
"id": 1,
"mssql": {
"columnName": "id",
"dataType": "int",
"dataLength": null,
"dataPrecision": 10,
"dataScale": 0,
"nullable": "NO"
}
},
...,
"email": {
"type": "String",
"required": false,
"length": 255,
"precision": null,
"scale": null,
"mssql": {
"columnName": "email",
"dataType": "nvarchar",
"dataLength": 255,
"dataPrecision": null,
"dataScale": null,
"nullable": "YES"
}
}
}
Then we use the model to find all accounts from MSSQL:
[ { id: 1,
email: '[email protected]',
level: 10,
created: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT),
modified: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT) },
{ id: 2,
email: '[email protected]',
level: 20,
created: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT),
modified: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT) } ]Let's examine the code in discover.js too. It's surprisingly simple! 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 (err, schema) {
console.log(JSON.stringify(schema, null, ' '));
});
dataSource.discoverAndBuildModels('account', {owner: 'demo'}, function (err, models) {
models.Account.find(function (err, act) {
if (err) {
console.error(err);
} else {
console.log(act);
}
});
});As you have seen, the MSSQL connector for LoopBack enables applications to work with data in MSSQL databases. It can be new data generated by 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 MSSQL. It’s great to have MSSQL in the Loop!