Skip to content

Getting Started with LoopBack and SQL Server

sql server, loopback, api3 min read

In this tutorial, we’ll talk about getting started with LoopBack and SQL Server. We’ll create an API using an existing SQL Server table as the backend. Let’s jump right in.


  • You have Node.js installed

  • You have some version of SQL Server installed

SQL Server

You know SQL Server.

SQL Server is Microsofts RDMS offering for OLTP, data warehousing, business intelligence and analytics.

The data

We’re going to be using an existing SQL Server table in our database. This should be a very common scenario when working with LoopBack and SQL Server as you are likely not creating your tables in SQL Server specifically while creating your LoopBack application.

We’ll use the excellent site to seed our table with 100 rows.

1CREATE TABLE [contacts] (
3 [first_name] VARCHAR(255) NULL,
4 [last_name] VARCHAR(255) NULL,
5 PRIMARY KEY ([id])
9INSERT INTO contacts([first_name],[last_name]) VALUES('Rudyard','Kerr'),('Stone','Cardenas'),('Jelani','Adkins'),('Solomon','Whitley'),('Ronan','Gillespie'),('Arsenio','Gutierrez'),('Moses','Kent'),('Jonah','Hammond'),('Ezekiel','Watson'),('Hayden','Mccray');
10INSERT INTO contacts([first_name],[last_name]) VALUES('Wesley','Campbell'),('Gabriel','Williams'),('Norman','Rosales'),('Chester','Wade'),('James','Noble'),('Alan','Graves'),('Aidan','Cameron'),('Hu','Luna'),('Philip','Harrison'),('Hu','Wilkerson');
11INSERT INTO contacts([first_name],[last_name]) VALUES('Uriel','Acosta'),('Troy','May'),('Cody','Sweet'),('Derek','Maxwell'),('Ryan','Russell'),('Lance','Jenkins'),('Kyle','May'),('Jerome','Mcdowell'),('Jermaine','Gray'),('Austin','Graham');
12INSERT INTO contacts([first_name],[last_name]) VALUES('Kareem','Flynn'),('Griffin','Smith'),('Lucas','Ruiz'),('Vernon','Rivers'),('Allen','Pearson'),('Murphy','Glass'),('Robert','Ryan'),('Gray','Burke'),('Joseph','Stephenson'),('Josiah','Vinson');
13INSERT INTO contacts([first_name],[last_name]) VALUES('Malachi','Burch'),('Stone','Horne'),('Harding','Santana'),('Blake','Valentine'),('Grady','Pollard'),('Lance','Johnson'),('Myles','Vance'),('Amery','Ferguson'),('Garth','Padilla'),('Fuller','Wolf');
14INSERT INTO contacts([first_name],[last_name]) VALUES('Lyle','Martin'),('Isaac','Huff'),('Steven','Lawrence'),('Sylvester','Gallagher'),('Benjamin','West'),('Allistair','Norman'),('Armando','Hawkins'),('Allen','Rivas'),('Norman','Fischer'),('Gray','Silva');
15INSERT INTO contacts([first_name],[last_name]) VALUES('Theodore','Mcdonald'),('Luke','Hicks'),('Reed','Baxter'),('August','Gould'),('Carter','Durham'),('Uriah','Parker'),('Alan','Griffin'),('Forrest','Dunn'),('Dalton','Horne'),('Ryan','Hood');
16INSERT INTO contacts([first_name],[last_name]) VALUES('Mason','Paul'),('Xander','Nunez'),('Nasim','Reyes'),('Abbot','Buckner'),('Wayne','Beach'),('Rigel','Keith'),('Brennan','Dunn'),('Perry','Burns'),('Lars','Merritt'),('Burke','Boyle');
17INSERT INTO contacts([first_name],[last_name]) VALUES('Fuller','Vaughan'),('Davis','White'),('Nehru','Foster'),('Gareth','Santos'),('Odysseus','Oconnor'),('Camden','Myers'),('Jacob','Dorsey'),('Laith','Mooney'),('Bert','Wiggins'),('Lyle','Bradshaw');
18INSERT INTO contacts([first_name],[last_name]) VALUES('Cedric','House'),('Honorato','Rosario'),('Perry','Rasmussen'),('Charles','Dotson'),('Burton','Rivas'),('Cruz','Wheeler'),('Forrest','Cote'),('Fuller','Hooper'),('Raja','Holden'),('Raja','Leonard');

Alright, now that we have our already existing table example setup, let’s move on to LoopBack.


LoopBack is a highly-extensible, open-source Node.js framework

LoopBack allows you to do many things like:

  • Quickly create dynamic end-to-end REST APIs.

  • Connect devices and browsers to data and services.

  • Use Android, iOS, and AngularJS SDKs to easily create client apps.

  • Add-on components for push, file management, 3rd-party login, and geolocation.

  • Use StrongLoop Arc to visually edit, deploy, and monitor LoopBack apps.

  • LoopBack API gateway acts an intermediary between API consumers (clients) and API providers to externalize, secure, and manage APIs.

  • Runs on-premises or in the cloud

We are going to use LoopBack to:

  • Connect to our SQL Server database.

  • Discover the schema of our already existing table and create a LoopBack model based upon the schema.

  • Create a REST based API using this model.

Install LoopBack

Before we do any of that, let’s walk through the very simple steps needed to install LoopBack.

1npm install -g strongloop

Well that was easy! Now let’s scaffold our new LoopBack API application.

Run the LoopBack application generator and answer a few quick questions.

1$ slc loopback
2 _-----_
3 | | .--------------------------.
4 |--(o)--| | Let's create a LoopBack |
5 `---------´ | application! |
6 ( _´U`_ ) '--------------------------'
7 /___A___\
8 | ~ |
9 __'.___.'__
10 ´ ` |° ´ Y `
11? What's the name of your application? loopback-sqlserver-example
12? Enter name of the directory to contain the project: loopback-sqlserver-example

