Building JSON with Node.js with multiple queries

JSON

Node.js

multiple queries

data processing

JavaScript

Building JSON with Node.js with multiple queries

Interview Questions practice on Codemia

Over 8,000 real interview questions from top companies, searchable by company and role.

Browse interview questions

JavaScript & Web

Introduction

A common backend task is fetching data from multiple database tables and combining the results into a single JSON response. In Node.js, doing this correctly means understanding asynchronous control flow, because each database query is a non-blocking operation. Getting this wrong leads to callback hell, race conditions, or incomplete responses sent before all queries finish.

The Callback Hell Problem

Before Promises became standard, Node.js developers nested callbacks to run sequential queries. This approach works but quickly becomes unreadable and error-prone:

javascript

1app.get('/api/dashboard', (req, res) => {
2  db.query('SELECT * FROM users WHERE id = ?', [req.params.id], (err, users) => {
3    if (err) return res.status(500).json({ error: err.message });
4    db.query('SELECT * FROM orders WHERE user_id = ?', [req.params.id], (err, orders) => {
5      if (err) return res.status(500).json({ error: err.message });
6      db.query('SELECT * FROM notifications WHERE user_id = ?', [req.params.id], (err, notifications) => {
7        if (err) return res.status(500).json({ error: err.message });
8        res.json({ user: users[0], orders, notifications });
9      });
10    });
11  });
12});

Each level of nesting adds indentation and makes error handling repetitive. Worse, these queries run sequentially even though they do not depend on each other, wasting time.

Using Promise.all with async/await

The modern approach wraps each query in a Promise and runs independent queries in parallel with Promise.all. This is both faster and more readable:

javascript

1// Helper to promisify a callback-based query function
2function queryAsync(sql, params) {
3  return new Promise((resolve, reject) => {
4    db.query(sql, params, (err, results) => {
5      if (err) reject(err);
6      else resolve(results);
7    });
8  });
9}
10
11app.get('/api/dashboard/:id', async (req, res) => {
12  try {
13    const userId = req.params.id;
14
15    const [user, orders, notifications] = await Promise.all([
16      queryAsync('SELECT * FROM users WHERE id = ?', [userId]),
17      queryAsync('SELECT * FROM orders WHERE user_id = ?', [userId]),
18      queryAsync('SELECT * FROM notifications WHERE user_id = ?', [userId]),
19    ]);
20
21    res.json({
22      user: user[0],
23      orders,
24      notifications,
25    });
26  } catch (err) {
27    res.status(500).json({ error: err.message });
28  }
29});

All three queries start at the same time. The total wait time equals the slowest query rather than the sum of all three. The destructured result array keeps the code flat and easy to follow.

Combining Query Results Into a Response Object

When building complex JSON responses, you often need to reshape raw query results. Keep the transformation logic separate from the querying logic:

javascript

1function buildDashboardResponse(user, orders, notifications) {
2  return {
3    user: {
4      id: user.id,
5      name: user.name,
6      email: user.email,
7    },
8    orderSummary: {
9      total: orders.length,
10      totalRevenue: orders.reduce((sum, o) => sum + o.amount, 0),
11      recent: orders.slice(0, 5),
12    },
13    unreadNotifications: notifications.filter(n => !n.read_at),
14  };
15}
16
17app.get('/api/dashboard/:id', async (req, res) => {
18  try {
19    const userId = req.params.id;
20    const [users, orders, notifications] = await Promise.all([
21      queryAsync('SELECT * FROM users WHERE id = ?', [userId]),
22      queryAsync('SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC', [userId]),
23      queryAsync('SELECT * FROM notifications WHERE user_id = ? ORDER BY created_at DESC', [userId]),
24    ]);
25
26    if (users.length === 0) {
27      return res.status(404).json({ error: 'User not found' });
28    }
29
30    res.json(buildDashboardResponse(users[0], orders, notifications));
31  } catch (err) {
32    res.status(500).json({ error: err.message });
33  }
34});

This separation makes the response structure easy to test independently from the database layer.

Error Handling for Partial Failures

Promise.all rejects as soon as any single promise rejects, which means one failed query aborts the entire response. If some data is optional and you want to return partial results, use Promise.allSettled instead:

javascript

1app.get('/api/dashboard/:id', async (req, res) => {
2  const userId = req.params.id;
3
4  const results = await Promise.allSettled([
5    queryAsync('SELECT * FROM users WHERE id = ?', [userId]),
6    queryAsync('SELECT * FROM orders WHERE user_id = ?', [userId]),
7    queryAsync('SELECT * FROM recommendations WHERE user_id = ?', [userId]),
8  ]);
9
10  const [userResult, ordersResult, recsResult] = results;
11
12  if (userResult.status === 'rejected') {
13    return res.status(500).json({ error: 'Failed to load user data' });
14  }
15
16  res.json({
17    user: userResult.value[0],
18    orders: ordersResult.status === 'fulfilled' ? ordersResult.value : [],
19    recommendations: recsResult.status === 'fulfilled' ? recsResult.value : [],
20    warnings: results
21      .filter(r => r.status === 'rejected')
22      .map(r => r.reason.message),
23  });
24});

