MySQL and Services

After the MySQL Shell is installed, there is a new MySQL Shell for VS Code tab. From the MySQL Shell for VS Code tab, select DB Connection Overview. Click New Connection and enter the MySQL installation information. Use the installation root username and password.

Select the new connection and open a MySQL Shell Script window. Then use SQL script to create a new database. Use underscores (_) to separate words (snake_case) instead of spaces or mixed case for the database name.

MySQL Shell Script

CREATE DATABASE get_started;

You should create a new database user with privileges restricted to the new database.

MySQL Shell Script

CREATE USER 'get_started_user'@'localhost' IDENTIFIED BY 'P@ssw0rd';
GRANT ALL PRIVILEGES ON get_started.* TO 'get_started_user'@'localhost';
FLUSH PRIVILEGES;

Use the .env file to pass the database configuration to the application's process.env object.

.env

DB_HOST=localhost
DB_PORT=3306
DB_USER=get_started_user
DB_PASSWORD=P@ssw0rd
DB_DATABASE=get_started

Add a new root directory named Services and add a new file named connection-pool.mjs. Import mysql then create, test, and export a ConnectionPool.

connection-pool.mjs

import mysql from 'mysql2/promise';

process.loadEnvFile();
// Create the connection pool
const ConnectionPool = mysql.createPool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Function to test the connection
ConnectionPool.getConnection()
  .then(connection => {
    console.log('Database connected successfully.');
    connection.release();
  })
  .catch(err => {
    console.error('Error connecting to the database:', err);
    throw err;
  });

export default ConnectionPool;

Create a database table for a Settings service.

MySQL Shell Script

USE get_started;
CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting VARCHAR(256) UNIQUE NOT NULL,
  value TEXT NOT NULL,
  updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Add a settings-service.mjs file to the Services directory. Import the connection-pool.mjs to perform database queries. If you use the pool's shortcut pool.query() method, the connection is automatically acquired, used for the single query, and returned to the pool. This is the easiest and recommended approach for simple, one-off queries.

settings-service.mjs

import ConnectionPool from './connection-pool.mjs';

const SettingsService = {};

// Function to create a new setting
SettingsService.create = async (newSetting) => {
  const [result] = await ConnectionPool.query(
    'INSERT INTO settings (setting, value) VALUES (?, ?)',
    [newSetting.setting, newSetting.value]
  );
  return { id: result.insertId, ...newSetting };
};

// Function to find a setting by ID
SettingsService.findById = async (id) => {
  const [rows] = await ConnectionPool.query('SELECT * FROM settings WHERE id = ?', [id]);
  return rows[0];
};

// Function to find the value by setting
SettingsService.findValueBySetting = async (setting) => {
  const [value] = await ConnectionPool.query('SELECT value FROM settings WHERE setting = ?', [setting]);
  return value[0];
};

// Function to find all settings
SettingsService.findAll = async () => {
  const [rows] = await ConnectionPool.query('SELECT * FROM settings');
  return rows;
};

// Function to update a setting
SettingsService.update = async (id, updatedSetting) => {
  const [result] = await ConnectionPool.query(
    'UPDATE settings SET setting = ?, value = ?, updated = ? WHERE id = ?',
    [updatedSetting.setting, updatedSetting.value, updatedSetting.updated, id]
  );
  return result.affectedRows > 0;
};

// Function to remove a setting
SettingsService.remove = async (id) => {
  const [result] = await ConnectionPool.query('DELETE FROM settings WHERE id = ?', [id]);
  return result.affectedRows > 0;
};

export default SettingsService;

Import the SettingsService where needed.

import SettingsService from '../services/settings-service.mjs';

Then you can call SettingsService functions.

const result = await SettingsService.findValueBySetting('Contact-Name');
const contactName = result.value;