MySQL: Using MySQL with Node.js
This documentation is part of the Getting started guide. View the full guide here: How to get started with MySQL.
👋 Welcome to the Stackhero documentation!
Stackhero offers a ready-to-use MySQL cloud solution that provides a host of benefits, including:
- Unlimited connections and transfers.
- phpMyAdmin web UI included.
- Effortless updates with just a click.
- Optimal performance and robust security powered by a private and dedicated VM.
Save time and simplify your life: it only takes 5 minutes to try Stackhero's MySQL cloud hosting solution!
MySQL X protocol (xdevapi package)
To use the MySQL X protocol, you can install the official xdevapi package:
npm install @mysql/xdevapi
Here is an example script:
const mysqlx = require('@mysql/xdevapi');
(async () => {
// Connect to MySQL using MySQL X Protocol
const session = await mysqlx.getSession({
host: '<XXXXXX>.stackhero-network.com',
port: '<PORT>',
user: 'root',
password: '<ROOT_PASSWORD>'
});
// Create a schema (database) if it does not exist
const schemaExists = await session.getSchema('stackherotest').existsInDatabase();
if (!schemaExists) {
await session.createSchema('stackherotest');
}
// Create table 'users' if it does not exist
const tableExists = await session
.getSchema('stackherotest')
.getTable('users')
.existsInDatabase();
if (!tableExists) {
await session
.sql('CREATE TABLE `stackherotest`.`users` '
+ '('
+ '`userId` INT UNSIGNED NOT NULL,'
+ '`name` VARCHAR(128) NOT NULL,'
+ '`address` TEXT NOT NULL,'
+ '`email` VARCHAR(265) NOT NULL'
+ ') '
+ 'ENGINE = InnoDB;')
.execute();
}
// Insert a sample user
await session
.getSchema('stackherotest')
.getTable('users')
.insert('userId', 'name', 'address', 'email')
.values(
Math.round(Math.random() * 100000),
'User name',
'User address',
'[email protected]'
)
.execute();
// Count the number of users
const usersCount = await session
.getSchema('stackherotest')
.getTable('users')
.count();
console.log(`There are now ${usersCount} entries in the table "users"`);
// Close the connection
await session.close();
})().catch(error => {
console.error('');
console.error('An error occurred!');
console.error(error);
process.exit(1);
});
MySQL protocol (mysql2 package)
If you prefer the classic protocol, you can use the mysql2 package with promise support. You can install it with:
npm install mysql2
Example usage:
const mysql = require('mysql2/promise');
(async () => {
const db = await mysql.createConnection({
host: '<XXXXXX>.stackhero-network.com',
port: '<PORT>',
user: 'root',
password: '<ROOT_PASSWORD>'
});
// Create database if needed
await db.query('CREATE DATABASE IF NOT EXISTS stackherotest');
// Create table if needed
await db.query('CREATE TABLE IF NOT EXISTS `stackherotest`.`users` '
+ '('
+ '`userId` INT UNSIGNED NOT NULL,'
+ '`name` VARCHAR(128) NOT NULL,'
+ '`address` TEXT NOT NULL,'
+ '`email` VARCHAR(265) NOT NULL'
+ ') '
+ 'ENGINE = InnoDB;');
// Insert a sample user
await db.query(
'INSERT INTO `stackherotest`.`users` (`userId`, `name`, `address`, `email`) VALUES ?',
[
[
Math.round(Math.random() * 100000),
'User name',
'User address',
'[email protected]'
]
]
);
// Count users
const [ usersCount ] = await db.query('SELECT COUNT(*) AS `cpt` FROM `stackherotest`.`users`');
console.log(`There are now ${usersCount[0].cpt} entries in the table "users"`);
// Close connection
await db.end();
})().catch(error => {
console.error('');
console.error('An error occurred!');
console.error(error);
process.exit(1);
});