Introduction: Following the previous NodeJS Hello, World! We can also see other powerful features. With the current popularity of NodeJS community and the support of a large number of engineers, a large number of modules have been gradually introduced.
Content: The following demonstrates the interaction between NodeJS and Mysql.
At this time, you need to add Mysql's Module to NodeJS. At this time, the npm (Node package manager) mentioned in the previous chapter has been activated.
Install Mysql Module into NodeJS:
The code copy is as follows:
$npm install Mysql
JS script mysqlTest.js
The code copy is as follows:
// mysqlTest.js
//Load mysql Module
var Client = require('mysql').Client,
client = new Client(),
//The database name to be created
TEST_DATABASE = 'nodejs_mysql_test',
//The table name to be created
TEST_TABLE = 'test';
//username
client.user = 'root';
//password
client.password = 'root';
//Create a connection
client.connect();
client.query('CREATE DATABASE '+TEST_DATABASE, function(err) {
if (err && err.number != Client.ERROR_DB_CREATE_EXISTS) {
throw err;
}
});
// If no callback is provided, any errors will be emitted as `'error'`
// events by the client
client.query('USE '+TEST_DATABASE);
client.query(
'CREATE TABLE '+TEST_TABLE+
'(id INT(11) AUTO_INCREMENT, '+
'title VARCHAR(255), '+
'text TEXT, '+
'created DATETIME, '+
'PRIMARY KEY (id))'
);
client.query(
'INSERT INTO '+TEST_TABLE+' '+
'SET title = ?, text = ?, created = ?',
['super cool', 'this is a nice text', '2010-08-16 10:00:23']
);
var query = client.query(
'INSERT INTO '+TEST_TABLE+' '+
'SET title = ?, text = ?, created = ?',
['another entry', 'because 2 entries make a better test', '2010-08-16 12:42:15']
);
client.query(
'SELECT * FROM '+TEST_TABLE,
function selectCb(err, results, fields) {
if (err) {
throw err;
}
console.log(results);
console.log(fields);
client.end();
}
);
Execute scripts
The code copy is as follows:
node mysqlTest.js
The effects are as follows:
// mysql-demo.js
const mysql = require('mysql2/promise'); // Use mysql2 that supports Promise
require('dotenv').config(); // Environment variable management
// Use connection pool to improve performance
const pool = mysql.createPool({
host: process.env.DB_HOST || 'localhost',
user: process.env.DB_USER || 'root',
password: process.env.DB_PASSWORD || 'root',
database: process.env.DB_NAME || 'nodejs_mysql_test',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
// Table structure definition (DDL management)
const TABLE_SCHEMA = `
CREATE TABLE IF NOT EXISTS posts (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
content TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
`;
// Business logic encapsulation
class PostDAO {
constructor(pool) {
this.pool = pool;
}
async initialize() {
await this.pool.execute(TABLE_SCHEMA);
console.log('Table verified/created');
}
async createPost(post) {
const [result] = await this.pool.execute(
`INSERT INTO posts (title, content)
VALUES (?, ?)`,
[post.title, post.content]
);
return { ...post, id: result.insertId };
}
async getPosts(page = 1, pageSize = 10) {
const offset = (page - 1) * pageSize;
const [rows] = await this.pool.execute(
`SELECT * FROM posts
ORDER BY created_at DESC
LIMIT ? OFFSET ?`,
[pageSize, offset]
);
return rows;
}
async transactionDemo() {
const conn = await this.pool.getConnection();
try {
await conn.beginTransaction();
// Transaction operation 1
await conn.execute(
`INSERT INTO posts (title, content)
VALUES (?, ?)`,
['Transaction 1', 'Atomic operation demo']
);
// Transaction operation 2
const [result] = await conn.execute(
`UPDATE posts SET content = ?
WHERE title = ?`,
['Updated content', 'Transaction 1']
);
await conn.commit();
return result;
} catch (err) {
await conn.rollback();
throw err;
} finally {
conn.release();
}
}
}
// Execution example
(async () => {
try {
const dao = new PostDAO(pool);
await dao.initialize();
// Create a new article
const newPost = await dao.createPost({
title: 'Modern Node.js MySQL Guide',
content: 'Best practices for database operations'
});
console.log('Created post:', newPost);
// Pagination query
const posts = await dao.getPosts();
console.log('First page results:', posts);
// Transaction Demo
const txResult = await dao.transactionDemo();
console.log('Transaction completed:', txResult);
// Final query
const finalPosts = await dao.getPosts();
console.log('Final posts:', finalPosts);
} catch (err) {
console.error('Application error:', err);
} finally {
await pool.end();
}
})();