@balena/abstract-sql-compiler - npm Package Compare versions

Comparing version 10.0.0-build-renovate-balena-odata-parser-3-x-5d630054862b2fb289f09d7d8aeafb989815667d-1 to 10.0.0-build-update-dependencies-28c15f305c99bd1e581a93fddfc985b82d20bdf8-1

out/AbstractSQLCompiler.d.ts

@@ -6,6 +6,6 @@ export declare const enum Engines {
 }
-import { Binding, SqlResult } from './AbstractSQLRules2SQL';
+import type { Binding, SqlResult } from './AbstractSQLRules2SQL';
 export { Binding, SqlResult } from './AbstractSQLRules2SQL';
-import * as _ from 'lodash';
-import { ReferencedFields, RuleReferencedFields, ModifiedFields } from './referenced-fields';
+import _ from 'lodash';
+import type { ReferencedFields, RuleReferencedFields, ModifiedFields } from './referenced-fields';
 export type { ReferencedFields, RuleReferencedFields, ModifiedFields };
@@ -67,3 +67,5 @@ export type NullNode = ['Null'];
 export type StartsWithNode = ['StartsWith', TextTypeNodes, TextTypeNodes];
-export type StrictBooleanTypeNodes = BooleanNode | EqualsNode | NotEqualsNode | IsDistinctFromNode | IsNotDistinctFromNode | GreaterThanNode | GreaterThanOrEqualNode | LessThanNode | LessThanOrEqualNode | BetweenNode | LikeNode | InNode | NotInNode | ExistsNode | NotExistsNode | NotNode | AndNode | OrNode | StartsWithNode;
+export type EndsWithNode = ['EndsWith', TextTypeNodes, TextTypeNodes];
+export type ContainsNode = ['Contains', TextTypeNodes, TextTypeNodes];
+export type StrictBooleanTypeNodes = BooleanNode | EqualsNode | NotEqualsNode | IsDistinctFromNode | IsNotDistinctFromNode | GreaterThanNode | GreaterThanOrEqualNode | LessThanNode | LessThanOrEqualNode | BetweenNode | LikeNode | InNode | NotInNode | ExistsNode | NotExistsNode | NotNode | AndNode | OrNode | StartsWithNode | EndsWithNode | ContainsNode;
 export type BooleanTypeNodes = StrictBooleanTypeNodes | UnknownTypeNodes;
@@ -272,2 +274,10 @@ export type YearNode = ['Year', DateTypeNodes];
 }
+export interface Index {
+    type: string;
+    fields: string[];
+    name?: string;
+    description?: string;
+    distinctNulls?: boolean;
+    predicate?: BooleanTypeNodes;
+}
 export interface Check {
@@ -290,6 +300,3 @@ description?: string;
     fields: AbstractSqlField[];
-    indexes: Array<{
-        type: string;
-        fields: string[];
-    }>;
+    indexes: Index[];
     primitive: false | string;
@@ -367,3 +374,3 @@ triggers?: Trigger[];
 }
-export declare const isAliasNode: <T>(n: AbstractSqlType | AliasNode<T>) => n is AliasNode<T>;
+export declare const isAliasNode: <T>(n: AliasNode<T> | AbstractSqlType) => n is AliasNode<T>;
 export declare const isFromNode: (n: AbstractSqlType) => n is FromNode;
@@ -383,5 +390,6 @@ export declare const isTableNode: (n: AbstractSqlType) => n is TableNode;
     optimizeSchema: (abstractSqlModel: AbstractSqlModel, createCheckConstraints?: boolean) => AbstractSqlModel;
+    generateRuleSlug: (tableName: string, ruleBody: AbstractSqlType) => string;
     compileSchema: (abstractSqlModel: AbstractSqlModel) => SqlModel;
     compileRule: (abstractSQL: AbstractSqlQuery) => SqlResult | [SqlResult, SqlResult];
-    dataTypeValidate: (value: any, field: Pick<AbstractSqlField, 'dataType' | 'required'>) => Promise<any>;
+    dataTypeValidate: (value: any, field: Pick<AbstractSqlField, "dataType" | "required">) => Promise<any>;
     getReferencedFields: (ruleBody: AbstractSqlQuery) => ReferencedFields;
@@ -393,5 +401,6 @@ getRuleReferencedFields: (ruleBody: AbstractSqlQuery) => RuleReferencedFields;
     optimizeSchema: (abstractSqlModel: AbstractSqlModel, createCheckConstraints?: boolean) => AbstractSqlModel;
+    generateRuleSlug: (tableName: string, ruleBody: AbstractSqlType) => string;
     compileSchema: (abstractSqlModel: AbstractSqlModel) => SqlModel;
     compileRule: (abstractSQL: AbstractSqlQuery) => SqlResult | [SqlResult, SqlResult];
-    dataTypeValidate: (value: any, field: Pick<AbstractSqlField, 'dataType' | 'required'>) => Promise<any>;
+    dataTypeValidate: (value: any, field: Pick<AbstractSqlField, "dataType" | "required">) => Promise<any>;
     getReferencedFields: (ruleBody: AbstractSqlQuery) => ReferencedFields;
@@ -403,5 +412,6 @@ getRuleReferencedFields: (ruleBody: AbstractSqlQuery) => RuleReferencedFields;
     optimizeSchema: (abstractSqlModel: AbstractSqlModel, createCheckConstraints?: boolean) => AbstractSqlModel;
+    generateRuleSlug: (tableName: string, ruleBody: AbstractSqlType) => string;
     compileSchema: (abstractSqlModel: AbstractSqlModel) => SqlModel;
     compileRule: (abstractSQL: AbstractSqlQuery) => SqlResult | [SqlResult, SqlResult];
-    dataTypeValidate: (value: any, field: Pick<AbstractSqlField, 'dataType' | 'required'>) => Promise<any>;
+    dataTypeValidate: (value: any, field: Pick<AbstractSqlField, "dataType" | "required">) => Promise<any>;
     getReferencedFields: (ruleBody: AbstractSqlQuery) => ReferencedFields;
@@ -408,0 +418,0 @@ getRuleReferencedFields: (ruleBody: AbstractSqlQuery) => RuleReferencedFields;

out/AbstractSQLCompiler.js

 "use strict";
+var __importDefault = (this && this.__importDefault) || function (mod) {
+    return (mod && mod.__esModule) ? mod : { "default": mod };
+};
 Object.defineProperty(exports, "__esModule", { value: true });
-exports.websql = exports.mysql = exports.postgres = exports.compileRule = exports.isWhereNode = exports.isSelectNode = exports.isSelectQueryNode = exports.isResourceNode = exports.isTableNode = exports.isFromNode = exports.isAliasNode = exports.Engines = void 0;
+exports.websql = exports.mysql = exports.postgres = exports.isWhereNode = exports.isSelectNode = exports.isSelectQueryNode = exports.isResourceNode = exports.isTableNode = exports.isFromNode = exports.isAliasNode = exports.Engines = void 0;
+exports.compileRule = compileRule;
 var Engines;
@@ -9,12 +13,12 @@ (function (Engines) {
     Engines["websql"] = "websql";
-})(Engines = exports.Engines || (exports.Engines = {}));
+})(Engines || (exports.Engines = Engines = {}));
 const AbstractSQLOptimiser_1 = require("./AbstractSQLOptimiser");
 const AbstractSQLRules2SQL_1 = require("./AbstractSQLRules2SQL");
-const sbvrTypes = require("@balena/sbvr-types");
-const _ = require("lodash");
+const sbvr_types_1 = __importDefault(require("@balena/sbvr-types"));
+const lodash_1 = __importDefault(require("lodash"));
 const AbstractSQLSchemaOptimiser_1 = require("./AbstractSQLSchemaOptimiser");
 const referenced_fields_1 = require("./referenced-fields");
