Getting Started with Sequelize and Postgres
Tags: node, express, javascript, postgres
Published: 2019-08-09 15:49:36

[Sequelize](https://github.com/sequelize/sequelize#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](https://stackoverflow.com/questions/1279613/what-is-an-orm-how-does-it-work-and-how-should-i-use-one)

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**](https://www.npmjs.com/package/pg) short for postgres is a Postgres client for Node.js

[**pg-hstore**](https://www.npmjs.com/package/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:

```js
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:

![](./db-folder-struct.png)

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

```js
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](https://www.elephantsql.com/) 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:

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

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

Edit the `models/index.js` file as follows:

```javascript

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:

```javascript
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
```

```javascript
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
```

```javascript
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:

```javascript
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
```

```javascript
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

```

```javascript
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](https://github.com/oriechinedu/sequelize-with-postgres-tutorial.git)


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

 - [Sequelize Docs](https://sequelize.org/)
 - [What is an ORM and Why You Should Use it](https://blog.bitsrc.io/what-is-an-orm-and-why-you-should-use-it-b2b6f75f5e2a)


_This article was originally published on my [blog](https://www.oriechinedu.com/posts/getting-started-with-sequelize-and-postgres/)_