ts-sql-query

Changes:

• Add support to aggregateAsArrayDistinct and aggregateAsArrayOfOneColumnDistinct to allow aggregate as array distinct values.
• LoggingQueryRunner: Use performance.now() in non-Node environments.

Bug fixes:

• Fix insert multiple no-inserting records when setForAllIfHasNoValue is called and the records to insert contain a single record

Changes:

• Add support to custom reusable SQL fragments that the returning value can be optional or required depending on the provided arguments.

Removals:

• Remove deprecated mergeType additional utility type. Use connection.dynamicBooleanExpressionUsing instead.
• Remove deprecated composing and splitting results functionality long warned to be removed in ts-sql-query. Use complex projections or aggregate as an object array instead.

Changes:

• Deprecate composing and splitting results functionality long warned to be removed in ts-sql-query. Use complex projections or aggregate as an object array instead.
• Deprecate mergeType additional utility type. Use Use connection.dynamicBooleanExpressionUsing instead.

Removals:

• Remove deprecated any-db support and query runner.

• Remove deprecated LoopBack support and query runner.

• Remove deprecated msnodesqlv8 support and query runner.

• Remove deprecated tedious support and query runner. Tedious still available using mssql.

• Remove deprecated Prisma's short-running transactions support. Prisma's Long-running transactions continue supported.

• Remove deprecated connections with extended types: TypeSafeMariaDBConnection, TypeSafeMySqlConnection, TypeSafeNoopDBConnection, TypeSafeOracleConnection, TypeSafePostgreSqlConnection, TypeSafeSqliteConnection, TypeSafeSqlServerConnection.

• Remove ts-extended-types dependency.

• Remove deprecated stringInt and stringDouble column types in favour of customInt and customDouble.

• Remove long deprecated functions:

  Removed deprecated nam	Current name
  smaller	lessThan
  smallAs	lessOrEquals
  larger	greaterThan
  largeAs	greaterOrEquals
  mod	modulo
  lower	toLowerCase
  upper	toUpperCase
  ltrim	trimLeft
  rtrim	trimRight
  replace	replaceAll
  replaceIfValue	replaceAllIfValue

• Remove long deprecated overload of functions in columns that allowed to send to the database null values in TypeScript when the type were optional.

Changes:

• Allow to use notEqualsInsensitive in dynamic filters previously not included in the white list of allowed functions.
• Deprecate Tedious and MsNode query runners in favour of mssql.
• Deprecate Prisma's short-running transactions support.
• Deprecate stringInt and stringDouble in favour of customInt and customDouble.
• Deprecated database connections with extended types: TypeSafeMariaDBConnection, TypeSafeMySqlConnection, TypeSafeOracleConnection, TypeSafePostgreSqlConnection, TypeSafeSqliteConnection, TypeSafeSqlServerConnection.

Changes:

• Add support to more custom types: customInt, customDouble, customUuid, customLocalDate, customLocalTime, customLocalDateTime.
• Add the possibility to get some metadata regarding the query execution in a query runner: the query execution stack, information about the function that requests to execute the query, knowing if the query is a select count in a select page, allowing to specify an execution name, allow to specify additional metadata for the execution.

Documentation changes:

• Improve documentation, making the simplified type definition more explicit.

Changes:

• Add support to complex projections in compound select (union, intersect, etc.)

Bug fixes:

• Fix missing with in compound select queries (union, intersect, etc.)

Changes:

• Allow to defer the execution of a logic till just before the transaction's commit.
• Add support to execute the queries using an @sqlite.org/sqlite-wasm Object Oriented API 1 in Web Assembly.

Bug fixes:

• Fix inIfValue and notInIfValue forcing include the optional join when it is not required.
• Fix subquery used as boolean value in a sql fragment when it is not on SqlServer or Oracle databases.

Changes:

• Add support to projecting optional values in an object as nullable in the output of select, insert, update, delete and aggregate array. This make the optional property required as nullable in the projected value.

Documentation changes:

• Reorganize documentation to put select related documentation next to each other.
• Updating mkdocs, code highlight.
• Including Google search functionality complementary to the build-in search.
• Change log excluded from the search output.
• Improve build-in search.
• Move "Composing recursive query as an array of objects in two requests" documentation to the "Composing and splitting results (legacy)" page.

Bug fixes:

• Fix the error indicating there is no transaction active when executeConnectionConfiguration is executed before any other query immediately after opening a transaction.

Changes:

