MySQL database connection and operations

MySQL Database Connection and Operations

Koa2 is a lightweight web framework based on Node.js, which provides powerful asynchronous flow control capabilities through middleware mechanisms. In practical development, database operations are one of the core functionalities of backend services, and MySQL, as one of the most popular relational databases, is commonly used in combination with Koa2.

Installing the MySQL Driver

To use MySQL in a Koa2 project, you first need to install the mysql2 package, a high-performance MySQL client for Node.js:

npm install mysql2 --save

Compared to the mysql package, mysql2 offers better performance and Promise support, which is crucial for Koa2's asynchronous features.

Creating a Database Connection

First, create a database connection pool, which is the recommended approach for production environments:

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

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

Connection pool configuration details:

• connectionLimit: Maximum number of connections in the pool
• queueLimit: Maximum number of queued requests when no connections are available
• waitForConnections: Whether to wait when the pool reaches its limit

Basic Query Operations

Executing Simple Queries

async function getUsers() {
  const [rows] = await pool.query('SELECT * FROM users');
  return rows;
}

Parameterized Queries

Use prepared statements to prevent SQL injection:

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

Transaction Handling

Handling transactions in Koa2 requires special attention to asynchronous flow:

async function transferMoney(fromId, toId, amount) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    
    // Deduct amount
    await conn.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
    
    // Deposit amount
    await conn.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
    
    await conn.commit();
    return true;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

Integration with Koa2 Middleware

Encapsulate the database connection as Koa2 middleware:

app.use(async (ctx, next) => {
  ctx.db = pool;
  await next();
});

// Usage in routes
router.get('/users', async (ctx) => {
  const [users] = await ctx.db.query('SELECT * FROM users');
  ctx.body = users;
});

Advanced Query Techniques

Pagination Queries

async function getUsersWithPagination(page = 1, pageSize = 10) {
  const offset = (page - 1) * pageSize;
  const [rows] = await pool.query(
    'SELECT * FROM users LIMIT ? OFFSET ?', 
    [pageSize, offset]
  );
  return rows;
}

Join Queries

async function getUserWithPosts(userId) {
  const [rows] = await pool.query(`
    SELECT u.*, p.title, p.content 
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    WHERE u.id = ?
  `, [userId]);
  
  return rows;
}

Performance Optimization

Using Connection Pool Statistics

// Get connection pool status
console.log(pool.pool.config.connectionLimit);
console.log(pool.pool._freeConnections.length);
console.log(pool.pool._allConnections.length);

Batch Insertion

async function batchInsertUsers(users) {
  const values = users.map(user => [user.name, user.email]);
  await pool.query(
    'INSERT INTO users (name, email) VALUES ?',
    [values]
  );
}

Error Handling

Centralized database error handling in Koa2:

app.use(async (ctx, next) => {
  try {
    await next();
  } catch (err) {
    if (err.code === 'ER_DUP_ENTRY') {
      ctx.status = 409;
      ctx.body = { error: 'Duplicate entry' };
    } else if (err.code === 'ER_NO_REFERENCED_ROW_2') {
      ctx.status = 400;
      ctx.body = { error: 'Foreign key constraint fails' };
    } else {
      ctx.status = 500;
      ctx.body = { error: 'Database error' };
    }
  }
});

Using ORM Instead of Raw Queries

While raw SQL queries are flexible, consider using an ORM like Sequelize for large projects:

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

const sequelize = new Sequelize('koa_demo', 'root', 'yourpassword', {
  host: 'localhost',
  dialect: 'mysql'
});

// Define model
const User = sequelize.define('User', {
  name: { type: Sequelize.STRING },
  email: { type: Sequelize.STRING }
});

// Usage in Koa2
router.get('/users', async (ctx) => {
  const users = await User.findAll();
  ctx.body = users;
});

Database Migrations

For production environments, use migration tools to manage database schema changes:

npm install db-migrate mysql -g

Create a migration file:

db-migrate create add-users-table

Then edit the generated migration file:

exports.up = function(db) {
  return db.createTable('users', {
    id: { type: 'int', primaryKey: true, autoIncrement: true },
    name: 'string',
    email: { type: 'string', unique: true }
  });
};

exports.down = function(db) {
  return db.dropTable('users');
};

Monitoring and Logging

Add query logs for debugging SQL:

const pool = mysql.createPool({
  // ...other configurations
  enableKeepAlive: true,
  debug: process.env.NODE_ENV === 'development'
});

