Interact with Relational Databases using Sequelize

I bet a lot of people like to use abstractions when they want to interact with databases for a variety of reasons.

In many cases I find a more intuitive solution, but this abstraction in some cases can cost flexibility.

But today I'm not talking about these points, today I'm going to teach how to create a simple CRUD using Sequelize ORM to interact with the database and Fastify will be our framework today.

You can use Sequelize with several relational databases such as MySQL, PostgreSQL and SQLite. In today's example I'm going to use SQLite, but if you want to use it with another one mentioned above, the configuration is simple and it's only done in one place.

The idea of today's application is to save a list of products, for that we will start by configuring the connection with the database, then we define our Model and only then start working on our logic.

At the end of this post you will have access to the github repository with the final code.

Let's code

First let's start by installing the necessary dependencies:

npm install fastify sequelize sqlite3

## If you don't use SQLite, replace sqlite3 with the following:
# MySQL => mysql2
# PostgreSQL => pg

Let's start by creating our database connection configuration. Let's import the Sequelize package and in this case let's make it clear that the dialect will be SQLite and where it will be created.

If you are using another dialect, follow this guide.

// @src/database/index.js

import Sequelize from "sequelize";

const connection = new Sequelize({
  dialect: "sqlite",
  storage: "src/database/dev.db",
});

export default connection;

Now we can define our Product Model. First let's import the Sequelize package, as well as the configuration of the connection to the database we created.

// @src/models/product.js

import Sequelize from "sequelize";

import database from "../database/index.js";

// More stuff comes here.

Now let's define our Model giving it the name of product and let's export it before we define each of its attributes.

// @src/models/product.js

import Sequelize from "sequelize";

import database from "../database/index.js";

const Product = database.define("product", {
  // More stuff comes here.
});

export default Product;

Now we can start defining each of our Model's attributes. First of all we know that we have to have an id which is going to be a primary key, auto-incrementing and which is an integer.

In the same way, we have two attributes that will be strings, called name and description.

Last but not least we have an attribute called price which is going to be a decimal number.

// @src/models/product.js

import Sequelize from "sequelize";

import database from "../database/index.js";

const Product = database.define("product", {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    allowNull: false,
  },
  name: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  price: {
    type: Sequelize.DECIMAL,
    allowNull: false,
  },
  description: {
    type: Sequelize.STRING,
    allowNull: false,
  },
});

export default Product;

With the connection to the database made and with our Model already defined, we can start working on the module that will launch our application.

First let's import the main module of our application (which has yet to be created) as well as the module for configuring the connection to the database.

Next we will create the function that will run our application, first we will connect to the database and only then we will start our application, however if an error occurs, we will end the process.

// @src/main.js

import app from "./app.js";
import database from "./database/index.js";

const start = async () => {
  try {
    await database.sync();
    await app.listen(3333);
  } catch (err) {
    console.error(err);
    process.exit(1);
  }
};

start();

And so that Sequelize can synchronize our Model with the database, just import it at the top of the module.

// @src/main.js

import "./models/product.js";
import app from "./app.js";
import database from "./database/index.js";

const start = async () => {
  try {
    await database.sync();
    await app.listen(3333);
  } catch (err) {
    console.error(err);
    process.exit(1);
  }
};

start();

With all the progress made so far, just start working on the main module of our application, where all the logic will reside.

First we will import Fastify and then our Product Model so that we can interact with the respective database table.

// @src/app.js

import Fastify from "fastify";

import Product from "./models/product.js";

const app = Fastify();

// More stuff comes here.

export default app;

Now we can start creating the routes for our application. First, I will create the route to acquire all the products that already exist in the database. And then we will return all these records.

For this we will use the Sequelize method .findAll() to get all the records we have in the product table.

// @src/app.js

app.get("/product", async (request, reply) => {
  const post = await Product.findAll();
  return reply.send({ post });
});

If you have already tested the application, you may have noticed that the response to the request you made was an empty array. So let's create the route to insert a new product in the database.

To do so, we will use Sequelize's .create() method to add each of the properties present in the http request body. And then we will return the product data that was entered into the database.

// @src/app.js

app.post("/product", async (request, reply) => {
  const post = await Product.create({ ...request.body });
  return reply.send({ post });
});

Now with the created product, let's get only that same article, for that we'll need to create a route with a parameter, which in this case will be the id.

Then we will use Sequelize's .findByPk() method which will fetch a product in the database table according to the primary key.

// @src/app.js

app.get("/product/:id", async (request, reply) => {
  const { id } = request.params;
  const post = await Product.findByPk(id);
  return reply.send({ post });
});

We can already fetch all the products, we can create a new product and we can only fetch one product.

Now we can start working on the route to updating a single product. For this we will again have a single parameter in the route, which will again be the id.

The Sequelize method that we will use this time will be .update() and we will pass two arguments, the first will be the updated object and the second will be its id to perform the update.

// @src/app.js

app.put("/product/:id", async (request, reply) => {
  const { id } = request.params;
  await Product.update({ ...request.body }, { where: { id } });
  const post = await Product.findByPk(id);
  return reply.send({ post });
});

Last but not least, we will remove a product from the database, for that we will use the .destroy() method and we will pass only the id of the product we want to delete.

// @src/app.js

app.delete("/product/:id", async (request, reply) => {
  const { id } = request.params;
  const post = await Product.findByPk(id);
  await Product.destroy({ where: { id } });
  return reply.send({ post });
});

The end result of our main module should look like the following:

// @src/app.js

import Fastify from "fastify";

import Product from "./models/product.js";

const app = Fastify();

app.get("/product", async (request, reply) => {
  const post = await Product.findAll();
  return reply.send({ post });
});

app.post("/product", async (request, reply) => {
  const post = await Product.create({ ...request.body });
  return reply.send({ post });
});

app.get("/product/:id", async (request, reply) => {
  const { id } = request.params;
  const post = await Product.findByPk(id);
  return reply.send({ post });
});

app.put("/product/:id", async (request, reply) => {
  const { id } = request.params;
  await Product.update({ ...request.body }, { where: { id } });
  const post = await Product.findByPk(id);
  return reply.send({ post });
});

app.delete("/product/:id", async (request, reply) => {
  const { id } = request.params;
  const post = await Product.findByPk(id);
  await Product.destroy({ where: { id } });
  return reply.send({ post });
});

export default app;