A brief tutorial on using the LoopBack MS SQL connector.
Page Contents

loopback-example-database

A tutorial for basic database related features.

Overview

Topics covered

  • Data sources
    • Creating
    • Configuring
  • Models
    • Creating
  • Automigration
  • Discovery

Database specific tutorials

Database specific tutorials are on separate branches. The master branch contains the tutorial for MongoDB.

Branch Connector
master MongoDB
mssql Microsoft SQL Server
mysql MySQL
oracle Oracle
postgresql PostgreSQL

For example, to view the MySQL example:

git clone https://github.com/strongloop/loopback-example-database
cd loopback-example-database
git checkout mysql

Prerequisites

Before starting this tutorial, make sure you have the following installed:

  • Node.js
  • LoopBack CLI tools; see lb

Running the example

git clone https://github.com/strongloop/loopback-example-database
cd loopback-example-database
npm install
npm start

Tutorial - Microsoft SQL Server (MSSQL)

1. Create a new LoopBack app

App info

  • Name: loopback-example-database
  • Dir to contain the project: loopback-example-database
lb app loopback-example-database
... # follow the prompts

2. Install the LoopBack MSSQL connector

cd loopback-example-database
npm install --save loopback-connector-mssql

3. Create a data source

Data source info

  • Data source name: accountDS
  • Select the connector for accountDS: Microsoft SQL
lb datasource accountDS
... # follow the prompts

This creates a new data source named accountDS that uses the MSSQL connector.

4. Configure the data source

For the purposes of this example, we will use a preconfigured StrongLoop MSSQL server. Edit server/datasources.json to set the MSSQL configs:

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

Feel free to use your own local MSSQL instance. Simply change the configs above to match your own.

5. Create a new model

Model Info

  • Model name: Account
  • Attach Account to: accountDS (mssql)
  • Base class: PersistedModel
  • Expose via REST: Yes
  • Custom plural form: *Leave blank*
  • Properties:
    • email
      • String
      • Not required
    • createdAt
      • Date
      • Not required
    • lastModifiedAt
      • Date
      • Not required
lb model Account
... # follow the prompts

6. Create the collection with sample data - Automigration

With the account model configured, we can generate the corresponding MSSQL table using the info from the Account metadata in common/models/account.json via auto-migration.

Start by creating a dir to store general-purpose scripts:

mkdir bin

Inside that dir, create a script named automigrate.js. To create the Account collection and create two sample accounts, run:

node bin/automigrate.js

WARNING

The automigrate function creates a new collection if it doesn’t exist. If the collection already exists, it will be destroyed and it’s data will be deleted. If you want to keep this data, use autoupdate instead.

You should see:

Created: { email: [email protected]',
  createdAt: Fri Oct 23 2015 16:25:50 GMT-0700 (PDT),
  lastModifiedAt: Fri Oct 23 2015 16:25:50 GMT-0700 (PDT),
  id: 1 }
Created: { email: [email protected]',
  createdAt: Fri Oct 23 2015 16:25:50 GMT-0700 (PDT),
  lastModifiedAt: Fri Oct 23 2015 16:25:50 GMT-0700 (PDT),
  id: 2 }

7. View data using the explorer

Projects scaffolded via slc loopback come with loopback-component-explorer preconfigured. From the project root, start the server:

node .

Then to view the existing account data, browse to localhost:3000/explorer and click:

  • GET /Accounts
  • Try it out!

You should see:

[
  {
    "email": "[email protected]",
    "createdAt": "2015-10-23T23:25:50.000Z",
    "lastModifiedAt": "2015-10-23T23:25:50.000Z",
    "id": 1
  },
  {
    "email": "[email protected]",
    "createdAt": "2015-10-23T23:25:50.000Z",
    "lastModifiedAt": "2015-10-23T23:25:50.000Z",
    "id": 2
  }
]

Try out some of the other endpoints to get a feel for how explorer works.

8. Add a script to perform discover the database schema

Discovery is the process of reverse engineering a LoopBack model from an existing database schema.

Create a script name discover-schema.js. Then run this script to discover the schema from the existing Account table:

node bin/discover-schema

You should see:

FIXME

9. Add a script to discover and build models

When retrieving the scheme is not enough, you can discover and build LoopBack models in one step.

Create a sript named discover-and-build-models.js. Then run:

node bin/discover-and-build-models

You should see:

Found: [ { id: 1,
    email: [email protected]',
    createdat: Fri Oct 23 2015 16:25:50 GMT-0700 (PDT),
    lastmodifiedat: Fri Oct 23 2015 16:25:50 GMT-0700 (PDT) },
  { id: 2,
    email: [email protected]',
    createdat: Fri Oct 23 2015 16:25:50 GMT-0700 (PDT),
    lastmodifiedat: Fri Oct 23 2015 16:25:50 GMT-0700 (PDT) } ]

See the official docs for more info.


More LoopBack examples

Tags: example_app