23572402971

Committed 26 Mar 2026 01:08AM UTC coverage: 94.888% (-0.2%) from 95.086%

Build # 23572402971

Build Type

push

github

Committed by

rogerpadilla

Commit Message

chore: update version to 0.7.1 in CHANGELOG.md to reflect the addition of Bun SQL support and related enhancements

Coverage Stats

2890 of 3210 branches covered (90.03%)

Branch coverage included in aggregate %.

5129 of 5241 relevant lines covered (97.86%)

348.18 hits per line

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

91.28

/packages/uql-orm/src/postgres/postgresDialect.ts

import sqlstring from 'sqlstring-sqlite';
import { AbstractSqlDialect } from '../dialect/index.js';
import { buildElemMatchConditions } from '../dialect/jsonArrayElemMatchUtils.js';
import { getMeta } from '../entity/index.js';
import {
  type Dialect,
  type EntityMeta,
  type FieldKey,
  type FieldOptions,
  type JsonUpdateOp,
  type NamingStrategy,
  type QueryComparisonOptions,
  type QueryConflictPaths,
  type QueryContext,
  type QueryOptions,
  QueryRaw,
  type QuerySizeComparisonOps,
  type QueryTextSearchOptions,
  type QueryVectorSearch,
  type QueryWhereFieldOperatorMap,
  type Type,
  type VectorDistance,
} from '../type/index.js';
import { hasKeys, isJsonType } from '../util/index.js';

export class PostgresDialect extends AbstractSqlDialect {
  constructor(namingStrategy?: NamingStrategy, dialect: Dialect = 'postgres') {
    super(dialect, namingStrategy);
  }

  override normalizeValue(value: unknown): unknown {
    if (typeof value === 'boolean') return value ? 'true' : 'false';
    if (value !== null && typeof value === 'object') return Array.isArray(value) ? toPgArray(value) : value;
    return super.normalizeValue(value);
  }

  override placeholder(index: number): string {
    return `$${index}`;
  }

  override insert<E>(ctx: QueryContext, entity: Type<E>, payload: E | E[], opts?: QueryOptions): void {
    super.insert(ctx, entity, payload, opts);
    ctx.append(' ' + this.returningId(entity));
  }

  override upsert<E>(ctx: QueryContext, entity: Type<E>, conflictPaths: QueryConflictPaths<E>, payload: E | E[]): void {
    const meta = getMeta(entity);
    const update = this.getUpsertUpdateAssignments(ctx, meta, conflictPaths, payload, (name) => `EXCLUDED.${name}`);
    const keysStr = this.getUpsertConflictPathsStr(meta, conflictPaths);
    const onConflict = update ? `DO UPDATE SET ${update}` : 'DO NOTHING';
    super.insert(ctx, entity, payload);
    // xmax system column is 0 for newly inserted rows, non-zero for updated rows (MVCC).
    ctx.append(
      ` ON CONFLICT (${keysStr}) ${onConflict} ${this.returningId(entity)}, (xmax = 0) AS ${this.escapeId('_created')}`,
    );
  }

  override compare<E>(
    ctx: QueryContext,
    entity: Type<E>,
    key: string,
    val: unknown,
    opts: QueryComparisonOptions = {},
  ): void {
    if (key === '$text') {
      const meta = getMeta(entity);
      const search = val as QueryTextSearchOptions<E>;
      const fields = (search.$fields ?? [])
        .map((fKey) => {
          const field = meta.fields[fKey];
          const columnName = this.resolveColumnName(fKey, field!);
          return this.escapeId(columnName);
        })
        .join(` || ' ' || `);
      ctx.append(`to_tsvector(${fields}) @@ to_tsquery(`);
      ctx.addValue(search.$value);
      ctx.append(')');
      return;
    }
    super.compare(ctx, entity, key, val, opts);
  }

