pg.adapter Integration Documentation

Overview

The pg.adapter is an integration module for Vantage that allows for Read-Only and controlled Write access to PostgreSQL databases. It implements a connection pool to manage database connections efficiently and ensures safe handling of operations through strict validation of SQL queries.

Purpose

The main purposes of the pg.adapter are:

• To facilitate secure connections to PostgreSQL databases.
• To enable users to perform read operations and controlled write operations with restrictions on destructive commands.
• To provide schema introspection capabilities, allowing users to retrieve metadata about database tables.
• To assist in creating and modifying tables while enforcing safe practices.

How It Works

The pg.adapter utilizes the pg library to create a connection pool to a PostgreSQL database. It provides methods for:

• Testing database connections.
• Listing available tables in the database.
• Describing the schema of specific tables.
• Executing read-only SQL queries while blocking any write operations.
• Executing controlled write operations and managing table creation and alterations.

Expected Data

The pg.adapter expects the following data for initialization:

• host: The hostname of the PostgreSQL server.
• port: The port on which the PostgreSQL server is running.
• username: The username used to connect to the database.
• password: The password associated with the username.
• databaseName: The name of the database to connect to.
• ssl: Configuration for Secure Socket Layer connections (if applicable).

These parameters are passed as part of an encryptedRecord object during the instantiation of the SQLAdapter class.

Settings

The following settings are used to configure the pg.adapter. Each one is explained in detail below:

1. Host

• Input Type: String
• Purpose: Specifies the hostname or IP address of the PostgreSQL server where the database is hosted.
• Default Value: N/A (required field).

2. Port

• Input Type: Numeric
• Purpose: Defines the network port on which the PostgreSQL server is listening for connections. The default PostgreSQL port is 5432.
• Default Value: 5432

3. Username

• Input Type: String
• Purpose: The username that will be used for authentication when connecting to the PostgreSQL database. This user should have sufficient permissions for read operations and controlled write operations.
• Default Value: N/A (required field).

4. Password

• Input Type: String (usually encrypted)
• Purpose: The password associated with the provided username for database authentication.
• Default Value: N/A (required field).

5. Database Name

• Input Type: String
• Purpose: Indicates the specific database within the PostgreSQL server to connect to for executing queries.
• Default Value: N/A (required field).

6. SSL

• Input Type: Boolean or object
• Purpose: Determines whether the connection to the database should use SSL encryption for enhanced security. If set to true, the connection will be established using SSL.
• Default Value: false

Use Cases & Examples

Use Case 1: Data Analysis

A data analyst needs to extract data from a PostgreSQL database for a specific reporting project. They can use the pg.adapter to query multiple tables without modifying the database structure.

Use Case 2: Dashboard Integration

A developer is looking to create a live dashboard that needs up-to-date data from a PostgreSQL database. They can use the pg.adapter to efficiently capture data for real-time visualizations without the risk of corrupting the underlying data.

Use Case 3: Compliance and Audit

A compliance officer needs to review database schemas and tables for audit purposes. Using the pg.adapter, they can introspect the database schema and retrieve metadata without the risk of executing unsafe queries.

Configuration Example

Use Case: Data Analysis

Scenario: A data analyst needs to perform read-only operations on the "sales_data" table.

Sample Configuration Data:

{
  "host": "localhost",
  "port": 5432,
  "username": "data_analyst",
  "password": "securepassword123",
  "databaseName": "sales_db",
  "ssl": false
}

Implementation Example:

const pgAdapter = new SQLAdapter({
  host: "localhost",
  port: 5432,
  username: "data_analyst",
  password: "securepassword123",
  databaseName: "sales_db",
  ssl: false,
});

// Testing the connection
pgAdapter.testConnection().then(isConnected => {
  if (isConnected) {
    console.log("Successfully connected to the database!");
    // Perform read operations
    pgAdapter.executeReadOnly("SELECT * FROM sales_data;")
      .then(data => console.log(data.rows))
      .catch(err => console.error(err));
  } else {
    console.error("Failed to connect to the database.");
  }
});

AI Integrations

Currently, the pg.adapter does not have any direct AI integrations. However, the data retrieved through this integration can be fed into AI models or analytics tools for further processing, forecasting, and data insights.

Billing Impacts

Using the pg.adapter incurs potential costs related to database connections and query executions. These costs depend on:

• The PostgreSQL service provider (cloud or on-premise).
• The type of queries executed, particularly if they result in substantial resource usage.

Users should consult their PostgreSQL service provider for detailed billing information based on their usage of the pg.adapter.