Database connection and ORM integration

Basic Methods of Database Connection

In Express applications, there are two main ways to connect to a database: using the database driver directly or through an ORM tool. Native driver connections are closer to the underlying layer and are suitable for scenarios requiring fine-grained query control. Taking MySQL as an example, the connection code using the mysql2 package is as follows:

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

async function queryUser(id) {
  const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
  return rows[0];
}

In the connection pool configuration parameters, connectionLimit controls the maximum number of connections, and queueLimit sets the length of the waiting queue. The advantage of this approach is the execution of raw SQL statements, but manual handling of result set conversion is required.

Core Value of ORM

Object-Relational Mapping (ORM) solves the impedance mismatch between database tables and program objects. Taking Sequelize as an example, model definitions not only include field mappings but can also declare relationships:

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('sqlite::memory:');

const User = sequelize.define('User', {
  username: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  age: {
    type: DataTypes.INTEGER,
    validate: { min: 18 }
  }
}, {
  indexes: [{ fields: ['username'] }]
});

const Post = sequelize.define('Post', {
  title: DataTypes.STRING,
  content: DataTypes.TEXT
});

User.hasMany(Post);  // One-to-many relationship
Post.belongsTo(User); // Inverse relationship

Validation rules like allowNull and validate automatically take effect before data persistence. Association methods like hasMany generate foreign key constraints, and queries can use include to achieve automatic table joins.

Deep Configuration of Connection Pools

Production environments require optimized connection pool parameters. Taking MongoDB's mongoose as an example:

const mongoose = require('mongoose');
const options = {
  poolSize: 5, // Connection pool size
  connectTimeoutMS: 30000, // Connection timeout
  socketTimeoutMS: 45000, // Socket timeout
  serverSelectionTimeoutMS: 5000 // Server selection timeout
};

mongoose.connect('mongodb://localhost:27017/mydb', options)
  .then(() => console.log('MongoDB connected'))
  .catch(err => console.error('Connection error:', err));

// Listen for connection events
mongoose.connection.on('connected', () => {
  console.log('Mongoose default connection open');
});

The key parameter poolSize should be adjusted based on application concurrency; setting it too high can overload the database. Event listeners can handle abnormal situations like connection interruptions.

Implementation Patterns for Transaction Handling

ORMs typically provide three ways to handle transactions. An example of explicit transactions in TypeORM:

import { getManager } from 'typeorm';

await getManager().transaction(async transactionalEntityManager => {
  await transactionalEntityManager.save(User, { name: 'Alice' });
  await transactionalEntityManager.update(Profile, { userId: 1 }, { age: 30 });
  
  // If an error is thrown here, the entire transaction rolls back
  const user = await transactionalEntityManager.findOne(User, 1);
  user.credits += 100;
  await transactionalEntityManager.save(user);
});

Decorator transactions simplify code through annotations:

@Transaction()
async transfer(@TransactionManager() manager: EntityManager) {
  await manager.decrement(Account, { id: 1 }, 'balance', 100);
  await manager.increment(Account, { id: 2 }, 'balance', 100);
}

Performance Optimization Strategies

N+1 queries are a common performance pitfall. Prisma's solution:

// Wrong approach: Generates N+1 queries
const posts = await prisma.post.findMany();
const authors = await Promise.all(
  posts.map(post => prisma.user.findUnique({ where: { id: post.authorId } }))
);

// Correct approach: Preload related data
const postsWithAuthors = await prisma.post.findMany({
  include: { author: true }
});

Batch operations can significantly improve performance:

// Single insert
for (const user of users) {
  await prisma.user.create({ data: user });
}

// Batch insert
await prisma.user.createMany({ data: users });

Multi-Database Support Solutions

Some ORMs support multiple data source connections. A configuration example for MikroORM:

const orm = await MikroORM.init({
  entities: [User, Post],
  dbName: 'main_db',
  type: 'postgresql',
  // Second data source
  replicas: [{
    name: 'read-replica',
    host: 'replica.db.example.com',
    user: 'readonly_user'
  }]
});

// Use the primary database for writes
const em = orm.em.fork();
em.persist(new User(...));

// Use the replica for queries
const readEm = orm.em.fork({ useReplica: true });
const users = await readEm.find(User, {});

In read-write separation configurations, all write operations are automatically routed to the primary database, while queries can be distributed across multiple replicas.

Combining Raw Queries with ORM

Complex queries may require a mix of ORM and raw SQL. An example combining Knex.js and Objection.js:

const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL
});

class Person extends Model {
  static get tableName() { return 'persons'; }

  static async findHighValueCustomers() {
    const sql = `
      SELECT p.*, SUM(o.total) as lifetime_value
      FROM persons p
      JOIN orders o ON o.customer_id = p.id
      GROUP BY p.id
      HAVING SUM(o.total) > ?
    `;
    return await knex.raw(sql, [10000]).then(res => res.rows);
  }
}

Example of calling a stored procedure:

const result = await knex.raw(
  'CARDINALITY(?)', 
  ['{1,2,3}']
).then(res => res.rows[0].cardinality);

Data Migration Management

Migration files should remain idempotent. A Sequelize migration example:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Products', {
      id: {
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
        primaryKey: true
      },
      name: {
        type: Sequelize.STRING(100),
        allowNull: false,
        unique: true
      },
      price: {
        type: Sequelize.DECIMAL(10,2),
        validate: { min: 0 }
      },
      createdAt: {
        type: Sequelize.DATE,
        defaultValue: Sequelize.NOW
      }
    });

    await queryInterface.addIndex('Products', ['name'], {
      name: 'IDX_PRODUCT_NAME'
    });
  },

  down: async (queryInterface) => {
    await queryInterface.dropTable('Products');
  }
};

Rollback operations should completely undo the changes made in up. Field modifications require separate migrations:

await queryInterface.changeColumn('Products', 'price', {
  type: Sequelize.DECIMAL(12,2),
  comment: 'Supports higher amounts'
});

Connection Health Checks

Active health monitoring mechanisms need to be implemented. A TypeORM health check in NestJS:

import { HealthCheckService, TypeOrmHealthIndicator } from '@nestjs/terminus';

@Controller('health')
export class HealthController {
  constructor(
    private health: HealthCheckService,
    private db: TypeOrmHealthIndicator
  ) {}

  @Get()
  check() {
    return this.health.check([
      () => this.db.pingCheck('database', { timeout: 300 })
    ]);
  }
}

Custom check strategy example:

const checkConnection = async () => {
  try {
    const conn = await pool.getConnection();
    await conn.ping();
    conn.release();
    return true;
  } catch (err) {
    return false;
  }
};

setInterval(async () => {
  if (!await checkConnection()) {
    alertAdmin('DB connection lost');
  }
}, 60000);

Retry Strategies for Connection Failures

Exponential backoff algorithm implementation:

const retryConnect = async (fn, retries = 3, delay = 1000) => {
  try {
    return await fn();
  } catch (err) {
    if (retries <= 0) throw err;
    await new Promise(res => setTimeout(res, delay));
    return retryConnect(fn, retries - 1, delay * 2);
  }
};

await retryConnect(() => mongoose.connect(uri));

Connection state event handling:

sequelize.addHook('afterDisconnect', async () => {
  console.warn('Disconnected from DB, attempting reconnect...');
  await sequelize.authenticate();
});