PostgreSQL and Services

After the PostgreSQL extension is installed, there is a new PostgreSQL tab. From the PostgreSQL tab, click the Add New Connection button and enter the PostgreSQL installation information. Use the root username (postgres) and the installation password.

Expand the new connection and Databases. Right click on the postgres database and select new query.

In the query pane, enter the commands to create a new database and a database user with privileges limited to the new database.

PostgreSQL Script

CREATE DATABASE get_started;
CREATE USER get_started_user WITH PASSWORD 'P@ssw0rd';
ALTER DATABASE get_started OWNER TO get_started_user;
GRANT ALL PRIVILEGES ON DATABASE get_started TO get_started_user;

Click the Execute PostgreSQL Query button.

From a Terminal window in the project folder, use the Node Package Manager (npm) to install the PostgreSQL (pg) package.

PowerShell or Command Prompt

npm install pg

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

.env

DB_CONFIGURED=true
DB_HOST=localhost
DB_PORT=5432
DB_USER=get_started_user
DB_PASSWORD=P@ssw0rd
DB_DATABASE=get_started

You load the variables to the runtime process.env object with the loadEnvFile() method at the very start of server.js.

server.js

process.loadEnvFile(); // Loads from .env by default

Add a new root directory named Services and add a new file named connection-pool.mjs. Import the Pool object from the pg package, then create, test and export the new configured pool.

connection-pool.mjs

import { Pool } from 'pg';

export const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT, 
  // Pool-specific settings
  max: 10,                    // Maximum connections in the pool
  idleTimeoutMillis: 30000,   // Close idle connections after 30 seconds
  connectionTimeoutMillis: 2000, // Fail fast if connection takes > 2 seconds
});

// Test database connection
pool.connect((err, client, release) => {
  if (err) {
    return console.error('Error acquiring client', err.stack);
  }
  client.query('SELECT NOW()', (err, result) => {
    release();
    if (err) {
      return console.error('Error executing query', err.stack);
    }
    console.log(`Connected to database: ${process.env.DB_DATABASE}!`);
  });
});

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.

The Get Started PostgeSQL app creates a Settings table if DB_CONFIGURED=true.

settings-service.mjs

import { pool } from './connection-pool.mjs';

const SettingsService = {};

// Function to create the settings table if not exists
SettingsService.initialize = async () => {
  await pool.query(
    `CREATE TABLE IF NOT EXISTS settings (
      id int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1000),
      setting varchar(256) NOT NULL,
      value text NOT NULL,
      updated timestamptz NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      CONSTRAINT ux_settings_setting UNIQUE (setting)
    );`
  );
};

// Function to create a new setting
SettingsService.create = async (newSetting) => {
  const result = await pool.query(
    'INSERT INTO settings (setting, value) VALUES ($1, $2) RETURNING *',
    [newSetting.setting, newSetting.value]
  );
  return result.rows[0];
};

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

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

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

// Function to update a setting
SettingsService.update = async (id, updatedSetting) => {
  const result = await pool.query(
    'UPDATE settings SET setting = $1, value = $2, updated = $3 WHERE id = $4 RETURNING *',
    [updatedSetting.setting, updatedSetting.value, updatedSetting.updated, id]
  );
  return result.rows.length > 0;
};

// Function to remove a setting
SettingsService.remove = async (id) => {
  const result = await pool.query('DELETE FROM settings WHERE id = $1 RETURNING *', [id]);
  return result.rows.length > 0;
};

export default SettingsService;

Import the SettingsService where needed.

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

Then you can call SettingsService functions.

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