To use Waterline or Not to use Waterline

[Randall Meeker]

I'm trying to plan an new project and I have used sails with waterline to date. 

However, I have another project coming up and I know of one fact. Were using MySQL and never going to use nothing else anytime soon. 

I know that waterline is missing a few things here an there that can be found in other node DB adapters. 

My question is what do I lose by NOT using waterline aside from a generic adapter that can be used with different DBs?

Is anyone else NOT using waterline, are their any write ups about this, how to setup my app if I'm not using waterline (specific settings that need to be turned on / off and what not)?

[Manuel Darveau]

Hi,

For the last 4 months, we have been busy developing an application to manage sports league: maligue.ca. We started dev with sails, waterline and mongodb. We then switched to postgres just before go live and the transition was smooth.

We are not using waterline anymore for two reasons:

1)

However, 1 week after the beginning of the beta testing, we lost all data in one table. After several hours of investigation, we found out that there is (was) a bug in waterline that generated a delete statement without where clause: https://github.com/balderdashy/waterline/issues/812

I can understand and live with the fact that there could be bugs in such a young open source library. I opened the issue which pointed to the source of the problem (didn't had a patch but pointed to the exact line of the issue) and 2 days later the issue was closed. However, this issue was discovered 2 weeks before and the only answer by the maintainer was "Yikes!". 

I was disappointed by the lack of responsiveness from the maintainers. I would have expected the following:

* Investigation and release ASAP

* Notification to the sails maintainer so they update the dependency and release a new version. BTW, the latest sails 0.10.x release still depend on the bugged waterline version.

* Notification on the user mailing that a major issue was found and that update is required asap.

Nothing of that happened... so we decided to drop waterline.

2)

One reason we switched from mongodb to postgres is that we required an ACID transactional database. The switch to postgres went smooth... until we realized that there is no transaction support in waterline. I have read hacks here and there but it is not properly supported. We will be processing payments soon and we need to be rock solid on what data is persisted and when.

It's a bit awkward to do promotion for another library on this list, but I assume that this will be the next question. We are in the process of rewriting the whole model layer using bookshelfjs.org / knexjs.org.

My 2 cents

Manuel

[Randall Meeker]

This is good information. Since I know i'm going to be using MySql without a doubt, I am tempted to do what your doing and use a more robust dedicated library. 

I don't think its awkward to talk about your implementation and libraries you have used. Someone tell me I'm wrong, but It has been made plain that sails / waterline were built so that they could independent of each other and I think discussing other ORMs with sails is perfectly valid. Your use case even shows why and I think the sails team can be applauded for such a design. 

I would ask advice on how you uncoupled waterline and drop in bookshelf / knex ?

[Brooke Ganz]

