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

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.