Just a few weeks until the 2021 JavaScript Full-Stack Bootcamp opens.
Signup to the waiting list!
When working with databases you can choose to use the primitives offered by the database, or use a library that builds on top and abstract the tiny details for you.
Sequelize is one of those libraries, and it’s a very popular Node.js wrapper for PostgreSQL, MySQL and other databases.
In this post I’m going to explore how to use Sequelize to work with a PostgreSQL database.
Install and configure Sequelize
Under the hood, Sequelize uses the pg
library to connect to PostgreSQL, so when we install the sequelize
npm package, we also need to install pg
:
npm install pg sequelize
Tip: don’t forget to first run
npm init -y
if the project is brand new and you don’t have apackage.json
file already.
In your Node.js file, you first define the database access variables:
const user = '<postgres user>'
const host = 'localhost'
const database = '<postgres db name>'
const password = '<postgres password>'
const port = '<postgres port>'
Then import 3 objects from sequelize
:
import { Sequelize, Model, DataTypes } from 'sequelize'
Then you can initialize a new Sequelize
object instance using this syntax:
const sequelize = new Sequelize(database, user, password, {
host,
port,
dialect: 'postgres',
logging: false
})
We tell Sequelize which kind of database this is in the dialect
property (as mentioned, it can handle more than just Postgres).
We also disable logging, because it can be very verbose as it logs all the SQL queries, which we don’t really need to look at (unless you’re debugging a problem).
How to create a Sequelize model
For every table you want to manipulate using Sequelize, you create a model.
Here’s an example, suppose we have a dogs
table with two columns: name
and age
.
We create a Dog
class extending the Model
base class:
import { Sequelize, Model, DataTypes } from 'sequelize'
const class Dog extends Model {}
Then call the init()
static method on the class describing the data it contains and the rules we want to apply. In this case, we disable null
:
Dog.init({
name: {
type: DataTypes.STRING,
allowNull: false
},
age: {
type: DataTypes.INTEGER,
allowNull: false
}
}, {
sequelize,
modelName: 'dog',
timestamps: false
})
We used DataTypes.STRING
and DataTypes.INTEGER
. The DataTypes
object contains reference to all the types we can use, and they map to each specific database type. See the official docs for more types you can use.
How to get data from the database
Now that we have a model, how do we get data out of a table?
We can use the findAll()
method:
Dog.findAll()
Calling this method will return a list of all the rows, and we’ll assign it to a variable:
const results = await Dog.findAll()
We use
await
becausefindAll()
returns a promise
To limit the columns we retrieve, pass an object with the attributes
array:
Dog.findAll({
attributes: ['age']
})
Add a WHERE
clause to the query using the where
property. For example, get all dogs with age 8:
Dog.findAll({
where: {
age: 8,
}
})
Or get all dogs with age higher than 5:
Dog.findAll({
where: {
age: {
[Op.gte]: 5,
}
}
})
More properties allow you to do other operations like limit
and order
:
Dog.findAll({
limit: 10,
order: [
['name', 'DESC']
]
})
How to insert data into the database
We can call Dog.create()
passing an object to create a new row in the database:
const name = 'Roger'
const age = 8
const result = await Dog.create({ name, age })
How to update data
Use the update()
method to update values in the table.
In this example I set the age of ‘Roger’ to 9:
Post.update({
age: 9
}, {
where: {
name: 'Roger'
}
})
Removing the where
property will update all rows:
Post.update({
age: 10
})
Download my free Node.js Handbook
The 2021 JavaScript Full-Stack Bootcamp will start at the end of March 2021. Don't miss this opportunity, signup to the waiting list!
More node tutorials:
- An introduction to the npm package manager
- Introduction to Node.js
- HTTP requests using Axios
- Where to host a Node.js app
- Interact with the Google Analytics API using Node.js
- The npx Node Package Runner
- The package.json guide
- Where does npm install the packages?
- How to update Node.js
- How to use or execute a package installed using npm
- The package-lock.json file
- Semantic Versioning using npm
- Should you commit the node_modules folder to Git?
- Update all the Node dependencies to their latest version
- Parsing JSON with Node.js
- Find the installed version of an npm package
- Node.js Streams
- Install an older version of an npm package
- Get the current folder in Node
- How to log an object in Node
- Expose functionality from a Node file using exports
- Differences between Node and the Browser
- Make an HTTP POST request using Node
- Get HTTP request body data using Node
- Node Buffers
- A brief history of Node.js
- How to install Node.js
- How much JavaScript do you need to know to use Node?
- How to use the Node.js REPL
- Node, accept arguments from the command line
- Output to the command line using Node
- Accept input from the command line in Node
- Uninstalling npm packages with `npm uninstall`
- npm global or local packages
- npm dependencies and devDependencies
- The Node.js Event Loop
- Understanding process.nextTick()
- Understanding setImmediate()
- The Node Event emitter
- Build an HTTP Server
- Making HTTP requests with Node
- The Node fs module
- HTTP requests in Node using Axios
- Reading files with Node
- Node File Paths
- Writing files with Node
- Node file stats
- Working with file descriptors in Node
- Working with folders in Node
- The Node path module
- The Node http module
- Using WebSockets with Node.js
- The basics of working with MySQL and Node
- Error handling in Node.js
- The Pug Guide
- How to read environment variables from Node.js
- How to exit from a Node.js program
- The Node os module
- The Node events module
- Node, the difference between development and production
- How to check if a file exists in Node.js
- How to create an empty file in Node.js
- How to remove a file with Node.js
- How to get the last updated date of a file using Node.js
- How to determine if a date is today in JavaScript
- How to write a JSON object to file in Node.js
- Why should you use Node.js in your next project?
- Run a web server from any folder
- How to use MongoDB with Node.js
- Use the Chrome DevTools to debug a Node.js app
- What is pnpm?
- The Node.js Runtime v8 options list
- How to fix the "Missing write access" error when using npm
- How to enable ES Modules in Node.js
- How to spawn a child process with Node.js
- How to get both parsed body and raw body in Express
- How to handle file uploads in Node.js
- What are peer dependencies in a Node module?
- How to write a CSV file with Node.js
- How to read a CSV file with Node.js
- The Node Core Modules
- Incrementing multiple folders numbers at once using Node.js
- How to print a canvas to a data URL
- How to create and save an image with Node.js and Canvas
- How to download an image using Node.js
- How to mass rename files in Node.js
- How to get the names of all the files in a folder in Node
- How to use promises and await with Node.js callback-based functions
- How to test an npm package locally
- How to check the current Node.js version at runtime
- How to use Sequelize to interact with PostgreSQL
- Serve an HTML page using Node.js
- How to solve the `util.pump is not a function` error in Node.js