• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

vzakharchenko / forge-sql-orm / 18980807892

31 Oct 2025 05:48PM UTC coverage: 80.264% (+3.6%) from 76.616%
18980807892

push

github

vzakharchenko
added dev Observability

457 of 643 branches covered (71.07%)

Branch coverage included in aggregate %.

22 of 35 new or added lines in 6 files covered. (62.86%)

13 existing lines in 2 files now uncovered.

942 of 1100 relevant lines covered (85.64%)

16.11 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

83.5
/src/utils/sqlUtils.ts
1
import {
2
  and,
3
  AnyColumn,
4
  Column,
5
  gte,
6
    ilike,
7
  isNotNull,
8
  isTable,
9
    ne,
10
    not,
11
  notInArray,
12
  SQL,
13
  sql,
14
  StringChunk,
15
} from "drizzle-orm";
16
import { AnyMySqlTable, MySqlCustomColumn } from "drizzle-orm/mysql-core/index";
17
import { DateTime } from "luxon";
18
import { PrimaryKeyBuilder } from "drizzle-orm/mysql-core/primary-keys";
19
import { AnyIndexBuilder } from "drizzle-orm/mysql-core/indexes";
20
import { CheckBuilder } from "drizzle-orm/mysql-core/checks";
21
import { ForeignKeyBuilder } from "drizzle-orm/mysql-core/foreign-keys";
22
import { UniqueConstraintBuilder } from "drizzle-orm/mysql-core/unique-constraint";
23
import {
24
    SelectedFields
25
} from "drizzle-orm/mysql-core/query-builders/select.types";
26
import { MySqlTable } from "drizzle-orm/mysql-core";
27
import { isSQLWrapper } from "drizzle-orm/sql/sql";
28
import {clusterStatementsSummary, slowQuery} from "../core/SystemTables";
29
import { ForgeSqlOperation } from "../core/ForgeSQLQueryBuilder";
30
import { ColumnDataType} from "drizzle-orm/column-builder";
31
import {AnyMySqlColumn} from "drizzle-orm/mysql-core/columns/common";
32
import type {ColumnBaseConfig} from "drizzle-orm/column";
33

34
/**
35
 * Interface representing table metadata information
36
 */
37
export interface MetadataInfo {
38
  /** The name of the table */
39
  tableName: string;
40
  /** Record of column names and their corresponding column definitions */
41
  columns: Record<string, AnyColumn>;
42
  /** Array of index builders */
43
  indexes: AnyIndexBuilder[];
44
  /** Array of check constraint builders */
45
  checks: CheckBuilder[];
46
  /** Array of foreign key builders */
47
  foreignKeys: ForeignKeyBuilder[];
48
  /** Array of primary key builders */
49
  primaryKeys: PrimaryKeyBuilder[];
50
  /** Array of unique constraint builders */
51
  uniqueConstraints: UniqueConstraintBuilder[];
52
  /** Array of all extra builders */
53
  extras: any[];
54
}
55

56
/**
57
 * Interface for config builder data
58
 */
59
interface ConfigBuilderData {
60
  value?: any;
61
  [key: string]: any;
62
}
63

64
/**
65
 * Parses a date string into a Date object using the specified format
66
 * @param value - The date string to parse or Date
67
 * @param format - The format to use for parsing
68
 * @returns Date object
69
 */
4✔
70

71
export const parseDateTime = (value: string | Date, format: string): Date => {
17✔
72
  let result: Date;
2✔
73
  if (value instanceof Date) {
74
    result = value;
75
  } else {
15✔
76
    // 1. Try to parse using the provided format (strict mode)
15✔
77
    const dt = DateTime.fromFormat(value, format);
3✔
78
    if (dt.isValid) {
79
      result = dt.toJSDate();
80
    } else {
12✔
81
      // 2. Try to parse as SQL string
12✔
82
      const sqlDt = DateTime.fromSQL(value);
4✔
83
      if (sqlDt.isValid) {
84
        result = sqlDt.toJSDate();
85
      } else {
8✔
86
        // 3. Try to parse as RFC2822 string
8!
UNCOV
87
        const isoDt = DateTime.fromRFC2822(value);
×
88
        if (isoDt.isValid) {
89
          result = isoDt.toJSDate();
90
        } else {
8✔
91
          // 4. Fallback: use native Date constructor
92
          result = new Date(value);
93
        }
94
      }
95
    }
96
  }
17✔
97
  // 4. Ensure the result is a valid Date object
5✔
98
  if (isNaN(result.getTime())) {
99
    result = new Date(value);
17✔
100
  }
101
  return result;
102
};
103

