Cannot associate models in a Schema (postgresql) using Sequelize

Question

I am trying to use Sequelize (1.7) with Postgresql (9.3) and trying to make use of schemas. I have created schemas externally in the database but making use of them in my models. When I am trying to do model associations, Sequelize complains that the relation doesn't exist. When I redefine the models to use the default "public" schema of PostgreSQL, it works fine.

Here are the model definitions and code for the test case

var Promise =   require("bluebird");
var Sequelize = require("sequelize");
var _ = require('lodash-node');

var schemaName ="test_schema";
var sequelize = new Sequelize( c.database,c.username,
    c.password,{dialect :'postgres', port:'5432',schema:schemaName});

var User =sequelize.define('User',{
id:{ type: Sequelize.INTEGER,primaryKey:true, autoIncrement:true},
name:{type:Sequelize.TEXT,allowNull:false},
nick_name:{type:Sequelize.TEXT,allowNull:true},
date_of_joining:{type:Sequelize.DATE,allowNull:false,defaultValue:Sequelize.NOW}

},
{
    tableName:"user",
    underscored:true,
    timestamps:false

}
);

var Task = sequelize.define( 'Task',
{
    id:{ type: Sequelize.INTEGER,primaryKey:true, autoIncrement:true},
    name:{type:Sequelize.TEXT,allowNull:false},
    begin_time:{type:Sequelize.DATE,allowNull:false,defaultValue:Sequelize.NOW},
    end_time:{type:Sequelize.DATE,allowNull:true}
},
{
    tableName:"task",
    underscored:true,
    timestamps:false

}

);

User.schema(schemaName);
Task.schema(schemaName);

User.hasMany( Task, {as:"Tasks"});

sequelize.sync( {force:true}, {logging:console.log})
.then( function( args ){

var users =[
    {name:"Abraham Milbard"},
    {name:"Jimmy Quake"},
    {name:"John Amayo"}
];
var tasks =[
    {name:"Bring 100 apples by today evening"},
    {name:"Do the dishes"},
    {name:"Watch soap and weep"},
    {name:"Bitch about your miserable life"}
];

User.create( users[0])
    .success( function( usr ){
        var chainer = new Sequelize.Utils.QueryChainer;
        var t1 = Task.build( tasks[0 ]),
            t2 = Task.build( tasks[1]);
        chainer.add(t1.save() );
        chainer.add(t2.save() );
        chainer.runSerially({ skipOnError:true})
            .success( function( results ){
                var tsk1 = results[0];
                var tsk2 = results[1];
                usr.setTasks([tsk1,tsk2]).success( function(assocTasks){

                });
            }).error( function( err ){
                console.log("Could not add tasks to user"+err);
            });

    }).error( function( err ){
        console.log("Could not create user ");
    });

});

If you execute this code, the error reported is:

error: relation "task" does not exist

And the queries generated are:

Executing (default): SELECT * FROM "test_schema"."task" WHERE "test_schema"."task"."user_id"=1;
Executing (default): UPDATE "task" SET "user_id"=1 WHERE "id" IN (1,2) RETURNING *

It can clearly be seen that the UPDATE statement is failing because the schema prefix "test_schema" is missing. In other words, had the tool emitted the following query:

 UPDATE "test_schema"."task" SET "user_id"=1 WHERE "id" IN (1,2) RETURNING *

It would have worked just fine. Can anyone here provide any workarounds/insights?

Answer 1

Looks like you're running an older version of sequelize.js

The QueryChainer was removed from a version of sequelize, which is what I ran into when I first started trying to run your example. I also found a github issue that referenced a similar issue and a merged fix for that issue.

Minor Bug Fix - using create instead of build

Here is the updated code:

var Sequelize = require("sequelize");
var _ = require('lodash-node');

var schemaName ="test_schema";

var sequelize = new Sequelize( 'database', 'username', 'password', {dialect :'postgres', port:'5432',schema:schemaName});

var User =sequelize.define('User',{
    id:{ type: Sequelize.INTEGER,primaryKey:true, autoIncrement:true},
    name:{type:Sequelize.TEXT,allowNull:false},
    nick_name:{type:Sequelize.TEXT,allowNull:true},
    date_of_joining: {type:Sequelize.DATE,allowNull:false,defaultValue:Sequelize.NOW}

    },
    {
        tableName:"user",
        underscored:true,
        timestamps:false
    }
);

var Task = sequelize.define( 'Task',
    {
        id:{ type: Sequelize.INTEGER,primaryKey:true, autoIncrement:true},
        name:{type:Sequelize.TEXT,allowNull:false},
        begin_time:{type:Sequelize.DATE,allowNull:false,defaultValue:Sequelize.NOW},
        end_time:{type:Sequelize.DATE,allowNull:true}
    },
    {
        tableName:"task",
        underscored:true,
        timestamps:false
    }

);

User.schema(schemaName);
Task.schema(schemaName);

User.hasMany( Task, {as:"Tasks"});

sequelize.sync( {force:true}, {logging:console.log})
    .then( function( args ){

        var users =[
            {name:"Abraham Milbard"},
            {name:"Jimmy Quake"},
            {name:"John Amayo"}
        ];
        var tasks =[
            {name:"Bring 100 apples by today evening"},
            {name:"Do the dishes"},
            {name:"Watch soap and weep"},
            {name:"Bitch about your miserable life"}
        ];

        User.create( users[0])
            .then( function( usr ){
            var tsk1;
            Task.create( tasks[0] )
               .then( function( task ) {
                   tsk1 = task;
                   return Task.create( tasks[1] );
               } )
               .then( function( tsk2 ) {
                   return usr.setTasks([tsk1,tsk2] );
               } )
               .then( function ( assocTasks ) {
               } )
               .catch( function( err ) {
                   console.log( err );
               });
         });
});

and here is the output:

Executing (default): DROP TABLE IF EXISTS "test_schema"."task" CASCADE;
Executing (default): DROP TABLE IF EXISTS "test_schema"."user" CASCADE;
Executing (default): DROP TABLE IF EXISTS "test_schema"."user" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "test_schema"."user" ("id"  SERIAL , "name" TEXT NOT NULL, "nick_name" TEXT, "date_of_joining" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' and t.relname = 'user' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "test_schema"."task" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "test_schema"."task" ("id"  SERIAL , "name" TEXT NOT NULL, "begin_time" TIMESTAMP WITH TIME ZONE NOT NULL, "end_time" TIMESTAMP WITH TIME ZONE, "user_id" INTEGER REFERENCES "test_schema"."user" ("id") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' and t.relname = 'task' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "test_schema"."user" ("id","name","date_of_joining") VALUES (DEFAULT,'Abraham Milbard','2015-08-15 14:39:46.000 +00:00') RETURNING *;
Executing (default): SELECT "id", "name", "begin_time", "end_time", "user_id" FROM "test_schema"."task" AS "Task" WHERE "Task"."user_id" = 1;
Executing (default): UPDATE "test_schema"."task" SET "user_id"=1 WHERE "id" IN (1, 2)