0
# Database Adapters
1

2
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.
3

4
## Capabilities
5

6
### Base Database Interface
7

8
Core interface defining the contract for all database adapters.
9

10
```typescript { .api }
11
/**
12
 * Base interface for database adapters
13
 */
14
interface DataBaseBase {
15
  /**
16
   * Execute SQL query with parameter binding
17
   * @param cfg - Database configuration
18
   * @param queryString - SQL query string
19
   * @param params - Optional query parameters
20
   * @returns Promise resolving to DatabaseResult
21
   */
22
  sqlquery<T>(cfg: DataBaseConfig<ISaveAbleObject>, queryString: string, params?: Array<any>): Promise<DatabaseResult>;
23

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

32
  /**
33
   * Close database connection
34
   * @returns Promise that resolves when connection is closed
35
   */
36
  end(): Promise<void>;
37
}
38

39
/**
40
 * Result interface for database operations
41
 */
42
interface DatabaseResult {
43
  /** ID of inserted record (for INSERT operations) */
44
  insertId: BigInt;
45
  /** Number of rows affected by the operation */
46
  affectedRows: number;
47
  /** Warning status code from database */
48
  warningStatus: number;
49
}
50

51
/**
52
 * Query strings interface for database-specific SQL generation
53
 */
54
interface QueryStrings {
55
  /** Medium text type string for the database */
56
  mediumTextStr: string;
57
  /** Generate constraint name */
58
  constraintName(constraint: any, context: any): string;
59
  /** Generate unique constraint SQL */
60
  uniqueConstraintSql(constraint: any, name: string | undefined, context: any): string;
61
  /** Generate duplicate key update clause */
62
  duplicateKeyUpdate(escapedKeys: Array<string>, context: any): string;
63
  /** Insert query transformation (optional) */
64
  insertQuery?(sql: string, context: any): string;
65
  /** Convert values for database-specific types (optional) */
66
  convertValue?(val: any, column: any): any;
67
}
68

69
interface DataBaseConfig<T> {
70
  /** Primary key field name */
71
  modelPrimary: string;
72
  /** Database table name */
73
  table: string;
74
  /** Column definitions */
75
  columns: { [key: string]: any };
76
  /** Table options */
77
  options: any;
78
  /** Reference key field name */
79
  referenceKey: string;
80
  /** Creates new instance of entity */
81
  createInstance(): T;
82
}
83

84
interface ISaveAbleObject {
85
  [key: string]: any;
86
}
87
```
88

89
### MariaDB/MySQL Adapter
90

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

93
```typescript { .api }
94
/**
95
 * MariaDB/MySQL database adapter implementation
96
 * Provides optimized connectivity for MariaDB and MySQL databases
97
 */
98
class MariaDbBase implements DataBaseBase {
99
  /** Query strings implementation for MariaDB */
100
  static queryStrings: QueryStrings;
101
  /** Query execution counter */
102
  static queryCt: number;
103

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

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

114
  /**
115
   * Close MariaDB connection and release pool resources
116
   */
117
  end(): Promise<void>;
118
}
119

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

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

133
// MariaDB-specific types
134
interface mariadb.PoolConfig {
135
  /** Database host */
136
  host?: string;
137
  /** Database port */
138
  port?: number;
139
  /** Database user */
140
  user?: string;
141
  /** Database password */
142
  password?: string;
143
  /** Database name */
144
  database?: string;
145
  /** Connection timeout in milliseconds */
146
  connectTimeout?: number;
147
  /** Maximum connections in pool */
148
  connectionLimit?: number;
149
  /** Minimum connections to maintain */
150
  minimumIdle?: number;
151
  /** Maximum idle time before connection closure */
152
  idleTimeout?: number;
153
  /** Enable compression */
154
  compress?: boolean;
155
  /** SSL configuration */
156
  ssl?: boolean | object;
157
}
158
```
159

160
**Usage Examples:**
161

