Express で Waterline(Sails ORM) を使う

Expressで開発しててORM導入に結構詰まったので備忘録

Express： Node.jsのための、高速で軽量なWebフレームワーク
Waterline： webフレームワークのSailsで使われているORM
Babel： ES6の機能をトランスパイルするもの AltJS

ディレクトリ構成

ディレクトリ構成

app.js
config/
    connection.js
    db.js
model/
    Photo.js
    Category.js

とりあえず必要な物だけ
実際にはコントローラ部分やビュー部分が追加されるはず

DB接続設定

config/connections.js

import mysqlAdapter from 'sails-mysql'

module.exports = {

  adapters: {'sails-mysql': mysqlAdapter},

  connections: {
    default: {
      adapter: 'sails-mysql',
      database: 'foo',
      host: 'bar',
      user: 'docker',
      password: 'docker',
      pool: false,
      ssl: false
    }
  },

  defaults: {
    // migrate: 'drop' // コネクションを貼った時点でDropするオプション
  }
}

接続先とかDBアダプタとか
MySQLとPostgreSQLは動作確認済み

config/db.js

import Waterline from 'waterline'
var waterline = new Waterline()
import connectionConfig from './connections'

var models = [
  'photo',
  'category'
]

models.forEach(function(model){
  waterline.loadCollection(require('../model/'+model))
})

module.exports = {}

module.exports.initialize = function(app, callback) {
  waterline.initialize(connectionConfig, function(err, models) {
    if(err) throw err
      callback(models.collections, models.connections)
  })
}
module.exports.getModels = models

モデルを読み込み

DB接続とサーバー起動

app.js

import express from 'express'

var app = express()
var db = require('./config/db')
db.initialize(app, function(collections, connections) {

  // モデルをグローバル変数へ example -> "exampleModel"
  db.getModels.forEach(function(model){
    eval("global." + model + "Model = collections." + model)
  })

  app.listen(8080, function () {
    console.log('Server listening on http://localhost:8080, Ctrl+C to stop')
  })
})

Sails風にモデルをグローバル変数へ

db.initialize(app, function(collections, connections) {
  app.models = collections
  app.connections = connections

グローバル変数にしたくない場合は上記
(参考：https://gist.github.com/particlebanana/8441398)

モデルの設定

model/Photo.js

import Waterline from 'waterline'

module.exports = Waterline.Collection.extend({
  identity: 'photo',
  connection: 'default',

  attributes: {
    name:'string',
    path:'string',

    has_category:{
      collection: 'category',
      via: 'has_photo',
      dominant: true
    }
  }

})

model/Category.js

import Waterline from 'waterline'

module.exports = Waterline.Collection.extend({
  identity: 'category',
  connection: 'default',

  attributes: {
    name:'string',

    has_photo: {
      collection: 'photo',
      via: 'has_category'
    }
  }

})

has_category, has_photoの部分が多対多の場合の設定

$ mysql

> show tables;
+----------------------------------------+
| Tables_in_foo                          |
+----------------------------------------+
| category                               |
| category_has_photo__photo_has_category |
| photo                                  |
+----------------------------------------+

> show full columns from category;
+-----------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field     | Type             | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-----------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| name      | varchar(255)     | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| id        | int(10) unsigned | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| createdAt | datetime         | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
| updatedAt | datetime         | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
+-----------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

> show full columns from category_has_photo__photo_has_category;
+--------------------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field              | Type             | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+--------------------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id                 | int(10) unsigned | NULL      | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| photo_has_category | int(11)          | NULL      | YES  |     | NULL    |                | select,insert,update,references |         |
| category_has_photo | int(11)          | NULL      | YES  |     | NULL    |                | select,insert,update,references |         |
+--------------------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+

> show full columns from photo;
+-----------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field     | Type             | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-----------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| name      | longtext         | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| path     | longtext         | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| id        | int(10) unsigned | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| createdAt | datetime         | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
| updatedAt | datetime         | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
+-----------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

使用例

controller

photoModel.find() // photoを全件
photoModel.find(1)  // photoのidが1のもの
photoModel.find({name:'foo'})  // photoのnameが'foo'のもの
photoModel.find().populate('has_category') // photoにcategoryをjoinしたものを全件

controller

import Q from 'q'

Q.all([
  photoModel.find(),
  categoryModel.find(),
]).spread(function(photos, categories){
  console.log(photos, categories)
}).catch(function(err){
  console.log(err)
})