104
/**
105
 * Helper function to validate and format a date-like value using Luxon DateTime.
106
 * @param value - Date object, ISO/RFC2822/SQL/HTTP string, or timestamp (number|string).
107
 * @param format - DateTime format string (Luxon format tokens).
108
 * @returns Formatted date string.
109
 * @throws Error if value cannot be parsed as a valid date.
110
 */
111
export function formatDateTime(
112
  value: Date | string | number,
113
  format: string,
114
  isTimeStamp: boolean,
22✔
115
): string {
116
  let dt: DateTime | null = null;
22✔
117

17✔
118
  if (value instanceof Date) {
5✔
119
    dt = DateTime.fromJSDate(value);
3✔
120
  } else if (typeof value === "string") {
121
    for (const parser of [
122
      DateTime.fromISO,
123
      DateTime.fromRFC2822,
124
      DateTime.fromSQL,
125
      DateTime.fromHTTP,
8✔
126
    ]) {
8✔
127
      dt = parser(value);
128
      if (dt.isValid) break;
3✔
129
    }
1✔
130
    if (!dt?.isValid) {
1!
131
      const parsed = Number(value);
×
132
      if (!isNaN(parsed)) {
133
        dt = DateTime.fromMillis(parsed);
134
      }
2✔
135
    }
1✔
136
  } else if (typeof value === "number") {
137
    dt = DateTime.fromMillis(value);
1✔
138
  } else {
139
    throw new Error("Unsupported type");
140
  }
21✔
141

1✔
142
  if (!dt?.isValid) {
143
    throw new Error("Invalid Date");
20✔
144
  }
20✔
145
  const minDate = DateTime.fromSeconds(1);
146
  const maxDate = DateTime.fromMillis(2147483647 * 1000); // 2038-01-19 03:14:07.999 UTC
20✔
147

5✔
148
  if (isTimeStamp) {
1✔
149
    if (dt < minDate) {
150
      throw new Error(
151
        "Atlassian Forge does not support zero or negative timestamps. Allowed range: from '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.",
152
      );
4✔
153
    }
1✔
154
    if (dt > maxDate) {
155
      throw new Error(
156
        "Atlassian Forge does not support timestamps beyond 2038-01-19 03:14:07.999999. Please use a smaller date within the supported range.",
157
      );
158
    }
159
  }
18✔
160

161
  return dt.toFormat(format);
162
}
163

164
/**
165
 * Gets primary keys from the schema.
166
 * @template T - The type of the table schema
167
 * @param {T} table - The table schema
168
 * @returns {[string, AnyColumn][]} Array of primary key name and column pairs
169
 */
14✔
170
export function getPrimaryKeys<T extends AnyMySqlTable>(table: T): [string, AnyColumn][] {
171
  const { columns, primaryKeys } = getTableMetadata(table);
172

38✔
173
  // First try to find primary keys in columns
174
  const columnPrimaryKeys = Object.entries(columns).filter(([, column]) => column.primary) as [
175
    string,
176
    AnyColumn,
177
  ][];
14✔
178

10✔
179
  if (columnPrimaryKeys.length > 0) {
180
    return columnPrimaryKeys;
181
  }
182

4!
183
  // If no primary keys found in columns, check primary key builders
184
  if (Array.isArray(primaryKeys) && primaryKeys.length > 0) {
4✔
185
    // Collect all primary key columns from all primary key builders
186
    const primaryKeyColumns = new Set<[string, AnyColumn]>();
4✔
187

188
    primaryKeys.forEach((primaryKeyBuilder) => {
4✔
189
      // Get primary key columns from each builder
190
      Object.entries(columns)
191
        .filter(([, column]) => {
8✔
192
          // @ts-ignore - PrimaryKeyBuilder has internal columns property
193
          return primaryKeyBuilder.columns.includes(column);
194
        })
4✔
195
        .forEach(([name, column]) => {
196
          primaryKeyColumns.add([name, column]);
197
        });
198
    });
4✔
199

200
    return Array.from(primaryKeyColumns);
UNCOV
201
  }
×
202

203
  return [];
204
}
205

