EzDevInfo.com

sequelize

Sequelize is an easy-to-use multi sql dialect ORM for Node.js & io.js. It currently supports MySQL, MariaDB, SQLite, PostgreSQL and MSSQL. Sequelize | The Node.js / io.js ORM for PostgreSQL, MySQL, SQLite and MSSQL

Sequelize.js delete query?

Is there a way to write a delete/deleteAll query like findAll?

For example I want to do something like this (assuming MyModel is a Sequelize model...):

MyModel.deleteAll({ where: ['some_field != ?', something] })
    .on('success', function() { /* ... */ });

Source: (StackOverflow)

Sequelize use camel case in JS but underscores in table names

Is it possible to have column names be underscored (postgres) but have the JavaScript getters be camelCase per language standards?


Source: (StackOverflow)

Advertisements

Is it possible to do a subquery with Sequelize.js?

I have a query that looks like:

select es.EssayId, (esmax.WordCount - esmin.WordCount)
from (select es.EssayId, min(es.EssayDate) as mined, max(es.EssayDate) as maxed
      from EssayStats es
      group by es.EssayId
     ) es join
     EssayStats esmin
     on es.EssayId = esmin.EssayId and es.mined = esmin.EssayDate join
     EssayStats esmax
     on es.EssayId = esmax.EssayId and es.maxed = esmax.EssayDate;

Is it possible to write this with Sequelize.js ORM? I know I can just use a query directly, but I'm wondering if it's possible to construct.


Source: (StackOverflow)

node.js sequelize: multiple 'where' query conditions

How do i query a table with multiple conditions? Here are the examples both working:

Post.findAll({ where: ['deletedAt IS NULL'] }).success()

and

Post.findAll({ where: {topicId: req.params.id} }).success()

Then, if i need conditions combined, i feel like i need to do something like

Post.findAll({ where: [{topicId: req.params.id}, 'deletedAt IS NULL'] }).success()

, but it doesn't work.
What is the syntax the sequelize waits for?

node debug says:

DEBUG: TypeError: Object # has no method 'replace'

if it matters...


Source: (StackOverflow)

Sequelize REST API generator

I am developing a Nodejs application and my database is Postgres and I am using Sequelize as my ORM because of its excellent support for migrations.

I am on the lookout for a good REST API generator based on the schema I have defined. There are two main hurdles I am facing and they are that the generators don't do a good job of creating association API routes and lack of ACL support. On the associations front, my schema has multiple levels of association i.e. for example..

Student.hasMany(Courses);
Courses.hasMany(Subjects);

So ideally the generated REST API should be something like

/student/:student_id/course/:course_id/subject/:subjectId

I found a few projects that are doing this, but are incomplete.

Is there any module that supports this?


Source: (StackOverflow)

passport.deserializeUser executing a DB (sequelize) command for each HTTP request

I'm using sequelize as an ORM and passport.js (passport-local) for authentication. I noticed that every HTTP request is resulting in a separate database command. I started looking at the deserializeUser() function.

When loading a single page, this is what I get:

Executing: SELECT * FROM Users WHERE Users.id=1 LIMIT 1;

Over and over and over!

GET / 200 12ms - 780

Executing: SELECT * FROM Users WHERE Users.id=1 LIMIT 1;

Executing: SELECT * FROM Users WHERE Users.id=1 LIMIT 1;

Over and over and over!

GET /js/ui.js 304 4ms

Over and over and over!

GET /stylesheets/main.css 304 6ms

Executing: SELECT * FROM Users WHERE Users.id=1 LIMIT 1;

Over and over and over!

GET /images/logo.jpg 304 3ms

Here's how passport.deserializeUser looks:

passport.deserializeUser(function(id, done) {
    User.find(id).success(function(user) {
        console.log('Over and over and over!');
        done(null, user);
    }).error(function(err) {
        done(err, null);
    });
});

The page I'm requesting is:

index: function(req, res) {
    res.render('index', {
        title: "Welcome to EKIPLE!",
        currentUser: req.user
    });
}

Is the deserializeUser supposed to run for every image, html, css file requested? If so, is there a way of reducing the number of requests to the DB?


Source: (StackOverflow)

Sequelize, problem getting associations to return

I'm currently experimenting with Sequelize and have two objects, a Person and Position, When getting a list of persons I want to get their position.

models:

var User = sequelize.define('user', {
    first_name: Sequelize.STRING,
    last_name: Sequelize.STRING 
});

var Position = sequelize.define('position', {
    name: Sequelize.STRING,
    affiliation: Sequelize.STRING
});

Position.hasMany(User, { foreignKey : 'position_id' });
User.belongsTo(Position, { foreignKey : 'position_id'});

My query:

User.findAll({ fetchAssociations: true }, function(results) {
    //I've tried doing some work in here, but haven't found the correct procedure. 
}).on('success', function(results) {
    res.render('users', {
        title: 'user page',
        users: results
    });
});

Watching the log it never queries Person at all. Do I need to use queryChaining? From the documentation I was able to find it appeared it should auto fetch associations.


Source: (StackOverflow)

How to make join querys using sequelize in nodejs

I am using sequelize ORM; everything is great and clean, but I had a problem when I use it with join queries. I have two models: users and posts.

