ts-sql-query

Documentation changes:

• Add the insert on conflict methods to the supported operation documentation page

Bug fixes:

• Fix TS4029 error when you need to emit the type definition (for use in a library) of the files that contains the database, tables and views
• Avoid database connection leaks due a forbidden concurrent usage of a pooled query runner

Changes:

• Add support to "insert on conflict do nothing" and "insert on conflict do update" on PostgreSql, Sqlite, MariaDB and MySql
• Add support to specify raw sql fragment in the order by; allowing complex order by in the select query
• Allow insert, update and delete in raw sql fragments

Documentation changes:

• Add a demo video to the documentation

Bug fixes:

• Fix infinite instantiation in newer versions of TypeScript

Changes:

• Implements forUseAsInlineAggregatedArrayValue function, that allows to transform a query in create an array value of a single column (if it is single-column query), or an object where the rows are represented as an object
• Implements aggregateAsArray aggregation function, that allows to create an value that contains, per each row, an array of a single column, or an array with several columns represented as an object
• Add support to the uuid type
• Add support to orderByFromStringIfValue, limitIfValue and offsetIfValue
• Add support to subqueries that contains with clause with external/contextual dependencies
• Add support to compose over optional properties
• Add support to withOptionalMany composing rule that allows to use undefined instead of an empty array when no value
• Detect invalid queries in SqlServer, Oracle and MariaDB when an outer reference is used to create a query that is not supported by the database because no outer references are allowed in inner with, or, in MariaDB, no outer references are allowed in inner from
• Combine multiple concat expressions in a single concat function call in MySql and MariaDB

Documentation changes:

• Add a note in the mergeType function documentation warning about the reader evaluate the preferred alternatives first

Bug fixes:

• Fix invalid query when a table alias is specified in Oracle
• Fix invalid recursive query in Sql Server
• Fix invalid recursive query in Oracle
• Fix invalid query when contains method of a string value source is called in MySql/MariaDB
• Fix substrToEnd, substringToEnd, substr and substring: now the index is according to JavaScript definition (the count start in 0) and the parameters have the correct type
• Fix invalid type when a mathematical function is used and the provided value is not the same type that the column

Changes:

• Manage complex projections in compound operations (union, intercept, etc.)
• Ensure the dynamic conditions cannot create conditions when null/undefined values are provided to functions that doesn't expect it
• Detect when null/undefined values are provided to an operation with a value coming from a left join where a not null/undefined value must be provided
• Deprecate all value source methods overload that can produce unexpected falsy/null values because the provided value in JavaScript is null or undefined. Now all value source methods doesn't admit null or undefined values (except the *IfValue, is, isNot methods). In the odd case you need to use a nullable value from JavaScript, and you want to maintain the falsy/null output use an optional constant with the JavaScript value
• Add the methods trueWhenNoValue and falseWhenNoValue to allows specify a boolean value when the *IfValue function produces no value. This can help to manage optional values coming from JavaScript in complex logic without need to use the deprecated methods that can produce unexpected falsy/null values
• Allows to negate the result of a *IfValue function
• Improve boolean expression reduction when the negate method is used
• Detect invalid columns to be returned in a select (non-string key)

Preview of upcoming changes:

• Implements aggregateAsArray aggregation function, that allows to create an value that contains, per each row, an array of a single column, or an array with several columns represented as an object
• Add support to subqueries that contains with clause with external/contextual dependencies

Documentation changes:

• Clean up sync helper function to handle synchronous promises in BetterSqlite3 with a stricter typing and better readability

Bug fixes:

• Ensure any boolean operation apply over a boolean created using dynamicBooleanExpressionUsing is asignable to the initial type
• Fix invalid result type of calling asOptional or asRequiredInOptionalObject when the type is different to int
• Fix BetterSqlite3 implementation that returns a real promise instead of a synchronous promise when there is no columns to set

Changes:

• Add support to complex projections, that allows to create inner objects in the result of a query
• Detect invalid query when a table in the from of an update appears in the returning clause in sqlite. Now it verify the restriction 7 of the returning clause in Sqlite
• Add support for Prisma 3
• Add support for the interactive transactions in Prisma

Documentation changes:

• Add test strategy information

Bug fixes:

