Kết nối ứng dụng Node.js với MySQL

Tình huống mở đầu

Dũng viết backend Node.js cho ShopBook và thấy cả ứng dụng chạy tốt suốt buổi sáng. Đến 3 giờ chiều, đúng lúc nhiều khách đặt hàng, mọi request bỗng đứng im. Không có lỗi trong log, CPU rảnh, MySQL không báo gì. Khởi động lại ứng dụng thì mọi thứ chạy lại bình thường, được khoảng hai tiếng rồi treo tiếp.

Hãy chọn trước một giả thuyết: một thứ gì đó đang cạn dần theo từng request, thứ gì? Cuối bài bạn sẽ chạy đúng tình huống này với một pool nhỏ và thấy chữ "treo" xuất hiện ra sao.

Ôn nhanh bài trước: một tài khoản chỉ có quyền trên database shopbook_vs gõ -D nope (database không tồn tại). Lỗi 1044 hay 1049?

Đáp án: 1044, vì MySQL không tiết lộ database có tồn tại hay không cho tài khoản không có quyền. Bài này nối MySQL từ code Node.js, nơi bạn gặp lại các mã lỗi đó, nhưng từ bên trong một chương trình chạy lâu.

Mục tiêu bài học

Sau bài này bạn có thể:

• Tạo pool bằng mysql2/promise, chạy truy vấn với pool.execute và đọc đúng kết quả [rows, fields].
• Viết một giao dịch chuyển tiền đúng khuôn: getConnection, beginTransaction, commit hoặc rollback, release trong finally.
• Dự đoán điều gì xảy ra khi pool hết kết nối và vì sao quên release làm ứng dụng treo.
• Nhận ra ba bẫy dữ liệu: quên bóc [rows], tham số undefined, và cột DECIMAL trả về chuỗi.

Bức tranh lớn

Xuyên suốt bài, MySQL là tòa nhà văn phòng và pool là quầy phát thẻ khách: quầy chỉ có 2 thẻ. Mỗi request lấy một thẻ, làm việc, rồi trả thẻ. Ai không trả thẻ thì người sau đứng chờ mãi. Dự án xuyên suốt là bảng accounts (ví tiền của khách ShopBook) với hai tài khoản mẫu.

Hình dưới trả lời một câu hỏi: một request đi qua những bước nào từ lúc xin kết nối đến lúc trả lại, nhất là khi có giao dịch?

Cách đọc hình: ô xanh dương là tạo pool. Từ đó có hai đường: pool.execute cho một câu lệnh đơn lẻ (pool tự mượn và trả), hoặc getConnection khi bạn cần giữ cùng một kết nối cho nhiều lệnh. Ô cam là câu hỏi mọi bước có thành công không. Nhánh Yes dẫn tới commit, nhánh No dẫn tới rollback, và cả hai đều kết thúc ở ô release. Màu chỉ hỗ trợ, chữ trong ô mới là nội dung chính.

Chỗ ẩn dụ không còn đúng: thẻ khách thật có thể hết hạn và tự thu hồi. Kết nối trong pool không tự trả, nó chỉ về khi bạn gọi release.

Khái niệm cốt lõi

Bài này có bốn khái niệm. Mọi đoạn code dưới đây chạy thật bằng Node.js với mysql2.

1. Pool

mysql.createPool({...}) tạo một nhóm kết nối dùng chung. connectionLimit là số kết nối tối đa. Với waitForConnections: true, request đến lúc pool hết chỗ sẽ xếp hàng chờ chứ không lỗi. Pool tạo kết nối khi có người xin, và pool.end() đóng tất cả khi tắt chương trình.

2. execute và kết quả

pool.execute(sql, [tham số]) chạy câu lệnh có ?, trả về một mảng hai phần tử [rows, fields]. Hầu như luôn viết const [rows] = await ... để lấy phần đầu. Với mọi câu SELECT, rows là mảng các object theo tên cột.

3. Giao dịch cần một kết nối cố định

pool.execute mượn và trả kết nối ngay sau mỗi lệnh, nên hai lệnh liên tiếp có thể chạy trên hai kết nối khác nhau. Một giao dịch phải chạy trên cùng một kết nối. Vì thế bạn dùng getConnection, giữ nó cho cả giao dịch, và bắt buộc release trong finally.

4. Kiểu dữ liệu mà driver trả về

Cột DECIMAL được trả về dưới dạng chuỗi để không mất độ chính xác. Số nguyên và COUNT trả về số. Tham số undefined bị từ chối, vì SQL không có undefined: muốn đưa NULL thì truyền null.

Ví dụ đầu tiên

Bảng accounts có hai tài khoản: hoa với 100.50 và minh với 20.00. Cột balance là DECIMAL(10,2) kèm ràng buộc CHECK (balance >= 0). Chương trình đọc host, cổng, tài khoản, mật khẩu và tên database từ biến môi trường như hai bài trước.