162
```typescript
163
import { MariaDbBase, withMariaDbPool, setMariaDbPoolDefaults } from "hibernatets";
164

165
// Configure default pool settings
166
setMariaDbPoolDefaults({
167
  host: process.env.DB_URL,
168
  port: parseInt(process.env.DB_PORT || "3306"),
169
  user: process.env.DB_USER,
170
  password: process.env.DB_PASSWORD,
171
  database: process.env.DB_NAME,
172
  connectionLimit: 10,
173
  minimumIdle: 2,
174
  idleTimeout: 300000, // 5 minutes
175
  connectTimeout: 10000 // 10 seconds
176
});
177

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

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

191
  return { users, insertId: insertResult.insertId };
192
});
193

194
// Direct adapter usage
195
const db = new MariaDbBase();
196
try {
197
  const result = await db.sqlquery(
198
    {},
199
    "UPDATE users SET last_login = NOW() WHERE id = ?",
200
    [123]
201
  );
202
  console.log(`Updated ${result.affectedRows} rows`);
203
} finally {
204
  await db.end();
205
}
206
```
207

208
### MySQL Adapter (Legacy)
209

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

212
```typescript { .api }
213
/**
214
 * MySQL database adapter implementation (legacy driver)
215
 * Provides compatibility with older MySQL driver implementations
216
 */
217
class MysqlBase implements DataBaseBase {
218
  /** Query strings implementation for MySQL */
219
  static queryStrings: QueryStrings;
220
  /** Query execution counter */
221
  static queryCt: number;
222

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

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

233
  /**
234
   * Close MySQL connection
235
   */
236
  end(): Promise<void>;
237
}
238

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

245
// MySQL-specific types
246
interface PoolConfig {
247
  /** Database host */
248
  host?: string;
249
  /** Database port */
250
  port?: number;
251
  /** Database user */
252
  user?: string;
253
  /** Database password */
254
  password?: string;
255
  /** Database name */
256
  database?: string;
257
  /** Connection limit */
258
  connectionLimit?: number;
259
  /** Enable multiple statements */
260
  multipleStatements?: boolean;
261
  /** Timezone setting */
262
  timezone?: string;
263
}
264
```
265

266
**Usage Examples:**
267

268
```typescript
269
import { MysqlBase, setMysqlDefaults } from "hibernatets";
270

271
// Configure MySQL defaults
272
setMysqlDefaults({
273
  host: process.env.DB_URL,
274
  port: parseInt(process.env.DB_PORT || "3306"),
275
  user: process.env.DB_USER,
276
  password: process.env.DB_PASSWORD,
277
  database: process.env.DB_NAME,
278
  connectionLimit: 15,
279
  multipleStatements: false,
280
  timezone: "UTC"
281
});
282

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

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

297
  console.log(`Found ${products.length} products, updated ${updateResult.affectedRows} records`);
298
} finally {
299
  await db.end();
300
}
301
```
302

303
### PostgreSQL Adapter
304

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

307
```typescript { .api }
308
/**
309
 * PostgreSQL database adapter implementation
310
 * Provides full-featured PostgreSQL connectivity with advanced features
311
 */
312
class PsqlBase implements DataBaseBase {
313
  /** Query strings implementation for PostgreSQL */
314
  static queryStrings: QueryStrings;
315
  /** Query execution counter */
316
  static queryCt: number;
317
  /**
318
   * Execute SQL query with PostgreSQL-specific parameter binding
319
   */
320
  sqlquery<T>(cfg: DataBaseConfig<ISaveAbleObject>, queryString: string, params?: Array<any>): Promise<DatabaseResult>;
321

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

327
  /**
328
   * Close PostgreSQL connection
329
   */
330
  end(): Promise<void>;
331
}
332
```
333

334
**Usage Examples:**
335

336
```typescript
337
import { PsqlBase } from "hibernatets";
338

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

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

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

361
  console.log(`Inserted user with ID: ${insertResult.insertId}`);
362
} finally {
363
  await db.end();
364
}
365
```
366

367
### Environment Configuration
368

369
Environment variables for database connection configuration across different adapters.
370

371
```typescript { .api }
372
// MariaDB/MySQL Environment Variables
373
interface MariaDBEnvironment {
374
  /** Database name */
375
  DB_NAME: string;
376
  /** Database port (default: 3306) */
377
  DB_PORT: string;
378
  /** Database user */
379
  DB_USER: string;
380
  /** Database host URL */
381
  DB_URL: string;
382
  /** Database password */
383
  DB_PASSWORD: string;
384
}
385

386
// PostgreSQL Environment Variables
387
interface PostgreSQLEnvironment {
388
  /** PostgreSQL host URL */
389
  PSQL_URL: string;
390
  /** PostgreSQL port */
391
  PSQL_PORT: string;
392
  /** PostgreSQL password */
393
  PSQL_PWD: string;
394
  /** PostgreSQL user (default: "postgres") */
395
  PSQL_USER: string;
396
  /** PostgreSQL database name */
397
  PSQL_DB: string;
398
}
399
```
400

