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,
}

In the previous model, we only defined the model name as 'Book', but when we create the table in the database is gonna be named "Books", this is because when a table name is not specified, Sequelize table name inference, uses the library 

 to pluralize the model name, and apply it as the table name.

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();

The following line would be dangerous in a real-life application 

In a production environment, this could cause major data loss, you should be careful with it, the proper way of doing data migrations using Sequelize is through the 

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();


If you wanted to delete all the elements in a table you would do:

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();


One too many relations require for the existence of a third table, for indexing purposes, we can automatically create such a table by passing an inexistent table name when calling the 

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

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 

<h1 data-node-type="titlePlaceholder" class="titlePlaceholder">An elegant guide to Sequelize and Node.js</h1><p>Sequelize is a promise-based SQL ORM for Node.js, with support to Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL! <br>During this tutorial, we will go through the creation of a simple library database using Sequelize and Node.js system.<br></p><div data-panel-type="info" data-panel-position="inline" class="panel panel-type-info panel-position-inline"><div class="panel-container lolwut"><h2><a name="what-you-will-learn" id="what-you-will-learn">What You Will Learn</a></h2><ul><li><p>How to do CRUD SQL operations with Sequelize ✅</p></li><li><p>To create your own Sequelize models ✅</p></li><li><p>Use Sequelize to handle model relationships ✅</p></li></ul></div></div><h1><a name="requirements" id="requirements">Requirements</a></h1><ul><li><p><a href="https://www.newline.co/books/fullstack-nodejs/bonus-persistence-with-postgresql" rel="noopener noreferrer nofollow">Some experience with SQL.</a></p></li><li><p><a href="https://www.newline.co/@AoX04/nodejs-tutorial-how-javascript-on-the-backend-can-make-your-life-easier--714b9ba9" rel="noopener noreferrer nofollow">Some experience with Node.js and promises.</a></p></li></ul><h1><a name="create-a-model-to-represent-our-first-table" id="create-a-model-to-represent-our-first-table">Create a model to represent our first table</a></h1><p>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.<br>Let's create our first model to represent a book in our library.<br><br>But first using NPM we need to install the following libraries:</p><textarea data-iscodemirror="true" data-mode="text/jsx" data-uniqueid="cm-joykn" id="cm-joykn">npm i sequelize sqlite3</textarea><p>Let's create a db.js file, to initialize our Sequelize database connection</p><textarea data-iscodemirror="true" data-mode="text/jsx" data-uniqueid="cm-9GQDK" id="cm-9GQDK">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 
}
</textarea><p>Now, let's create a book.js file to store our model</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-JzQ7H" id="cm-JzQ7H">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,
}</textarea><div data-panel-type="tip" data-panel-position="inline" class="panel panel-type-tip panel-position-inline"><div class="panel-container lolwut"><p>In the previous model, we only defined the model name as 'Book', but when we create the table in the database is gonna be named "Books", this is because when a table name is not specified, Sequelize table name inference, uses the library <a href="https://www.npmjs.com/package/inflection" rel="noopener noreferrer nofollow">inflection</a> to pluralize the model name, and apply it as the table name.</p></div></div><p><br>Now let's add an index.js file to serve as our application entry point. Here we will have a <code>main</code> function that we will use to create our first object in the database.</p><textarea data-iscodemirror="true" data-mode="text/jsx" data-uniqueid="cm-oLPTP" id="cm-oLPTP">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();</textarea><div data-panel-type="warning" data-panel-position="inline" class="panel panel-type-warning panel-position-inline"><div class="panel-container lolwut"><p>The following line would be dangerous in a real-life application <br><br><code>await sequelize.sync({ alter: true });</code><br><br>In a production environment, this could cause major data loss, you should be careful with it, the proper way of doing data migrations using Sequelize is through the <a href="https://sequelize.org/master/manual/migrations.html" rel="noopener noreferrer nofollow">migrations</a> procedures.</p></div></div><h1><a name="using-our-model-to-do-more-crud-operations" id="using-our-model-to-do-more-crud-operations">Using our model to do more CRUD operations</a></h1><p>Let's have some fun with some crud operations inside our main function.<br><br>Let's list all the books in our database:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-yzuAQ" id="cm-yzuAQ">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();</textarea><p>Here is the data I got in my DB after running the findAll query:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-NKUdi" id="cm-NKUdi">[
  {
    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'
  }
]</textarea><p>To delete an entry in the database, we use <code>.destroy() </code> method: </p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-zeraP" id="cm-zeraP">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();
</textarea><div data-panel-type="tip" data-panel-position="inline" class="panel panel-type-tip panel-position-inline"><div class="panel-container lolwut"><p>If you wanted to delete all the elements in a table you would do:<br><br><code>await Book.destroy({ truncate: true }) </code></p><p>Any call to the destroy method without <code>truncate: true</code>, or a <code>where</code> clause will result in an error.</p></div></div><p>To update a model we use .update() method:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-zeraP" id="cm-zeraP">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();</textarea><p>To drop an entire table, we would use the <code>.drop()</code> method: </p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-sAiKR" id="cm-sAiKR">await Book.drop();
console.log("Book table dropped!");</textarea><h1><a name="indexes-and-performance-for-our-queries" id="indexes-and-performance-for-our-queries">Indexes and performance for our queries</a></h1><p>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 <br></p><textarea data-iscodemirror="true" data-mode="text/jsx" data-uniqueid="cm-JxBwZ" id="cm-JxBwZ">Book.findOne({where: {authorName: 'Marcus Pfister New name'} })</textarea><textarea data-iscodemirror="true" data-mode="text/jsx" data-uniqueid="cm-scuyW" id="cm-scuyW">Book.findByPk('10f544ce-d49a-4024-9665-584c33d0a971')</textarea><p>The difference is that, since the latter is using the primary Key index, to search for the element will be faster.</p><h1><a name="model-relationships" id="model-relationships">Model Relationships</a></h1><p>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.<br><br>Let's create the Author.js file:</p><textarea data-iscodemirror="true" data-mode="text/jsx" data-uniqueid="cm-NQNZc" id="cm-NQNZc">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 
}</textarea><p><br>And we need to update our Book.js file in the following manner to include the <code>authorId</code> foreign key:<br></p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-b8A0p" id="cm-b8A0p">// 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 
}</textarea><p>These are one too many relations. In Sequelize we use the method <code>.belongsTo()</code> and <code>.hasMany()</code> to properly define the relation.<br><br>In our index.js lets do the following:</p><textarea data-iscodemirror="true" data-mode="text/jsx" data-uniqueid="cm-RB0QY" id="cm-RB0QY">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();
</textarea><div data-panel-type="tip" data-panel-position="inline" class="panel panel-type-tip panel-position-inline"><div class="panel-container lolwut"><p>One too many relations require for the existence of a third table, for indexing purposes, we can automatically create such a table by passing an inexistent table name when calling the <code>.belongsTo()</code> method:<br><br><code>Book.belongsTo(Author,&nbsp;{&nbsp;through:&nbsp;'bookAuthorKeys'});</code><br></p></div></div><h1><a name="conclusion" id="conclusion">Conclusion</a></h1><p>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.<br><br>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.</p><p>Have fun and keep coding!</p><h2><a name="more-resources" id="more-resources">More Resources</a></h2><p>Check out the documentation of the modules we used in this post:</p><ul><li><p><a href="https://sequelize.org/master/index.html" rel="noopener noreferrer nofollow">Sequelize</a></p></li><li><p><a href="https://www.sqlite.org/docs.html" rel="noopener noreferrer nofollow">sqlite3</a></p></li></ul><h2><a name="getting-help-and-feedback" id="getting-help-and-feedback">Getting Help (and Feedback)</a></h2><p>If you have any questions - or want feedback on your post - come join <a href="https://newline.co/discord" rel="noopener noreferrer nofollow">our community Discord</a>. See you there!</p><p></p><p></p><p></p><p></p>

Learn

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

Teach

Amelia Wattenberger

Author of Fullstack D3

Community

An elegant guide to Sequelize and Node.js

Responses (0)

Masterclasses

Tutorials

Fullstack React with TypeScript