206
/**
207
 * Processes foreign keys from both foreignKeysSymbol and extraSymbol
208
 * @param table - The table schema
209
 * @param foreignKeysSymbol - Symbol for foreign keys
210
 * @param extraSymbol - Symbol for extra configuration
211
 * @returns Array of foreign key builders
212
 */
213
function processForeignKeys(
214
  table: AnyMySqlTable,
215
  foreignKeysSymbol: symbol | undefined,
216
  extraSymbol: symbol | undefined,
46✔
217
): ForeignKeyBuilder[] {
218
  const foreignKeys: ForeignKeyBuilder[] = [];
219

46!
220
  // Process foreign keys from foreignKeysSymbol
221
  if (foreignKeysSymbol) {
46✔
222
    // @ts-ignore
46!
223
    const fkArray: any[] = table[foreignKeysSymbol];
46✔
UNCOV
224
    if (fkArray) {
×
UNCOV
225
      fkArray.forEach((fk) => {
×
226
        if (fk.reference) {
×
227
          const item = fk.reference(fk);
228
          foreignKeys.push(item);
229
        }
230
      });
231
    }
232
  }
233

46!
234
  // Process foreign keys from extraSymbol
235
  if (extraSymbol) {
46✔
236
    // @ts-ignore
46✔
237
    const extraConfigBuilder = table[extraSymbol];
21✔
238
    if (extraConfigBuilder && typeof extraConfigBuilder === "function") {
21!
239
      const configBuilderData = extraConfigBuilder(table);
21!
240
      if (configBuilderData) {
241
        const configBuilders = Array.isArray(configBuilderData)
UNCOV
242
          ? configBuilderData
×
243
          : Object.values(configBuilderData).map(
244
              (item) => (item as ConfigBuilderData).value ?? item,
245
            );
21✔
246

21!
247
        configBuilders.forEach((builder) => {
248
          if (!builder?.constructor) return;
21✔
249

21!
UNCOV
250
          const builderName = builder.constructor.name.toLowerCase();
×
251
          if (builderName.includes("foreignkeybuilder")) {
252
            foreignKeys.push(builder);
253
          }
254
        });
255
      }
256
    }
257
  }
46✔
258

259
  return foreignKeys;
260
}
261

262
/**
263
 * Extracts table metadata from the schema.
264
 * @param {AnyMySqlTable} table - The table schema
265
 * @returns {MetadataInfo} Object containing table metadata
266
 */
