Master Egg‑Sequelize: From Setup to Advanced Tips for Egg.js Projects

Introduction

Sequelize is a Node.js ORM library. Egg‑Sequelize is a plugin that wraps Sequelize for the Egg framework, allowing database operations through object‑oriented APIs.

Purpose and Advantages of Egg‑Sequelize

Egg‑Sequelize simplifies database interaction with the following benefits:

Simplified Database Operations : ORM eliminates the need to write complex SQL.

Model Definition and Management : Define table structures via models and manipulate data through model methods.

Data Validation and Associations : Define validation rules and relationships to ensure data integrity.

Transaction Management : Supports database transactions for atomic operations.

With simple model definitions you can perform CRUD operations quickly, keep business logic separate from data access, and support multiple databases such as MySQL, PostgreSQL, and SQLite.

Basic Usage of Egg‑Sequelize

1. Installation and Configuration

Install the required packages:

<code>npm install --save egg-sequelize sequelize mysql2</code>

Enable the plugin in config/plugin.js :

<code>exports.sequelize = {
  enable: true,
  package: 'egg-sequelize',
};</code>

Configure the database connection in config/config.default.js :

<code>exports.sequelize = {
  dialect: 'mysql',
  host: 'localhost',
  port: 3306,
  database: 'test',
  username: 'root',
  password: 'yourpassword',
};</code>

2. Define Model

Create a model file under app/model , for example user.js :

<code>module.exports = app => {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    name: STRING(30),
    age: INTEGER,
    created_at: DATE,
    updated_at: DATE,
  });
  return User;
};</code>

Sequelize provides various field types, including STRING, TEXT, CHAR, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, DATE, DATEONLY, TIME, BOOLEAN, and BLOB.

3. Use Model

In a controller (e.g., app/controller/user.js ) you can perform CRUD operations:

<code>const Controller = require('egg').Controller;

class UserController extends Controller {
  async index() {
    const users = await this.ctx.model.User.findAll();
    this.ctx.body = users;
  }

  async create() {
    const { name, age } = this.ctx.request.body;
    const user = await this.ctx.model.User.create({ name, age });
    this.ctx.body = user;
  }
}

module.exports = UserController;</code>

Note that Sequelize automatically adds createdAt and updatedAt fields; you can disable them with timestamps: false if not needed.

Advanced Usage

1. Complex Queries

Sequelize supports rich query options, including conditions and associations. Example: find users older than 30.

<code>const users = await this.ctx.model.User.findAll({
  where: { age: { [Op.gt]: 30 } }
});</code>

2. Transaction Management

Wrap multiple operations in a transaction to guarantee atomicity:

<code>const transaction = await this.ctx.model.transaction();
try {
  const user = await this.ctx.model.User.create({ name, age }, { transaction });
  await this.ctx.model.Profile.create({ userId: user.id, bio }, { transaction });
  await transaction.commit();
} catch (err) {
  await transaction.rollback();
  throw err;
}</code>

3. Data Validation

Define validation rules in the model, e.g., limit username length:

<code>const User = app.model.define('user', {
  name: {
    type: STRING(30),
    validate: { len: [2, 30] }
  }
});</code>

4. Database Migration

Use Sequelize’s migration tool to manage schema changes:

<code>npx sequelize migration:create --name create-user</code>

In the generated migration file:

<code>module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('users', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
      name: { type: Sequelize.STRING },
      created_at: { type: Sequelize.DATE },
      updated_at: { type: Sequelize.DATE }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('users');
  }
};</code>

5. Data Association

Define relationships, such as a many‑to‑many association between users and roles:

<code>const User = app.model.define('user', {/* ... */});
const Role = app.model.define('role', {/* ... */});
User.belongsToMany(Role, { through: 'UserRole' });
Role.belongsToMany(User, { through: 'UserRole' });</code>

Query with eager loading:

<code>const users = await this.ctx.model.User.findAll({
  include: [{ model: this.ctx.model.Role, through: { attributes: [] } }]
});</code>

Common Pitfalls and Solutions

1. Unhandled Transaction Errors

Failing to catch and roll back a transaction can cause data inconsistency.

<code>const transaction = await sequelize.transaction();
try {
  // transactional operations
  await transaction.commit();
} catch (error) {
  await transaction.rollback();
  throw error;
}</code>

2. Improper Concurrency Handling

High‑concurrency scenarios may lead to race conditions; use row‑level locks or optimistic locking.

<code>const user = await User.findByPk(1, { lock: true, transaction });</code>

3. N+1 Query Problem

Associations can trigger many separate queries; use include for eager loading.

<code>const users = await User.findAll({ include: [{ model: Profile }] });</code>

4. Inefficient Query Optimization

Leverage raw queries and proper indexing for complex queries.

<code>const users = await sequelize.query('SELECT * FROM users WHERE age > ?', {
  replacements: [30],
  type: sequelize.QueryTypes.SELECT
});</code>

5. Model Synchronization in Production

Never use sync directly in production; rely on migrations instead.

<code>await sequelize.sync({ force: false });</code>

6. Unhandled Database Connection Issues

Configure a connection pool and handle authentication errors.

<code>const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql',
  pool: { max: 5, min: 0, acquire: 30000, idle: 10000 }
});
sequelize.authenticate().catch(err => {
  console.error('Unable to connect to the database:', err);
});</code>

7. Timestamp Mismanagement

Ensure consistent timestamp formats and time‑zone handling.

<code>const User = sequelize.define('user', {
  createdAt: { type: Sequelize.DATE, defaultValue: Sequelize.NOW }
}, {
  timestamps: true,
  timezone: '+00:00'
});</code>

8. Incorrect Data Validation

Define comprehensive validation rules to prevent invalid data.

<code>const User = sequelize.define('user', {
  name: { type: Sequelize.STRING, allowNull: false, validate: { len: [2, 50] } },
  email: { type: Sequelize.STRING, allowNull: false, validate: { isEmail: true } }
});</code>

9. Over‑reliance on Auto‑Generated SQL

For performance‑critical queries, write custom SQL.

<code>const users = await sequelize.query('SELECT * FROM users WHERE age > ?', {
  replacements: [30],
  type: sequelize.QueryTypes.SELECT
});</code>

10. Lack of Proper Testing

Write unit and integration tests for database operations.

<code>describe('User model', () => {
  it('should create a user', async () => {
    const user = await User.create({ name: 'John', age: 25 });
    expect(user.name).to.equal('John');
  });
});</code>

Conclusion

Egg‑Sequelize combines Egg.js and Sequelize to provide powerful database capabilities. By following best practices for basic and advanced usage, and by being aware of common pitfalls, developers can improve efficiency, maintainability, and performance of their Node.js backend applications.