CRUD operations with Sequelize in Express - Mini Project: Build & Apply

CRUD operations with Sequelize

📖 Scenario: You are building a simple Express API to manage a list of books in a library. Each book has a title and an author. You will use Sequelize to interact with a database and perform CRUD operations.

🎯 Goal: Create an Express app with Sequelize that can create, read, update, and delete books from the database.

📋 What You'll Learn

Create a Sequelize model for Book with title and author fields

Set up an Express app with Sequelize connection

Implement CRUD routes: POST /books, GET /books, PUT /books/:id, DELETE /books/:id

Use async/await and proper Sequelize methods for database operations

💡 Why This Matters

🌍 Real World

APIs like this are common in web apps to manage data records such as books, users, or products.

💼 Career

Understanding Sequelize CRUD operations is essential for backend developers working with Node.js and relational databases.

1

Set up Sequelize and define the Book model

Create a Sequelize instance called sequelize connected to an SQLite database in memory. Then define a model called Book with fields title and author, both of type Sequelize.STRING.

Express

const { Sequelize, DataTypes } = require('sequelize');

// Your code here

Hint

Use new Sequelize('sqlite::memory:') to create the connection. Use sequelize.define to create the model with title and author as string fields.

2

Set up Express app and sync database

Create an Express app called app. Add a line to sync the Sequelize models with the database using sequelize.sync().

Express

const express = require('express');
const app = express();

// Your code here

Hint

Import Express, create app with express(), and call sequelize.sync() to create tables. Use app.use(express.json()) to parse JSON bodies.

3

Create CRUD routes for books

Add four routes to app: POST /books to create a book using Book.create(), GET /books to get all books using Book.findAll(), PUT /books/:id to update a book by id using Book.update(), and DELETE /books/:id to delete a book by id using Book.destroy(). Use async functions and await for Sequelize calls.

Express

// Your code here

Hint

Use app.post, app.get, app.put, and app.delete with async functions. Use Sequelize methods create, findAll, update, and destroy with await.

4

Start the Express server

Add code to start the Express server on port 3000 using app.listen(3000). Add a callback that logs 'Server is running on port 3000'.

Express

// Your code here

Hint

Use app.listen(3000, () => { console.log('Server is running on port 3000') }) to start the server.

Practice

1. Which Sequelize method is used to add a new record to the database?

easy

A. update()

B. findAll()

C. create()

D. destroy()

Solution

1. Step 1: Understand CRUD operations

   CRUD stands for Create, Read, Update, Delete. Each operation has a matching Sequelize method.

2. Step 2: Match method to Create operation

   The create() method is used to add new records to the database.

3. Final Answer:

   create() -> Option C

4. Quick Check:

   Create = create() [OK]

Hint: Create means add new, so use create() method [OK]

Common Mistakes:

• Confusing create() with findAll() which reads data
• Using update() to add new records
• Using destroy() which deletes records

2. Which of the following is the correct syntax to update a user's name to 'Alice' where id is 5 using Sequelize?

easy

A. User.update({ name: 'Alice' }, { where: { id: 5 } })

B. User.update({ where: { id: 5 } }, { name: 'Alice' })

C. User.update('Alice', { id: 5 })

D. User.update({ id: 5 }, { name: 'Alice' })

Solution

1. Step 1: Recall Sequelize update syntax

   The update method takes two arguments: the new values object, and the options object with a where clause.

2. Step 2: Match correct argument order and structure

   User.update({ name: 'Alice' }, { where: { id: 5 } }) correctly places the new values first and the where condition second.

3. Final Answer:

   User.update({ name: 'Alice' }, { where: { id: 5 } }) -> Option A

4. Quick Check:

   update(values, { where }) = User.update({ name: 'Alice' }, { where: { id: 5 } }) [OK]

Hint: Update needs values first, then where condition [OK]

Common Mistakes:

• Swapping the order of arguments
• Passing values inside where instead of separate object
• Using strings instead of objects for values

3. Given the code:

const users = await User.findAll({ where: { age: { [Op.gt]: 18 } } });
console.log(users.length);

What will console.log(users.length) output?

medium

A. The total number of users in the database

B. An error because Op.gt is not defined

C. Always 0 because findAll returns undefined

D. The number of users older than 18

Solution

1. Step 1: Understand findAll with where clause

   The findAll method returns all records matching the where condition. Here, it filters users with age greater than 18.

2. Step 2: Determine what users.length represents

   users is an array of matching records, so users.length is the count of users older than 18.

3. Final Answer:

   The number of users older than 18 -> Option D

4. Quick Check:

   findAll with condition returns matching array length [OK]

Hint: findAll returns array; length counts matching records [OK]

Common Mistakes:

• Thinking findAll returns undefined or null
• Ignoring the where condition filtering
• Assuming Op.gt is undefined without importing

4. What is wrong with this code snippet for deleting a user by id?

await User.destroy(id);

medium

A. destroy requires an object with a where clause, not just id

B. destroy cannot be awaited

C. destroy only works with arrays, not single values

D. destroy needs a callback function

Solution

1. Step 1: Recall destroy method signature

   Sequelize's destroy method expects an options object with a where property to specify which records to delete.

2. Step 2: Identify incorrect argument usage

   Passing just the id directly is incorrect; it must be inside { where: { id: id } }.

3. Final Answer:

   destroy requires an object with a where clause, not just id -> Option A

4. Quick Check:

   destroy({ where: { id } }) is correct [OK]

Hint: destroy needs where inside an object, not just id [OK]

Common Mistakes:

• Passing id directly instead of inside where
• Not awaiting asynchronous destroy call
• Expecting destroy to accept callback

5. You want to update multiple users' status to 'active' where their last login was before 2023-01-01. Which Sequelize code correctly does this?

hard

A. User.update({ where: { lastLogin: { [Op.lt]: '2023-01-01' } } }, { status: 'active' })

B. User.update({ status: 'active' }, { where: { lastLogin: { [Op.lt]: new Date('2023-01-01') } } })

C. User.update('active', { lastLogin: { [Op.lt]: new Date('2023-01-01') } })

D. User.update({ status: 'active' }, { lastLogin: { [Op.lt]: new Date('2023-01-01') } })

Solution

1. Step 1: Understand update method parameters

   The first argument is the values to update, the second is an options object with a where clause to filter records.

2. Step 2: Check correct use of Op.lt and date object

   User.update({ status: 'active' }, { where: { lastLogin: { [Op.lt]: new Date('2023-01-01') } } }) correctly uses [Op.lt] with a Date object inside the where clause.

3. Step 3: Verify argument order and structure

   The other options have incorrect argument order or missing where wrapper.

4. Final Answer:

   User.update({ status: 'active' }, { where: { lastLogin: { [Op.lt]: new Date('2023-01-01') } } }) -> Option B

5. Quick Check:

   update(values, { where: condition }) with Op.lt date [OK]

Hint: Update needs values first, then where with Op.lt date [OK]

Common Mistakes:

• Swapping values and where arguments
• Using string instead of Date object for date comparison
• Omitting where wrapper around condition