atdatabases

Breaking Changes

• Rewritten to share most of the implementation with Postgres (#148)

  This means the types now match Postgres for Queryable/ConnectionPool/Connection/Transaction. It also introduces some new timeouts.

• Upgrade the underlying driver (i.e. mysql2) to support MySQL version 8 (#145)

  This shouldn't cause any breaking changes for end users, but it is a substantial change, so I have marked it as a breaking change to be on the safe side.

New Features

• Support nested transactions: (#148)

  const db = createConnectionPool();
  
  await db.tx(async db => {
    // I'm in a transaction
    await db.tx(async db => {
      // I'm in a nested transaction, implemented using "savepoints"
    });
  });
  

• New option: tinyIntMode to allow you to control whether TINYINT should be treated as a boolean or a number (#148)

  MySQL doesn't have a true boolean type, so when you create a column of type BOOLEAN or BOOL you actually get a column of type TINYINT(1). It is possible to use this to store other numbers (in the range 0-255).

  If you use 'boolean' mode, all values other than "0" are interpreted as true, and "0" is interpreted as false. This matches the behaviour of MySQL queries that use value IS TRUE or value IS NOT TRUE.

  Usage:

  // to treat TINYINT as a number (this is currently the default):
  const db = createConnectionPool({
    tinyIntMode: 'number'
  });
  
  // to treat TINYINT as boolean:
  const db = createConnectionPool({
    tinyIntMode: 'boolean'
  });
  

  See https://www.mysqltutorial.org/mysql-boolean/ for details.

• New option: bigIntMode to allow you to control whether BIGINT (aka LONGLONG) should be parsed as a number, string or BigInt (#148)

  If you choose number you may get inexact values for numbers greater than Number.MAX_SAFE_INTEGER. Currently defaults to 'number', but this may change in a future version

  Usage:

  // to treat BIGINT as a number (this is currently the default):
  const db = createConnectionPool({
    bigIntMode: 'number'
  });
  
  // to treat BIGINT as string:
  const db = createConnectionPool({
    bigIntMode: 'string'
  });
  
  // to treat BIGINT as BigInt (N.B. requires a recent version of node.js):
  const db = createConnectionPool({
    bigIntMode: 'bigint'
  });
  

• New option: dateMode to allow you to control how DATE fields should be returned (#148)

  If you choose 'date-object' it will be a JavaScript Date that is midnight in the client timeZone.

  Currently this defaults to 'date-object' but the default will change to 'string' in a future version.

  Usage:

  // to treat DATE as a Date object (this is currently the default):
  const db = createConnectionPool({
    dateMode: 'date-object'
  });
  
  // to treat DATE as string in the format "yyyy-mm-dd":
  const db = createConnectionPool({
    dateMode: 'string'
  });
  

• New option: dateTimeMode allows you to control how DATETIME fields should be returned from the database (#148)

  • If you choose 'date-object' it will be a JavaScript Date.
  • If you choose 'string' it will be a string in the MySQL format, i.e. "yyyy-mm-dd HH:MM:SS[.nnnnnn]" with no time zone

  // to treat DATETIME as a Date object (this is the default):
  const db = createConnectionPool({
    dateTimeMode: 'date-object'
  });
  
  // to treat DATETIME as string in the format "yyyy-mm-dd HH:MM:SS[.nnnnnn]":
  const db = createConnectionPool({
    dateTimeMode: 'string'
  });
  

• New option: timeStampMode allows you to control how DATETIME fields should be returned from the database (#148)

  • If you choose 'date-object' it will be a JavaScript Date.
  • If you choose 'string' it will be a string in the MySQL format, i.e. "yyyy-mm-dd HH:MM:SS[.nnnnnn]" with no time zone

  // to treat TIMESTAMP as a Date object (this is the default):
  const db = createConnectionPool({
    timeStampMode: 'date-object'
  });
  
  // to treat TIMESTAMP as string in the format "yyyy-mm-dd HH:MM:SS[.nnnnnn]":
  const db = createConnectionPool({
    timeStampMode: 'string'
  });
  

• New option: timeZone allows you to control the time zone to use when serializing and parsing (#148)

  There are two timestamps involved in MySQL:

  • The MySQL "server" timezone. This is the timezone used for TIMESTAMP fields and the NOW() function in SQL
  • The Node.js "client" timezone. This is the timezone that node.js uses when interpreting strings from MySQL as Date objects, and converting Date objects to strings before sending to MySQL

  The "client" timezone defaults to 'local', i.e. the timezone of your server. This can be overridden using the the TZ environment variable.

  The "server" timezone defaults to undefined, which means it will just be whatever your MySQL server is currently configured with.

  Strange things can happen when these don't match. It's also not a good idea to store times in an arbitrary time zone. It's therefore a good idea to set both to utc.

  // default value, not recommended:
  const db = createConnectionPool({
    timeZone: {client: 'local', server: undefined},
  });
  
  // set both server and client to utc, recommended:
  const db = createConnectionPool({
    timeZone: 'utc'
  });
  

• New option: maxUses allows you to limit how many times the same connection is re-used from the pool. This is unlikely to be needed. Defaults to Infinity (#148)

• New option: idleTimeoutMilliseconds - number of milliseconds a connection can be idle in the pool before it is automatically closed and discarded. Defaults to 10,000 (i.e. 10 seconds) (#148)

• New option: queueTimeoutMilliseconds - number of milliseconds to wait for a connection from the connection pool. Defaults to 60,000 (i.e. 60 seconds) (#148)

• New option: aquireLockTimeoutMilliseconds - number of milliseconds to wait for a lock on a connection/transaction. This is helpful for catching cases where you have accidentally attempted to query a connection within a transaction that is on that connection, or attempted to query an outer transaction within a nested transaction. (#148)

• Event handlers (#148)

  These event handlers can be passed in when creating the connection pool:

  // database.ts
  
  import createConnectionPool, {sql} from '@databases/mysql';
  
  export {sql};
  
  const db = createConnectionPool({
    onQueryStart: (_query, {text, values}) => {
      console.log(
        `${new Date().toISOString()} START QUERY ${text} - ${JSON.stringify(
          values,
        )}`,
      );
    },
    onQueryResults: (_query, {text}, results) => {
      console.log(
        `${new Date().toISOString()} END QUERY   ${text} - ${
          results.length
        } results`,
      );
    },
    onQueryError: (_query, {text}, err) => {
      console.log(
        `${new Date().toISOString()} ERROR QUERY ${text} - ${err.message}`,
      );
    },
  });
  
  export default db;
  

New Features

• Initial release (#148)

Bug Fixes

• Fixed an issue that could cause some queries to not be split (#148)

Breaking Changes

• Restructured API to make it clearer what calls what (#148)

New Features

• Initial release (#148)

Bug Fixes

• Fix typo in error message (#148)

New Features

• Initial release (#146)

New Features

• Initial release (#148)

New Features

• Added events to enable logging each Postgres query (#144)

  These event handlers can be passed in when creating the connection pool:

  // database.ts
  
  import createConnectionPool, {sql} from '@databases/pg';
  
  export {sql};
  
  const db = createConnectionPool({
    onQueryStart: (_query, {text, values}) => {
      console.log(
        `${new Date().toISOString()} START QUERY ${text} - ${JSON.stringify(
          values,
        )}`,
      );
    },
    onQueryResults: (_query, {text}, results) => {
      console.log(
        `${new Date().toISOString()} END QUERY   ${text} - ${
          results.length
        } results`,
      );
    },
    onQueryError: (_query, {text}, err) => {
      console.log(
        `${new Date().toISOString()} ERROR QUERY ${text} - ${err.message}`,
      );
    },
  });
  
  export default db;
  

  See Postgres Logging & Debugging for details.

New Features

• Support async stack traces in node.js 14+ (#141)

  Previously errors thrown by pg would have their stack traces cut off, making it difficult to track down where the call originated from. This update means that stack traces will normally include the full chain of functions that lead up to the query, even if they are async.

Bug Fixes

• Fix typo in bigIntMode warning message (#138)

New Features

• Added a serializeValue option (#134)

  This lets you apply a custom transformation to any column value before passing it to the database in insert/update statements, and where clauses. If you update to @databases/pg-schema-cli version 4.0.0, it will automatically generate a serializeValue function that handles calling JSON.stringify(...) on any values being passed to JSON or JSONB columns. This fixes an issue that caused an error to be thrown if you attempted to store an array (or other non-object value) in a column of type JSON or JSONB.

  N.B. If you were previously calling JSON.stringify manually (and suppressing the TypeScript error) and you enable this feature, you will need to remove those calls to JSON.stringify, otherwise your values will be stringified twice. Which will result in them being incorrectly parsed when you query.

Breaking Changes

• Generates a new serializeValue function (#134)

  By default, this is placed in index.ts. You can pass this function to pg-typed to automatically serialize all JSON & JSONB fields, even when they are not objects. This is useful if you want to store Arrays, booleans, strings etc. in JSON fields.

Breaking Changes

• Generates a new serializeValue function (#134)

  By default, this is placed in index.ts. You can pass this function to pg-typed to automatically serialize all JSON & JSONB fields, even when they are not objects. This is useful if you want to store Arrays, booleans, strings etc. in JSON fields.

New Features

• Add config for serializeValueTypeName and serializeValueFileName (#134)

  These let you configure the name & file location of the new serializeValue function that pg-schema-print-types outputs

Bug Fixes

• Do not pass file migrations as an array when calling db.query (#132)

  This would have resulted in the error:

  The number of queries in the array did not match the number of result sets. You cannot pass a query with multiple statements as an entry in an array.

  For any migration with multiple statements.

Bug Fixes

• Expose IsolationLevel enum (#131)

Bug Fixes

• Do not verify ssl certificates when no certs were provided (#130)

  This is a closer match for the default behaviour of most postgres clients. You can still perform ssl verification by providing sslcert, sslkey, sslrootcert or by specifying the ssl options when constructing the client. If ssl is only enabled because the connection string or environment variables specifies an sslmode, we will not verify certificates.

New Features

• Added support for more complex queries (#127)

  This adds new conditions:

  • not(condition) - check the field does not match the condition
  • anyOf([...conditions]) - check the field matches one or more of the conditions
  • greaterThan(value) - check the field is greater than value
  • lessThan(value) - check the field is less than value
  • inQueryResult(query) - check the field value is in the results of query

  You can use any of these in place of field values for the where clause in .find and .findOne calls. It is still only possible to include results from a single table, but these helpers make queries significantly more flexible.

Refactorings

• Renamed .selectOne(...) to .findOne(...) (#125)

  The old method name is still supported, but it is deprecated and will be removed in a future major version.

  The name .selectOne was confusing for people familiar with SQL, because we are never selecting/filtering the list of columns that are retrieved, we are filtering the rows. Hopefully the verb "find" will be a more natural fit.

• Renamed .select(...) to .find(...) (#125)

  The old method name is still supported, but it is deprecated and will be removed in a future major version.

  The name .select was confusing for people familiar with SQL, because we are never selecting/filtering the list of columns that are retrieved, we are filtering the rows. Hopefully the verb "find" will be a more natural fit.

Bug Fixes

• Generate IS NULL instead of = ${null} when null is passed as a condition (#126)

  This fixes an issue where pg-typed would never return any results if you passed null as one of the fields.

Bug Fixes

• Handle empty arrays passed to .query (#123)

  If you call db.query([]) it is a no-op and just immediately returns. If you call it on a ConnectionPool, it won't even allocate a connection.

New Features

• Expose Transaction and Queryable types (#121)