how can i create tables User, AccessToken, ACL, RoleMapping, ROLE,... in Mysql

[yagobski]

I want to use MySQL to manage Users. By default Loopback use DB dataSource. I have installed MySQL connector and all my custom models works good with mysql when i change datasource.

I want to use Mysql for Users model too. Do you have sql file with all schema for Users model?
Or there is automatic command can generate for me User, AccessToken, ACL, RoleMapping, ROLE,... tables.

Thanks

[projectxmaker]

@yagobski

if you named Mysql datasource as "mysql"

  "mysql": {
    "host": "localhost",
    "port": 3306,
    "database": "api",
    "username": "root",
    "password": "rootpassword",
    "name": "mysql",
    "connector": "mysql"
  },

then what you need to use it as "dataSource" of User, AccessToken, ACL, RoleMapping, Role in /server/model-config.json, for example:

  "User": {
    "dataSource": "mysql",
    "public": true
  },
  "AccessToken": {
    "dataSource": "mysql",
    "public": false
  },
  "ACL": {
    "dataSource": "mysql",
    "public": false
  },
  "RoleMapping": {
    "dataSource": "mysql",
    "public": false
  },
  "Role": {
    "dataSource": "mysql",
    "public": false
  },

then, to generate these tables, you could automigrate or for easier, use module grunt-loopback-auto.

[yagobski]

Thanks for your answer buy User, AccessToken ... already exists in explorer without creating the models. It comes with loopback by default. If i add like you suggest i have 2 User models in my explorer.

[raymondfeng]

You can use similar code as https://github.com/strongloop/loopback-example-database/blob/master/server/create-test-data.js#L15. The automigrate can also take an array of model names, for example:

dataSource.automigrate(['User', 'Application', 'Role', 'ACL', 'RoleMapping, 'AccessToken'], function(err) {
...
});

If the models parameter is not present, all models will be applied.

[yagobski]

Thanks it works for me :). Just last question how can i change ROLE for USER model. I don't have user.json in my models list.

I want to add something like this :

"acls": [
{
"accessType": "READ",
"principalType": "ROLE",
"principalId": "$everyone",
"permission": "ALLOW"
},
{
"accessType": "WRITE",
"principalType": "ROLE",
"principalId": "admin",
"permission": "ALLOW"
},
{
"accessType": "WRITE",
"principalType": "ROLE",
"principalId": "$everyone",
"permission": "DENY"
}

],

[yagobski]

Fixed thanks

[ghost]

Can we customise the table names for built in models? For example I'd like to name the built in User model as users in my mysql database and AccessToken as access_tokens. I understand it can be done for custom models using model.json. Just wondering how to do it with built in models?

[charlie-s]

@nivincp Seems to me that the only way to customize the name is to redefine the model. Hoping for another method.

[colceagus]

How do you create the default loopback model acls, roles, rolemapping etc. ?

after using loopback autoupdate to create the array of default models, the values for User acls in the acls table were not created, in fact, only the model tables (with fields) were created, but no properties of the model were persisted (like acls).

How can I achieve that?

[ninoguba]

I'm experiencing the same thing as @danielmihai described. I can see the User, Role, RoleMapping tables on my mongodb datasource but the ACL, and AccessToken are not "migrated". Anybody figured this out?

[colceagus]

I made a boot script which does this on app startup. Take a look at the boot scripts in my loopback prototype repo if you'd like. I had to extend the basic User etc. models. Kind of ugly and imo defeats the scope of loopback

[ninoguba]

Thanks @danielmihai your boot scripts gave me some ideas but I ended up just leaving ACL and AccessTokens in memory while the rest connected to a sql datasource. Works great so far. Do you guys see any issues with this approach?

[ghost]

Probably it'll clear the data inside these tables if it's configured in memory after a script restart.

[ninoguba]

Since the ACL and AccessTokens are in memory, I believe they are already automatically reset after every script restart.

[vkachan]

Started playing with Loopback and MySQL database and found that automigrate/autoupdate doesn't create relations between tables as well as ACL records for my models. I'm just wondering has anyone solved that somehow? I don't want to put this into the boot script as this makes usage of the relation generator and ACL generators kinda needless.

[colceagus]

I did it like that. Daniel M. Colceag

…

On 26 Feb 2017, at 18:48, Victor Kachan ***@***.***> wrote: Started playing with Loopback and MySQL database and found that automigrate/autoupdate doesn't create relations between tables as well as ACL records for my models. I'm just wondering has anyone solved that somehow? I don't want to put this into the boot script as this makes usage of the relation generator and ACL generators kinda needless. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[vkachan]

@danielmihai, I dived deep into the code of loopback-connector-mysql and I found that logic does not exist there, only creation of the tables. Thereby it seems like creation of the fk's and ACL records is a manual job :(

[Vandivier]

I'm having this issue in loopback 3. I'm reviewing Daniel's solution but at first glance the API is loopback 2 (app.models(), etc) and it's postgresql.

Here's what I'm seeing and here's the repro.

The below code is my automigrate.js in /boot:

module.exports = async function (app) {
const oEllaDB = app.dataSources.EllaDB;
const Item = app.models.Item;
const EllaUser = app.models.EllaUser;

const oEllaUserJohn = { // for testing/developing only. remove before production.
    'password': '1234', // #amyschumerbelike
    'role-name': 'developer', // TODO: autogenerate
    'username': 'john'
}

oEllaDB.automigrate(['User', 'Application', 'Role', 'ACL', 'RoleMapping', 'AccessToken'], function(){}); // ref: https://github.com/strongloop/loopback/issues/591

// first autoupdate the `EllaUser` model to avoid foreign key constraint failure
oEllaDB.automigrate('EllaUser', function (err) {
    if (err) throw err;

    EllaUser.create(oEllaUserJohn);

    console.log('\nAutomigrated table `EllaUser`.');

    oEllaDB.automigrate('Item', function (err) {
        if (err) throw err;
        console.log('\nAutomigrated table `Item`.');
        // at this point the database table `Item` should have foreign key `userId` or `ellaUserId`
    });
});
};

[andrewpaulino]

Has any solutions came up, or are going to have to manually recreate all the tables with our SQL DB. I am assuming the reason I can't login is because mySQL DB has no reference to the accessTokens?

[yongqiangyue]

app.dataSources.mysqlDs.automigrate(['User', 'Application', 'Role', 'ACL',
'RoleMapping', 'AccessToken'], function(err) {
if (err) throw err; // must have
});