A simple database modelling for a web-based messenger with Sequelize and postgresDB

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

• 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.

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 for this type of work, but when I learned Sequelize, 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.

Comments

[Ash]

I've only built/worked on a few back end projects, and much of that involved the arduous process of setting up Knex to connect, migrate and seed to the database. I can't tell you how many headaches I've developed just in the boilerplate phase haha.
Thank you for this post! I love how clean the Sequelize syntax looks