MySQL examples in Node.js
If you’re integrating your Node.js service with MySQL, you probably want to execute queries.
I’ve seen a fair bit of ugly code to do this, often using callbacks. I thought I would share some of my own patterns here. Maybe this is useful to others to read.
I don’t typically use ORM’s. If you’re already happy with sequalize, this article is probably not for you.
Prequisites
The most popular NPM package for MySQL is mysql, but I use the mysql2 package instead.
The mysql2 package is also very popular, but has a few advantages.
The advantage I care about is support for promises. The authors of this
package have actually teamed up with the authors of the mysql package to
reduce double efforts, and was made to be compatible with mysql so for many
people it’s a drop-in replacement.
Creating a pool
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'test',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
A pool manages multiple connections. I create a pool once per node.js server.
Note: if you are migrating from the mysql to mysql2 package, you probably
don’t want to require mysql2/promise. You can instead require mysql2 to get
full backwards compatibility and use the promise() functions to get access
to promisified versions of the APIs.
Note2: mysql2 uses the utf8mb4 charset by default. If you don’t know what
this means trust that it’s the right choice. If you do know what this is,
rejoice that somebody finally picked a sane default.
Executing a SELECT query
Generally I don’t need transactions for SELECT queries, because of this I
can simply ask the pool to execute the query.
async function getBlogPost(id) {
const result = await pool.query('SELECT * from posts WHERE id = ?', [id]);
if (!result[0].length < 1) {
throw new Error('Post with this id was not found');
}
return result[0][0];
}
Truncated by Planet PHP, read more at the original (another 15105 bytes)