How to use Sequelize Cli
Sequelize is a popular, easy-to-use JavaScript object relational mapping (ORM) tool that works with SQL databases. It’s fairly straightforward to start a new project using the Sequelize CLI, but to truly take advantage of Sequelize’s capabilities, you’ll want to define relationships between your models.
In this walkthrough, we’ll set up a Sequelize project to assign tasks to particular users. We’ll use associations to define that relationship, then explore ways to query the database based on those associations.
Let’s start by installing Postgres, Sequelize, and the Sequelize CLI in a new project folder:
mkdir sequelize-associations
cd sequelize-associations
npm init -y
npm install sequelize pg
npm install --save-dev sequelize-cliNext, let’s initialize a Sequelize project, then open the whole directory in our code editor:
npx sequelize-cli init
code .To learn more about any of the Sequelize CLI commands below, see:
Getting Started with Sequelize CLI
Let’s configure our Sequelize project to work with Postgres. Find config.json in the /config directory and replace what’s there with this code:
{
"development": {
"database": "sequelize_associations_development",
"host": "127.0.0.1",
"dialect": "postgres"
},
"test": {
"database": "sequelize_associations_test",
"host": "127.0.0.1",
"dialect": "postgres"
},
"production": {
"database": "sequelize_associations_production",
"host": "127.0.0.1",
"dialect": "postgres"
}
}Cool, now we can tell Sequelize to create the database:
npx sequelize-cli db:createNext we will create a User model from the command line:
npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string,password:stringRunning model:generate automatically creates both a model file and a migration with the attributes we’ve specified. You can find these files within your project directory, but there’s no need to change them right now. (Later, we’ll edit the model file to define our associations.)
Now we’ll execute our migration to create the Users table in our database:
npx sequelize-cli db:migrateNow let’s create a seed file:
npx sequelize-cli seed:generate --name userYou will see a new file in /seeders. In that file, paste the following code to create a “John Doe” demo user:
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('Users', [{
firstName: 'John',
lastName: 'Doe',
email: 'demo@demo.com',
password: '$321!pass!123$',
createdAt: new Date(),
updatedAt: new Date()
}], {});
},down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('Users', null, {});
}
};
Once we’ve saved our seed file, let’s execute it:
npx sequelize-cli db:seed:allDrop into psql and query the database to see the Users table:
psql sequelize_associations_development
SELECT * FROM "Users";Defining associations
Great! We’ve got a working User model, but our John Doe seems a little bored. Let’s give John something to do by creating a Task model:
npx sequelize-cli model:generate --name Task --attributes title:string,userId:integerJust as with the User model above, this Sequelize CLI command will create both a model file and a migration based on the attributes we specified. But this time, we’ll need to edit both in order to tie our models together.
First, find task.js in the /models subdirectory within your project directory. This is the Sequelize model for tasks, and you’ll see that the sequelize.define() method sets up title and userId as attributes, just as we specified above.
Below that, you’ll see Task.associate. It’s currently empty, but this is where we’ll actually tie each task to a userId. Edit your file to look like this:
module.exports = (sequelize, DataTypes) => {
const Task = sequelize.define('Task', {
title: DataTypes.STRING,
userId: DataTypes.INTEGER
}, {});
Task.associate = function(models) {
// associations can be defined here
Task.belongsTo(models.User, {
foreignKey: 'userId',
onDelete: 'CASCADE'
})
};
return Task;
};What do those changes do? Task.belongsTo() sets up a “belongs to” relationship with the User model, meaning that each task will be associated with a specific user.
We do this by setting userId as a “foreign key,” which means it refers to a key in another model. In our model, tasks must belong to a user, so userId will correspond to theid in a particular User entry. (The onDelete: 'CASCADE' configures our model so that if a user is deleted, the user’s tasks will be deleted too.)
We also need to change our User model to reflect the other side of this relationship. Find user.js and change the section under User.associate so that your file looks like this:
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
firstName: DataTypes.STRING,
lastName: DataTypes.STRING,
password: DataTypes.STRING,
email: DataTypes.STRING
}, {});
User.associate = function(models) {
// associations can be defined here
User.hasMany(models.Task, {
foreignKey: 'userId',
})
};
return User;
};For this model, we’ve set up a “has many” relationship, meaning a user can have multiple tasks. In the .hasMany() method, the foreignKey option is set to the name of the key on the other table. In other words, when theuserId on a task is the same as the id of a user, we have a match.
We still have to make one more change to set up our relationship in the database. In your project’s/migrations folder, you should see a file whose name ends with create-task.js. Change the object labeled userId so that your file looks like the code below:
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Tasks', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
title: {
type: Sequelize.STRING
},
userId: {
type: Sequelize.INTEGER,
onDelete: 'CASCADE',
references: {
model: 'Users',
key: 'id',
as: 'userId',
}
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('Tasks');
}
};The references section will set up the Tasks table in our database to reflect the same relationships we described above. Now we can run our migration:
npx sequelize-cli db:migrateNow our John Doe is ready to take on tasks — but John still doesn’t have any actual tasks assigned. Let’s create a task seed file:
npx sequelize-cli seed:generate --name taskFind the newly generated seed file and paste in the following to create a task:
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('Tasks', [{
title: 'Build an app',
userId: 1,
createdAt: new Date(),
updatedAt: new Date()
}], {});
}, down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('Tasks', null, {});
}
};
We’ll set userId to 1 so that the task will belong to the user we created earlier. Now we can populate the database.
npx sequelize-cli db:seed:allTest the database:
psql sequelize_associations_development
SELECT * FROM "Users" JOIN "Tasks" ON "Tasks"."userId" = "Users".id;Querying via Sequelize
Now we can query our database for information based on these associations — and through Sequelize, we can do it with JavaScript, which makes it easy to incorporate with a Node.js application. Let’s create a file to hold our queries:
touch query.jsPaste the code below into your new file:
const { User, Task } = require('./models')
const Sequelize = require('sequelize');
const Op = Sequelize.Op
// Find all users with their associated tasks
// Raw SQL: SELECT * FROM "Users" JOIN "Tasks" ON "Tasks"."userId" = "Users".id;
const findAllWithTasks = async () => {
const users = await User.findAll({
include: [{
model: Task
}]
});
console.log("All users with their associated tasks:", JSON.stringify(users, null, 4));
}
const run = async () => {
await findAllWithTasks()
await process.exit()
}
run()The first three lines above import our User and Task models, along with Sequelize. After that, we include a query function that returns every User along with that user’s associated tasks.
Sequelize’s .findAll() method accepts options as a JavaScript object. Above, we used the include option to take advantage of “eager loading” — querying data from multiple models at the same time. With this option, Sequelize will return a JavaScript object that includes each User with all associated Task instances as nested objects.
Let’s run our query file to see this in action:
node query.jsNow it’s clear that our John Doe has a project to work on! We can use the same method to include the User when our query finds a Task. Paste the following code into query.js:
// Find a task with its associated user
// Raw SQL: SELECT * FROM "Tasks" JOIN "Users" ON "Users"."id" = "Tasks"."userId";
const findTasksWithUser = async () => {
const tasks = await Task.findAll({
include: [{
model: User
}]
});
console.log("All tasks with their associated user:", JSON.stringify(tasks, null, 4));
}Modify const run at the bottom of query.js by adding a line to call findTasksWithUser(). Now run your file again in Node — each Task should include info for the User it belongs to.
The queries in this walkthrough make use of the
.findAll()method. To learn more about other Sequelize queries, see: Using the Sequelize CLI and Querying
You can also include other options alongside include to make more specific queries. For example, below we’ll use the where option to find only the users named John while still returning the associated tasks for each:
// Find all users named John with their associated tasks
// Raw SQL: SELECT * FROM "Users" WHERE firstName = "John" JOIN tasks ON "Tasks"."userId" = "Users".id;const findAllJohnsWithTasks = async () => {
const users = await User.findAll({
where: { firstName: "John" },
include: [{
model: Task
}]
});
console.log("All users named John with their associated tasks:", JSON.stringify(users, null, 4));
}
Paste the above into your query.js and change const run to call findAllJohnsWithTasks() to try it out.
Now that you know how to use model associations in Sequelize, you can design your application to deliver the nested data you need. For your next step, you might decide to include more robust seed data using Faker or integrate your Sequelize application with Express to create a Node.js server!
This article was co-authored with Jeremy Rose, a software engineer, editor, and writer based in New York City.
More info on Sequelize CLI:
- Getting Started with Sequelize CLI
- Using Sequelize CLI and Querying
- Sequelize CLI and Express
- Getting Started with Sequelize CLI using Faker
- Build an Express API with Sequelize CLI and Express Router
- Building an Express API with Sequelize CLI and Unit Testing!
Comentarios
Publicar un comentario