@contember/database - npm Package Compare versions

Comparing version 0.8.0-alpha.0 to 0.8.0-alpha.1

dist/src/builders/Compiler.d.ts

@@ -16,2 +16,3 @@ import { SelectBuilder } from './SelectBuilder';
     private compileGrouping;
+    private compileUnion;
     private compileLock;
@@ -18,0 +19,0 @@ private compileJoin;

dist/src/builders/Compiler.js

@@ -18,2 +18,3 @@ "use strict";
             .append(this.compileGrouping(options.grouping))
+            .append(this.compileUnion(options.union, namespaceContextFinal))
             .append(this.compileOrderBy(options.orderBy))
@@ -91,2 +92,8 @@ .append(this.compileLimit(options.limit))
     }
+    compileUnion(grouping, namespaceContext) {
+        if (!grouping) {
+            return Literal_1.Literal.empty;
+        }
+        return new Literal_1.Literal(` union ${grouping.type} (`).append(grouping.literal(namespaceContext)).appendString(')');
+    }
     compileLock(lock) {
@@ -141,4 +148,3 @@ if (!lock) {
         }
-        const [from, alias] = fromExpr;
-        return new Literal_1.Literal(' from ').append(utils_1.aliasLiteral(this.prependSchema(from, namespaceContext), alias));
+        return new Literal_1.Literal(' from ').appendAll(fromExpr.map(([from, alias]) => utils_1.aliasLiteral(this.prependSchema(from, namespaceContext), alias)), ', ');
     }