After the generator runs and npm installs all the necessary packages, you’ll receive a message like this.

1Next steps:
3 Change directory to your app
4 $ cd loopback-sqlserver-example
6 Create a model in your app
7 $ slc loopback:model
9 Optional: Enable StrongOps monitoring
10 $ slc strongops
12 Run the app
13 $ slc run .

The only thing left for us right now is to install a database connector for our application so that we can connect to SQL Server. Loopback offers a number of different connects, but we want loopback-connector-mssql.

1npm install loopback-connector-mssql --save

Connect to SQL Server

Now we need to point our LoopBack application to our SQL Server. In your loopback-sqlserver-example directory, open up the server/datasources.js file. Initially there is only a listing for the in-memory database, but we want to add a reference to our SQL Server.

Edit the file to look like this, of course, replacing with your SQL Server host, username and password.

2 "db": {
3 "name": "db",
4 "connector": "memory"
5 },
6 "mySqlServer": {
7 "host": "",
8 "port": 1433,
9 "database": "myDb",
10 "username": "username",
11 "password": "password",
12 "name": "mySqlServer",
13 "connector": "mssql",
14 "schema": "dbo"
15 }

Now we’re ready to build our LoopBack model for our SQL Server table.

LoopBack table model

LoopBack uses JSON models to define tables in the application. While we could create the JSON by hand, we want to use our already existing table to automatically generate it for us. Thankfully LoopBack offers the discoverSchema method which will auto generate the JSON for us based upon our already existent SQL Server table.

Create a server/discover.js file and put in this code.

1var app = require("./server");
2var dataSource = app.dataSources.mySqlServer;
5 "contacts",
6 {
7 owner: "dbo",
8 },
9 function (err, schema) {
10 console.log(JSON.stringify(schema, null, " "));
11 }

First we get a reference to our app and then to the mySqlServer data source we created in the datasource.js file. Then we tell LoopBack to go get the schema of our table and output the JSON model to the console.

Run this script and you’ll get LoopBack’s JSON model representation of your table.

1$ node server/discover.js
3 "name": "Contacts",
4 "options": {
5 "idInjection": false,
6 "mssql": {
7 "schema": "dbo",
8 "table": "contacts"
9 }
10 },
11 "properties": {
12 "id": {
13 "type": "Number",
14 "required": true,
15 "length": null,
16 "precision": 10,
17 "scale": 0,
18 "id": 1,
19 "mssql": {
20 "columnName": "id",
21 "dataType": "int",
22 "dataLength": null,
23 "dataPrecision": 10,
24 "dataScale": 0,
25 "nullable": "NO"
26 }
27 },
28 "firstName": {
29 "type": "String",
30 "required": false,
31 "length": 255,
32 "precision": null,
33 "scale": null,
34 "mssql": {
35 "columnName": "first_name",
36 "dataType": "varchar",
37 "dataLength": 255,
38 "dataPrecision": null,
39 "dataScale": null,
40 "nullable": "YES"
41 }
42 },
43 "lastName": {
44 "type": "String",
45 "required": false,
46 "length": 255,
47 "precision": null,
48 "scale": null,
49 "mssql": {
50 "columnName": "last_name",
51 "dataType": "varchar",
52 "dataLength": 255,
53 "dataPrecision": null,
54 "dataScale": null,
55 "nullable": "YES"
56 }
57 }
58 }

Cool! LoopBack just autogenerated a model of our contacts table based upon the already existing table.

Let’s get that model integrated into our app. We need to create two files to represent our contacts table in LoopBack.

1$ mkdir -p common/models
2$ touch common/models/contacts.js
3$ touch common/models/contacts.json

The JSON file will hold our model definition and JavaScript file will export our model. There are more uses for the JavaScript file which we’ll get into in future posts.

First, copy the JSON we generated above into the contacts.json file.

Next, drop this simple code into the contacts.js file.

1module.exports = function (Contact) {};

That’s it for creating our LoopBack model. Now we can tell LoopBack to use our model in the API.

Create the API

We need to tell LoopBack to use our newly created Contact model in the API. To do so, we open up server/model-config.json and add an entry for our model.

2 "_meta": {
3 "sources": [
4 "loopback/common/models",
5 "loopback/server/models",
6 "../common/models",
7 "./models"
8 ]
9 },
10 "User": {
11 "dataSource": "db"
12 },
13 "AccessToken": {
14 "dataSource": "db",
15 "public": false
16 },
17 "ACL": {
18 "dataSource": "db",
19 "public": false
20 },
21 "RoleMapping": {
22 "dataSource": "db",
23 "public": false
24 },
25 "Role": {
26 "dataSource": "db",
27 "public": false
28 },
29 "Contacts": {
30 "dataSource": "mySqlServer",
31 "public": true
32 }

model-config.json already has some definitions in it for the LoopBack base models. We’ll add our Contacts model at the end. Note that we set “public”: true to tell LoopBack to expose our model via the API.

Run the API

Now it’s time to fire up our API!

1$ node server/server.js

Browse to http://localhost:3000/explorer to see your API in action. Try to retrieve some data using the explorer and you can see that we have an working API!



It doesn’t take much to get an API up and running with LoopBack, even if you need to connect to and discover and already existing table in SQL Server - or in any database for that matter!