Ví dụ nhỏ nhất: đọc tài khoản số 1 và thử cộng thêm 50.

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  port: Number(process.env.MYSQL_PORT),   // biến môi trường luôn là chuỗi, cổng phải là số
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 2,
});

const [rows] = await pool.execute(
  "SELECT id, owner, balance FROM accounts WHERE id = ?",
  [1]
);
console.log(rows);
console.log(typeof rows[0].balance, rows[0].balance + 50);
await pool.end();

Dự đoán trước khi xem kết quả: typeof rows[0].balance là gì, và rows[0].balance + 50 ra bao nhiêu?

Kết quả [Đã chạy: Node.js 22.21.1, mysql2 3.24.6, MySQL 8.0.44]:

[ { id: 1, owner: 'hoa', balance: '100.50' } ]
string 100.5050

Cộng ra 100.5050 thay vì 150.5 là bài học đắt giá: số tiền sai nhưng không có lỗi nào. Hãy nhớ nó khi tính toán với cột DECIMAL.

Xem từng bước

Animation dưới đây chạy tình huống pool chỉ có 2 kết nối và ba request. Nút so sánh cho bạn đổi giữa việc A gọi release với A quên release. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Chương trình sau chạy đúng kịch bản của animation. Nó bắt A và B giữ hai kết nối, cho C xin kết nối thứ ba, chờ 1500 ms, rồi mới cho A gọi release.

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  port: Number(process.env.MYSQL_PORT),   // biến môi trường luôn là chuỗi, cổng phải là số
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 2,
});

const wait = (ms) => new Promise((resolve) => setTimeout(resolve, ms));
const a = await pool.getConnection();
const b = await pool.getConnection();
console.log("A and B hold both connections");

let got = false;
const third = pool.getConnection().then((c) => { got = true; return c; });
await wait(1500);
console.log("after 1500 ms the third request has a connection:", got);

a.release();
const c = await third;
console.log("after A.release() the third request has a connection:", got);
c.release();
b.release();
await pool.end();

Kết quả [Đã chạy: Node.js 22.21.1, mysql2 3.24.6, MySQL 8.0.44]:

A and B hold both connections
after 1500 ms the third request has a connection: false
after A.release() the third request has a connection: true

Bảng trace bằng chữ [Đã chạy: Node.js 22.21.1, mysql2 3.24.6, MySQL 8.0.44, kết quả của chương trình ngay trước]:

Bạn vừa làm được: dự đoán đúng thời điểm request thứ ba được phục vụ, và hiểu vì sao một lần quên release biến thành một request chờ mãi.

Concept Check

Câu 1. const rows = await pool.execute("SELECT id FROM accounts") (không có dấu [ ]). rows là gì?

• A. Mảng các hàng của bảng.
• B. Một cặp [hàng, trường], tức mảng hai phần tử.
• C. Một số bằng số hàng.
• D. undefined.

Câu 2. Pool có connectionLimit: 2, waitForConnections: true. Request thứ ba xin kết nối khi hai kết nối đang bận. Điều gì xảy ra?

• A. Pool ném lỗi ngay lập tức.
• B. Pool tạo kết nối thứ ba vượt giới hạn.
• C. Request thứ ba xếp hàng chờ cho tới khi có kết nối được release.
• D. Pool tự đóng kết nối cũ nhất để nhường chỗ.

Câu 3. Cột balance DECIMAL(10,2) có giá trị 100.50. Trong JavaScript, typeof của nó là gì?

• A. number
• B. string
• C. bigint
• D. object

Đáp án và giải thích:

• Câu 1: B. Kết quả của execute luôn là cặp [rows, fields], không bóc thì bạn giữ cả cặp. A xảy ra khi có [ ].
• Câu 2: C. Lần chạy thật cho thấy request thứ ba chờ cho tới khi A release. A, B và D không phải hành vi của pool với cấu hình này.
• Câu 3: B. Lần chạy thật in string. Driver giữ chuỗi để không mất độ chính xác của số thập phân.

Làm cùng mình

Bạn cần hàm transfer(from, to, amount) chuyển tiền giữa hai tài khoản: hoặc cả hai lệnh UPDATE thành công, hoặc không lệnh nào có hiệu lực.

Bước 1. Vẽ ra giấy

Vẽ trình tự: mượn kết nối, bắt đầu giao dịch, hai lệnh UPDATE, rồi hai nhánh commit hoặc rollback, cuối cùng release. Đánh dấu chỗ nào bạn dự đoán dễ quên.

Bước 2. Điền chỗ trống