• Fix MariaDB/MySql stringConcat when an empty separator is used

Changes:

• Deprecate replace method in favour of replaceAll in the string value source to align with JavaScript
• Add the substr and substrToEnd to the string value source to align with JavaScript and respect the real available implementation in the databases
• Add support to create complex dynamic boolean expression using the dynamicBooleanExpresionUsing method in the connection object. It allows to create programmatically dynamically complex boolean expressions instead of declarative dynamically conditions using the IfValue functions. It is recommend to use the IfValue functions when it is possible
• Add mergeType utility function to deal with advanced dynamic queries when a variable ended with type a union of several types of value source. This function allows to resolve the union type in a single value source type

Documentation changes:

• Combine all topics related to dynamic queries in a single page to avoid confusion
• Improve documentation style

Bug fixes:

• Fix broken substring implementation in the string value source

Changes:

• Added a new general query runner: InterceptorQueryRunner

Bug fixes:

• Fix error lost that was throw by a logger in a LogginQueryRunner

Changes:

• Add support to scalar queries, that is an inline select query as value for another query
• Add support to insert returning on databases that support it (PostgreSql, SqlServer, Oracle, modern Sqlite)
• Add support to update returning on databases that support it (PostgreSql, SqlServer, Oracle, modern Sqlite)
• Add support to update returning old values on databases that support it (SqlServer)
• Add support to update returning old values on databases where it can be emulated in a single query (PostgreSql)
• Add support to delete returning on databases that support it (PostgreSql, SqlServer, Oracle, modern Sqlite)
• Add support to use more tables or views in an update (from clause)
• Add support to use more tables or views in a delete (using clause)
• Add support to use more tables or views in an update returning old values on databases that support it (SqlServer)
• Add support to use more tables or views in an update returning old values on databases where it can be emulated in a single query (PostgreSql)
• Improve error detection to identify misuse of values that have different columns types with same TypeScript type (like date and time)
• Improve min and max limit verification on insert

Bug fixes:

• Fix selectOneColum result type on complex objects (like Date)

Changes:

• Add support to numeric date/time in Sqlite that is expressed as bigint in JavaScript by the database connector (By example, using defaultSafeIntegers option in BetterSqlite3)

Bug fixes:

• Fix typo in Sqlite treatUnexpectedStringDateTimeAsUTC connection option (wrongly named: treatUxepectedStringDateTimeAsUTC)
• Fix typo in Sqlite unexpectedUnixDateTimeAreMilliseconds connection option (wrongly named: uxepectedUnixDateTimeAreMilliseconds)

Changes:

• Manage the errors coming from the deferred execution logic till the end of a transaction, after commit or rollback. Now all deferred logic will be executed even if one of them throw an error. All errors thrown by the deferred logic will be collected and combined in one single error that will be thrown after the commit or rollback is executed

Bug fixes:

• Fix invalid high level transaction management when the commit fails. The transaction was not rolled back when the commit fails
• Fix connection released too early due when the commit fails in a pooled query runner
• Don't fire the deferred functions when rollback when the commit fails; when this happens the transaction is still ongoing

Changes:

• Implements "Unix time milliseconds as integer" date/time strategy for sqlite that allows to store dates & times in UNIX time as milliseconds
• MockQueryRunner create the output param for oracle in the same way this database expect it
• Add support to differing execution logic using async functions till the end of a transaction, after commit or rollback

New examples:

• Add a running mocked version of the examples in the documentation per each supported database

Internal changes:

• Add code coverage report

Bug fixes:

• Fix differing logic execution till the end of transaction in case of multiple nested transaction with multiple differed logic but not in the middle of the nesting transaction

Changes:

• Add support to differing execution logic till the end of a transaction, after commit or rollback

Internal changes:

• Introduce ts-node to run the examples

Bug fixes:

• Fix sqlite compatibility mode by default (regression introduced in the previous release)
• Fix oracle example due oracle instant client not loading and throwing error when the oracle driver is initialized

Changes:

• Allows to use in split/compose previously created property using split/compose