@@ -145,0 +151,0 @@ compileIntoStatement(into, values, namespaceContext) {

dist/src/builders/ConditionBuilder.d.ts

@@ -18,3 +18,3 @@ import { Value } from '../types';
     compareColumns(columnName1: QueryBuilder.ColumnIdentifier, operator: Operator, columnName2: QueryBuilder.ColumnIdentifier): ConditionBuilder;
-    in<Filled extends keyof SelectBuilder.Options>(columnName: QueryBuilder.ColumnIdentifier, values: Value[] | SelectBuilder<SelectBuilder.Result, Filled>): ConditionBuilder;
+    in(columnName: QueryBuilder.ColumnIdentifier, values: Value[] | SelectBuilder<SelectBuilder.Result>): ConditionBuilder;
     null(columnName: QueryBuilder.ColumnIdentifier): ConditionBuilder;
@@ -21,0 +21,0 @@ raw(sql: string, ...bindings: Value[]): ConditionBuilder;

dist/src/builders/DeleteBuilder.d.ts

@@ -8,14 +8,15 @@ import { Returning } from './internal/Returning';
 import { Literal } from '../Literal';
-declare class DeleteBuilder<Result extends DeleteBuilder.DeleteResult, Filled extends keyof DeleteBuilder<Result, never>> implements Returning.Aware, With.Aware, Where.Aware, QueryBuilder {
+import { SubQueryExpression } from './internal/Subqueries';
+declare class DeleteBuilder<Result extends DeleteBuilder.DeleteResult> implements Returning.Aware, With.Aware, Where.Aware, QueryBuilder {
     private readonly options;
     private constructor();
-    static create(): DeleteBuilder.NewDeleteBuilder;
-    with(alias: string, expression: With.Expression): DeleteBuilder.DeleteBuilderState<Result, Filled | 'with'>;
-    from(tableName: string): DeleteBuilder.DeleteBuilderState<Result, Filled | 'from'>;
-    where(where: Where.Expression): DeleteBuilder.DeleteBuilderState<Result, Filled | 'where'>;
-    using(tableName: string, alias?: string): DeleteBuilder.DeleteBuilderState<Result, Filled | 'using'>;
-    returning(column: QueryBuilder.ColumnIdentifier | Literal): DeleteBuilder.DeleteBuilderState<Returning.Result[], Filled | 'returning'>;
+    static create(): DeleteBuilder<DeleteBuilder.AffectedRows>;
+    with(alias: string, expression: SubQueryExpression): DeleteBuilder<Result>;
+    from(tableName: string): DeleteBuilder<Result>;
+    where(where: Where.Expression): DeleteBuilder<Result>;
+    using(tableName: string, alias?: string): DeleteBuilder<Result>;
+    returning(column: QueryBuilder.ColumnIdentifier | Literal): DeleteBuilder<Returning.Result[]>;
     createQuery(context: Compiler.Context): Literal;
     execute(db: Client): Promise<Result>;
-    protected withOption<K extends keyof DeleteBuilder.Options, V extends DeleteBuilder.Options[K]>(key: K, value: V): DeleteBuilder.DeleteBuilderState<Result, Filled | K>;
+    protected withOption<K extends keyof DeleteBuilder.Options, V extends DeleteBuilder.Options[K]>(key: K, value: V): DeleteBuilder<Result>;
 }
@@ -32,7 +33,4 @@ declare namespace DeleteBuilder {
     } & With.Options & Where.Options;
-    type DeleteBuilderWithoutExecute<Result extends DeleteResult, Filled extends keyof DeleteBuilder<Result, Filled>> = Pick<DeleteBuilder<Result, Filled>, Exclude<keyof DeleteBuilder<Result, Filled>, 'execute' | 'createQuery'>>;
-    type DeleteBuilderState<Result extends DeleteResult, Filled extends keyof DeleteBuilder<Result, Filled>> = 'from' extends Filled ? DeleteBuilder<Result, Filled> : DeleteBuilderWithoutExecute<Result, Filled>;
-    type NewDeleteBuilder = DeleteBuilder.DeleteBuilderState<DeleteBuilder.AffectedRows, never>;
 }
 export { DeleteBuilder };
 //# sourceMappingURL=DeleteBuilder.d.ts.map

dist/src/builders/DeleteBuilder.js

@@ -7,2 +7,3 @@ "use strict";
 const Compiler_1 = require("./Compiler");
+const Subqueries_1 = require("./internal/Subqueries");
 class DeleteBuilder {
@@ -22,3 +23,3 @@ constructor(options) {
     with(alias, expression) {
-        return this.withOption('with', this.options.with.withCte(alias, With_1.With.createLiteral(expression)));
+        return this.withOption('with', this.options.with.withCte(alias, Subqueries_1.createSubQueryLiteralFactory(expression)));
     }
@@ -25,0 +26,0 @@ from(tableName) {

dist/src/builders/InsertBuilder.d.ts

@@ -9,13 +9,14 @@ import { Returning } from './internal/Returning';
 import { ConflictActionType } from './ConflictActionType';
-declare class InsertBuilder<Result extends InsertBuilder.InsertResult, Filled extends keyof InsertBuilder<Result, never>> implements With.Aware, QueryBuilder {
+import { SubQueryExpression } from './internal/Subqueries';
+declare class InsertBuilder<Result extends InsertBuilder.InsertResult> implements With.Aware, QueryBuilder {
     private readonly options;
     private constructor();
-    static create(): InsertBuilder.NewInsertBuilder;
-    with(alias: string, expression: With.Expression): InsertBuilder.InsertBuilderState<Result, Filled | 'with'>;
-    into(intoTable: string): InsertBuilder.InsertBuilderState<Result, Filled | 'into'>;
-    values(columns: QueryBuilder.Values): InsertBuilder.InsertBuilderState<Result, Filled | 'values'>;
-    onConflict(type: ConflictActionType.update, target: InsertBuilder.ConflictTarget, values: QueryBuilder.Values): InsertBuilder.InsertBuilderState<Result, Filled | 'onConflict'>;
-    onConflict(type: ConflictActionType.doNothing, target?: InsertBuilder.ConflictTarget): InsertBuilder.InsertBuilderState<Result, Filled | 'onConflict'>;
-    returning(column: string | Literal): InsertBuilder.InsertBuilderState<Returning.Result[], Filled | 'returning'>;
-    from(from: SelectBuilder.Callback): InsertBuilder.InsertBuilderState<Result, Filled | 'from'>;
+    static create(): InsertBuilder<InsertBuilder.AffectedRows>;
+    with(alias: string, expression: SubQueryExpression): InsertBuilder<Result>;
+    into(intoTable: string): InsertBuilder<Result>;
+    values(columns: QueryBuilder.Values): InsertBuilder<Result>;
+    onConflict(type: ConflictActionType.update, target: InsertBuilder.ConflictTarget, values: QueryBuilder.Values): InsertBuilder<Result>;
+    onConflict(type: ConflictActionType.doNothing, target?: InsertBuilder.ConflictTarget): InsertBuilder<Result>;
+    returning(column: string | Literal): InsertBuilder<Returning.Result[]>;
+    from(from: SelectBuilder.Callback): InsertBuilder<Result>;
     createQuery(context: Compiler.Context): Literal;
@@ -40,5 +41,2 @@ execute(db: Client): Promise<Result>;
     };
-    type InsertBuilderWithoutExecute<Result extends InsertResult, Filled extends keyof InsertBuilder<Result, Filled>> = Pick<InsertBuilder<Result, Filled>, Exclude<keyof InsertBuilder<Result, Filled>, 'execute' | 'createQuery'>>;
-    type InsertBuilderState<Result extends InsertResult, Filled extends keyof InsertBuilder<Result, Filled>> = 'into' | 'values' extends Filled ? InsertBuilder<Result, Filled> : InsertBuilderWithoutExecute<Result, Filled>;
-    type NewInsertBuilder = InsertBuilder.InsertBuilderState<InsertBuilder.AffectedRows, never>;
     type ConflictAction = {
@@ -45,0 +43,0 @@ type: ConflictActionType.doNothing;

dist/src/builders/InsertBuilder.js

@@ -9,2 +9,3 @@ "use strict";
 const ConflictActionType_1 = require("./ConflictActionType");
+const Subqueries_1 = require("./internal/Subqueries");
 class InsertBuilder {
@@ -25,3 +26,3 @@ constructor(options) {
     with(alias, expression) {
-        return this.withOption('with', this.options.with.withCte(alias, With_1.With.createLiteral(expression)));
+        return this.withOption('with', this.options.with.withCte(alias, Subqueries_1.createSubQueryLiteralFactory(expression)));
     }
@@ -28,0 +29,0 @@ into(intoTable) {

dist/src/builders/internal/With.d.ts

 import { Literal } from '../../Literal';
-import { QueryBuilder } from '../QueryBuilder';
-import { SelectBuilder } from '../SelectBuilder';
 import { Compiler } from '../Compiler';
-declare type LiteralFactory = (context: Compiler.Context) => Literal;
+import { SubQueryExpression, SubQueryLiteralFactory } from './Subqueries';
 declare namespace With {
-    class Statement {
+    type CteOptions = {
+        literalFactory: SubQueryLiteralFactory;
+        recursive: boolean;
+        columns?: string[];
+    };
+    export class Statement {
         readonly ctes: {
-            [alias: string]: LiteralFactory;
+            [alias: string]: CteOptions;
         };
         constructor(ctes: {
-            [alias: string]: LiteralFactory;
+            [alias: string]: CteOptions;
         });
         compile(context: Compiler.Context): [Literal, Compiler.Context];
-        withCte(alias: string, expression: LiteralFactory): Statement;
+        withCte(alias: string, literalFactory: SubQueryLiteralFactory, recursive?: boolean, columns?: string[]): Statement;
         includes(alias: string): boolean;
         getAliases(): string[];
     }
-    interface Options {
+    export interface Options {
         with: Statement;
     }
-    type Expression = SelectBuilder.Callback | Literal | QueryBuilder;
-    interface Aware {
-        with(alias: string, expression: Expression): any;
+    export interface Aware {
+        with(alias: string, expression: SubQueryExpression): any;
     }
-    function createLiteral(expr: Expression): LiteralFactory;
+    export {};
 }
 export { With };
 //# sourceMappingURL=With.d.ts.map

dist/src/builders/internal/With.js

@@ -5,3 +5,2 @@ "use strict";
 const Literal_1 = require("../../Literal");
-const SelectBuilder_1 = require("../SelectBuilder");
 var With;
@@ -15,14 +14,19 @@ (function (With) {
             const ctes = Object.entries(this.ctes);
+            const hasRecursive = ctes.some(([, { recursive }]) => recursive);
             if (ctes.length === 0) {
                 return [new Literal_1.Literal(''), context];
             }
-            const literal = new Literal_1.Literal('with ').appendAll(ctes.map(([alias, expr]) => {
-                const literal = expr(context);
-                context = context.withAlias(alias);
-                return new Literal_1.Literal(utils_1.wrapIdentifier(alias) + ' as (' + literal.sql + ')', literal.parameters);
+            if (hasRecursive) {
+                context = context.withAlias(...Object.keys(this.ctes));
+            }
+            const literal = new Literal_1.Literal(hasRecursive ? 'with recursive ' : 'with ').appendAll(ctes.map(([alias, { literalFactory, columns }]) => {
+                const literal = literalFactory(context);
+                context = !hasRecursive ? context.withAlias(alias) : context;
+                const columnsStr = columns ? '(' + columns.map(utils_1.wrapIdentifier).join(', ') + ')' : '';
+                return new Literal_1.Literal(utils_1.wrapIdentifier(alias) + columnsStr + ' as (' + literal.sql + ')', literal.parameters);
             }), ', ');
             return [literal, context];
         }
-        withCte(alias, expression) {
-            return new Statement({ ...this.ctes, [alias]: expression });
+        withCte(alias, literalFactory, recursive = false, columns) {
+            return new Statement({ ...this.ctes, [alias]: { literalFactory, recursive, columns } });
         }
@@ -37,16 +41,4 @@ includes(alias) {
     With.Statement = Statement;
-    function createLiteral(expr) {
-        if (typeof expr === 'function') {
-            return ctx => expr(SelectBuilder_1.SelectBuilder.create()).createQuery(ctx);
-        }
-        else if (((expr) => 'createQuery' in expr)(expr)) {
-            return ctx => expr.createQuery(ctx);
-        }
-        else {
-            return () => expr;
-        }
-    }
-    With.createLiteral = createLiteral;
 })(With || (With = {}));
 exports.With = With;
 //# sourceMappingURL=With.js.map

dist/src/builders/LimitByGroupWrapper.d.ts

@@ -9,6 +9,6 @@ import { QueryBuilder } from './QueryBuilder';
     private readonly limit;
-    constructor(groupBy: QueryBuilder.ColumnIdentifier, orderByCallback: (<Orderable extends QueryBuilder.Orderable<any>>(orderable: Orderable, qb: SelectBuilder<any, any>) => [Orderable, SelectBuilder<any, any>]) | undefined, skip: number | undefined, limit: number | undefined);
-    getResult<R>(qb: SelectBuilder<R, any>, db: Client): Promise<R[]>;
+    constructor(groupBy: QueryBuilder.ColumnIdentifier, orderByCallback: (<Orderable extends QueryBuilder.Orderable<any>>(orderable: Orderable, qb: SelectBuilder<any>) => [Orderable, SelectBuilder<any>]) | undefined, skip: number | undefined, limit: number | undefined);
+    getResult<R>(qb: SelectBuilder<R>, db: Client): Promise<R[]>;
 }
 export { LimitByGroupWrapper };
 //# sourceMappingURL=LimitByGroupWrapper.d.ts.map

dist/src/builders/SelectBuilder.d.ts

@@ -9,28 +9,31 @@ import { With } from './internal/With';
 import { LockType } from './LockType';
-export declare type SelectBuilderSpecification<OutputOptions extends keyof SelectBuilder.Options> = <Result, InputOptions extends keyof SelectBuilder.Options>(qb: SelectBuilder<Result, InputOptions>) => SelectBuilder<Result, InputOptions | OutputOptions>;
-declare class SelectBuilder<Result = SelectBuilder.Result, Filled extends keyof SelectBuilder.Options = never> implements With.Aware, Where.Aware, QueryBuilder.Orderable<SelectBuilder<Result, Filled | 'orderBy'>>, QueryBuilder {
+import { SubQueryExpression, SubQueryLiteralFactory } from './internal/Subqueries';
+export declare type SelectBuilderSpecification = <Result>(qb: SelectBuilder<Result>) => SelectBuilder<Result>;
+declare class SelectBuilder<Result = SelectBuilder.Result> implements With.Aware, Where.Aware, QueryBuilder.Orderable<SelectBuilder<Result>>, QueryBuilder {
     readonly options: SelectBuilder.Options;
     constructor(options: SelectBuilder.Options);
-    static create<Result = SelectBuilder.Result>(): SelectBuilder<Result, never>;
-    with(alias: string, expression: With.Expression): SelectBuilder<Result, Filled | 'with'>;
-    from(tableName: string | Literal, alias?: string): SelectBuilder<Result, Filled | 'from'>;
-    select(columnName: QueryBuilder.ColumnIdentifier, alias?: string): SelectBuilder<Result, Filled | 'select'>;
-    select(callback: QueryBuilder.ColumnExpression, alias?: string): SelectBuilder<Result, Filled | 'select'>;
-    where(where: Where.Expression): SelectBuilder<Result, Filled | 'where'>;
-    orderBy(columnName: QueryBuilder.ColumnIdentifier, direction?: 'asc' | 'desc'): SelectBuilder<Result, Filled | 'orderBy'>;
-    join(table: string, alias?: string, condition?: SelectBuilder.JoinCondition): SelectBuilder<Result, Filled | 'join'>;
-    leftJoin(table: string, alias?: string, condition?: SelectBuilder.JoinCondition): SelectBuilder<Result, Filled | 'join'>;
+    static create<Result = SelectBuilder.Result>(): SelectBuilder<Result>;
+    with(alias: string, expression: SubQueryExpression): SelectBuilder<Result>;
+    withRecursive(alias: string, expression: SubQueryExpression, columns?: string[]): SelectBuilder<Result>;
+    unionAll(expression: SubQueryExpression): SelectBuilder<Result>;
+    from(tableName: string | Literal, alias?: string): SelectBuilder<Result>;
+    select(columnName: QueryBuilder.ColumnIdentifier, alias?: string): SelectBuilder<Result>;
+    select(callback: QueryBuilder.ColumnExpression, alias?: string): SelectBuilder<Result>;
+    where(where: Where.Expression): SelectBuilder<Result>;
+    orderBy(columnName: QueryBuilder.ColumnIdentifier, direction?: 'asc' | 'desc'): SelectBuilder<Result>;
+    join(table: string, alias?: string, condition?: SelectBuilder.JoinCondition): SelectBuilder<Result>;
+    leftJoin(table: string, alias?: string, condition?: SelectBuilder.JoinCondition): SelectBuilder<Result>;
     private joinConditionToLiteral;
-    groupBy(columnName: QueryBuilder.ColumnIdentifier): SelectBuilder<Result, Filled | 'grouping'>;
-    groupBy(callback: QueryBuilder.ColumnExpression): SelectBuilder<Result, Filled | 'grouping'>;
-    limit(limit: number, offset?: number): SelectBuilder<Result, Filled | 'limit'>;
-    lock(type: LockType): SelectBuilder<Result, Filled | 'lock'>;
-    meta(key: string, value: any): SelectBuilder<Result, Filled | 'meta'>;
-    match<AdditionalOptions extends keyof SelectBuilder.Options>(spec: SelectBuilderSpecification<AdditionalOptions>): SelectBuilder<Result, Filled | AdditionalOptions>;
+    groupBy(columnName: QueryBuilder.ColumnIdentifier): SelectBuilder<Result>;
+    groupBy(callback: QueryBuilder.ColumnExpression): SelectBuilder<Result>;
+    limit(limit: number, offset?: number): SelectBuilder<Result>;
+    lock(type: LockType): SelectBuilder<Result>;
+    meta(key: string, value: any): SelectBuilder<Result>;
+    match(spec: SelectBuilderSpecification): SelectBuilder<Result>;
     getResult(db: Client): Promise<Result[]>;
     createQuery(context: Compiler.Context): Literal;
-    protected withOption<K extends keyof SelectBuilder.Options, V extends SelectBuilder.Options[K]>(key: K, value: V): SelectBuilder<Result, Filled | K>;
+    protected withOption<K extends keyof SelectBuilder.Options, V extends SelectBuilder.Options[K]>(key: K, value: V): SelectBuilder<Result>;
 }
 declare namespace SelectBuilder {
-    type Callback = (qb: SelectBuilder<any, any>) => SelectBuilder<any, any>;
+    type Callback = (qb: SelectBuilder<any>) => SelectBuilder<any>;
     type Result = {
@@ -42,3 +45,3 @@ [columnName: string]: any;
         limit: undefined | [number, number];
-        from: undefined | [Literal | string, string | undefined];
+        from: undefined | [Literal | string, string | undefined][];
         orderBy: [Literal, 'asc' | 'desc'][];
@@ -56,2 +59,6 @@ join: {
         meta: Record<string, any>;
+        union?: {
+            type: 'all' | 'distinct';
+            literal: SubQueryLiteralFactory;
+        };
     }>;
@@ -58,0 +65,0 @@ type JoinCondition = ConditionCallback;

dist/src/builders/SelectBuilder.js

@@ -10,2 +10,3 @@ "use strict";
 const utils_2 = require("./utils");
+const Subqueries_1 = require("./internal/Subqueries");
 class SelectBuilder {
@@ -32,6 +33,15 @@ constructor(options) {
     with(alias, expression) {
-        return this.withOption('with', this.options.with.withCte(alias, With_1.With.createLiteral(expression)));
+        return this.withOption('with', this.options.with.withCte(alias, Subqueries_1.createSubQueryLiteralFactory(expression), false));
     }
+    withRecursive(alias, expression, columns) {
+        return this.withOption('with', this.options.with.withCte(alias, Subqueries_1.createSubQueryLiteralFactory(expression), true, columns));
+    }
+    unionAll(expression) {
+        return this.withOption('union', {
+            type: 'all',
+            literal: Subqueries_1.createSubQueryLiteralFactory(expression),
+        });
+    }
     from(tableName, alias) {
-        return this.withOption('from', [tableName, alias]);
+        return this.withOption('from', [...(this.options.from || []), [tableName, alias]]);
     }
@@ -38,0 +48,0 @@ select(expr, alias) {

dist/src/builders/UpdateBuilder.d.ts

@@ -9,15 +9,16 @@ import { Returning } from './internal/Returning';
 import { SelectBuilder } from './SelectBuilder';
-declare class UpdateBuilder<Result extends UpdateBuilder.UpdateResult, Filled extends keyof UpdateBuilder<Result, never>> implements With.Aware, Where.Aware, QueryBuilder {
+import { SubQueryExpression } from './internal/Subqueries';
+declare class UpdateBuilder<Result extends UpdateBuilder.UpdateResult> implements With.Aware, Where.Aware, QueryBuilder {
     private readonly options;
     private constructor();
-    static create(): UpdateBuilder.NewUpdateBuilder;
-    with(alias: string, expression: With.Expression): UpdateBuilder.UpdateBuilderState<Result, Filled | 'with'>;
-    table(name: string): UpdateBuilder.UpdateBuilderState<Result, Filled | 'table'>;
-    values(columns: QueryBuilder.Values): UpdateBuilder.UpdateBuilderState<Result, Filled | 'values'>;
-    returning(column: string | Literal): UpdateBuilder.UpdateBuilderState<Returning.Result[], Filled | 'returning'>;
-    from(from: SelectBuilder.Callback): UpdateBuilder.UpdateBuilderState<Result, Filled | 'from'>;
-    where(where: Where.Expression): UpdateBuilder.UpdateBuilderState<Result, Filled | 'where'>;
+    static create(): UpdateBuilder<UpdateBuilder.AffectedRows>;
+    with(alias: string, expression: SubQueryExpression): UpdateBuilder<Result>;
+    table(name: string): UpdateBuilder<Result>;
+    values(columns: QueryBuilder.Values): UpdateBuilder<Result>;
+    returning(column: string | Literal): UpdateBuilder<Returning.Result[]>;
+    from(from: SelectBuilder.Callback): UpdateBuilder<Result>;
+    where(where: Where.Expression): UpdateBuilder<Result>;
     createQuery(context: Compiler.Context): Literal;
     execute(db: Client): Promise<Result>;
-    protected withOption<K extends keyof UpdateBuilder.Options, V extends UpdateBuilder.Options[K]>(key: K, value: V): UpdateBuilder.UpdateBuilderState<Result, Filled | K>;
+    protected withOption<K extends keyof UpdateBuilder.Options, V extends UpdateBuilder.Options[K]>(key: K, value: V): UpdateBuilder<Result>;
 }
@@ -38,7 +39,4 @@ declare namespace UpdateBuilder {
     };
-    type UpdateBuilderWithoutExecute<Result extends UpdateResult, Filled extends keyof UpdateBuilder<Result, Filled>> = Pick<UpdateBuilder<Result, Filled>, Exclude<keyof UpdateBuilder<Result, Filled>, 'execute' | 'createQuery'>>;
-    type UpdateBuilderState<Result extends UpdateResult, Filled extends keyof UpdateBuilder<Result, Filled>> = 'table' | 'values' extends Filled ? UpdateBuilder<Result, Filled> : UpdateBuilderWithoutExecute<Result, Filled>;
-    type NewUpdateBuilder = UpdateBuilder.UpdateBuilderState<UpdateBuilder.AffectedRows, never>;
 }
 export { UpdateBuilder };
 //# sourceMappingURL=UpdateBuilder.d.ts.map

dist/src/builders/UpdateBuilder.js

@@ -10,2 +10,3 @@ "use strict";
 const utils_1 = require("./utils");
+const Subqueries_1 = require("./internal/Subqueries");
 class UpdateBuilder {
@@ -26,3 +27,3 @@ constructor(options) {
     with(alias, expression) {
-        return this.withOption('with', this.options.with.withCte(alias, With_1.With.createLiteral(expression)));
+        return this.withOption('with', this.options.with.withCte(alias, Subqueries_1.createSubQueryLiteralFactory(expression)));
     }
@@ -29,0 +30,0 @@ table(name) {

dist/src/client/Client.d.ts

@@ -13,6 +13,6 @@ import { DeleteBuilder, InsertBuilder, SelectBuilder, UpdateBuilder } from '../builders';
     transaction<T>(transactionScope: (wrapper: Client<Connection.TransactionLike>) => Promise<T> | T): Promise<T>;
-    selectBuilder<Result = SelectBuilder.Result>(): SelectBuilder<Result, never>;
-    insertBuilder(): InsertBuilder.NewInsertBuilder;
-    updateBuilder(): UpdateBuilder.NewUpdateBuilder;
-    deleteBuilder(): DeleteBuilder.NewDeleteBuilder;
+    selectBuilder<Result = SelectBuilder.Result>(): SelectBuilder<Result>;
+    insertBuilder(): InsertBuilder<InsertBuilder.AffectedRows>;
+    updateBuilder(): UpdateBuilder<UpdateBuilder.AffectedRows>;
+    deleteBuilder(): DeleteBuilder<DeleteBuilder.AffectedRows>;
     query<Row extends Record<string, any>>(sql: string, parameters?: any[], meta?: Record<string, any>, config?: Connection.QueryConfig): Promise<Connection.Result<Row>>;
@@ -19,0 +19,0 @@ createQueryHandler(): QueryHandler<DatabaseQueryable>;

dist/src/client/Connection.js

@@ -24,5 +24,3 @@ "use strict";
             const result = await callback(transaction);
-            if (!transaction.isClosed) {
-                await transaction.commit();
-            }
+            await transaction.commitUnclosed();
             client.release();
@@ -32,5 +30,3 @@ return result;
         catch (e) {
-            if (!transaction.isClosed) {
-                await transaction.rollback();
-            }
+            await transaction.rollbackUnclosed();
             client.release(e);
@@ -37,0 +33,0 @@ throw e;

dist/src/client/execution.d.ts

@@ -1,5 +0,5 @@
-import { PoolClient } from 'pg';
+import { ClientBase } from 'pg';
 import { EventManager } from './EventManager';
 import { Connection } from './Connection';
-export declare function executeQuery<Row extends Record<string, any>>(pgClient: PoolClient, eventManager: EventManager, { sql, parameters, meta, timing }: Connection.Query & Connection.QueryConfig, context: Connection.QueryContext): Promise<Connection.Result<Row>>;
+export declare function executeQuery<Row extends Record<string, any>>(pgClient: ClientBase, eventManager: EventManager, { sql, parameters, meta, timing }: Connection.Query & Connection.QueryConfig, context: Connection.QueryContext): Promise<Connection.Result<Row>>;
 //# sourceMappingURL=execution.d.ts.map

dist/src/client/index.d.ts

@@ -6,2 +6,3 @@ export * from './Client';
 export * from './Transaction';
+export * from './SingleConnection';
 //# sourceMappingURL=index.d.ts.map

dist/src/client/index.js

@@ -11,2 +11,3 @@ "use strict";
 __export(require("./Transaction"));
+__export(require("./SingleConnection"));
 //# sourceMappingURL=index.js.map

dist/src/client/Transaction.d.ts

 import { Connection } from './Connection';
 import { EventManager } from './EventManager';
-import { PoolClient } from 'pg';
+import { ClientBase } from 'pg';
 export declare class Transaction implements Connection.TransactionLike {
@@ -12,9 +12,11 @@ private readonly pgClient;
     get isClosed(): boolean;
-    constructor(pgClient: PoolClient, eventManager: EventManager, config: Connection.QueryConfig);
+    constructor(pgClient: ClientBase, eventManager: EventManager, config: Connection.QueryConfig);
     transaction<Result>(callback: (connection: Connection.TransactionLike) => Promise<Result> | Result): Promise<Result>;
     query<Row extends Record<string, any>>(sql: string, parameters?: any[], meta?: Record<string, any>, config?: Connection.QueryConfig): Promise<Connection.Result<Row>>;
     rollback(): Promise<void>;
+    rollbackUnclosed(): Promise<void>;
     commit(): Promise<void>;
+    commitUnclosed(): Promise<void>;
     private close;
 }
 //# sourceMappingURL=Transaction.d.ts.map

dist/src/client/Transaction.js

@@ -44,5 +44,17 @@ "use strict";
     }
+    async rollbackUnclosed() {
+        if (this.isClosed) {
+            return;
+        }
+        await this.rollback();
+    }
     async commit() {
         await this.close('COMMIT');
     }
+    async commitUnclosed() {
+        if (this.isClosed) {
+            return;
+        }
+        await this.commit();
+    }
     async close(command) {
@@ -49,0 +61,0 @@ await this.pgClient.query(command);

dist/tests/cases/unit/queryBuilderTest.js

@@ -114,4 +114,4 @@ "use strict";
             sql: tags_1.SQL `
-				with "root_" as (select ? :: text as "title", ? :: int as "id", ? :: text as "content") 
-				insert into "public"."author" ("id", "title") 
+				with "root_" as (select ? :: text as "title", ? :: int as "id", ? :: text as "content")
+				insert into "public"."author" ("id", "title")
 					select "id", "title" from "root_"
@@ -242,6 +242,6 @@ on conflict do nothing returning "id"`,
             },
-            sql: tags_1.SQL `with "data" as 
-			(select * from "public"."abc") 
-			delete from "public"."bar" 
-			using "data" as "data" 
+            sql: tags_1.SQL `with "data" as
+			(select * from "public"."abc")
+			delete from "public"."bar"
+			using "data" as "data"
 			where "data"."a" >= ? returning "xyz"`,
@@ -275,6 +275,6 @@ parameters: [1],
             },
-            sql: tags_1.SQL `with "data" as 
-			(select "foo"."bar", 
-				 row_number() over(partition by "foo"."lorem" order by "foo"."ipsum" asc) as "rowNumber_" 
-			 from "public"."foo" order by "foo"."ipsum" asc) 
+            sql: tags_1.SQL `with "data" as
+			(select "foo"."bar",
+				 row_number() over(partition by "foo"."lorem" order by "foo"."ipsum" asc) as "rowNumber_"
+			 from "public"."foo" order by "foo"."ipsum" asc)
 			select "data".* from "data" where "data"."rowNumber_" > ? and "data"."rowNumber_" <= ?`,
@@ -298,3 +298,43 @@ parameters: [1, 4],
     });
+    it('select union', async () => {
+        await execute({
+            query: async (wrapper) => {
+                const qb = wrapper
+                    .selectBuilder()
+                    .select('id')
+                    .from('foo')
+                    .unionAll(qb => qb.select('id').from('bar'));
+                await qb.getResult(wrapper);
+            },
+            sql: tags_1.SQL `select "id" from "public"."foo" union all ( select "id" from "public"."bar")`,
+            parameters: [],
+        });
+    });
+    it('select with recursive', async () => {
+        await execute({
+            query: async (wrapper) => {
+                const qb = wrapper
+                    .selectBuilder()
+                    .withRecursive('recent_events', qb => qb
+                    .select('id')
+                    .from('events')
+                    .where({ id: '123' })
+                    .unionAll(qb => qb
+                    .select('id')
+                    .from('events')
+                    .from('recent_events')
+                    .where(expr => expr.columnsEq(['events', 'id'], ['recent_events', 'previous_id']))))
+                    .select('id')
+                    .from('recent_events');
+                await qb.getResult(wrapper);
+            },
+            sql: tags_1.SQL `
+			with recursive
+     			"recent_events" as (select "id" from "public"."events" where "id" = ?
+					union all ( select "id" from "public"."events", "recent_events" where "events"."id" = "recent_events"."previous_id"))
+			select "id" from "recent_events"`,
+            parameters: ['123'],
+        });
+    });
 });
 //# sourceMappingURL=queryBuilderTest.js.map

package.json

 {
 	"name": "@contember/database",
-	"version": "0.8.0-alpha.0",
+	"version": "0.8.0-alpha.1",
 	"license": "Apache-2.0",
@@ -11,7 +11,7 @@ "main": "dist/src/index.js",
 	"dependencies": {
-		"@contember/queryable": "^0.8.0-alpha.0",
+		"@contember/queryable": "^0.8.0-alpha.1",
 		"pg": "^7.17.1"
 	},
 	"devDependencies": {
-		"@contember/database-tester": "^0.8.0-alpha.0",
+		"@contember/database-tester": "^0.8.0-alpha.1",
 		"@types/jasmine": "^3.5.10",
@@ -23,3 +23,3 @@ "@types/node": "^13.11.1",
 	},
-	"gitHead": "e8480ed2e4f612537e229c6017565ec43917618e"
+	"gitHead": "c20d28db4163768b7e479fabf8ffb2d15a7fc153"
 }

src/builders/Compiler.ts

@@ -23,2 +23,3 @@ import { assertNever, aliasLiteral, prependSchema, wrapIdentifier } from '../utils'
 				.append(this.compileGrouping(options.grouping))
+				.append(this.compileUnion(options.union, namespaceContextFinal))
 				.append(this.compileOrderBy(options.orderBy))
@@ -123,2 +124,9 @@ .append(this.compileLimit(options.limit))
 
+	private compileUnion(grouping: SelectBuilder.Options['union'], namespaceContext: Compiler.Context): Literal {
+		if (!grouping) {
+			return Literal.empty
+		}
+		return new Literal(` union ${grouping.type} (`).append(grouping.literal(namespaceContext)).appendString(')')
+	}
+
 	private compileLock(lock?: SelectBuilder.Options['lock']): Literal {
@@ -176,4 +184,6 @@ if (!lock) {
 		}
-		const [from, alias] = fromExpr
-		return new Literal(' from ').append(aliasLiteral(this.prependSchema(from, namespaceContext), alias))
+		return new Literal(' from ').appendAll(
+			fromExpr.map(([from, alias]) => aliasLiteral(this.prependSchema(from, namespaceContext), alias)),
+			', ',
+		)
 	}
@@ -180,0 +190,0 @@

src/builders/ConditionBuilder.ts

@@ -69,5 +69,5 @@ import { Value } from '../types'
 
-	in<Filled extends keyof SelectBuilder.Options>(
+	in(
 		columnName: QueryBuilder.ColumnIdentifier,
-		values: Value[] | SelectBuilder<SelectBuilder.Result, Filled>,
+		values: Value[] | SelectBuilder<SelectBuilder.Result>,
 	): ConditionBuilder {
@@ -74,0 +74,0 @@ if (!Array.isArray(values)) {

src/builders/DeleteBuilder.ts

@@ -8,8 +8,9 @@ import { Returning } from './internal/Returning'
 import { Literal } from '../Literal'
+import { createSubQueryLiteralFactory, SubQueryExpression } from './internal/Subqueries'
 
-class DeleteBuilder<Result extends DeleteBuilder.DeleteResult, Filled extends keyof DeleteBuilder<Result, never>>
+class DeleteBuilder<Result extends DeleteBuilder.DeleteResult>
 	implements Returning.Aware, With.Aware, Where.Aware, QueryBuilder {
 	private constructor(private readonly options: DeleteBuilder.Options) {}
 
-	public static create(): DeleteBuilder.NewDeleteBuilder {
+	public static create(): DeleteBuilder<DeleteBuilder.AffectedRows> {
 		return new DeleteBuilder({
@@ -21,28 +22,23 @@ from: undefined,
 			where: new Where.Statement([]),
-		}) as DeleteBuilder.DeleteBuilderState<DeleteBuilder.AffectedRows, never>
+		})
 	}
 
-	with(alias: string, expression: With.Expression): DeleteBuilder.DeleteBuilderState<Result, Filled | 'with'> {
-		return this.withOption('with', this.options.with.withCte(alias, With.createLiteral(expression)))
+	with(alias: string, expression: SubQueryExpression): DeleteBuilder<Result> {
+		return this.withOption('with', this.options.with.withCte(alias, createSubQueryLiteralFactory(expression)))
 	}
 
-	public from(tableName: string): DeleteBuilder.DeleteBuilderState<Result, Filled | 'from'> {
+	public from(tableName: string): DeleteBuilder<Result> {
 		return this.withOption('from', tableName)
 	}
 
-	public where(where: Where.Expression): DeleteBuilder.DeleteBuilderState<Result, Filled | 'where'> {
+	public where(where: Where.Expression): DeleteBuilder<Result> {
 		return this.withOption('where', this.options.where.withWhere(where))
 	}
 
-	public using(tableName: string, alias?: string): DeleteBuilder.DeleteBuilderState<Result, Filled | 'using'> {
+	public using(tableName: string, alias?: string): DeleteBuilder<Result> {
 		return this.withOption('using', { ...this.options.using, [alias || tableName]: tableName })
 	}
 
-	public returning(
-		column: QueryBuilder.ColumnIdentifier | Literal,
-	): DeleteBuilder.DeleteBuilderState<Returning.Result[], Filled | 'returning'> {
-		return this.withOption('returning', new Returning(column)) as DeleteBuilder.DeleteBuilderState<
-			Returning.Result[],
-			Filled | 'returning'
-		>
+	public returning(column: QueryBuilder.ColumnIdentifier | Literal): DeleteBuilder<Returning.Result[]> {
+		return this.withOption('returning', new Returning(column)) as DeleteBuilder<Returning.Result[]>
 	}
@@ -65,7 +61,7 @@
 		value: V,
-	): DeleteBuilder.DeleteBuilderState<Result, Filled | K> {
-		return new DeleteBuilder<Result, Filled | K>({
+	): DeleteBuilder<Result> {
+		return new DeleteBuilder<Result>({
 			...this.options,
 			[key]: value,
-		}) as DeleteBuilder.DeleteBuilderState<Result, Filled | K>
+		}) as DeleteBuilder<Result>
 	}
@@ -84,16 +80,4 @@ }
 		Where.Options
-
-	export type DeleteBuilderWithoutExecute<
-		Result extends DeleteResult,
-		Filled extends keyof DeleteBuilder<Result, Filled>
-	> = Pick<DeleteBuilder<Result, Filled>, Exclude<keyof DeleteBuilder<Result, Filled>, 'execute' | 'createQuery'>>
-
-	export type DeleteBuilderState<
-		Result extends DeleteResult,
-		Filled extends keyof DeleteBuilder<Result, Filled>
-	> = 'from' extends Filled ? DeleteBuilder<Result, Filled> : DeleteBuilderWithoutExecute<Result, Filled>
-
-	export type NewDeleteBuilder = DeleteBuilder.DeleteBuilderState<DeleteBuilder.AffectedRows, never>
 }
 
 export { DeleteBuilder }

src/builders/InsertBuilder.ts

@@ -10,8 +10,8 @@ import { Returning } from './internal/Returning'
 import { ConflictActionType } from './ConflictActionType'
+import { createSubQueryLiteralFactory, SubQueryExpression } from './internal/Subqueries'
 
-class InsertBuilder<Result extends InsertBuilder.InsertResult, Filled extends keyof InsertBuilder<Result, never>>
-	implements With.Aware, QueryBuilder {
+class InsertBuilder<Result extends InsertBuilder.InsertResult> implements With.Aware, QueryBuilder {
 	private constructor(private readonly options: InsertBuilder.Options) {}
 
-	public static create(): InsertBuilder.NewInsertBuilder {
+	public static create(): InsertBuilder<InsertBuilder.AffectedRows> {
 		return new InsertBuilder({
@@ -24,14 +24,14 @@ into: undefined,
 			from: undefined,
-		}) as InsertBuilder.InsertBuilderState<InsertBuilder.AffectedRows, never>
+		})
 	}
 
-	public with(alias: string, expression: With.Expression): InsertBuilder.InsertBuilderState<Result, Filled | 'with'> {
-		return this.withOption('with', this.options.with.withCte(alias, With.createLiteral(expression)))
+	public with(alias: string, expression: SubQueryExpression): InsertBuilder<Result> {
+		return this.withOption('with', this.options.with.withCte(alias, createSubQueryLiteralFactory(expression)))
 	}
 
-	public into(intoTable: string): InsertBuilder.InsertBuilderState<Result, Filled | 'into'> {
+	public into(intoTable: string): InsertBuilder<Result> {
 		return this.withOption('into', intoTable)
 	}
 
-	public values(columns: QueryBuilder.Values): InsertBuilder.InsertBuilderState<Result, Filled | 'values'> {
+	public values(columns: QueryBuilder.Values): InsertBuilder<Result> {
 		return this.withOption('values', resolveValues(columns))
@@ -44,12 +44,9 @@ }
 		values: QueryBuilder.Values,
-	): InsertBuilder.InsertBuilderState<Result, Filled | 'onConflict'>
+	): InsertBuilder<Result>
+	public onConflict(type: ConflictActionType.doNothing, target?: InsertBuilder.ConflictTarget): InsertBuilder<Result>
 	public onConflict(
-		type: ConflictActionType.doNothing,
-		target?: InsertBuilder.ConflictTarget,
-	): InsertBuilder.InsertBuilderState<Result, Filled | 'onConflict'>
-	public onConflict(
 		type: ConflictActionType,
 		target?: InsertBuilder.ConflictTarget,
 		values?: QueryBuilder.Values,
-	): InsertBuilder.InsertBuilderState<Result, Filled | 'onConflict'> {
+	): InsertBuilder<Result> {
 		let conflictAction: InsertBuilder.ConflictAction
@@ -66,12 +63,7 @@ if (type === ConflictActionType.update && values && target) {
 
-	public returning(
-		column: string | Literal,
-	): InsertBuilder.InsertBuilderState<Returning.Result[], Filled | 'returning'> {
-		return this.withOption('returning', new Returning(column)) as InsertBuilder.InsertBuilderState<
-			Returning.Result[],
-			Filled | 'returning'
-		>
+	public returning(column: string | Literal): InsertBuilder<Returning.Result[]> {
+		return this.withOption('returning', new Returning(column)) as InsertBuilder<Returning.Result[]>
 	}
 
-	public from(from: SelectBuilder.Callback): InsertBuilder.InsertBuilderState<Result, Filled | 'from'> {
+	public from(from: SelectBuilder.Callback): InsertBuilder<Result> {
 		return this.withOption('from', from)
@@ -91,3 +83,3 @@ }
 		if (this.options.from !== undefined) {
-			let queryBuilder: SelectBuilder<SelectBuilder.Result, any> = SelectBuilder.create()
+			let queryBuilder: SelectBuilder<SelectBuilder.Result> = SelectBuilder.create()
 			queryBuilder = Object.values(values).reduce((qb, raw) => qb.select(raw), queryBuilder)
@@ -112,7 +104,4 @@ queryBuilder = this.options.from(queryBuilder)
 		value: V,
-	): InsertBuilder.InsertBuilderState<Result, Filled | K> {
-		return new InsertBuilder<Result, Filled | K>({ ...this.options, [key]: value }) as InsertBuilder.InsertBuilderState<
-			Result,
-			Filled | K
-		>
+	): InsertBuilder<Result> {
+		return new InsertBuilder<Result>({ ...this.options, [key]: value })
 	}
@@ -139,15 +128,2 @@ }
 
-	export type InsertBuilderWithoutExecute<
-		Result extends InsertResult,
-		Filled extends keyof InsertBuilder<Result, Filled>
-	> = Pick<InsertBuilder<Result, Filled>, Exclude<keyof InsertBuilder<Result, Filled>, 'execute' | 'createQuery'>>
-
-	export type InsertBuilderState<Result extends InsertResult, Filled extends keyof InsertBuilder<Result, Filled>> =
-		| 'into'
-		| 'values' extends Filled
-		? InsertBuilder<Result, Filled>
-		: InsertBuilderWithoutExecute<Result, Filled>
-
-	export type NewInsertBuilder = InsertBuilder.InsertBuilderState<InsertBuilder.AffectedRows, never>
-
 	export type ConflictAction =
@@ -154,0 +130,0 @@ | { type: ConflictActionType.doNothing; target?: ConflictTarget }

src/builders/internal/With.ts

 import { wrapIdentifier } from '../../utils'
 import { Literal } from '../../Literal'
-import { QueryBuilder } from '../QueryBuilder'
-import { SelectBuilder } from '../SelectBuilder'
 import { Compiler } from '../Compiler'
+import { SubQueryExpression, SubQueryLiteralFactory } from './Subqueries'
 
-type LiteralFactory = (context: Compiler.Context) => Literal
+namespace With {
+	type CteOptions = {
+		literalFactory: SubQueryLiteralFactory
+		recursive: boolean
+		columns?: string[]
+	}
 
-namespace With {
 	export class Statement {
-		constructor(public readonly ctes: { [alias: string]: LiteralFactory }) {}
+		constructor(public readonly ctes: { [alias: string]: CteOptions }) {}
 
 		public compile(context: Compiler.Context): [Literal, Compiler.Context] {
 			const ctes = Object.entries(this.ctes)
+			const hasRecursive = ctes.some(([, { recursive }]) => recursive)
 			if (ctes.length === 0) {
 				return [new Literal(''), context]
 			}
-			const literal = new Literal('with ').appendAll(
-				ctes.map(([alias, expr]) => {
-					const literal = expr(context)
-					context = context.withAlias(alias)
-					return new Literal(wrapIdentifier(alias) + ' as (' + literal.sql + ')', literal.parameters)
+			if (hasRecursive) {
+				context = context.withAlias(...Object.keys(this.ctes))
+			}
+			const literal = new Literal(hasRecursive ? 'with recursive ' : 'with ').appendAll(
+				ctes.map(([alias, { literalFactory, columns }]) => {
+					const literal = literalFactory(context)
+					context = !hasRecursive ? context.withAlias(alias) : context
+					const columnsStr = columns ? '(' + columns.map(wrapIdentifier).join(', ') + ')' : ''
+					return new Literal(wrapIdentifier(alias) + columnsStr + ' as (' + literal.sql + ')', literal.parameters)
 				}),
@@ -29,4 +37,9 @@ ', ',
 
-		public withCte(alias: string, expression: LiteralFactory): Statement {
-			return new Statement({ ...this.ctes, [alias]: expression })
+		public withCte(
+			alias: string,
+			literalFactory: SubQueryLiteralFactory,
+			recursive: boolean = false,
+			columns?: string[],
+		): Statement {
+			return new Statement({ ...this.ctes, [alias]: { literalFactory, recursive, columns } })
 		}
@@ -47,19 +60,7 @@
 
-	export type Expression = SelectBuilder.Callback | Literal | QueryBuilder
-
 	export interface Aware {
-		with(alias: string, expression: Expression): any
+		with(alias: string, expression: SubQueryExpression): any
 	}
-
-	export function createLiteral(expr: Expression): LiteralFactory {
-		if (typeof expr === 'function') {
-			return ctx => expr(SelectBuilder.create()).createQuery(ctx)
-		} else if (((expr: any): expr is QueryBuilder => 'createQuery' in expr)(expr)) {
-			return ctx => expr.createQuery(ctx)
-		} else {
-			return () => expr
-		}
-	}
 }
 
 export { With }

src/builders/LimitByGroupWrapper.ts

@@ -12,4 +12,4 @@ import { QueryBuilder } from './QueryBuilder'
 					orderable: Orderable,
-					qb: SelectBuilder<any, any>,
-			  ) => [Orderable, SelectBuilder<any, any>])
+					qb: SelectBuilder<any>,
+			  ) => [Orderable, SelectBuilder<any>])
 			| undefined,
@@ -20,3 +20,3 @@ private readonly skip: number | undefined,
 
-	public async getResult<R>(qb: SelectBuilder<R, any>, db: Client): Promise<R[]> {
+	public async getResult<R>(qb: SelectBuilder<R>, db: Client): Promise<R[]> {
 		if (this.limit !== undefined || this.skip !== undefined) {
@@ -47,3 +47,3 @@ qb = qb.select(
 
-			let wrapperQb: SelectBuilder<R, any> = SelectBuilder.create<R>()
+			let wrapperQb: SelectBuilder<R> = SelectBuilder.create<R>()
 				.with('data', qb)
@@ -50,0 +50,0 @@ .from('data')

src/builders/SelectBuilder.ts

@@ -11,12 +11,8 @@ import { With } from './internal/With'
 import { columnExpressionToLiteral, toFqnWrap } from './utils'
+import { createSubQueryLiteralFactory, SubQueryExpression, SubQueryLiteralFactory } from './internal/Subqueries'
 
-export type SelectBuilderSpecification<OutputOptions extends keyof SelectBuilder.Options> = <
-	Result,
-	InputOptions extends keyof SelectBuilder.Options
->(
-	qb: SelectBuilder<Result, InputOptions>,
-) => SelectBuilder<Result, InputOptions | OutputOptions>
+export type SelectBuilderSpecification = <Result>(qb: SelectBuilder<Result>) => SelectBuilder<Result>
 
-class SelectBuilder<Result = SelectBuilder.Result, Filled extends keyof SelectBuilder.Options = never>
-	implements With.Aware, Where.Aware, QueryBuilder.Orderable<SelectBuilder<Result, Filled | 'orderBy'>>, QueryBuilder {
+class SelectBuilder<Result = SelectBuilder.Result>
+	implements With.Aware, Where.Aware, QueryBuilder.Orderable<SelectBuilder<Result>>, QueryBuilder {
 	constructor(public readonly options: SelectBuilder.Options) {}
@@ -41,16 +37,30 @@
 
-	public with(alias: string, expression: With.Expression): SelectBuilder<Result, Filled | 'with'> {
-		return this.withOption('with', this.options.with.withCte(alias, With.createLiteral(expression)))
+	public with(alias: string, expression: SubQueryExpression): SelectBuilder<Result> {
+		return this.withOption('with', this.options.with.withCte(alias, createSubQueryLiteralFactory(expression), false))
 	}
 
-	public from(tableName: string | Literal, alias?: string): SelectBuilder<Result, Filled | 'from'> {
-		return this.withOption('from', [tableName, alias])
+	public withRecursive(alias: string, expression: SubQueryExpression, columns?: string[]): SelectBuilder<Result> {
+		return this.withOption(
+			'with',
+			this.options.with.withCte(alias, createSubQueryLiteralFactory(expression), true, columns),
+		)
 	}
 
-	public select(columnName: QueryBuilder.ColumnIdentifier, alias?: string): SelectBuilder<Result, Filled | 'select'>
-	public select(callback: QueryBuilder.ColumnExpression, alias?: string): SelectBuilder<Result, Filled | 'select'>
+	public unionAll(expression: SubQueryExpression): SelectBuilder<Result> {
+		return this.withOption('union', {
+			type: 'all',
+			literal: createSubQueryLiteralFactory(expression),
+		})
+	}
+
+	public from(tableName: string | Literal, alias?: string): SelectBuilder<Result> {
+		return this.withOption('from', [...(this.options.from || []), [tableName, alias]])
+	}
+
+	public select(columnName: QueryBuilder.ColumnIdentifier, alias?: string): SelectBuilder<Result>
+	public select(callback: QueryBuilder.ColumnExpression, alias?: string): SelectBuilder<Result>
 	public select(
 		expr: QueryBuilder.ColumnIdentifier | QueryBuilder.ColumnExpression,
 		alias?: string,
-	): SelectBuilder<Result, Filled | 'select'> {
+	): SelectBuilder<Result> {
 		let raw = columnExpressionToLiteral(expr)
@@ -63,18 +73,11 @@ if (raw === undefined) {
 
-	public where(where: Where.Expression): SelectBuilder<Result, Filled | 'where'> {
+	public where(where: Where.Expression): SelectBuilder<Result> {
 		return this.withOption('where', this.options.where.withWhere(where))
 	}
 
-	public orderBy(
-		columnName: QueryBuilder.ColumnIdentifier,
-		direction: 'asc' | 'desc' = 'asc',
-	): SelectBuilder<Result, Filled | 'orderBy'> {
+	public orderBy(columnName: QueryBuilder.ColumnIdentifier, direction: 'asc' | 'desc' = 'asc'): SelectBuilder<Result> {
 		return this.withOption('orderBy', [...this.options.orderBy, [new Literal(toFqnWrap(columnName)), direction]])
 	}
 
-	public join(
-		table: string,
-		alias?: string,
-		condition?: SelectBuilder.JoinCondition,
-	): SelectBuilder<Result, Filled | 'join'> {
+	public join(table: string, alias?: string, condition?: SelectBuilder.JoinCondition): SelectBuilder<Result> {
 		return this.withOption('join', [
@@ -86,7 +89,3 @@ ...this.options.join,
 
-	public leftJoin(
-		table: string,
-		alias?: string,
-		condition?: SelectBuilder.JoinCondition,
-	): SelectBuilder<Result, Filled | 'join'> {
+	public leftJoin(table: string, alias?: string, condition?: SelectBuilder.JoinCondition): SelectBuilder<Result> {
 		return this.withOption('join', [
@@ -107,7 +106,5 @@ ...this.options.join,
 
-	public groupBy(columnName: QueryBuilder.ColumnIdentifier): SelectBuilder<Result, Filled | 'grouping'>
-	public groupBy(callback: QueryBuilder.ColumnExpression): SelectBuilder<Result, Filled | 'grouping'>
-	public groupBy(
-		expr: QueryBuilder.ColumnIdentifier | QueryBuilder.ColumnExpression,
-	): SelectBuilder<Result, Filled | 'grouping'> {
+	public groupBy(columnName: QueryBuilder.ColumnIdentifier): SelectBuilder<Result>
+	public groupBy(callback: QueryBuilder.ColumnExpression): SelectBuilder<Result>
+	public groupBy(expr: QueryBuilder.ColumnIdentifier | QueryBuilder.ColumnExpression): SelectBuilder<Result> {
 		let raw = columnExpressionToLiteral(expr)
@@ -123,17 +120,15 @@ if (raw === undefined) {
 
-	public limit(limit: number, offset?: number): SelectBuilder<Result, Filled | 'limit'> {
+	public limit(limit: number, offset?: number): SelectBuilder<Result> {
 		return this.withOption('limit', [limit, offset || 0])
 	}
 
-	public lock(type: LockType): SelectBuilder<Result, Filled | 'lock'> {
+	public lock(type: LockType): SelectBuilder<Result> {
 		return this.withOption('lock', type)
 	}
 
-	public meta(key: string, value: any): SelectBuilder<Result, Filled | 'meta'> {
+	public meta(key: string, value: any): SelectBuilder<Result> {
 		return this.withOption('meta', { ...this.options.meta, [key]: value })
 	}
 
-	public match<AdditionalOptions extends keyof SelectBuilder.Options>(
-		spec: SelectBuilderSpecification<AdditionalOptions>,
-	): SelectBuilder<Result, Filled | AdditionalOptions> {
+	public match(spec: SelectBuilderSpecification): SelectBuilder<Result> {
 		return spec(this)
@@ -161,4 +156,4 @@ }
 		value: V,
-	): SelectBuilder<Result, Filled | K> {
-		return new SelectBuilder<Result, Filled | K>({ ...this.options, [key]: value }) as SelectBuilder<Result, Filled | K>
+	): SelectBuilder<Result> {
+		return new SelectBuilder<Result>({ ...this.options, [key]: value })
 	}
@@ -168,3 +163,3 @@ }
 namespace SelectBuilder {
-	export type Callback = (qb: SelectBuilder<any, any>) => SelectBuilder<any, any>
+	export type Callback = (qb: SelectBuilder<any>) => SelectBuilder<any>
 
@@ -178,3 +173,3 @@ export type Result = { [columnName: string]: any }
 				limit: undefined | [number, number]
-				from: undefined | [Literal | string, string | undefined]
+				from: undefined | [Literal | string, string | undefined][]
 				orderBy: [Literal, 'asc' | 'desc'][]
@@ -192,2 +187,6 @@ join: {
 				meta: Record<string, any>
+				union?: {
+					type: 'all' | 'distinct'
+					literal: SubQueryLiteralFactory
+				}
 			}
@@ -194,0 +193,0 @@ >

src/builders/UpdateBuilder.ts

@@ -10,8 +10,8 @@ import { Returning } from './internal/Returning'
 import { resolveValues } from './utils'
+import { createSubQueryLiteralFactory, SubQueryExpression } from './internal/Subqueries'
 
-class UpdateBuilder<Result extends UpdateBuilder.UpdateResult, Filled extends keyof UpdateBuilder<Result, never>>
-	implements With.Aware, Where.Aware, QueryBuilder {
+class UpdateBuilder<Result extends UpdateBuilder.UpdateResult> implements With.Aware, Where.Aware, QueryBuilder {
 	private constructor(private readonly options: UpdateBuilder.Options) {}
 
-	public static create(): UpdateBuilder.NewUpdateBuilder {
+	public static create(): UpdateBuilder<UpdateBuilder.AffectedRows> {
 		return new UpdateBuilder({
@@ -24,31 +24,26 @@ table: undefined,
 			where: new Where.Statement([]),
-		}) as UpdateBuilder.UpdateBuilderState<UpdateBuilder.AffectedRows, never>
+		})
 	}
 
-	public with(alias: string, expression: With.Expression): UpdateBuilder.UpdateBuilderState<Result, Filled | 'with'> {
-		return this.withOption('with', this.options.with.withCte(alias, With.createLiteral(expression)))
+	public with(alias: string, expression: SubQueryExpression): UpdateBuilder<Result> {
+		return this.withOption('with', this.options.with.withCte(alias, createSubQueryLiteralFactory(expression)))
 	}
 
-	public table(name: string): UpdateBuilder.UpdateBuilderState<Result, Filled | 'table'> {
+	public table(name: string): UpdateBuilder<Result> {
 		return this.withOption('table', name)
 	}
 
-	public values(columns: QueryBuilder.Values): UpdateBuilder.UpdateBuilderState<Result, Filled | 'values'> {
+	public values(columns: QueryBuilder.Values): UpdateBuilder<Result> {
 		return this.withOption('values', resolveValues(columns))
 	}
 
-	public returning(
-		column: string | Literal,
-	): UpdateBuilder.UpdateBuilderState<Returning.Result[], Filled | 'returning'> {
-		return this.withOption('returning', new Returning(column)) as UpdateBuilder.UpdateBuilderState<
-			Returning.Result[],
-			Filled | 'returning'
-		>
+	public returning(column: string | Literal): UpdateBuilder<Returning.Result[]> {
+		return this.withOption('returning', new Returning(column)) as UpdateBuilder<Returning.Result[]>
 	}
 
-	public from(from: SelectBuilder.Callback): UpdateBuilder.UpdateBuilderState<Result, Filled | 'from'> {
+	public from(from: SelectBuilder.Callback): UpdateBuilder<Result> {
 		return this.withOption('from', from)
 	}
 
-	public where(where: Where.Expression): UpdateBuilder.UpdateBuilderState<Result, Filled | 'where'> {
+	public where(where: Where.Expression): UpdateBuilder<Result> {
 		return this.withOption('where', this.options.where.withWhere(where))
@@ -69,3 +64,3 @@ }
 		if (this.options.from !== undefined) {
-			let fromQb: SelectBuilder<SelectBuilder.Result, any> = SelectBuilder.create()
+			let fromQb: SelectBuilder<SelectBuilder.Result> = SelectBuilder.create()
 			fromQb = this.options.from(fromQb)
@@ -95,7 +90,7 @@ fromQb = this.options.where.values.reduce((builder, value) => builder.where(value), fromQb)
 		value: V,
-	): UpdateBuilder.UpdateBuilderState<Result, Filled | K> {
-		return new UpdateBuilder<Result, Filled | K>({
+	): UpdateBuilder<Result> {
+		return new UpdateBuilder<Result>({
 			...this.options,
 			[key]: value,
-		}) as UpdateBuilder.UpdateBuilderState<Result, Filled | K>
+		})
 	}
@@ -121,17 +116,4 @@ }
 	}
-
-	export type UpdateBuilderWithoutExecute<
-		Result extends UpdateResult,
-		Filled extends keyof UpdateBuilder<Result, Filled>
-	> = Pick<UpdateBuilder<Result, Filled>, Exclude<keyof UpdateBuilder<Result, Filled>, 'execute' | 'createQuery'>>
-
-	export type UpdateBuilderState<Result extends UpdateResult, Filled extends keyof UpdateBuilder<Result, Filled>> =
-		| 'table'
-		| 'values' extends Filled
-		? UpdateBuilder<Result, Filled>
-		: UpdateBuilderWithoutExecute<Result, Filled>
-
-	export type NewUpdateBuilder = UpdateBuilder.UpdateBuilderState<UpdateBuilder.AffectedRows, never>
 }
 
 export { UpdateBuilder }

src/client/Client.ts

@@ -25,15 +25,15 @@ import { DeleteBuilder, InsertBuilder, SelectBuilder, UpdateBuilder } from '../builders'
 
-	selectBuilder<Result = SelectBuilder.Result>(): SelectBuilder<Result, never> {
+	selectBuilder<Result = SelectBuilder.Result>(): SelectBuilder<Result> {
 		return SelectBuilder.create<Result>()
 	}
 
-	insertBuilder(): InsertBuilder.NewInsertBuilder {
+	insertBuilder(): InsertBuilder<InsertBuilder.AffectedRows> {
 		return InsertBuilder.create()
 	}
 
-	updateBuilder(): UpdateBuilder.NewUpdateBuilder {
+	updateBuilder(): UpdateBuilder<UpdateBuilder.AffectedRows> {
 		return UpdateBuilder.create()
 	}
 
-	deleteBuilder(): DeleteBuilder.NewDeleteBuilder {
+	deleteBuilder(): DeleteBuilder<DeleteBuilder.AffectedRows> {
 		return DeleteBuilder.create()
@@ -40,0 +40,0 @@ }

src/client/Connection.ts

@@ -31,5 +31,3 @@ import { Pool, PoolConfig } from 'pg'
 
-			if (!transaction.isClosed) {
-				await transaction.commit()
-			}
+			await transaction.commitUnclosed()
 			client.release()
@@ -39,5 +37,3 @@
 		} catch (e) {
-			if (!transaction.isClosed) {
-				await transaction.rollback()
-			}
+			await transaction.rollbackUnclosed()
 			client.release(e)
@@ -44,0 +40,0 @@ throw e

src/client/execution.ts

@@ -1,2 +0,2 @@
-import { PoolClient } from 'pg'
+import { ClientBase } from 'pg'
 import { EventManager } from './EventManager'
@@ -18,3 +18,3 @@ import { Connection } from './Connection'
 export async function executeQuery<Row extends Record<string, any>>(
-	pgClient: PoolClient,
+	pgClient: ClientBase,
 	eventManager: EventManager,
@@ -21,0 +21,0 @@ { sql, parameters, meta, timing }: Connection.Query & Connection.QueryConfig,

src/client/index.ts

@@ -6,1 +6,2 @@ export * from './Client'
 export * from './Transaction'
+export * from './SingleConnection'

src/client/Transaction.ts

 import { Connection } from './Connection'
 import { EventManager } from './EventManager'
-import { PoolClient } from 'pg'
+import { ClientBase, PoolClient } from 'pg'
 import { wrapIdentifier } from '../utils'
@@ -19,3 +19,3 @@ import { executeQuery } from './execution'
 	constructor(
-		private readonly pgClient: PoolClient,
+		private readonly pgClient: ClientBase,
 		public readonly eventManager: EventManager,
@@ -66,2 +66,9 @@ private readonly config: Connection.QueryConfig,
 
+	async rollbackUnclosed(): Promise<void> {
+		if (this.isClosed) {
+			return
+		}
+		await this.rollback()
+	}
+
 	async commit(): Promise<void> {
@@ -71,2 +78,9 @@ await this.close('COMMIT')
 
+	async commitUnclosed(): Promise<void> {
+		if (this.isClosed) {
+			return
+		}
+		await this.commit()
+	}
+
 	private async close(command: string) {
@@ -92,3 +106,3 @@ await this.pgClient.query(command)
 		private readonly transactionInst: Transaction,
-		private readonly pgClient: PoolClient,
+		private readonly pgClient: ClientBase,
 	) {}
@@ -95,0 +109,0 @@

tests/cases/unit/queryBuilderTest.ts

 import 'jasmine'
-import {
-	Client,
-	ConditionBuilder,
-	ConflictActionType,
-	InsertBuilder,
-	LimitByGroupWrapper,
-	LockType,
-	Operator,
-	SelectBuilder,
-} from '../../../src'
+import { Client, ConflictActionType, LimitByGroupWrapper, LockType, Operator } from '../../../src'
 import { createConnectionMock } from '@contember/database-tester'
@@ -148,4 +139,4 @@ import { SQL } from '../../src/tags'
 			sql: SQL`
-				with "root_" as (select ? :: text as "title", ? :: int as "id", ? :: text as "content") 
-				insert into "public"."author" ("id", "title") 
+				with "root_" as (select ? :: text as "title", ? :: int as "id", ? :: text as "content")
+				insert into "public"."author" ("id", "title")
 					select "id", "title" from "root_"
@@ -288,6 +279,6 @@ on conflict do nothing returning "id"`,
 			},
-			sql: SQL`with "data" as 
-			(select * from "public"."abc") 
-			delete from "public"."bar" 
-			using "data" as "data" 
+			sql: SQL`with "data" as
+			(select * from "public"."abc")
+			delete from "public"."bar"
+			using "data" as "data"
 			where "data"."a" >= ? returning "xyz"`,
@@ -334,6 +325,6 @@ parameters: [1],
 			},
-			sql: SQL`with "data" as 
-			(select "foo"."bar", 
-				 row_number() over(partition by "foo"."lorem" order by "foo"."ipsum" asc) as "rowNumber_" 
-			 from "public"."foo" order by "foo"."ipsum" asc) 
+			sql: SQL`with "data" as
+			(select "foo"."bar",
+				 row_number() over(partition by "foo"."lorem" order by "foo"."ipsum" asc) as "rowNumber_"
+			 from "public"."foo" order by "foo"."ipsum" asc)
 			select "data".* from "data" where "data"."rowNumber_" > ? and "data"."rowNumber_" <= ?`,
@@ -359,2 +350,50 @@ parameters: [1, 4],
 	})
+
+	it('select union', async () => {
+		await execute({
+			query: async wrapper => {
+				const qb = wrapper
+					.selectBuilder()
+					.select('id')
+					.from('foo')
+					.unionAll(qb => qb.select('id').from('bar'))
+
+				await qb.getResult(wrapper)
+			},
+			sql: SQL`select "id" from "public"."foo" union all ( select "id" from "public"."bar")`,
+			parameters: [],
+		})
+	})
+
+	it('select with recursive', async () => {
+		await execute({
+			query: async wrapper => {
+				const qb = wrapper
+					.selectBuilder()
+					.withRecursive('recent_events', qb =>
+						qb
+							.select('id')
+							.from('events')
+							.where({ id: '123' })
+							.unionAll(qb =>
+								qb
+									.select('id')
+									.from('events')
+									.from('recent_events')
+									.where(expr => expr.columnsEq(['events', 'id'], ['recent_events', 'previous_id'])),
+							),
+					)
+					.select('id')
+					.from('recent_events')
+
+				await qb.getResult(wrapper)
+			},
+			sql: SQL`
+			with recursive
+     			"recent_events" as (select "id" from "public"."events" where "id" = ?
+					union all ( select "id" from "public"."events", "recent_events" where "events"."id" = "recent_events"."previous_id"))
+			select "id" from "recent_events"`,
+			parameters: ['123'],
+		})
+	})
 })

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

564342

increased by1.9%

Number of package files

210

increased by2.44%

Lines of code

4698

increased by4.08%

Dependency changes

• Updated@contember/queryable@^0.8.0-alpha.1