We’re happy to announce the release of the LoopBack MySQL Connector that enables LoopBack applications to access MySQL databases.  It is being released as part of StrongLoop Suite 1.1, as a GitHub module, and as an NPM package .

StrongLoop Suite includes the LoopBack open-source mobile backend framework that enables mobile apps to connect to enterprise data through models that use pluggable data sources and connectors. Connectors provide connectivity to backend systems such as databases. Models are in turn exposed to mobile devices through REST APIs and client SDKs.

Let’s start to explore this new module step by step. The code below is available at https://github.com/strongloop-community/loopback-mysql-example. You can browse the project as you go or check it out to your computer with this command:

git clone git@github.com:strongloop-community/loopback-mysql-example.git

Prerequisites

First, make sure you have strong-cli tools 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

Now use the  slc command to  create a simple application from scratch:

slc lb project loopback-mysql-example
cd loopback-mysql-example
slc lb model account -i

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 modules/account/properties.json.

    Install dependencies

    Next, add the loopback-connector-mysql module and install the dependencies:

    npm install loopback-connector-mysql --save
    slc install
    

    Configure the data source

    The generated data source uses the memory connector by default.  To connect to MySQL instead, you’re going to modify the data source configuration.  Enter these commands:

    cd modules/db
    vi index.js
    

    In index.js replace this code:

    module.exports = loopback.createDataSource({
      connector: loopback.Memory
    });
    

    with the following code:

    module.exports = loopback.createDataSource({
      connector: require('loopback-connector-mysql'),
      host: 'demo.strongloop.com',
      port: 3306,
      database: 'demo',
      username: 'demo',
      password: 'L00pBack'
    });
    

    Create the table and add test data

    Now you have an account model in LoopBack, you need to run some SQL statements to create the corresponding table in MySQL database.  LoopBack provides a Node.js API to make this really easy.

    Look at the code in create-test-data.js:

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

    The call to dataSource.automigrate() creates or recreates the table in MySQL based on the model definition for account. Note this function will drop the table if it exists and your data will be lost.  Use dataSource.autoupdate() instead if you need to keep existing data.

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

    Now run this code by entering this command:

    node create-test-data
    

    You’ll see some messages about two records created.  As usual with Node, you’ll need to hit Ctrl-C to terminate the app after it displays these messages.

    Run the application

    node app
    

    Open your browser to http://localhost:3000/accounts.  You’ll see the JSON describing the two records that you just added to the accounts table:

    [
      {
        "email": "foo@bar.com",
        "level": 10,
        "created": "2013-10-15T21:34:50.000Z",
        "modified": "2013-10-15T21:34:50.000Z",
        "id": 1
      },
      {
        "email": "bar@bar.com",
        "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/accounts/1.

    {
      "email": "foo@bar.com",
      "level": 10,
      "created": "2013-10-15T21:34:50.000Z",
      "modified": "2013-10-15T21:34:50.000Z",
      "id": "1"
    }
    

    You can explore the REST API that LoopBack created with the API Explorer at:

    http://127.0.0.1:3000/explorer

    Try model discovery

    Now you have the account table in MySQL, you can “discover” the LoopBack model from the database. Run the following example:

    node discover
    

    First, you’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: 'foo@bar.com',
      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: 'bar@bar.com',
      level: 20,
      created: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT),
      modified: Tue Oct 15 2013 14:34:50 GMT-0700 (PDT) } ]
    

    Now, examine the code in discover.js. It’s surprisingly simple! The dataSource.discoverSchema() method returns the model definition based on the account table schema.

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

    The dataSource.discoverAndBuildModels() method goes one step further by making the model classes available to perform CRUD operations.

    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!

    What’s next?

  • What’s in the upcoming Node v0.12 release? Six new features, plus new and breaking APIs.
  • Ready to develop APIs in Node.js and get them connected to your data? Check out the Node.js LoopBack framework. We’ve made it easy to get started either locally or on your favorite cloud, with a simple npm install.
  • Need training and certification for Node? Learn more about both the private and open options StrongLoop offers.