29711874318

Committed 20 Jul 2026 02:03AM UTC coverage: 95.079% (+0.02%) from 95.058%

Build # 29711874318

Build Type

push

github

Committed by

rogerpadilla

Commit Message

chore(release): publish

 - uql-orm@0.17.0

Coverage Stats

3310 of 3657 branches covered (90.51%)

Branch coverage included in aggregate %.

5712 of 5832 relevant lines covered (97.94%)

531.72 hits per line

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

97.96

/packages/uql-orm/src/sqlite/sqliteDialect.ts

import { AbstractSqlDialect } from '../dialect/abstractSqlDialect.js';
import { buildElemMatchConditions } from '../dialect/jsonArrayElemMatchUtils.js';
import { getMeta } from '../entity/index.js';
import type {
  DialectFeatures,
  FieldKey,
  FieldOptions,
  JsonUpdateOp,
  QueryComparisonOptions,
  QueryConflictPaths,
  QueryContext,
  QueryOptions,
  QuerySizeComparisonOps,
  QueryTextSearchOptions,
  QueryWhereFieldOperatorMap,
  Type,
  VectorDistance,
} from '../type/index.js';
import { escapeAnsiSqlLiteral } from '../util/ansiSqlLiteral.js';
import { hasKeys } from '../util/index.js';

export class SqliteDialect extends AbstractSqlDialect {
  /** Default {@link DialectFeatures} for SQLite and SQLite-derived dialects. */
  protected override readonly featureDefaults: DialectFeatures = {
    explicitJsonCast: false,
    nativeArrays: false,
    supportsJsonb: false,
    ifNotExists: true,
    indexIfNotExists: true,
    dropTableCascade: false,
    renameColumn: true,
    foreignKeyAlter: false, // SQLite does not support adding FKs to existing tables
    columnComment: false, // SQLite does not support column comments
    vectorIndexStyle: 'create',
    vectorSupportsLength: false,
    supportsTimestamptz: false,
    defaultStringAsText: true,
  };

  override readonly dialectName = 'sqlite';

  override readonly quoteChar = '`';

  override readonly serialPrimaryKey = 'INTEGER PRIMARY KEY AUTOINCREMENT';

  override readonly tableOptions = '';

  override readonly beginTransactionCommand = 'BEGIN TRANSACTION';

  override readonly commitTransactionCommand = 'COMMIT';

  override readonly rollbackTransactionCommand = 'ROLLBACK';

  override readonly isolationLevelStrategy = 'none';

  override readonly alterColumnSyntax = 'none';

  override readonly booleanLiteral = 'integer';

  // SQLite supports `RETURNING` (including on `INSERT ... ON CONFLICT`), so IDs are exact per row.
  override readonly insertIdSource = 'returning';

  protected override readonly vectorDistanceFns: ReadonlyMap<VectorDistance, string> = new Map([
    ['cosine', 'vec_distance_cosine'],
    ['l2', 'vec_distance_L2'],
    ['hamming', 'vec_distance_hamming'],
  ]);

  /**
   * SQLite does not support the `DEFAULT` keyword inside `VALUES`. Inline the metadata default
   * when declared, else `NULL` (which is also how SQLite auto-generates INTEGER PRIMARY KEYs).
   */
  protected override appendDefaultInsertValue(ctx: QueryContext, field: FieldOptions | undefined): void {
    if (field?.defaultValue !== undefined) {
      this.formatPersistableValue(ctx, field, field.defaultValue);
    } else {
      ctx.append('NULL');
    }
  }

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

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

