Negligent Parachute Maintainers

    loopback-connector-mssql-regevbr

    3.6.2 • Public • Published

    loopback-connector-mssql

    Microsoft SQL Server is a relational database management system developed by Microsoft. The loopback-connector-mssql module is the Microsoft SQL Server connector for the LoopBack framework.

    For more information, see LoopBack documentation.

    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.

    If you create a SQL Server data source using the data source generator as described below, you don't have to do this, since the generator will run npm install for you.

    Creating a SQL Server data source

    Use the Data source generator to add a SQL Server data source to your application.
    The generator will prompt for the database server hostname, port, and other settings required to connect to a SQL Server database. It will also run the npm install command above for you.

    The entry in the application's /server/datasources.json will look like this (for example):

    {% include code-caption.html content="/server/datasources.json" %}

    "sqlserverdb": {
        "name": "sqlserverdb",
        "connector": "mssql",
        "host": "myhost",
        "port": 1234,
        "url": "mssql://username:password@dbhost/dbname",
        "database": "mydb",
        "password": "admin",
        "user": "admin",
      }

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

    Instead of specifying individual connection properties, you can use a single url property that combines them into a single string, 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:

    {% include code-caption.html content="/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:

    {% include code-caption.html content="/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"

    Model discovery

    The SQL Server connector supports model discovery that enables you to create LoopBack models based on an existing database schema using the unified database discovery API. For more information on discovery, see Discovering models from relational databases.

    Auto-migratiion

    The SQL Server connector also supports auto-migration that enables you to create a database schema from LoopBack models using the LoopBack automigrate method. For each model, the LoopBack SQL Server connector creates a table in the 'dbo' schema in the database.

    For more information on auto-migration, see Creating a database schema from models for more information.

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

    Defining models

    The model definition consists of the following properties:

    • name: Name of the model, by default, the table name in camel-case.
    • options: Model-level operations and mapping to Microsoft SQL Server schema/table. Use the mssql model property to specify additional SQL Server-specific properties for a LoopBack model.
    • properties: Property definitions, including mapping to Microsoft SQL Server columns.
      • For each property, use the mssql key to specify additional settings for that property/field.

    For example:

    {% include code-caption.html content="/common/models/inventory.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"
            }
          }
        }}

    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

    Running tests

    Own instance

    If you have a local or remote MSSQL instance and would like to use that to run the test suite, use the following command:

    • Linux
    MSSQL_HOST=<HOST> MSSQL_PORT=<PORT> MSSQL_USER=<USER> MSSQL_PASSWORD=<PASSWORD> MSSQL_DATABASE=<DATABASE> CI=true npm test
    • Windows
    SET MSSQL_HOST=<HOST> SET MSSQL_PORT=<PORT> SET MSSQL_USER=<USER> SET MSSQL_PASSWORD=<PASSWORD> SET MSSQL_DATABASE=<DATABASE> SET CI=true npm test

    Docker

    If you do not have a local MSSQL instance, you can also run the test suite with very minimal requirements.

    • Assuming you have Docker installed, run the following script which would spawn a MSSQL instance on your local:
    source setup.sh <HOST> <PORT> <USER> <PASSWORD> <DATABASE>

    where <HOST>, <PORT>, <USER>, <PASSWORD> and <DATABASE> are optional parameters. The default values are localhost, 1433, sa, M55sqlT35t and master respectively.

    • Run the test:
    npm test

    Install

    npm i loopback-connector-mssql-regevbr

    DownloadsWeekly Downloads

    31

    Version

    3.6.2

    License

    MIT

    Unpacked Size

    4.22 MB

    Total Files

    2112

    Last publish

    Collaborators

    • regevbr