How to Use MySQL Database with Node.js

I still remember the first time I connected a Node.js app to MySQL. I had built plenty of REST APIs before, but wiring up a real relational database instead of a mock JSON file felt like a rite of passage. Over the years I’ve used MySQL with Node.js in production systems that handled everything from e-commerce orders to analytics dashboards, and in this guide I want to walk you through everything I’ve learned — from the absolute basics to the kind of optimization tricks that only show up after you’ve been paged at 2 AM because a query started timing out.

This is a long one, so grab a coffee. I’m going to take you from “what even is a database driver” to connection pooling, transactions, indexing strategy, and security hardening.

Why MySQL + Node.js Is Still a Popular Combo

Node.js is asynchronous and event-driven by nature, and MySQL is a mature, battle-tested relational database. When I need strong consistency, well-defined schemas, and the ability to run complex joins and aggregations, I reach for MySQL over a NoSQL option. Node’s non-blocking I/O model pairs nicely with MySQL’s client libraries, which are built around callbacks, promises, and async/await.

In my experience, this combination shines when:

• You need ACID-compliant transactions (payments, inventory, bookings)
• Your data has clear relationships (users, orders, products)
• You want a huge ecosystem of ORMs, migration tools, and hosting options
• Your team already knows SQL

MySQL Architecture Fundamentals (You Need This Before Writing Any Code)

Before touching Node.js, I always make sure I understand what’s happening on the MySQL side, because half of the “why is my query slow” questions I get from junior developers trace back to not understanding the architecture.

graph TD
    A[Client Application - Node.js] -->|SQL over TCP/Socket| B[Connection Layer]
    B --> C[SQL Layer - Parser, Optimizer, Cache]
    C --> D[Storage Engine Layer]
    D --> E[InnoDB]
    D --> F[MyISAM]
    D --> G[Memory Engine]
    E --> H[(Disk / Tablespace)]
    F --> H
    G --> I[(RAM)]

MySQL is layered:

1. Connection layer — handles authentication, thread management, and connection pooling on the server side.
2. SQL layer — the parser turns your query into a parse tree, the optimizer picks an execution plan, and the query cache (deprecated since MySQL 8.0) used to store results.
3. Storage engine layer — this is where the actual reading and writing happens. InnoDB is the default and the one I use for nearly everything because it supports transactions, row-level locking, and foreign keys.

Understanding this layering matters because when I tune performance from Node.js, I’m really tuning how efficiently my queries move through these layers.

Setting Up MySQL

I’ll assume you already have MySQL installed. If not, on Ubuntu I’d run:

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

Then I log in and create a database and a dedicated user (never use root in application code):

CREATE DATABASE shop_db;
CREATE USER 'shop_app'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_app'@'%';
FLUSH PRIVILEGES;

Choosing a Node.js MySQL Driver

There are two libraries I reach for depending on the project:

For this guide, I’ll focus on mysql2 because understanding raw SQL interaction is the foundation everything else builds on. If you jump straight to an ORM without knowing what’s happening underneath, you’ll struggle the moment something goes wrong.

Installing it:

npm install mysql2

Establishing a Basic Connection

Here’s the simplest possible connection using promises:

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

async function main() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'shop_app',
    password: 'StrongPassword123!',
    database: 'shop_db'
  });

  const [rows, fields] = await connection.query('SELECT NOW() AS currentTime');
  console.log(rows);

  await connection.end();
}

main().catch(console.error);

Output looks something like:

[ { currentTime: 2026-07-30T09:12:44.000Z } ]

This works, but I never use a single raw connection in a real application. Every request creating and destroying a TCP connection to MySQL is expensive and doesn’t scale.

Connection Pooling — The Thing Every Production App Needs

Instead, I always create a pool once, at application startup, and reuse it across every request.

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

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000
});

module.exports = pool;

Here’s what each setting actually does, because I’ve seen these misconfigured constantly:

• connectionLimit — the maximum number of simultaneous connections the pool will open. I usually set this based on max_connections on the MySQL server divided across however many app instances I run.
• waitForConnections — if true, requests wait in a queue when the pool is exhausted instead of throwing immediately.
• queueLimit — caps how many queued requests are allowed before rejecting; 0 means unlimited (I set a real limit in production to avoid unbounded memory growth).

sequenceDiagram
    participant App as Node.js App
    participant Pool as Connection Pool
    participant DB as MySQL Server

    App->>Pool: request connection
    alt connection available
        Pool-->>App: hand over idle connection
    else pool exhausted
        Pool->>Pool: queue request
        Pool-->>App: wait until one frees up
    end
    App->>DB: execute query
    DB-->>App: return result set
    App->>Pool: release connection back

CRUD Operations

Create

const [result] = await pool.query(
  'INSERT INTO products (name, price, stock) VALUES (?, ?, ?)',
  ['Wireless Mouse', 19.99, 150]
);
console.log('Inserted ID:', result.insertId);

I always use parameterized queries (the ? placeholders) — never string concatenation. This is the single biggest security habit I enforce on every team I work with, because it prevents SQL injection entirely.

Read

const [products] = await pool.query(
  'SELECT id, name, price FROM products WHERE stock > ? ORDER BY price DESC LIMIT 10',
  [0]
);
console.table(products);

Update

const [result] = await pool.query(
  'UPDATE products SET stock = stock - ? WHERE id = ?',
  [1, 42]
);
console.log('Rows affected:', result.affectedRows);

Delete

await pool.query('DELETE FROM products WHERE id = ?', [42]);

Transactions — Where Node.js and MySQL Really Have to Cooperate

Whenever I have multiple related writes that must all succeed or all fail together — like deducting stock and creating an order row — I wrap them in a transaction. I don’t use pool.query for this; I grab a dedicated connection.