401
**Usage Examples:**
402

403
```typescript
404
// MariaDB/MySQL configuration from environment
405
const mariaDbConfig = {
406
  host: process.env.DB_URL,
407
  port: parseInt(process.env.DB_PORT || "3306"),
408
  user: process.env.DB_USER,
409
  password: process.env.DB_PASSWORD,
410
  database: process.env.DB_NAME
411
};
412

413
// PostgreSQL configuration from environment
414
const psqlConfig = {
415
  host: process.env.PSQL_URL,
416
  port: parseInt(process.env.PSQL_PORT || "5432"),
417
  user: process.env.PSQL_USER || "postgres",
418
  password: process.env.PSQL_PWD,
419
  database: process.env.PSQL_DB
420
};
421
```
422

423
### Connection Pool Management
424

425
Advanced patterns for managing database connections and optimizing performance.
426

427
```typescript
428
import { MariaDbBase, withMariaDbPool, setMariaDbPoolDefaults, MysqlBase, setMysqlDefaults, PsqlBase } from "hibernatets";
429

430
// Connection pooling strategies
431
class DatabaseManager {
432
  private static mariaDbPool: MariaDbBase;
433
  private static mysqlPool: MysqlBase;
434
  private static psqlPool: PsqlBase;
435

436
  static async initializePools() {
437
    // MariaDB pool with custom settings
438
    setMariaDbPoolDefaults({
439
      connectionLimit: 20,
440
      minimumIdle: 5,
441
      idleTimeout: 600000, // 10 minutes
442
      connectTimeout: 15000, // 15 seconds
443
      compress: true
444
    });
445

446
    // MySQL pool for legacy systems
447
    setMysqlDefaults({
448
      connectionLimit: 10,
449
      timezone: "UTC",
450
      multipleStatements: false
451
    });
452

453
    console.log("Database pools initialized");
454
  }
455

456
  static async executeWithTransaction<T>(
457
    operation: (db: DataBaseBase) => Promise<T>
458
  ): Promise<T> {
459
    return await withMariaDbPool(async (db) => {
460
      await db.sqlquery({}, "START TRANSACTION", []);
461
      try {
462
        const result = await operation(db);
463
        await db.sqlquery({}, "COMMIT", []);
464
        return result;
465
      } catch (error) {
466
        await db.sqlquery({}, "ROLLBACK", []);
467
        throw error;
468
      }
469
    });
470
  }
471

472
  static async healthCheck(): Promise<boolean> {
473
    try {
474
      await withMariaDbPool(async (db) => {
475
        await db.selectQuery("SELECT 1 as health", []);
476
      });
477
      return true;
478
    } catch (error) {
479
      console.error("Database health check failed:", error);
480
      return false;
481
    }
482
  }
483
}
484

485
// Usage examples
486
await DatabaseManager.initializePools();
487

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

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

502
  return user.insertId;
503
});
504
```
505

506
### Performance Optimization
507

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

510
```typescript
511
import { MariaDbBase, withMariaDbPool } from "hibernatets";
512

513
// Batch operations for improved performance
514
class PerformanceOptimizer {
515
  static async batchInsert<T>(
516
    tableName: string,
517
    records: T[],
518
    batchSize: number = 1000
519
  ): Promise<void> {
520
    await withMariaDbPool(async (db) => {
521
      for (let i = 0; i < records.length; i += batchSize) {
522
        const batch = records.slice(i, i + batchSize);
523
        const placeholders = batch.map(() => "(?, ?)").join(", ");
524
        const values = batch.flatMap(record =>
525
          [record.name, record.email]
526
        );
527

528
        await db.sqlquery(
529
          {},
530
          `INSERT INTO ${tableName} (name, email) VALUES ${placeholders}`,
531
          values
532
        );
533
      }
534
    });
535
  }
536

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

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

549
      await db.sqlquery(
550
        {},
551
        `UPDATE ${tableName}
552
         SET name = CASE ${caseStatements} END,
553
             updated_at = NOW()
554
         WHERE id IN (${ids})`,
555
        []
556
      );
557
    });
558
  }
559

560
  static async getConnectionStats(): Promise<any> {
561
    return await withMariaDbPool(async (db) => {
562
      return await db.selectQuery(
563
        "SHOW STATUS LIKE 'Threads_%'",
564
        []
565
      );
566
    });
567
  }
568
}
569
```