ts-sql-query

Changes:

• Implement isTransactionActive method at the connection object that allows to know if there is an active open transaction in ts-sql-query
• Allows to use objects with the values in an insert or update that contains additional properties not precent in the table that will be ignored. This change makes the behaviour coherent with TypeScript compiler.

Bug fixes:

• Fix transaction management when a ts-sql-connection connection from a pool is reused, started a transaction, but no query is executed.
• Fix select result on non-strict mode, making the best approximation to have an usable result (but loosing the optional property information)

Changes:

• Allows to use complex names in different places like the column alias (name of the property in the result object of a select)
• Allow a dynamic select picking the columns
• Handle splitting with select picking columns
• The split method automatically determines if the created property is required or optional
• Added splitRequired splitting method
• Add support to optional joins in a select picking columns
• Add support to table "from" customization, allowing to include raw sql to use features not supported yet by ts-sql-query
• Add support to select query customizations
• Add support to update query customizations
• Add support to delete query customizations
• Add support to insert query customizations

Documentation changes:

• Document about how to deal with splitting result and dynamic queries
• Add column types section in the documentation

Bug fixes:

• Ensure insert multiple can generate the with clause
• Add support to with clause on insert queries on databases that doesn't support a global with on insert (oracle, mysql, mariadb)
• Fix invalid insert default values query on oracle

Changes:

• Add support to custom array types
• Add support to globally encrypted id
• Big refactor to simplify the query runners implementation
• Dropped support to very old better-sqlite3 versions (6 or before)
• Allow to use returning clause on sqlite and mariadb in a sql text query executed directly with the query runner

Documentation changes:

• Implements new documentation website using mkdocs and readthedocs.io, available at: https://ts-sql-query.readthedocs.io/
• Add transaction documentation
• Document security constraint regarding update and delete with no where
• Add select with left join example to the documentation

Distribution changes:

• Source maps are not included any more

Bug fixes:

• Fix insert from select returning last inserted id
• Fix invalid in queries when the in function didn't receives an array of values

Changes:

• Add support to create dynamic conditions where the criteria is defined at runtime. This allows to have a select with a where provided by an external system.
• Implements compound operator (union, intersect, except) on select expressions.
• Allows executeSelectPage on select with group by
• Allows insert from select returning last inserted id in PostgreSql and Sql Server
• Extends the possibility of a select query to change the shape of the projected object allowing move some property to an internal object (split) or combine the result with a second query string the value as a property of the first one (compose)
• Add support to recursive select queries

Bug fixes:

• Fix startsWith and endsWith misspelling

Changes:

• Add the transaction method to the connection to make easier deal with transactions at high level
• Add Prisma support

Changes:

• Implements LoggingQueryRunner

Documentation changes:

• README improvements
• Include optionalConst connection method in the documentation

Changes:

• Implements SQL with clause that allows using a select as a view in another select query.
• Rework insensitive comparison to allow use collations instead of the lower function; allowing in that way make comparison case insensitive and accent insensitive.
• Implements insensitive order by extension.
• Rework boolean management to support databases that don't have boolean data type (Sql Server and Oracle).
• Add support to custom boolean columns.
• Add support to execute better-sqlite3 queries synchronously.
• Add support to computed columns on tables.
• Add ID encrypter utility.

Documentation changes:

• Add documentation about how encrypt the IDs.
• Add warning to the readme about sharing the connection between HTTP requests.
• Add warning about non-public files.
• Add waring about table and views constructor arguments

New examples:

• Add Sqlite example using better-sqlite3 for the connection and synchronous queries.
• Add PostgreSql example using pg for the connection and encrypted primary/foreign keys.

Bug fixes:

• Fix mismatching column name when an uppercase character is used as column's alias on PostgreSQL. PostgreSQL lowercase the column's alias when it is not escaped; in consequence, an error was thrown because the column was not found.
• Fix some 'not' ignored during text comparison: notContainsInsensitive (on MySQL, MariaDB, Oracle, PostgreSQL, Sqlite, SqlServer), notEndWith (on Oracle, Sqlite, SqlServer)
• Fix some posible invalid order by in MySql, MariaDB, SqlServer and Sqlite.
• Fix invalid queries involving boolean operations in Sql Server and Oracle.
• Fix missing bigint cast for a value coming from the database when it is a number.

First stable release!

See 1.0.0-beta.1 release notes

Bug fixes:

• setIfValue, setIfSetIfValue, setIfNotSetIfValue when insert or update now have the same behaviour that any *IfValue function, respecting the configuration about treating an empty string as null value

Changes:

• Implements reusable fragments as functions using the buildFragmentWithArgs function with the arg and valueArg functions (all defined in the connection)
• Implements reusable fragments as functions that allow creating *IfValue functions using the buildFragmentWithArgsIfValue function with the arg and valueArg functions (all defined in the connection)
• Add support to the newest Better Sqlite 3 returning bingint
• Update all dependencies, and apply all required changes
• Implements the method execute in the query runners to allows have direct access to the database using the raw objects used to establish the connection
• Refactor how const values are handled. Now value source included two new methods:
  • isConstValue(): boolean that allows verify if it contains a const value
  • getConstValue(): TYPE that allows getting the value of a const value source (throw an error if it is not a const value source)
• Update the readme to include explanations about dynamic queries
• Add support to bigint column type
• Add examples section to the readme

Braking changes:

• Don't inline true or false values when they are defined with the const function. If you want a true or false value inlined use the true() and false() methods defined in the connection
• Rename QueryRunner.getNativeConnection as getNativeRunner to avoid confusion because this method doesn't return the connection in all the implementation (could be the pool)
• Big refactor to reduce the pressure on TypeScript type validations. Breaking changes:
  • Connections classes now only receive one generic argument with a unique name.
    Before: DBConection extends PostgreSqlConnection<DBConection, 'DBConnection'> { }
    After: DBConection extends PostgreSqlConnection<'DBConnection'> { }
  • Tables and views now receive a second generic argument with a unique name.
    Before: class TCompany extends Table<DBConection> { ... }
    After: class TCompany extends Table<DBConection, 'TCompany'> { ... }
    Before: class VCustomerAndCompany extends View<DBConection> { ... }
    After: class VCustomerAndCompany extends View<DBConection, 'VCustomerAndCompany'> { ... }
• The value argument and the return type in the type adapters (including the default implementation in the connection) have now type unknown
• Trak if a value source is optional and validates if the result of executing a query return a value when it is expected. Braking changes:
  • A const with an optional value must be created using the new optionalConst function in the connection, previously was used the const function in the connection
  • Theis function that allows comparing two values now returns a not optional boolean, previously it returned an optional value
• Dropped the method NumberValueSource.asStringNumber, use instead the new methods:
  • NumberValueSource.asInt(): number
  • NumberValueSource.asDouble(): number
  • NumberValueSource.asStringInt(): number|string
  • NumberValueSource.asStringDouble(): number|string
  • StringNumberValueSource.asStringInt(): number|string
  • StringNumberValueSource.asStringDouble(): number|string

Internal changes:

• Big refactor without change the public interface:
  • Use symbols for type marks instead of protected fields
  • Use interfaces instead of abstract classes (allowed by the previous change)
  • Use import type when it is possible
  • Join all databases files in one file
  • Drop alternative implementations code not in use

Bug fixes:

• Fix invalid query when no value is provided to the function concatIfValue
• Fix invalid usage of *IfValue functions result, now typescript report an error when it happens
• Handle when the update has nothing to set, in that case, no update will be performed, and it returns 0 rows updated

Changes:

• Implements LoopBack support for sqlite3, postgresql, mysql/mariadb, sql server and oracle
• Attach error information to beginTransaction, commit and rollback methods
• Add an option to run all examples
• Use the param placeholder defined in the query runner instead of redefined it in the sql builders
• Always use positional parameters in sqlite
• Refactor how is ensured that you are using a compatible query runner in a connection