46✔
267
export function getTableMetadata(table: AnyMySqlTable): MetadataInfo {
46✔
268
  const symbols = Object.getOwnPropertySymbols(table);
184✔
269
  const nameSymbol = symbols.find((s) => s.toString().includes("Name"));
460✔
270
  const columnsSymbol = symbols.find((s) => s.toString().includes("Columns"));
414✔
271
  const foreignKeysSymbol = symbols.find((s) => s.toString().includes("ForeignKeys)"));
272
  const extraSymbol = symbols.find((s) => s.toString().includes("ExtraConfigBuilder"));
273

46✔
274
  // Initialize builders arrays
275
  const builders = {
276
    indexes: [] as AnyIndexBuilder[],
277
    checks: [] as CheckBuilder[],
278
    foreignKeys: [] as ForeignKeyBuilder[],
279
    primaryKeys: [] as PrimaryKeyBuilder[],
280
    uniqueConstraints: [] as UniqueConstraintBuilder[],
281
    extras: [] as any[],
282
  };
283

46✔
284
  // Process foreign keys
285
  builders.foreignKeys = processForeignKeys(table, foreignKeysSymbol, extraSymbol);
286

46!
287
  // Process extra configuration if available
288
  if (extraSymbol) {
46✔
289
    // @ts-ignore
46✔
290
    const extraConfigBuilder = table[extraSymbol];
21✔
291
    if (extraConfigBuilder && typeof extraConfigBuilder === "function") {
21!
292
      const configBuilderData = extraConfigBuilder(table);
293
      if (configBuilderData) {
21!
294
        // Convert configBuilderData to array if it's an object
295
        const configBuilders = Array.isArray(configBuilderData)
UNCOV
296
          ? configBuilderData
×
297
          : Object.values(configBuilderData).map(
298
              (item) => (item as ConfigBuilderData).value ?? item,
299
            );
300

21✔
301
        // Process each builder
21!
302
        configBuilders.forEach((builder) => {
303
          if (!builder?.constructor) return;
21✔
304

305
          const builderName = builder.constructor.name.toLowerCase();
306

21✔
307
          // Map builder types to their corresponding arrays
308
          const builderMap = {
309
            indexbuilder: builders.indexes,
310
            checkbuilder: builders.checks,
311
            primarykeybuilder: builders.primaryKeys,
312
            uniqueconstraintbuilder: builders.uniqueConstraints,
313
          };
314

21✔
315
          // Add builder to appropriate array if it matches any type
63✔
316
          for (const [type, array] of Object.entries(builderMap)) {
21✔
317
            if (builderName.includes(type)) {
21✔
318
              array.push(builder);
319
              break;
320
            }
321
          }
322

21✔
323
          // Always add to extras array
324
          builders.extras.push(builder);
325
        });
326
      }
327
    }
328
  }
46✔
329

46!
330
  return {
46!
331
    tableName: nameSymbol ? (table as any)[nameSymbol] : "",
332
    columns: columnsSymbol ? ((table as any)[columnsSymbol] as Record<string, AnyColumn>) : {},
333
    ...builders,
334
  };
335
}
336

337
/**
338
 * Generates SQL statements for dropping tables and/or their sequences.
339
 *
340
 * @param tables - List of table names to generate DROP statements for.
341
 * @param options - Configuration object:
342
 *   - sequence: whether to drop associated sequences (default: true)
343
 *   - table: whether to drop tables themselves (default: true)
344
 * @returns Array of SQL statements for dropping the specified objects
345
 */
346
export function generateDropTableStatements(
347
  tables: string[],
348
  options?: { sequence: boolean; table: boolean },
7✔
349
): string[] {
7✔
350
  const dropStatements: string[] = [];
7✔
351
  const validOptions = options ?? { sequence: true, table: true };
352
  if (!validOptions.sequence && !validOptions.table) {
1✔
353
    // eslint-disable-next-line no-console
1✔
354
    console.warn('No drop operations requested: both "table" and "sequence" options are false');
355
    return [];
6✔
356
  }
8✔
357
  tables.forEach((tableName) => {
7✔
358
    if (validOptions.table) {
359
      dropStatements.push(`DROP TABLE IF EXISTS \`${tableName}\`;`);
8✔
360
    }
7✔
361
    if (validOptions.sequence) {
362
      dropStatements.push(`DROP SEQUENCE IF EXISTS \`${tableName}\`;`);
363
    }
364
  });
6✔
365

366
  return dropStatements;
367
}
368

369
type AliasColumnMap = Record<string, AnyColumn>;
370

371
function mapSelectTableToAlias(
372
  table: MySqlTable,
373
  uniqPrefix: string,
374
  aliasMap: AliasColumnMap,
10✔
375
): any {
10✔
376
  const { columns, tableName } = getTableMetadata(table);
10✔
377
  const selectionsTableFields: Record<string, unknown> = {};
39✔
378
  Object.keys(columns).forEach((name) => {
39✔
379
    const column = columns[name] as AnyColumn;
39✔
380
    const uniqName = `a_${uniqPrefix}_${tableName}_${column.name}`.toLowerCase();
39✔
381
    const fieldAlias = sql.raw(uniqName);
39✔
382
    selectionsTableFields[name] = sql`${column} as \`${fieldAlias}\``;
383
    aliasMap[uniqName] = column;
10✔
384
  });
385
  return selectionsTableFields;
386
}
387

