Notes on ‘Introduction to Sequelize ORM on Node.js’ by Patrick Shroeder on plurasight.com (excellent course!)

Sequelize can be used with sql db’s. Create an entry point server.js to node.js,

touch server.js

Then create a package.json file,

$ npm init -y

Wrote to /Users/shanegibney/sequelizeORMCoursePlurasight/package.json:

{
“name”: “sequelizeORMCoursePlurasight”,
“version”: “1.0.0”,
“description”: “”,
“main”: “server.js”,
“scripts”: {
“test”: “echo \”Error: no test specified\” && exit 1″,
“start”: “node server.js”
},
“keywords”: [],
“author”: “”,
“license”: “ISC”
}

Install these npm packages,

$ npm i express sequelize sqlite3 -S

The -S flag saves them to package.JSON

Add to server.js

const express = require('express');
const Sequelize = require('sequelize');

app.listen(port, () => {
console.log('Running server on port ' + port);
})

Run with,

$ npm start

Add a Sequleize instance which is a constructor function,

const connection = new Sequelize('db', 'user', 'pass', {
host: 'localhost',
dialect: 'sqlite',
storage: 'db.sqlite',
operatorsAliases: false
})

the db, user and pass can be anything you want because the DB is local.

Next add,

connection
.authenticate()
.then(() => {
console.log('Connection to database established successfully.');
})
.catch(err => {
console.log('Unable to connect to the database: ', err);
})

and server.js should now be,

const express = require('express');
const Sequelize = require('sequelize');

app.listen(port, () => {
console.log('Running server on port ' + port);
})

Stop server with ctrl+c and restart it,

$ npm started

Hopefully you get the message,

Connection to database established successfully.

A new file should be added to the project directory called db.sqlite

Sequelize maps JavaScript object to SQL tables using Models.

Here is an example of a Model called user,

User = {
name: Sequelize.STRING,
bio: Sequelize.STRING
};

This gets mapped to a table in the database called Users, note the Model is called ‘User’ but the table is called ‘Users’. This table will be created by Sequelize.

We need to create a variable to hold our Model reference,

const User =
connection.define('User', {
name: Sequelize.STRING,
bio: Sequelize.TEXT
})

The second ‘User’ is our Model name. This is placed in server.js

Next we need to sync the Model with the database. We can call sync() on the define,

connection.define('User', {
name: Sequelize.STRING,
bio: Sequelize.TEXT
}).sync();

or more commonly on the connection,

connection.sync();

When invoking sync() we can pass it a optional objects such as logging, to see the raw sql commands being used,

connection.sync({
logging: console.log
});

In server.js chain the sync() method to the connection and pass it the logging option.

The authenticate method can be removed because calling sync() performs the same operation.

Restart server as before. This will also output the raw sql command. Extra columns are also created such as id, createdAt and updatedAt.

After sync() we can use the create method on the User Model to add data,

connection
.sync({
logging: console.log
})
.then(() => {
User.create({
name: 'Luke',
bio: 'Luke bio entry'
})
})
// .authenticate()
.then(() => {
console.log('Connection to database established successfully.');
})
.catch(err => {
console.log('Unable to connect to the database: ', err);
})

server.js is now,

const express = require('express');
const Sequelize = require('sequelize');

app.listen(port, () => {
console.log('Running server on port ' + port);
})

To view the data,

$ sqlite3 db.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> .tables
Users
sqlite> select * from Users;
1|Luke|Luke bio entry|2019-09-08 14:37:08.758 +00:00|2019-09-08 14:37:08.758 +00:00
sqlite> .headers ON
sqlite> select * from Users;
id|name|bio|createdAt|updatedAt
1|Luke|Luke bio entry|2019-09-08 14:37:08.758 +00:00|2019-09-08 14:37:08.758 +00:00
sqlite>

There is a Db browser for sqlite.

Primary keys uniquely indentify a row and also can be used for association with other tables.

