slonik - npm Package Compare versions

Comparing version 14.0.0 to 14.1.0

dist/templateTags/sql.js

@@ -20,5 +20,17 @@ "use strict";
   namespace: 'sql'
-}); // eslint-disable-next-line complexity
+});
 
+const createIncrementalNames = columnCount => {
+  const columnNames = [];
+  let index = 0;
 
+  while (index < columnCount) {
+    columnNames.push(String.fromCharCode('a'.charCodeAt(0) + index));
+    index++;
+  }
+
+  return columnNames;
+}; // eslint-disable-next-line complexity
+
+
 const sql = (parts, ...values) => {
@@ -107,2 +119,50 @@ let raw = '';
       raw += tupleListMemberSql.join(', ');
+    } else if (value && value.type === 'UNNEST_LIST' && Array.isArray(value.tuples)) {
+      (0, _invariant.default)(Array.isArray(value.columnTypes), 'Unexpected column types shape.');
+      const columnTypes = value.columnTypes;
+      const aliasNames = Array.isArray(value.aliasNames) ? value.aliasNames : createIncrementalNames(value.columnTypes.length);
+
+      if (columnTypes.length !== aliasNames.length) {
+        throw new Error('Column types length must match alias names length.');
+      }
+
+      const unnestBindings = [];
+      const unnsetSqlTokens = [];
+      let columnIndex = 0;
+      let placeholderIndex = bindings.length;
+
+      while (columnIndex < columnTypes.length) {
+        const columnType = columnTypes[columnIndex];
+        const aliasName = aliasNames[columnIndex];
+        (0, _invariant.default)(typeof columnType === 'string', 'Column type unavailable');
+        (0, _invariant.default)(typeof aliasName === 'string', 'Alias name unavailable');
+        unnsetSqlTokens.push('UNNEST($' + ++placeholderIndex + '::' + columnType + '[]) ' + aliasName);
+        unnestBindings[columnIndex] = [];
+        columnIndex++;
+      }
+
+      let lastTupleSize;
+
+      for (const tupleValues of value.tuples) {
+        (0, _invariant.default)(Array.isArray(tupleValues), 'Values must be an array.');
+
+        if (typeof lastTupleSize === 'number' && lastTupleSize !== tupleValues.length) {
+          throw new Error('Each tuple in a list of tuples must have an equal number of members.');
+        }
+
+        if (tupleValues.length !== columnTypes.length) {
+          throw new Error('Column types length must match tuple member length.');
+        }
+
+        lastTupleSize = tupleValues.length;
+        let tupleColumnIndex = 0;
+
+        for (const tupleValue of tupleValues) {
+          (0, _invariant.default)((0, _isPrimitiveValueExpression.default)(tupleValue), 'Unexpected tuple member type.');
+          unnestBindings[tupleColumnIndex++].push(tupleValue);
+        }
+      }
+
+      bindings.push(...unnestBindings);
+      raw += unnsetSqlTokens.join(', ');
     } else if ((0, _isPrimitiveValueExpression.default)(value)) {
@@ -164,4 +224,13 @@ raw += '$' + (bindings.length + 1);
 
+sql.unnestList = (tuples, columnTypes, aliasNames) => {
+  return {
+    aliasNames: aliasNames || null,
+    columnTypes,
+    tuples,
+    type: 'UNNEST_LIST'
+  };
+};
+
 var _default = sql;
 exports.default = _default;
 //# sourceMappingURL=sql.js.map

package.json

@@ -96,3 +96,3 @@ {
   },
-  "version": "14.0.0"
+  "version": "14.1.0"
 }

README.md

@@ -10,3 +10,3 @@ <a name="slonik"></a>
 
