loading...
Cover image for Getting Started with Sequelize and Postgres

Getting Started with Sequelize and Postgres

nedsoft profile image Chinedu Orie Updated on ・6 min read

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

An ORM library is a completely ordinary library written in your language of choice that encapsulates the code needed to manipulate the data, so you don't use SQL anymore; you interact directly with an object in the same language you're using -stackoverflow

Though Sequelize supports several other database clients, this article focuses on using Sequelize with Postgres. We will be building a simple blog where users can create posts, view posts and add comments to a post.

This article is divided into two parts:

  • Part One - Sequelize setup, configuration, migration and seeding.
  • Part Two - Performing CRUD with Sequelize.

Requirements

  • NodeJs installed
  • npm or yarn installed

Part One

If you have not created an express project, quickly do so and open the project in a terminal. At the root folder of the project, run the following commands to install the necessary dependencies:

npm install sequelize sequelize-cli pg pg-hstore

A brief explanation of the libraries installed in the command above:

sequelize is the sequelize library itself.

sequelize-cli is a package that enables us interact with the database through sequelize from the CLI.

pg short for postgres is a Postgres client for Node.js

pg-hstore is a node package for serializing and deserializing JSON data to hstore format.

Next up, let's create sequelize config, to do that, run command below to create a file called .sequelizerc

touch .sequelizerc

Copy the code below into the .seqluelizerc file:

const path = require('path')

module.exports = {
  config: path.resolve('./database/config', 'config.js'),
  'models-path': path.resolve('./database/models'),
  'seeders-path': path.resolve('./database/seeders'),
  'migrations-path': path.resolve('./database/migrations'),
}

Sequelize uses the .sequelizerc file to generate the config and the model using the specified path.

Next up, we generate the config by running the command below:

sequelize init

Now, you should have a new directory called database with a scaffolding as shown below:

Next up, let's edit the database/config/config.js.
Replace the content of the database/config/config.js with the code below:

require('dotenv').config()

module.exports = {
  development: {
    url: process.env.DEV_DATABASE_URL,
    dialect: 'postgres',
  },
  test: {
    url: process.env.TEST_DATABASE_URL,
    dialect: 'postgres',
  },
  production: {
    url: process.env.DATABASE_URL,
    dialect: 'postgres',
  },
}

Since our focus is on using Sequelize with Postgres, we've streamlined the config to be Postgres specific.

Since we are using Postgres, we'll be using connection string to connect to the database. Create two Postgres databases one for development and one for test (if you need test for your project).

How to create a Postgres database connection string

You can jump to the next part if you've already created database.

Method 1

If you have Postgres installed locally follow the steps below to create a database and generate connection string. Open a terminal and run the command below:

createdb dev_db -U <db_user>
createdb test_db -U <db_user>

The connection strings for the databases above will be:

postgres://<db_user>:<db_password>@127.0.0.1:5432/dev_db
postgres://<db_user>:<db_password>@127.0.0.1:5432/test_db

Now, create a .env file and copy the snippet below into it.

DATABASE_URL=
DEV_DATABASE_URL=postgres://<db_user>:<db_password>@127.0.0.1:5432/dev_db
TEST_DATABASE_URL=postgres://<db_user>:<db_password>@127.0.0.1:5432/test_db

Note that if you're using Heroku for production, Heroku will generate a connection string and inject into the environment variable DATABASE_URL once you add the Postgres add-on.

Method 2

If you don't have Postgres installed locally, you can make use of ElephantSQL to create the databases.

Creating Models and Migrations

We need to create the User, Post and Comment models. To do that run the following commands:

sequelize model:generate --name User --attributes name:string,email:string

sequelize model:generate --name Post --attributes title:string,content:text,userId:integer

sequelize model:generate --name Comment --attributes postId:integer,comment:text,userId:integer

Each of the commands above will generate a migration and a model in /database/migrations and database/models directory respectively.

Note, ensure there's no space between --attributes definition.

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.

Next up, we need to make a few changes on the migrations and models.
First we need to add NOT NULL constraints to the FOREIGN_KEY attributes (userId, postId). The first time I worked with Sequelize, I didn't know about this and the model eager loading wasn't working. In the migrations edit the FOREIGN_KEY attributes as shown below:

userId: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },

postId: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },

Edit the models/index.js file as follows:


const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const envConfigs =  require('../config/config');

const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = envConfigs[env];
const db = {};

