How to Use Vultr Managed Databases for MySQL with NodeJS

[mahesh]

Introduction
Node.js is a Javascript runtime environment that allows you to build highly scalable applications with support for popular relational database systems such as MySQL. You can integrate a Vultr Managed Database for MySQL with your Node.js application to make it highly available and scalable.

Follow the steps in this guide to use a Vultr Managed Database for MySQL with Node.js. You will create a sample Node.js project, and use the mysql2 library to connect to the deployed database. In addition, execute queries to view the database data using your application.

Prerequisites
Before you begin, make sure you:

• Deploy a Vultr Managed Database for MySQL cluster
• Install Node.js version 16 or higher using NVMon your development machine
• Have some basic Node.js and MySQL usage skills

Set Up the Project
1.Create a new project directory

Code Select Expand

$ mkdir project
2.Switch to the new project directory.

Code Select Expand

$ cd project
3.Using npm, initialize your project

Code Select Expand

$ npm init -y
The above command creates a new package.json file in the directory that contains all important information about the project including the metadata and project dependencies

4.Install the project dependencies

Code Select Expand

$ npm i mysql2 dotenv
The above command installs the mysql2 package that allows Node.js application to interact with MySQL databases. The dotenv package loads environment variables such as database connections from a .env file to your process.env object.

5.View the package.json file and verify that all dependency packages are available to the project

Code Select Expand

$ cat package.json
Your output should look like the one below with different version numbers:

 

Code Select Expand

"dependencies": {
     "dotenv": "^16.3.1",
     "mysql2": "^3.6.0"
 }
6.Create the main project index.js file

Code Select Expand

$ touch index.js
7.Using a text editor such as Nano, create a new .env file to store the database information values

Code Select Expand

$ nano .env
8.Add the following variables to the file. Replace the example values with your actual Vultr Managed Database for MySQL details

 

Code Select Expand

DB_HOSTNAME = example.prod.vultrdb.com
 DB_PORT = 167521
 DB_USER = example-user
 DB_PASSWORD = strong-passwrd
 DB_NAME = example-dbSave and close the file.

Connect to the Database
To connect your Node.js application to the MySQL database, use the mysql2 library to use your .env file details as described in the steps below.

1.Open and edit the index.js file

Code Select Expand

$ nano index.js
2.Add the following code to the file

Import the project dependencies

Code Select Expand

  require('dotenv').config()
  const mysql = require('mysql2');
The above code imports mysql2 module, and the dotenv module that reads the contents of your .env file, then forwards the contents to process.env.

Create a new object to store database connection details from the process.env variable.

 

Code Select Expand

const dbConfig = {
      host: process.env.DB_HOSTNAME,
      port: process.env.DB_PORT,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
  };
Create a database connection connectToDB() method

Code Select Expand

  let db;
  const connectToDB = async () => {
      const pool = mysql.createPool(dbConfig);
      const poolPromise = pool.promise();

      return poolPromise;
  }
In the above code, the createPool() connection method creates a pool of connections to the database. The mysql2 module supports the following two methods:

createConnection(): Creates one-time connection to execute queries. One connection can only handle one query at a time. The Next time a query executes, the application re-establishes a connection to the database again

createPool(): Creates a pool of connections to the database. Connection pooling enhances the performance of executing the commands on a database. Instead of establishing a new connection every time the application only executes a query and gets a connection from the pool

Further, pool.promise() wraps the pool object into a promise-based version, and provides a version of the pool that returns promises. This makes it possible to use the async/await syntax for cleaner, and more readable asynchronous code

Create the main() method to call the connectToDB() function with a Connected to Database result when successful

 

Code Select Expand

const main = async () => {
      db = await connectToDB();
      console.log('Connected to database');
  }
Call the main() method at the end of the file

  main();Save and close the file

Your complete index.js should look like the one below:

Code Select Expand

  require('dotenv').config()
  const mysql = require('mysql2');
  const dbConfig = {
      host: process.env.DB_HOSTNAME,
      port: process.env.DB_PORT,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
  };
  let db;
  const connectToDB = async () => {
      const pool = mysql.createPool(dbConfig);
      const poolPromise = pool.promise();

      return poolPromise;
  }
  const main = async () => {
      db = await connectToDB();
      console.log('Connected to database');
  }

  main();
The above index.js file connects your Node.js application to the deployed MySQL database on Vultr and assigns the promise-based pool to the db variable which supports CRUD operations.

Run the index.js file to start the Node.js application

Code Select Expand

$ node index.js
Output:

Code Select Expand

Connected to database
Interact with the MySQL Database Using Node.js
To test your Node.js application functionality, create a new table in the connected database and perform CRUD operations using SQL queries as described in the steps below.

Create a New Table
1.Open and edit the index.js file

Code Select Expand

$ nano index.js
2.Edit the connectToDB() method before return poolPromise;, and include a query that creates a new posts table using the CREATE TABLE IF NOT EXISTS SQL statement

Code Select Expand

