pg-qry-exec

This module provides a simple interface for executing PostgreSQL queries with with or without arguments and provides comprehensive response handling with logging and performance monitoring capabilites for PostgreSQL databases.

Installation

Install from npm :

$ npm install pg-qry-exec

Supported Properties

| Function Name | Description | | ------------- | ----------- | | getRowCount | gives number of rows returned by the query | | getRowData | gives array of object (data) returned by the query | | getFields | gives the array of fields from select part | | getErrorData | gives details regarding error in query execution | | getExecutionStatus | Gives the status(true/false) of query execution | | getExecutionTime | Gives query execution time |

Features

a) Simple query execution interface b) Comprehensive response handling c) Built-in error management d) Field metadata access e) Row count tracking f) Parameterized query support

Usage

Install this module by using below command

$ npm install pg-qry-exec

Import module in your code base

Example (With argument)

import PgQryExecutor from 'pg-qry-exec';
import { Client } from 'pg';

async function executeQuery() {
    const client = new Client({
        // your postgres connection config
    });
    
    try {
        await client.connect();
        const queryExecutor = new PgQryExecutor();
        const query = 'SELECT * FROM users WHERE id = $1';
        const response = await queryExecutor.run(query, userId, client);
        
        if (response.getExecutionStatus()) {
            const data = response.getRowData();
            const rowCount = response.getRowCount();
            console.log(`Retrieved ${rowCount} rows:`, data);
        } else {
            const error = response.getErrorData();
            console.error('Query failed:', error);
        }
    } finally {
        await client.end();
    }
}

Points to Note

a) First argument should be query b) Last argument should be client c) Between you can pass argument if required by the query

Example (Without argument)

import PgQryExecutor from 'pg-qry-exec';
import { Client } from 'pg';

async function executeQuery() {
    const client = new Client({
        // your postgres connection config
    });
    
    try {
        await client.connect();
        const queryExecutor = new PgQryExecutor();
        const query = 'SELECT * FROM users';
        const response = await queryExecutor.run(query, client);
        
        if (response.getExecutionStatus()) {
            const data = response.getRowData();
            const rowCount = response.getRowCount();
            console.log(`Retrieved ${rowCount} rows:`, data);
        } else {
            const error = response.getErrorData();
            console.error('Query failed:', error);
        }
    } finally {
        await client.end();
    }
}

Error Handling

The module includes built-in error handling through the QueryErrorModel:

try {
    const response = await queryExecutor.run(query, client);
    if (!response.getExecutionStatus()) {
        const error = response.getErrorData();
        // Handle error appropriately
    }
} catch (error) {
    // Handle unexpected errors
}

Logging

If you want to log query

    const queryExecutor = new PgQryExecutor(true);

If you want to log argument passed to query

    const queryExecutor = new PgQryExecutor(false,true);

If you want to log both query and argument

    const queryExecutor = new PgQryExecutor(true,true);

If you don't want to log

    const queryExecutor = new PgQryExecutor();

If you want to log query plan

    const queryExecutor = new PgQryExecutor(false,false,true);

The query plan output will show you

a) The sequence of operations PostgreSQL will perform. b) Estimated costs for each operation. c) How tables will be scanned (sequential scan, index scan etc.) d) Join strategies if multiple tables are involved e) Estimated number of rows

Best Practises

Always close database connections:

try {
    // your query execution
} finally {
    await client.end();
}

Check execution status before accessing data:

if (response.getExecutionStatus()) {
    // Safe to access data
    const data = response.getRowData();
} else {
    // Handle error case
}

✨ Authors

🤝 Contributors