CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/npm-hibernatets

A TypeScript ORM library inspired by Hibernate for working with MySQL, MariaDB, and PostgreSQL databases using decorators and async/await patterns

Overview
Eval results
Files

database-adapters.mddocs/

Database Adapters

Database connectivity and adapter implementations for MySQL, MariaDB, and PostgreSQL with connection pooling, transaction support, and optimized query execution. HibernateTS provides a unified database interface that abstracts vendor-specific differences while maintaining performance.

Capabilities

Base Database Interface

Core interface defining the contract for all database adapters.

/**
 * Base interface for database adapters
 */
interface DataBaseBase {
  /**
   * Execute SQL query with parameter binding
   * @param cfg - Database configuration
   * @param queryString - SQL query string
   * @param params - Optional query parameters
   * @returns Promise resolving to DatabaseResult
   */
  sqlquery<T>(cfg: DataBaseConfig<ISaveAbleObject>, queryString: string, params?: Array<any>): Promise<DatabaseResult>;

  /**
   * Execute SELECT query and return typed results
   * @param queryString - SQL SELECT statement
   * @param params - Optional query parameters
   * @returns Promise resolving to array of result objects
   */
  selectQuery<T>(queryString: string, params?: Array<any>): Promise<Array<T>>;

  /**
   * Close database connection
   * @returns Promise that resolves when connection is closed
   */
  end(): Promise<void>;
}

/**
 * Result interface for database operations
 */
interface DatabaseResult {
  /** ID of inserted record (for INSERT operations) */
  insertId: BigInt;
  /** Number of rows affected by the operation */
  affectedRows: number;
  /** Warning status code from database */
  warningStatus: number;
}

/**
 * Query strings interface for database-specific SQL generation
 */
interface QueryStrings {
  /** Medium text type string for the database */
  mediumTextStr: string;
  /** Generate constraint name */
  constraintName(constraint: any, context: any): string;
  /** Generate unique constraint SQL */
  uniqueConstraintSql(constraint: any, name: string | undefined, context: any): string;
  /** Generate duplicate key update clause */
  duplicateKeyUpdate(escapedKeys: Array<string>, context: any): string;
  /** Insert query transformation (optional) */
  insertQuery?(sql: string, context: any): string;
  /** Convert values for database-specific types (optional) */
  convertValue?(val: any, column: any): any;
}

interface DataBaseConfig<T> {
  /** Primary key field name */
  modelPrimary: string;
  /** Database table name */
  table: string;
  /** Column definitions */
  columns: { [key: string]: any };
  /** Table options */
  options: any;
  /** Reference key field name */
  referenceKey: string;
  /** Creates new instance of entity */
  createInstance(): T;
}

interface ISaveAbleObject {
  [key: string]: any;
}

MariaDB/MySQL Adapter

High-performance adapter for MariaDB and MySQL databases with connection pooling and advanced configuration options.

/**
 * MariaDB/MySQL database adapter implementation
 * Provides optimized connectivity for MariaDB and MySQL databases
 */
class MariaDbBase implements DataBaseBase {
  /** Query strings implementation for MariaDB */
  static queryStrings: QueryStrings;
  /** Query execution counter */
  static queryCt: number;

  /**
   * Execute SQL query with MariaDB-specific optimizations
   */
  sqlquery<T>(cfg: DataBaseConfig<ISaveAbleObject>, queryString: string, params?: Array<any>): Promise<DatabaseResult>;

  /**
   * Execute SELECT query with result streaming support
   */
  selectQuery<T>(queryString: string, params?: Array<any>): Promise<Array<T>>;

  /**
   * Close MariaDB connection and release pool resources
   */
  end(): Promise<void>;
}

/**
 * Set default pool configuration for all MariaDB connections
 * @param opts - Partial MariaDB pool configuration options
 */
function setMariaDbPoolDefaults(opts: Partial<mariadb.PoolConfig>): void;

/**
 * Execute function with auto-managed MariaDB connection pool
 * @param consumer - Function that receives MariaDB pool instance
 * @returns Promise resolving to function result
 */
function withMariaDbPool<T>(consumer: (pool: MariaDbBase) => Promise<T>): Promise<T>;

// MariaDB-specific types
interface mariadb.PoolConfig {
  /** Database host */
  host?: string;
  /** Database port */
  port?: number;
  /** Database user */
  user?: string;
  /** Database password */
  password?: string;
  /** Database name */
  database?: string;
  /** Connection timeout in milliseconds */
  connectTimeout?: number;
  /** Maximum connections in pool */
  connectionLimit?: number;
  /** Minimum connections to maintain */
  minimumIdle?: number;
  /** Maximum idle time before connection closure */
  idleTimeout?: number;
  /** Enable compression */
  compress?: boolean;
  /** SSL configuration */
  ssl?: boolean | object;
}

Usage Examples:

import { MariaDbBase, withMariaDbPool, setMariaDbPoolDefaults } from "hibernatets";

// Configure default pool settings
setMariaDbPoolDefaults({
  host: process.env.DB_URL,
  port: parseInt(process.env.DB_PORT || "3306"),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 10,
  minimumIdle: 2,
  idleTimeout: 300000, // 5 minutes
  connectTimeout: 10000 // 10 seconds
});