async function placeOrder(productId, quantity, userId) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    const [[product]] = await connection.query(
      'SELECT stock, price FROM products WHERE id = ? FOR UPDATE',
      [productId]
    );

    if (!product || product.stock < quantity) {
      throw new Error('Insufficient stock');
    }

    await connection.query(
      'UPDATE products SET stock = stock - ? WHERE id = ?',
      [quantity, productId]
    );

    const total = product.price * quantity;
    await connection.query(
      'INSERT INTO orders (user_id, product_id, quantity, total) VALUES (?, ?, ?, ?)',
      [userId, productId, quantity, total]
    );

    await connection.commit();
    return { success: true, total };
  } catch (err) {
    await connection.rollback();
    throw err;
  } finally {
    connection.release();
  }
}

); const total = product.price * quantity; await connection.query( ‘INSERT INTO orders (user_id, product_id, quantity, total) VALUES (?, ?, ?, ?)’, [userId, productId, quantity, total] ); await connection.commit(); return { success: true, total }; } catch (err) { await connection.rollback(); throw err; } finally { connection.release(); } }

Notice the FOR UPDATE clause. This places a row-level lock on that product row until the transaction commits or rolls back, preventing two simultaneous orders from overselling the same stock. This is InnoDB’s row-level locking in action — one of the biggest reasons I default to InnoDB over MyISAM, which only supports table-level locks.

Indexing From the Node.js Application’s Perspective

Indexes live in MySQL, but the queries I write in Node.js determine whether they get used. A few things I always check:

CREATE INDEX idx_products_stock_price ON products (stock, price);

Then I verify MySQL is actually using it:

EXPLAIN SELECT id, name, price FROM products WHERE stock > 0 ORDER BY price DESC LIMIT 10;

If I see type: ALL in the output, that’s a full table scan — a red flag. I want to see range, ref, or index instead.

Handling Errors Properly

I never let raw MySQL errors leak to API responses. I wrap query calls and translate error codes:

try {
  await pool.query('INSERT INTO users (email) VALUES (?)', [email]);
} catch (err) {
  if (err.code === 'ER_DUP_ENTRY') {
    throw new Error('Email already registered');
  }
  console.error('Database error:', err.message);
  throw new Error('Something went wrong, please try again');
}

Security Best Practices I Follow

• Never trust user input — always use parameterized queries.
• Least privilege accounts — my app user gets only the grants it needs, never ALL PRIVILEGES.
• Environment variables for credentials — I keep DB credentials out of source control using .env and a secrets manager in production.
• TLS connections — I enable ssl options in the pool config when connecting to a remote MySQL instance, especially on managed cloud databases.
• Rate limit and validate at the application layer before queries ever reach the database.

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: { rejectUnauthorized: true }
});

Performance and Optimization Tips

1. Use connection pooling — never open a raw connection per request.
2. Select only the columns you need — I avoid SELECT * in production code because it pulls unnecessary data over the wire and can prevent covering indexes from being used.
3. Batch inserts when writing many rows at once:

await pool.query(
  'INSERT INTO logs (message, level) VALUES ?',
  [[['Server started', 'info'], ['Cache warmed', 'info']]]
);

4. Use EXPLAIN religiously on any query that touches a table with more than a few thousand rows.
5. Paginate with keyset pagination instead of large OFFSET values for big tables — offset pagination gets slower as the offset grows because MySQL still has to scan and discard those rows.

SELECT id, name FROM products WHERE id > 1000 ORDER BY id LIMIT 20;

6. Monitor slow queries using the slow query log, and correlate them with the exact query text your Node.js code generates.

Real-World Scenario: A Checkout Service

In one project, our checkout service handled roughly 200 orders per minute at peak. We used a pool with connectionLimit: 20 across four app instances, wrapped every checkout in a transaction with row locks on inventory, and used a read replica for the product catalog browsing endpoints so writes never contended with heavy read traffic. That separation between read and write paths made a measurable difference once we crossed a few hundred concurrent users.

Troubleshooting Common Issues

Frequently Asked Questions

Should I use an ORM like Sequelize instead of raw SQL? It depends on the team. I like ORMs for fast CRUD scaffolding and migrations, but I still drop down to raw SQL for complex reporting queries where the ORM’s generated SQL isn’t efficient.

Is mysql2 better than the older mysql package? Yes — mysql2 supports Promises natively, prepared statements, and is actively maintained. I don’t start new projects with the original mysql package anymore.

How many connections should my pool have? Start conservative (5–10) and scale based on load testing. Too many connections can overwhelm MySQL’s own connection limits across multiple app instances.

Can Node.js handle concurrent MySQL writes safely? Yes, through transactions and row-level locking in InnoDB, as shown in the order example above.

Interview Questions on This Topic

1. Why should you use parameterized queries instead of string concatenation in Node.js MySQL code?
2. Explain the difference between pool.query() and using pool.getConnection() for a transaction.
3. What does FOR UPDATE do, and why is it important in a checkout flow?
4. How does connection pooling improve performance compared to opening a new connection per request?
5. What’s the difference between InnoDB and MyISAM, and why does it matter for transactional applications?

Key Takeaways

• Always use a connection pool, never a single raw connection, in production Node.js apps.
• Parameterized queries are non-negotiable for security.
• Transactions with row-level locking keep multi-step writes consistent under concurrency.
• Index your queries deliberately and verify with EXPLAIN.
• Separate read-heavy and write-heavy workloads when you scale.

References

• MySQL 8.0 Reference Manual: https://dev.mysql.com/doc/refman/8.0/en/
• mysql2 npm package documentation: https://www.npmjs.com/package/mysql2
• MySQL InnoDB Storage Engine documentation: https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html