let sequelize;
if (config.url) {
  sequelize = new Sequelize(config.url, config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}

fs
  .readdirSync(__dirname)
  .filter(file => {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(file => {
    const model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

//models/index.js

Defining the model relationships

We have three models that are interrelated as follows

  • a user has many posts and a post belongs to a user (1:n)
  • a user has many comments and a comment belongs to a user (1:n)
  • a post has many comments and a comment belongs to a post (1:n)

To achieve establish the relationships above programmatically, let's edit the models as follows:

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;
};

// database/models/user.js
module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define('Post', {
    title: DataTypes.STRING,
    content: DataTypes.TEXT,
    userId: DataTypes.INTEGER
  }, {});
  Post.associate = function(models) {
    // associations can be defined here
    Post.hasMany(models.Comment, {
      foreignKey: 'postId',
      as: 'comments',
      onDelete: 'CASCADE',
    });

    Post.belongsTo(models.User, {
      foreignKey: 'userId',
      as: 'author',
      onDelete: 'CASCADE',
    })
  };
  return Post;
};

// database/models/post.js
module.exports = (sequelize, DataTypes) => {
  const Comment = sequelize.define('Comment', {
    postId: DataTypes.INTEGER,
    comment: DataTypes.TEXT,
    userId: DataTypes.INTEGER
  }, {});
  Comment.associate = function(models) {
    // associations can be defined here
    Comment.belongsTo(models.User, {
      foreignKey: 'userId',
      as: 'author'
    });
    Comment.belongsTo(models.Post, {
      foreignKey: 'postId',
      as: 'post'
    });
  };
  return Comment;
};

// database/models/comment.js

It's time to run the migrations which will translate the migrations into tables in the database. Run

sequelize db:migrate

If everything went well, the tables would be generated and we are ready to start shoving data into the database.

Seeding data to the database

Let's populate the database with some dummy data. Run the commands below to generate the seed files for the models.

sequelize seed:generate --name User

sequelize seed:generate --name Post

sequelize seed:generate --name Comment

The commands above will generate three files xxxx-User.js, xxxx-Post.js, and xxxx-Comment.js for User, Post and Comment models respectively.

Edit the seed files as follows:

module.exports = {
  up: (queryInterface, Sequelize) => queryInterface.bulkInsert(
    'Users',
    [
      {
        name: 'Jane Doe',
        email: 'janedoe@example.com',
        createdAt: new Date(),
        updatedAt: new Date(),
      },
      {
        name: 'Jon Doe',
        email: 'jondoe@example.com',
        createdAt: new Date(),
        updatedAt: new Date(),
      },
    ],
    {},
  ),

  down: (queryInterface, Sequelize) => queryInterface.bulkDelete('Users', null, {}),
};

// database/seeds/xxxx-User.js
module.exports = {
  up: (queryInterface, Sequelize) =>
    queryInterface.bulkInsert(
      "Posts",
      [
        {
          userId: 1,
          title: "hispotan de nu",
          content:
            "Nulla mollis molestie lorem. Quisque ut erat. Curabitur gravida nisi at nibh.",
          createdAt: new Date(),
          updatedAt: new Date()
        },
        { 
          userId: 2,
          title: 'some dummy title',
          content:
            "Maecenas tincidunt lacus at velit. Vivamus vel nulla eget eros elementum pellentesque. Quisque porta volutpat erat.",
          createdAt: new Date(),
          updatedAt: new Date()
        }
      ],

      {}
    ),

  down: (queryInterface, Sequelize) =>
    queryInterface.bulkDelete("Posts", null, {})
};
// database/seeds/xxxx-Post.js

module.exports = {
  up: (queryInterface, Sequelize) =>
    queryInterface.bulkInsert(
      "Comments",
      [
        {
          userId: 1,
          postId: 2,
          comment:
            "Nulla mollis molestie lorem. Quisque ut erat. Curabitur gravida nisi at nibh.",
          createdAt: new Date(),
          updatedAt: new Date()
        },
        {
          userId: 2,
          postId: 1,
          comment:
            "Maecenas tincidunt lacus at velit. Vivamus vel nulla eget eros elementum pellentesque. Quisque porta volutpat erat.",
          createdAt: new Date(),
          updatedAt: new Date()
        }
      ],
      {}
    ),

  down: (queryInterface, Sequelize) =>
    queryInterface.bulkDelete("Comments", null, {})
};

// database/seeds/xxxx-Comment.js

Now, run the command below to seed the database:

sequelize db:seed:all

You can clone the complete code for this article here

Yo! that is it for now. In the part two of this article, I will be building on this article to implement CRUD for the blog. Stay tuned! 📌

Suggested resources

This article was originally published on my blog

Discussion

pic
Editor guide
Collapse
nmaxcom profile image
Carles Alcolea

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 :)

Collapse
nedsoft profile image
Chinedu Orie Author

Thanks a lot for sharing Carles!

Collapse
jakub41 profile image
Jakub

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

Collapse
nedsoft profile image
Chinedu Orie Author

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.

Collapse
jakub41 profile image
Jakub

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

Thread Thread
nedsoft profile image
Chinedu Orie Author

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');
  }
}

Thread Thread
jakub41 profile image
Jakub

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

Thread Thread
nedsoft profile image
Chinedu Orie Author

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.

Thread Thread
jakub41 profile image
Jakub

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 😁

Collapse
rnagarajan96 profile image
Nagarajan R

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

Collapse
tcee42 profile image
Thomas

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.

Collapse
nedsoft profile image
Chinedu Orie Author

Glad to hear that it helped

Collapse
viditkothari profile image
Vidit Kothari

I'm looking for the link to part 2 of this article/post ? :/

Collapse
Collapse
viditkothari profile image
Vidit Kothari

Thank you so much. This is a nice guide. Although I'm working with MySQL this did direct me in helpful direction

Thread Thread
nedsoft profile image
Chinedu Orie Author

Good to know!

Collapse
inezabonte profile image
Ineza Bonté Grévy

If you want to add new data. Do you create another seed file or do you use the same seed file you used before?