-A [battle-tested](#battled-tested) PostgreSQL client with strict types, detail logging and assertions.
+A [battle-tested](#battle-tested) PostgreSQL client with strict types, detail logging and assertions.
 
@@ -30,5 +30,8 @@ <a name="slonik-features"></a>
 
-<a name="slonik-battle-tested"></a>
-## Battle-Tested
+<a name="slonik-about-slonik"></a>
+## About Slonik
 
+<a name="slonik-about-slonik-battle-tested"></a>
+### Battle-Tested
+
 Slonik began as a collection of utilities designed for working with [`node-postgres`](https://github.com/brianc/node-postgres). We continue to use `node-postgres` as it provides a robust foundation for interacting with PostgreSQL. However, what once was a collection of utilities has since grown into a framework that abstracts repeating code patterns, protects against unsafe connection handling and value interpolation, and provides rich debugging experience.
@@ -38,4 +41,4 @@
 
-<a name="slonik-origin-of-the-name"></a>
-## Origin of the name
+<a name="slonik-about-slonik-origin-of-the-name"></a>
+### Origin of the name
 
@@ -48,2 +51,79 @@ ![Slonik](./.README/postgresql-elephant.png)
 
+<a name="slonik-about-slonik-repeating-code-patterns-and-type-safety"></a>
+### Repeating code patterns and type safety
+
+Among the primary reasons for developing Slonik, was the motivation to reduce the repeating code patterns and add a level of type safety. This is primarily achieved through the methods such as `one`, `many`, etc. But what is the issue? It is best illustrated with an example.
+
+Suppose the requirement is to write a method that retrieves a resource ID given values defining (what we assume to be) a unique constraint. If we did not have the aforementioned convenience methods available, then it would need to be written as:
+
+```js
+// @flow
+import {
+  sql
+} from 'slonik';
+import type {
+  DatabaseConnectionType
+} from 'slonik';
+
+opaque type DatabaseRecordIdType = number;
+
+const getFooIdByBar = async (connection: DatabaseConnectionType, bar: string): Promise<DatabaseRecordIdType> => {
+  const fooResult = await connection.query(sql`
+    SELECT id
+    FROM foo
+    WHERE bar = ${bar}
+  `);
+
+  if (fooResult.rowCount === 0) {
+    throw new Error('Resource not found.');
+  }
+
+  if (fooResult.rowCount > 1) {
+    throw new Error('Data integrity constraint violation.');
+  }
+
+  return fooResult[0].id;
+};
+
+```
+
+`oneFirst` method abstracts all of the above logic into:
+
+```js
+const getFooIdByBar = (connection: DatabaseConnectionType, bar: string): Promise<DatabaseRecordIdType> => {
+  return connection.oneFirst(sql`
+    SELECT id
+    FROM foo
+    WHERE bar = ${bar}
+  `);
+};
+
+```
+
+`oneFirst` throws:
+
+* `NotFoundError` if query returns no rows
+* `DataIntegrityError` if query returns multiple rows
+* `DataIntegrityError` if query returns multiple columns
+
+This becomes particularly important when writing routines where multiple queries depend on the previous result. Using methods with inbuilt assertions ensure that in case of an error, the error points to the original source of the problem. In contrast, unless assertions for all possible outcomes are typed out as in the previous example, the unexpected result of the query will be fed to the next operation. If you are lucky, the next operation will simply break; if you are unlucky, you are risking data corruption and hard to locate bugs.
+
+Furthermore, using methods that guarantee the shape of the results, allows us to leverage static type checking and catch some of the errors even before they executing the code, e.g.
+
+```js
+const fooId = await connection.many(sql`
+  SELECT id
+  FROM foo
+  WHERE bar = ${bar}
+`);
+
+await connection.query(sql`
+  DELETE FROM baz
+  WHERE foo_id = ${fooId}
+`);
+
+```
+
+The above example will throw an error as the `fooId` is guaranteed to be an array and last query binding is expecting a primitive value.
+
 <a name="slonik-documentation"></a>
@@ -54,4 +134,6 @@ ## Documentation
     * [Features](#slonik-features)
-    * [Battle-Tested](#slonik-battle-tested)
-    * [Origin of the name](#slonik-origin-of-the-name)
+    * [About Slonik](#slonik-about-slonik)
+        * [Battle-Tested](#slonik-about-slonik-battle-tested)
+        * [Origin of the name](#slonik-about-slonik-origin-of-the-name)
+        * [Repeating code patterns and type safety](#slonik-about-slonik-repeating-code-patterns-and-type-safety)
     * [Documentation](#slonik-documentation)
@@ -76,2 +158,3 @@ * [Usage](#slonik-usage)
         * [`sql.tupleList`](#slonik-value-placeholders-sql-tuplelist)
+        * [`sql.unnestList`](#slonik-value-placeholders-sql-unnestlist)
         * [`sql.identifier`](#slonik-value-placeholders-sql-identifier)
@@ -748,2 +831,54 @@ * [`sql.raw`](#slonik-value-placeholders-sql-raw)
 
+<a name="slonik-value-placeholders-sql-unnestlist"></a>
+### <code>sql.unnestList</code>
+
+```js
+/**
+ * @param aliasNames When alias names for the unnset members are not provided, then incremental names are used (a, b, c...).
+ */
+(
+  tuples: $ReadOnlyArray<$ReadOnlyArray<PrimitiveValueExpressionType>>,
+  columnTypes: $ReadOnlyArray<string>,
+  aliasNames?: $ReadOnlyArray<string>
+): UnnestListSqlTokenType;
+
+```
+
+Creates a list of `unnset` expressions, e.g.
+
+```js
+await connection.query(sql`
+  SELECT ${sql.unnestList(
+    [
+      [1, 'foo'],
+      [2, 'bar']
+    ],
+    [
+      'integer',
+      'text'
+    ]
+  )}
+`);
+
+```
+
+Produces:
+
+```js
+{
+  sql: 'SELECT UNNEST($1::integer[]), UNNEST($2::text[])',
+  values: [
+    [
+      1,
+      2
+    ],
+    [
+      'foo',
+      'bar'
+    ]
+  ]
+}
+
+```
+
 <a name="slonik-value-placeholders-sql-identifier"></a>
@@ -750,0 +885,0 @@ ### <code>sql.identifier</code>

src/templateTags/sql.js

@@ -11,2 +11,3 @@ // @flow
   TupleSqlTokenType,
+  UnnestListSqlTokenType,
   ValueExpressionType,
@@ -25,2 +26,16 @@ ValueListSqlTokenType
 
+const createIncrementalNames = (columnCount: number): $ReadOnlyArray<string> => {
+  const columnNames = [];
+
+  let index = 0;
+
+  while (index < columnCount) {
+    columnNames.push(String.fromCharCode('a'.charCodeAt(0) + index));
+
+    index++;
+  }
+
+  return columnNames;
+};
+
 // eslint-disable-next-line complexity
@@ -129,2 +144,60 @@ const sql = (parts: $ReadOnlyArray<string>, ...values: $ReadOnlyArray<ValueExpressionType>): TaggledTemplateLiteralInvocationType => {
       raw += tupleListMemberSql.join(', ');
+    } else if (value && value.type === 'UNNEST_LIST' && Array.isArray(value.tuples)) {
+      invariant(Array.isArray(value.columnTypes), 'Unexpected column types shape.');
+
+      const columnTypes = value.columnTypes;
+
+      const aliasNames = Array.isArray(value.aliasNames) ? value.aliasNames : createIncrementalNames(value.columnTypes.length);
+
+      if (columnTypes.length !== aliasNames.length) {
+        throw new Error('Column types length must match alias names length.');
+      }
+
+      const unnestBindings = [];
+      const unnsetSqlTokens = [];
+
+      let columnIndex = 0;
+
+      let placeholderIndex = bindings.length;
+
+      while (columnIndex < columnTypes.length) {
+        const columnType = columnTypes[columnIndex];
+        const aliasName = aliasNames[columnIndex];
+
+        invariant(typeof columnType === 'string', 'Column type unavailable');
+        invariant(typeof aliasName === 'string', 'Alias name unavailable');
+
+        unnsetSqlTokens.push('UNNEST($' + ++placeholderIndex + '::' + columnType + '[]) ' + aliasName);
+
+        unnestBindings[columnIndex] = [];
+
+        columnIndex++;
+      }
+
+      let lastTupleSize;
+
+      for (const tupleValues of value.tuples) {
+        invariant(Array.isArray(tupleValues), 'Values must be an array.');
+
+        if (typeof lastTupleSize === 'number' && lastTupleSize !== tupleValues.length) {
+          throw new Error('Each tuple in a list of tuples must have an equal number of members.');
+        }
+
+        if (tupleValues.length !== columnTypes.length) {
+          throw new Error('Column types length must match tuple member length.');
+        }
+
+        lastTupleSize = tupleValues.length;
+
+        let tupleColumnIndex = 0;
+
+        for (const tupleValue of tupleValues) {
+          invariant(isPrimitiveValueExpression(tupleValue), 'Unexpected tuple member type.');
+
+          unnestBindings[tupleColumnIndex++].push(tupleValue);
+        }
+      }
+
+      bindings.push(...unnestBindings);
+      raw += unnsetSqlTokens.join(', ');
     } else if (isPrimitiveValueExpression(value)) {
@@ -188,2 +261,15 @@ raw += '$' + (bindings.length + 1);
 
+sql.unnestList = (
+  tuples: $ReadOnlyArray<$ReadOnlyArray<PrimitiveValueExpressionType>>,
+  columnTypes: $ReadOnlyArray<string>,
+  aliasNames?: $ReadOnlyArray<string>
+): UnnestListSqlTokenType => {
+  return {
+    aliasNames: aliasNames || null,
+    columnTypes,
+    tuples,
+    type: 'UNNEST_LIST'
+  };
+};
+
 export default sql;

src/types.js

@@ -134,27 +134,34 @@ // @flow
 export type IdentifierTokenType = {|
-  names: $ReadOnlyArray<string>,
-  type: 'IDENTIFIER'
+  +names: $ReadOnlyArray<string>,
+  +type: 'IDENTIFIER'
 |};
 
 export type RawSqlTokenType = {|
-  sql: string,
-  type: 'RAW_SQL',
-  values: $ReadOnlyArray<PrimitiveValueExpressionType>
+  +sql: string,
+  +type: 'RAW_SQL',
+  +values: $ReadOnlyArray<PrimitiveValueExpressionType>
 |};
 
 export type ValueListSqlTokenType = {|
-  values: $ReadOnlyArray<PrimitiveValueExpressionType>,
-  type: 'VALUE_LIST'
+  +values: $ReadOnlyArray<PrimitiveValueExpressionType>,
+  +type: 'VALUE_LIST'
 |};
 
 export type TupleSqlTokenType = {|
-  values: $ReadOnlyArray<PrimitiveValueExpressionType>,
-  type: 'TUPLE'
+  +values: $ReadOnlyArray<PrimitiveValueExpressionType>,
+  +type: 'TUPLE'
 |};
 
 export type TupleListSqlTokenType = {|
-  tuples: $ReadOnlyArray<$ReadOnlyArray<PrimitiveValueExpressionType>>,
-  type: 'TUPLE_LIST'
+  +tuples: $ReadOnlyArray<$ReadOnlyArray<PrimitiveValueExpressionType>>,
+  +type: 'TUPLE_LIST'
 |};
 
+export type UnnestListSqlTokenType = {|
+  +aliasNames: $ReadOnlyArray<string> | null,
+  +columnTypes: $ReadOnlyArray<string>,
+  +tuples: $ReadOnlyArray<$ReadOnlyArray<PrimitiveValueExpressionType>>,
+  +type: 'UNNEST_LIST'
+|};
+
 export type PrimitiveValueExpressionType = string | number | boolean | null;
@@ -168,3 +175,4 @@
   TupleSqlTokenType |
-  TupleListSqlTokenType;
+  TupleListSqlTokenType |
+  UnnestListSqlTokenType;
 
@@ -171,0 +179,0 @@ export type TaggledTemplateLiteralInvocationType = {|

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

261408

increased by7.05%

Lines of code

2705

increased by4.72%

Number of lines in readme file

1314

increased by11.45%

No dependency changes