Convert SQL JSON and JSONB to Sequelize DataTypes

JSON Columns in Sequelize

Modern SQL databases support JSON storage natively, and Sequelize provides two dedicated DataTypes: JSON and JSONB. The converter distinguishes between them based on the SQL column type.

JSON vs JSONB

Feature	DataTypes.JSON	DataTypes.JSONB
Storage	Text-based	Binary (parsed)
Query operators	Limited	Full (containment, key exists)
Indexing	Not indexable	GIN indexable
Database	MySQL, PostgreSQL, SQLite	PostgreSQL only

Example Conversion

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  metadata JSON,
  search_data JSONB,
  tags JSONB DEFAULT '[]'
);

Product.init({
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false },
  name: { type: DataTypes.STRING(255), allowNull: false },
  metadata: { type: DataTypes.JSON, allowNull: true },
  search_data: { type: DataTypes.JSONB, allowNull: true },
  tags: { type: DataTypes.JSONB, allowNull: true, defaultValue: '[]' },
}, { ... });

Querying JSON Columns

Sequelize provides special operators for JSON queries:

// PostgreSQL JSONB containment
Product.findAll({
  where: { metadata: { [Op.contains]: { color: 'red' } } }
});

// Nested key access
Product.findAll({
  where: { 'metadata.category': 'electronics' }
});

TypeScript Typing

In TypeScript mode, JSON columns are typed as object. For stricter typing, you can manually refine the generated interface:

interface ProductAttributes {
  metadata: { color: string; size: string } | null;
  search_data: Record<string, unknown> | null;
}

Default Values

JSON default values in SQL (like DEFAULT '{}' or DEFAULT '[]') are preserved as string literals in the Sequelize model. Sequelize automatically parses these when creating records.