  override compareFieldOperator<E, K extends keyof QueryWhereFieldOperatorMap<E>>(
    ctx: QueryContext,
    entity: Type<E>,
    key: FieldKey<E>,
    op: K,
    val: QueryWhereFieldOperatorMap<E>[K],
    opts: QueryOptions = {},
  ): void {
    switch (op) {
      case '$elemMatch':
        this.buildElemMatchCondition(ctx, entity, key, val as Record<string, unknown>, opts);
        break;
      case '$all':
        // PostgreSQL: JSONB array contains all specified values
        // e.g., tags @> '["typescript", "orm"]'::jsonb
        this.getComparisonKey(ctx, entity, key, opts);
        ctx.append(' @> ');
        ctx.addValue(JSON.stringify(val));
        ctx.append('::jsonb');
        break;
      case '$size':
        // PostgreSQL: Check JSONB array length
        // e.g., jsonb_array_length(roles) = 3, or jsonb_array_length(roles) >= 2
        this.buildSizeComparison(
          ctx,
          () => {
            ctx.append('jsonb_array_length(');
            this.getComparisonKey(ctx, entity, key, opts);
            ctx.append(')');
          },
          val as number | QuerySizeComparisonOps,
        );
        break;
      default:
        super.compareFieldOperator(ctx, entity, key, op, val, opts);
    }
  }

  /**
   * Build $elemMatch condition for PostgreSQL JSONB arrays.
   * - Simple objects (no operators): Use fast @> containment
   * - Objects with operators ($ilike, $regex, etc.): Use EXISTS subquery
   */
  private buildElemMatchCondition<E>(
    ctx: QueryContext,
    entity: Type<E>,
    key: FieldKey<E>,
    match: Record<string, unknown>,
    opts: QueryOptions,
  ): void {
    // Check if any field value contains operators
    const hasOperators = Object.values(match).some(
      (v) => v && typeof v === 'object' && !Array.isArray(v) && Object.keys(v).some((k) => k.startsWith('$')),
    );

    if (!hasOperators) {
      // Simple case: use fast @> containment operator
      // e.g., addresses @> '[{"city": "NYC"}]'::jsonb
      this.getComparisonKey(ctx, entity, key, opts);
      ctx.append(' @> ');
      ctx.addValue(JSON.stringify([match]));
      ctx.append('::jsonb');
      return;
    }

    // Complex case: use EXISTS with jsonb_array_elements
    // e.g., EXISTS (SELECT 1 FROM jsonb_array_elements(addresses) AS elem WHERE elem->>'city' ILIKE $1)
    ctx.append('EXISTS (SELECT 1 FROM jsonb_array_elements(');
    this.getComparisonKey(ctx, entity, key, opts);
    ctx.append(') AS elem WHERE ');

    const conditions = buildElemMatchConditions(
      match,
      (field, op, opVal) =>
        this.buildJsonFieldCondition(ctx, (f) => `elem->>'${this.escapeJsonKey(f)}'`, field, op, opVal),
      (field, value) => `elem->>'${this.escapeJsonKey(field)}' = ${this.addValue(ctx.values, value)}`,
    );

    ctx.append(conditions.join(' AND '));
    ctx.append(')');
  }

  protected override get regexpOp(): string {
    return '~';
  }

  protected override ilikeExpr(f: string, ph: string): string {
    return `${f} ILIKE ${ph}`;
  }

  protected override get neOp(): string {
    return 'IS DISTINCT FROM';
  }

  protected override formatIn(ctx: QueryContext, values: unknown[], negate: boolean): string {
    if (values.length === 0) return negate ? ' NOT IN (NULL)' : ' IN (NULL)';
    const ph = this.addValue(ctx.values, values);
    return negate ? ` <> ALL(${ph})` : ` = ANY(${ph})`;
  }

  protected override numericCast(expr: string): string {
    return `(${expr})::numeric`;
  }

  protected override formatPersistableValue<E>(ctx: QueryContext, field: FieldOptions, value: unknown): void {
    if (value instanceof QueryRaw) {
      super.formatPersistableValue(ctx, field, value);
      return;
    }
    if (isJsonType(field.type)) {
      ctx.addValue(value ? JSON.stringify(value) : null);
      ctx.append(`::${field.type}`);
      return;
    }
    if (field.type === 'vector' && Array.isArray(value)) {
      ctx.addValue(`[${value.join(',')}]`);
      ctx.append('::vector');
      return;
    }
    super.formatPersistableValue(ctx, field, value);
  }