async function transfer(from, to, amount) {
  const conn = await pool.getConnection();
  try {
    // TODO 1: bắt đầu giao dịch trên kết nối này
    await conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, to]);
    await conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, from]);
    // TODO 2: chốt giao dịch
    return "committed";
  } catch (e) {
    // TODO 3: hủy giao dịch
    return "rolled back: " + e.code;
  } finally {
    // TODO 4: trả kết nối cho pool
  }
}

Gợi ý: bốn hàm của kết nối: beginTransaction, commit, rollback, release.

Bước 3. Chạy và so sánh

Dự đoán trước: transfer(1, 2, 30) và transfer(1, 2, 500) trả về gì, và số dư cuối của hai tài khoản là bao nhiêu? Đối chiếu với mục Lời giải và đáp án.

Bước 4. Vẽ lại ra giấy

Ghi một câu: nếu quên rollback trong nhánh catch nhưng vẫn gọi release, kết nối trả về pool đang mang trạng thái gì?

Thử phá nó đi

Chương trình dưới thử bốn lỗi nhỏ liên tiếp [Đã chạy: Node.js 22.21.1, mysql2 3.24.6, MySQL 8.0.44]:

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  port: Number(process.env.MYSQL_PORT),   // biến môi trường luôn là chuỗi, cổng phải là số
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 2,
});

const result = await pool.execute("SELECT id FROM accounts");
console.log("kept the pair [rows, fields]:", Array.isArray(result[0]), result.length);

try {
  await pool.execute("SELECT id FROM accounts WHERE id = ?", [undefined]);
} catch (e) {
  console.log(e.name + ": " + e.message);
}

const [[sum]] = await pool.execute("SELECT SUM(balance) AS total FROM accounts");
console.log(typeof sum.total, sum.total);

try {
  await pool.execute("SELECT id FROM no_such_table");
} catch (e) {
  console.log(e.code, e.errno, e.sqlState);
}
await pool.end();

kept the pair [rows, fields]: true 2
TypeError: Bind parameters must not contain undefined. To pass SQL NULL specify JS null
string 120.50
ER_NO_SUCH_TABLE 1146 42S02

Hình dưới gom các triệu chứng thành cây quyết định.

Cách đọc hình: ô xanh dương là triệu chứng chung, ô cam hỏi bạn thấy gì. Bốn ô đỏ là bốn hướng, mỗi hướng một cách sửa.

Lỗi thứ năm, quên release, đã chạy ở phần animation: không có thông báo nào, chỉ có sự chờ đợi.

Tự tay làm

Dùng chung bảng accounts hai dòng.

Cơ bản

Bài 1. Dự đoán giao dịch. Đầu vào: transfer(1, 2, 30) rồi transfer(1, 2, 500). Điền bảng gồm lệnh, kết quả trả về và số dư của hoa, minh sau mỗi lệnh. Tiêu chí đạt: đúng cả hai dòng và nêu mã lỗi của lệnh thứ hai. Gợi ý: ràng buộc CHECK (balance >= 0).

Vận dụng

Bài 2. Số thay vì chuỗi. Đầu vào: truy vấn đọc tài khoản số 1. Cho balance + 50 ra 150.5. Tiêu chí đạt: kết quả đúng và typeof là number. Gợi ý: một tùy chọn khi tạo pool.

Thử thách (tùy chọn)

Bài 3. Pool không cạn. Đầu vào: hàm badRead() mượn kết nối bằng getConnection, chạy một SELECT rồi quên release. Gọi nó ba lần với connectionLimit: 2, sau đó viết lại để gọi bao nhiêu lần cũng không treo. Tiêu chí đạt: bản sửa dùng finally. Gợi ý: hoặc dùng pool.execute thay cho getConnection khi chỉ có một lệnh.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài.

1. (Mục tiêu 1) pool.execute trả về gì, và vì sao người ta viết const [rows]?
2. (Mục tiêu 2, dự đoán) transfer(1, 2, 500) khi hoa chỉ có 70.50. Hàm trả về gì, và số dư thay đổi thế nào?
3. (Mục tiêu 2, giải thích cho bạn chưa học) Vì sao giao dịch không dùng được pool.execute cho từng lệnh?
4. (Mục tiêu 3) Vì sao quên release làm ứng dụng treo mà không có lỗi?
5. (Mục tiêu 4) balance + 50 ra 100.5050. Nguyên nhân và hai cách sửa?

Điều cần nhớ

• Pool là nhóm kết nối có giới hạn. Hết chỗ thì request xếp hàng, không lỗi.
• execute trả [rows, fields]. Giao dịch cần getConnection, commit hoặc rollback, và release trong finally.
• Quên release là rò rỉ kết nối: ứng dụng treo dần mà không có lỗi.
• DECIMAL về dạng chuỗi, undefined bị từ chối, null mới là NULL.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho ngày mai: suốt chương này bạn đã gõ CREATE USER và GRANT nhiều lần mà chưa học kỹ. Tài khoản shop_app@localhost và shop_app@% là hai tài khoản khác nhau hay cùng một tài khoản?

Đào sâu

Phần này dành cho bạn tò mò.

• Cơ chế bên trong. Trong mysql2, pool.execute dùng câu lệnh đã chuẩn bị (prepared statement) còn pool.query ghép giá trị vào câu SQL ở phía client. Bài này chỉ dùng execute. Cả hai nhận tham số dạng mảng cho dấu ?.
• So sánh với lựa chọn khác. Bài cũ dùng namedPlaceholders để viết :status. Tùy chọn đó có thật, nhưng bài này chưa chạy nên không mô tả thêm.
• Hiểu lầm nâng cao. conn.release() không đóng kết nối thật mà chỉ trả nó về pool để người khác dùng. Đóng thật là conn.destroy() hoặc pool.end().
• Giới hạn của bài. Bài chưa nói về queueLimit, thời gian chờ lấy kết nối, hay kết nối bị MySQL đóng vì không dùng (wait_timeout).
• Đọc thêm. Tài liệu mysql2: https://sidorares.github.io/node-mysql2/docs

Bài tiếp theo

Chương sau là Quản lý người dùng MySQL, bắt đầu với câu lệnh CREATE USER: nơi bạn học đầy đủ cái mà chương này mới dùng lướt. Mini-dự án nối tiếp: bộ tài khoản chuẩn cho ShopBook gồm tài khoản ứng dụng, tài khoản báo cáo chỉ đọc và tài khoản quản trị.

Lời giải và đáp án

Làm cùng mình, Bước 2. Bốn chỗ trống điền:

await conn.beginTransaction();
await conn.commit();
await conn.rollback();
conn.release();

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  port: Number(process.env.MYSQL_PORT),   // biến môi trường luôn là chuỗi, cổng phải là số
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 2,
});

async function transfer(from, to, amount) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    await conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, to]);
    await conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, from]);
    await conn.commit();
    return "committed";
  } catch (e) {
    await conn.rollback();
    return "rolled back: " + e.code;
  } finally {
    conn.release();   // luôn trả kết nối, dù thành công hay lỗi
  }
}

console.log(await transfer(1, 2, 30));
console.log(await transfer(1, 2, 500));
const [rows] = await pool.execute("SELECT id, owner, balance FROM accounts ORDER BY id");
console.log(rows);
await pool.end();

committed
rolled back: ER_CHECK_CONSTRAINT_VIOLATED
[
  { id: 1, owner: 'hoa', balance: '70.50' },
  { id: 2, owner: 'minh', balance: '50.00' }
]

Lần chuyển 500 vi phạm CHECK (balance >= 0) ở lệnh trừ tiền, nên hoa giữ nguyên số dư và minh cũng không nhận thêm tiền. Số dư cuối là 70.50 và 50.00 (hiện dạng chuỗi) như kết quả trên.

Bài 1.

Bài 2. Thêm decimalNumbers: true vào cấu hình pool [Đã chạy: Node.js 22.21.1, mysql2 3.24.6, MySQL 8.0.44]:

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  port: Number(process.env.MYSQL_PORT),   // biến môi trường luôn là chuỗi, cổng phải là số
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 2,
  decimalNumbers: true,
});

const [rows] = await pool.execute("SELECT id, balance FROM accounts WHERE id = ?", [1]);
console.log(rows, typeof rows[0].balance, rows[0].balance + 50);
await pool.end();

[ { id: 1, balance: 100.5 } ] number 150.5

Cũng có thể giữ nguyên cấu hình và dùng Number(rows[0].balance) + 50.

Bài 3. Bản sửa dùng try/finally để release luôn được gọi:

async function goodRead() {
  const conn = await pool.getConnection();
  try {
    const [rows] = await conn.execute("SELECT id FROM accounts");
    return rows;
  } finally {
    conn.release();
  }
}

Hoặc đơn giản hơn: const [rows] = await pool.execute(...), vì execute tự mượn và trả.

Tự kiểm tra.

1. Một cặp [rows, fields]. Bóc [rows] để lấy ngay danh sách hàng.
2. Trả rolled back: ER_CHECK_CONSTRAINT_VIOLATED, số dư không đổi.
3. Vì execute trả kết nối ngay sau mỗi lệnh, hai lệnh có thể chạy trên hai kết nối, và giao dịch phải ở cùng một kết nối.
4. Pool đếm kết nối còn cho mượn. Không release thì pool coi như chưa ai trả, request sau xếp hàng mãi, không có gì để báo lỗi.
5. DECIMAL về dạng chuỗi nên + nối chuỗi. Sửa bằng Number(...) hoặc decimalNumbers: true.