Sequelize

What is an ORM?

"Object Relational Mapper"

Sequelize setup

$ npm install --save sequelize

# And one of the following:
$ npm install --save pg pg-hstore
$ npm install --save mysql // For both mysql and mariadb dialects
$ npm install --save sqlite3
$ npm install --save tedious // MSSQL

Sequelize setup

var Sequelize = require('Sequelize');

var db = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql',
});

var db = new Sequelize('postgres://user:pass@host:5432/dbname');

Models

var Restaurant = db.define('Restaurant', {
  name: { type: Sequelize.STRING, },
  founded: { type: Sequelize.INTEGER }
});

var Dish = db.define('Dish', {
  name: { type: Sequelize.STRING, },
  spicy: { type: Sequelize.BOOLEAN }
});

Relationships

• hasOne
• belongsTo
• hasMany

Automatically add foreign key id fields

Restaurant.hasMany(Dish);
//adds RestaurantId to Dish

Syncing Models

db.sync()

db.sync({force: true});

In sequelize-restaurants...

1. npm install
2. create a database (test-db, node-workshop, whatever)
3. edit index.js to match your environment
4. node .

Hints:

• Grants! Consider using root
  (never in production of course ☺)
• Database must be created, but not tables

Restaurants

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| name      | varchar(255) | YES  |     | NULL    |                |
| founded   | int(11)      | YES  |     | NULL    |                |
| createdAt | datetime     | NO   |     | NULL    |                |
| updatedAt | datetime     | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

Dishes

+--------------+--------------+------+-----+------+----------------+
| id           | int(11)      | NO   | PRI | NULL | auto_increment |
| name         | varchar(255) | YES  |     | NULL |                |
| spicy        | tinyint(1)   | YES  |     | NULL |                |
| createdAt    | datetime     | NO   |     | NULL |                |
| updatedAt    | datetime     | NO   |     | NULL |                |
| RestaurantId | int(11)      | YES  | MUL | NULL |                |
+--------------+--------------+------+-----+------+----------------+

Queries

• Model.find
• Model.findById
• Model.findAll
• Return Promises

Post.findAll()
  .then(function(posts){
    //do something with posts Array
  });

.toJSON

Post.findById(1)
  .then(function(post){
    post.title;                           // "The Hotdog Dillema"
    post.hasOwnProperty('title');         // false
    post.toJSON().hasOwnProperty('title');// true
  });

Property access is proxied. Use .toJSON to get a POJO (plain old javascript object).

Connect the /restaurants/ route

Hints:

• Promise!!

  Model.findAll()
    .then(function(results){
      //use results
    });
  

• Template expects an object with a restaurants key:

  each restaurant in restaurants
    li
      a(href='/restaurants/' + restaurant.id)= restaurant.name
  

Query Conditions

Post.findAll({
  where: {
    featured : true
  }
});

SELECT * FROM Posts WHERE featured = 1;

Query Conditions

$or: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)

$gt: 6,                // > 6
$gte: 6,               // >= 6

$ne: 20,               // != 20

$between: [6, 10],     // BETWEEN 6 AND 10
$notBetween: [11, 15], // NOT BETWEEN 11 AND 15

$in: [1, 2],           // IN [1, 2]
$like: '%hat',         // LIKE '%hat'

Query Conditions

Post.findAll({
  where : {
    $or : [
      { featured : true },
      { likes : { $gte : 10 } }
    ]
  }
});

SELECT * FROM Posts
  WHERE (featured = 1 OR likes >= 10)

Attributes

Post.find({
  where: { id : 32 },
  attributes : [ 'title', 'url']
});

SELECT title, url FROM Posts WHERE id = 32

Relations

Post.find({
  where: { id : 32 },
  include: [ Comment ]
});

SELECT * FROM Posts
  LEFT OUTER JOIN Comments ON Posts.id = Comments.PostId
  WHERE id = 32

Post.findById(32, { include: [ Comment ] });

Connect the /restaurants/:id route

Hints:

• id passed in req.params
• findById
• include : [ Related, Models, Here ]
• Promise!!
• Template expects an object with name, founded, Dishes keys

Saving data

Build & Save

postContents = '<h1>You have probably asked yourself this question a million times...';

var hd_post = Post.build({
  title : 'Are Hotdogs Sandwiches?'
});

hd_post.contents = postContents;
hd_post.save();

Create

Post.create({
  title : 'Are Hotdogs Sandwiches?',
  contents : postContents
})
.then(function(hd_post){ ... });

Updating data

Post
  .findById(123)
  .then(function(hd_post){
    hd_post.update({
      title : 'The Hotdog Dilemma'
    });
  })

Post.update(
  { title : 'The Hotdog Dilemma' },
  { where : { id : 123 } }
);

Connect the POST /restaurants/ route

Hints:

• req.body (already parsed! ☺)
• Promise!!

  Model.saveOperation(data)
    .then(function(newRecord){
      // console.log(newRecord.id)
    });
  

• create or build & save

Up Next: Mongoose