  /** pgvector distance operators. */
  private static readonly VECTOR_OPS: Record<VectorDistance, string> = {
    cosine: '<=>',
    l2: '<->',
    inner: '<#>',
    l1: '<+>',
    hamming: '<~>',
  };

  /** Emit a pgvector distance expression: `"col" <op> $N::<vectorType>`. */
  protected override appendVectorSort<E>(
    ctx: QueryContext,
    meta: EntityMeta<E>,
    key: string,
    search: QueryVectorSearch,
  ): void {
    const { colName, distance, vectorCast } = this.resolveVectorSortParams(meta, key, search);
    const op = PostgresDialect.VECTOR_OPS[distance];
    ctx.append(`${this.escapeId(colName)} ${op} `);
    ctx.addValue(`[${search.$vector.join(',')}]`);
    ctx.append(`::${vectorCast}`);
  }

  protected override formatJsonUpdate<E>(ctx: QueryContext, escapedCol: string, value: JsonUpdateOp<E>): void {
    let expr = escapedCol;
    if (hasKeys(value.$merge)) {
      ctx.pushValue(JSON.stringify(value.$merge));
      expr = `COALESCE(${escapedCol}, '{}') || ${this.placeholder(ctx.values.length)}::jsonb`;
    }
    if (hasKeys(value.$push)) {
      const push = value.$push as Record<string, unknown>;
      for (const [key, v] of Object.entries(push)) {
        const currentExpr = expr;
        ctx.pushValue(JSON.stringify(v));
        const ph = this.placeholder(ctx.values.length);
        expr = `jsonb_set(${currentExpr}, '{${this.escapeJsonKey(key)}}', COALESCE((${currentExpr})->'${this.escapeJsonKey(
          key,
        )}', '[]'::jsonb) || jsonb_build_array(${ph}::jsonb))`;
      }
    }
    if (value.$unset?.length) {
      for (const key of value.$unset) {
        expr = `(${expr}) - '${this.escapeJsonKey(key)}'`;
      }
    }
    ctx.append(`${escapedCol} = ${expr}`);
  }

  override escape(value: unknown): string {
    return sqlstring.escape(value);
  }
}

/**
 * Converts a JS array to a Postgres array literal string: `{"val1","val2"}`.
 * Safely handles nesting and escaping of special characters.
 */
