An elegant guide to Sequelize and Node.js

Sequelize is a promise-based SQL ORM for Node.js, with support to Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL! 

During this tutorial, we will go through the creation of a simple library database using Sequelize and Node.js system.

Create a model to represent our first table

Models are the soul of Sequelize, we use them to represent the data of our tables, both on a row-level as a model instance, or as a table structure level as a model.

Let's create our first model to represent a book in our library.

But first using NPM we need to install the following libraries:

Let's create a db.js file, to initialize our Sequelize database connection

const { Sequelize, DataTypes, Model } = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
    dialect: 'sqlite',
  	// we will be saving our db as a file on this path
    storage: 'database.sqlite', // or ':memory:'
});

module.exports = { 
    sequelize 
}


Now, let's create a book.js file to store our model

const { sequelize } = require('./db');

const Book = sequelize.define('Book', {
    // Here we define our model attributes
    // Each attribute will pair to a column in our database

    // Our primaryKey, book id, our unique identifier
    id: {
        type: DataTypes.UUID,
        defaultValue: Sequelize.UUIDV4,
        primaryKey: true,
    },

    // A column for the title of our book
    title: {
        type: DataTypes.STRING,
        allowNull: false
    },

    // A column for the author name 
    authorName: {
        type: DataTypes.STRING
        // remember allowNull defaults to true
    }
}, {
  	// For the sake of clarity we specify our indexes
    indexes: [{ unique: true, fields: ['id'] }]
});

// `sequelize.define` also returns the model
console.log(Book === sequelize.models.Book); // true

module.exports = { 
    Book,
    sequelize,
}

Now let's add an index.js file to serve as our application entry point. Here we will have a 

 function that we will use to create our first object in the database.

const { sequelize } = require('./db');
const { Book, sequelize }= require('./Book.js');

// Even tho our model is an ES6 compatible class 
// we should not use new to create an instance
// instead we should call the build method
async function main(){
	// The following line will sync our models
	// To the database creating them or altering the db
	// To match the new model
    await sequelize.sync({ alter: true });

    const newBook = Book.build({ title: "Rainbow Fish", authorName:"Marcus Pfister" });
    console.log(newBook instanceof Book); // true
    console.log(newBook.title); // "Jane"
	
  	// Our newly build instance haven't been saved
	// to the db yet, we need to call .save() 
    await newBook.save();
}

main();

Using our model to do more CRUD operations

Let's have some fun with some crud operations inside our main function.

Let's list all the books in our database:

const { Book, sequelize }= require('./Book.js');

async function main(){
    const books = await Book.findAll({
        // The following ensure we get plain data
        // Instead of an array of instances
        raw: true,
    });
    console.log(books);
}

main();

Here is the data I got in my DB after running the findAll query:

[
  {
    id: '867d2fc9-1d57-4b1a-8617-594968be4c05',
    title: 'Rainbow Fish',
    authorName: 'Marcus Pfister',
    createdAt: '2020-12-07 05:02:20.387 +00:00',
    updatedAt: '2020-12-07 05:02:20.387 +00:00'
  },
  {
    id: '7ff2ee59-ebae-4eef-aeb7-8f62571ce1fc',
    title: 'Rainbow Fish',
    authorName: 'Marcus Pfister',
    createdAt: '2020-12-07 05:02:42.113 +00:00',
    updatedAt: '2020-12-07 05:02:42.113 +00:00'
  },
  {
    id: '10f544ce-d49a-4024-9665-584c33d0a971',
    title: 'Rainbow Fish',
    authorName: 'Marcus Pfister',
    createdAt: '2020-12-07 05:16:44.556 +00:00',
    updatedAt: '2020-12-07 05:16:44.556 +00:00'
  }
]

To delete an entry in the database, we use 

const { Book, sequelize }= require('./Book.js');

async function main(){
    const delete_result = await Book.destroy({
      	// to restrict we only delete one
		// We use a where clause with the id
        where: {
            id:'867d2fc9-1d57-4b1a-8617-594968be4c05',
        }
    });

    console.log(delete_result);
	// we list all the remaining books in the table
    const books = await Book.findAll({
        raw:true,
    });
    console.log(books);
}

main();


To update a model we use .update() method:

const { Book, sequelize }= require('./Book.js');

