Week 11 W11D3Querying with Express and Sequelize
There are a variety of query methods built into our models
** Create a router file for the table we are working on
** Create a Get All route for that router
Note that since we are interacting with our DB, this function needs to be async
To use these methods, we have to follow a couple steps
router.get("/", async (req, res) => {
const array = await <model>.findAll();
res.json(array);
});
Remember that all of the queries we write in Sequelize get turned into SQL
const users = await User.findAll(); = SELECT * FROM Users;
We can see this in the terminal
By default, Sequelize will do SELECT *
If we want to select certain columns, we need to add something to our query
All of the queries take in an object
We can add the attributes property to select certain columns, it takes in an array of the cols we want
router.get("/attributes", async (req, res) => {
const array = await <model>.findAll({
attributes: ["col1", "col2"],
});
res.json(array);
});
To add a WHERE statement, we add a where property to the query that takes in a nested object that has a key of the column and value of what we want to look for
router.get("/where", async (req, res) => {
const array = await <model>.findAll({
attributes: ["col1", "col2"],
where: {
<column>: <value>,
},
});
res.json(array);
});
If we only want to find a single record we can use findOne
router.get("/find-one/:name", async (req, res) => {
const name = req.params.name;
const <record> = await <model>.findOne({
where: {
name: name,
},
});
res.json(<record>);
});
Note, if we target a column that isn't UNIQUE, it will add LIMIT 1 to the SQL
We can also use findByPk to find a specific record by its id
In a findByPk, the first arg is not an obj, but instead the PK
router.get("/find-by-pk/:id", async (req, res) => {
const id = req.params.id;
const <record> = await <model>.findByPk(id);
res.json(<record>);
});
If we want to add ORDER BY, that is another option in our query object
order: [[<col>, 'DESC']]
router.get("/order", async (req, res) => {
const <records> = await <model>.findAll({
order: [
["col1", "DESC"],
["col2", "ASC"],
],
});
res.json(<records>);
});
Note that the arg for order is a 2D array
We can order by multiple columns by adding more inner arrays
If we want to use SQl comparison operators in our query, we can do so using the sequelize.Op package
** Create 2 post routes to demo (1 for /build and 1 for /create)
Build is used for creating records. It consists of a few steps
Create
Build does in one callWe can use destructuring to pull the data we need from the req.body
const {col1, col2 col3, etc...} = req.body;
To use build:
const <instance> = <model>.build({<data>})
await <instance>.validate()
await <instance>.save()
res.json(<instance>)
There are multiple ways to Update:
<model>.update - NOT recommended due to us running into a lot of issues with this method
This requires a where
Major preference and what we will be using
We want to make sure that we are not accidentally updating our properties to undefined
const {arg1, arg2} = req.body;
if (arg1 !== undefined) <instance>.property = arg1;
//...
This is done by checking the parts of the req.body to make sure they exist before updating
There are multiple ways to Delete:
<Model>.destroy() - NOT recommended for the same reasons as update
<instance>.destroy()
To tell Sequelize that a column is a FK, we need to add a couple properties to that column obj
references: {
model: <table name>,
key: 'id' (not needed unless PK is something besides id)
},
onDelete: 'CASCADE' (if desired)
Next we have to connect our models, and this is done by using Associations
We have to determine which is which, and much like raw SQL the order does matter
The model with the FK is the belongsTo
// <model we are in>.<relationship>(models.<name of the model we are connecting to>, { foreignKey: <name of the FK being used to connect>})
// in Post.js
Post.belongsTo(models.User, { foreignKey: "userId" })
// in User.js
User.hasMany(models.Post, { foreignKey: "userId" })
SELECT * FROM Posts
JOIN Users
ON Posts.userId = Users.id;
Or
SELECT * FROM Users
JOIN Posts
ON Users.id = Posts.userId;
In order to CASCADE Delete, we need to add some additional stuff to the hasMany
onDelete: 'CASCADE',hooks: trueThe hooks: true enforces that the deletions occur in the right order. If this is left off, we can still run into the Foreign Key Constraint Failed errors
belongsToMany(models.<name of the model on other side of joins table>, {
through: models.<name of model for join table>,
foreignKey: <FK to join to join table>,
otherKey: <FK to join from join table to other table>
})
** Create a route to do a getAll with the relationship
In our query object, we need to add an include property that points to the model we want to join.
Since we are referencing another model, we need to make sure and add that model to our imports.
include: <model name>
We can also add multiple models to the join by pointing the include property to an array
include: [<model 1>, <model 2>]
We can also point include to an object or an array of objects
include: {
model: <model name>
}
We can also add attributes and where properties to our include object.
This allows us to further refine our query, getting only the data that we need.
We can also have nested includes
If we don't want any of the info from our join table, we can add a through property to one of our includes objects. That through property will also point to an object with attributes: []
include: {
model: <model name>,
through: {
attributes: []
}
}