This is all good information to have, thanks for posting it to the list.  I'm also planning to use Sails with a non-Waterline ORM, but I'm eyeing Sequelize (http://sequelizejs.com).  I need it because I'm planning on having nested models populate multiple other models, going pretty far down the line, and it looks like Waterline can't quite handle that yet -- or couldn't the last time I looked at the GitHub issues anyway.

If anyone has written a tutorial or posted code samples for how to combine Sails with ORM's like Sequelize or Bookshelf, and if links to those articles or code could be posted to this group, that would be really helpful.

- Brooke

[Randall Meeker]

+ 1 articles or how to. I'm trying to figure out he best way to uncouple waterline and re-couple bookshelf. 

[Ryan Emberling]

+1 I would love any help doing this. I am very dissatisfied with the inability to recursively populate models, as well as the awkwardness of sorting collections (you can't change the indices of models in a collection easily and you can't easily sort a collection during your find() command for the model with the collection attribute). I would love to leverage a more powerful ORM.

[Manuel Darveau]

Stay tuned, I will post a "how to" and we will opensource the hook and db migration tool in a few weeks.

Manuel

[Randall Meeker]

totally tuned!

[Manuel Darveau]

Hi,

I still haven't had time to package everything, but I figured I could just paste the hook I created and some basic instruction.

In the api/hooks, add a sequelize folder with a file named index.coffee (or js if that's your thing):

###

# sequelize hook

###

module.exports = ( sails ) ->

  global['Sequelize'] = require('sequelize')

  

  # TODO Add config to enable cls and configure namespace

  Sequelize.cls = require('continuation-local-storage').createNamespace('sails-sequelize-#{connection.database}')

  

  {

    

  initialize: ( next ) ->

    console.log "Using connection named #{sails.config.models.connection}"

    

    connection = sails.config.connections[sails.config.models.connection]

    if !connection?

      throw new Error("Connection '#{sails.config.models.connection}' not found in config/connections")

      

    connection.options ?= {}

    # Reformat the logged SQL to remove all attributes

    connection.options.logging = ( log ) ->

      if log.indexOf "Executing" == 0

        indexOfSelect = log.indexOf ": SELECT"

        if indexOfSelect > 0

          indexOfFrom = log.indexOf " FROM "

          console.log log.substring(0, indexOfSelect + 8) + " {}" + log.substring( indexOfFrom )

          return

      console.log log

      

    console.log "Connection details #{JSON.stringify(connection)}"

    if connection.url?

      console.log "via url"

      sequelize = new Sequelize( connection.url, connection.options )

    else

      sequelize = new Sequelize( connection.database, connection.username, connection.password, connection.options )

      

    # Keep the sequelize in the config

    global['sequelize'] = sequelize

      

    sails.modules.loadModels (err, models) ->

      return next(err) if err?

      

      # Load models and add them to global

      for modelName, modelDef of models

        console.log "Loading model '#{modelDef.globalId}'"

        global[modelDef.globalId] = sequelize.define( modelDef.globalId, modelDef.attributes, modelDef.options )

      

      # Define relations if any

      for modelName, modelDef of models

        if modelDef.associations?

          console.log "Loading associations for '#{modelDef.globalId}'"

          modelDef.associations?( modelDef )

        

      if connection.options.sync in [true, 'force']

        console.log "Will sync with force == #{connection.options.sync == 'force'}"

        sequelize.sync(

          force: connection.options.sync == 'force'

        ).nodeify(next)

    

  }

Then, you keep your model in the same folder but change the syntax a little bit to something like:

module.exports =

  attributes:

    date:

      type: Sequelize.DATE

    durationMs:

      type: Sequelize.INTEGER

    homeTeamScore:

      type: Sequelize.INTEGER

    awayTeamScore:

      type: Sequelize.INTEGER

  associations: () ->

    Game.League = Game.belongsTo League, {foreignKey: { allowNull: false }}

    

    Game.HomeTeam = Game.belongsTo Team, {as: 'HomeTeam'}

    Game.AwayTeam = Game.belongsTo Team, {as: 'AwayTeam'}

    Game.Location = Game.belongsTo Location, {foreignKey: { allowNull: false }}

    Game.GameSpots = Game.hasMany GameSpot, {onDelete:'CASCADE'}

    

    Game.GameSubstitutionRequests = Game.hasMany GameSubstitutionRequest, {onDelete:'CASCADE'}

  options:

    freezeTableName: true

    

    instanceMethods:

      # Return Promise<Game>

      lock: () ->

        Game.find( this.id,

          lock: Sequelize.Transaction.LOCK.UPDATE

        )

The hook will read attributes and options, and call associations so you can hook up your associations.

in your config/connections file, you should have something like:

module.exports.connections =

  memoryDb:

    database: 'maligue'

    options:

      sync: 'force'

      dialect: 'sqlite',

      storage: ':memory:'

Note the sync option can be: true, false, 'sync'.

I have converted the whole application to Sequelize and just love it. The maintainers are super responsive (typically get an answer within a few hours) and I still didn't hit something the library does not support. It also made me convert the whole application to promise instead of callback and this was a huge improvement to the codebase.

Hope this helps!

Manuel

[Moises Rodriguez]

Hi Manuel,

Thanks for posting the hook. Just a couple questions. Where did you put Sequelize? Also I don't have an api/hooks folder, so I just add the hooks folder in api and it automatically picks it up?

Thanks!

[Manuel Darveau]

What do you mean by "Where did you put Sequelize"? I just added "sequelize": "^2.0.5", to the dependencies in package.json

Yes, just create an api/hook folder and then add the sequelize folder and an index.coffee (or index.js if you convert to js. Check http://js2.coffee/ if you need help): YOUR_APP/api/hooks/sequelize/index.coffee

I found that by looking at https://github.com/balderdashy/sails/tree/master/lib/hooks and digging into the code. Let's not start another thread about sails documentation. :-)

As I said before, we are working on how we will integrate the migration process but it's not that simple.

Manuel

[Moises Rodriguez]

You just answered my questions. I'm going to give it a try and see if I can get it working with Sequelize. 

Thanks!