DEV Community

Getting Started with Sequelize and Postgres

Chinedu Orie on August 09, 2019

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, rela...

Read full post

Carles Alcolea • Dec 22 '19 • Edited

A couple of things for newcomers like me who will run into some issues following the code as it is here.

If seeding the DB you get "ERROR: Migration xxxx-User.js (or wrapper) didn't return a promise", you can add async/await (since all async functions return a promise) like so:

up: async (queryInterface, Sequelize) => await queryInterface.bulkInsert('Users' .............

Another gotcha can be "ERROR: getaddrinfo ENOTFOUND postgresroot" (or similar error).
If your password contains certain symbols (like #, / etc.), it can mess up the parsing. You can either change your password, removing those symbols, or URL encode your password (you can use encodeURIComponent('pass#word')). Don't worry; you don't need to decode it. So this:
postgres://user:pass#word@endpoint.......
becomes:
postgres://user:pass%23word@endpoint.......

And you are good to go.

As an overall style, I'd advise using a more semantic/component-like folder structure
Also, that repo I just linked, is filled with gold in all kinds of NodeJS knowledge!

Thanks to the author for this article, it's been handy in starting with Sequelize :)

Chinedu Orie • Jan 16 '20

Thanks a lot for sharing Carles!

Jakub • Jan 16 '20

Hello,
I've been trying this but I stuck as I need to make a migration file with a foreign key.
Your example is not creating an association on the DB between 2 tables and ignoring the foreign key.

I tried several times but cannot make it work with the foreign key.

It would be nice to see a migration file with a foreign key so I can understand what to do.
Thanks

Chinedu Orie • Jan 16 '20 • Edited

Hi Jakub, I deliberately did not add the foreign key constraints in the migrations. Adding the foreign key constraints in the migrations makes integration with the models very rigid and most times it can be a pain.

From my experience enforcing foreign key constraints in the migrations is good if only at the time of writing the migration you have completely figured out all the models and the relationships that exist between them. This is because foreign key constraints demand that the parent table's migration must precede the child table. In deleting, you cannot delete a parent table if it has a child (this is a good feature btw).

Of course, enforcing the FK constraint ensures data integrity but the opportunity cost most time for me is higher.

So what I do is to define the model relationship in the models, this will do the same job of associating the tables as defining the FK constraint in the migrations in addition to ensuring flexibility in interacting with the tables/models.

Looking at the code used for the article, say

//database/models/user.js

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    name: DataTypes.STRING,
    email: DataTypes.STRING
  }, {});
  User.associate = function(models) {
    // associations can be defined here
    User.hasMany(models.Post, {
      foreignKey: 'userId',
      as: 'posts',
      onDelete: 'CASCADE',
    });

    User.hasMany(models.Comment, {
      foreignKey: 'userId',
      as: 'comments',
      onDelete: 'CASCADE',
    });
  };
  return User;
};

You can see how the relationships were defined.
Like to the repo here

The pattern used here is strictly opinionated.

Let me know if that helped.

Jakub • Jan 16 '20

Hi thanks for the help :)

However, the issue is that in this way on DB I have manually to make the foreign key to have the relationship as I have to make 2 tables related.
Here what I'm trying to do:
github.com/Jakub41/School-API-PSQL
I have an exercise to make projects related to Students.
A student has many projects but a project has one student.
So I have to make it on my own on the DB then this?
Thanks

Chinedu Orie • Jan 16 '20 • Edited

The explanation I gave above answered this question already. I can only repeat the same thing.
You correctly defined the relationship between student and project.
If you remove the constraints in the migrations, you'll still get the expected results in terms of the relationship

For example

student_id: {
                foreignKey: true,
                type: Sequelize.UUID,
                defaultValue: Sequelize.UUIDV4,
                allowNull: false
            },

could be

student_id: {
                type: Sequelize.UUID,
                defaultValue: Sequelize.UUIDV4,
                allowNull: false
            },

