Connect your API to a data source

You’re going to take the app from the previous section and connect it to MySQL.   

Get the app (in the state following the last article) from GitHub and install all its dependencies:

$ git clone https://github.com/strongloop/loopback-getting-started.git
$ cd loopback-getting-started
$ git checkout lb2-step1
$ npm install

Add a data source

Now you’re going to define a data source using the Data source generator:

$ slc loopback:datasource

The generator will prompt you to name the data source:

[?] Enter the data-source name:

Enter mysqlDs and hit Enter.

Next, the generator will prompt you for the type of data source:

[?] Select the connector for mysqlDS: (Use arrow keys)
  other
  In-memory db (supported by StrongLoop)
  MySQL (supported by StrongLoop)
  PostgreSQL (supported by StrongLoop)
  Oracle (supported by StrongLoop)
  Microsoft SQL (supported by StrongLoop)
  MongoDB (supported by StrongLoop)
(Move up and down to reveal more choices)

Press the down-arrow key to highlight MySQL, then hit Enter.  

The tool adds the data source definition to the server/datasources.json file, which will now look as shown below.  Notice the “mysqlDs” data source you just added, as well as in-memory data source named “db,” which is there by default.

{
  "db": {
    "name": "db",
    "connector": "memory"
  },
  "mysqlDs": {
    "name": "mysqlDs",
    "connector": "mysql"
  }
}

Install MySQL connector 

Now add the loopback-connector-mysql module and install the dependencies:

$ npm install loopback-connector-mysql --save

Configure data source

Next, you need to configure the data source to use the desired MySQL server.

Edit /server/datasources.json and after the line

` “connector”: “mysql”`

add host, port, database, username, and password properties.  

To use the StrongLoop MySQL server: running on demo.strongloop.com, then enter the values shown below.  

To use your own MySQL server: enter the hostname, port number, and login credentials for your server. 

{
  "db": {
    "name": "db",
    "connector": "memory"
  },
  "mysqlDs": {
    "name": "mysqlDs",
    "connector": "mysql",
    "host": "demo.strongloop.com",
    "port": 3306,
    "database": "getting_started",
    "username": "demo",
    "password": "L00pBack"
  }
}

Connect CoffeeShop model to MySQL

Now you’ve created a MySQL data source and you have a CoffeeShop model; you just need to connect them.  LoopBack applications use the model-config.json file to link models to data sources.  Edit /server/model-config.json and look for the CoffeeShop entry:

...
  "CoffeeShop": {
    "dataSource": "db",
    "public": true
  }
  ...

Change the dataSource property from db to mysqlDs.  This attaches the CoffeeShop model to the MySQL datasource you just created and configured:

...
  "CoffeeShop": {
    "dataSource": "mysqlDs",
    "public": true
  }
  ...

Add some test data and view it

Now you have a CoffeeShop model in LoopBack, how do you  create the corresponding table in MySQL database?

You could try executing some SQL statements directly…but LoopBack provides a Node API to do it for you automatically using a process called auto-migration.  For more information, see Creating a database schema from models.

The loopback-getting-started module contains the create-sample-models.js script to demonstrate auto-migration.  If you’ve been following along from the beginning (and didn’t just clone this module), then you’ll need to copy it from below or from GitHub .  Put it in the application’s /server/boot directory so it will get executed when the application starts.

module.exports = function(app) {
  app.dataSources.mysqlDs.automigrate('CoffeeShop', function(err) {
    if (err) throw err;

    app.models.CoffeeShop.create([{
      name: 'Bel Cafe',
      city: 'Vancouver'
    }, {
      name: 'Three Bees Coffee House',
      city: 'San Mateo'
    }, {
      name: 'Caffe Artigiano',
      city: 'Vancouver'
    }, ], function(err, coffeeShops) {
      if (err) throw err;

      console.log('Models created: \n', coffeeShops);
    });
  });
};

This will save some test data to the data source.

Now run the application:

$ node .

In the console, you’ll see this:

...
Browse your REST API at http://0.0.0.0:3000/explorer
Web server listening at: http://0.0.0.0:3000/
Models created: [ { name: 'Bel Cafe',
    city: 'Vancouver',
    id: 1 },
  { name: 'Three Bees Coffee House',
    city: 'San Mateo',
    id: 3 },
  { name: 'Caffe Artigiano',
    city: 'Vancouver',
    id: 2 } ]

You can also use the API Explorer:

1. Browse to http://0.0.0.0:3000/explorer/ (you may need to use http://localhost:3000/explorer, depending on your browser and OS).
2. Click GET  /CoffeeShops  Find all instance of the model matched by filter…
3. Click Try it out!
4. You’ll see the data for the three coffee shops created in the above script.