@databases/mysql - npm Package Compare versions

Comparing version 3.0.0 to 4.0.0

lib/__tests__/index.test.mysql.js

@@ -29,7 +29,5 @@ "use strict";
 
-        1 | SELECT 1 + ? as foo;
-        2 | SELECT 1 + 42 as bar;
-      > 3 | SELECT * FRM 'baz;
+      > 1 | SELECT * FRM 'baz;
           |          ^^^^^^^^^
-      > 4 | SELECT * FROM bing;
+      > 2 | SELECT * FROM bing;
           | ^^^^^^^^^^^^^^^^^^^
@@ -54,19 +52,2 @@ "
   expect(foo).toBe(42);
-});
-test('bigint', async () => {
-  await db.query(__1.sql`CREATE TABLE bigint_test_bigints (id BIGINT NOT NULL PRIMARY KEY);`);
-  await db.query(__1.sql`
-    INSERT INTO bigint_test_bigints (id)
-    VALUES (1),
-           (2),
-           (42);
-  `);
-  const result = await db.query(__1.sql`SELECT id from bigint_test_bigints;`);
-  expect(result).toEqual([{
-    id: 1
-  }, {
-    id: 2
-  }, {
-    id: 42
-  }]);
 });

lib/index.d.ts

@@ -1,19 +0,105 @@
-/// <reference types="node" />
 import sql, { SQLQuery, isSqlQuery } from '@databases/sql';
+import Queryable, { Connection, ConnectionPool, Transaction } from './types/Queryable';
+import EventHandlers from './types/EventHandlers';
 export type { SQLQuery };
 export { sql, isSqlQuery };
-export interface Connection {
-    query(query: SQLQuery): Promise<any[]>;
-    queryStream(query: SQLQuery, options?: {
-        highWaterMark?: number;
-    }): AsyncIterableIterator<any>;
-    queryNodeStream(query: SQLQuery, options?: {
-        highWaterMark?: number;
-    }): NodeJS.ReadableStream;
+export type { Queryable, Transaction, Connection, ConnectionPool };
+export interface ConnectionPoolConfig extends EventHandlers {
+    /**
+     * Should the `TINYINT` type be treated as a boolean or a number?
+     *
+     * 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`.
+     *
+     * See https://www.mysqltutorial.org/mysql-boolean/ for details.
+     */
+    tinyIntMode?: 'boolean' | 'number';
+    /**
+     * How would you like bigints to be returned from the database?
+     *
+     * 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
+     */
+    bigIntMode?: 'string' | 'number' | 'bigint';
+    /**
+     * How would you like `DATE` types to be returned from the database?
+     *
+     * 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.
+     */
+    dateMode?: 'string' | 'date-object';
+    /**
+     * How would you like `DATETIME` types to be returned from the database?
+     *
+     * 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
+     *
+     * Currently this defaults to `'date-object'`.
+     */
+    dateTimeMode?: 'string' | 'date-object';
+    /**
+     * How would you like `TIMESTAMP` types to be returned from the database?
+     *
+     * 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
+     *
+     * Currently this defaults to `'date-object'`.
+     */
+    timeStampMode?: 'string' | 'date-object';
+    /**
+     * Time zone to use when serializing and parsing
+     */
+    timeZone?: 'local' | 'utc' | {
+        server?: 'local' | 'utc';
+        client: 'local' | 'utc';
+    };
+    /**
+     * Defaults to process.env.DATABASE_URL
+     */
+    connectionString?: string;
+    /**
+     * maximum number of clients the pool should contain
+     * by default this is set to 10.
+     */
+    poolSize?: number;
+    /**
+     * Maximum times to use a single connection from a connection pool before
+     * discarding it and requesting a fresh connection.
+     * defaults to Infinity
+     */
+    maxUses?: number;
+    /**
+     * number of milliseconds a client must sit idle in the pool and not be checked out
+     * before it is disconnected from the backend and discarded
+     *
+     * default is 30_000 (30 seconds) - set to 0 to disable auto-disconnection of idle clients
+     */
+    idleTimeoutMilliseconds?: number;
+    /**
+     * Number of milliseconds to wait for a connection from the connection pool.
+     *
+     * Defaults to 60 seconds
+     */
+    queueTimeoutMilliseconds?: number;
+    /**
+     * 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.
+     *
+     * Defaults to 60 seconds
+     */
+    aquireLockTimeoutMilliseconds?: number;
+    onError?: (err: Error) => void;
 }
-export interface ConnectionPool extends Connection {
-    task<T>(fn: (connection: Connection) => Promise<T>): Promise<T>;
-    tx<T>(fn: (connection: Connection) => Promise<T>): Promise<T>;
-    dispose(): Promise<void>;
-}
-export default function connect(connectionConfig?: string | undefined): ConnectionPool;
+export default function createConnectionPool(connectionConfig?: string | ConnectionPoolConfig | undefined): ConnectionPool;

lib/index.js

@@ -6,306 +6,210 @@ "use strict";
 });
