See also:

See also:

The LoopBack SQL Server connector enables LoopBack applications to connect to Microsoft SQL Server databases.

Installation

In your application root directory, enter:

shell
$ 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.

Icon
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.

PropertyTypeDefaultDescription
connectorString 

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

databaseString Database name
debugBoolean If true, turn on verbose mode to debug database queries and lifecycle.
hostStringlocalhostDatabase host name
passwordString Password to connect to database
portNumber1433Database TCP port
schemadboDatabase schema 
urlString Use instead of the hostportuserpassword, and database properties.  For example: 'mssql://test:mypassword@localhost:5432/dev'.
userString 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": 3306,
    "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  Expand source
    {"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 TypeSQL Server Type
BooleanBIT
DateDATETIME
GeoPointFLOAT
NumberINT

String

JSON

NVARCHAR

SQL Server to LoopBack types

SQL Server TypeLoopBack Type
BITBoolean

BINARY
VARBINARY
IMAGE

Node.js Buffer object

DATE
DATETIMEOFFSET
DATETIME2
SMALLDATETIME
DATETIME
TIME

Date
POINTGeoPoint

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.