Auto-create mysql table with StrongLoop

Question

I am trying to use Strongloop with MySql but cannot figure out how to migrate or automatically create tables into a MySql database.

Is there at least a way to export the models into MySql schemas or do I have to manually create the tables?

I've been trying with the mysql demo app, and going over the docs for a while but no luck - http://docs.strongloop.com/display/DOC/MySQL+connector

Thanks!

Taskwork

This is the cleanest answer. All the others require modifications as soon as you change the model's datasource config, which is error prone. – Eam

Answer 1

LoopBack calls it auto-migration. Check these links and search for that term:

Recipes for LoopBack Models, part 5 of 5: Model Synchronization with Relational Databases

Data sources and connectors

Manheim

thnks, although I guess I shoulda specified that I've been reading over those docs over and over but can't figure it out. I'm confused as to where and how to use the JS configuration ds.automigrate(schema_v1.name, function () {} when using datasources.json to define my db connector – Taskwork

the second link is dead, not sure what original link was supposed to link to, but if you could update it that would be great. – Braxton

Answer 2

I created /server/boot/autoupdate.js. It runs when the app boots. It loads "model-config" and "datasources" JSON and migrates or updates all models to the datasources defined for them.

# /server/boot/autoupdate.js
module.exports = function(app) {
    var path = require('path');
    var models = require(path.resolve(__dirname, '../model-config.json'));
    var datasources = require(path.resolve(__dirname, '../datasources.json'));

    function autoUpdateAll(){
        Object.keys(models).forEach(function(key) {
            if (typeof models[key].dataSource != 'undefined') {
                if (typeof datasources[models[key].dataSource] != 'undefined') {
                    app.dataSources[models[key].dataSource].autoupdate(key, function (err) {
                        if (err) throw err;
                        console.log('Model ' + key + ' updated');
                    });
                }
            }
        });
    }

    function autoMigrateAll(){
        Object.keys(models).forEach(function(key) {
            if (typeof models[key].dataSource != 'undefined') {
                if (typeof datasources[models[key].dataSource] != 'undefined') {
                    app.dataSources[models[key].dataSource].automigrate(key, function (err) {
                        if (err) throw err;
                        console.log('Model ' + key + ' migrated');
                    });
                }
            }
        });
    }
    //TODO: change to autoUpdateAll when ready for CI deployment to production
    autoMigrateAll();
    //autoUpdateAll();

};

Muncey

This is WAY overly complicated. Look this answer instead – Eam

The answer linked by @Eam is incomplete and does not handle all the cases as well as this answer. – Luxembourg

Arguments to support your point ? This code is just rewriting what is already implemented in loopback-datasource-juggler. The documentation is very clear. Model to migrate. If not present, apply to all models. Same for autoupdate. – Eam

This code is also pretty bad because rather than throwing an error if a model has a misconfigured datasource that does not exist it will just skip to the next model and ignore it. – Eam

@jduhls: Thanks for the script. All the models are migrated / updated, but in User model Login (/login) & Logout (/logout) methods are not generated (or are not displayed) in my loopback application. All other existing methods are displayed & working as usual. Can you provide a fix for this issue!!! Thanks in advance :-) – Tool

Answer 3

You can simply migrate models by adding following lines to your server.js file before app.start method:

app.datasources['mySqlConnection'].automigrate(['orders','customers', 'User', 'ACL'], function(err) {
     console.log(err);
});

1. Add models to the array as per your need.
2. Run the application by slc run.

Note: mySqlConnection is the connection name, replace it by your own connection name.

Jeffcott

When I run the application again my data is loses – Outlaw

Yes, it will. Because when you run migration again for the tables which are exists, It will re-create them. If you want to migrate new tables then only pass those tables in automigrate array. – Jeffcott

@Ben it does here docs.strongloop.com/display/public/LB/… – Sumerlin

@Outlaw They have auto-update method for the tables that have data in them docs.strongloop.com/display/public/LB/… – Sumerlin

One minor change that I needed to get this working was to place these lines within the app.start (first statement) and not before. It doesn't appear that the datasources hash is loaded until you start the app. – Gerigerianna

Answer 4

To update and/or create all mysql tables for your models:

var dataSource = app.dataSources.mysql;       
dataSource.autoupdate(null, function (err) {
    if(err) return cb(err);
    return cb();
});      

Glebe

This should be the accepted answer. Indeed, when first parameter is null, the autoupdate or automigrate function will create/update datasource table for all server models, automatically. See the doc – Eam

