The SQL Server connector enables LoopBack applications to connect to Microsoft SQL Server data sources.
Page Contents

Installation

In your application root directory, enter:

$ npm install loopback-connector-mssql --save

This will install the module from npm and add it as a dependency to the application’s package.json file.

Creating a SQL Server data source

Use the Data source generator to add a SQL Server data source to your application. The generator will add the following entry to the /server/datasources.json file:

/server/datasources.json

"sqlserverdb": {
    "name": "sqlserverdb",
    "connector": "mssql"
}

Edit datasources.json to add other properties that enable you to connect the data source to a SQL Server database.

To connect to a SQL Server instance running in Azure, you must specify a qualified user name with hostname, and add to the following to the data source declaration:

"options": {
   "encrypt": true
   ...
}

Connector settings

To configure the data source to use your MS SQL Server database, edit datasources.json and add the following settings as appropriate. The MSSQL connector uses node-mssql as the driver. For more information about configuration parameters, see node-mssql documentation.

Property Type Default Description
connector String  

Either "loopback-connector-mssql" or "mssql"

database String   Database name
debug Boolean   If true, turn on verbose mode to debug database queries and lifecycle.
host String localhost Database host name
password String   Password to connect to database
port Number 1433 Database TCP port
schema String dbo Database schema
url String   Use instead of the hostportuserpassword, and database properties.  For example: 'mssql://test:mypassword@localhost:1433/dev'.
user String   Qualified username with host name, for example "user@your.sqlserver.dns.host".

For example:

/server/datasources.json

...
"accountDB": {
    "connector": "mssql",
    "host": "demo.strongloop.com",
    "port": 1433,
    "database": "demo",
    "username": "demo",
    "password": "L00pBack"
  }
...

Alternatively you can use a single ‘url’ property that combines all the database configuration settings, for example:

"accountDB": {
    "url": "mssql://test:mypassword@localhost:1433/demo?schema=dbo"
}

The application will automatically load the data source when it starts. You can then refer to it in code, for example:

/server/boot/script.js

var app = require('./app');
var dataSource = app.dataSources.accountDB;

Alternatively, you can create the data source in application code; for example:

/server/script.js

var DataSource = require('loopback-datasource-juggler').DataSource;
var dataSource = new DataSource('mssql', config);
config = { ... };  // JSON object as specified above in "Connector settings"

Defining models

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 Microsoft SQL Server schema/table
  • properties: Property definitions, including mapping to Microsoft SQL Server columns

For example:

/common/models/model.json

{"name": "Inventory", 
     "options": {
       "idInjection": false,
       "mssql": {
         "schema": "strongloop",
         "table": "inventory"
       }
     }, "properties": {
      "id": {
        "type": "String",
        "required": false,
        "length": 64,
        "precision": null,
        "scale": null,
        "mssql": {
          "columnName": "id",
          "dataType": "varchar",
          "dataLength": 64,
          "dataPrecision": null,
          "dataScale": null,
          "nullable": "NO"
        }
      },
      "productId": {
        "type": "String",
        "required": false,
        "length": 64,
        "precision": null,
        "scale": null,
        "id": 1,
        "mssql": {
          "columnName": "product_id",
          "dataType": "varchar",
          "dataLength": 64,
          "dataPrecision": null,
          "dataScale": null,
          "nullable": "YES"
        }
      },
      "locationId": {
        "type": "String",
        "required": false,
        "length": 64,
        "precision": null,
        "scale": null,
        "id": 1,
        "mssql": {
          "columnName": "location_id",
          "dataType": "varchar",
          "dataLength": 64,
          "dataPrecision": null,
          "dataScale": null,
          "nullable": "YES"
        }
      },
      "available": {
        "type": "Number",
        "required": false,
        "length": null,
        "precision": 10,
        "scale": 0,
        "mssql": {
          "columnName": "available",
          "dataType": "int",
          "dataLength": null,
          "dataPrecision": 10,
          "dataScale": 0,
          "nullable": "YES"
        }
      },
      "total": {
        "type": "Number",
        "required": false,
        "length": null,
        "precision": 10,
        "scale": 0,
        "mssql": {
          "columnName": "total",
          "dataType": "int",
          "dataLength": null,
          "dataPrecision": 10,
          "dataScale": 0,
          "nullable": "YES"
        }
      }
    }}

Auto migrating and auto-updating

After making changes to model properties you must call Model.automigrate() or Model.autoupdate(). Call Model.automigrate() only on a new model, since it will drop existing tables. See Creating a database schema from models for more information.

For each model, the LoopBack SQL Server connector creates a table in the ‘dbo’ schema in the database.

Destroying models

Destroying models may result in errors due to foreign key integrity. First delete any related models first calling delete on models with relationships.

Type mapping

See LoopBack types for details on LoopBack’s data types.

LoopBack to SQL Server types

LoopBack Type SQL Server Type
Boolean BIT
Date DATETIME
GeoPoint FLOAT
Number INT

String

JSON

NVARCHAR

SQL Server to LoopBack types

SQL Server Type LoopBack Type
BIT Boolean

BINARY
VARBINARY
IMAGE

Node.js Buffer object

DATE
DATETIMEOFFSET
DATETIME2
SMALLDATETIME
DATETIME
TIME

Date
POINT GeoPoint

BIGINT
NUMERIC
SMALLINT
DECIMAL
SMALLMONEY
INT
TINYINT
MONEY
FLOAT
REAL

Number

CHAR
VARCHAR
TEXT
NCHAR
NVARCHAR
NTEXT
CHARACTER VARYING
CHARACTER

String

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 Database discovery API.

Tags: connectors