• Deprecate AnyDB, LoopBack and tedious-connection-pool query runners due their respective projects are dead.
• Implement executeConnectionConfiguration in the query runner, allowing you to execute raw queries that modify the connection configuration.
• MariaDB and MySql don't support nested transactions, but instead of throwing an error, it silently finishes the previous one; when this circumstance is detected, an error with be thrown to avoid dangerous situations.
• Add support to beforeQuery custom SQL fragment when queries are customized.

Documentation changes:

• Update tedious query runner documentation to don't use tedious-connection-pool and add a note requesting information to the users to explain how to use it with a proper pool.
• Mark compose and split functionality as legacy with the intention to be deprecated in the future. Documentation of this functionality moved to a single place.

Changes:

• Allow extend the rules in a dynamic condition to provide own rules not included by ts-sql-query
• Ensure tall types returned by dynamicCondition are readable

Bug fixes:

• Fix missing rules for comparison in the type created using DynamicCondition when the database types are used
• Fix invalid cast using fromRef not reported by the typescript (now you will get a compilation error)
• Fix dynamicPickPaths not picking the inner properties
• Fix left join property marked as optional when it is used in a complex projection and with dynamic picking columns

Changes:

• Add support dynamicPickPaths to work with a list of fields to pick, and implement expandTypeFromDynamicPickPaths utility function to rectify the query output when the list of fields to pick is a generic type (Previously experimental)
• Implement insert/update shape that allows controlling the structure of the object to use to set the value (Previously experimental in update)
• Add support to update multiple tables in a single update in MariaDB and MySql (Previously experimental)
• Add support to Oracle recursive queries using connect by syntax
• Extend utility types and functions to filter by the id columns
• Add PickValuesPath utility function that allows getting the result of a select query given the fields to pick picked paths
• Extend the DynamicCondition, allowing to use fields of the dynamic condition as an argument
• Add PickValuesPathWitAllProperties utility type that allows getting the type of each element returned by a select picking columns
• Extend SelectedValues and SelectedRow, allowing to use of complex projections
• Implement selectCountAll() as a shortcut to selectOneColumn(connection.countAll()) that doesn't return an optional value when the query is used as an inline value (removing in this way the current limitation)
• Add support to order by a column not returned by the select (removing in this way the current limitation)
• Allow ignoreIfSet over a required property in an insert
• Add keepOnly method that allows filtering the columns to be set in an insert or update
• Allow the dynamic set to receive as an argument the initial values to set
• Add support to dynamic set on an insert with multiple rows (removing in this way the current limitation)
• Add support to throw an error if some columns are set or have value in an insert or update. New methods in insert and update: disallowIfSet, disallowIfNotSet, disallowIfValue, disallowIfNotValue, disallowAnyOtherSet
• Add support to manipulate the data to insert or update conditionally
• Allow the insert do dynamicSet or dynamicValues using an object where a required property is optional

Documentation changes:

• Document how to define select picking functions in base on the business types or in base on the database types
• Add documentation regarding data manipulation in insert/update. Before, it was not clear this functionality existed because it was only mentioned in the supported operations

Bug fixes:

• Fix expandTypeFromDynamicPickPaths (Previously experimental) to work with all kinds of output produced when a query is executed
• Make dynamic pick columns work with complex projections in case a property with a group with several columns is not picked

Bug fixes:

• Fix infinite loop by discovering the optional joins used in the query
• Fix infinite recursive function call in ChainedQueryRunner for the execute method

Internal changes:

• Add support to run all the tests natively in Apple M1 except for loopback and oracle
• Add support to run oracle tests in an x86 emulated docker and using node running under rosetta

Bug fixes:

• Fix valueWhenNull in SqlServer
• Major rework on custom booleans to fix several bugs

Changes:

• Add utility types UpdatableOnInsertConflictRow and UpdatableOnInsertConflictValues to represent updatable values in case of conflict on insert

Experimental changes:

• Implement dynamicPickPaths to work with a list of fields to pick, and implement expandTypeFromDynamicPickPaths utility function to rectify the query output when the list of fields to pick is a generic type
• Implement update's shapeAs that allow controlling the structure of the object to use to set the value
• Implement update multiple tables in a single update in MariaDB and MySql

Bug fixes:

• Fix boolean value binding for Oracle
• Fix worng count in a select page query when the distinct modifier is used

Bug fixes:

• Fix typo in generated sql when the sqrt function is used
• Fix internal error when an empty array is provided in a in or notIn methods in Sqlite, MariaDB and MySql

Documentation changes:

• Fix typo (confict → conflict)
• Mention term "upsert" for easier discoverability

Bug fixes:

• Fix wrong count on executeSelectPage when a groupBy is used