Sequelize a Simple Introduction

22 May 2018 in front-end on nodejs - Hits()

Sequelize “An easy-to-use multi SQL dialect ORM for Node.js”

Set up connection

import Sequelize, { Op } from 'sequelize';

const sequelize = new Sequelize('postgres://postgres:postgres@localhost:5432/postgres', {
  dialect: 'postgres',
  define: {
    freezeTableName: true,
  },
})

The above example set up a database connection with a uri. the dialect specify the database type.
define option: define the default options of Model
freezeTableName: If freezeTableName is true, sequelize will not try to alter the model name to get the table name. Otherwise, the model name will be pluralized

Logging sql

Global setting

import Sequelize, { Op } from 'sequelize';

const sequelize = new Sequelize('postgres://postgres:postgres@localhost:5432/postgres', {
  dialect: 'postgres',
  define: {
    freezeTableName: true,
  },
  logging: sql => {
    log.debug(`SQL:${sql}`);
  },
})

Per-query setting

Reference

Model.upsert({}, {
   logging: false,
})

this options is available for most of the command of the Model.

Sync database schema

Reference

import sequelize from '../sequelize';
import User from './User';
import UserLogin from './UserLogin';
import UserClaim from './UserClaim';
import UserProfile from './UserProfile';


function sync(...args) {
  return sequelize.sync(...args);
}

this is a very useful funtion when start the code from scratch, it will create the database table schema based on the Model definition.
Note: to sync the Models, the script should imported all the Models definition before invoke the sync function.
force option is default set to false. If force is true, each Model will run DROP TABLE IF EXISTS, before it tries to create its own table

Jsonb for postgres

Reference

SomeModel.findAll({
  attributes: {
    meta: {
      video: {
        url: {
          [Op.ne]: null
        }
      }
    }
  }
})

SomeModel.findAll({
  attributes: {
    "meta.audio.length": {
      [Op.gt]: 20
    }
  }
})

SomeModel.findAll({
  attributes: {
    "meta": {
      [Op.contains]: {
        site: {
          url: 'http://google.com'
        }
      }
    }
  }
})

Distinct

Reference

  Model.findAll({
    attributes: [
      [Sequelize.fn('DISTINCT', Sequelize.col('someColumnName')), 'aliasName'],
    ],
  })
    .then(dr => {

    })
    .catch(err => {

    });