Sling Academy
Home/Node.js/Sequelize.js: How to save a JSON object in a column

Sequelize.js: How to save a JSON object in a column

Last updated: December 29, 2023

Introduction

Sequelize is a powerful Object-Relational Mapping (ORM) library for Node.js. It allows developers to work with relational databases in an object-oriented fashion, using JavaScript. One of the robust features of Sequelize is its ability to handle JSON data types, which can be particularly useful when working with complex data structures that do not fit into the traditional relational tables. In this tutorial, we will explore how to save JSON objects in a column using Sequelize.js.

Prerequisites:

  • Basic understanding of Node.js and Sequelize.
  • Node.js and npm installed.
  • A relational database (like PostgreSQL, which supports JSON types) installed.
  • Sequelize CLI installed and a Sequelize project initialized.

Defining a Model with a JSON Column

First, we’ll start by defining a Sequelize model that includes a JSON column. Sequelize supports the JSON data type out of the box for databases like PostgreSQL and MySQL.

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'postgres'
});

const User = sequelize.define('user', {
  settings: {
    type: Sequelize.JSON,
    defaultValue: {}
  }
});

sequelize.sync();

Inserting JSON Data

Once the model is defined, you can insert JSON data directly into the ‘settings’ column.

User.create({
  settings: {
    theme: 'dark',
    notifications: true,
    version: 1
  }
});

Querying JSON Data

Sequelize also allows you to query JSON columns using the attributes and conditions you specify in your queries.

User.findOne({ where: Sequelize.json({ 'settings.theme': 'dark' }) }).then(user => {
  console.log(user);
});

Updating JSON Data

To update a JSON column, you can use the standard ‘update’ method.

User.update(
  { 'settings.theme': 'light' },
  { where: { id: 1 } }
);

Advanced Queries with JSON Data

You can also perform more complex operations on JSON columns, such as filtering based on nested properties or using functions to manipulate the JSON data.

// Find users with notifications enabled
User.findAll({
  where: Sequelize.json({ 'settings.notifications': true })
}).then(users => {
  console.log(users);
});

// Increment version inside the JSON column
User.findByPk(1).then(user => {
  user.settings.version++;
  user.save();
});

Handling JSON Arrays

In addition to JSON objects, you can handle JSON arrays in a similar fashion. Here’s an example of how you can manipulate a JSON array within a JSON column.

// Define a model with a JSON array in the 'tags' column
const Article = sequelize.define('article', {
  tags: {
    type: Sequelize.JSON,
    defaultValue: []
  }
});

// Add a tag to the 'tags' column
Article.findByPk(1).then(article => {
  article.tags.push('sequelize');
  article.save();
});

Conclusion

In this tutorial, we’ve covered the essentials of saving JSON objects in a column using Sequelize.js. We’ve looked at defining models with JSON types, inserting, querying, and updating JSON data, as well as handling more complex scenarios and arrays. Armed with this knowledge, you’re now well-equipped to utilize JSON columns in your Node.js applications seamlessly. As always, be sure to consult the Sequelize documentation for more details and advanced features.

Next Article: Sequelize: How to Migrate Data from One Database to Another (3 Ways)

Previous Article: Cursor-Based Pagination in SequelizeJS: Practical Examples

Series: Sequelize.js Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates