Deep integration of Sequelize ORM

Sequelize is an ORM framework based on Node.js that supports multiple database systems, including PostgreSQL, MySQL, SQLite, and MSSQL. Deep integration of Sequelize in Koa2 can greatly simplify database operations and improve development efficiency. With features like model definition, association relationships, and transaction handling, Sequelize provides robust data layer support for Koa2 applications.

Installation and Basic Configuration

First, install Sequelize and the corresponding database driver. For MySQL:

npm install sequelize mysql2

Initialize a Sequelize instance in a Koa2 project:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    idle: 10000
  }
});

It is recommended to extract the configuration into a separate file, such as config/database.js:

module.exports = {
  database: 'koa_demo',
  username: 'root',
  password: '123456',
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    idle: 10000
  }
};

Model Definition and Synchronization

Sequelize uses models to map database tables. Define a user model:

const { DataTypes } = require('sequelize');

const User = sequelize.define('User', {
  username: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  email: {
    type: DataTypes.STRING,
    validate: {
      isEmail: true
    }
  },
  age: {
    type: DataTypes.INTEGER,
    defaultValue: 18
  }
}, {
  timestamps: true,
  paranoid: true
});

There are three ways to synchronize models to the database:

1. Force synchronization (will drop existing tables):

await User.sync({ force: true });

2. Incremental synchronization (only adds missing fields):

await User.sync();

3. Global synchronization (synchronizes all models):

await sequelize.sync();

Model Associations

Sequelize supports various association relationships:

One-to-One Relationship

User.hasOne(Profile);
Profile.belongsTo(User);

One-to-Many Relationship

const Article = sequelize.define('Article', { title: DataTypes.STRING });
User.hasMany(Article);
Article.belongsTo(User);

Many-to-Many Relationship

const Tag = sequelize.define('Tag', { name: DataTypes.STRING });
Article.belongsToMany(Tag, { through: 'ArticleTag' });
Tag.belongsToMany(Article, { through: 'ArticleTag' });

CRUD Operations

Create Records

const user = await User.create({
  username: 'john_doe',
  email: 'john@example.com'
});

// Batch creation
await User.bulkCreate([
  { username: 'alice', email: 'alice@example.com' },
  { username: 'bob', email: 'bob@example.com' }
]);

Query Records

Basic query:

const users = await User.findAll();

Conditional query:

const user = await User.findOne({
  where: {
    username: 'john_doe'
  }
});

Pagination query:

const { count, rows } = await User.findAndCountAll({
  offset: 10,
  limit: 5
});

Update Records

await User.update(
  { age: 30 },
  { where: { username: 'john_doe' } }
);

Delete Records

await User.destroy({
  where: {
    username: 'john_doe'
  }
});

Transaction Handling

Sequelize supports multiple transaction handling methods:

Manual transaction management:

const transaction = await sequelize.transaction();
try {
  await User.create({ username: 'test' }, { transaction });
  await transaction.commit();
} catch (error) {
  await transaction.rollback();
}

Automatic transaction management:

await sequelize.transaction(async (t) => {
  await User.create({ username: 'test' }, { transaction: t });
});

Hooks

Sequelize provides various lifecycle hooks:

User.beforeCreate(async (user, options) => {
  user.username = user.username.toLowerCase();
});

User.afterCreate(async (user, options) => {
  console.log('New user created:', user.username);
});

Scopes

Define model scopes to reuse query conditions:

User.addScope('active', {
  where: {
    active: true
  }
});

// Use scope
const activeUsers = await User.scope('active').findAll();

Raw Queries

For complex queries, use raw SQL:

const [results, metadata] = await sequelize.query(
  'SELECT * FROM users WHERE age > :age',
  {
    replacements: { age: 20 },
    type: sequelize.QueryTypes.SELECT
  }
);

Performance Optimization

1. Use batch operations:

await User.bulkCreate(usersArray);

2. Limit query fields:

await User.findAll({
  attributes: ['id', 'username']
});

3. Use index hints:

await User.findAll({
  indexHints: [
    { type: 'USE', values: ['index_username'] }
  ]
});

Integration with Koa2

In a Koa2 application, typically mount the Sequelize instance to the context:

app.context.db = sequelize;

Then use it in middleware:

app.use(async (ctx, next) => {
  const users = await ctx.db.models.User.findAll();
  ctx.body = users;
});

For large projects, organize models by feature modules:

models/
  index.js
  user.js
  article.js
  comment.js

models/index.js initializes all models:

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(__filename);
const config = require('../config/database');

const db = {};

const sequelize = new Sequelize(config);

fs.readdirSync(__dirname)
  .filter(file => {
    return (file.indexOf('.') !== 0) && 
           (file !== basename) && 
           (file.slice(-3) === '.js');
  })
  .forEach(file => {
    const model = require(path.join(__dirname, file))(sequelize, Sequelize.DataTypes);
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Error Handling

Example of Sequelize error handling middleware:

app.use(async (ctx, next) => {
  try {
    await next();
  } catch (err) {
    if (err instanceof Sequelize.ValidationError) {
      ctx.status = 400;
      ctx.body = {
        errors: err.errors.map(e => ({
          field: e.path,
          message: e.message
        }))
      };
    } else {
      throw err;
    }
  }
});

Advanced Query Techniques

Subqueries

const activeUsers = await User.findAll({
  where: {
    id: {
      [Sequelize.Op.in]: sequelize.literal(
        '(SELECT userId FROM active_sessions)'
      )
    }
  }
});

JSON Queries

For databases supporting JSON:

await User.findAll({
  where: {
    meta: {
      [Sequelize.Op.contains]: { vip: true }
    }
  }
});

Multi-Table Joins

const articles = await Article.findAll({
  include: [{
    model: User,
    where: { active: true }
  }]
});

Data Validation

Sequelize provides various validation methods:

const User = sequelize.define('User', {
  email: {
    type: DataTypes.STRING,
    validate: {
      isEmail: true,
      notEmpty: true,
      len: [5, 100]
    }
  },
  age: {
    type: DataTypes.INTEGER,
    validate: {
      min: 0,
      max: 120
    }
  }
});

Custom validators:

validate: {
  isEven(value) {
    if (value % 2 !== 0) {
      throw new Error('Only even values are allowed!');
    }
  }
}

Performance Monitoring

Add query logging:

const sequelize = new Sequelize({
  // ...other configurations
  logging: (sql, timing) => {
    console.log(`[SQL] ${sql} - ${timing}ms`);
  },
  benchmark: true
});

Data Migration

While Sequelize provides the sync() method, migration tools are recommended for production:

Install Sequelize CLI:

npm install --save-dev sequelize-cli

Initialize migration configuration:

npx sequelize-cli init

Create migration file:

npx sequelize-cli migration:generate --name create-users

Run migrations:

npx sequelize-cli db:migrate

Rollback migrations:

npx sequelize-cli db:migrate:undo

Multiple Database Support

Sequelize can connect to multiple databases simultaneously:

const sequelizeMain = new Sequelize('main_db', 'user', 'pass', {
  dialect: 'mysql'
});

const sequelizeLog = new Sequelize('log_db', 'user', 'pass', {
  dialect: 'postgres'
});

Connection Pool Optimization

Adjust connection pool settings for better performance:

const sequelize = new Sequelize({
  // ...other configurations
  pool: {
    max: 20,
    min: 5,
    acquire: 30000,
    idle: 10000
  }
});

Type Safety

Use TypeScript for better type support:

import { Model, DataTypes, Optional } from 'sequelize';

interface UserAttributes {
  id: number;
  username: string;
  email?: string;
}

interface UserCreationAttributes extends Optional<UserAttributes, 'id'> {}

class User extends Model<UserAttributes, UserCreationAttributes> 
  implements UserAttributes {
  public id!: number;
  public username!: string;
  public email?: string;
}

User.init({
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  username: {
    type: DataTypes.STRING,
    allowNull: false
  },
  email: {
    type: DataTypes.STRING,
    validate: {
      isEmail: true
    }
  }
}, {
  sequelize,
  modelName: 'User'
});

Database Health Check

Add health check middleware:

router.get('/health', async (ctx) => {
  try {
    await sequelize.authenticate();
    ctx.body = { status: 'healthy' };
  } catch (error) {
    ctx.status = 503;
    ctx.body = { status: 'unhealthy', error: error.message };
  }
});

Data Encryption

Encrypt sensitive fields:

const crypto = require('crypto');

function encrypt(text) {
  const cipher = crypto.createCipher('aes-256-cbc', 'secret-key');
  let encrypted = cipher.update(text, 'utf8', 'hex');
  encrypted += cipher.final('hex');
  return encrypted;
}

User.beforeCreate((user) => {
  if (user.password) {
    user.password = encrypt(user.password);
  }
});

Cache Integration

Implement query caching with Redis:

const redis = require('redis');
const client = redis.createClient();

async function cachedQuery(key, queryFn, ttl = 3600) {
  const cached = await client.getAsync(key);
  if (cached) return JSON.parse(cached);
  
  const result = await queryFn();
  await client.setex(key, ttl, JSON.stringify(result));
  return result;
}

// Usage example
const users = await cachedQuery(
  'all_users',
  () => User.findAll()
);