Sequelize CLI and Express

Sequelize is a popular, easy-to-use JavaScript object relational mapping (ORM) tool that works with SQL databases. If you’re comfortable querying your database using Sequelize models, it’s fairly straightforward to tie Sequelize in with Express to serve your data from a Node.js app.

In this walkthrough, we’ll start a Sequelize project from scratch, then incorporate Express to build basic routes that will deliver the information from your database.

Let’s start by installing Postgres, Sequelize, and the Sequelize CLI in a new project folder:

mkdir express-sequelize-api
cd express-sequelize-api
npm init -y && npm install sequelize pg &&  npm install --save-dev sequelize-cli

Next we will initialize a Sequelize project, then open it 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 make sure the development section looks like this:

"development": {
    "database": "products_development",
    "host": "127.0.0.1",
    "dialect": "postgres"
  }

Cool, now we can tell the Sequelize CLI to create the database:

npx sequelize-cli db:create

Next we will create a Product model:

npx sequelize-cli model:generate --name Product --attributes title:string,description:string,price:integer

Running model:generate automatically creates both a model file and a migration with the attributes we’ve specified. For now, you won’t need to worry about (or edit) these in order to make our simple Express app below.

Check out the other data types you can use with Sequelize: https://sequelize.org/master/manual/data-types.html

Now we’ll execute our migration to create the Products table in our database:

npx sequelize-cli db:migrate

Now let’s create a seed file:

npx sequelize-cli seed:generate --name products

You will see a new file in the/seeders directory. In that file, paste the code below, which will create an entry in your database for each of the following irresistible products:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Products', [{
      title: 'Apple AirPods',
      description: "https://www.apple.com/airpods",
      price: 199,
      createdAt: new Date(),
      updatedAt: new Date()
    },
    {
      title: 'Apple iPhone Pro',
      description: "https://www.apple.com/iphone-11-pro",
      price: 1000,
      createdAt: new Date(),
      updatedAt: new Date()
    },
    {
      title: 'Apple Watch',
      description: "https://www.apple.com/watch",
      price: 499,
      createdAt: new Date(),
      updatedAt: new Date()
    },
    {
      title: 'Vespa Primavera',
      description: "https://www.vespa.com/us_EN/vespa-models/primavera.html",
      price: 3000,
      createdAt: new Date(),
      updatedAt: new Date()
    },
    {
      title: 'New Balance 574 Core',
      description: "https://www.newbalance.com/pd/574-core/ML574-EG.html",
      price: 84,
      createdAt: new Date(),
      updatedAt: new Date()
    },
    {
      title: 'Tribe Messenger Bike 004',
      description: "https://tribebicycles.com/collections/messenger-series/products/mess-004-tx",
      price: 675,
      createdAt: new Date(),
      updatedAt: new Date()
    },
    {
      title: 'Stumptown Hair Bender Coffee',
      description: "https://www.stumptowncoffee.com/products/hair-bender",
      price: 16,
      createdAt: new Date(),
      updatedAt: new Date()
    }], {});
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('Products', null, {});
  }
};

Once we’ve saved our seed file, let’s execute it:

npx sequelize-cli db:seed:all

Drop into psql and query the database to see what’s in your Products table:

psql products_development
SELECT * FROM "Products";

You should see a stylish Vespa, some strong coffee, and a few Apple products, among other things. Great!

Setting up Express

Now that our database is set up, let’s incorporate Express to deliver these items via a Node.js server. Express is a highly customizable framework with a lot of capabilities, but for this example we’ll just quickly set up a few basic routes.

First, let’s install Express in our project directory and create a file to hold our server code:

npm install express
touch server.js

Express doesn’t require much configuration to get up and running. In your server.js file, just add the following:

const express = require('express')
const PORT = process.env.PORT || 3000
const app = express()
app.listen(PORT, () => {
  console.log(`Express server listening on port ${PORT}`)
});
app.get('/', (req, res) => {
  res.send("This is root!")
})

The first line pulls in the Express library, the second sets a default port (3000), and the third line fires up Express. Now app refers to your running Express app, and we can use methods like .listen() and .get() to tell it what to do!

Now fire up your app…

node server.js

…and open localhost:3000 in your web browser. You should see “This is root!” — that’s because we used app.listen() in our code to listen at that port, and we used app.get() to set up a route at '/'. (Press ctrl + c in your terminal to exit.)

Using Sequelize with Express

Awesome! Now we can connect our server to Sequelize. First, we need to introduce our Product model from within the /models folder. Add this line near the top of server.js:

const { Product } = require('./models')

Now we can create a route to show our entire list of products. We’ll use app.get() to set a route at '/products' and use our Sequelize model’s .findAll() method to return every Product:

app.get('/products', async (req, res) => {
    const products = await Product.findAll()
    res.json(products)
})

Restart the server by typing node server.js in your terminal, then test the route in your browser by going to http://localhost:3000/products. You should see a JSON response that includes every object, from the Apple Watch to the messenger bike.

OK, great! But what if we would like to see a specific product? Let’s say we navigate to http://localhost:3000/products/2 in the browser. We can make our API respond with only the product whose id is 2 — and to do this we’ll use thereq.params object in Express:

app.get('/products/:id', async (req, res) => {
  const { id } = req.params
  const product = await Product.findById(id)
  res.json(product)
})

The req object in Express represents the HTTP request from the user. The req.params property includes the parameters we named in our route — these parameters are set up with a colon, like :id in '/products/:id' above. With req.params.id, we can use Sequelize’s .findById() method to get only the object whose id matches our URL.

OK, but what if the id doesn’t exist in the database? Currently, our user will get a JSON response that just says null. We can send a more useful error message by using a try/catch block:

app.get('/products/:id', async (req, res) => {
    try {
        const { id } = req.params
        const product = await Product.findByPk(id)
        if (!product) throw Error('Product not found')
        res.json(product)
    } catch (e) {
        console.log(e)
        res.send('Product not found!')
    }
})

Does it work? Restart the server and test the route.

node server.js

Open http://localhost:3000/products/2 in your browser. Hey, it’s an iPhone! (You can also test the error handling by navigating to /products/987654321.)

Going further with Express and Sequelize

Now you know how to define endpoints in Express that deliver data from your database via a simple Sequelize model — and you can expand your project by using other Express request methods with different Sequelize queries.

But when you’re tinkering with your Express project, restarting the server with every change can be a pain. Let’s fix this by installing nodemon!

npm install nodemon --save-dev

Modify your package.json file:

....
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon server.js"
  },
....

Now when you run npm start from your terminal, nodemon will watch for changes to your JavaScript files and automatically restart your server — so you won’t have to keep going back to your terminal while you’re designing new routes using other Sequelize querying methods!

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
• Creating Sequelize Associations with Sequelize CLI
• 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!