SQL Server connector | LoopBack Documentation

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

Page Contents

Note: The SQL Server connector requires SQL Server 2005+.

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 `host`, `port`, `user`, `password`, 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