// Listen to query events
pool.on('connection', (connection) => {
  console.log('New connection established');
});

pool.on('acquire', (connection) => {
  console.log('Connection acquired');
});

Connection Pool Tuning

Adjust connection pool parameters based on application load:

const pool = mysql.createPool({
  // ...other configurations
  connectionLimit: process.env.DB_POOL_SIZE || 10,
  idleTimeout: 60000, // Idle connection timeout (ms)
  maxIdle: 5 // Maximum number of idle connections
});

Security Considerations

1. Never directly concatenate user input into SQL
2. Configure database users with the principle of least privilege
3. Regularly back up the database
4. Encrypt sensitive data storage

// Unsafe approach
const unsafeQuery = `SELECT * FROM users WHERE name = '${ctx.query.name}'`;

// Safe approach
const safeQuery = 'SELECT * FROM users WHERE name = ?';
const [rows] = await pool.query(safeQuery, [ctx.query.name]);

Testing Database Operations

Use Jest to test database-related code:

describe('User Model', () => {
  beforeAll(async () => {
    await pool.query('CREATE TABLE IF NOT EXISTS test_users LIKE users');
  });

  afterAll(async () => {
    await pool.query('DROP TABLE test_users');
    await pool.end();
  });

  test('should create a user', async () => {
    await pool.query('INSERT INTO test_users (name, email) VALUES (?, ?)', ['Test', 'test@example.com']);
    const [rows] = await pool.query('SELECT * FROM test_users WHERE email = ?', ['test@example.com']);
    expect(rows.length).toBe(1);
  });
});

Production Environment Practices

1. Use environment variables to manage database configurations:

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'koa_demo'
});

2. Implement a database health check endpoint:

router.get('/health', async (ctx) => {
  try {
    await pool.query('SELECT 1');
    ctx.body = { status: 'OK' };
  } catch (err) {
    ctx.status = 503;
    ctx.body = { status: 'Database unavailable' };
  }
});

Common Issue Resolution

Connection Timeout Issues

const pool = mysql.createPool({
  // ...other configurations
  connectTimeout: 10000, // 10-second connection timeout
  acquireTimeout: 10000 // 10-second connection acquisition timeout
});

Handling Disconnections

pool.on('error', (err) => {
  if (err.code === 'PROTOCOL_CONNECTION_LOST') {
    console.error('Database connection was closed.');
  } else if (err.code === 'ER_CON_COUNT_ERROR') {
    console.error('Database has too many connections.');
  } else if (err.code === 'ECONNREFUSED') {
    console.error('Database connection was refused.');
  }
});

Performance Benchmarking

Use the benchmark module to test query performance:

const Benchmark = require('benchmark');
const suite = new Benchmark.Suite;

suite.add('Simple query', {
  defer: true,
  fn: function(deferred) {
    pool.query('SELECT 1 + 1 AS solution')
      .then(() => deferred.resolve());
  }
})
.on('cycle', function(event) {
  console.log(String(event.target));
})
.run({ 'async': true });

Database Design Recommendations

1. Add indexes to frequently queried fields
2. Avoid using SELECT *
3. Design table relationships properly
4. Consider using stored procedures for complex logic

// Example of creating an index
await pool.query('CREATE INDEX idx_user_email ON users(email)');

// Calling a stored procedure
await pool.query('CALL sp_get_user_posts(?)', [userId]);

Connecting to Multiple Databases

Sometimes you need to connect to multiple MySQL instances:

const mainPool = mysql.createPool({/* Primary database configuration */});
const readPool = mysql.createPool({/* Read-only database configuration */});

// Select connection pool based on operation type
async function query(sql, params, isReadOnly = false) {
  const pool = isReadOnly ? readPool : mainPool;
  return pool.query(sql, params);
}

Using Connection Pool Events for Monitoring

pool.on('enqueue', () => {
  console.log('Waiting for available connection slot');
});

pool.on('release', (connection) => {
  console.log('Connection %d released', connection.threadId);
});

Database Version Compatibility

Handling differences between MySQL versions:

const mysqlVersion = await pool.query('SELECT VERSION() AS version');
console.log(`MySQL version: ${mysqlVersion[0][0].version}`);

// Execute different SQL based on version
if (mysqlVersion[0][0].version.startsWith('5.7')) {
  // MySQL 5.7 specific syntax
} else if (mysqlVersion[0][0].version.startsWith('8.0')) {
  // MySQL 8.0 specific syntax
}