A simple database modelling for a web-based messenger with Sequelize and postgresDB
Tags: database, node
Published: 2020-12-28 19:43:40

## Goal
This is a simplified database modelling example, which aims to provide a basic structure of the database and demonstrate data persistence.

It will be used for an application that is designed to be a messenger clone, featuring real-time and offline messaging. All the messages and conversations are stored in the database so that a registered user could retrieve the information when logged in.

## Tables and associations
![database](https://www.dropbox.com/s/ds4w8rbkrxamqof/database.png?raw=1)

* A User table for storing username, email and hashed password.
* Conversation table and UserToConversation tables for storing user's conversation and many-to-many relationships between user and conversations. 
* Message table for storing message including sender's id, conversation id and content.

A user can create many conversations, and a conversation can have many users, the UserToConversation table is used to storing this mapping info. 

For example, when user_1 wants to have a conversation with user_2 and user_3 in a group chat, a conversation record will be created first, and three UserToConversation records would be created subsequently. 

![relationship](https://www.dropbox.com/s/8y6ms5uwmq1fk82/dbrelation.png?raw=1)

>Here, the "users" column in the Conversation table is a string for recording all the users' IDs in a conversation. It could be used for eliminating duplicated conversations.

##Connecting Postgres with Sequelize
I used to use [Knex](http://knexjs.org/) for this type of work, but when I learned [Sequelize](https://sequelize.org/master/), I forget about Knex right away, as well as the trauma when setting up the Knex environment.

File structure

```
├── models
│   ├── index.js
│   ├── addAssociations.js
│   ├── syncModels.js
│   ├── user.model.js
│   └── conversation.model.js
    .
    .
```

First, for initializing the Sequlize instance, we could setup a 'models' folder and have an index.js file as such:

```
const { Sequelize } = require("sequelize");
const { addAssociations } = require("./addAssociations");
const { syncModels } = require("./syncModels");

const sequelize = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USERNAME,
  process.env.DB_PASSWORD,

  {
    host: process.env.DB_HOST,
    dialect: "postgres",
    operatorsAliases: false,

    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000,
    },
  }
);

const modelDefiners = [
  require("./user.model"),
  require("./conversation.model"),
  require("./message.model"),
  require("./userToConversation.model"),
];

for (const modelDefiner of modelDefiners) {
  modelDefiner(sequelize);
}

addAssociations(sequelize);
syncModels(sequelize);

//test the database connection
sequelize
  .authenticate()
  .then(() => console.log("Postgres Connected!"))
  .catch((err) => console.error(err));

module.exports = sequelize;
```

Once the connection is up, we could add tables and associations. Sequelize will take care of setting up foreign keys. 

For users in 'users.modules.js':
```
const { DataTypes } = require("sequelize");

module.exports = (sequelize) => {
  sequelize.define("user", {
    username: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      isEmail: true,
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    photoURL: { type: DataTypes.STRING, allowNull: true },
  });
};
```
For conversations in 'conversation.model.js':
```
const { DataTypes } = require("sequelize");

module.exports = (sequelize) => {
  sequelize.define("conversation", {
    users: {
      type: DataTypes.STRING,
      unique: true,
    },
  });
};

```
For UserToConversation in 'userToConversation.module.js':
```
module.exports = (sequelize) => {
  sequelize.define("userToConversation");
};
```
For Messages in 'message.model.js':

```
const { DataTypes } = require("sequelize");

module.exports = (sequelize) => {
  sequelize.define("message", {
    content: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    currentChatReceiverId: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
  });
};
```
For associations in 'addAssociations.js':
```
function addAssociations(sequelize) {
  const { user, conversation, message, userToConversation } = sequelize.models;

  user.hasMany(userToConversation);
  userToConversation.belongsTo(user);

  conversation.hasMany(userToConversation);
  userToConversation.belongsTo(conversation);

  conversation.hasMany(message);
  message.belongsTo(conversation);

  user.hasMany(message);
  message.belongsTo(user);
}

module.exports = { addAssociations };
```

At last, we will need to sync tables with the posgres server in 'syncModels.js':
```
const syncModels = async (sequelize) => {
  const { user, conversation, message, userToConversation } = sequelize.models;

  try {
    await user.sync();
    await conversation.sync();
    await userToConversation.sync();
    await message.sync();
    console.log("synced");
  } catch (error) {
    console.error(error);
  }
};

module.exports = { syncModels };

```

The modelling part is done, and the models will be used in the routes for querying data and so on.

Thanks for reading.