DEV Community

loading...

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

yuesu profile image Yue Su Updated on ・3 min read

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

  • 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

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
    .
    .
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 },
  });
};
Enter fullscreen mode Exit fullscreen mode

For conversations in 'conversation.model.js':

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

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

Enter fullscreen mode Exit fullscreen mode

For UserToConversation in 'userToConversation.module.js':

module.exports = (sequelize) => {
  sequelize.define("userToConversation");
};
Enter fullscreen mode Exit fullscreen mode

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,
    },
  });
};
Enter fullscreen mode Exit fullscreen mode

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 };
Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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

Thanks for reading.

Discussion (1)

pic
Editor guide
Collapse
ash_bergs profile image
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