UUID is a data type that stands for Universal Unique Identifier. Testing UUID inside out User Model. We will use ‘force’ which will drop the Users table first before creating it.

const User = connection.define('User', {
uuid: {
type: Sequelize.UUID,
primaryKey: true,
defaultValue: Sequelize.UUIDV4
},
name: Sequelize.STRING,
bio: Sequelize.TEXT
})

Without defaultValue that column will be null. This creates a unique identifier for the row instead of just an number as the primary key. We you vierw th data in the table now you will see a unqiue identifier for the primary key. It will look like this,

5f63759a-e176-4cbe-b2b3-082fed5e98fe

Using freezeTableName set to tru means that the name we give our Model will match the name we give our table. This prevents the default pluralization of the table name.

const connection = new Sequelize('db', 'user', 'pass', {
host: 'localhost',
dialect: 'sqlite',
storage: 'db.sqlite',
operatorsAliases: false,
define: {
freezeTableName: true
}
})

Turn off timestamps,

const User = connection.define('User', {
uuid: {
type: Sequelize.UUID,
primaryKey: true,
defaultValue: Sequelize.UUIDV4
},
name: Sequelize.STRING,
bio: Sequelize.TEXT
}, {
timestamps: false
})

Attribute qualifiers allow us to customise attributes before they become columns, these include primaryKey, defaultValue and allowNull. These objects are placed in the value field of an attributes name.

Adding a validator to the name attribute, you must make the value of the attribute an object

name: {…}

name: {
type: Sequelize.STRING,
validate: {
len: [3,]
}
},

so that name must have a minimum of 3 characters. len[3,10] additionally gives a maximum length on 10 but that is optional.

The validator ‘contains’ ensures the content contains a certain string and an error message also,

bio: {
type: Sequelize.TEXT,
validate: {
contains: {
args: ['foo'],
msg: 'Error: Field must contain foo'
}
}
}

We will create an Express route,

