Easy Database Connector

1.4.1 verified Thu Apr 23 auth: no javascript

easy-database-connector, currently at version 1.4.1, is a TypeScript-first database connector library primarily designed for MSSQL (SQL Server) environments. It offers a comprehensive suite of features aimed at enhancing performance, security, and developer productivity. Key functionalities include connection pooling for optimal performance, built-in encryption for sensitive data, and integrated Redis caching for pagination and general query results. The library provides robust transaction management, supports bulk operations, and offers type-safe querying with full TypeScript backing. While specific release cadence is not explicitly stated, its current version suggests active development. Its differentiators lie in its all-in-one approach to common database concerns for MSSQL, providing encryption, caching, and robust operations within a single, coherent API, unlike more minimalist ORMs or query builders.

Common errors

error Login failed for user 'your_user'. ↓

cause Incorrect database connection credentials (username, password, host, or port) are provided in the environment variables.

fix

Verify that DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, and DB_DATABASE are correctly configured in your .env file and that the user has appropriate database permissions.

error Error: Environment variable DB_HOST is not set. ↓

cause A critical database configuration environment variable is missing or misspelled, preventing the connector from initializing.

fix

Ensure all mandatory database configuration variables (e.g., DB_TYPE, DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE, DB_PORT, DB_ENCRYPT) are present and correctly named in your .env file.

error A Master Key must be open before a Symmetric Key can be opened. Open the Master Key in the database. ↓

cause The MSSQL encryption setup for `easy-database-connector` is incomplete or improperly configured, specifically related to the master key or symmetric key initialization in SQL Server.

fix

Confirm that your SQL Server database has the necessary master key and symmetric key/certificate created and that the MASTER_KEY_PASSWORD, MSSQL_SYNNETRIC_KEY_NAME, and MSSQL_CERTIFICATE_NAME in your .env file match the database configuration.

Warnings

gotcha The library relies heavily on environment variables for database, Redis, and encryption configuration. Missing or incorrectly named variables in the .env file can lead to connection failures or unexpected behavior. ↓

fix Ensure all required environment variables (e.g., DB_HOST, DB_USER, REDIS_HOST) are correctly defined and loaded before application startup (e.g., using `dotenv/config`).

gotcha Utilizing the built-in encryption features requires specific setup in your MSSQL database, including creating master keys, symmetric keys, and certificates. Misconfiguration or missing database objects will cause encryption-related operations to fail. ↓

fix Consult MSSQL documentation for setting up database encryption. Verify that `MSSQL_SYNNETRIC_KEY_NAME`, `MSSQL_CERTIFICATE_NAME`, and `MASTER_KEY_PASSWORD` in your .env correctly reference existing and accessible SQL Server encryption objects.

gotcha While optional, enabling and configuring Redis caching requires a running Redis server and correct `REDIS_` environment variables. Incorrect Redis configuration can lead to caching failures, fallback to direct database queries, or application errors. ↓

fix Ensure a Redis server is accessible from your application and `REDIS_ENABLED=true`, `REDIS_HOST`, `REDIS_PORT`, `REDIS_PASSWORD` (if applicable) are correctly set in your .env file.

gotcha When performing bulk operations with the `execute` function, the `bulk.columns` array requires precise MSSQL data type definitions (e.g., `mssql.NVarChar(100)`). Mismatches between these types, the database schema, or the data being inserted will result in insertion errors. ↓

fix Carefully match the `ColumnType` definitions in `bulk.columns` to the exact data types and lengths of your target SQL Server table columns. Import `mssql` types if needed for type definitions.

Install

npm install easy-database-connector

yarn add easy-database-connector

pnpm add easy-database-connector

Imports

query

wrong

const query = require('easy-database-connector').query;

correct

import { query } from 'easy-database-connector';

Primarily used for SELECT operations with optional caching.

execute

wrong

const execute = require('easy-database-connector').execute;

correct

import { execute } from 'easy-database-connector';

Used for INSERT, UPDATE, DELETE, and bulk operations.

transaction

wrong

const transaction = require('easy-database-connector').transaction;

correct

import { transaction } from 'easy-database-connector';

Provides a robust mechanism for running multiple queries within a single database transaction.

Quickstart

This quickstart demonstrates basic querying, cached pagination, encrypted data insertion, and transaction management using the library's core functions.

import { query, execute, queryWithPagination, transaction } from 'easy-database-connector';
import 'dotenv/config'; // Ensure environment variables are loaded

interface User {
    id: number;
    name: string;
    email: string;
    active: boolean;
}

async function runExample() {
    // Basic query
    const users = await query<User>({
        sql: 'SELECT * FROM users WHERE active = @p0',
        parameters: [true]
    });
    console.log('Active users:', users);

    // Paged query with caching
    const pagedUsers = await queryWithPagination<User>({
        sql: 'SELECT * FROM users',
        parameters: [],
        page: 1,
        pageSize: 10,
        orderBy: 'name ASC',
        cache: {
            key: 'users:page1',
            ttl: 300 // 5 minutes
        }
    });
    console.log('Paged users:', pagedUsers.rows);

    // Encrypted data insertion (requires MSSQL encryption setup)
    // Ensure environment variables for encryption are set: MSSQL_SYNNETRIC_KEY_NAME, MSSQL_CERTIFICATE_NAME, MASTER_KEY_PASSWORD
    try {
        await execute({
            sql: 'INSERT INTO secure_data (data) VALUES (@p0)',
            parameters: ['sensitive information'],
            encryption: {
                open: { aes: true, masterkey: true },
                data: ['0']
            }
        });
        console.log('Sensitive data inserted (encrypted).');
    } catch (e) {
        console.error('Failed to insert encrypted data. Check MSSQL encryption setup and .env config.', e);
    }

    // Transaction example
    await transaction(async (trx) => {
        await execute({
            sql: 'INSERT INTO users (name) VALUES (@p0)',
            parameters: ['John Doe'],
            transaction: trx
        });

        await execute({
            sql: 'INSERT INTO logs (action) VALUES (@p0)',
            parameters: ['user_john_created'],
            transaction: trx
        });
    });
    console.log('Transaction completed: User and log created.');
}

runExample().catch(console.error);