80✔
388
function isDrizzleColumn(column: any): boolean {
389
  return column && typeof column === "object" && "table" in column;
390
}
391

392
export function mapSelectAllFieldsToAlias(
393
  selections: any,
394
  name: string,
395
  uniqName: string,
396
  fields: any,
397
  aliasMap: AliasColumnMap,
90✔
398
): any {
10✔
399
  if (isTable(fields)) {
80✔
400
    selections[name] = mapSelectTableToAlias(fields as MySqlTable, uniqName, aliasMap);
59✔
401
  } else if (isDrizzleColumn(fields)) {
59✔
402
    const column = fields as Column;
59✔
403
    const uniqAliasName = `a_${uniqName}_${column.name}`.toLowerCase();
59✔
404
    let aliasName = sql.raw(uniqAliasName);
59✔
405
    selections[name] = sql`${column} as \`${aliasName}\``;
21✔
406
    aliasMap[uniqAliasName] = column;
10✔
407
  } else if (isSQLWrapper(fields)) {
408
    selections[name] = fields;
11✔
409
  } else {
11✔
410
    const innerSelections: any = {};
22✔
411
    Object.entries(fields).forEach(([iname, ifields]) => {
412
      mapSelectAllFieldsToAlias(innerSelections, iname, `${uniqName}_${iname}`, ifields, aliasMap);
11✔
413
    });
414
    selections[name] = innerSelections;
90✔
415
  }
416
  return selections;
417
}
418
export function mapSelectFieldsWithAlias<TSelection extends SelectedFields>(
419
  fields: TSelection,
30!
UNCOV
420
): { selections: TSelection; aliasMap: AliasColumnMap } {
×
421
  if (!fields) {
422
    throw new Error("fields is empty");
30✔
423
  }
30✔
424
  const aliasMap: AliasColumnMap = {};
30✔
425
  const selections: any = {};
68✔
426
  Object.entries(fields).forEach(([name, fields]) => {
427
    mapSelectAllFieldsToAlias(selections, name, name, fields, aliasMap);
30✔
428
  });
429
  return { selections, aliasMap };
430
}
431

432
function getAliasFromDrizzleAlias(value: unknown): string | undefined {
201✔
433
  const isSQL =
201✔
434
    value !== null && typeof value === "object" && isSQLWrapper(value) && "queryChunks" in value;
159✔
435
  if (isSQL) {
159✔
436
    const sql = value as SQL;
159✔
437
    const queryChunks = sql.queryChunks;
141✔
438
    if (queryChunks.length > 3) {
141!
439
      const aliasNameChunk = queryChunks[queryChunks.length - 2];
141✔
440
      if (isSQLWrapper(aliasNameChunk) && "queryChunks" in aliasNameChunk) {
141!
441
        const aliasNameChunkSql = aliasNameChunk as SQL;
141✔
442
        if (aliasNameChunkSql.queryChunks?.length === 1 && aliasNameChunkSql.queryChunks[0]) {
141!
443
          const queryChunksStringChunc = aliasNameChunkSql.queryChunks[0];
141✔
444
          if ("value" in queryChunksStringChunc) {
141!
445
            const values = (queryChunksStringChunc as StringChunk).value;
141✔
446
            if (values && values.length === 1) {
447
              return values[0];
448
            }
449
          }
450
        }
451
      }
452
    }
60✔
453
  }
454
  return undefined;
455
}
456

457
function transformValue(
458
  value: unknown,
459
  alias: string,
460
  aliasMap: Record<string, AnyColumn>,
141✔
461
): unknown {
141!
462
  const column = aliasMap[alias];
463
  if (!column) return value;
141✔
464

465
  let customColumn = column as MySqlCustomColumn<any>;
141✔
466
  // @ts-ignore
141✔
467
  const fromDriver = customColumn?.mapFrom;
26✔
468
  if (fromDriver && value !== null && value !== undefined) {
469
    return fromDriver(value);
115✔
470
  }
471
  return value;
472
}
473

