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.
Top comments (0)