monstrous - npm Package Compare versions

Comparing version 0.3.1 to 0.4.0

CHANGELOG.md

@@ -5,2 +5,22 @@ # Changelog
 
+## [0.4.0](https://gitlab.com/monstrous/monstrous/compare/v0.3.1...v0.4.0) (2023-08-06)
+
+
+### ⚠ BREAKING CHANGES
+
+* projection $columns has been renamed
+
+### Features
+
+* project literals ([e1d1edb](https://gitlab.com/monstrous/monstrous/commit/e1d1edb9ff1487d36d42f019a81779bbf6d75467))
+* project nested relations as objects or arrays ([81de0ff](https://gitlab.com/monstrous/monstrous/commit/81de0ff91021613d82cc18f454cabad8d17096a0))
+
+
+### Bug Fixes
+
+* implement not-in ([6d971bb](https://gitlab.com/monstrous/monstrous/commit/6d971bb8ba92a23c818d1b3ca9214355a81d7c00))
+
+
+* $columns -> $fields ([d9209a6](https://gitlab.com/monstrous/monstrous/commit/d9209a6f7bbe5719bac32d93d2d4224791485131))
+
 ## [0.3.1](https://gitlab.com/monstrous/monstrous/compare/v0.3.0...v0.3.1) (2023-07-26)
@@ -7,0 +27,0 @@

lib/database/index.js

@@ -12,3 +12,3 @@ import * as url from 'url';
 import {$order} from '../statement/index.js';
-import Expr, {Tuple} from '../statement/expr.js';
+import Expr, {Tuple, Literal} from '../statement/expr.js';
 import Values from '../statement/values.js';
@@ -135,2 +135,6 @@ import {$join} from '../statement/join.js';
 
+  literal() {
+    return new Literal(...arguments);
+  }
+
   values(alias, ...objs) {
@@ -137,0 +141,0 @@ return new Values(alias, ...objs);

lib/database/relation.js

@@ -46,2 +46,6 @@ import Statement from '../statement/index.js';
 
+  get [Symbol.toStringTag]() {
+    return 'Relation';
+  }
+
   *[Symbol.iterator]() {
@@ -167,2 +171,6 @@ for (const column of this.qualified) {
 
+  get [Symbol.toStringTag]() {
+    return 'Alias';
+  }
+
   equals(schema, relname) {
@@ -169,0 +177,0 @@ return this.schema === schema && this.#relname === relname;

lib/statement/expr.js

@@ -73,1 +73,18 @@ import pgp from 'pg-promise';
 }
+
+export class Literal extends Expr {
+  // literal value (boolean, number, text) escaped and formatted; always
+  // quoted text, but Postgres has built-in conversions for bools, integers,
+  // and floating-point numbers
+  constructor() {
+    super(...arguments);
+  }
+
+  get [Symbol.toStringTag]() {
+    return 'Literal';
+  }
+
+  compile() {
+    return pgp.as.text(this.sql);
+  }
+}

lib/statement/predicate-tree.js

@@ -58,4 +58,6 @@ import pgp from 'pg-promise';
 
+      const sql_op = op === '=' ? 'in' : 'not in';
+
       return {
-        sql: pgp.as.format(`${lhs} in (${placeholders.join(', ')})`),
+        sql: pgp.as.format(`${lhs} ${sql_op} (${placeholders.join(', ')})`),
         params: rhs
@@ -62,0 +64,0 @@ }

lib/statement/project.js

@@ -67,2 +67,9 @@ import pgp from 'pg-promise';
 
+    if (Object.prototype.toString.call(proj) === '[object Relation]') {
+      return Projection.from({
+        $key: proj.primary_key,
+        $fields: proj.qualified
+      });
+    }
+
     for (const [name, val] of Object.entries(proj)) {
@@ -81,3 +88,3 @@ if (name === '$key') {
 
-      if (name === '$columns') {
+      if (name === '$fields') {
         created.#columns = val.reduce((acc, v) => {
@@ -101,3 +108,4 @@ if (v instanceof Expr) {
         case '[object Object]':
-          // object descendant
+        case '[object Relation]':
+          // object or relation descendant
           created.#projections[name] = Projection.from(proj[name]);
@@ -108,3 +116,3 @@ created.#projections[name].#to_array = false;
           if (Array.isArray(val)) {
-            // array descendant
+            // array descendant, can also be an array containing a relation!
             created.#projections[name] = Projection.from(proj[name][0]);
@@ -258,2 +266,4 @@ } else {
             case '[object Expr]':
+            case '[object Tuple]':
+            case '[object Literal]':
               obj[key][name] = row[prefix(path, val.alias)];
@@ -260,0 +270,0 @@ break;

package.json

 {
   "name": "monstrous",
-  "version": "0.3.1",
+  "version": "0.4.0",
   "description": "a lightweight SQL composer for Node.js and PostgreSQL",
@@ -5,0 +5,0 @@ "main": "index.js",

README.md

@@ -20,6 +20,6 @@ # monstrous
       $key: db.libraries.$id,
-      $columns: [...db.libraries],
+      $fields: [...db.libraries],
       authors: [{
         $key: db.authors.$id,
-        $columns: [
+        $fields: [
           db.authors.$name,
@@ -33,3 +33,3 @@ db.expr(
           $key: db.books.$id,
-          $columns: [...db.books]
+          $fields: [...db.books]
         }]
@@ -145,5 +145,5 @@ }]
 
-### exprs and tuples
+### exprs, tuples, and literals
 
-Exprs interpolate SQL text into a constructed query. They're a thin layer over [pg-promise's custom type formatting](https://vitaly-t.github.io/pg-promise/formatting.html#.format) and accept the SQL with an optional array of indexed or map of named parameters.
+Exprs and descendant types interpolate SQL text into a constructed query. They're a thin layer over [pg-promise's custom type formatting](https://vitaly-t.github.io/pg-promise/formatting.html#.format) and accept the SQL with an optional array of indexed or map of named parameters.
 
@@ -171,2 +171,8 @@ ```javascript
 
+Finally, literals allow the specification of text, numeric, or boolean values in projections:
+
+```javascript
+db.literal(`'this' will be properly escaped`)
+```
+
 **Tuples, and exprs provided as tuple arguments, are always interpolated directly into the final SQL statement; user input is not sanitized against SQL injection.**
@@ -234,19 +240,13 @@
   db.libraries
-    .join(
-      director,
-      {[director.$library_id]: db.libraries.$id}
-    )
+    .join(directors, {[directors.$library_id]: db.libraries.$id})
     .join(db.patrons)
     .project({
-      $key: db.libraries.$id,
-      $columns: [...db.libraries],
+      $key: db.libraries.primary_key,
+      $fields: [...db.libraries],
       director: { // override the subquery alias to singular
-        $key: director.$id,
-        name: director.$name,
-        tenure: db.expr(`now() - ${director.$hired_on}`)
+        $key: directors.$id,
+        name: directors.$name,
+        tenure: db.expr(`now() - ${directors.$hired_on}`)
       },
-      patrons: [{
-        $key: db.patrons.primary_key,
-        $columns: [...db.patrons]
-      }]
+      patrons: [db.patrons] // project entire patron records into an array
     })
@@ -258,20 +258,55 @@ );
 
-All three quantities -- the root record, `director`, and `patrons` -- specify a `$key` uniquely identifying a library, an employee, or a patron respectively. This is usually the table primary key, and indeed each table has a `primary_key` property as seen with `patrons`. Composite primary keys are supported with arrays.
+The right-hand side determines the output shape of the nested projection. An object representation yields an object, while a single-element array containing the same object representation yields an array.
 
-`libraries` and `patrons` also use the `$columns` field to specify fields without renaming them. `$columns` is processed similarly to the array form of the projection. In the example, the `director` instead specifies only a subset of fields one by one, and `tenure` is a raw SQL expression which will be interpolated into the query. As always, raw SQL is a potential vector for SQL injection; use it carefully!
+There are two special object keys being used here:
 
-`db.expr`s may be included in a `$columns` array, but must be aliased. In other words, the `director` could be constructed thus:
+- `$key` uniquely identifies a record-node at the current level of the projection tree
+- `$fields` specifies a list of fields to be included in this record-node without renaming, similar to the simple array form of projection
 
+`$key` is usually the table's primary key, and indeed each table has a `primary_key` property as seen with `libraries`. A composite `$key` may be specified explicitly with an array of constituent fields.
+
+`libraries` also uses the `$fields` property to specify relation columns without renaming them. Meanwhile, the `director` instead specifies only a subset of fields one by one, and `tenure` is a raw SQL expression which will be interpolated into the select list. Finally, `patrons` is articulated as an array without any changes or additions, by supplying the relation itself.
+
+These three variations on `patrons` are all equivalent:
+
 ```javascript
+{
+  patrons: [db.patrons]
+}
+
+{
+  patrons: [{
+    $key: db.patrons.primary_key, // or db.patrons.$patron_id!
+    $fields: [...db.patrons]
+  }]
+}
+
+{
+  patrons: [{
+    $key: db.patrons.$id,
+    name: db.patrons.$name,
+    address: db.patrons.$address
+  }]
+}
+```
+
+`db.expr`s may be included in a `$fields` array, but must be aliased. In other words, a list of library directors could be constructed thus:
+
+```javascript
 await db.select(
-  db.libraries.join(db.employees).project({
-    $key: db.employees.$id,
-    $columns: [
-      db.employees.$name,
-      db.expr(`now() - ${db.employees.$hired_on}`).as('tenure')
-    ]
-  })
+  db.libraries
+    .join(directors, {[directors.$library_id]: db.libraries.$id})
+    .project({
+      $key: directors.$id,
+      $fields: [
+        db.libraries.$name,
+        directors.$name,
+        db.expr(`now() - ${directors.$hired_on}`).as('tenure')
+      ]
+    })
 );
 ```
 
+As always, raw SQL is a potential vector for SQL injection; use it carefully!
+
 ### aliasing relations
@@ -278,0 +313,0 @@

No alert changes

Improved metrics

Total package byte prevSize

93520

increased by2.93%

Lines of code

1858

increased by1.81%

Number of lines in readme file

536

increased by6.99%

No dependency changes