474
function transformObject(
475
  obj: Record<string, unknown>,
476
  selections: Record<string, unknown>,
477
  aliasMap: Record<string, AnyColumn>,
72✔
478
): Record<string, unknown> {
479
  const result: Record<string, unknown> = {};
72✔
480

201✔
481
  for (const [key, value] of Object.entries(obj)) {
201✔
482
    const selection = selections[key];
201✔
483
    const alias = getAliasFromDrizzleAlias(selection);
141✔
484
    if (alias && aliasMap[alias]) {
60✔
485
      result[key] = transformValue(value, alias, aliasMap);
42✔
486
    } else if (selection && typeof selection === "object" && !isSQLWrapper(selection)) {
487
      result[key] = transformObject(
488
        value as Record<string, unknown>,
489
        selection as Record<string, unknown>,
490
        aliasMap,
491
      );
18✔
492
    } else {
493
      result[key] = value;
494
    }
495
  }
72✔
496

497
  return result;
498
}
499

500
export function applyFromDriverTransform<T, TSelection>(
501
  rows: T[],
502
  selections: TSelection,
503
  aliasMap: Record<string, AnyColumn>,
22✔
504
): T[] {
30✔
505
  return rows.map((row) => {
506
    const transformed = transformObject(
507
      row as Record<string, unknown>,
508
      selections as Record<string, unknown>,
509
      aliasMap,
510
    ) as Record<string, unknown>;
30✔
511

512
    return processNullBranches(transformed) as unknown as T;
513
  });
514
}
515

80!
UNCOV
516
function processNullBranches(obj: Record<string, unknown>): Record<string, unknown> | null {
×
517
  if (obj === null || typeof obj !== "object") {
518
    return obj;
519
  }
520

80✔
521
  // Skip built-in objects like Date, Array, etc.
8✔
522
  if (obj.constructor && obj.constructor.name !== "Object") {
523
    return obj;
524
  }
72✔
525

72✔
526
  const result: Record<string, unknown> = {};
527
  let allNull = true;
72✔
528

201✔
529
  for (const [key, value] of Object.entries(obj)) {
5✔
530
    if (value === null || value === undefined) {
5✔
531
      result[key] = null;
532
      continue;
533
    }
196✔
534

50✔
535
    if (typeof value === "object") {
50✔
536
      const processed = processNullBranches(value as Record<string, unknown>);
50✔
537
      result[key] = processed;
49✔
538
      if (processed !== null) {
539
        allNull = false;
540
      }
146✔
541
    } else {
146✔
542
      result[key] = value;
543
      allNull = false;
544
    }
545
  }
72✔
546

547
  return allNull ? null : result;
548
}
549

5✔
550
export function formatLimitOffset(limitOrOffset: number): number {
2✔
551
  if (typeof limitOrOffset !== "number" || isNaN(limitOrOffset)) {
552
    throw new Error("limitOrOffset must be a valid number");
3✔
553
  }
554
  return sql.raw(`${limitOrOffset}`) as unknown as number;
555
}
556

3✔
557
export function nextVal(sequenceName: string): number {
558
  return sql.raw(`NEXTVAL(${sequenceName})`) as unknown as number;
559
}
560

561
/**
562
 * Analyzes and prints query performance data from CLUSTER_STATEMENTS_SUMMARY table.
563
 *
564
 * This function queries the CLUSTER_STATEMENTS_SUMMARY table to find queries that were executed
565
 * within the specified time window and prints detailed performance information including:
566
 * - SQL query text
567
 * - Memory usage (average and max in MB)
568
 * - Execution time (average in ms)
569
 * - Number of executions
570
 * - Execution plan
571
 *
572
 * @param forgeSQLORM - The ForgeSQL operation instance for database access
573
 * @param timeDiffMs - Time window in milliseconds to look back for queries (e.g., 1500 for last 1.5 seconds)
574
 * @param timeout - Optional timeout in milliseconds for the query execution (defaults to 1500ms)
575
 *
576
 * @example
577
 * ```typescript
578
 * // Analyze queries from the last 2 seconds
579
 * await printQueriesWithPlan(forgeSQLORM, 2000);
580
 *
581
 * // Analyze queries with custom timeout
582
 * await printQueriesWithPlan(forgeSQLORM, 1000, 3000);
583
 * ```
584
 *
585
 * @throws Does not throw - errors are logged to console.debug instead
586
 */