// Using managed pool for operations
const results = await withMariaDbPool(async (db) => {
  const users = await db.selectQuery<User>(
    "SELECT * FROM users WHERE active = ?",
    [true]
  );

  const insertResult = await db.sqlquery(
    {},
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ["Alice", "alice@example.com"]
  );

  return { users, insertId: insertResult.insertId };
});

// Direct adapter usage
const db = new MariaDbBase();
try {
  const result = await db.sqlquery(
    {},
    "UPDATE users SET last_login = NOW() WHERE id = ?",
    [123]
  );
  console.log(`Updated ${result.affectedRows} rows`);
} finally {
  await db.end();
}

MySQL Adapter (Legacy)

Legacy MySQL adapter using the older MySQL driver for compatibility with existing systems.

/**
 * MySQL database adapter implementation (legacy driver)
 * Provides compatibility with older MySQL driver implementations
 */
class MysqlBase implements DataBaseBase {
  /** Query strings implementation for MySQL */
  static queryStrings: QueryStrings;
  /** Query execution counter */
  static queryCt: number;

  /**
   * Execute SQL query with MySQL-specific handling
   */
  sqlquery<T>(cfg: DataBaseConfig<ISaveAbleObject>, queryString: string, params?: Array<any>): Promise<DatabaseResult>;

  /**
   * Execute SELECT query with MySQL result formatting
   */
  selectQuery<T>(queryString: string, params?: Array<any>): Promise<Array<T>>;

  /**
   * Close MySQL connection
   */
  end(): Promise<void>;
}

/**
 * Set default pool configuration for MySQL connections
 * @param cfg - Partial MySQL pool configuration
 */
function setMysqlDefaults(cfg: Partial<PoolConfig>): void;

// MySQL-specific types
interface PoolConfig {
  /** Database host */
  host?: string;
  /** Database port */
  port?: number;
  /** Database user */
  user?: string;
  /** Database password */
  password?: string;
  /** Database name */
  database?: string;
  /** Connection limit */
  connectionLimit?: number;
  /** Enable multiple statements */
  multipleStatements?: boolean;
  /** Timezone setting */
  timezone?: string;
}

Usage Examples:

import { MysqlBase, setMysqlDefaults } from "hibernatets";

// Configure MySQL defaults
setMysqlDefaults({
  host: process.env.DB_URL,
  port: parseInt(process.env.DB_PORT || "3306"),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 15,
  multipleStatements: false,
  timezone: "UTC"
});

// Using MySQL adapter
const db = new MysqlBase();
try {
  const products = await db.selectQuery<Product>(
    "SELECT * FROM products WHERE category = ? AND price > ?",
    ["electronics", 100]
  );

  const updateResult = await db.sqlquery(
    {},
    "UPDATE products SET stock = stock - 1 WHERE id = ?",
    [456]
  );

  console.log(`Found ${products.length} products, updated ${updateResult.affectedRows} records`);
} finally {
  await db.end();
}

PostgreSQL Adapter

Modern PostgreSQL adapter with advanced features and optimizations for PostgreSQL-specific functionality.

/**
 * PostgreSQL database adapter implementation
 * Provides full-featured PostgreSQL connectivity with advanced features
 */
class PsqlBase implements DataBaseBase {
  /** Query strings implementation for PostgreSQL */
  static queryStrings: QueryStrings;
  /** Query execution counter */
  static queryCt: number;
  /**
   * Execute SQL query with PostgreSQL-specific parameter binding
   */
  sqlquery<T>(cfg: DataBaseConfig<ISaveAbleObject>, queryString: string, params?: Array<any>): Promise<DatabaseResult>;

  /**
   * Execute SELECT query with PostgreSQL result handling
   */
  selectQuery<T>(queryString: string, params?: Array<any>): Promise<Array<T>>;

  /**
   * Close PostgreSQL connection
   */
  end(): Promise<void>;
}

Usage Examples:

import { PsqlBase } from "hibernatets";

// Using PostgreSQL adapter
const db = new PsqlBase();
try {
  // PostgreSQL-specific features like RETURNING clause
  const insertResult = await db.sqlquery(
    {},
    "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
    ["Bob", "bob@example.com"]
  );

  // JSON operations (PostgreSQL specific)
  const jsonUsers = await db.selectQuery<any>(
    "SELECT id, name, metadata->'preferences' as prefs FROM users WHERE metadata->>'active' = $1",
    ["true"]
  );

  // Array operations
  const taggedPosts = await db.selectQuery<Post>(
    "SELECT * FROM posts WHERE $1 = ANY(tags)",
    ["javascript"]
  );

  console.log(`Inserted user with ID: ${insertResult.insertId}`);
} finally {
  await db.end();
}

Environment Configuration

Environment variables for database connection configuration across different adapters.