function toPgArray(arr: any[]): string {
  const elements = arr.map((val) => {
    if (val == null) return 'NULL';
    if (Array.isArray(val)) return toPgArray(val);
    if (typeof val === 'boolean') return val ? 'true' : 'false';
    if (val instanceof Uint8Array || (typeof Buffer !== 'undefined' && Buffer.isBuffer(val))) {
      const hex = Array.from(val)
        .map((b) => b.toString(16).padStart(2, '0'))
        .join('');
      return `"\\\\x${hex}"`;
    }
    const str = String(val);
    const escaped = str.replace(/\\/g, '\\\\').replace(/"/g, '\\"');
    return `"${escaped}"`;
  });
  return `{${elements.join(',')}}`;
}

1	import sqlstring from 'sqlstring-sqlite';
2	import { AbstractSqlDialect } from '../dialect/index.js';
3	import { buildElemMatchConditions } from '../dialect/jsonArrayElemMatchUtils.js';
4	import { getMeta } from '../entity/index.js';
5	import {
6	type Dialect,
7	type EntityMeta,
8	type FieldKey,
9	type FieldOptions,
10	type JsonUpdateOp,
11	type NamingStrategy,
12	type QueryComparisonOptions,
13	type QueryConflictPaths,
14	type QueryContext,
15	type QueryOptions,
16	QueryRaw,
17	type QuerySizeComparisonOps,
18	type QueryTextSearchOptions,
19	type QueryVectorSearch,
20	type QueryWhereFieldOperatorMap,
21	type Type,
22	type VectorDistance,
23	} from '../type/index.js';
24	import { hasKeys, isJsonType } from '../util/index.js';
25
26	export class PostgresDialect extends AbstractSqlDialect {
27	constructor(namingStrategy?: NamingStrategy, dialect: Dialect = 'postgres') {	26✔
28	super(dialect, namingStrategy);	26✔
29	}
30
31	override normalizeValue(value: unknown): unknown {
32	if (typeof value === 'boolean') return value ? 'true' : 'false';	1,774!
33	if (value !== null && typeof value === 'object') return Array.isArray(value) ? toPgArray(value) : value;	1,773✔
34	return super.normalizeValue(value);	1,673✔
35	}
36
37	override placeholder(index: number): string {
38	return `$${index}`;	636✔
39	}
40
41	override insert<E>(ctx: QueryContext, entity: Type<E>, payload: E \| E[], opts?: QueryOptions): void {
42	super.insert(ctx, entity, payload, opts);	79✔
43	ctx.append(' ' + this.returningId(entity));	79✔
44	}
45
46	override upsert<E>(ctx: QueryContext, entity: Type<E>, conflictPaths: QueryConflictPaths<E>, payload: E \| E[]): void {
47	const meta = getMeta(entity);	14✔
48	const update = this.getUpsertUpdateAssignments(ctx, meta, conflictPaths, payload, (name) => `EXCLUDED.${name}`);	14✔
49	const keysStr = this.getUpsertConflictPathsStr(meta, conflictPaths);	14✔
50	const onConflict = update ? `DO UPDATE SET ${update}` : 'DO NOTHING';	14✔
51	super.insert(ctx, entity, payload);	14✔
52	// xmax system column is 0 for newly inserted rows, non-zero for updated rows (MVCC).
53	ctx.append(	14✔
54	` ON CONFLICT (${keysStr}) ${onConflict} ${this.returningId(entity)}, (xmax = 0) AS ${this.escapeId('_created')}`,
55	);
56	}
57
58	override compare<E>(
59	ctx: QueryContext,
60	entity: Type<E>,
61	key: string,
62	val: unknown,
63	opts: QueryComparisonOptions = {},	311✔
64	): void {
65	if (key === '$text') {	311✔
66	const meta = getMeta(entity);	2✔
67	const search = val as QueryTextSearchOptions<E>;	2✔
68	const fields = (search.$fields ?? [])	2!
69	.map((fKey) => {
70	const field = meta.fields[fKey];	3✔
71	const columnName = this.resolveColumnName(fKey, field!);	3✔
72	return this.escapeId(columnName);	3✔
73	})
74	.join(` \|\| ' ' \|\| `);
75	ctx.append(`to_tsvector(${fields}) @@ to_tsquery(`);	2✔
76	ctx.addValue(search.$value);	2✔
77	ctx.append(')');	2✔
78	return;	2✔
79	}
80	super.compare(ctx, entity, key, val, opts);	309✔
81	}
82
83	override compareFieldOperator<E, K extends keyof QueryWhereFieldOperatorMap<E>>(
84	ctx: QueryContext,
85	entity: Type<E>,
86	key: FieldKey<E>,
87	op: K,
88	val: QueryWhereFieldOperatorMap<E>[K],
89	opts: QueryOptions = {},	299✔
90	): void {
91	switch (op) {	299✔
92	case '$elemMatch':
93	this.buildElemMatchCondition(ctx, entity, key, val as Record<string, unknown>, opts);	9✔
94	break;	9✔
95	case '$all':
96	// PostgreSQL: JSONB array contains all specified values
97	// e.g., tags @> '["typescript", "orm"]'::jsonb
98	this.getComparisonKey(ctx, entity, key, opts);	1✔
99	ctx.append(' @> ');	1✔
100	ctx.addValue(JSON.stringify(val));	1✔
101	ctx.append('::jsonb');	1✔
102	break;	1✔
103	case '$size':
104	// PostgreSQL: Check JSONB array length
105	// e.g., jsonb_array_length(roles) = 3, or jsonb_array_length(roles) >= 2
106	this.buildSizeComparison(	4✔
107	ctx,
108	() => {
109	ctx.append('jsonb_array_length(');	5✔
110	this.getComparisonKey(ctx, entity, key, opts);	5✔
111	ctx.append(')');	5✔
112	},
113	val as number \| QuerySizeComparisonOps,
114	);
115	break;	4✔
116	default:
117	super.compareFieldOperator(ctx, entity, key, op, val, opts);	285✔
118	}
119	}
120
121	/**
122	* Build $elemMatch condition for PostgreSQL JSONB arrays.
123	* - Simple objects (no operators): Use fast @> containment
124	* - Objects with operators ($ilike, $regex, etc.): Use EXISTS subquery
125	*/
126	private buildElemMatchCondition<E>(
127	ctx: QueryContext,
128	entity: Type<E>,
129	key: FieldKey<E>,
130	match: Record<string, unknown>,
131	opts: QueryOptions,
132	): void {
133	// Check if any field value contains operators
134	const hasOperators = Object.values(match).some(	9✔
135	(v) => v && typeof v === 'object' && !Array.isArray(v) && Object.keys(v).some((k) => k.startsWith('$')),	11✔
136	);
137
138	if (!hasOperators) {	9✔
139	// Simple case: use fast @> containment operator
140	// e.g., addresses @> '[{"city": "NYC"}]'::jsonb
141	this.getComparisonKey(ctx, entity, key, opts);	1✔
142	ctx.append(' @> ');	1✔
143	ctx.addValue(JSON.stringify([match]));	1✔
144	ctx.append('::jsonb');	1✔
145	return;	1✔
146	}
147
148	// Complex case: use EXISTS with jsonb_array_elements
149	// e.g., EXISTS (SELECT 1 FROM jsonb_array_elements(addresses) AS elem WHERE elem->>'city' ILIKE $1)
150	ctx.append('EXISTS (SELECT 1 FROM jsonb_array_elements(');	8✔
151	this.getComparisonKey(ctx, entity, key, opts);	8✔
152	ctx.append(') AS elem WHERE ');	8✔
153
154	const conditions = buildElemMatchConditions(	8✔
155	match,
156	(field, op, opVal) =>
157	this.buildJsonFieldCondition(ctx, (f) => `elem->>'${this.escapeJsonKey(f)}'`, field, op, opVal),	17✔
158	(field, value) => `elem->>'${this.escapeJsonKey(field)}' = ${this.addValue(ctx.values, value)}`,	1✔
159	);
160
161	ctx.append(conditions.join(' AND '));	8✔
162	ctx.append(')');	8✔
163	}
164
165	protected override get regexpOp(): string {
166	return '~';	2✔
167	}
168
169	protected override ilikeExpr(f: string, ph: string): string {
170	return `${f} ILIKE ${ph}`;	13✔
171	}
172
173	protected override get neOp(): string {
174	return 'IS DISTINCT FROM';	7✔
175	}
176
177	protected override formatIn(ctx: QueryContext, values: unknown[], negate: boolean): string {
178	if (values.length === 0) return negate ? ' NOT IN (NULL)' : ' IN (NULL)';	98!
179	const ph = this.addValue(ctx.values, values);	98✔
180	return negate ? ` <> ALL(${ph})` : ` = ANY(${ph})`;	98✔
181	}
182
183	protected override numericCast(expr: string): string {
184	return `(${expr})::numeric`;	6✔
185	}
186
187	protected override formatPersistableValue<E>(ctx: QueryContext, field: FieldOptions, value: unknown): void {
188	if (value instanceof QueryRaw) {	406✔
189	super.formatPersistableValue(ctx, field, value);	1✔
190	return;	1✔
191	}
192	if (isJsonType(field.type)) {	405✔
193	ctx.addValue(value ? JSON.stringify(value) : null);	1!
194	ctx.append(`::${field.type}`);	1✔
195	return;	1✔
196	}
197	if (field.type === 'vector' && Array.isArray(value)) {	404✔
198	ctx.addValue(`[${value.join(',')}]`);	15✔
199	ctx.append('::vector');	15✔
200	return;	15✔
201	}
202	super.formatPersistableValue(ctx, field, value);	389✔
203	}
204
205	/** pgvector distance operators. */
206	private static readonly VECTOR_OPS: Record<VectorDistance, string> = {	12✔
207	cosine: '<=>',
208	l2: '<->',
209	inner: '<#>',
210	l1: '<+>',
211	hamming: '<~>',
212	};
213
214	/** Emit a pgvector distance expression: `"col" <op> $N::<vectorType>`. */
215	protected override appendVectorSort<E>(
216	ctx: QueryContext,
217	meta: EntityMeta<E>,
218	key: string,
219	search: QueryVectorSearch,
220	): void {
221	const { colName, distance, vectorCast } = this.resolveVectorSortParams(meta, key, search);	13✔
222	const op = PostgresDialect.VECTOR_OPS[distance];	13✔
223	ctx.append(`${this.escapeId(colName)} ${op} `);	13✔
224	ctx.addValue(`[${search.$vector.join(',')}]`);	13✔
225	ctx.append(`::${vectorCast}`);	13✔
226	}
227
228	protected override formatJsonUpdate<E>(ctx: QueryContext, escapedCol: string, value: JsonUpdateOp<E>): void {
229	let expr = escapedCol;	7✔
230	if (hasKeys(value.$merge)) {	7✔
231	ctx.pushValue(JSON.stringify(value.$merge));	4✔
232	expr = `COALESCE(${escapedCol}, '{}') \|\| ${this.placeholder(ctx.values.length)}::jsonb`;	4✔
233	}
234	if (hasKeys(value.$push)) {	7✔
235	const push = value.$push as Record<string, unknown>;	4✔
236	for (const [key, v] of Object.entries(push)) {	4✔
237	const currentExpr = expr;	4✔
238	ctx.pushValue(JSON.stringify(v));	4✔
239	const ph = this.placeholder(ctx.values.length);	4✔
240	expr = `jsonb_set(${currentExpr}, '{${this.escapeJsonKey(key)}}', COALESCE((${currentExpr})->'${this.escapeJsonKey(	4✔
241	key,
242	)}', '[]'::jsonb) \|\| jsonb_build_array(${ph}::jsonb))`;
243	}
244	}
245	if (value.$unset?.length) {	7✔
246	for (const key of value.$unset) {	3✔
247	expr = `(${expr}) - '${this.escapeJsonKey(key)}'`;	4✔
248	}
249	}
250	ctx.append(`${escapedCol} = ${expr}`);	7✔
251	}
252
253	override escape(value: unknown): string {
254	return sqlstring.escape(value);	1✔
255	}
256	}
257
258	/**
259	* Converts a JS array to a Postgres array literal string: `{"val1","val2"}`.
260	* Safely handles nesting and escaping of special characters.
261	*/
262	function toPgArray(arr: any[]): string {
263	const elements = arr.map((val) => {	98✔
264	if (val == null) return 'NULL';	163!
265	if (Array.isArray(val)) return toPgArray(val);	163!
266	if (typeof val === 'boolean') return val ? 'true' : 'false';	163!
267	if (val instanceof Uint8Array \|\| (typeof Buffer !== 'undefined' && Buffer.isBuffer(val))) {	163!
268	const hex = Array.from(val)	×
269	.map((b) => b.toString(16).padStart(2, '0'))	×
270	.join('');
271	return `"\\\\x${hex}"`;	×
272	}
273	const str = String(val);	163✔
274	const escaped = str.replace(/\\/g, '\\\\').replace(/"/g, '\\"');	163✔
275	return `"${escaped}"`;	163✔
276	});
277	return `{${elements.join(',')}}`;	98✔
278	}

rogerpadilla / uql / 23572402971

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

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