In this section, we will explore how to use SQL databases with Sequelize, a promise-based Node.js ORM (Object-Relational Mapping) for PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server. Sequelize provides a straightforward API for interacting with SQL databases, making it easier to perform CRUD operations and manage database schemas.
Table of Contents
Introduction to Sequelize
Sequelize is a powerful ORM that allows you to interact with SQL databases using JavaScript. It abstracts the complexities of SQL queries and provides a clean and intuitive API for database operations.
Key Features of Sequelize:
- Promise-based: Sequelize uses promises for asynchronous operations.
- Supports multiple SQL dialects: PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server.
- Model-based: Define your database schema using models.
- Associations: Easily define relationships between models.
- Migrations: Manage database schema changes with migrations.
Setting Up Sequelize
To get started with Sequelize, you need to install Sequelize and the appropriate database driver for your SQL database.
Installation
# Install Sequelize npm install sequelize # Install the database driver (e.g., for PostgreSQL) npm install pg pg-hstore
Initializing Sequelize
Create a new file database.js to initialize Sequelize:
const { Sequelize } = require('sequelize');
// Create a new Sequelize instance
const sequelize = new Sequelize('database', 'username', 'password', {
host: 'localhost',
dialect: 'postgres', // Change this to your database dialect
});
// Test the connection
sequelize.authenticate()
.then(() => {
console.log('Connection has been established successfully.');
})
.catch(err => {
console.error('Unable to connect to the database:', err);
});
module.exports = sequelize;Replace 'database', 'username', and 'password' with your actual database credentials.
Defining Models
Models in Sequelize represent tables in your database. Each model corresponds to a table, and each instance of a model represents a row in that table.
Example Model Definition
Create a new file models/User.js to define a User model:
const { DataTypes } = require('sequelize');
const sequelize = require('../database');
const User = sequelize.define('User', {
// Define attributes
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true,
},
username: {
type: DataTypes.STRING,
allowNull: false,
},
email: {
type: DataTypes.STRING,
allowNull: false,
unique: true,
},
password: {
type: DataTypes.STRING,
allowNull: false,
},
}, {
// Other model options
timestamps: true,
});
module.exports = User;Performing CRUD Operations
CRUD operations (Create, Read, Update, Delete) are fundamental for interacting with databases. Sequelize makes it easy to perform these operations.
Create
const User = require('./models/User');
// Create a new user
User.create({
username: 'john_doe',
email: '[email protected]',
password: 'securepassword',
}).then(user => {
console.log('User created:', user.toJSON());
}).catch(err => {
console.error('Error creating user:', err);
});Read
// Find a user by primary key
User.findByPk(1).then(user => {
if (user) {
console.log('User found:', user.toJSON());
} else {
console.log('User not found');
}
}).catch(err => {
console.error('Error finding user:', err);
});Update
// Update a user's email
User.update({ email: '[email protected]' }, {
where: {
id: 1,
},
}).then(([rowsUpdated]) => {
console.log('Number of rows updated:', rowsUpdated);
}).catch(err => {
console.error('Error updating user:', err);
});Delete
// Delete a user
User.destroy({
where: {
id: 1,
},
}).then(() => {
console.log('User deleted');
}).catch(err => {
console.error('Error deleting user:', err);
});Associations
Sequelize supports various types of associations between models, such as one-to-one, one-to-many, and many-to-many relationships.
Example: One-to-Many Association
Define a Post model and associate it with the User model:
const { DataTypes } = require('sequelize');
const sequelize = require('../database');
const User = require('./User');
const Post = sequelize.define('Post', {
title: {
type: DataTypes.STRING,
allowNull: false,
},
content: {
type: DataTypes.TEXT,
allowNull: false,
},
}, {
timestamps: true,
});
// Define the association
User.hasMany(Post, { foreignKey: 'userId' });
Post.belongsTo(User, { foreignKey: 'userId' });
module.exports = Post;Practical Exercises
Exercise 1: Define a Product Model
Define a Product model with the following attributes:
id(primary key, auto-increment)name(string, not null)price(decimal, not null)stock(integer, not null)
Solution:
const { DataTypes } = require('sequelize');
const sequelize = require('../database');
const Product = sequelize.define('Product', {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true,
},
name: {
type: DataTypes.STRING,
allowNull: false,
},
price: {
type: DataTypes.DECIMAL,
allowNull: false,
},
stock: {
type: DataTypes.INTEGER,
allowNull: false,
},
}, {
timestamps: true,
});
module.exports = Product;Exercise 2: Perform CRUD Operations on the Product Model
- Create a new product.
- Read the product by its primary key.
- Update the product's price.
- Delete the product.
Solution:
const Product = require('./models/Product');
// Create a new product
Product.create({
name: 'Laptop',
price: 999.99,
stock: 10,
}).then(product => {
console.log('Product created:', product.toJSON());
// Read the product by its primary key
return Product.findByPk(product.id);
}).then(product => {
if (product) {
console.log('Product found:', product.toJSON());
// Update the product's price
return product.update({ price: 899.99 });
}
}).then(product => {
if (product) {
console.log('Product updated:', product.toJSON());
// Delete the product
return product.destroy();
}
}).then(() => {
console.log('Product deleted');
}).catch(err => {
console.error('Error:', err);
});Summary
In this section, we covered the basics of using SQL databases with Sequelize. We learned how to:
- Set up Sequelize and connect to a database.
- Define models to represent database tables.
- Perform CRUD operations using Sequelize's API.
- Define associations between models.
By mastering these concepts, you can efficiently interact with SQL databases in your Node.js applications. In the next module, we will explore authentication and authorization techniques in Node.js applications.
Node.js Course
Module 1: Introduction to Node.js
Module 2: Core Concepts
Module 3: File System and I/O
Module 4: HTTP and Web Servers
Module 5: NPM and Package Management
- Introduction to NPM
- Installing and Using Packages
- Creating and Publishing Packages
- Semantic Versioning
Module 6: Express.js Framework
- Introduction to Express.js
- Setting Up an Express Application
- Middleware
- Routing in Express
- Error Handling
Module 7: Databases and ORMs
- Introduction to Databases
- Using MongoDB with Mongoose
- Using SQL Databases with Sequelize
- CRUD Operations
Module 8: Authentication and Authorization
Module 9: Testing and Debugging
- Introduction to Testing
- Unit Testing with Mocha and Chai
- Integration Testing
- Debugging Node.js Applications
Module 10: Advanced Topics
Module 11: Deployment and DevOps
- Environment Variables
- Using PM2 for Process Management
- Deploying to Heroku
- Continuous Integration and Deployment