587
export async function printQueriesWithPlan(
588
  forgeSQLORM: ForgeSqlOperation,
589
  timeDiffMs: number,
590
  timeout?: number,
1✔
591
) {
1✔
592
  try {
1✔
593
    const statementsTable = clusterStatementsSummary;
1✔
594
      const timeoutMs = timeout ?? 3000;
595
      const results = await withTimeout(
596
      forgeSQLORM
597
        .getDrizzleQueryBuilder()
598
        .select({
599
          digestText: withTidbHint(statementsTable.digestText),
600
          avgLatency: statementsTable.avgLatency,
601
          avgMem: statementsTable.avgMem,
602
          execCount: statementsTable.execCount,
603
          plan: statementsTable.plan,
604
            stmtType: statementsTable.stmtType,
605
        })
606
        .from(statementsTable)
607
        .where(
608
          and(
609
            isNotNull(statementsTable.digest),
610
              not(ilike(statementsTable.digestText, "%information_schema%")),
611
            notInArray(statementsTable.stmtType, ["Use", "Set", "Show","Commit","Rollback", "Begin"]),
612
            gte(
613
              statementsTable.lastSeen,
614
              sql`DATE_SUB
615
                            (NOW(), INTERVAL
616
                            ${timeDiffMs * 1000}
617
                            MICROSECOND
618
                            )`,
619
            ),
620
          ),
621
        ),
622
          `Timeout ${timeoutMs}ms in printQueriesWithPlan - transient timeouts are usually fine; repeated timeouts mean this diagnostic query is consistently slow and should be investigated`
623
        ,
624
      timeoutMs+200,
625
    );
626

627
    results.forEach((result) => {
628
      // Average execution time (convert from nanoseconds to milliseconds)
629
      const avgTimeMs = Number(result.avgLatency) / 1_000_000;
630
      const avgMemMB = Number(result.avgMem) / 1_000_000;
UNCOV
631

×
632
      // 1. Query info: SQL, memory, time, executions
UNCOV
633
      // eslint-disable-next-line no-console
×
634
      console.warn(
×
635
        `SQL: ${result.digestText} | Memory: ${avgMemMB.toFixed(2)} MB | Time: ${avgTimeMs.toFixed(2)} ms | stmtType: ${result.stmtType} | Executions: ${result.execCount}\n Plan:${result.plan}`,
636
      );
637
    });
UNCOV
638
  } catch (error) {
×
639
    // eslint-disable-next-line no-console
640
    console.debug(
641
      `Error occurred while retrieving query execution plan: ${error instanceof Error ? error.message : "Unknown error"}. Try again after some time`,
642
      error,
643
    );
644
  }
1✔
645
}
1!
646

647
const SESSION_ALIAS_NAME_ORM = 'orm';
648

649
/**
650
 * Analyzes and logs slow queries from the last specified number of hours.
651
 * 
4✔
652
 * This function queries the slow query system table to find queries that were executed
653
 * within the specified time window and logs detailed performance information including:
654
 * - SQL query text
655
 * - Maximum memory usage (in MB)
656
 * - Query execution time (in ms)
657
 * - Execution count
658
 * - Execution plan
659
 * 
660
 * @param forgeSQLORM - The ForgeSQL operation instance for database access
661
 * @param hours - Number of hours to look back for slow queries (e.g., 1 for last hour, 24 for last day)
662
 * @param timeout - Optional timeout in milliseconds for the query execution (defaults to 1500ms)
663
 * 
664
 * @example
665
 * ```typescript
666
 * // Analyze slow queries from the last hour
667
 * await slowQueryPerHours(forgeSQLORM, 1);
668
 * 
669
 * // Analyze slow queries from the last 24 hours with custom timeout
670
 * await slowQueryPerHours(forgeSQLORM, 24, 3000);
671
 * 
672
 * // Analyze slow queries from the last 6 hours
673
 * await slowQueryPerHours(forgeSQLORM, 6);
674
 * ```
675
 * 
676
 * @throws Does not throw - errors are logged to console.debug instead
677
 */