• Add support to Date and Time management in sqlite using different strategies to represent the value (sqlite doesn't have dedicate types to represent dates and time). The implemented strategies are aligned with the date time support in sqlite allowing to store the information as text (in the local timezone or UTC), as integer (in unix time seconds) or as a real value (in Julian days)

• Align method names with convention, where ts-sql-query tries to use well known method names, giving preferences to already existing names in JavaScript, o well known function names in SQL, avoiding abbreviations. Methods with new names (Previous names are still available as deprecated methods):

  Previous name	New name
  smaller	lessThan
  smallAs	lessOrEquals
  larger	greaterThan
  largeAs	greaterOrEquals
  mod	modulo
  lower	toLowerCase
  upper	toUpperCase
  ltrim	trimLeft
  rtrim	trimRight

• Change some internal type names to improve the readability of the type name in the IDE and in error messages

• Implement the compatibility mode on sqlite (enabled by default). When is disabled allows to take advantages of the newer syntax in sqlite. Right now only prisma and better sqlite includes an sqlite compatible

• Now is possible create an insert from a select o with multiples values that returns the last inserted id if a compatible sqlite with the returning clause is used

• Now is possible create an insert from a select that returns the last inserted id if a compatible sqlite with the returning clause is used

• Ensure the MockQueryRunner returns a number when the mock function return no value when an insert, update or delete is executed

• Detect invalid results from the mock function returned to the MockQueryRunner

• Add support to mock the call to the method isTransactionActive

Documentation changes:

• Add example of MockQueryRunner usage to the documentation
• Document how to run the examples

Internal changes:

• Changes to make happy TypeScript 4.4 and avoid error messages
• Set up GitHub CI

Bug fixes:

• Fix type returned by a table or view customization when the original table or view has alias

Changes:

• Add utility functions that allow to create a prefix map for a guided split taking as reference another object with columns, marking as required the same keys that have a required column in the reference object

Bug fixes:

• Fix invisible characters included in the prefixed property names in the prefix utility functions

Changes:

• Add more options to organize the select clauses, making in this way easier to create functions that return queries partially constructed. The where clause can be postponed until the end of the query, before the query execution
• Add support to queries that use orderBy, limit, offset inside of a compound operator (like union, intersect). With this change now it is possible to use a limit in the inner query, not only in the outer one with the compound operator
• Implement insert default values query customization on MySql/MariaDB
• Increase the flexibility of a select from no table, allowing all the clauses supported by a select (outside the from definition)
• Add utility function that allows extracting all columns from an object (like table or view) that enables to write a select all columns
• Add utility functions that allow to deal with situations when a prefixed copy of a list of columns is required to use multiple columns with the same name in a select; complementary functions to help split back in a select the prefixed columns are also included

Bug fixes:

• Fix invalid order by of a compound query in Oracle. When a compound operator (union, intersect, ...) is used, Oracle requires to use the positional notation instead of the name of the columns
• Fix invalid subquery in SqlServer that contains an order by. In SqlServer subqueries with an order by must always include an offset

Changes:

• Add support to undefined elements in the and/or array of a dynamic condition

Bug fixes:

• Fix undefined not treated as absence of value in IfValue conditions

Documentation changes:

• Fix missing parent definition in the "Splitting the result of a left join query" example of the documentation

Bug fixes:

• Fix error when composition or splitting are use in a select with executeSelectNoneOrOne and the result is null

Changes:

• Implement guided splitting to help handle the splitting situation originated by a left join when the optionality of the moved properties are not correct due to known null rules that are not able to be extracted by ts-sql-query from the query

Documentation changes:

• Documented error for method executeSelectNoneOrOne

Bug fixes:

• Fix constraint violation when a left join return null on a column that originally was marked as required

Changes:

• Add utilities methods to insert and update operations that helps to deal with columns that were prepared to set with no value (null, undefined, empty string, empty array): setIfHasValue, setIfHasValueIfValue, setIfHasNoValue, setIfHasNoValueIfValue, ignoreIfHasValue, ignoreIfHasNoValue, ignoreAnySetWithNoValue

Bug fixes:

• Fix wrong result of isTransactionActive in connections that potentially can nest transaction levels

Documentation changes:

• Make more clear and visible the warning about sharing the connection between HTTP requests.

Bug fixes:

• Fix invalid query when an insert or update contains additional properties not precent in the table (that must be ignored)