DO Hackathon - Postgres and Sequelize models

As mentioned in a previous post, our app will be using a PostgreSQL database. To consolidate our database tables with data models in our code, we'll be using Sequelize.

I followed this brilliant post which outlines everything you'll need to know about connecting to the database and running migrations. I won't reiterate anything that's already in the post so instead I'll get right into our models and their relationships.

As you might remember, the purpose of our app is to help users keep track of when they complete habits and allow them to track their goal progression. We also want users to have the ability to login so that we can protect their data better. This means we'll likely be looking at models that look like this:

User:
  id: primary key
  name: string
  email: string
  password: string
  createdAt: timestamp

Habit:
  id: primary key
  userId: foreign key - users
  name: string
  description: string
  color: string
  weeklyGoal: number
  createdAt: timestamp

CompletedTask:
  id: primary key
  habitId: foreign key - inhalers
  dateCompleted: datetime
  createdAt: timestamp

CompletedGoal:
  id: primary key
  habitId: foreign key - inhalers
  weekStartDate: datetime
  createdAt: timestamp

I found the Sequelize documentation a little confusing, because there's multiple ways to run migrations. Coming from a Django/Flask background I was used to the process of modifying your models, which would automatically create migrations. Sequelize allows us to do something similar using the sync() method. Here's how the models turned out:

Our User model:

// models/users.js
const { DataTypes } = require('sequelize')

module.exports = {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    },
    createdAt: {
      allowNull: false,
      type: DataTypes.DATE
    },
    updatedAt: {
      allowNull: false,
      type: DataTypes.DATE
    }
  }

Our Habit model:

// models/habits.js
const { DataTypes } = require('sequelize')
module.exports = {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    description: {
      type: DataTypes.STRING,
      allowNull: false
    },
    color: {
      type: DataTypes.STRING,
      allowNull: false
    },
    weeklyGoal: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    createdAt: {
      allowNull: false,
      type: DataTypes.DATE
    },
    updatedAt: {
      allowNull: false,
      type: DataTypes.DATE
    }
}

Our CompletedTask model:

// models/completedTask
const { DataTypes } = require('sequelize')

module.exports = {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    dateCompleted: {
      allowNull: false,
      type: DataTypes.DATE
    },
    createdAt: {
      allowNull: false,
      type: DataTypes.DATE
    },
    updatedAt: {
      allowNull: false,
      type: DataTypes.DATE
    }
}

And lastly:

// models/completedGoals
const { DataTypes } = require('sequelize')

module.exports = {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    weekStartDate: {
      allowNull: false,
      type: DataTypes.DATE
    },
    createdAt: {
      allowNull: false,
      type: DataTypes.DATE
    },
    updatedAt: {
      allowNull: false,
      type: DataTypes.DATE
    }
}

You'll notice that there's no foreign keys in these models. That's because sequelize allows us to define relationships using the .hasMany(), .haseOne() and .belongsTo() model functions.

Now that we've got our definitions, we need to initialize our models and sync up any changes with the database. I created an index.js file in the /models directory to do this:

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const envConfigs =  require('../config/config');
const userDef = require('./user');
const habitDef = require('./habit');
const completedTaskDef = require('./completedTask');
const completedGoalDef = require('./completedGoal');

const env = process.env.NODE_ENV || 'development';
const config = envConfigs[env];
const db = {};

// Establish the connection
const sequelize = new Sequelize(config.url, config);
db.sequelize = sequelize;
db.Sequelize = Sequelize;

// Define the models
const User = sequelize.define('User', userDef);
const Habit = sequelize.define('Habit', habitDef);
const CompletedTask = sequelize.define('CompletedTask', completedTaskDef);
const CompletedGoal = sequelize.define('CompletedGoal', completedGoalDef);

// Define the relationships between models
Habit.hasMany(CompletedTask, {
  onDelete: 'CASCADE'
});
Habit.hasMany(CompletedGoal, {
  onDelete: 'CASCADE'
});
CompletedTask.belongsTo(Habit);
CompletedGoal.belongsTo(Habit);
User.hasMany(Habit, {
  onDelete: 'CASCADE'
});
Habit.belongsTo(User);

// Sync any changes to the database
sequelize.sync({ alter: true })
db.Habit = Habit;
db.CompletedTask = CompletedTask;
db.CompletedGoal = CompletedGoal;
db.User = User;

module.exports = db;

When we import this file into our main index.js, it'll connect to the database, initialize our models and sync any changes in our model definitions to our database tables. We can then import the db object and run a query like so:

const user = await db.User.findOne({
  where: { id: 1 }
 });

We'll be using queries like that in the next post to setup password authentication for our users.