Absolutely! You can absolutely combine async/await with mysqljs/mysql's connection pool to handle your exact use case: holding a single connection for sequential write queries (with foreign key constraints) while letting the pool serve other connections for parallel read operations. This is actually a clean, efficient pattern—here's how to implement it:

Step 1: Promisify the pool and connection methods #

The mysqljs/mysql library uses callbacks by default, so we'll wrap the key methods with promises to work seamlessly with async/await. We'll use Node.js's built-in util.promisify for this:

const mysql = require('mysql');
const util = require('util');

// Initialize your connection pool with your database credentials
const pool = mysql.createPool({
  host: 'your-db-host',
  user: 'your-db-user',
  password: 'your-db-password',
  database: 'your-db-name',
  connectionLimit: 10 // Adjust based on your database's max connections and workload
});

// Promisify pool.getConnection() to use with async/await
pool.getConnection = util.promisify(pool.getConnection);

Step 2: Create a method to get a dedicated connection for sequential writes #

We'll make an async function that fetches a connection from the pool—you can hold onto this connection for your sequential writes, then release it back to the pool when done:

async function getDedicatedConnection() {
  try {
    const connection = await pool.getConnection();
    // Optional: If your sequential writes need atomicity (critical for foreign key constraints), start a transaction here
    // connection.beginTransaction = util.promisify(connection.beginTransaction).bind(connection);
    // await connection.beginTransaction();
    return connection;
  } catch (err) {
    console.error('Failed to retrieve connection from pool:', err);
    throw err; // Let the caller handle the error
  }
}

Step 3: Use the dedicated connection for sequential writes #

With your connection in hand, you can run your ordered write queries (with foreign key dependencies) using async/await to ensure they execute in sequence. Don't forget to release the connection in a finally block to avoid leaks:

async function executeSequentialWrites() {
  let connection;
  try {
    connection = await getDedicatedConnection();
    // Promisify the connection's query method for async/await
    const runQuery = util.promisify(connection.query).bind(connection);

    // 1. First write: Insert parent record (required for child's foreign key)
    await runQuery('INSERT INTO parent_table (name) VALUES (?)', ['New Parent']);
    const parentId = connection.insertId; // Grab the auto-generated ID

    // 2. Second write: Insert child record using the parent ID
    await runQuery('INSERT INTO child_table (parent_id, description) VALUES (?, ?)', [parentId, 'Child linked to parent']);

    // Optional: If using transactions, commit here
    // connection.commit = util.promisify(connection.commit).bind(connection);
    // await connection.commit();

    console.log('Sequential writes completed successfully');
  } catch (err) {
    console.error('Error during sequential writes:', err);
    // Optional: If using transactions, rollback on failure
    // if (connection) {
    //   connection.rollback = util.promisify(connection.rollback).bind(connection);
    //   await connection.rollback();
    // }
    throw err;
  } finally {
    // Always release the connection back to the pool, regardless of success/failure
    if (connection) connection.release();
  }
}

Step 4: Run parallel reads using the pool directly #

For read operations, you don't need to hold a dedicated connection—just use the pool's query method (promisified), which automatically fetches and releases a connection for each query. Multiple read calls will use separate connections from the pool, allowing them to run in parallel:

async function executeParallelReads() {
  const poolQuery = util.promisify(pool.query).bind(pool);

  // Run multiple read queries in parallel with Promise.all()
  const [userData, productData, orderCount] = await Promise.all([
    poolQuery('SELECT * FROM users LIMIT 20'),
    poolQuery('SELECT * FROM products WHERE in_stock = 1'),
    poolQuery('SELECT COUNT(*) AS total FROM orders')
  ]);

  console.log('Parallel reads done:', {
    users: userData.length,
    inStockProducts: productData.length,
    totalOrders: orderCount[0].total
  });
}

Step 5: Run both operations concurrently #

You can execute the sequential writes and parallel reads at the same time—since the write operation holds only one connection, the pool has remaining connections available for the parallel reads:

async function main() {
  try {
    // Run both tasks in parallel
    await Promise.all([executeSequentialWrites(), executeParallelReads()]);
    console.log('All database operations completed');
  } catch (err) {
    console.error('Top-level error:', err);
  } finally {
    // Close the pool when your application is ready to exit
    pool.end();
  }
}

main();

Key Notes #

• Connection Management: Always release connections in a finally block—failing to do this will eventually exhaust your pool.
• Transactions: For foreign key-related writes, using transactions is highly recommended to ensure atomicity (if one write fails, none are persisted).
• Pool Size: Set connectionLimit to a value that balances your parallel workload needs and your database's maximum allowed connections.
• Reusability: You can wrap promisified query methods into utility functions to avoid repeating code across your application.

内容的提问来源于stack exchange，提问作者Schmalitz