async function main(){
    await Book.update(
		 // This object represent the fields
      	// We are trying to update
        { authorName: "Marcus Pfister New name" }, 
        {
          	// This where clause specify 
          	// Which element we are updating
            where: {
                id: '10f544ce-d49a-4024-9665-584c33d0a971',
            }
        }
    );

    const books = await Book.findAll({
        raw:true,
    });
    console.log(books);
}

main();

To drop an entire table, we would use the 

await Book.drop();
console.log("Book table dropped!");

The way we index our data in our database is essential for the functionality of our system, a good index will allow you to properly retrieve data faster, for example, the 2 following queries, gets us the same data 

Book.findOne({where: {authorName: 'Marcus Pfister New name'} })

Book.findByPk('10f544ce-d49a-4024-9665-584c33d0a971')

The difference is that, since the latter is using the primary Key index, to search for the element will be faster.

Models can relate to each other. Let's say we have an author model that relates to the book model where a book could have a single author and an author could have multiple books.

const { DataTypes } = require('sequelize/types');
const { sequelize } = require('./db');

const Author = sequelize.define('Author', {
    // Our primaryKey, author id, our unique identifier
    id: {
        type: DataTypes.UUID,
        defaultValue: Sequelize.UUIDV4,
        primaryKey: true,
    },

    // A column for the 
    name: {
        type: DataTypes.STRING,
        allowNull: false
    },

}, {
    indexes: [
        { unique: true, fields: ['id'] },
    ]
});

// `sequelize.define` also returns the model
console.log(Author === sequelize.models.Author); // true

module.exports = { 
    Author 
}

And we need to update our Book.js file in the following manner to include the 

// For this tutorial we will be using sqlite and storing our data in a file

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

const Book = sequelize.define('Book', {
    // Here we define our model attributes
    // Each attribute will pair to a column in our database

    // Our primaryKey, book id, our unique identifier
    id: {
        type: DataTypes.UUID,
        defaultValue: Sequelize.UUIDV4, // Or Sequelize.UUIDV1
        primaryKey: true,
    },

    // A column for the title of our book
    title: {
        type: DataTypes.STRING,
        allowNull: false
    },

    // A column for the author Id foreing key
    AuthorId: {
        type: DataTypes.STRING,
        allowNull: false
    }

}, {
    indexes: [
        { unique: true, fields: ['id'] },
        { unique: false, fields: ['AuthorId'] },
    ]
});

// `sequelize.define` also returns the model
console.log(Book === sequelize.models.Book); // true

module.exports = { 
    Book 
}

These are one too many relations. In Sequelize we use the method 

const { sequelize } = require('./db')
const { Book }= require('./Book.js');
const { Author }= require('./Author.js');



async function main(){

    try {
        // await sequelize.drop();
        // since we updated our models, we need to sync

        
        Book.belongsTo(Author, { through: 'bookAuthorKeys'});
        Author.hasMany(Book);

        await sequelize.sync({ force: true });

        // We create a new author
        const author = await Author.create({
            name: 'Marcus Pfister',
        });

		// we link the book to the author
		// Using AuthorId
        const book = await Book.create({
            AuthorId: author.get('id') ,
            title: 'this is a pretty book name'
        });

		// We retrieve the author model instance
      	// with the generated method .getAuthor
        console.log( await book.getAuthor({raw: true}));

      	// Here we retrieve the list of books
      	// Associated with the author
		// With the auto generated methor .getBooks
        console.log( await author.getBooks({raw: true}));

    } catch (error) {
        console.log(error)
    }
}

main();


We've been through a lot within this short post but with these few examples, you should have a good grasp on the core functionalities of Sequelize.

Sequelize is a really potent tool for working with SQL databases from within Node.js. If you want to keep going deeper into it, I recommend following it up with learning about Sequelize transactions and migrations.

Check out the documentation of the modules we used in this post:

If you have any questions - or want feedback on your post - come join 

Learn

The newline Guide to Building Your First GraphQL Server with Node and TypeScript

Teach

Amelia Wattenberger

Author of Fullstack D3

Community

Tutorials on Sequelize

An elegant guide to Sequelize and Node.js

Email Newsletter

Popular Topics

Masterclasses

Tutorials

Fullstack React with TypeScript