also check sequelize docs for how to add Fk constraint, the snippet below was copied from the docs

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Person', {
      name: Sequelize.STRING,
      isBetaMember: {
        type: Sequelize.BOOLEAN,
        defaultValue: false,
        allowNull: false
      },
      userId: {
        type: Sequelize.INTEGER,
        references: {
          model: {
            tableName: 'users',
            schema: 'schema'
          }
          key: 'id'
        },
        allowNull: false
      },
    });
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Person');
  }
}

Jakub • Jan 16 '20

Thanks :)

I have 2 questions:

1) "schema" what is that? I should live schema or I need to define something? Is not clear from docs actually what that means

2) There 2 ways of doing this kind of stuff no async/async, what method is better or how to choose which one to use? It is not really clear to me from the docs.

Thanks for helping :D

Chinedu Orie • Jan 16 '20

Schema is the entire structure of the database, including the tables and the relationships that exist between them.

If you are referring to something somewhere, you can share the link to the reference, that way it'll be easier for me to relate with.

Jakub • Jan 17 '20

I see thanks so in that field I have to put the name of my database but I also need to have a schema defined of my dB in the project?
Sorry for many questions but I'm learning 😁

Another question when you create at or update at and want this is generated by server do I have to add default values NOW? And for Uuid same? In sense UUIDV4?
I'm asking because seems is not generating that when I input records to the dB is giving me Null errors but should be autogenerated fields. Thanks again 😁

Nagarajan R • Jan 15 '20

Awesome tutorial for beginners who they are just like me. Here I am new to nodeJs so i came up with idea's with some doubts (may be old but i found as a beginner i got nothing from stackoverflow). Here i am working based on Multi-Tenant Architecture. so every client organisations need to have their own Databases. so, in my case i am need to work with " 'n' no of databases ". from this tutorial i found the database connection is established during the app initialisation.so, to solve my case i need to establish the database connection dynamically based on the user request to their individual DB and need to perform CRUD operations ( Note : concurrent operations need to perform on their own db without disturb others ). so, i created another db and tried to perform dynamic connection based on user request but nothing helped me so, it's remained unsolved. i heard something about connection pool concept in sequelize that's may be related to my case. so, here i am posting this to expect answers and some suggestions from your side as theoretical and sample code. May be a new tutorial post that will help all people who fell in the same category. i found more peoples tried this but nothing is solid evidence.

Problem: Dynamic DB connection based on the user request not during app initialisation

Please correct me if my understanding is wrong, looking forward for your answer.

Regards,
Nagarajan R

Rob • Jan 5 '21

You should consider editing this article to also instruct the user to install the dotenv package. Otherwise, reading the database config file will fail once the user goes to migrate the database.

They will get "ERROR: Error reading "database\config\config.js". Error: Error: Cannot find module 'dotenv'"

Chinedu Orie • Feb 7 '21

Thanks for pointing this out! will do

Rick • Feb 13 '21 • Edited

For models/index.js I found the line
const model = sequelize['import'](path.join(__dirname, file));
to instead need to be something like:
const model = require(path.join(__dirname, file))(sequelize, Sequelize.DataTypes);

It kept complaining sequelize['import'] was not a function.
Also, not sure when the version might have changed but they suggest "require" over "import". sequelize.org/master/manual/models...
The documentation suggests the sequelize['import'] style should work (even w/ "require") but it wasn't working for me.

I'll echo Carles and others, this article has been an absolute gem! It has saved me days of scouring for insights on how to hook up Sequelize.

Thomas • Feb 10 '20 • Edited

For example, --attributes postId:integer, comment:text, userId:integer will throw an error ERROR: Attribute '' cannot be parsed: Cannot read property 'dataType' of undefined because of the whitespace between attributes.

I was trying to resolve the above error when I stumbled on your post. Thanks a lot, It worked.

Chinedu Orie • Feb 10 '20

Glad to hear that it helped

Rob • Jan 5 '21

You should consider mentioning that sequelize commands need to be prefixed with 'npx', e.g. npx sequelize db:migrate

Chinedu Orie • Feb 7 '21

Thanks for pointing this out! will do

shreydoshi010 • Aug 26 '23

Hi,
I have successfully implemented everything as suggested.
My seeders, when I run them are not recorded in DB. So overtime I cant run seed:all, when I add new seeders. It runs all seeders again
Can you please help me fix this?