Changes:

• Implements insert from a select
• Implements custom comparable types
• Custom column type now includes in and not in operations

Changes:

• Implements executeDatabaseSchemaModification in the query runner for all supported databases
• Make params optional in the query runners
• Add fake order by to allow have limit without order by in Sql Server like in other databases
• Change the way how a function is executed in Oracle. Now a select is executed
• Add warning of AnyDB for Sqlite is not working properly due a bug of any-db-sqlite3
  Add warning of AnyDB for Sql Server is not working properly due a bug of any-db-mssql
• Add warning: tedious-connection-pool is not working due a bug of tedious-connection-pool
• Update readme

New examples:

• Add PostgreSql example using pg for the connection
• Add SqlServer example using tedious for the connection
• Add SqlServer example using mssql with tedious for the connection
• Add PostgreSql example using AnyDB with pg for the connection
• Add SqlServer example using AnyDB (any-db-mssql) with tedious for the connection
• Add Oracle example using oracledb for the connection
• Add MySql example using mysql for the connection
• Add MySql example using mysql2 for the connection
• Add MariaDB example using mariadb for the connection
• Add MySql example using AnyDB with mysql for the connection
• Add Sqlite example using sqlite for the connection
• Add Sqlite example using sqlite3 for the connection
• Add Sqlite example using AnyDB with sqlite3 for the connection
• Add Sqlite example using better-sqlite3 for the connection

Bug fixes:

• Add missing executeInsertReturningMultipleLastInsertedId implementation
• Fix missing result when a executeSelectOneRow is executed with PgQueryRunner
• Fix select current value of a sequence in Sql Server
• Fix limit in Sql Server when offset is not provided
• Fix procedure and function call in Sql Server
• Fix missing result when an executeSelectOneRow is executed with AnyDBQueryRunner
• Fix executeInsertReturningLastInsertedId and executeInsertReturningMultipleLastInsertedId implementations for AnyDB
• Fix column alias in Oracle, the alias must be quoted in order to preserve the case. Unquoted alias are returned as uppercase.
• Fix missing result when a executeSelectOneRow is executed in Oracle
• Fix wrong result order when a insert multiple returning last inserted id is executed in Oracle
• Fix unhandled safe integer object used by better-sqlite3 when an executeFunction or executeSelectOneColumnOneRow query is executed

Changes:

• Implements insert multiple values and allows to return the last inserted id for each one (this last one only for PostgreSql, SqlServer and Oracle)
• Add table of content to the readme

Bug fixes:

• Fix get output values in oracle
• Fix source stack (where the query was executed) added twice to the error stack
• Fix readme

Changes:

• Add the possibility to disable the treatment of an empty string as null
• Escape reserved words when it is used as identifier
• When a select query references to two o more tables or view, the table or view name is used as the prefix of the column when no alias is provided. It avoid the query ambiguity when two columns from different sources have the same name (used in the query or not)

Bug fixes:

• Fix double cast when the value is coming from the database
• Allow NaN, Infinity and -Infinity in stringDouble when it is represented as string
• Fix localTime type name
• Fix localDate type name
• Fix int cast when the value is coming from the database
• Fix invalid sql in SqlServer
• Fix type information used by the query runners in sql server

Changes:

• Allows to execute a selectOne over an optional column
• Don't allow to call "returningLastInsertedId" when an insert query is constructed for a table without autogenerated primary key

Bug fixes:

• Fix MySqlPoolQueryRunner name
• Make PoolQueryRunner not abstract
• Fix invalid result on MySql when a query that must returns one row is executed

Changes:

• Implements more query runners that handles the connection pool directly
• Implements insert default values with a primary key generated by a sequence

Bug fixes:

• Fix wrong inference type caused because typescript drops the type of private fields
• Fix "Type instantiation is excessively deep and possibly infinite.ts(2589)" when the connection is TypeSafe

Initial release