  override normalizeValue(value: unknown): unknown {
    if (value instanceof Date) return value.getTime();
    return super.normalizeValue(value);
  }

  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);
      });
      const tableName = this.resolveTableName(entity, meta);
      ctx.append(`${this.escapeId(tableName)} MATCH {${fields.join(' ')}} : `);
      ctx.addValue(search.$value);
      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: QueryComparisonOptions = {},
  ): void {
    switch (op) {
      case '$elemMatch':
        this.buildElemMatchCondition(ctx, entity, key, val as Record<string, unknown>, opts);
        break;
      case '$all': {
        // SQLite: Check JSON array contains all values using multiple json_each subqueries
        const values = val as unknown[];
        const conditions = values
          .map((v) => {
            ctx.pushValue(JSON.stringify(v));
            return `EXISTS (SELECT 1 FROM json_each(${this.escapeId(key)}) WHERE value = json(?))`;
          })
          .join(' AND ');
        ctx.append(`(${conditions})`);
        break;
      }
      case '$size':
        // SQLite: Check JSON array length
        // e.g., json_array_length(roles) = 3, or json_array_length(roles) >= 2
        this.buildSizeComparison(
          ctx,
          () => {
            ctx.append('json_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 SQLite JSON arrays.
   * Uses EXISTS with json_each and supports nested operators.
   */
  private buildElemMatchCondition<E>(
    ctx: QueryContext,
    _entity: Type<E>,
    key: FieldKey<E>,
    match: Record<string, unknown>,
    opts: QueryComparisonOptions,
  ): void {
    ctx.append('EXISTS (SELECT 1 FROM json_each(');
    this.getComparisonKey(ctx, _entity, key, opts);
    ctx.append(') WHERE ');

    const conditions = buildElemMatchConditions(
      match,
      (field, op, opVal) =>
        this.buildJsonFieldCondition(ctx, (f) => `json_extract(value, '$.${this.escapeJsonKey(f)}')`, field, op, opVal),
      (field, value) => {
        // Keep SQLite's placeholder behavior consistent with prior implementation.
        ctx.pushValue(value);
        return `json_extract(value, '$.${this.escapeJsonKey(field)}') = ?`;
      },
    );

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

  protected override getJsonPathScalarExpr(escapedColumn: string, jsonPath: string): string {
    return `json_extract(${escapedColumn}, '$.${this.escapeJsonKey(jsonPath)}')`;
  }

  protected override numericCast(expr: string): string {
    return `CAST(${expr} AS REAL)`;
  }

  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 {
    // Use the base (non-RETURNING) insert here: `onConflictUpsert` appends its own RETURNING
    // after the ON CONFLICT clause, so calling `this.insert` would append it twice.
    this.onConflictUpsert(ctx, entity, conflictPaths, payload, (c, e, p) => super.insert(c, e, p));
  }

  protected override formatJsonUpdate<E>(ctx: QueryContext, escapedCol: string, value: JsonUpdateOp<E>): void {
    let expr = escapedCol;
    if (hasKeys(value.$merge)) {
      const merge = value.$merge as Record<string, unknown>;
      expr = `json_set(COALESCE(${escapedCol}, '{}')`;
      for (const [key, v] of Object.entries(merge)) {
        expr += `, '$.${this.escapeJsonKey(key)}', json(?)`;
        ctx.pushValue(JSON.stringify(v));
      }
      expr += ')';
    }
    if (hasKeys(value.$push)) {
      const push = value.$push as Record<string, unknown>;
      expr = `json_insert(${expr}`;
      for (const [key, v] of Object.entries(push)) {
        expr += `, '$.${this.escapeJsonKey(key)}[#]', json(?)`;
        ctx.pushValue(JSON.stringify(v));
      }
      expr += ')';
    }
    if (value.$unset?.length) {
      const paths = value.$unset.map((k) => `'$.${this.escapeJsonKey(k)}'`).join(', ');
      expr = `json_remove(${expr}, ${paths})`;
    }
    ctx.append(`${escapedCol} = ${expr}`);
  }

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

1	import { AbstractSqlDialect } from '../dialect/abstractSqlDialect.js';
2	import { buildElemMatchConditions } from '../dialect/jsonArrayElemMatchUtils.js';
3	import { getMeta } from '../entity/index.js';
4	import type {
5	DialectFeatures,
6	FieldKey,
7	FieldOptions,
8	JsonUpdateOp,
9	QueryComparisonOptions,
10	QueryConflictPaths,
11	QueryContext,
12	QueryOptions,
13	QuerySizeComparisonOps,
14	QueryTextSearchOptions,
15	QueryWhereFieldOperatorMap,
16	Type,
17	VectorDistance,
18	} from '../type/index.js';
19	import { escapeAnsiSqlLiteral } from '../util/ansiSqlLiteral.js';
20	import { hasKeys } from '../util/index.js';
21
22	export class SqliteDialect extends AbstractSqlDialect {
23	/** Default {@link DialectFeatures} for SQLite and SQLite-derived dialects. */
24	protected override readonly featureDefaults: DialectFeatures = {	136✔
25	explicitJsonCast: false,
26	nativeArrays: false,
27	supportsJsonb: false,
28	ifNotExists: true,
29	indexIfNotExists: true,
30	dropTableCascade: false,
31	renameColumn: true,
32	foreignKeyAlter: false, // SQLite does not support adding FKs to existing tables
33	columnComment: false, // SQLite does not support column comments
34	vectorIndexStyle: 'create',
35	vectorSupportsLength: false,
36	supportsTimestamptz: false,
37	defaultStringAsText: true,
38	};
39
40	override readonly dialectName = 'sqlite';	136✔
41
42	override readonly quoteChar = '`';	136✔
43
44	override readonly serialPrimaryKey = 'INTEGER PRIMARY KEY AUTOINCREMENT';	136✔
45
46	override readonly tableOptions = '';	136✔
47
48	override readonly beginTransactionCommand = 'BEGIN TRANSACTION';	136✔
49
50	override readonly commitTransactionCommand = 'COMMIT';	136✔
51
52	override readonly rollbackTransactionCommand = 'ROLLBACK';	136✔
53
54	override readonly isolationLevelStrategy = 'none';	136✔
55
56	override readonly alterColumnSyntax = 'none';	136✔
57
58	override readonly booleanLiteral = 'integer';	136✔
59
60	// SQLite supports `RETURNING` (including on `INSERT ... ON CONFLICT`), so IDs are exact per row.
61	override readonly insertIdSource = 'returning';	136✔
62
63	protected override readonly vectorDistanceFns: ReadonlyMap<VectorDistance, string> = new Map([	136✔
64	['cosine', 'vec_distance_cosine'],
65	['l2', 'vec_distance_L2'],
66	['hamming', 'vec_distance_hamming'],
67	]);
68
69	/**
70	* SQLite does not support the `DEFAULT` keyword inside `VALUES`. Inline the metadata default
71	* when declared, else `NULL` (which is also how SQLite auto-generates INTEGER PRIMARY KEYs).
72	*/
73	protected override appendDefaultInsertValue(ctx: QueryContext, field: FieldOptions \| undefined): void {
74	if (field?.defaultValue !== undefined) {	11!
75	this.formatPersistableValue(ctx, field, field.defaultValue);	×
76	} else {
77	ctx.append('NULL');	11✔
78	}
79	}
80
81	protected override ilikeExpr(f: string, ph: string): string {
82	return `${f} LIKE ${ph}`;	16✔
83	}
84
85	protected override get neOp(): string {
86	return 'IS NOT';	17✔
87	}
88
89	override normalizeValue(value: unknown): unknown {
90	if (value instanceof Date) return value.getTime();	3,604✔
91	return super.normalizeValue(value);	3,601✔
92	}
93
94	override compare<E>(
95	ctx: QueryContext,
96	entity: Type<E>,
97	key: string,
98	val: unknown,
99	opts?: QueryComparisonOptions,
100	): void {
101	if (key === '$text') {	990✔
102	const meta = getMeta(entity);	2✔
103	const search = val as QueryTextSearchOptions<E>;	2✔
104	const fields = search.$fields!.map((fKey) => {	2✔
105	const field = meta.fields[fKey];	3✔
106	const columnName = this.resolveColumnName(fKey, field!);	3✔
107	return this.escapeId(columnName);	3✔
108	});
109	const tableName = this.resolveTableName(entity, meta);	2✔
110	ctx.append(`${this.escapeId(tableName)} MATCH {${fields.join(' ')}} : `);	2✔
111	ctx.addValue(search.$value);	2✔
112	return;	2✔
113	}
114	super.compare(ctx, entity, key, val, opts);	988✔
115	}
116
117	override compareFieldOperator<E, K extends keyof QueryWhereFieldOperatorMap<E>>(
118	ctx: QueryContext,
119	entity: Type<E>,
120	key: FieldKey<E>,
121	op: K,
122	val: QueryWhereFieldOperatorMap<E>[K],
123	opts: QueryComparisonOptions = {},	960✔
124	): void {
125	switch (op) {	960✔
126	case '$elemMatch':
127	this.buildElemMatchCondition(ctx, entity, key, val as Record<string, unknown>, opts);	6✔
128	break;	6✔
129	case '$all': {
130	// SQLite: Check JSON array contains all values using multiple json_each subqueries
131	const values = val as unknown[];	1✔
132	const conditions = values	1✔
133	.map((v) => {
134	ctx.pushValue(JSON.stringify(v));	2✔
135	return `EXISTS (SELECT 1 FROM json_each(${this.escapeId(key)}) WHERE value = json(?))`;	2✔
136	})
137	.join(' AND ');
138	ctx.append(`(${conditions})`);	1✔
139	break;	1✔
140	}
141	case '$size':
142	// SQLite: Check JSON array length
143	// e.g., json_array_length(roles) = 3, or json_array_length(roles) >= 2
144	this.buildSizeComparison(	4✔
145	ctx,
146	() => {
147	ctx.append('json_array_length(');	5✔
148	this.getComparisonKey(ctx, entity, key, opts);	5✔
149	ctx.append(')');	5✔
150	},
151	val as number \| QuerySizeComparisonOps,
152	);
153	break;	4✔
154	default:
155	super.compareFieldOperator(ctx, entity, key, op, val, opts);	949✔
156	}
157	}
158
159	/**
160	* Build $elemMatch condition for SQLite JSON arrays.
161	* Uses EXISTS with json_each and supports nested operators.
162	*/
163	private buildElemMatchCondition<E>(
164	ctx: QueryContext,
165	_entity: Type<E>,
166	key: FieldKey<E>,
167	match: Record<string, unknown>,
168	opts: QueryComparisonOptions,
169	): void {
170	ctx.append('EXISTS (SELECT 1 FROM json_each(');	6✔
171	this.getComparisonKey(ctx, _entity, key, opts);	6✔
172	ctx.append(') WHERE ');	6✔
173
174	const conditions = buildElemMatchConditions(	6✔
175	match,
176	(field, op, opVal) =>
177	this.buildJsonFieldCondition(ctx, (f) => `json_extract(value, '$.${this.escapeJsonKey(f)}')`, field, op, opVal),	15✔
178	(field, value) => {
179	// Keep SQLite's placeholder behavior consistent with prior implementation.
180	ctx.pushValue(value);	2✔
181	return `json_extract(value, '$.${this.escapeJsonKey(field)}') = ?`;	2✔
182	},
183	);
184
185	ctx.append(conditions.join(' AND '));	6✔
186	ctx.append(')');	6✔
187	}
188
189	protected override getJsonPathScalarExpr(escapedColumn: string, jsonPath: string): string {
190	return `json_extract(${escapedColumn}, '$.${this.escapeJsonKey(jsonPath)}')`;	7✔
191	}
192
193	protected override numericCast(expr: string): string {
194	return `CAST(${expr} AS REAL)`;	4✔
195	}
196
197	override insert<E>(ctx: QueryContext, entity: Type<E>, payload: E \| E[], opts?: QueryOptions): void {
198	super.insert(ctx, entity, payload, opts);	212✔
199	ctx.append(' ' + this.returningId(entity));	212✔
200	}
201
202	override upsert<E>(ctx: QueryContext, entity: Type<E>, conflictPaths: QueryConflictPaths<E>, payload: E \| E[]): void {
203	// Use the base (non-RETURNING) insert here: `onConflictUpsert` appends its own RETURNING
204	// after the ON CONFLICT clause, so calling `this.insert` would append it twice.
205	this.onConflictUpsert(ctx, entity, conflictPaths, payload, (c, e, p) => super.insert(c, e, p));	15✔
206	}
207
208	protected override formatJsonUpdate<E>(ctx: QueryContext, escapedCol: string, value: JsonUpdateOp<E>): void {
209	let expr = escapedCol;	17✔
210	if (hasKeys(value.$merge)) {	17✔
211	const merge = value.$merge as Record<string, unknown>;	10✔
212	expr = `json_set(COALESCE(${escapedCol}, '{}')`;	10✔
213	for (const [key, v] of Object.entries(merge)) {	10✔
214	expr += `, '$.${this.escapeJsonKey(key)}', json(?)`;	10✔
215	ctx.pushValue(JSON.stringify(v));	10✔
216	}
217	expr += ')';	10✔
218	}
219	if (hasKeys(value.$push)) {	17✔
220	const push = value.$push as Record<string, unknown>;	6✔
221	expr = `json_insert(${expr}`;	6✔
222	for (const [key, v] of Object.entries(push)) {	6✔
223	expr += `, '$.${this.escapeJsonKey(key)}[#]', json(?)`;	6✔
224	ctx.pushValue(JSON.stringify(v));	6✔
225	}
226	expr += ')';	6✔
227	}
228	if (value.$unset?.length) {	17✔
229	const paths = value.$unset.map((k) => `'$.${this.escapeJsonKey(k)}'`).join(', ');	6✔
230	expr = `json_remove(${expr}, ${paths})`;	5✔
231	}
232	ctx.append(`${escapedCol} = ${expr}`);	17✔
233	}
234
235	override escape(value: unknown): string {
236	return escapeAnsiSqlLiteral(value);	1✔
237	}
238	}

rogerpadilla / uql / 29711874318

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