Sails: Dynamic models question

[radoslavpetranov]

Hello,

First of all apologies as this is not really an issue but more of a question/discussion. I'm pretty new to sailsjs but a somewhat experienced nodejs/express/backbone/sequelize/q developer. Having said that, we use this stack at work for some of our products and I was never a fan until I came across sails. I can honestly say I now actually enjoy node and you guys are fully responsible for that. So there goes a massive THANK YOU for the great product you've come up with!!

Now to the point. One weakness of sequelize and (as far as I can tell) of the sails ORM is the inability to define dynamic models. In both my work and my personal projects I've come to a point where I need to map my model to a different table (or even a different database) but I can't do that. The models are always bound to a particular connection and a particular table so if I want to utilize different databases + tables that contain the same data I need to either statically define different models (ex: User1, User2, User3) or I need to drop the ORM functionality and rely on plain queries.

I know that this is not the most common case but I wouldn't call it rare either. The platform I work on at work dynamically creates many temporary databases with identical schema but different data and having the ability to map them using an ORM mechanism would definitely be beneficial. For the personal project that I work on right now I would like to keep the data (read multiple tables) for each registered company in a separate database (for both scaling and security reasons) but again I can't tell the ORM framework to retrieve user XYZ from a runtime-specified connection/db or a different table name in the current connection/db.

So my question is whether implementing a dynamic model would be a possible feature? Basically this would be just like regular models only without a predefined tableName and/or connection. All model functions such as create, find, destroy, etc would take 2 additional parameters: a dynamically created connection and/or a tableName. Obviously dynamic models would not support migrations.

Judging by the questions I found on google and my personal experience this seems like a pretty useful feature and one that would make you stand out from the nodejs crowd by a long shot IMO.

So someone familiar with the sails core care to chime in if doing something like that would be easy/hard/impossible?

Thanks and keep on rocking guys!

[tjwebb]

I need to map my model to a different table (or even a different database)

You can do this using the tableName property. See: http://sailsjs.org/#!/documentation/concepts/ORM/model-settings.html?q=tablename

The models are always bound to a particular connection and a particular table so if I want to utilize different databases + tables that contain the same data

This is sort of true, but if you have identical data in different data sources, I'm having trouble seeing the case for that. That's a data management issue; if the data truly has identical schema, then why not just ETL it all into the same datastore? Trying to solve this at the application layer would be difficult and onerous, and probably <1% of users need this feature.

[radoslavpetranov]

Thanks for the reply Travis!

I think you misunderstood my initial comment. I know I can use tableName in the Model definition but I don't think it's possible to pass a tableName to the model methods dynamically. So if I have different user tables (in the same or different databases) with the same schema I can't do like

User.find({connection : {baseConnection : 'mysql', hostname : 'aws.node.1.amazonaws.com'}, tableName : 'user'})...

// or if we have a single DB but the tables are different

User.find({tableName : 'company1234_user'})...

As for your other question, the reason why I want multiple datastores is security. If something happens and someone manages to break into the DB through a company account they can just do select * from whatever and then they've got all the information for all companies. But if I associate each company with different sql users (1 DB - Many tables) and give each user permissions only for the tables related to his company I can add this extra bit of security. Or even better - I can just keep each company in a separate database and let my backends manage everything through an admin DB accessible only through the AWS internal network. In the former approach I'd need to change the tableName and use the same connection and in the latter approach I'd have identical tableNames but I'd change the connection settings. Separating the data like that also makes management tasks easier because you can more easily target a single client and also it saves you from possible bugs that can return data from other clients unintentionally (since either the data resides in a different DB or it's accessed through an sql user that only has access to their own tables).

Since each model method ends up executing an sql query I figured this feature must be a 3-step task:

• add an optional hook for each model method that takes in a tableName and uses that in the query
• add the ability to create and destroy connections on the go without restarting the server (I'm not sure if that's possible or not right now)
• create a simple connection pool that keeps a max of X connections open and gets updated (if needed) before every query (again - no idea if something similar already exists or not)

You are correct in saying this is not a very common case and that's why I figured I'd ask whether it's just a matter of exposing a hook in the model methods that allows you to change the tableName / connection before executing the SQL or if there's a whole lot more to it.

Thanks for your reply once again and cheers!

[sailsbot]

Thanks for posting, @radoslavpetranov. I'm a repo bot-- nice to meet you!

It has been 60 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only _verified bugs_ with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

[smrutimandal]

Hi,

Turns out the need is not that occasional after all. As part of a project, I need to store data in different DBs and dynamically pick the model to operate on in runtime. FYI, the schemas are identical for all DBs.

An example is; dataRepoN.find() where the value of N is dynamically determined in run time from controller logic.

Due to stringent compliance, many projects are deciding to house the data belonging to separate customers in different DBs. So, I am sure this would benefit a large number of users.

Any assistance is greatly appreciated.