var User = db.seq.define('User',{
    username: { type: db.Sequelize.STRING},
    email: { type: db.Sequelize.STRING},
    password: { type: db.Sequelize.STRING},
    sex : { type: db.Sequelize.INTEGER},
    day_birth: { type: db.Sequelize.INTEGER},
    month_birth: { type: db.Sequelize.INTEGER},
    year_birth: { type: db.Sequelize.INTEGER}

});

User.sync().success(function(){
    console.log("table created")
}).error(function(error){
    console.log(err);
})


var Post = db.seq.define("Post",{
    body: { type: db.Sequelize.TEXT },
    user_id: { type: db.Sequelize.INTEGER},
    likes: { type: db.Sequelize.INTEGER, defaultValue: 0 },

});

Post.sync().success(function(){
    console.log("table created")
}).error(function(error){
    console.log(err);
})

I want a query that respond with a post with the info of user that made it. In the raw query, I get this:

db.seq.query('SELECT * FROM posts, users WHERE posts.user_id = users.id ').success(function(rows){
            res.json(rows);
        });

My question is how can I change the code to use the ORM style instead of the SQL query?


Source: (StackOverflow)

Sequelize: how to import definitions from an existing database

Am I required to handwrite the model definitions for Sequelize even if I'm working off of an existing database.

If it's not required, then how does one go about using Sequelize with an existing database?

I've already defined the database's schema in Doctrine, so I'd rather not have to write another set of model definitions again.


Source: (StackOverflow)

Cleaning out test database before running tests

What is the best way to clean out a database before running a test suite (is there a npm library or recommended method of doing this).

I know about the before() function.

I'm using node/express, mocha and sequelize.


Source: (StackOverflow)

'belongsTo' vs 'hasMany' in Sequelize.js

What's the difference between B.belongsTo(A) and A.hasMany(B)

Artist = sequelize.define('Artist', {});
Album = sequelize.define('Albums', {});

Album.belongsTo(Artist, foreignKey: 'album_belongsl_artist');
Artist.hasMany(Album, foreignKey: 'artist_hasmany_albums');

if it in both cases creates the depended tables in Album?


Source: (StackOverflow)

node-webkit Error: please install sqlite3 package manually

I'm working with node-webkit, Sequelize and sqlite3. Node runs the app with no problems, but when I run it from node-webkit it throws me this Error

"Uncaught Error: The dialect sqlite is not supported. (Error: Please install sqlite3 package manually)", source: /Users/mariowise/projects/node-webkit/requies-pos/node_modules/sequelize/lib/sequelize.js (176)

This are my dependencies

"dependencies": {
    "express": "~4.2.0",
    "static-favicon": "~1.0.0",
    "morgan": "~1.0.0",
    "cookie-parser": "~1.0.1",
    "body-parser": "~1.0.0",
    "debug": "~0.7.4",
    "jade": "~1.3.0",
    "nunjucks": "^1.0.5",
    "sqlite3": "~2.1.19",
    "config": "0.4.33",
    "sequelize": "~2.0.0-rc1",
    "sequelize-sqlite": "~1.7.0"
}

Source: (StackOverflow)

How does sequelize.sync() work, specifically the force option?

What does the force option on sequelize.sync() do?

sequelize.sync({
    force: true
});

Specifically, I am interested in knowing what force: false does? Will it not sync the schema with the database?

Are there any formal docs for sequelize? I could only find examples inside the docs.


Source: (StackOverflow)

Specifying specific fields with Sequelize (NodeJS) instead of *

Alright so I have a project in NodeJS where I'm utilizing Sequelize for a MySQL ORM. The thing works fantastically however I'm trying to figure out if there is a way to specify what fields are being returned on a query basis or if there's even a way just to do a .query() somewhere.

For example in our user database there can be ridiculous amounts of records and columns. In this case I need to return three columns only so it would be faster to get just those columns. However, Sequelize just queries the table for everything "*" to fulfill the full object model as much as possible. This is the functionality I'd like to bypass in this particular area of the application.


Source: (StackOverflow)

Heroku + Node: Cannot find module error

My Node app is running fine locally, but has run into an error when deploying to Heroku. The app uses Sequelize in a /models folder, which contains index.js, Company.js and Users.js. Locally, I am able to import the models using the following code in /models/index.js:

// load models
var models = [
  'Company',
  'User'
];
models.forEach(function(model) {
  module.exports[model] = sequelize.import(__dirname + '/' + model);
});

This works fine, however, when I deploy to Heroku the app crashes with the following error:

Error: Cannot find module '/app/models/Company'
   at Function.Module._resolveFilename (module.js:338:15)
   at Function.Module._load (module.js:280:25)
   at Module.require (module.js:364:17)
   at require (module.js:380:17)
   at module.exports.Sequelize.import (/app/node_modules/sequelize/lib/sequelize.js:219:24)
   at module.exports.sequelize (/app/models/index.js:60:43)
   at Array.forEach (native)
   at Object.<anonymous> (/app/models/index.js:59:8)
   at Module._compile (module.js:456:26)
   at Object.Module._extensions..js (module.js:474:10)
Process exited with status 8

Initially I thought it was due to case sensitivity (local mac vs heroku linux), but I moved the file, made a git commit, and then moved back and committed again to ensure Company.js is capitalized in the git repository. This didn't solve the problem and I'm not sure what the issue could be.


Source: (StackOverflow)