app.get('/', (req, res) => {
User.create({
name: 'Joe',
bio: 'Luke bio foo entry 2'
})
.then(user => {
res.json(user);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

We can modify data before it gets to the database using hooks or lifecycle events. There are four of these,

beforeValidate: () => {
console.log('beforeValidate');
}

is used before validation.

afterValidate: () => {
console.log('afterValidate');
}

is used after validation.

beforeCreate: () => {
console.log('beforeCreate');
}

is used after validation and before the column data is created.

afterCreate: () => {
console.log('afterCreate');
}

is used after the column data is created.

To use a hook, add the property ‘hooks’ as a thrid parameter to the Model after all the attributes have been declared.

The first three types of hooks accept a parameter that represents the Model object. The afterCreate hook also accepts a parameter that represents the final response object. A common use case for hooks is when we need to hash a users password before it is sent to the database.

Next we will remove the validation for ‘name’ and ‘bio’ and change the ‘name’ attribute to ‘first’ and add another attribute called ‘last’ and also an attribute ‘full_name’. All of these are of type STRING.

const User = connection.define('User', {
uuid: {
type: Sequelize.UUID,
primaryKey: true,
defaultValue: Sequelize.UUIDV4,
allowNull: false
},
first: Sequelize.STRING,
last: Sequelize.STRING,
full_name: Sequelize.STRING,
bio: Sequelize.TEXT
}, {
hooks: {
beforeValidate: () => {
console.log('before validate');
},
afterValidate: () => {
console.log('after validate');
},
beforeCreate: () => {
console.log('before create');
},
afterCreate: () => {
console.log('after create');
}
}
})

Add a new user,

connection
.sync({
logging: console.log,
force: true
})
.then(() => {
User.create({
first: 'Luke',
last: 'Johnson',
bio: 'New bio entry here'
})
})
// .authenticate()
.then(() => {
console.log('Connection to database established successfully.');
})
.catch(err => {
console.log('Unable to connect to the database: ', err);
})

In the terminal you can see the responses to each of the lifecycle events or hooks.

Running server on port 8001
Executing (default): DROP TABLE IF EXISTS `User`;
Executing (default): DROP TABLE IF EXISTS `User`;
Executing (default): CREATE TABLE IF NOT EXISTS `User` (`uuid` UUID NOT NULL PRIMARY KEY, `first` VARCHAR(255), `last` VARCHAR(255), `full_name` VARCHAR(255), `bio` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`User`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_User_1`)
before validate
Connection to database established successfully.
after validate
before create
Executing (default): INSERT INTO `User` (`uuid`,`first`,`last`,`bio`,`createdAt`,`updatedAt`) VALUES ($1,$2,$3,$4,$5,$6);
after create

We want to combine the first and last values into full_name. We will use the beforeCreate to accept a parameter that represents the user Model. Then inside the body of that function we will combine the user’s first and last name.

Here we use back ticks and string interpolation.

beforeCreate: (user) => {
user.full_name = `${user.first} ${user.last}`
console.log('before create');
},

In the sqlite database we can see that the full_name column contains the value ‘LukeJohnson’

server.js

const express = require('express');
const Sequelize = require('sequelize');

app.listen(port, () => {
console.log('Running server on port ' + port);
})

Generate data with generatedata.com and create a file users.json.
Require this in to server.js with,

const _USERS = require('./users.json');

Change the User Model to match the users.json file,
we add this file to the Model using the method bulkCreate which accepts an array with an optional second argument. bulkCreate will create and accept multiple instance in bulk.

The User Model is now,

const User = connection.define('User', {
name: Sequleize.STRING,
email: {
type: Sequleize.STRING,
validate: {
isEmail: true
}
},
password: {
type: Sequelize.STRING,
validate: {
isAlphanumeric: true
}
}
})

Onto connection.sync() we can chain a .then() method calling bulkCreate on our Users Model and passing in _USERS which we created previously and add a success and error message to this.

Remove the force option as we do not want to remove the table after it is initially created. But initially we do want force to overwrite the previous table. Also commented out the logging message.

server.js

const express = require('express');
const Sequelize = require('sequelize');
const _USERS = require('./users.json');

app.listen(port, () => {
console.log('Running server on port ' + port);
})

Next we will use POST to send data to the DB. This won’t actually work at the moment but it gives the structure and API endpoint,

app.post('/post', (req, res) => {
const newUser = req.body.user;
User.create(newUser)
.then(user => {
res.json(user);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

Remove the comment creating bulk users. We will create a new route called ‘findall’.

app.get('/findall', (req, res) => {
User.findAll()
.then(user => {
res.json(user);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

Navigate in the browser to,

http://localhost:8001/findall

But what if want only a subset of users? Use the where operator.

Testing the where functionality,

app.get('/findall', (req, res) => {
User.findAll({
where: {
name: 'David'
}
})
.then(user => {
res.json(user);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

To use the Op method we need to import it at the top of server.js

const Op = Sequelize.Op;

This will allow us to use filters on the queries.

To match all records that start with ‘Be’,

where: {
name: {
[Op.like]: 'Be%'
}
}

You can also find an entry by id,

app.get('/findOne', (req, res) => {
// User.findById('55')
//findById() has been replaced with findByPk() as of Sequelize v5
User.findByPk('55')
.then(user => {
res.json(user);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

To update an item we first call the method update on the Model the parameters we are updating. In the second parameter we can use ‘where’.

After an update Express does not return the newly updated row only information on the number of rows updated. That is why we change the response from ‘users’ to ‘rows’.

app.put('/update', (req, res) => {
console.log("updating");
User.update({
name: 'Michael Keaton',
password: 'password'
}, {
where: {
id: 55
}
})
.then(rows => {
res.json(rows);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

We need to use POSTMAN to test the /update route. The browser will not work. Remember to make a PUT request to http://localhost:8001/update in POSTMAN and check if the row with id 55 has updated using http://localhost:8001/findOne or look in the sqlite db itself from the command line.

To complete the CRUD operations, we will look at deleting data.

app.delete('/remove', (req, res) => {
User.destroy({
where: {
id: '50'
}
})
.then(user => {
res.send('User successfully deleted');
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

Again you will need to use POSTMAN and http://localhost:8001/remove with a DELETE request.

Server.js

const express = require('express');
const Sequelize = require('sequelize');
const Op = Sequelize.Op;
// const _USERS = require('./users.json');

app.listen(port, () => {
console.log('Running server on port ' + port);
})

Associations form relationships between tables. These relationships are used to create joins. Given two tables Users and Posts, each had a primary id and other attributes. In the Posts table add a column that holds the primary key of the user called UserId. Querying this the response would look like this,

[{
"id": 1,
"title": 'Post name',
"content": 'Post content',
"UserId": 2
"User": {
"name": 'Joe',
....other attributes...
}
}]

Above we have id, title, content from the Posts table and then UserId of 2 gives us the object User in the Users table where the UserId is 2 and so we get ‘Joe’.

Creating associations:

1. Define association between Models

Post.belongsTo(User);

This adds the UserId column to the Posts table and is added after we define the Models.

2. Add include property with value as associated model.

Whenever we execute a query on the Posts table we need to add an include property inside the query with the associated model as the value.

Post.findByPk('1', {
include: [User]
})

So if we wanted to fetch a single post by its id we would also include the User Model in an array like this,

include: [User]

This would return all of the user’s attributes. If however we wanted to only return certain attributes we could wrap the value of the attributes in an object like this,

include: [{
model: User
attributes: ['name'] //specify attributes here
}]

specifying the model and then the attributes.

Next we will create a new Model called Posts and create an association to User. In server.js we will remove all routes except the findAll route which we will edit.

Just before the routes we will create the new Model Post,

const Post = connection.define('Post', {
id: {
primaryKey: true,
type: Sequelize.UUID,
defaultValue: Sequelize.UUIDV4
},
title: Sequelize.STRING,
content: Sequelize.TEXT
})

Then above the connection and sync() method we will add the associations,

Post.belongsTo(User);

Just after the sync() method we will add data to the Post table,

connection
.sync()
.then(() => {
Post.create({
UserId: 1,
title: 'First post',
content: 'post content 1'
})
})....]

We need a new route to view this data,

app.get('/allposts', (req, res) => {
Post.findAll({
include: [User]
})
.then(posts => {
res.json(posts);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

Restart server and check this route.

The UserID column is referred to as a Foreign Key. It is automatically generated for us and is Pascal Cased. We may want to choose the name for our foreign key instead of the default. To do this we add an object as the second argument of our belongsTo method, with a key of foreignKey and a value of whatever we want to name of the foreign key to be,

Post.belongsTo(User, { foreignKey: 'userID'});

Replace the Post.belongsTo(..) in server.js with the above. Then we need to uncomment force set to true so that the tables get dropped. We will uncomment the bulkCreate so as to add the user.json data to the database.

An alias will renamed a Model, when it is used as an association this can be useful. In the current Post response we get back ‘User’ with their attributes.

[
{
"id": "d84c65ad-9b28-4fd6-bf94-ad64675d723b",
"title": "First post",
"content": "post content 1",
"createdAt": "2019-09-09T16:52:06.521Z",
"updatedAt": "2019-09-09T16:52:06.521Z",
"UserId": 1,
"User": {
"id": 1,
"name": "Declan",
"email": "neque.sed.sem@consectetuereuismod.com",
"password": "HOM40YJD6VF",
"createdAt": "2019-09-08T18:30:12.023Z",
"updatedAt": "2019-09-08T18:30:12.023Z"
}
}
]

But we can change User to UserRef. To this we need to add a key value pair to the object which is the second parameter in belongsTo().

Previously we had,

Post.belongsTo(User, {
foreignKey: 'userID'
});

This key value pair will have

as : 'UserRef'

So now we have,

Post.belongsTo(User, {
as : 'UserRef',
foreignKey: 'userID'
});

Additionally we need to make sure that anytime we add the user model to our query that we reference the alias using the ‘as’ property,

User.findByPk('1', {
include: [{
model: User,
as: 'UserRef'
}]
})

The route now becomes,

app.get('/allposts', (req, res) => {
Post.findAll({
include: [{
model: User,
as: 'UserRef'
}]
})
.then(posts => {
res.json(posts);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

Also include the bulkCreate method and make sure force set to true. Also add Post.create with the foreign key column’s new name ‘userID’ instead of the default ‘UserId’,

.then(() => {
User.bulkCreate(_USERS)
.then(users => {
console.log('Success adding users');
})
.catch(err => {
console.log(err);
})
})
.then(() => {
Post.create({
userID: 1,
tiitle: 'First post',
content: 'post content 1'
})
})

Then restart server.

This should return,

[{
"id": "16184133-1df3-464b-bb21-e35f5654e19a",
"title": null,
"content": "post content 1",
"createdAt": "2019-09-10T15:48:06.485Z",
"updatedAt": "2019-09-10T15:48:06.485Z",
"userID": 1,
"UserRef": {
"id": 1,
"name": "Declan",
"email": "neque.sed.sem@consectetuereuismod.com",
"password": "HOM40YJD6VF",
"createdAt": "2019-09-10T15:48:06.479Z",
"updatedAt": "2019-09-10T15:48:06.479Z"
}
}]

We an see the foreignKey has been renamed to ‘userId’ and the associated table to ‘UserRef’.

Associations: One-to-One, One-to-Many and Many-to-Many

For One-to-One associations we use the belongsTo() or hasOne() methods. hasOne() is the same as belongsTo() except that it places the foreign key in the opposite table.

This example would place a foreignKey of PostId in the User table.

User.hasOne(Post);

One-to-One associations only return a single item.

One-to-Many associations use hasMany() and the syntax is,

User.hasMany(Post);

A user may have many posts, and each post has a foreignKey of UserId in the Post table. A query returns an array of posts for the individual posts the associated user has made.

In a Many-to-Many assoication we use belongsToMany(). This type of association is used on BOTH models,

User.belongsToMany(Post);
Post.belongsToMany(User);

This will create a join table that contains just two columns. Each column will contain the primary key reference for each of the Models. The response that we get back when either of these tables is included will always be an array items.

Next we will build a One-toMany association.

1. Define association between models using hasmany(),

Post.hasMany(Comment, {as : 'All_Comments'});

In this example a post can have many comments. When the association is created a foreignKey of PostId will be added to the Comments table. We can optionally add an alias or a foreignKey as a second parameter. When making arequest you must include the associated Model along with an alias if there is one.

Post.findByPk('1', {
include: Comment, as: 'All_Comments'
})

The response we get back when making a query on the Post table will comntain an array of all the comments that have been added.

{
"id": 1,
"title": 'Post name',
"content": 'Post content'
"All_Comments": [{
"the_comment": 'first'
}, {
"the_comment": 'hi'
}, {
"the_comment": 'sup yo'
}, ]
}

We will build an example. First remove the freezeTableName property so that we will get the pluralised table name.

We will remove the id field of UUID from Post. This will make it easier to reference a Post id when we create our comments.

Next we create a Comments Model which will have one attribute the_comment and it is of type STRING,

const Comment = connection.define('Comment', {
the_comment: Sequelize.STRING
})

Now create a new association for Post to Comments. Since a post can have many comments we will use hasMany()

Note: ERD is an Entity-Relationship Diagram
https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning

Now the route,

app.get('/singlepost', (req, res) => {
Post.findByPk('1', {
include: [{
model: Comment,
as: 'All_Comments',
attributes: ['the_comment'] // the attributes we want to get back
}]
})
.then(posts => {
res.json(posts);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

server.js is now,

const express = require('express');
const Sequelize = require('sequelize');
const Op = Sequelize.Op;
const _USERS = require('./users.json');

app.listen(port, () => {
console.log('Running server on port ' + port);
})

and running this should return,

{
"id": 1,
"title": "First post",
"content": "post content 1",
"createdAt": "2019-09-10T19:41:54.978Z",
"updatedAt": "2019-09-10T19:41:54.978Z",
"userId": 1,
"All_Comments": [
{
"the_comment": "first comment"
},
{
"the_comment": "second comment here"
}
]
}

so this returning a single post along with its two comments. We get back the associated userID but not any of the model’s data. To get the associated Model’s data we add to the route like this,

app.get('/singlepost', (req, res) => {
Post.findByPk('1', {
include: [{
model: Comment,
as: 'All_Comments',
attributes: ['the_comment'] // the attributes we want to get back
}, {
model: User,
as: UserRef
}]
})
.then(posts => {
res.json(posts);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

Comment out force so that the tables do not get dropped and the .then() methods that add initial data, including bulkCreate().

This should return,

{
"id": 1,
"title": "First post",
"content": "post content 1",
"createdAt": "2019-09-10T19:50:29.542Z",
"updatedAt": "2019-09-10T19:50:29.542Z",
"userId": 1,
"All_Comments": [
{
"the_comment": "first comment"
},
{
"the_comment": "second comment here"
}
],
"UserRef": {
"id": 1,
"name": "Declan",
"email": "neque.sed.sem@consectetuereuismod.com",
"password": "HOM40YJD6VF",
"createdAt": "2019-09-10T19:50:29.538Z",
"updatedAt": "2019-09-10T19:50:29.538Z"
}
}

Many-to-Many association

1. Define a relationship between two Models

Use the belongsToMany() method. We need to define this association on both Models.

// foreignKey = ProjectId and UserID in UserProject table
User.belongsToMany(Project, {
as: 'Tasks',
through: 'UserProject'
});
User.belongsToMany(User, {
as: 'Workers',
through: 'UserProject'
});

So UserProject is a join table between Project and User. Notice that the table name UserProject does not get automatically pluralised.

2. Add include property with value as associated model.

To retrieving a user or project we just call a query method such as findByPk() and include the Project Model along with the alias. And we can ask for only certain attributes.

3. Add in optional attributes for either model.

User.findByPk(‘1’, {
include: Project, as: ‘Tasks’, attributes: [‘name’]
})

The response should be of the form,

{
"id": 1,
"name": 'Hakeem',
"email",
'1234@email.com'
"Tasks": [{
"name": 'project 1'
}, {
"name": 'project 4'
}]
}

The user with an id of 1 gets queried and includes an array of two projects that have been associated with this user and it includes the alias that we have given of tasks.

When creating a Many-to-Many or a One-to-Many association, Sequelize also provides getters and setters methods. These members allow us to perform CRUD operations on members of an association. There are seven get and set methods but only four are commonly used. These are ‘set’, ‘get’, ‘add’ and ‘remove’.

‘set’ is used when initially creating an association, and it accepts an array of id’s, setWorkers([]). For these methods the syntax is camel cased. So in ‘setWorkers’, the ‘set’ part of this is the name of the ‘setter’ or ‘getter’ followed by the name of the alias which is in this case, ‘Workers’. And that is how we get ‘setWorkers([])’.

‘add’ is used to add a single member to an association, addWorkers().

‘get’ can be used to find all associated members, getWorkers()

‘remove’ can be used to remove a single associated member removeWorkers().

Example syntax to set a new worker,

Project.create({
name: 'project name'
}).then((project) => {
project.setWorkers([1, 2]);
})

After creating a new project we then want to add a few new workers to this project. We can do this by chaining on the setWorkers([1, 2]) method to the response, passing in an array of id’s that we want to add.

Remember we are using ‘Workers’ as an alias for our User. Inside the UserProject table we will have two new columns added, ‘UserID and ProjectId’, associating two users with a project.

After a project is created we may want to add a new worker to the project. To do this we can use the addWorkers() method,

app.put('/addWorker', (req, res)
=> {Project.findByPk(4)
.then((project)=> {
project.addWorkers(5)
})...

This uses a put request and inside the function we first find a project by its id. Then we take that found project and chain on the addWorkers(5) method passing in the id of the worker we want to add.

Next we will create an example of a Many-to-Many association.

Create a new model called Project, with a single attribute of ‘title’ and type STRING.

const Project = connection.define('Project', {
title: Sequelize.STRING
})

Then create the belongsToMany() associations for a user belonging to many projects and a project belonging to many users. We add aliases ‘Tasks’ and ‘Workers’ to both associations, ‘Tasks’ for User and ‘Workers’ for Project. Remember that the UserProjects table gets created with ID’s of ProjectId and UserId columns,

// Creates a UserProjects table with ID's for the ProjectId and UserID
User.belongsToMany(Project, {
as: 'Tasks',
through: 'UserProjects'
});
Project.belongsToMany(User, {
as: 'Workers',
through: 'UserProjects'
});

Create two new projects chained to .sync()

connection
.sync({
// force: true
})
.then(() => {
Project.create({
title: 'project 1'
}).then((project) => {
project.setWorkers([4, 5]);
})
})
.then(() => {
Project.create({
title: 'project 2'
})
})...

The first project will have a title of ‘project 1’ and then we setWorkers 4 and 5 to be included with this project on creation. We also add in another project with the title of ‘project 2’ without setting any workers.

If we run the app now we should expect to see two new tables along with the new database entries.

Next we will look at adding a single worker to the project2. For this we need a new route,

app.put('/addWorker', (req, res) => {
Project.findByPk(2) //project has id of 2
.then((project) => {
project.addWorkers(5) //user has id of 5
})
.then(posts => {
res.send('User added');
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

and we need to use POSTMAN with the PUT method.

http://localhost:8001/addWorker

This should return the success method of ‘User added’. In the UserProjects table you should see a new row, which has a new user of id 5, has been added to project 2. That is UserId of 5 and ProjectID of 2.

Next how to query users and get back all their projects while filtering for attributes. For this we will create a new route,

app.get('/getUserProjects', (req, res) => {
User.findAll({
attributes: ['name'],
include: [{
model: Project,
as: 'Tasks',
attributes: ['title']
}]
})
.then(output => {
res.json(output);
})
.catch(error => {
console.log(error);
res.status(404).send(error);
})
})

This will find all users but only retrieve the attribute of ‘name’, then the model of Project, along with the alias that we have assigned of ‘Tasks’, while only getting back the attribute of title.

This gives all users, just their names as well as any associated project titles,

[
{
"name": "Declan",
"Tasks": []
},
{
"name": "Wallace",
"Tasks": []
},
{
"name": "Maisie",
"Tasks": []
},
{
"name": "Ayanna",
"Tasks": [
{
"title": "project 1",
"UserProjects": {
"createdAt": "2019-09-11T12:06:12.585Z",
"updatedAt": "2019-09-11T12:06:12.585Z",
"UserId": 4,
"ProjectId": 1
}
}
]
},
{
"name": "Rose",
"Tasks": [
{
"title": "project 1",
"UserProjects": {
"createdAt": "2019-09-11T12:06:12.585Z",
"updatedAt": "2019-09-11T12:06:12.585Z",
"UserId": 5,
"ProjectId": 1
}
},
{
"title": "project 2",
"UserProjects": {
"createdAt": "2019-09-11T16:49:17.227Z",
"updatedAt": "2019-09-11T16:49:17.227Z",
"UserId": 5,
"ProjectId": 2
}
}
]
},
{
"name": "Ishmael",
"Tasks": []
},
..............
{
"name": "Nash",
"Tasks": []
}
]

Server.js is,

const express = require('express');
const Sequelize = require('sequelize');
const Op = Sequelize.Op;
const _USERS = require('./users.json');

app.listen(port, () => {
console.log('Running server on port ' + port);
})