loopback-mysql-example

This project contains examples to demonstrate LoopBack MySQL connector. The MySQL connector module allows LoopBack applications to interact with MySQL databases for the data models, such as Account and Catalog.

For those who are not familiar with LoopBack, it’s an open source mobile backend framework that connects mobile devices to enterprise data. LoopBack provides out-of-box data access capabilities for models through pluggable datasources and connectors. Connectors provide connectivity to variable backend systems, such as databases or REST APIs. Models are in turn exposed to mobile devices as REST APIs and SDKs.

Prerequisite

First, make sure you have strong-cli installed.

    npm install -g strong-cli

Next, you need a running MySQL server. In this article, you'll connect to an instance running on demo.strongloop.com.

Create the LoopBack application

To demonstrate how to use MySQL connector for LoopBack, we'll create a simple application from scratch using the slc command:

    slc lb project loopback-mysql-example
    cd loopback-mysql-example
    slc lb datasource mysql --connector mysql
    slc lb model account -i --data-source mysql

Follow the prompts to create your model with the following properties:

• email: string - The email id for the account
• level: number - The game level you are in
• created: date - The date your account is created
• modified: date - The date your account is updated

The properties will be saved to models.json.

Install dependencies

Let's add the loopback-connector-mysql module and install the dependencies.

    npm install loopback-connector-mysql --save

Configure the data source

The generated data source use the memory connector by default, to connect to MySQL, we'll modify the data source configuration as follows.

    vi datasources.json

Note: Future releases will probably generate a config.json file for the data source configuration.

In datasoures.json, replace the data source configuration for mysql with the following snippet:

    "mysql": {
    "connector": "mysql",
    "host": "demo.strongloop.com",
    "port": 3306,
    "database": "demo",
    "username": "demo",
    "password": "L00pBack"
  }

Create the table and add test data

Now we have an account model in LoopBack, do we need to run some SQL statements to create the corresponding table in MySQL database?

Sure, but even simpler, LoopBack provides Node.js APIs to do so automatically. The code is create-test-data.js.

    node create-test-data

Let's look at the code:

    dataSource.automigrate('account', function (err) {
      accounts.forEach(function(act) {
        Account.create(act, function(err, result) {
          if(!err) {
            console.log('Record created:', result);
          }
        });
      });
    });

dataSource.automigrate() creates or recreates the table in MySQL based on the model definition for account. Please note the call will drop the table if it exists and your data will be lost. We can use dataSource.autoupdate() instead to keep the existing data.

Account.create() inserts two sample records to the MySQL table.

Run the application

    node app

Open your browser now.

To get all accounts, go to http://localhost:3000/api/accounts.

    [
      {
        "email": "[email protected]",
        "level": 10,
        "created": "2013-10-15T21:34:50.000Z",
        "modified": "2013-10-15T21:34:50.000Z",
        "id": 1
      },
      {
        "email": "[email protected]",
        "level": 20,
        "created": "2013-10-15T21:34:50.000Z",
        "modified": "2013-10-15T21:34:50.000Z",
        "id": 2
      }
    ]

To get an account by id, go to http://localhost:3000/api/accounts/1.

    {
      "email": "[email protected]",
      "level": 10,
      "created": "2013-10-15T21:34:50.000Z",
      "modified": "2013-10-15T21:34:50.000Z",
      "id": "1"
    }

All the REST APIs can be explored at:

http://127.0.0.1:3000/explorer

Try the discovery

Now we have the account table existing in MySQL, we can try to discover the LoopBack model from the database. Let's run the following example:

    node discover

First, we'll see the model definition for account in JSON format.

{
  "name": "Account",
  "options": {
    "idInjection": false,
    "mysql": {
      "schema": "demo",
      "table": "account"
    }
  },
  "properties": {
    "id": {
      "type": "Number",
      "required": false,
      "length": null,
      "precision": 10,
      "scale": 0,
      "id": 1,
      "mysql": {
        "columnName": "id",
        "dataType": "int",
        "dataLength": null,
        "dataPrecision": 10,
        "dataScale": 0,
        "nullable": "NO"
      }
    },
    "email": {
      "type": "String",
      "required": false,
      "length": 765,
      "precision": null,
      "scale": null,
      "mysql": {
        "columnName": "email",
        "dataType": "varchar",
        "dataLength": 765,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    },
    ...
    }
  }
}

Then we use the model to find all accounts from MySQL:

[ { id: 1,
    email: '[email protected]',
    level: 10,
    created: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT),
    modified: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT) },
  { id: 2,
    email: '[email protected]',
    level: 20,
    created: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT),
    modified: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT) } ]

Let's examine the code in discover.js too. It's surprisingly simple! The dataSource.discoverSchema() method returns the model definition based on the account table schema. dataSource.discoverAndBuildModels() goes one step further by making the model classes available to perform CRUD operations.

    dataSource.discoverSchema('account', {owner: 'demo'}, function (err, schema) {
        console.log(JSON.stringify(schema, null, '  '));
    });

    dataSource.discoverAndBuildModels('account', {}, function (err, models) {
        models.Account.find(function (err, act) {
            if (err) {
                console.error(err);
            } else {
                console.log(act);
            }
        });
    });

As you have seen, the MySQL connector for LoopBack enables applications to work with data in MySQL databases. It can be new data generated by mobile devices that need to be persisted, or existing data that need to be shared between mobile clients and other backend applications. No matter where you start, LoopBack makes it easy to handle your data with MySQL. It’s great to have MySQL in the Loop!

History

Version 0.0.1 - https://github.com/strongloop-community/loopback-mysql-example/releases/tag/0.0.1