+exports.isSqlQuery = exports.sql = void 0;
 
 const url_1 = require("url");
 
-const escape_identifier_1 = require("@databases/escape-identifier");
-
 const mysql_config_1 = require("@databases/mysql-config");
 
-const push_to_async_iterable_1 = require("@databases/push-to-async-iterable");
-
 const sql_1 = require("@databases/sql");
 
 exports.sql = sql_1.default;
-exports.isSqlQuery = sql_1.isSqlQuery;
+Object.defineProperty(exports, "isSqlQuery", {
+  enumerable: true,
+  get: function () {
+    return sql_1.isSqlQuery;
+  }
+});
 
-const raw_1 = require("./raw");
+const ConnectionPool_1 = require("./ConnectionPool");
 
-const stream_1 = require("stream");
-
 const {
-  codeFrameColumns
-} = require('@babel/code-frame');
-
-const {
   connectionStringEnvironmentVariable
 } = mysql_config_1.getMySqlConfigSync();
-const mysqlFormat = {
-  escapeIdentifier: str => escape_identifier_1.escapeMySqlIdentifier(str),
-  formatValue: value => ({
-    placeholder: '?',
-    value
-  })
-};
 
-function transformError(text, ex) {
-  // TODO: consider using https://github.com/Vincit/db-errors
-  if (ex.code === 'ER_PARSE_ERROR' && ex.sqlState === '42000' && typeof ex.sqlMessage === 'string') {
-    const match = / near \'((?:.|\n)+)\' at line (\d+)$/.exec(ex.sqlMessage);
+function createConnectionPool(connectionConfig = process.env[connectionStringEnvironmentVariable]) {
+  const connectionConfigObject = typeof connectionConfig === 'object' ? connectionConfig : {
+    connectionString: connectionConfig
+  };
+  const {
+    connectionString = process.env[connectionStringEnvironmentVariable]
+  } = connectionConfigObject;
 
-    if (match) {
-      const index = text.indexOf(match[1]);
+  if (!connectionString) {
+    throw new Error('You must provide a connection string for @databases/mysql. You can ' + 'either pass one directly to the createConnection call or set ' + `the ${connectionStringEnvironmentVariable} environment variable.`);
+  }
 
-      if (index === text.lastIndexOf(match[1])) {
-        const linesUptoStart = text.substr(0, index).split('\n');
-        const line = linesUptoStart.length;
-        const start = {
-          line,
-          column: linesUptoStart[linesUptoStart.length - 1].length + 1
-        };
-        const linesUptoEnd = text.substr(0, index + match[1].length).split('\n');
-        const end = {
-          line: linesUptoEnd.length,
-          column: linesUptoEnd[linesUptoEnd.length - 1].length + 1
-        };
-        ex.message = ex.message.replace(/ near \'((?:.|\n)+)\' at line (\d+)$/, ` near:\n\n${codeFrameColumns(text, {
-          start,
-          end
-        })}\n`);
-      }
+  validateMySqlUrl(connectionString);
+  const {
+    tinyIntMode = 'number',
+    bigIntMode = 'number',
+    dateMode = 'date-object',
+    dateTimeMode = 'date-object',
+    timeStampMode = 'date-object',
+    timeZone = {
+      client: 'local'
+    },
+    poolSize = 10,
+    maxUses = Infinity,
+    idleTimeoutMilliseconds = 30000,
+    queueTimeoutMilliseconds = 60000,
+    aquireLockTimeoutMilliseconds = 60000,
+    onConnectionClosed,
+    onConnectionOpened,
+    onQueryStart,
+    onQueryResults,
+    onQueryError,
+    onError = err => {
+      console.warn(`Error in MySQL ConnectionPool: ${err.message}`);
     }
-  }
-}
+  } = connectionConfigObject;
+  const serverTimeZone = typeof timeZone === 'string' ? timeZone : timeZone.server;
+  const clientTimeZone = typeof timeZone === 'string' ? timeZone : timeZone.client;
+  const tinyIntParser = getTinyIntParser(tinyIntMode);
+  const bigIntParser = getBigIntParser(bigIntMode);
+  const dateParer = getDateParser(dateMode, clientTimeZone);
+  const dateTimeParser = getDateTimeParser(dateTimeMode, clientTimeZone);
+  const timeStampParser = getDateTimeParser(timeStampMode, clientTimeZone);
+  return new ConnectionPool_1.default({
+    uri: connectionString,
+    multipleStatements: true,
+    timezone: clientTimeZone === 'utc' ? 'Z' : clientTimeZone,
+    typeCast: (field, next) => {
+      switch (field.type) {
+        case 'TINY':
+          return tinyIntParser(field);
 
-class ConnectionImplementation {
-  constructor(conn) {
-    this.conn = conn;
-  }
+        case 'LONGLONG':
+          return bigIntParser(field);
 
-  async query(query) {
-    if (!sql_1.isSqlQuery(query)) {
-      throw new Error('Invalid query, you must use @databases/sql to create your queries.');
-    }
+        case 'DATE':
+          return dateParer(field);
 
-    const {
-      text,
-      values
-    } = query.format(mysqlFormat);
+        case 'DATETIME':
+          return dateTimeParser(field);
 
-    try {
-      return (await this.conn.query(text, values))[0];
-    } catch (ex) {
-      transformError(text, ex);
-      throw ex;
-    }
-  }
+        case 'TIMESTAMP':
+          return timeStampParser(field);
+      }
 
-  queryStream(query, options) {
-    if (!sql_1.isSqlQuery(query)) {
-      throw new Error('Invalid query, you must use @databases/sql to create your queries.');
+      return next();
     }
+  }, {
+    maxSize: poolSize,
+    maxUses,
+    idleTimeoutMilliseconds,
+    queueTimeoutMilliseconds
+  }, {
+    onConnectionClosed,
+    onConnectionOpened,
+    onQueryStart,
+    onQueryResults,
+    onQueryError
+  }, onError, aquireLockTimeoutMilliseconds, serverTimeZone);
+}
 
-    const {
-      text,
-      values
-    } = query.format(mysqlFormat);
-    const highWaterMark = options && options.highWaterMark || 5;
-    const stream = this.conn.connection.query(text, values);
-    return push_to_async_iterable_1.default({
-      onData(fn) {
-        stream.on('result', fn);
-      },
+exports.default = createConnectionPool;
 
-      onError(fn) {
-        stream.on('error', fn);
-      },
+function validateMySqlUrl(urlString) {
+  let url;
 
-      onEnd(fn) {
-        stream.on('end', fn);
-      },
+  try {
+    url = new url_1.URL(urlString);
+  } catch (ex) {
+    throw new Error('Invalid MySQL connection string, expected a URI: ' + urlString);
+  }
 
-      pause: () => {
-        this.conn.connection.pause();
-      },
-      resume: () => {
-        this.conn.connection.resume();
-      },
-      highWaterMark
-    });
+  if (url.protocol !== 'mysqlx:' && url.protocol !== 'mysql:') {
+    throw new Error('Invalid MySQL connection string, expected protocol to be "mysql" or "mysqlx": ' + urlString);
   }
+}
 
-  queryNodeStream(query, options) {
-    if (!sql_1.isSqlQuery(query)) {
-      throw new Error('Invalid query, you must use @databases/sql to create your queries.');
-    }
+function getTinyIntParser(mode) {
+  switch (mode) {
+    case 'number':
+      return f => parseInt(f.string(), 10);
 
-    const {
-      text,
-      values
-    } = query.format(mysqlFormat);
-    const result = this.conn.connection.query(text, values).stream(options); // tslint:disable-next-line:no-unbound-method
-
-    const on = result.on;
-    const transformedExceptions = new Set();
-    return Object.assign(result, {
-      on(event, cb) {
-        if (event !== 'error') return on.call(this, event, cb);
-        return on.call(this, event, ex => {
-          // TODO: consider using https://github.com/Vincit/db-errors
-          if (!transformedExceptions.has(ex)) {
-            transformedExceptions.add(ex);
-            transformError(text, ex);
-          }
-
-          cb(ex);
-        });
-      }
-
-    });
+    case 'boolean':
+      return f => f.string() !== '0';
   }
-
 }
 
-class ConnectionPoolImplemenation {
-  constructor(pool) {
-    this.pool = pool;
-  }
+function getBigIntParser(mode) {
+  switch (mode) {
+    case 'number':
+      return f => parseInt(f.string(), 10);
 
-  async query(query) {
-    if (!sql_1.isSqlQuery(query)) {
-      throw new Error('Invalid query, you must use @databases/sql to create your queries.');
-    }
+    case 'string':
+      return f => f.string();
 
-    const {
-      text,
-      values
-    } = query.format(mysqlFormat);
-
-    try {
-      return (await this.pool.query(text, values))[0];
-    } catch (ex) {
-      transformError(text, ex);
-      throw ex;
-    }
+    case 'bigint':
+      return f => BigInt(f.string());
   }
+}
 
-  async *queryStream(query, options) {
-    const connection = await this.pool.getConnection();
-    const c = new ConnectionImplementation(connection);
+function getDateParser(mode, timeZone) {
+  switch (mode) {
+    case 'string':
+      return f => f.string();
 
-    try {
-      for await (const record of c.queryStream(query, options)) {
-        yield record;
-      }
-    } finally {
-      connection.release();
-    }
-  }
+    case 'date-object':
+      return f => {
+        const match = /^(\d{4})\-(\d{2})\-(\d{2})$/.exec(f.string());
 
-  queryNodeStream(query, options) {
-    const stream = new stream_1.PassThrough({
-      objectMode: true,
-      highWaterMark: 2
-    });
-    this.pool.getConnection().then(connection => {
-      const c = new ConnectionImplementation(connection);
-      let released = false;
-      return c.queryNodeStream(query, options).on('fields', fields => {
-        stream.emit('fields', fields);
-      }).on('error', err => {
-        if (!released) {
-          released = true;
-          connection.release();
+        if (!match) {
+          throw new Error('Expected yyyy-mm-dd');
         }
 
-        stream.emit('error', err);
-      }).on('end', () => {
-        if (!released) {
-          released = true;
-          connection.release();
+        if (timeZone === 'utc') {
+          return new Date(Date.UTC(parseInt(match[1], 10), parseInt(match[2], 10) - 1, parseInt(match[3], 10), 0, 0, 0, 0));
+        } else {
+          return new Date(parseInt(match[1], 10), parseInt(match[2], 10) - 1, parseInt(match[3], 10), 0, 0, 0, 0);
         }
-
-        stream.emit('end');
-      }).pipe(stream);
-    }).catch(ex => stream.emit('error', ex));
-    return stream;
+      };
   }
-
-  async task(fn) {
-    const connection = await this.pool.getConnection();
-
-    try {
-      const result = await fn(new ConnectionImplementation(connection));
-      return result;
-    } finally {
-      connection.release();
-    }
-  }
-
-  async tx(fn) {
-    const connection = await this.pool.getConnection();
-    let completed = false;
-
-    try {
-      await connection.beginTransaction();
-      let result;
-
-      try {
-        result = await fn(new ConnectionImplementation(connection));
-      } catch (ex) {
-        await connection.rollback();
-        completed = true;
-        throw ex;
-      }
-
-      await connection.commit();
-      completed = true;
-      return result;
-    } finally {
-      if (completed) {
-        connection.release();
-      } else {
-        connection.destroy();
-      }
-    }
-  }
-
-  async dispose() {
-    await this.pool.end();
-  }
-
 }
 
-function connect(connectionConfig = process.env[connectionStringEnvironmentVariable]) {
-  if (!connectionConfig) {
-    throw new Error('You must provide a connection string for @databases/mysql. You can ' + 'either pass one directly to the createConnection call or set ' + `the ${connectionStringEnvironmentVariable} environment variable.`);
-  }
+function getDateTimeParser(mode, timeZone) {
+  switch (mode) {
+    case 'string':
+      return f => f.string();
 
-  validateMySqlUrl(connectionConfig);
-  const pool = raw_1.default({
-    uri: connectionConfig,
+    case 'date-object':
+      return f => {
+        var _a, _b;
 
-    authSwitchHandler({
-      pluginName,
-      pluginData
-    }, cb) {
-      const err = new Error(`Unknown AuthSwitchRequest plugin name ${pluginName}`);
-      err.fatal = true;
-      cb(err);
-    },
+        const match = /^(\d{4})\-(\d{2})\-(\d{2}) (\d{2})\:(\d{2})\:(\d{2})(?:\.(\d+))?$/.exec(f.string());
 
-    multipleStatements: true
-  });
-  return new ConnectionPoolImplemenation(pool);
-}
+        if (!match) {
+          throw new Error('Expected yyyy-mm-dd HH:MM:SS');
+        }
 
-exports.default = connect;
+        let ms = match[7] ? parseInt(match[7].length > 3 ? match[7].substr(0, 3) : match[7], 10) : 0;
 
-function validateMySqlUrl(urlString) {
-  let url;
+        if (((_a = match[7]) === null || _a === void 0 ? void 0 : _a.length) === 2) {
+          ms = ms * 10;
+        }
 
-  try {
-    url = new url_1.URL(urlString);
-  } catch (ex) {
-    throw new Error('Invalid MySQL connection string, expected a URI: ' + urlString);
-  }
+        if (((_b = match[7]) === null || _b === void 0 ? void 0 : _b.length) === 1) {
+          ms = ms * 100;
+        }
 
-  if (url.protocol !== 'mysqlx:' && url.protocol !== 'mysql:') {
-    throw new Error('Invalid MySQL connection string, expected protocol to be "mysql" or "mysqlx": ' + urlString);
+        if (timeZone === 'utc') {
+          return new Date(Date.UTC(parseInt(match[1], 10), parseInt(match[2], 10) - 1, parseInt(match[3], 10), parseInt(match[4], 10), // hours
+          parseInt(match[5], 10), // minutes
+          parseInt(match[6], 10), // seconds
+          ms));
+        } else {
+          return new Date(parseInt(match[1], 10), parseInt(match[2], 10) - 1, parseInt(match[3], 10), parseInt(match[4], 10), // hours
+          parseInt(match[5], 10), // minutes
+          parseInt(match[6], 10), // seconds
+          ms);
+        }
+      };
   }
 }
 
-module.exports = Object.assign(connect, {
-  default: connect,
+module.exports = Object.assign(createConnectionPool, {
+  default: createConnectionPool,
   sql: sql_1.default,
   isSqlQuery: sql_1.isSqlQuery
 });

lib/raw.d.ts

@@ -24,20 +24,1 @@ /// <reference types="node" />
 }
-export interface PoolConnection {
-    readonly connection: CoreConnection;
-    release(): void;
-    destroy(): void;
-    query(sql: string, args: any[]): Promise<[unknown[], undefined | ColumnDefinition[]]>;
-    execute(sql: string, args: any[]): Promise<unknown>;
-    beginTransaction(): Promise<void>;
-    commit(): Promise<void>;
-    rollback(): Promise<void>;
-    ping(): Promise<void>;
-}
-export interface Pool {
-    getConnection(): Promise<PoolConnection>;
-    query(sql: string, args: any[]): Promise<[unknown[], undefined | ColumnDefinition[]]>;
-    execute(sql: string, args: any[]): Promise<unknown>;
-    end(): Promise<void>;
-}
-declare const _default: (opts: any) => Pool;
-export default _default;

lib/raw.js

@@ -5,6 +5,2 @@ "use strict";
   value: true
-});
-
-const mysql = require('mysql2/promise');
-
-exports.default = mysql.createPool;
+});

package.json

 {
   "name": "@databases/mysql",
-  "version": "3.0.0",
+  "version": "4.0.0",
   "description": "",
@@ -9,8 +9,9 @@ "main": "./lib/index.js",
     "@babel/code-frame": "^7.0.0",
-    "@databases/escape-identifier": "^1.0.0",
+    "@databases/escape-identifier": "^1.0.1",
     "@databases/mysql-config": "^2.0.0",
-    "@databases/push-to-async-iterable": "^2.0.0",
+    "@databases/push-to-async-iterable": "^3.0.0",
+    "@databases/shared": "^1.0.0",
     "@databases/sql": "^3.0.0",
     "@types/mysql": "^2.15.5",
-    "mysql2": "^1.6.4"
+    "mysql2": "^2.2.5"
   },
@@ -17,0 +18,0 @@ "scripts": {},

New alerts

Major refactor

Supply chain risk

Package has recently undergone a major refactor. It may be unstable or indicate significant internal changes. Use caution when updating to versions that include significant changes.

Found 1 instance in 1 package

Environment variable access

Supply chain risk

Package accesses environment variables, which may be a sign of credential stuffing or data theft.

Found 1 instance in 1 package

Fixed alerts

Major refactor

Supply chain risk

Package has recently undergone a major refactor. It may be unstable or indicate significant internal changes. Use caution when updating to versions that include significant changes.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

85816

increased by230.29%

Number of package files

48

increased by220%

Lines of code

1495

increased by248.48%

Worsened metrics

Dependency count

8

increased by14.29%

Number of low supply chain risk alerts

5

increased by150%

Dependency changes

• + Added@databases/shared@^1.0.0

• + Added@databases/connection-pool@1.1.0(transitive)

• + Added@databases/lock@1.0.0(transitive)

• + Added@databases/push-to-async-iterable@3.0.0(transitive)

• + Added@databases/queue@1.0.1(transitive)

• + Added@databases/shared@1.0.0(transitive)

• + Added@databases/split-sql-query@1.0.4(transitive)

• + Addedcuid@2.1.8(transitive)

• + Addeddenque@2.1.0(transitive)

• + Addediconv-lite@0.6.3(transitive)

• + Addedis-promise@4.0.0(transitive)

• + Addedlru-cache@6.0.0(transitive)

• + Addedmysql2@2.3.3(transitive)

• + Addedyallist@4.0.0(transitive)

• - Removed@databases/push-to-async-iterable@2.0.0(transitive)

• - Removedasap@1.0.0(transitive)

• - Removeddenque@1.5.1(transitive)

• - Removediconv-lite@0.5.2(transitive)

• - Removedlru-cache@5.1.1(transitive)

• - Removedmysql2@1.7.0(transitive)

• - Removedpromise@6.1.0(transitive)

• - Removedthen-queue@1.3.0(transitive)

• - Removedyallist@3.1.1(transitive)

• Updated@databases/escape-identifier@^1.0.1

• Updated@databases/push-to-async-iterable@^3.0.0

• Updatedmysql2@^2.2.5