-const validateTypes = _.mapValues(sbvrTypes, ({ validate }) => validate);
+const validateTypes = lodash_1.default.mapValues(sbvr_types_1.default, ({ validate }) => validate);
 const dataTypeValidate = async (value, field) => {
-    const { dataType, required } = field;
+    const { dataType, required = false } = field;
     const validateFn = validateTypes[dataType];
@@ -56,3 +60,3 @@ if (validateFn != null) {
     }
-    const dbType = sbvrTypes?.[dataType]?.types?.[engine];
+    const dbType = sbvr_types_1.default?.[dataType]?.types?.[engine];
     if (dbType != null) {
@@ -97,3 +101,2 @@ if (typeof dbType === 'function') {
 }
-exports.compileRule = compileRule;
 const compileSchema = (abstractSqlModel, engine, ifNotExists) => {
@@ -180,2 +183,3 @@ abstractSqlModel = (0, AbstractSQLSchemaOptimiser_1.optimizeSchema)(abstractSqlModel, false);
         const createSqlElements = [];
+        const createIndexes = [];
         for (const field of table.fields) {
@@ -229,3 +233,25 @@ const { fieldName, references, dataType, computed } = field;
         for (const index of table.indexes) {
-            createSqlElements.push(index.type + '("' + index.fields.join('", "') + '")');
+            let nullsSql = '';
+            if (index.distinctNulls != null) {
+                nullsSql =
+                    index.distinctNulls === false
+                        ? ` NULLS NOT DISTINCT`
+                        : ` NULLS DISTINCT`;
+            }
+            if (index.predicate == null) {
+                createSqlElements.push(index.type + nullsSql + '("' + index.fields.join('", "') + '")');
+                continue;
+            }
+            if (index.name == null) {
+                throw new Error('No name provided for partial index');
+            }
+            const comment = index.description
+                ? `-- ${index.description.replaceAll(/\r?\n/g, '\n-- ')}\n`
+                : '';
+            const whereSql = compileRule(index.predicate, engine, true);
+            createIndexes.push(`\
+${comment}\
+CREATE ${index.type} INDEX IF NOT EXISTS "${index.name}"
+ON "${table.name}" ("${index.fields.join('", "')}")${nullsSql}
+WHERE (${whereSql});`);
         }
@@ -279,2 +305,3 @@ if (table.checks) {
 );`,
+                ...createIndexes,
                 ...createTriggers,
@@ -295,3 +322,3 @@ ],
                 if (dependency !== resourceName &&
-                    schemaDependencyMap.hasOwnProperty(dependency)) {
+                    Object.prototype.hasOwnProperty.call(schemaDependencyMap, dependency)) {
                     unsolvedDependency = true;
@@ -373,2 +400,3 @@ break;
         optimizeSchema: AbstractSQLSchemaOptimiser_1.optimizeSchema,
+        generateRuleSlug: AbstractSQLSchemaOptimiser_1.generateRuleSlug,
         compileSchema: (abstractSqlModel) => compileSchema(abstractSqlModel, engine, ifNotExists),
@@ -375,0 +403,0 @@ compileRule: (abstractSQL) => compileRule(abstractSQL, engine, false),

out/AbstractSQLOptimiser.d.ts

@@ -1,2 +0,2 @@
-import { AbstractSqlQuery } from './AbstractSQLCompiler';
+import type { AbstractSqlQuery } from './AbstractSQLCompiler';
 export declare const AbstractSQLOptimiser: (abstractSQL: AbstractSqlQuery, $noBinds?: boolean) => AbstractSqlQuery;

out/AbstractSQLOptimiser.js

 "use strict";
+var __createBinding = (this && this.__createBinding) || (Object.create ? (function(o, m, k, k2) {
+    if (k2 === undefined) k2 = k;
+    var desc = Object.getOwnPropertyDescriptor(m, k);
+    if (!desc || ("get" in desc ? !m.__esModule : desc.writable || desc.configurable)) {
+      desc = { enumerable: true, get: function() { return m[k]; } };
+    }
+    Object.defineProperty(o, k2, desc);
+}) : (function(o, m, k, k2) {
+    if (k2 === undefined) k2 = k;
+    o[k2] = m[k];
+}));
+var __setModuleDefault = (this && this.__setModuleDefault) || (Object.create ? (function(o, v) {
+    Object.defineProperty(o, "default", { enumerable: true, value: v });
+}) : function(o, v) {
+    o["default"] = v;
+});
+var __importStar = (this && this.__importStar) || function (mod) {
+    if (mod && mod.__esModule) return mod;
+    var result = {};
+    if (mod != null) for (var k in mod) if (k !== "default" && Object.prototype.hasOwnProperty.call(mod, k)) __createBinding(result, mod, k);
+    __setModuleDefault(result, mod);
+    return result;
+};
+var __importDefault = (this && this.__importDefault) || function (mod) {
+    return (mod && mod.__esModule) ? mod : { "default": mod };
+};
 Object.defineProperty(exports, "__esModule", { value: true });
 exports.AbstractSQLOptimiser = void 0;
-const _ = require("lodash");
-const AbstractSQLRules2SQL = require("./AbstractSQLRules2SQL");
+const lodash_1 = __importDefault(require("lodash"));
+const AbstractSQLRules2SQL = __importStar(require("./AbstractSQLRules2SQL"));
 const { isAbstractSqlQuery, getAbstractSqlQuery, checkArgs, checkMinArgs, isNotNullable, } = AbstractSQLRules2SQL;
@@ -208,3 +234,3 @@ let helped = false;
 };
-const Text = matchArgs('Text', _.identity);
+const Text = matchArgs('Text', lodash_1.default.identity);
 const Value = (arg) => {
@@ -214,3 +240,3 @@ switch (arg) {
             return arg;
-        default:
+        default: {
             const [type, ...rest] = arg;
@@ -230,2 +256,3 @@ switch (type) {
             }
+        }
     }
@@ -284,2 +311,7 @@ };
 const SubtractDateMatcher = tryMatches(matchArgs('SubtractDateDate', DateValue, DateValue), matchArgs('SubtractDateDuration', DateValue, DurationValue), matchArgs('SubtractDateNumber', DateValue, NumericValue));
+const EndsWithMatcher = rewriteMatch('Endswith', [TextValue, TextValue], Helper(([haystack, needle]) => [
+    'Like',
+    haystack,
+    ['Concatenate', ['EmbeddedText', '%'], ['EscapeForLike', needle]],
+]));
 const typeRules = {
@@ -424,10 +456,10 @@ UnionQuery: (args) => {
     Sum: matchArgs('Sum', NumericValue),
-    Field: matchArgs('Field', _.identity),
-    ReferencedField: matchArgs('ReferencedField', _.identity, _.identity),
-    Cast: matchArgs('Cast', AnyValue, _.identity),
+    Field: matchArgs('Field', lodash_1.default.identity),
+    ReferencedField: matchArgs('ReferencedField', lodash_1.default.identity, lodash_1.default.identity),
+    Cast: matchArgs('Cast', AnyValue, lodash_1.default.identity),
     Number: NumberMatch('Number'),
     Real: NumberMatch('Real'),
     Integer: NumberMatch('Integer'),
-    Boolean: matchArgs('Boolean', _.identity),
-    EmbeddedText: matchArgs('EmbeddedText', _.identity),
+    Boolean: matchArgs('Boolean', lodash_1.default.identity),
+    EmbeddedText: matchArgs('EmbeddedText', lodash_1.default.identity),
     Null: matchArgs('Null'),
@@ -576,3 +608,3 @@ CurrentTimestamp: matchArgs('CurrentTimestamp'),
     ToJSON: matchArgs('ToJSON', AnyValue),
-    Any: matchArgs('Any', AnyValue, _.identity),
+    Any: matchArgs('Any', AnyValue, lodash_1.default.identity),
     Coalesce: (args) => {
@@ -595,3 +627,3 @@ checkMinArgs('Coalesce', args, 2);
                 switch (type) {
-                    case 'When':
+                    case 'When': {
                         checkArgs('When', rest, 2);
@@ -601,2 +633,3 @@ const matches = BooleanValue(getAbstractSqlQuery(rest, 0));
                         return ['When', matches, resultValue];
+                    }
                     case 'Else':
@@ -622,3 +655,3 @@ if (index !== args.length - 1) {
         let maybeHelped = false;
-        const conditions = _.flatMap(args, (arg) => {
+        const conditions = lodash_1.default.flatMap(args, (arg) => {
             if (!isAbstractSqlQuery(arg)) {
@@ -708,3 +741,3 @@ throw new SyntaxError(`Expected AbstractSqlQuery array but got ${typeof arg}`);
                     fieldBucket[0][1],
-                    ..._.flatMap(fieldBucket, (field) => field.slice(2)),
+                    ...lodash_1.default.flatMap(fieldBucket, (field) => field.slice(2)),
                 ];
@@ -734,3 +767,3 @@ }
         let maybeHelped = false;
-        const conditions = _.flatMap(args, (arg) => {
+        const conditions = lodash_1.default.flatMap(args, (arg) => {
             if (!isAbstractSqlQuery(arg)) {
@@ -820,3 +853,3 @@ throw new SyntaxError(`Expected AbstractSqlQuery array but got ${typeof arg}`);
                     fieldBucket[0][1],
-                    ..._.flatMap(fieldBucket, (field) => field.slice(2)),
+                    ...lodash_1.default.flatMap(fieldBucket, (field) => field.slice(2)),
                 ];
@@ -849,3 +882,3 @@ }
     Value: Text,
-    Date: matchArgs('Date', _.identity),
+    Date: matchArgs('Date', lodash_1.default.identity),
     Duration: (args) => {
@@ -857,7 +890,7 @@ checkArgs('Duration', args, 1);
         }
-        duration = _(duration)
+        duration = (0, lodash_1.default)(duration)
             .pick('negative', 'day', 'hour', 'minute', 'second')
-            .omitBy(_.isNil)
+            .omitBy(lodash_1.default.isNil)
             .value();
-        if (_(duration).omit('negative').isEmpty()) {
+        if ((0, lodash_1.default)(duration).omit('negative').isEmpty()) {
             throw new SyntaxError('Invalid duration');
@@ -981,3 +1014,3 @@ }
                     break;
-                case 'Values':
+                case 'Values': {
                     if (values.length !== 0) {
@@ -992,8 +1025,3 @@ throw new SyntaxError(`'InsertQuery' can only accept one '${type}'`);
                             case 'UnionQuery':
-                                values = [
-                                    [
-                                        'Values',
-                                        typeRules[valuesType](valuesRest),
-                                    ],
-                                ];
+                                values = [['Values', typeRules[valuesType](valuesRest)]];
                                 break;
@@ -1005,2 +1033,3 @@ default:
                     break;
+                }
                 case 'From':
@@ -1050,3 +1079,3 @@ tables.push(typeRules[type](rest));
                     break;
-                case 'Values':
+                case 'Values': {
                     if (values.length !== 0) {
@@ -1060,2 +1089,3 @@ throw new SyntaxError(`'UpdateQuery' can only accept one '${type}'`);
                     break;
+                }
                 case 'From':
@@ -1139,7 +1169,4 @@ tables.push(typeRules[type](rest));
     ])),
-    Endswith: rewriteMatch('Endswith', [TextValue, TextValue], Helper(([haystack, needle]) => [
-        'Like',
-        haystack,
-        ['Concatenate', ['EmbeddedText', '%'], ['EscapeForLike', needle]],
-    ])),
+    Endswith: EndsWithMatcher,
+    EndsWith: EndsWithMatcher,
     IndexOf: rewriteMatch('IndexOf', [TextValue, TextValue], Helper(([haystack, needle]) => [
@@ -1169,3 +1196,3 @@ 'Subtract',
                 break;
-            case 'UpsertQuery':
+            case 'UpsertQuery': {
                 checkArgs('UpsertQuery', rest, 2);
@@ -1184,2 +1211,3 @@ const insertQuery = getAbstractSqlQuery(rest, 0);
                 break;
+            }
             default:
@@ -1186,0 +1214,0 @@ abstractSQL = AnyValue(abstractSQL);

out/AbstractSQLRules2SQL.d.ts

@@ -1,2 +0,3 @@
-import { AbstractSqlQuery, AbstractSqlType, Engines, InsertQueryNode, SelectQueryNode, UnionQueryNode, UpdateQueryNode, DeleteQueryNode, UpsertQueryNode } from './AbstractSQLCompiler';
+import type { AbstractSqlQuery, AbstractSqlType, InsertQueryNode, SelectQueryNode, UnionQueryNode, UpdateQueryNode, DeleteQueryNode, UpsertQueryNode, StrictTextTypeNodes, StrictNumberTypeNodes, StrictBooleanTypeNodes, StrictDateTypeNodes, StrictDurationTypeNodes, StrictTextArrayTypeNodes, StrictJSONTypeNodes } from './AbstractSQLCompiler';
+import { Engines } from './AbstractSQLCompiler';
 export type Binding = [string, any] | ['Bind', number | string | [string, string]];
@@ -16,9 +17,9 @@ export interface SqlResult {
 };
-export declare const isTextValue: (type: unknown) => type is "Text" | "EmbeddedText" | "Concatenate" | "ConcatenateWithSeparator" | "Lower" | "Upper" | "Trim" | "Substring" | "Right" | "Replace" | "ExtractJSONPathAsText" | "EscapeForLike";
-export declare const isNumericValue: (type: unknown) => type is "Number" | "Integer" | "Real" | "Add" | "Subtract" | "Multiply" | "Divide" | "StrPos" | "Year" | "Month" | "Day" | "Hour" | "Minute" | "Second" | "Fractionalseconds" | "Totalseconds" | "Round" | "Floor" | "Ceiling" | "Count" | "Average" | "Sum" | "BitwiseAnd" | "BitwiseShiftRight" | "CharacterLength" | "SubtractDateDate";
-export declare const isBooleanValue: (type: unknown) => type is "Boolean" | "Equals" | "NotEquals" | "IsDistinctFrom" | "IsNotDistinctFrom" | "GreaterThan" | "GreaterThanOrEqual" | "LessThan" | "LessThanOrEqual" | "Between" | "Like" | "In" | "NotIn" | "Exists" | "NotExists" | "Not" | "And" | "Or" | "StartsWith";
-export declare const isDateValue: (type: unknown) => type is "Date" | "ToDate" | "ToTime" | "CurrentTimestamp" | "CurrentDate" | "DateTrunc" | "SubtractDateNumber" | "SubtractDateDuration" | "AddDateNumber" | "AddDateDuration";
-export declare const isArrayValue: (type: unknown) => type is "TextArray";
-export declare const isJSONValue: (type: unknown) => type is "AggregateJSON" | "ToJSON";
-export declare const isDurationValue: (type: unknown) => type is "Duration";
+export declare const isTextValue: (type: unknown) => type is StrictTextTypeNodes[0];
+export declare const isNumericValue: (type: unknown) => type is StrictNumberTypeNodes[0];
+export declare const isBooleanValue: (type: unknown) => type is StrictBooleanTypeNodes[0];
+export declare const isDateValue: (type: unknown) => type is StrictDateTypeNodes[0];
+export declare const isArrayValue: (type: unknown) => type is StrictTextArrayTypeNodes[0];
+export declare const isJSONValue: (type: unknown) => type is StrictJSONTypeNodes[0];
+export declare const isDurationValue: (type: unknown) => type is StrictDurationTypeNodes[0];
 export declare const isFieldValue: (type: unknown) => type is "Field" | "ReferencedField";
@@ -25,0 +26,0 @@ export declare const isNotNullable: (node: AbstractSqlType) => boolean;

out/AbstractSQLRules2SQL.js

 "use strict";
+var __importDefault = (this && this.__importDefault) || function (mod) {
+    return (mod && mod.__esModule) ? mod : { "default": mod };
+};
 Object.defineProperty(exports, "__esModule", { value: true });
-exports.AbstractSQLRules2SQL = exports.checkMinArgs = exports.checkArgs = exports.getAbstractSqlQuery = exports.isAbstractSqlQuery = exports.isNotNullable = exports.isFieldValue = exports.isDurationValue = exports.isJSONValue = exports.isArrayValue = exports.isDateValue = exports.isBooleanValue = exports.isNumericValue = exports.isTextValue = exports.comparisons = void 0;
-const _ = require("lodash");
-const sbvrTypes = require("@balena/sbvr-types");
+exports.checkMinArgs = exports.checkArgs = exports.getAbstractSqlQuery = exports.isAbstractSqlQuery = exports.isNotNullable = exports.isFieldValue = exports.isDurationValue = exports.isJSONValue = exports.isArrayValue = exports.isDateValue = exports.isBooleanValue = exports.isNumericValue = exports.isTextValue = exports.comparisons = void 0;
+exports.AbstractSQLRules2SQL = AbstractSQLRules2SQL;
+const lodash_1 = __importDefault(require("lodash"));
+const sbvr_types_1 = __importDefault(require("@balena/sbvr-types"));
 let fieldOrderings = [];
@@ -54,6 +58,7 @@ let fieldOrderingsLookup = {};
         case 'SelectQuery':
-        case 'UnionQuery':
+        case 'UnionQuery': {
             const nestedIndent = NestedIndent(indent);
             const query = typeRules[type](rest, nestedIndent);
             return '(' + nestedIndent + query + indent + ')';
+        }
         default:
@@ -298,6 +303,7 @@ throw new Error(`Invalid "UnknownValue" type: ${type}`);
         switch (type) {
-            case 'On':
+            case 'On': {
                 (0, exports.checkArgs)('On', rest, 1);
                 const ruleBody = BooleanValue((0, exports.getAbstractSqlQuery)(rest, 0), NestedIndent(indent));
                 return sqlJoinType + from + ' ON ' + ruleBody;
+            }
             default:
@@ -477,3 +483,3 @@ throw new SyntaxError(`'${joinType}' clause does not support '${type}' clause`);
             return 'DEFAULT';
-        default:
+        default: {
             const [type, ...rest] = arg;
@@ -493,2 +499,3 @@ switch (type) {
             }
+        }
     }
@@ -509,7 +516,8 @@ };
         case 'SelectQuery':
-        case 'UnionQuery':
+        case 'UnionQuery': {
             const nestedindent = NestedIndent(indent);
             const query = typeRules[type](rest, nestedindent);
             return '(' + nestedindent + query + indent + ')';
-        case 'Table':
+        }
+        case 'Table': {
             (0, exports.checkArgs)('Table', rest, 1);
@@ -521,2 +529,3 @@ const [table] = rest;
             return escapeField(table);
+        }
         default:
@@ -529,6 +538,7 @@ throw new SyntaxError(`From does not support ${type}`);
     switch (type) {
-        case 'Alias':
+        case 'Alias': {
             (0, exports.checkArgs)('Alias', rest, 2);
             const field = matchFn((0, exports.getAbstractSqlQuery)(rest, 0), indent);
             return `${field} AS "${rest[1]}"`;
+        }
         default:
@@ -760,7 +770,7 @@ return matchFn(args, indent);
         const typeName = args[1];
-        if (!sbvrTypes[typeName] || !sbvrTypes[typeName].types[engine]) {
+        if (!sbvr_types_1.default[typeName]?.types[engine]) {
             throw new SyntaxError(`Invalid cast type: ${typeName}`);
         }
         let type;
-        const dbType = sbvrTypes[typeName].types[engine];
+        const dbType = sbvr_types_1.default[typeName].types[engine];
         if (typeof dbType === 'function') {
@@ -772,2 +782,5 @@ type = dbType.castType;
         }
+        else if (dbType.toUpperCase() === 'BIGSERIAL') {
+            type = 'BIGINT';
+        }
         else {
@@ -1052,3 +1065,3 @@ type = dbType;
         const value = AnyValue((0, exports.getAbstractSqlQuery)(args, 0), indent);
-        const innerType = sbvrTypes[args[1]].types[engine];
+        const innerType = sbvr_types_1.default[args[1]].types[engine];
         return `ANY(CAST(${value} AS ${innerType}[]))`;
@@ -1076,3 +1089,3 @@ },
             switch (type) {
-                case 'When':
+                case 'When': {
                     (0, exports.checkArgs)('When', rest, 2);
@@ -1082,2 +1095,3 @@ const matches = BooleanValue((0, exports.getAbstractSqlQuery)(rest, 0), NestedIndent(nestedIndent));
                     return 'WHEN ' + matches + ' THEN ' + resultValue;
+                }
                 case 'Else':
@@ -1147,7 +1161,7 @@ if (index !== args.length - 1) {
         }
-        duration = _(duration)
+        duration = (0, lodash_1.default)(duration)
             .pick('negative', 'day', 'hour', 'minute', 'second')
-            .omitBy(_.isNil)
+            .omitBy(lodash_1.default.isNil)
             .value();
-        if (_(duration).omit('negative').isEmpty()) {
+        if ((0, lodash_1.default)(duration).omit('negative').isEmpty()) {
             throw new SyntaxError('Invalid duration');
@@ -1176,6 +1190,7 @@ }
             case 'SelectQuery':
-            case 'UnionQuery':
+            case 'UnionQuery': {
                 const nestedIndent = NestedIndent(indent);
                 const query = typeRules[type](rest, nestedIndent);
                 return 'EXISTS (' + nestedIndent + query + indent + ')';
+            }
             default:
@@ -1191,6 +1206,7 @@ return AnyValue(arg, indent) + ' IS NOT NULL';
             case 'SelectQuery':
-            case 'UnionQuery':
+            case 'UnionQuery': {
                 const nestedIndent = NestedIndent(indent);
                 const query = typeRules[type](rest, nestedIndent);
                 return 'NOT EXISTS (' + nestedIndent + query + indent + ')';
+            }
             default:
@@ -1245,3 +1261,3 @@ return AnyValue(arg, indent) + ' IS NULL';
                     break;
-                case 'Values':
+                case 'Values': {
                     if (values.length !== 0) {
@@ -1263,2 +1279,3 @@ throw new SyntaxError(`'InsertQuery' can only accept one '${type}'`);
                     break;
+                }
                 case 'From':
@@ -1308,3 +1325,3 @@ tables.push(typeRules[type](rest, indent));
                     break;
-                case 'Values':
+                case 'Values': {
                     if (values.length !== 0) {
@@ -1318,2 +1335,3 @@ throw new SyntaxError(`'UpdateQuery' can only accept one '${type}'`);
                     break;
+                }
                 case 'From':
@@ -1408,6 +1426,7 @@ tables.push(typeRules[type](rest, indent));
         case 'UpdateQuery':
-        case 'DeleteQuery':
+        case 'DeleteQuery': {
             const query = typeRules[type](rest, indent);
             return toSqlResult(query);
-        case 'UpsertQuery':
+        }
+        case 'UpsertQuery': {
             (0, exports.checkArgs)('UpsertQuery', rest, 2);
@@ -1427,3 +1446,4 @@ const insertQuery = (0, exports.getAbstractSqlQuery)(rest, 0);
             return [insert, update];
-        default:
+        }
+        default: {
             const value = AnyValue(abstractSQL, indent);
@@ -1437,5 +1457,5 @@ if (noBinds) {
             };
+        }
     }
 }
-exports.AbstractSQLRules2SQL = AbstractSQLRules2SQL;
 //# sourceMappingURL=AbstractSQLRules2SQL.js.map

out/AbstractSQLSchemaOptimiser.d.ts

@@ -7,3 +7,4 @@ export declare const enum Engines {
 export { Binding, SqlResult } from './AbstractSQLRules2SQL';
-import { AbstractSqlModel } from './AbstractSQLCompiler';
+import type { AbstractSqlModel, AbstractSqlType } from './AbstractSQLCompiler';
+export declare const generateRuleSlug: (tableName: string, ruleBody: AbstractSqlType) => string;
 export declare const optimizeSchema: (abstractSqlModel: AbstractSqlModel, createCheckConstraints?: boolean) => AbstractSqlModel;

out/AbstractSQLSchemaOptimiser.js

 "use strict";
+var __importDefault = (this && this.__importDefault) || function (mod) {
+    return (mod && mod.__esModule) ? mod : { "default": mod };
+};
 Object.defineProperty(exports, "__esModule", { value: true });
-exports.optimizeSchema = exports.Engines = void 0;
+exports.optimizeSchema = exports.generateRuleSlug = exports.Engines = void 0;
 var Engines;
@@ -9,6 +12,6 @@ (function (Engines) {
     Engines["websql"] = "websql";
-})(Engines = exports.Engines || (exports.Engines = {}));
+})(Engines || (exports.Engines = Engines = {}));
 const AbstractSQLOptimiser_1 = require("./AbstractSQLOptimiser");
-const sbvrTypes = require("@balena/sbvr-types");
-const _ = require("lodash");
+const sbvr_types_1 = __importDefault(require("@balena/sbvr-types"));
+const lodash_1 = __importDefault(require("lodash"));
 const AbstractSQLCompiler_1 = require("./AbstractSQLCompiler");
@@ -29,2 +32,7 @@ const countFroms = (n) => {
 };
+const generateRuleSlug = (tableName, ruleBody) => {
+    const sha = sbvr_types_1.default.SHA.validateSync(`${tableName}$${JSON.stringify(ruleBody)}`).replace(/^\$sha256\$/, '');
+    return `${tableName.slice(0, 30)}$${sha}`.slice(0, 63);
+};
+exports.generateRuleSlug = generateRuleSlug;
 const optimizeSchema = (abstractSqlModel, createCheckConstraints = true) => {
@@ -55,3 +63,3 @@ abstractSqlModel.rules = abstractSqlModel.rules
                 (ruleBody[0] === 'Equals' &&
-                    _.isEqual(ruleBody[2], ['Number', 0]))) &&
+                    lodash_1.default.isEqual(ruleBody[2], ['Number', 0]))) &&
             (0, AbstractSQLCompiler_1.isSelectQueryNode)(ruleBody[1])) {
@@ -88,4 +96,3 @@ const selectQueryNodes = ruleBody[1].slice(1);
                     const tableName = fromNode[1];
-                    const sha = sbvrTypes.SHA.validateSync(`${tableName}$${JSON.stringify(ruleBody)}`).replace(/^\$sha256\$/, '');
-                    const table = _.find(abstractSqlModel.tables, (t) => t.name === tableName);
+                    const table = lodash_1.default.find(abstractSqlModel.tables, (t) => t.name === tableName);
                     if (table) {
@@ -95,3 +102,3 @@ table.checks ??= [];
                             description: ruleSE,
-                            name: `${tableName.slice(0, 30)}$${sha}`.slice(0, 63),
+                            name: (0, exports.generateRuleSlug)(tableName, ruleBody),
                             abstractSql: whereNode,
@@ -98,0 +105,0 @@ });

out/referenced-fields.d.ts

@@ -1,2 +0,2 @@
-import { AbstractSqlQuery, EngineInstance, LfRuleInfo } from './AbstractSQLCompiler';
+import type { AbstractSqlQuery, EngineInstance, LfRuleInfo } from './AbstractSQLCompiler';
 export interface ReferencedFields {
@@ -3,0 +3,0 @@ [alias: string]: string[];

out/referenced-fields.js

 "use strict";
+var __importDefault = (this && this.__importDefault) || function (mod) {
+    return (mod && mod.__esModule) ? mod : { "default": mod };
+};
 Object.defineProperty(exports, "__esModule", { value: true });
 exports.insertAffectedIdsBinds = exports.getModifiedFields = exports.getRuleReferencedFields = exports.getReferencedFields = void 0;
-const _ = require("lodash");
+const lodash_1 = __importDefault(require("lodash"));
 const AbstractSQLCompiler_1 = require("./AbstractSQLCompiler");
@@ -10,3 +13,3 @@ const AbstractSQLOptimiser_1 = require("./AbstractSQLOptimiser");
     const referencedFields = (0, exports.getRuleReferencedFields)(ruleBody);
-    return _.mapValues(referencedFields, ({ update }) => _.uniq(update));
+    return lodash_1.default.mapValues(referencedFields, ({ update }) => lodash_1.default.uniq(update));
 };
@@ -156,3 +159,3 @@ exports.getReferencedFields = getReferencedFields;
     if (ruleBody[0] === 'Equals' &&
-        _.isEqual(ruleBody[2], ['Number', 0]) &&
+        lodash_1.default.isEqual(ruleBody[2], ['Number', 0]) &&
         (0, AbstractSQLCompiler_1.isSelectQueryNode)(ruleBody[1])) {
@@ -169,3 +172,3 @@ const select = ruleBody[1].find(AbstractSQLCompiler_1.isSelectNode);
         for (const method of Object.keys(tableRefs)) {
-            tableRefs[method] = _.uniq(tableRefs[method]);
+            tableRefs[method] = lodash_1.default.uniq(tableRefs[method]);
         }
@@ -205,3 +208,3 @@ }
     }
-    const fields = _(query)
+    const fields = (0, lodash_1.default)(query)
         .filter((v) => v != null && v[0] === 'Fields')
@@ -246,7 +249,8 @@ .flatMap((v) => v[1])
         case 'ToJSON':
-        case 'Where':
+        case 'Where': {
             const unaryOperation = abstractSql;
             assertAbstractSqlIsNotLegacy(unaryOperation[1]);
             return countTableSelects(unaryOperation[1], table);
-        case 'Count':
+        }
+        case 'Count': {
             const countNode = abstractSql;
@@ -257,2 +261,3 @@ if (countNode[1] !== '*') {
             return 0;
+        }
         case 'AddDateDuration':
@@ -272,3 +277,3 @@ case 'AddDateNumber':
         case 'SubtractDateDuration':
-        case 'SubtractDateNumber':
+        case 'SubtractDateNumber': {
             const binaryOperation = abstractSql;
@@ -281,6 +286,7 @@ const leftOperand = binaryOperation[1];
                 countTableSelects(rightOperand, table));
+        }
         case 'FullJoin':
         case 'Join':
         case 'LeftJoin':
-        case 'RightJoin':
+        case 'RightJoin': {
             const joinNode = abstractSql;
@@ -293,2 +299,3 @@ assertAbstractSqlIsNotLegacy(joinNode[1]);
             return sum + countTableSelects(joinNode[1], table);
+        }
         case 'And':
@@ -298,3 +305,3 @@ case 'Or':
         case 'TextArray':
-        case 'UnionQuery':
+        case 'UnionQuery': {
             const selectQueryNode = abstractSql;
@@ -306,4 +313,5 @@ for (const arg of selectQueryNode.slice(1)) {
             return sum;
+        }
         case 'In':
-        case 'NotIn':
+        case 'NotIn': {
             const inNode = abstractSql;
@@ -315,3 +323,4 @@ for (const arg of inNode.slice(2)) {
             return sum;
-        case 'Select':
+        }
+        case 'Select': {
             const selectNode = abstractSql;
@@ -323,2 +332,3 @@ for (const arg of selectNode[1]) {
             return sum;
+        }
         case 'Boolean':
@@ -335,3 +345,3 @@ case 'Date':
             return 0;
-        case 'Table':
+        case 'Table': {
             const tableNode = abstractSql;
@@ -344,2 +354,3 @@ if (tableNode[1] === table) {
             }
+        }
         default:
@@ -346,0 +357,0 @@ throw new Error(`unknown abstract sql type: ${abstractSql[0]}`);

package.json

 {
   "name": "@balena/abstract-sql-compiler",
-  "version": "10.0.0-build-renovate-balena-odata-parser-3-x-5d630054862b2fb289f09d7d8aeafb989815667d-1",
+  "version": "10.0.0-build-update-dependencies-28c15f305c99bd1e581a93fddfc985b82d20bdf8-1",
   "description": "A translator for abstract sql into sql.",
+  "type": "commonjs",
   "main": "out/AbstractSQLCompiler.js",
@@ -12,3 +13,3 @@ "types": "out/AbstractSQLCompiler.d.ts",
     "posttest": "npm run lint",
-    "prepare": "node -e \"try { require('husky').install() } catch (e) {if (e.code !== 'MODULE_NOT_FOUND') throw e}\" && npm run build",
+    "prepare": "node -e \"try { (await import('husky')).default() } catch (e) { if (e.code !== 'ERR_MODULE_NOT_FOUND') throw e }\" --input-type module && npm run build",
     "lint-fix": "balena-lint --fix -e js -e ts src/ test/",
@@ -20,23 +21,25 @@ "lint": "balena-lint -e js -e ts src/ test/ && tsc --noEmit && tsc --noEmit --project tsconfig.js.json"
   "dependencies": {
-    "@balena/sbvr-types": "^4.0.0",
     "lodash": "^4.17.21"
   },
+  "peerDependencies": {
+    "@balena/sbvr-types": "^7.1.0, ^8.0.0, ^9.0.2"
+  },
   "devDependencies": {
-    "@balena/lf-to-abstract-sql": "^5.0.0",
-    "@balena/lint": "^6.2.2",
-    "@balena/odata-parser": "^3.0.0",
-    "@balena/odata-to-abstract-sql": "^6.0.1",
-    "@balena/sbvr-parser": "^1.4.3",
+    "@balena/lf-to-abstract-sql": "^5.0.2",
+    "@balena/lint": "^8.2.8",
+    "@balena/odata-parser": "^3.1.0",
+    "@balena/odata-to-abstract-sql": "^6.4.0",
+    "@balena/sbvr-parser": "^1.4.6",
     "@types/chai": "^4.3.4",
-    "@types/common-tags": "^1.8.1",
-    "@types/lodash": "^4.14.192",
-    "@types/mocha": "^10.0.1",
-    "@types/node": "^18.15.11",
+    "@types/common-tags": "^1.8.4",
+    "@types/lodash": "^4.17.10",
+    "@types/mocha": "^10.0.8",
+    "@types/node": "^20.16.10",
     "chai": "^4.3.7",
     "common-tags": "^1.8.2",
-    "husky": "^8.0.3",
-    "lint-staged": "^13.2.1",
-    "mocha": "^10.2.0",
-    "ts-node": "^10.9.1",
-    "typescript": "^5.0.4"
+    "husky": "^9.1.6",
+    "lint-staged": "^15.2.10",
+    "mocha": "^10.7.3",
+    "ts-node": "^10.9.2",
+    "typescript": "^5.6.2"
   },
@@ -62,8 +65,8 @@ "lint-staged": {
   "engines": {
-    "node": ">=16.13.0",
-    "npm": ">=8.1.0"
+    "node": ">=20.14.0",
+    "npm": ">=10.7.0"
   },
   "versionist": {
-    "publishedAt": "2023-05-02T10:53:01.023Z"
+    "publishedAt": "2024-10-04T20:22:55.874Z"
   }
 }

src/AbstractSQLCompiler.ts

 export const enum Engines {
+	/* eslint-disable @typescript-eslint/no-shadow -- this is fine since we only assign plain string values to the enum items */
 	postgres = 'postgres',
 	mysql = 'mysql',
 	websql = 'websql',
+	/* eslint-enable @typescript-eslint/no-shadow */
 }
 
 import { AbstractSQLOptimiser } from './AbstractSQLOptimiser';
-import {
-	AbstractSQLRules2SQL,
-	Binding,
-	SqlResult,
-} from './AbstractSQLRules2SQL';
+import type { Binding, SqlResult } from './AbstractSQLRules2SQL';
+import { AbstractSQLRules2SQL } from './AbstractSQLRules2SQL';
 export { Binding, SqlResult } from './AbstractSQLRules2SQL';
-import sbvrTypes = require('@balena/sbvr-types');
-import * as _ from 'lodash';
-import { optimizeSchema } from './AbstractSQLSchemaOptimiser';
+import type { SbvrType } from '@balena/sbvr-types';
+import sbvrTypes from '@balena/sbvr-types';
+import _ from 'lodash';
+import { optimizeSchema, generateRuleSlug } from './AbstractSQLSchemaOptimiser';
+import type {
+	ReferencedFields,
+	RuleReferencedFields,
+	ModifiedFields,
+} from './referenced-fields';
 import {
@@ -21,5 +26,2 @@ getReferencedFields,
 	getModifiedFields,
-	ReferencedFields,
-	RuleReferencedFields,
-	ModifiedFields,
 	insertAffectedIdsBinds,
@@ -86,2 +88,4 @@ } from './referenced-fields';
 export type StartsWithNode = ['StartsWith', TextTypeNodes, TextTypeNodes];
+export type EndsWithNode = ['EndsWith', TextTypeNodes, TextTypeNodes];
+export type ContainsNode = ['Contains', TextTypeNodes, TextTypeNodes];
 export type StrictBooleanTypeNodes =
@@ -106,3 +110,5 @@ | BooleanNode
 	| OrNode
-	| StartsWithNode;
+	| StartsWithNode
+	| EndsWithNode
+	| ContainsNode;
 export type BooleanTypeNodes = StrictBooleanTypeNodes | UnknownTypeNodes;
@@ -333,3 +339,3 @@
 	'UnionQuery',
-	// tslint:disable-next-line:array-type typescript fails on a circular reference when `Array<T>` form
+	// eslint-disable-next-line @typescript-eslint/array-type -- Typescript fails on a circular reference when prettier changes this to an `Array<T>` form
 	...(UnionQueryNode | SelectQueryNode)[],
@@ -455,2 +461,11 @@ ];
 }
+export interface Index {
+	type: string;
+	fields: string[];
+	name?: string;
+	/** For rules converted to partial unique indexes this holds the actual rule expression */
+	description?: string;
+	distinctNulls?: boolean;
+	predicate?: BooleanTypeNodes;
+}
 export interface Check {
@@ -473,6 +488,3 @@ description?: string;
 	fields: AbstractSqlField[];
-	indexes: Array<{
-		type: string;
-		fields: string[];
-	}>;
+	indexes: Index[];
 	primitive: false | string;
@@ -582,4 +594,7 @@ triggers?: Trigger[];
 	// In case one of the validation types throws an error.
-	const { dataType, required } = field;
-	const validateFn = validateTypes[dataType];
+	const { dataType, required = false } = field;
+	// Without the `: SbvrType['validate']` widening TS complains that
+	// "none of those signatures are compatible with each other".
+	const validateFn: SbvrType['validate'] =
+		validateTypes[dataType as keyof typeof sbvrTypes];
 	if (validateFn != null) {
@@ -624,3 +639,4 @@ return validateFn(value, required);
 	}
-	const dbType = sbvrTypes?.[dataType]?.types?.[engine];
+	const dbType =
+		sbvrTypes?.[dataType as keyof typeof sbvrTypes]?.types?.[engine];
 	if (dbType != null) {
@@ -821,2 +837,3 @@ if (typeof dbType === 'function') {
 		const createSqlElements: string[] = [];
+		const createIndexes: string[] = [];
 
@@ -880,5 +897,32 @@ for (const field of table.fields) {
 		for (const index of table.indexes) {
-			createSqlElements.push(
-				index.type + '("' + index.fields.join('", "') + '")',
+			let nullsSql = '';
+			if (index.distinctNulls != null) {
+				nullsSql =
+					index.distinctNulls === false
+						? ` NULLS NOT DISTINCT`
+						: ` NULLS DISTINCT`;
+			}
+			// Non-partial indexes are added directly to the CREATE TABLE statement
+			if (index.predicate == null) {
+				createSqlElements.push(
+					index.type + nullsSql + '("' + index.fields.join('", "') + '")',
+				);
+				continue;
+			}
+			if (index.name == null) {
+				throw new Error('No name provided for partial index');
+			}
+			const comment = index.description
+				? `-- ${index.description.replaceAll(/\r?\n/g, '\n-- ')}\n`
+				: '';
+			const whereSql = compileRule(
+				index.predicate as AbstractSqlQuery,
+				engine,
+				true,
 			);
+			createIndexes.push(`\
+${comment}\
+CREATE ${index.type} INDEX IF NOT EXISTS "${index.name}"
+ON "${table.name}" ("${index.fields.join('", "')}")${nullsSql}
+WHERE (${whereSql});`);
 		}
@@ -940,2 +984,3 @@
 );`,
+				...createIndexes,
 				...createTriggers,
@@ -961,3 +1006,3 @@ ],
 					dependency !== resourceName &&
-					schemaDependencyMap.hasOwnProperty(dependency)
+					Object.prototype.hasOwnProperty.call(schemaDependencyMap, dependency)
 				) {
@@ -1061,2 +1106,3 @@ unsolvedDependency = true;
 		optimizeSchema,
+		generateRuleSlug,
 		compileSchema: (abstractSqlModel: AbstractSqlModel) =>
@@ -1063,0 +1109,0 @@ compileSchema(abstractSqlModel, engine, ifNotExists),

src/AbstractSQLOptimiser.ts

@@ -1,5 +0,5 @@
-import * as _ from 'lodash';
+import _ from 'lodash';
 
-import { Dictionary } from 'lodash';
-import {
+import type { Dictionary } from 'lodash';
+import type {
 	AbstractSqlQuery,
@@ -326,3 +326,3 @@ AbstractSqlType,
 const AnyNotNullValue = (args: any): boolean => {
-	return args != null && (args as any) !== 'Null' && args[0] !== 'Null';
+	return args != null && args !== 'Null' && args[0] !== 'Null';
 };
@@ -428,3 +428,3 @@
 			return arg;
-		default:
+		default: {
 			const [type, ...rest] = arg;
@@ -450,2 +450,3 @@ switch (type) {
 			}
+		}
 	}
@@ -507,2 +508,3 @@ };
 				}
+			// eslint-disable-next-line no-fallthrough
 			default:
@@ -536,2 +538,14 @@ throw new SyntaxError(
 
+const EndsWithMatcher = rewriteMatch(
+	'Endswith',
+	[TextValue, TextValue],
+	Helper<MatchFn<LikeNode>>(
+		([haystack, needle]: [TextTypeNodes, TextTypeNodes]) => [
+			'Like',
+			haystack,
+			['Concatenate', ['EmbeddedText', '%'], ['EscapeForLike', needle]],
+		],
+	),
+);
+
 const typeRules = {
@@ -597,11 +611,3 @@ UnionQuery: (args): UnionQueryNode => {
 				case 'CrossJoin':
-					tables.push(
-						typeRules[type](rest) as
-							| FromNode
-							| InnerJoinNode
-							| LeftJoinNode
-							| RightJoinNode
-							| FullJoinNode
-							| CrossJoinNode,
-					);
+					tables.push(typeRules[type](rest));
 					break;
@@ -715,5 +721,7 @@ case 'Where':
 	Cast: matchArgs<CastNode>('Cast', AnyValue, _.identity),
+	// eslint-disable-next-line id-denylist
 	Number: NumberMatch('Number'),
 	Real: NumberMatch('Real'),
 	Integer: NumberMatch('Integer'),
+	// eslint-disable-next-line id-denylist
 	Boolean: matchArgs<BooleanNode>('Boolean', _.identity),
@@ -926,3 +934,3 @@ EmbeddedText: matchArgs('EmbeddedText', _.identity),
 				switch (type) {
-					case 'When':
+					case 'When': {
 						checkArgs('When', rest, 2);
@@ -932,2 +940,3 @@ const matches = BooleanValue(getAbstractSqlQuery(rest, 0));
 						return ['When', matches, resultValue];
+					}
 					case 'Else':
@@ -1358,3 +1367,3 @@ if (index !== args.length - 1) {
 					break;
-				case 'Values':
+				case 'Values': {
 					if (values.length !== 0) {
@@ -1371,10 +1380,3 @@ throw new SyntaxError(
 							case 'UnionQuery':
-								values = [
-									[
-										'Values',
-										typeRules[valuesType](valuesRest) as
-											| SelectQueryNode
-											| UnionQueryNode,
-									],
-								];
+								values = [['Values', typeRules[valuesType](valuesRest)]];
 								break;
@@ -1386,2 +1388,3 @@ default:
 					break;
+				}
 				case 'From':
@@ -1440,3 +1443,3 @@ tables.push(typeRules[type](rest));
 					break;
-				case 'Values':
+				case 'Values': {
 					if (values.length !== 0) {
@@ -1452,2 +1455,3 @@ throw new SyntaxError(
 					break;
+				}
 				case 'From':
@@ -1562,13 +1566,4 @@ tables.push(typeRules[type](rest));
 	),
-	Endswith: rewriteMatch(
-		'Endswith',
-		[TextValue, TextValue],
-		Helper<MatchFn<LikeNode>>(
-			([haystack, needle]: [TextTypeNodes, TextTypeNodes]) => [
-				'Like',
-				haystack,
-				['Concatenate', ['EmbeddedText', '%'], ['EscapeForLike', needle]],
-			],
-		),
-	),
+	Endswith: EndsWithMatcher,
+	EndsWith: EndsWithMatcher,
 	IndexOf: rewriteMatch(
@@ -1614,3 +1609,3 @@ 'IndexOf',
 				break;
-			case 'UpsertQuery':
+			case 'UpsertQuery': {
 				checkArgs('UpsertQuery', rest, 2);
@@ -1633,2 +1628,3 @@ const insertQuery = getAbstractSqlQuery(rest, 0);
 				break;
+			}
 			default:
@@ -1635,0 +1631,0 @@ abstractSQL = AnyValue(abstractSQL) as AbstractSqlQuery;

src/AbstractSQLRules2SQL.ts

@@ -1,10 +0,9 @@
-import * as _ from 'lodash';
+import _ from 'lodash';
 
-import sbvrTypes = require('@balena/sbvr-types');
+import sbvrTypes from '@balena/sbvr-types';
 
-import { Dictionary } from 'lodash';
-import {
+import type { Dictionary } from 'lodash';
+import type {
 	AbstractSqlQuery,
 	AbstractSqlType,
-	Engines,
 	InsertQueryNode,
@@ -26,2 +25,3 @@ SelectQueryNode,
 } from './AbstractSQLCompiler';
+import { Engines } from './AbstractSQLCompiler';
 
@@ -42,3 +42,3 @@ export type Binding =
 let engine: Engines = Engines.postgres;
-let noBinds: boolean = false;
+let noBinds = false;
 
@@ -95,6 +95,7 @@ export const comparisons = {
 		case 'SelectQuery':
-		case 'UnionQuery':
+		case 'UnionQuery': {
 			const nestedIndent = NestedIndent(indent);
 			const query = typeRules[type](rest, nestedIndent);
 			return '(' + nestedIndent + query + indent + ')';
+		}
 		default:
@@ -367,3 +368,3 @@ throw new Error(`Invalid "UnknownValue" type: ${type}`);
 		switch (type) {
-			case 'On':
+			case 'On': {
 				checkArgs('On', rest, 1);
@@ -375,2 +376,3 @@ const ruleBody = BooleanValue(
 				return sqlJoinType + from + ' ON ' + ruleBody;
+			}
 			default:
@@ -561,3 +563,3 @@ throw new SyntaxError(
 			return 'DEFAULT';
-		default:
+		default: {
 			const [type, ...rest] = arg;
@@ -577,2 +579,3 @@ switch (type) {
 			}
+		}
 	}
@@ -594,7 +597,8 @@ };
 		case 'SelectQuery':
-		case 'UnionQuery':
+		case 'UnionQuery': {
 			const nestedindent = NestedIndent(indent);
 			const query = typeRules[type](rest, nestedindent);
 			return '(' + nestedindent + query + indent + ')';
-		case 'Table':
+		}
+		case 'Table': {
 			checkArgs('Table', rest, 1);
@@ -606,2 +610,3 @@ const [table] = rest;
 			return escapeField(table);
+		}
 		default:
@@ -619,6 +624,7 @@ throw new SyntaxError(`From does not support ${type}`);
 	switch (type) {
-		case 'Alias':
+		case 'Alias': {
 			checkArgs('Alias', rest, 2);
 			const field = matchFn(getAbstractSqlQuery(rest, 0), indent);
 			return `${field} AS "${rest[1]}"`;
+		}
 		default:
@@ -676,3 +682,3 @@ return matchFn(args, indent);
 		const joins: string[] = [];
-		let select: string = '';
+		let select = '';
 		const groups = {
@@ -876,4 +882,4 @@ Where: '',
 		const value = AnyValue(getAbstractSqlQuery(args, 0), indent);
-		const typeName = args[1] as string;
-		if (!sbvrTypes[typeName] || !sbvrTypes[typeName].types[engine]) {
+		const typeName = args[1] as keyof typeof sbvrTypes;
+		if (!sbvrTypes[typeName]?.types[engine]) {
 			throw new SyntaxError(`Invalid cast type: ${typeName}`);
@@ -889,2 +895,6 @@ }
 			type = 'INTEGER';
+		} else if (dbType.toUpperCase() === 'BIGSERIAL') {
+			// HACK: BIGSERIAL type in postgres is really a BIGINT with automatic sequence,
+			// so it's not actually possible to cast to BIGSERIAL, instead you have to cast to BIGINT.
+			type = 'BIGINT';
 		} else {
@@ -895,5 +905,7 @@ type = dbType;
 	},
+	// eslint-disable-next-line id-denylist
 	Number: NumberMatch('Number'),
 	Real: NumberMatch('Real'),
 	Integer: NumberMatch('Integer'),
+	// eslint-disable-next-line id-denylist
 	Boolean: (args) => {
@@ -1183,3 +1195,4 @@ checkArgs('Boolean', args, 1);
 		const value = AnyValue(getAbstractSqlQuery(args, 0), indent);
-		const innerType = sbvrTypes[args[1] as string].types[engine];
+		const innerType =
+			sbvrTypes[args[1] as keyof typeof sbvrTypes].types[engine];
 		return `ANY(CAST(${value} AS ${innerType}[]))`;
@@ -1211,3 +1224,3 @@ },
 				switch (type) {
-					case 'When':
+					case 'When': {
 						checkArgs('When', rest, 2);
@@ -1223,2 +1236,3 @@ const matches = BooleanValue(
 						return 'WHEN ' + matches + ' THEN ' + resultValue;
+					}
 					case 'Else':
@@ -1328,6 +1342,7 @@ if (index !== args.length - 1) {
 			case 'SelectQuery':
-			case 'UnionQuery':
+			case 'UnionQuery': {
 				const nestedIndent = NestedIndent(indent);
 				const query = typeRules[type](rest, nestedIndent);
 				return 'EXISTS (' + nestedIndent + query + indent + ')';
+			}
 			default:
@@ -1343,6 +1358,7 @@ return AnyValue(arg, indent) + ' IS NOT NULL';
 			case 'SelectQuery':
-			case 'UnionQuery':
+			case 'UnionQuery': {
 				const nestedIndent = NestedIndent(indent);
 				const query = typeRules[type](rest, nestedIndent);
 				return 'NOT EXISTS (' + nestedIndent + query + indent + ')';
+			}
 			default:
@@ -1403,3 +1419,3 @@ return AnyValue(arg, indent) + ' IS NULL';
 					break;
-				case 'Values':
+				case 'Values': {
 					if (values.length !== 0) {
@@ -1423,2 +1439,3 @@ throw new SyntaxError(
 					break;
+				}
 				case 'From':
@@ -1458,3 +1475,3 @@ tables.push(typeRules[type](rest, indent));
 		let values: string[] = [];
-		let where: string = '';
+		let where = '';
 		for (const arg of args) {
@@ -1475,3 +1492,3 @@ if (!isAbstractSqlQuery(arg)) {
 					break;
-				case 'Values':
+				case 'Values': {
 					if (values.length !== 0) {
@@ -1487,2 +1504,3 @@ throw new SyntaxError(
 					break;
+				}
 				case 'From':
@@ -1519,3 +1537,3 @@ tables.push(typeRules[type](rest, indent));
 		const tables: string[] = [];
-		let where: string = '';
+		let where = '';
 		for (const arg of args) {
@@ -1631,6 +1649,7 @@ if (!isAbstractSqlQuery(arg)) {
 		case 'UpdateQuery':
-		case 'DeleteQuery':
+		case 'DeleteQuery': {
 			const query = typeRules[type](rest, indent);
 			return toSqlResult(query);
-		case 'UpsertQuery':
+		}
+		case 'UpsertQuery': {
 			checkArgs('UpsertQuery', rest, 2);
@@ -1655,3 +1674,4 @@ const insertQuery = getAbstractSqlQuery(rest, 0);
 			return [insert, update] as [string, string] | [SqlResult, SqlResult];
-		default:
+		}
+		default: {
 			const value = AnyValue(abstractSQL, indent);
@@ -1665,3 +1685,4 @@ if (noBinds) {
 			};
+		}
 	}
 }

src/AbstractSQLSchemaOptimiser.ts

@@ -9,5 +9,5 @@ export const enum Engines {
 export { Binding, SqlResult } from './AbstractSQLRules2SQL';
-import sbvrTypes = require('@balena/sbvr-types');
-import * as _ from 'lodash';
-import {
+import sbvrTypes from '@balena/sbvr-types';
+import _ from 'lodash';
+import type {
 	AbstractSqlModel,
@@ -18,5 +18,4 @@ AbstractSqlQuery,
 	WhereNode,
-	isFromNode,
-	isSelectQueryNode,
 } from './AbstractSQLCompiler';
+import { isFromNode, isSelectQueryNode } from './AbstractSQLCompiler';
 
@@ -37,5 +36,16 @@ const countFroms = (n: AbstractSqlType[]) => {
 
+export const generateRuleSlug = (
+	tableName: string,
+	ruleBody: AbstractSqlType,
+) => {
+	const sha = sbvrTypes.SHA.validateSync(
+		`${tableName}$${JSON.stringify(ruleBody)}`,
+	).replace(/^\$sha256\$/, '');
+	// Trim the trigger to a max of 63 characters, reserving at least 32 characters for the hash
+	return `${tableName.slice(0, 30)}$${sha}`.slice(0, 63);
+};
+
 export const optimizeSchema = (
 	abstractSqlModel: AbstractSqlModel,
-	createCheckConstraints: boolean = true,
+	createCheckConstraints = true,
 ): AbstractSqlModel => {
@@ -116,6 +126,2 @@ abstractSqlModel.rules = abstractSqlModel.rules
 						const tableName = fromNode[1];
-						const sha = sbvrTypes.SHA.validateSync(
-							`${tableName}$${JSON.stringify(ruleBody)}`,
-						).replace(/^\$sha256\$/, '');
-
 						const table = _.find(
@@ -127,6 +133,5 @@ abstractSqlModel.tables,
 							table.checks ??= [];
-							table.checks!.push({
+							table.checks.push({
 								description: ruleSE,
-								// Trim the trigger to a max of 63 characters, reserving at least 32 characters for the hash
-								name: `${tableName.slice(0, 30)}$${sha}`.slice(0, 63),
+								name: generateRuleSlug(tableName, ruleBody),
 								abstractSql: whereNode,
@@ -133,0 +138,0 @@ });

src/referenced-fields.ts

@@ -1,3 +0,3 @@
-import * as _ from 'lodash';
-import {
+import _ from 'lodash';
+import type {
 	AbstractSqlQuery,
@@ -29,10 +29,4 @@ AbstractSqlType,
 	InNode,
-	isAliasNode,
 	IsDistinctFromNode,
-	isFromNode,
 	IsNotDistinctFromNode,
-	isSelectNode,
-	isSelectQueryNode,
-	isTableNode,
-	isWhereNode,
 	LeftJoinNode,
@@ -60,2 +54,10 @@ LessThanNode,
 } from './AbstractSQLCompiler';
+import {
+	isAliasNode,
+	isFromNode,
+	isSelectNode,
+	isSelectQueryNode,
+	isTableNode,
+	isWhereNode,
+} from './AbstractSQLCompiler';
 import { AbstractSQLOptimiser } from './AbstractSQLOptimiser';
@@ -219,3 +221,3 @@ import { isAbstractSqlQuery } from './AbstractSQLRules2SQL';
 			}
-		// Fallthrough
+		// eslint-disable-next-line no-fallthrough -- Fallthrough
 		case 'Where':
@@ -274,3 +276,3 @@ case 'And':
 		) {
-			const select = ruleBody[1].find(isSelectNode) as SelectNode;
+			const select = ruleBody[1].find(isSelectNode)!;
 			select[1] = [];
@@ -381,3 +383,3 @@ $getRuleReferencedFields(referencedFields, ruleBody[1], IsSafe.Delete);
 		case 'ToJSON':
-		case 'Where':
+		case 'Where': {
 			const unaryOperation = abstractSql as
@@ -401,5 +403,5 @@ | AliasNode<FromTypeNodes>
 			return countTableSelects(unaryOperation[1], table);
-
+		}
 		// `COUNT` is an unary function but we only support the `COUNT(*)` form
-		case 'Count':
+		case 'Count': {
 			const countNode = abstractSql as CountNode;
@@ -411,3 +413,3 @@ if (countNode[1] !== '*') {
 			return 0;
-
+		}
 		// Binary nodes
@@ -428,3 +430,3 @@ case 'AddDateDuration':
 		case 'SubtractDateDuration':
-		case 'SubtractDateNumber':
+		case 'SubtractDateNumber': {
 			const binaryOperation = abstractSql as
@@ -455,3 +457,3 @@ | AddDateDurationNode
 			);
-
+		}
 		// Binary nodes with optional `ON` second argument
@@ -461,3 +463,3 @@ case 'FullJoin':
 		case 'LeftJoin':
-		case 'RightJoin':
+		case 'RightJoin': {
 			const joinNode = abstractSql as
@@ -475,3 +477,3 @@ | FullJoinNode
 			return sum + countTableSelects(joinNode[1], table);
-
+		}
 		// n-ary nodes
@@ -482,3 +484,3 @@ case 'And':
 		case 'TextArray':
-		case 'UnionQuery':
+		case 'UnionQuery': {
 			const selectQueryNode = abstractSql as
@@ -496,6 +498,6 @@ | AndNode
 			return sum;
-
+		}
 		// n-ary nodes but the slice starts at the third argument
 		case 'In':
-		case 'NotIn':
+		case 'NotIn': {
 			const inNode = abstractSql as InNode | NotInNode;
@@ -508,5 +510,5 @@ for (const arg of inNode.slice(2)) {
 			return sum;
-
+		}
 		// n-ary-like node
-		case 'Select':
+		case 'Select': {
 			const selectNode = abstractSql as SelectNode;
@@ -519,3 +521,3 @@ for (const arg of selectNode[1]) {
 			return sum;
-
+		}
 		// Uninteresting atomic nodes
@@ -535,3 +537,3 @@ case 'Boolean':
 		// The atomic node we're looking for: a table selection
-		case 'Table':
+		case 'Table': {
 			const tableNode = abstractSql as TableNode;
@@ -544,3 +546,3 @@
 			}
-
+		}
 		default:
@@ -547,0 +549,0 @@ throw new Error(`unknown abstract sql type: ${abstractSql[0]}`);

test/abstract-sql/aggregate-json.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/aggregate.ts

 import { expect } from 'chai';
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -8,4 +8,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -12,0 +12,0 @@ binds: any[][] | TestCb,

test/abstract-sql/and-or-boolean-optimisations.ts

 import { stripIndent } from 'common-tags';
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -8,4 +8,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -12,0 +12,0 @@ binds: any[][] | TestCb,

test/abstract-sql/case-when-else.js

@@ -1,2 +0,2 @@
-const test = require('./test');
+import test from './test';
 
@@ -3,0 +3,0 @@ const buildSelect = (withElse) => [

test/abstract-sql/cast.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/coalesce.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/comparisons.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/dates.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/duration.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/empty-query-optimisations.ts

 import { stripIndent } from 'common-tags';
-import { AnyTypeNodes, SelectQueryNode } from '../../src/AbstractSQLCompiler';
+import type {
+	AnyTypeNodes,
+	SelectQueryNode,
+} from '../../src/AbstractSQLCompiler';
 
@@ -8,4 +11,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -12,0 +15,0 @@ binds: any[][] | TestCb,

test/abstract-sql/functions_wrapper.ts

 import { stripIndent } from 'common-tags';
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -8,4 +8,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as ((
+import $test from './test';
+const test = $test as ((
 	query: AnyTypeNodes,
@@ -12,0 +12,0 @@ binds: any[][] | TestCb,

test/abstract-sql/insert-query.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/is-distinct.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/joins.ts

 import { stripIndent } from 'common-tags';
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -8,4 +8,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -12,0 +12,0 @@ binds: any[][] | TestCb,

test/abstract-sql/json.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/math.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/abstract-sql/nested-in-optimisations.ts

 import { stripIndent } from 'common-tags';
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -8,4 +8,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -12,0 +12,0 @@ binds: any[][] | TestCb,

test/abstract-sql/not-not-optimisations.ts

 import { stripIndent } from 'common-tags';
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -8,4 +8,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -12,0 +12,0 @@ binds: any[][] | TestCb,

test/abstract-sql/schema-rule-to-check.ts

@@ -151,3 +151,3 @@ import * as AbstractSQLCompiler from '../..';
 ,	-- It is necessary that each test has an id that is greater than 0.
-CONSTRAINT "test$qEORqfvLM2D8/gu0ZEVfvrnt19+uBo55ipVGKTdmu0k=\" CHECK (0 < "id"
+CONSTRAINT "test$qEORqfvLM2D8/gu0ZEVfvrnt19+uBo55ipVGKTdmu0k=" CHECK (0 < "id"
 AND "id" IS NOT NULL)
@@ -154,0 +154,0 @@ );`,

test/abstract-sql/test.js

@@ -1,5 +0,5 @@
-const AbstractSQLCompiler = require('../..');
+import * as AbstractSQLCompiler from '../..';
 
-const { expect } = require('chai');
-const _ = require('lodash');
+import { expect } from 'chai';
+import _ from 'lodash';
 
@@ -82,2 +82,3 @@ const bindingsTest = function (actualBindings, expectedBindings) {
 	bound.skip = runExpectation.bind(null, describe.skip, engine);
+	// eslint-disable-next-line no-only-tests/no-only-tests -- this is a false positive
 	bound.only = runExpectation.bind(null, describe.only, engine);
@@ -87,5 +88,7 @@ return bound;
 
-module.exports = bindRunExpectation('postgres');
-module.exports.postgres = bindRunExpectation('postgres');
-module.exports.mysql = bindRunExpectation('mysql');
-module.exports.websql = bindRunExpectation('websql');
+const testFn = bindRunExpectation('postgres');
+testFn.postgres = bindRunExpectation('postgres');
+testFn.mysql = bindRunExpectation('mysql');
+testFn.websql = bindRunExpectation('websql');
+
+export default testFn;

test/abstract-sql/text.ts

@@ -1,2 +0,2 @@
-import { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
+import type { AnyTypeNodes } from '../../src/AbstractSQLCompiler';
 
@@ -7,4 +7,4 @@ type TestCb = (
 ) => void;
-// tslint:disable-next-line no-var-requires
-const test = require('./test') as (
+import $test from './test';
+const test = $test as (
 	query: AnyTypeNodes,
@@ -11,0 +11,0 @@ binds: any[][] | TestCb,

test/odata/expand.js

@@ -1,3 +0,3 @@
-const test = require('./test');
-const {
+import test from './test';
+import {
 	pilotFields,
@@ -8,4 +8,4 @@ aliasFields,
 	aliasPilotCanFlyPlaneFields,
-} = require('./fields');
-const _ = require('lodash');
+} from './fields';
+import _ from 'lodash';
 
@@ -77,3 +77,3 @@ const postgresAgg = (field) => 'COALESCE(JSON_AGG(' + field + "), '[]')";
 
-	for (let url of [
+	for (const url of [
 		'/pilot?$expand=can_fly__plane/plane',
@@ -129,3 +129,3 @@ '/pilot?$expand=can_fly__plane($expand=plane)',
 
-	for (let url of [
+	for (const url of [
 		'/pilot?$expand=can_fly__plane/plane,licence',
@@ -194,3 +194,3 @@ '/pilot?$expand=can_fly__plane($expand=plane),licence',
 
-	for (let url of [
+	for (const url of [
 		'/pilot?$select=id&$expand=can_fly__plane/plane',
@@ -242,3 +242,3 @@ '/pilot?$select=id&$expand=can_fly__plane($expand=plane)',
 
-	for (let url of [
+	for (const url of [
 		'/pilot?$select=id,licence&$expand=can_fly__plane/plane,licence',
@@ -444,3 +444,3 @@ '/pilot?$select=id,licence&$expand=can_fly__plane($expand=plane),licence',
 		WHERE "pilot"."licence" = "pilot.licence"."id"
-		LIMIT 10
+		LIMIT ?
 	) AS "pilot.licence"
@@ -453,5 +453,5 @@ ) AS "licence", ${remainingPilotFields}
 	const url = '/pilot?$expand=licence($top=10)';
-	test.postgres(url, testFunc(postgresAgg));
-	test.mysql.skip(url, testFunc(mysqlAgg));
-	test.websql.skip(url, testFunc(websqlAgg));
+	test.postgres(url, 'GET', [['Bind', 0]], testFunc(postgresAgg));
+	test.mysql.skip(url, 'GET', [['Bind', 0]], testFunc(mysqlAgg));
+	test.websql.skip(url, 'GET', [['Bind', 0]], testFunc(websqlAgg));
 })();
@@ -503,3 +503,3 @@
 		WHERE "pilot"."licence" = "pilot.licence"."id"
-		OFFSET 10
+		OFFSET ?
 	) AS "pilot.licence"
@@ -512,5 +512,5 @@ ) AS "licence", ${remainingPilotFields}
 	const url = '/pilot?$expand=licence($skip=10)';
-	test.postgres(url, testFunc(postgresAgg));
-	test.mysql.skip(url, testFunc(mysqlAgg));
-	test.websql.skip(url, testFunc(websqlAgg));
+	test.postgres(url, 'GET', [['Bind', 0]], testFunc(postgresAgg));
+	test.mysql.skip(url, 'GET', [['Bind', 0]], testFunc(mysqlAgg));
+	test.websql.skip(url, 'GET', [['Bind', 0]], testFunc(websqlAgg));
 })();
@@ -517,0 +517,0 @@

test/odata/filterby.js

@@ -6,12 +6,7 @@ /*
  */
-const { expect } = require('chai');
-const test = require('./test');
-const { clientModel } = test;
-const _ = require('lodash');
-const { odataNameToSqlName } = require('@balena/odata-to-abstract-sql');
-const {
-	pilotFields,
-	teamFields,
-	aliasPilotCanFlyPlaneFields,
-} = require('./fields');
+import { expect } from 'chai';
+import test, { clientModel } from './test';
+import _ from 'lodash';
+import { odataNameToSqlName } from '@balena/odata-to-abstract-sql';
+import { pilotFields, teamFields, aliasPilotCanFlyPlaneFields } from './fields';
 
@@ -78,3 +73,3 @@ const pilotFieldsStr = pilotFields.join(', ');
 			_.isDate(operand) ||
-			(typeof operand === 'string' && operand.charAt(0) === "'")
+			(typeof operand === 'string' && operand.startsWith("'"))
 		) {
@@ -105,3 +100,3 @@ return [['Bind', bindNo++]];
 			}
-			if (operand.charAt(0) === "'") {
+			if (operand.startsWith("'")) {
 				return '?';
@@ -113,3 +108,3 @@ }
 				let previousResource = resource;
-				for (let resourceName of fieldParts.slice(0, -1)) {
+				for (const resourceName of fieldParts.slice(0, -1)) {
 					const sqlName = odataNameToSqlName(resourceName);
@@ -341,4 +336,4 @@ const sqlNameParts = sqlName.split('-');
 			};
-		default:
-			if (methodMaps.hasOwnProperty(method)) {
+		default: {
+			if (Object.prototype.hasOwnProperty.call(methodMaps, method)) {
 				method = methodMaps[method];
@@ -351,3 +346,3 @@ }
 			}
-			var sql = method + '(' + args.map((arg) => arg.sql).join(', ') + ')';
+			const sql = method + '(' + args.map((arg) => arg.sql).join(', ') + ')';
 			return {
@@ -358,2 +353,3 @@ sql,
 			};
+		}
 	}
@@ -650,3 +646,3 @@ };
 WHERE "pilot"."id" IN ((
-	SELECT "pilot"."id"
+	SELECT "pilot"."id" AS "$modifyid"
 	FROM "pilot",
@@ -746,3 +742,3 @@ "pilot-can fly-plane" AS "pilot.pilot-can fly-plane",
 WHERE "pilot"."id" IN ((
-	SELECT "pilot"."id"
+	SELECT "pilot"."id" AS "$modifyid"
 	FROM "pilot",
@@ -824,3 +820,3 @@ "pilot-can fly-plane" AS "pilot.pilot-can fly-plane",
 AND "pilot"."id" IN ((
-	SELECT "pilot"."id"
+	SELECT "pilot"."id" AS "$modifyid"
 	FROM "pilot"
@@ -882,3 +878,3 @@ WHERE ${sql}
 AND "pilot"."id" IN ((
-	SELECT "pilot"."id"
+	SELECT "pilot"."id" AS "$modifyid"
 	FROM "pilot"
@@ -885,0 +881,0 @@ WHERE ${sql}

test/odata/orderby.js

@@ -1,3 +0,3 @@
-const test = require('./test');
-const { pilotFields } = require('./fields');
+import test from './test';
+import { pilotFields } from './fields';
 const pilotFieldsStr = pilotFields.join(', ');
@@ -4,0 +4,0 @@

test/odata/paging.js

@@ -1,6 +0,6 @@
-const test = require('./test');
-const { pilotFields } = require('./fields');
+import test from './test';
+import { pilotFields } from './fields';
 const pilotFieldsStr = pilotFields.join(', ');
 
-test('/pilot?$top=5', (result, sqlEquals) => {
+test('/pilot?$top=5', 'GET', [['Bind', 0]], (result, sqlEquals) => {
 	it('should select from pilot limited by 5', () => {
@@ -12,3 +12,3 @@ sqlEquals(
 FROM "pilot"
-LIMIT 5`,
+LIMIT ?`,
 		);
@@ -18,3 +18,3 @@ });
 
-test('/pilot?$skip=100', (result, sqlEquals) => {
+test('/pilot?$skip=100', 'GET', [['Bind', 0]], (result, sqlEquals) => {
 	it('should select from pilot offset by 100', () => {
@@ -26,3 +26,3 @@ sqlEquals(
 FROM "pilot"
-OFFSET 100`,
+OFFSET ?`,
 		);
@@ -32,13 +32,21 @@ });
 
-test('/pilot?$top=5&$skip=100', (result, sqlEquals) => {
-	it('should select from pilot limited by 5 and offset by 100', () => {
-		sqlEquals(
-			result.query,
-			`\
+test(
+	'/pilot?$top=5&$skip=100',
+	'GET',
+	[
+		['Bind', 0],
+		['Bind', 1],
+	],
+	(result, sqlEquals) => {
+		it('should select from pilot limited by 5 and offset by 100', () => {
+			sqlEquals(
+				result.query,
+				`\
 SELECT ${pilotFieldsStr}
 FROM "pilot"
-LIMIT 5
-OFFSET 100`,
-		);
-	});
-});
+LIMIT ?
+OFFSET ?`,
+			);
+		});
+	},
+);

test/odata/resource_parsing.js

@@ -1,5 +0,5 @@
-const { expect } = require('chai');
-const test = require('./test');
-const ODataParser = require('@balena/odata-parser');
-let {
+import { expect } from 'chai';
+import test from './test';
+import * as ODataParser from '@balena/odata-parser';
+import {
 	pilotFields,
@@ -11,3 +11,3 @@ teamFields,
 	aliasLicenceFields,
-} = require('./fields');
+} from './fields';
 const aliasPilotFields = aliasFields(
@@ -14,0 +14,0 @@ 'plane.pilot-can fly-plane.pilot',

test/odata/select.js

@@ -1,3 +0,3 @@
-const test = require('./test');
-const { pilotFields } = require('./fields');
+import test from './test';
+import { pilotFields } from './fields';
 const pilotFieldsStr = pilotFields.join(', ');
@@ -4,0 +4,0 @@

test/odata/stress.js

@@ -1,4 +0,4 @@
-const test = require('./test');
-const _ = require('lodash');
-const { pilotFields } = require('./fields');
+import test from './test';
+import _ from 'lodash';
+import { pilotFields } from './fields';
 const pilotFieldsStr = pilotFields.join(', ');
@@ -5,0 +5,0 @@

test/odata/test.js

@@ -1,13 +0,14 @@
-const fs = require('fs');
-const ODataParser = require('@balena/odata-parser');
-const { OData2AbstractSQL } = require('@balena/odata-to-abstract-sql');
+import * as fs from 'node:fs';
+import * as ODataParser from '@balena/odata-parser';
+import { OData2AbstractSQL } from '@balena/odata-to-abstract-sql';
 const sbvrModel = fs.readFileSync(require.resolve('../model.sbvr'), 'utf8');
 
-const AbstractSQLCompiler = require('../..');
+import * as AbstractSQLCompiler from '../..';
 
-const { expect } = require('chai');
-const _ = require('lodash');
+import { expect } from 'chai';
+import _ from 'lodash';
 
 const generateClientModel = function (input) {
-	const sbvrTypes = require('@balena/sbvr-types');
+	// eslint-disable-next-line @typescript-eslint/no-var-requires
+	const sbvrTypes = require('@balena/sbvr-types').default;
 	const typeVocab = fs.readFileSync(
@@ -18,2 +19,3 @@ require.resolve('@balena/sbvr-types/Type.sbvr'),
 
+	// eslint-disable-next-line @typescript-eslint/no-var-requires
 	const SBVRParser = require('@balena/sbvr-parser').SBVRParser.createInstance();
@@ -25,2 +27,3 @@ SBVRParser.enableReusingMemoizations(SBVRParser._sideEffectingRules);
 
+	// eslint-disable-next-line @typescript-eslint/no-var-requires
 	const LF2AbstractSQL = require('@balena/lf-to-abstract-sql');
@@ -34,3 +37,3 @@ const LF2AbstractSQLTranslator = LF2AbstractSQL.createTranslator(sbvrTypes);
 
-const clientModel = generateClientModel(sbvrModel);
+export const clientModel = generateClientModel(sbvrModel);
 const odata2AbstractSQL = new OData2AbstractSQL(clientModel);
@@ -131,2 +134,3 @@
 	bound.skip = runExpectation.bind(null, describe.skip, engine);
+	// eslint-disable-next-line no-only-tests/no-only-tests -- this is a false positive
 	bound.only = runExpectation.bind(null, describe.only, engine);
@@ -136,6 +140,7 @@ return bound;
 
-module.exports = bindRunExpectation('postgres');
-module.exports.clientModel = clientModel;
-module.exports.postgres = bindRunExpectation('postgres');
-module.exports.mysql = bindRunExpectation('mysql');
-module.exports.websql = bindRunExpectation('websql');
+const testFn = bindRunExpectation('postgres');
+testFn.postgres = bindRunExpectation('postgres');
+testFn.mysql = bindRunExpectation('mysql');
+testFn.websql = bindRunExpectation('websql');
+
+export default testFn;

test/sbvr/pilots.js

@@ -1,5 +0,7 @@
-const typeVocab = require('fs').readFileSync(
+import * as fs from 'node:fs';
+const typeVocab = fs.readFileSync(
 	require.resolve('@balena/sbvr-types/Type.sbvr'),
 );
-const test = require('./test')(typeVocab);
+import { getTestHelpers } from './test';
+const test = getTestHelpers(typeVocab);
 
@@ -6,0 +8,0 @@ const modifiedAtTrigger = (tableName) => `\

test/sbvr/reference-type.js

@@ -1,4 +0,8 @@
-const typeVocab = require('fs').readFileSync(
+import * as fs from 'node:fs';
+import { getTestHelpers } from './test';
+
+const typeVocab = fs.readFileSync(
 	require.resolve('@balena/sbvr-types/Type.sbvr'),
 );
+
 const modifiedAtTrigger = (tableName) => `\
@@ -25,6 +29,6 @@ DO
 	beforeEach(() => {
-		test = require('./test')(typeVocab);
+		test = getTestHelpers(typeVocab);
 	});
 
-	it('informative - no foreignKey for reference field', async () => {
+	it('informative - no foreignKey for reference field', () => {
 		test(
@@ -31,0 +35,0 @@ `\

test/sbvr/test.js

@@ -1,14 +0,16 @@
-const _ = require('lodash');
-const sbvrTypes = require('@balena/sbvr-types');
+import _ from 'lodash';
+import sbvrTypes from '@balena/sbvr-types';
 
-const { expect } = require('chai');
-const AbstractSQLCompiler = require('../..');
+import { expect } from 'chai';
+import * as AbstractSQLCompiler from '../..';
 
-module.exports = function (builtInVocab) {
+export function getTestHelpers(builtInVocab) {
 	if (builtInVocab == null) {
 		builtInVocab = false;
 	}
+	// eslint-disable-next-line @typescript-eslint/no-var-requires
 	const SBVRParser = require('@balena/sbvr-parser').SBVRParser.createInstance();
 	SBVRParser.enableReusingMemoizations(SBVRParser._sideEffectingRules);
 
+	// eslint-disable-next-line @typescript-eslint/no-var-requires
 	const LF2AbstractSQL = require('@balena/lf-to-abstract-sql');
@@ -77,7 +79,9 @@ const LF2AbstractSQLTranslator = LF2AbstractSQL.createTranslator(sbvrTypes);
 	ret.skip = runSchema.bind(null, it.skip);
+	// eslint-disable-next-line no-only-tests/no-only-tests -- this is a false positive
 	ret.only = runSchema.bind(null, it.only);
 	ret.rule = runRule.bind(null, it);
 	ret.rule.skip = runRule.bind(null, it.skip);
+	// eslint-disable-next-line no-only-tests/no-only-tests -- this is a false positive
 	ret.rule.only = runRule.bind(null, it.only);
 	return ret;
-};
+}

tsconfig.js.json

@@ -7,2 +7,7 @@ {
 	},
+	"include": [
+		"src/**/*",
+		"test/**/*",
+		".eslintrc.js"
+	],
 }

tsconfig.json

 {
 	"compilerOptions": {
-		"module": "commonjs",
+		"module": "Node16",
 		"strict": true,
@@ -16,3 +16,3 @@ "strictFunctionTypes": false,
 		"exactOptionalPropertyTypes": true,
-		"target": "es2021",
+		"target": "es2023",
 		"outDir": "out",
@@ -19,0 +19,0 @@ "allowJs": true

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

Filesystem access

Supply chain risk

Accesses the file system, and could potentially read sensitive data.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

997707

increased by20.65%

Number of package files

71

increased by1.43%

Lines of code

17958

increased by1.24%

Number of low supply chain risk alerts

1

decreased by-75%

Dependency changes

• + Added@balena/sbvr-types@9.1.0(transitive)

• - Removed@balena/sbvr-types@^4.0.0

• - Removed@balena/sbvr-types@4.0.2(transitive)