// MariaDB/MySQL Environment Variables
interface MariaDBEnvironment {
  /** Database name */
  DB_NAME: string;
  /** Database port (default: 3306) */
  DB_PORT: string;
  /** Database user */
  DB_USER: string;
  /** Database host URL */
  DB_URL: string;
  /** Database password */
  DB_PASSWORD: string;
}

// PostgreSQL Environment Variables
interface PostgreSQLEnvironment {
  /** PostgreSQL host URL */
  PSQL_URL: string;
  /** PostgreSQL port */
  PSQL_PORT: string;
  /** PostgreSQL password */
  PSQL_PWD: string;
  /** PostgreSQL user (default: "postgres") */
  PSQL_USER: string;
  /** PostgreSQL database name */
  PSQL_DB: string;
}

Usage Examples:

// MariaDB/MySQL configuration from environment
const mariaDbConfig = {
  host: process.env.DB_URL,
  port: parseInt(process.env.DB_PORT || "3306"),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME
};

// PostgreSQL configuration from environment
const psqlConfig = {
  host: process.env.PSQL_URL,
  port: parseInt(process.env.PSQL_PORT || "5432"),
  user: process.env.PSQL_USER || "postgres",
  password: process.env.PSQL_PWD,
  database: process.env.PSQL_DB
};

Connection Pool Management

Advanced patterns for managing database connections and optimizing performance.

import { MariaDbBase, withMariaDbPool, setMariaDbPoolDefaults, MysqlBase, setMysqlDefaults, PsqlBase } from "hibernatets";

// Connection pooling strategies
class DatabaseManager {
  private static mariaDbPool: MariaDbBase;
  private static mysqlPool: MysqlBase;
  private static psqlPool: PsqlBase;

  static async initializePools() {
    // MariaDB pool with custom settings
    setMariaDbPoolDefaults({
      connectionLimit: 20,
      minimumIdle: 5,
      idleTimeout: 600000, // 10 minutes
      connectTimeout: 15000, // 15 seconds
      compress: true
    });

    // MySQL pool for legacy systems
    setMysqlDefaults({
      connectionLimit: 10,
      timezone: "UTC",
      multipleStatements: false
    });

    console.log("Database pools initialized");
  }

  static async executeWithTransaction<T>(
    operation: (db: DataBaseBase) => Promise<T>
  ): Promise<T> {
    return await withMariaDbPool(async (db) => {
      await db.sqlquery({}, "START TRANSACTION", []);
      try {
        const result = await operation(db);
        await db.sqlquery({}, "COMMIT", []);
        return result;
      } catch (error) {
        await db.sqlquery({}, "ROLLBACK", []);
        throw error;
      }
    });
  }

  static async healthCheck(): Promise<boolean> {
    try {
      await withMariaDbPool(async (db) => {
        await db.selectQuery("SELECT 1 as health", []);
      });
      return true;
    } catch (error) {
      console.error("Database health check failed:", error);
      return false;
    }
  }
}

// Usage examples
await DatabaseManager.initializePools();

// Transaction example
const result = await DatabaseManager.executeWithTransaction(async (db) => {
  const user = await db.sqlquery(
    {},
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ["Charlie", "charlie@example.com"]
  );

  await db.sqlquery(
    {},
    "INSERT INTO user_profiles (user_id, bio) VALUES (?, ?)",
    [user.insertId, "Software developer"]
  );

  return user.insertId;
});

Performance Optimization

Best practices and patterns for optimal database performance with HibernateTS adapters.

import { MariaDbBase, withMariaDbPool } from "hibernatets";

// Batch operations for improved performance
class PerformanceOptimizer {
  static async batchInsert<T>(
    tableName: string,
    records: T[],
    batchSize: number = 1000
  ): Promise<void> {
    await withMariaDbPool(async (db) => {
      for (let i = 0; i < records.length; i += batchSize) {
        const batch = records.slice(i, i + batchSize);
        const placeholders = batch.map(() => "(?, ?)").join(", ");
        const values = batch.flatMap(record =>
          [record.name, record.email]
        );

        await db.sqlquery(
          {},
          `INSERT INTO ${tableName} (name, email) VALUES ${placeholders}`,
          values
        );
      }
    });
  }

  static async optimizedBulkUpdate(
    tableName: string,
    updates: Array<{ id: number; data: any }>
  ): Promise<void> {
    await withMariaDbPool(async (db) => {
      // Use CASE statements for bulk updates
      const caseStatements = updates.map(update =>
        `WHEN id = ${update.id} THEN '${update.data.name}'`
      ).join(' ');

      const ids = updates.map(u => u.id).join(',');

      await db.sqlquery(
        {},
        `UPDATE ${tableName}
         SET name = CASE ${caseStatements} END,
             updated_at = NOW()
         WHERE id IN (${ids})`,
        []
      );
    });
  }

  static async getConnectionStats(): Promise<any> {
    return await withMariaDbPool(async (db) => {
      return await db.selectQuery(
        "SHOW STATUS LIKE 'Threads_%'",
        []
      );
    });
  }
}

Install with Tessl CLI

npx tessl i tessl/npm-hibernatets

docs

configuration.md

data-operations.md

database-adapters.md

entity-modeling.md

index.md

sql-conditions.md

tile.json