How to Use Visual Fields in Schema?

When using the JavaScript library Sequelize to operate on MySQL databases, the technique of virtual fields is often employed. Virtual fields refer to fields that exist on model instances but are not stored in the database. These fields can be used to add computed properties or perform formatting in query results. This article explores several scenarios of using virtual fields in Sequelize schemas.

1. Returning Full Name

Setting up Model Code

const User = sequelize.define('user', {
  firstName: Sequelize.STRING,
  lastName: Sequelize.STRING,
}, {
  // Define virtual field
  virtualFields: ['fullName'],

  // Define getter method for virtual field
  getterMethods: {
    fullName() {
      return `${this.firstName} ${this.lastName}`;
    },
  },
});

// Using virtual field
const user = await User.findByPk(1);
console.log(user.fullName); // Accessing virtual field

In the above example, by defining the virtual field fullName and its corresponding getter method, the complete name of the user can be obtained in query results.

Using Model Code

const Product = sequelize.define('product', {
  price: Sequelize.FLOAT,
  discount: Sequelize.FLOAT,
}, {
  // Define virtual field
  virtualFields: ['discountedPrice'],

  // Define getter method for virtual field
  getterMethods: {
    discountedPrice() {
      // Calculate discounted price based on discount
      return this.price - (this.price * this.discount) / 100;
    },
  },
});

// Using virtual field
const product = await Product.findByPk(1);
console.log(product.discountedPrice); // Accessing virtual field

In this example, the virtual field discountedPrice makes it easy to calculate the discounted price of a product.

Example 2: Using Virtual Fields to Generate URL

Model Setup Code

// models/Photo.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Photo = sequelize.define('Photo', {
  filename: {
    type: DataTypes.STRING,
    allowNull: false,
  },
}, {
  virtualFields: ['url'],

  getterMethods: {
    url() {
      return `https://example.com/photos/${this.filename}`;
    },
  },
});

module.exports = Photo;

Model Usage Code

// Example code
// ...

// Get URLs for all photos
const photos = await Photo.findAll();

// Format results, including virtual fields
const formattedPhotos = photos.map(photo => ({
  id: photo.id,
  filename: photo.filename,
  url: photo.url, // Accessing virtual field
}));

// Send response
res.json({ photos: formattedPhotos });

Example 3: Using Virtual Fields for Currency Conversion

Model Setup Code

// models/Transaction.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Transaction = sequelize.define('Transaction', {
  amount: {
    type: DataTypes.DECIMAL(10, 2),
    allowNull: false,
  },
  currency: {
    type: DataTypes.STRING,
    allowNull: false,
  },
}, {
  virtualFields: ['formattedAmount'],

  getterMethods: {
    formattedAmount() {
      return `${this.amount.toFixed(2)} ${this.currency}`;
    },
  },
});

module.exports = Transaction;

Model Usage Code

// Example code
// ...

// Get formatted amounts for all transactions
const transactions = await Transaction.findAll();

// Format results, including virtual fields
const formattedTransactions = transactions.map(transaction => ({
  id: transaction.id,
  amount: transaction.amount,
  currency: transaction.currency,
  formattedAmount: transaction.formattedAmount, // Accessing virtual field
}));

// Send response
res.json({ transactions: formattedTransactions });

Example 4: Using Virtual Fields for Permission Control

Model Setup Code

// models/User.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const User = sequelize.define('User', {
  role: {
    type: DataTypes.STRING,
    allowNull: false,
  },
}, {
  virtualFields: ['isAdmin'],

  getterMethods: {
    isAdmin() {
      return this.role === 'admin';
    },
  },
});

module.exports = User;

Model Usage Code

// Example code
// ...

// Get all users and check if they are administrators
const users = await User.findAll();

// Format results, including virtual fields
const formattedUsers = users.map(user => ({
  id: user.id,
  role: user.role,
  isAdmin: user.isAdmin, // Accessing virtual field
}));

// Send response
res.json({ users: formattedUsers });

Example 5: Using Virtual Fields for Image Dimensions

Model Setup Code

// models/Image.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Image = sequelize.define('Image', {
  width: {
    type: DataTypes.INTEGER,
    allowNull: false,
  },
  height: {
    type: DataTypes.INTEGER,
    allowNull: false,
  },
}, {
  virtualFields: ['aspectRatio'],

  getterMethods: {
    aspectRatio() {
      return this.width / this.height;
    },
  },
});

module.exports = Image;

Model Usage Code

// Example code
// ...

// Get aspect ratios for all images
const images = await Image.findAll();

// Format results, including virtual fields
const formattedImages = images.map(image => ({
  id: image.id,
  width: image.width,
  height: image.height,
  aspectRatio: image.aspectRatio, // Accessing virtual field
}));

// Send response
res.json({ images: formattedImages });

Through the introduction of these five typical examples in this article, developers can gain a deeper understanding of how to use virtual fields in Sequelize for model setup and usage.