Where did you define this code? In a boot file? server.js? Does it matter? Is there a convention? – Interpol

I tried this with a js in the boot folder and so far it works ok. – Berserk

Answer 5

In my case, I manually created MySQL tables and then created the models. For existing MySQL tables, I create the models where property names are the same as MySQL field's names.

So here are my steps in using StrongLoop LoopBack with MySQL Database:

1. Create MySQL Database and Tables (or use existing database).
2. Install MySQL connector using npm install loopback-connector-mysql --save
3. Add your MySQL Database details on datasources.json file.
4. Create a model for each table using slc lb model tablename -i OR edit models.json file and add the properties manually. (document: http://docs.strongloop.com/display/DOC/Creating+a+LoopBack+application#CreatingaLoopBackapplication-Creatingmodels)
5. Properties' names should be the same as MySQL field's names (more information on mapping MySQL to JSON data types: http://docs.strongloop.com/display/DOC/MySQL+connector#MySQLconnector-MySQLtoJSONtypes)

Contagion

Answer 6

In the same kind of issue, if you need to automatically create a database, you can use the createDatabase option in your dataSource JSON file.

  "mysql": {
    "host": "localhost",
    "port": 0,
    "database": "db",
    "username": "root",
    "password": "",
    "name": "mysql",
    "connector": "mysql",
    "debug": false,
    "createDatabase": true
  }

So you don't need to write yourself the queries to create the base. Hope it helps.

Wardieu

This didn't seem to create a database in my case – Ladd

Answer 7

jduhls answer is beautiful, but I needed to tweak it slightly to add some static data into tables. Here's my tweaked version, along with an example of loading data into a simple SystemSettings table (id, settingName, settingValue):

var async = require('async');

var SYSTEM_SETTINGS = [
  {
    "settingName": "mustPayInAdvance",
    "settingValue": "false",
  }
];

module.exports = function(app) {
    var path = require('path');
    var models = require(path.resolve(__dirname, '../model-config.json'));
    var datasources = require(path.resolve(__dirname, '../datasources.json'));
    var modelUpdates = [];

    function buildModelListForOperation(){
        Object.keys(models).forEach(function(key) {
            if (typeof models[key].dataSource != 'undefined') {
                if (typeof datasources[models[key].dataSource] != 'undefined') {
                    modelUpdates.push({operation: app.dataSources[models[key].dataSource], key: key});
                }
            }
        });
    }

    function createStaticData() {
        app.models.SystemSettings.create(SYSTEM_SETTINGS, function(err, created) {
            if (err) 
                throw err;
            else
                console.log('Sample data was imported.');
        });
    }

    function processModelsAndData(operationType) {
        buildModelListForOperation();

        // Create all models
        async.each(modelUpdates, function(item, callback) {
            item.operation[operationType](item.key, function (err) {
                if (err) throw err;
                console.log('Model ' + item.key + ' migrated');
                callback();
            });
        }, function (err) {
            if (err) throw err;
            createStaticData();
        });    
    }

    //TODO: change to 'autoupdate' when ready for CI deployment to production
    processModelsAndData('automigrate');
};

Ebro

Answer 8

i discovered an easy way to accomplish this task. The reference link is: Clique Here

You can use prototype or not, in my case, i do nott used.

For the documentation, you should use:

    ds.autoupdate (models, function (error) {
        if (!error) {
            console.log( "Updated models.");
        }else{
            console.log( "An error has occurred:" + error);
        }
        ds.disconnect();
    });

Where:

    var path = require ( 'path');
    var app = require (path.resolve (__ dirname, '../server/server'));
    var ds = app.datasources.x;

and x is datasource attribute name, example of /server/datasources.json:

    {
      "x": {
        "Host": "localhost"
        "Port": 3306,
        "Database", "loopapp"
        "Password": "",
        "Name": "x"
        "User", "root"
        "Connector": "mysql"
      }
    }

Note (1): Models can be the string model name or the array of string (models names).

Note (2): If you prefer not to put models, all models of the file whose base attribute equals "PersistedModel", will be updated.

With that, i used like this:

    autoupdate function () {
        ds.autoupdate (function (error) {
          if (!error) {
                console.log( "Updated all models");
          }else {
                console.log( "An error has occurred:" + error);
          }
          ds.disconnect();
        });
    }
    

and i called the: autoupdate();

You can put this code in a file.js and call the command line: node file.js.

If you want this file to be called every time you start the program, put it on /server/boot/file.js path.

Obviously, if you want to use automigrate, only replace the autoupdate word in the code above, by automigrate.

Zygospore