678
export async function slowQueryPerHours(forgeSQLORM: ForgeSqlOperation, hours:number, timeout?: number) {
679
    try {
680
        const timeoutMs = timeout ?? 1500;
681
        const results = await withTimeout(
682
            forgeSQLORM
683
                .getDrizzleQueryBuilder()
684
                .select({
685
                    query: withTidbHint(slowQuery.query),
686
                    queryTime: slowQuery.queryTime,
NEW
687
                    memMax: slowQuery.memMax,
×
NEW
688
                    plan: slowQuery.plan,
×
NEW
689
                })
×
690
                .from(slowQuery)
691
                .where(
692
                    and(
693
                        isNotNull(slowQuery.digest),
694
                        ne(slowQuery.sessionAlias, SESSION_ALIAS_NAME_ORM),
695
                        gte(
696
                            slowQuery.time,
697
                            sql`DATE_SUB
698
                            (NOW(), INTERVAL
699
                            ${hours}
700
                            HOUR
701
                            )`,
702
                        ),
703
                    ),
704
                ),
705
            `Timeout ${timeoutMs}ms in slowQueryPerHours - transient timeouts are usually fine; repeated timeouts mean this diagnostic query is consistently slow and should be investigated`,
706
            timeoutMs,
707
        );
708
       const response:string[] =[]
709
        results.forEach((result) => {
710
            // Convert memory from bytes to MB and handle null values
711
            const memMaxMB = result.memMax ? Number(result.memMax) / 1_000_000 : 0;
712

713
            const message = `Found SlowQuery SQL: ${result.query} | Memory: ${memMaxMB.toFixed(2)} MB | Time: ${result.queryTime} ms\n Plan:${result.plan}`;
714
            response.push(message);
715
            // 1. Query info: SQL, memory, time, executions
NEW
716
            // eslint-disable-next-line no-console
×
NEW
717
            console.warn(
×
718
                message,
NEW
719
            );
×
720
        });
NEW
721
        return response;
×
NEW
722
    } catch (error) {
×
723
        // eslint-disable-next-line no-console
724
        console.debug(
NEW
725
            `Error occurred while retrieving query execution plan: ${error instanceof Error ? error.message : "Unknown error"}. Try again after some time`,
×
726
            error,
NEW
727
        );
×
728
        return [`Error occurred while retrieving query execution plan: ${error instanceof Error ? error.message : "Unknown error"}`]
729
    }
NEW
730
}
×
731

×
732
/**
733
 * Executes a promise with a timeout.
NEW
734
 *
×
735
 * @param promise - The promise to execute
×
736
 * @param timeoutMs - Timeout in milliseconds
737
 * @returns Promise that resolves with the result or rejects on timeout
738
 * @throws {Error} When the operation times out
739
 */
740
export async function withTimeout<T>(promise: Promise<T>, message: string, timeoutMs: number): Promise<T> {
741
    let timeoutId: ReturnType<typeof setTimeout> | undefined;
742

743
    const timeoutPromise = new Promise<never>((_, reject) => {
744
        timeoutId = setTimeout(() => {
745
            reject(
746
                new Error(message),
747
            );
748
        }, timeoutMs);
749
    });
750

751
    try {
752
        return await Promise.race([promise, timeoutPromise]);
753
    } finally {
754
        if (timeoutId) {
755
            clearTimeout(timeoutId);
84✔
756
        }
84✔
757
    }
1✔
758
}
759

760
export function withTidbHint<TDataType extends ColumnDataType, TPartial extends Partial<ColumnBaseConfig<TDataType, string>>>(column: AnyMySqlColumn<TPartial>): AnyMySqlColumn<TPartial> {
761
    // We lie a bit to TypeScript here: at runtime this is a new SQL fragment,
84✔
762
    // but returning TExpr keeps the column type info in downstream inference.
84✔
763
    return sql`/*+ SET_VAR(tidb_session_alias=${sql.raw(`${SESSION_ALIAS_NAME_ORM}`)}) */ ${column}` as unknown as AnyMySqlColumn<TPartial>;
764
}
84!
765

84✔
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc