mysql

The MySQL extension provides access to MySQL and MariaDB databases with parameterised queries and transaction support.

Connecting

const conn = mysql.connect("localhost", "user", "password", "database");

// With custom port (default is 3306)
const conn = mysql.connect("localhost", "user", "password", "database", 3307);

API

MethodDescription
conn.query(sql, params?)Execute a query and return all rows as an array of objects
conn.queryOne(sql, params?)Execute a query and return the first row (or null)
conn.exec(sql, params?)Execute a statement (INSERT, UPDATE, DELETE, CREATE, etc.)
conn.lastInsertId()Last auto-increment ID from the most recent exec()
conn.changes()Number of rows affected by the most recent exec()
conn.beginTransaction()Start a transaction
conn.commit()Commit the current transaction
conn.rollback()Roll back the current transaction
conn.close()Close the connection

Parameterised Queries

Use ? placeholders to safely pass values. This prevents SQL injection:

const users = conn.query("SELECT * FROM users WHERE active = ? AND role = ?", [1, "admin"]);
const user = conn.queryOne("SELECT * FROM users WHERE email = ?", ["alice@example.com"]);

conn.exec("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);
conn.exec("UPDATE users SET name = ? WHERE id = ?", ["Bob", 1]);
conn.exec("DELETE FROM users WHERE id = ?", [1]);

Transactions

conn.beginTransaction();
try {
    conn.exec("INSERT INTO orders (user_id, total) VALUES (?, ?)", [1, 99.99]);
    const orderId = conn.lastInsertId();
    conn.exec("INSERT INTO order_items (order_id, product_id) VALUES (?, ?)", [orderId, 42]);
    conn.commit();
} catch (e) {
    conn.rollback();
}

Return Values

query() returns an array of objects. Column names become object keys, and values are automatically typed:

  • Integer columns → JavaScript numbers
  • Float/decimal columns → JavaScript numbers
  • NULL → null
  • Everything else → strings
const users = conn.query("SELECT id, name, email FROM users");
// [{ id: 1, name: "Alice", email: "alice@example.com" }, ...]

Requirements

The MySQL extension requires libmysqlclient to be installed on the system:

# Debian/Ubuntu
sudo apt-get install libmysqlclient21

# RHEL/Fedora
sudo dnf install mysql-libs

Notes

  • Connections use UTF8MB4 character set by default
  • Up to 16 simultaneous connections are supported
  • Always close connections when done to avoid resource leaks
  • Works with both MySQL and MariaDB
Was this page helpful?