This way, the user and orders load even if the recommendations query fails. The warnings array tells the client which parts are missing.

Using Transactions for Dependent Queries

When queries depend on each other, or when you need read-consistency across multiple tables, wrap them in a database transaction:

javascript

1async function getDashboardWithTransaction(userId) {
2  const connection = await pool.getConnection();
3  try {
4    await connection.beginTransaction();
5
6    const [users] = await connection.query(
7      'SELECT * FROM users WHERE id = ?', [userId]
8    );
9    const [orders] = await connection.query(
10      'SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC', [userId]
11    );
12    const [balance] = await connection.query(
13      'SELECT SUM(amount) as total FROM payments WHERE user_id = ?', [userId]
14    );
15
16    await connection.commit();
17    return { user: users[0], orders, balance: balance[0].total };
18  } catch (err) {
19    await connection.rollback();
20    throw err;
21  } finally {
22    connection.release();
23  }
24}

Transactions ensure you get a consistent snapshot. Without them, a payment could be recorded between the orders query and the balance query, producing inconsistent data.

Practical Example With Express and mysql2

Here is a complete working example using Express and the mysql2/promise driver:

javascript

1const express = require('express');
2const mysql = require('mysql2/promise');
3
4const pool = mysql.createPool({
5  host: 'localhost',
6  user: 'root',
7  password: 'secret',
8  database: 'myapp',
9  waitForConnections: true,
10  connectionLimit: 10,
11});
12
13const app = express();
14
15app.get('/api/users/:id/profile', async (req, res) => {
16  try {
17    const userId = req.params.id;
18
19    const [[users], [posts], [followers]] = await Promise.all([
20      pool.query('SELECT id, name, email FROM users WHERE id = ?', [userId]),
21      pool.query(
22        'SELECT id, title, created_at FROM posts WHERE author_id = ? ORDER BY created_at DESC LIMIT 10',
23        [userId]
24      ),
25      pool.query('SELECT COUNT(*) as count FROM follows WHERE followed_id = ?', [userId]),
26    ]);
27
28    if (users.length === 0) {
29      return res.status(404).json({ error: 'User not found' });
30    }
31
32    res.json({
33      user: users[0],
34      recentPosts: posts,
35      followerCount: followers[0].count,
36    });
37  } catch (err) {
38    console.error('Profile fetch error:', err);
39    res.status(500).json({ error: 'Internal server error' });
40  }
41});
42
43app.listen(3000, () => console.log('Server running on port 3000'));

Common Pitfalls

Running independent queries sequentially with await: If queries do not depend on each other, always use Promise.all to run them in parallel. Sequential awaits waste time equal to the sum of all query durations.
Forgetting to release database connections: When using connection pools with manual getConnection(), always call connection.release() in a finally block. Leaked connections exhaust the pool and freeze your server.
Sending the response before all queries complete: Without await or proper callback coordination, res.json() can execute before query results are available, sending undefined or empty data.
Not handling null or empty results: A query might return zero rows. Always check for empty arrays before accessing index 0, or the response will contain undefined fields.
Exposing raw database errors to clients: Catch errors and return a generic message. Sending the raw error object can leak table names, column names, and query details to attackers.

Summary

Use Promise.all with async/await to run independent database queries in parallel and combine results into a single JSON response.
Separate data fetching from response shaping to keep code testable and maintainable.
Use Promise.allSettled when some queries are optional and partial responses are acceptable.
Wrap dependent or consistency-critical queries in a database transaction.
Always handle errors gracefully, release connections in finally blocks, and never expose raw database errors to clients.

Tackling System Design Interview Problems

A short course that equips you with the skills to approach system design interviews methodically.

Start the free course

Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

Interview Questions practice on Codemia

Over 8,000 real interview questions from top companies, searchable by company and role.

Browse interview questions

Building JSON with Node.js with multiple queries

Interview Questions practice on Codemia

Introduction

The Callback Hell Problem

Using Promise.all with async/await

Combining Query Results Into a Response Object

Error Handling for Partial Failures

Using Transactions for Dependent Queries

Practical Example With Express and mysql2

Common Pitfalls

Summary

Related reading

Tackling System Design Interview Problems

Track what you have practised

Interview Questions practice on Codemia