const connectToDB = async () => {
     const pool = mysql.createPool(dbConfig);
     const poolPromise = pool.promise();

     //SQL query

     await poolPromise.query("CREATE TABLE IF NOT EXISTS `posts` (`id` int NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `body` varchar(255) NOT NULL, `author` varchar(255), PRIMARY KEY (`id`) );")

     const [allPosts, fields] = await poolPromise.query('SELECT * FROM posts;');
     console.log(`Posts Table: ${JSON.stringify({ allPosts, fields })}`);

     return poolPromise;
 }Save and close the file.

The above code creates a new MySQL table with the following columns:

• id: Automatically increments every new record with unique values using it's Primary Key
• title and body columns accept all characters (VARCHAR) and can store up to 255 characters
• author: Supports all characters with up to 255 characters

The const [allPosts, fields] = await poolPromise.query('SELECT * FROM posts;') query selects all records from the posts table. The query method returns an array where the first element is an array of rows from the query (assigned to allPosts), and the second element is an array of field metadata (assigned to fields).

3.Run the application

Code Select Expand

$ node index.js
Your output should look like the one below:

 

Code Select Expand

34,0]},"_clientEncoding":"utf8","_catalogLength":3,"_catalogStart":167,"_schemaLength":9,"_schemaStart":171,"_tableLength":5,"_tableStart":181,"_orgTableLength":5,"_orgTableStart":187,"_orgNameLength":6,"_orgNameStart":200,"characterSet":224,"encoding":"utf8","name":"author","columnLength":1020,"columnType":253,"type":253,"flags":0,"decimals":0}]}

 Connected to databasePress CTRL + C to stop the application

Create a New Post
1.Edit the index.js file

Code Select Expand

$ nano index.js
2.Add a new createNewPost() method to that inserts a new post into the posts table

 

Code Select Expand

const createNewPost = async (postData) => {
     const { title, body, author } = postData;
     const [newPost, fields] = await db.query('INSERT INTO posts (title, body, author) VALUES (?,?,?);', [title, body, author]);
     console.log(`New post created: ${JSON.stringify(newPost)}`);
 }Call createNewPost() within the main() method along with some dummy data to create a new post

Code Select Expand

await createNewPost({ title: "Test Title", body: "This is the return of test body.", author: "Tommy Shelby" });
Save and close the file.

3.Run the application

Code Select Expand

$ node index.js
Your output should look like the one below:

Code Select Expand

New post created: {"fieldCount":0,"affectedRows":1,"insertId":4,"info":"","serverStatus":2,"warningStatus":0,"changedRows":0}
Press CTRL + C to stop the application

Get a Post by ID
1.Edit the application file

Code Select Expand

$ nano index.js
2.Create a new getPostById() method to fetch the data of a single post

 

Code Select Expand

const getPostById = async (postId) => {
     const [post, fields] = await db.query('SELECT * FROM posts WHERE id=?;', [postId]);
     console.log(`Post: ${JSON.stringify(post)}`);
 }Within the main() method, call getPostById() with some the postId to fetch post data

Code Select Expand

await getPostById(1);
3.Run the application

Code Select Expand

$ node index.js
Output:

 

Code Select Expand

Post: [{"id":1,"title":"Test Title","body":"This is the return of test body.","author":"Tommy Shelby"}]Press CTRL + C to stop the application

Update a Post by ID
1.Edit the index.js file

Code Select Expand

$ nano index.js
2.Create a new updatePostById() method to update the posts table.

 

Code Select Expand

const updatePostById = async (postId, postData) => {
     const { title, body } = postData;
     const [updatePost, fields] = await db.query('UPDATE posts SET title=?, body=? WHERE id=?;', [title, body, postId]);
     console.log(`Post updated: ${JSON.stringify(updatePost)}`);
 }Within the main() method, call updatePostById() along with an some existing post id and the updated data. For example:

Code Select Expand

await updatePostById(1, { title: 'Updated Title', body: "This is updated post body" });3.Run the application

Code Select Expand

$ node index.js
Output:

 

Code Select Expand

Post updated: {"fieldCount":0,"affectedRows":1,"insertId":0,"info":"Rows matched: 1  Changed: 0  Warnings: 0","serverStatus":2,"warningStatus":0,"changedRows":0}Press CTRL + C to stop the application

Delete a Post by ID
1.Edit the index.js application file

Code Select Expand

$ nano index.js
2.Create a new deletePostById() method to delete a post by ID

Code Select Expand

const deletePostById = async (postId) => {
     const [post, fields] = await db.query('DELETE FROM posts WHERE id=?;', [postId]);
     console.log(`Post deleted : ${JSON.stringify(post)}`);
 }In the main() method, call deletePostById() with some the postId to delete. For example, delete the post with ID 2

Code Select Expand

await deletePostById(2);
3.Run the application

Code Select Expand

$ node index.js
Output:

Code Select Expand

Post deleted : {"fieldCount":0,"affectedRows":1,"insertId":0,"info":"","serverStatus":2,"warningStatus":0,"changedRows":0}Press CTRL + C to stop the application

Conclusion
You have integrated a Vultr Managed Database for MySQL with your Node.js application using the mysql2 library. Using the integration, you can create efficient and scalable backend solutions that meet the demands of modern applications.