massive-js issueshttps://gitlab.com/dmfay/massive-js/-/issues2023-01-12T13:44:40Zhttps://gitlab.com/dmfay/massive-js/-/issues/726Inconsistency between count and actual query results size when using join2023-01-12T13:44:40ZMaurĂcio JourdanInconsistency between count and actual query results size when using join## Summary
When setting a table A with two LEFT OUTER using a table B it returns inconsistent sizes when running a count and checking the length of the criteria. Moreover, the pagination returns wrong sizes or duplicates on every interac...## Summary
When setting a table A with two LEFT OUTER using a table B it returns inconsistent sizes when running a count and checking the length of the criteria. Moreover, the pagination returns wrong sizes or duplicates on every interaction.
## Example
### Database setup
```
// Table PARTICIPANTS contains 499 entries
table = dbClient.db[PARTICIPANTS].join({
[generalJoin]: {
type: 'LEFT OUTER',
relation: PARTICIPANTS_STATUS,
on: {
participant_id: 'id',
current: true,
status: ['open', 'prospecting', 'interviewing', 'offer_made', 'rejected'],
},
},
[hiredJoin]: {
type: 'LEFT OUTER',
relation: PARTICIPANTS_STATUS,
on: {
participant_id: 'id',
current: true,
status: 'hired',
},
},
});
```
### Code demonstrating the behavior
If I have, for example, two current status as 'prospecting', this happens:
```
console.log((await table.find(criteria)).length); // prints 499
console.log(await table.count(criteria)); // prints 500 (wrong)
```
## Expected behavior
Both console.log should return 499
## Actual behavior
The count returns +1 for each duplicated statuses
## Additional context
If I use a regular pagination (offset, limit) the result of each page also brings me inconsistent page sizes and items appearing again in other pages.https://gitlab.com/dmfay/massive-js/-/issues/741The join() on option only supports equality comparisons2023-01-05T09:17:14ZFederico MassaioliThe join() on option only supports equality comparisonsHi,
we discovered that if the `on` option of a join involves operators, e.g. like in
```
{
rel: {
on: {
owner_id: 'id',
'assignee_id <>': 'rel.owner_id'
},
....
}
}
```
The comparison operator is ignored a...Hi,
we discovered that if the `on` option of a join involves operators, e.g. like in
```
{
rel: {
on: {
owner_id: 'id',
'assignee_id <>': 'rel.owner_id'
},
....
}
}
```
The comparison operator is ignored and becomes an equality comparison in the generated SQL.
True, a where clause can be used (at list in this case) to workaround the issue, but sometimes being very selective in the `on` clauses makes queries much more efficient.https://gitlab.com/dmfay/massive-js/-/issues/739`.save()` throws error when provided with all fields2022-05-31T21:44:01ZMelvin Bagsik`.save()` throws error when provided with all fieldsHi. I just updated my package from v2 to v6 to make use of async but when I tried to use `tableName.save()` it throws an error `Must provide an object with all fields being modified and the primary key if updating` I already provided all...Hi. I just updated my package from v2 to v6 to make use of async but when I tried to use `tableName.save()` it throws an error `Must provide an object with all fields being modified and the primary key if updating` I already provided all the field and the primary key as well the other method works fine like `find`, `findOne`, and `insert`.
`
const record = await db.users.save({
id: 1,
first_name: 'test',
last_name: 'test',
timestamp: new Date()
});
`https://gitlab.com/dmfay/massive-js/-/issues/683As-Type Ordering With JSON Traversal2021-09-27T12:14:00ZKevin CrumAs-Type Ordering With JSON TraversalJSON traversal appears to always use 'as-text' operators. Therefore, I see only two ways to order a query by a non-text JSON field: (1) explicitly type cast or (2) use a custom expr. I would prefer to avoid these since (1) requires tra...JSON traversal appears to always use 'as-text' operators. Therefore, I see only two ways to order a query by a non-text JSON field: (1) explicitly type cast or (2) use a custom expr. I would prefer to avoid these since (1) requires tracking database field types in the application and (2) likely requires re-implementing the JSON traversal logic. Is there another way to order that I have missed or a plan to implement 'as-type' JSON operators?
Example in REPL:
```javascript
db.saveDocs('test', [{ total: 1 }, { total: 2 }, { total: 10 }])
db.test.findDoc({}, { order: [{ field: 'body.total', direction: 'DESC' }]}) // Result [2, 10, 1]
db.test.findDoc({}, { order: [{ field: 'total', direction: 'DESC' }], orderBody: true }) // Result [2, 10, 1]
db.test.findDoc({}, { order: [{ field: 'body.total', direction: 'DESC', type: 'int' }]}) // Works but I'd like to avoid
db.test.findDoc({}, { order: [{ expr: "body->'total'", direction: 'DESC' }]}) // Works but I'd like to avoid
```
I would be happy to write a PR if there is need/interest. Thank you for this project, it's great!https://gitlab.com/dmfay/massive-js/-/issues/694Can't destroy documents based on query object2021-09-27T12:14:00ZMitch JansCan't destroy documents based on query objectAccording to [the docs for destroy](https://massivejs.org/docs/persistence#destroy) it is possible to pass [universal options](https://massivejs.org/docs/options-objects#universal-options) to the `destroy` function. So Itried passing `{d...According to [the docs for destroy](https://massivejs.org/docs/persistence#destroy) it is possible to pass [universal options](https://massivejs.org/docs/options-objects#universal-options) to the `destroy` function. So Itried passing `{document: true}` to enable document handling for my destroy call:
```js
const collection = 'myTableName';
database[collection]
.destroy({'date <': '2019-10-01', 'status': 'O'}, {document: true})
.catch(/* error handling here */);
```
But that gets me `error: column date does not exist`
I also tried it wihtout the `{document: true}` but it gives the same result.
It is not entirely clear to me how I can use `destroy` in conjunction with a document table and there doesn't seem to be a `destroyDoc` function available. Is there any way to destroy items in a document table based on a query object? How should I go about this?
I am using node 12, Massivejs 6.1.0 and Postgres 11.https://gitlab.com/dmfay/massive-js/-/issues/703findOne(number) produces invalid query for tables with no PKEY2021-09-27T12:14:00ZBono SfindOne(number) produces invalid query for tables with no PKEYv6.1.1
Doing `findOne(1)` on a table without a primary key produces the following query:
`SELECT * FROM table_name WHERE LIMIT 1`v6.1.1
Doing `findOne(1)` on a table without a primary key produces the following query:
`SELECT * FROM table_name WHERE LIMIT 1`https://gitlab.com/dmfay/massive-js/-/issues/702PKEY not detected for read-only tables2021-09-27T12:13:59ZBono SPKEY not detected for read-only tablesv6.1.1
When the role used by massive only has SELECT permission (`r` when doing `\dp`) on a table, the primary key is not detected.
I haven't investigated in depth for now, but the following query (used when massive is initialized) doe...v6.1.1
When the role used by massive only has SELECT permission (`r` when doing `\dp`) on a table, the primary key is not detected.
I haven't investigated in depth for now, but the following query (used when massive is initialized) does not contain data for read-only tables:
```
SELECT
tc.table_schema,
tc.table_name,
array_agg(DISTINCT kc.column_name::text) FILTER (WHERE kc.column_name IS NOT NULL) AS columns
FROM
information_schema.table_constraints tc
LEFT OUTER JOIN information_schema.key_column_usage kc ON
kc.constraint_schema = tc.table_schema
AND kc.table_name = tc.table_name
AND kc.constraint_name = tc.constraint_name
WHERE
tc.constraint_type = 'PRIMARY KEY'
GROUP BY
tc.table_schema,
tc.table_name;
```
Granting INSERT to the role fixed the problem.https://gitlab.com/dmfay/massive-js/-/issues/709onConflictUpdate doesn't allow expressions2021-09-27T12:13:59ZmoonConflictUpdate doesn't allow expressions## Summary
Postgres unique indices support expressions (like `lower(name)`). It seems currently impossible to use such constraints in conjunction with massive's insert `onConflictUpdate` because it automatically puts quotes around the fi...## Summary
Postgres unique indices support expressions (like `lower(name)`). It seems currently impossible to use such constraints in conjunction with massive's insert `onConflictUpdate` because it automatically puts quotes around the fields.
## Example
### Database setup
```
CREATE TABLE things (
id SERIAL PRIMARY KEY,
stuff TEXT,
name TEXT
);
CREATE UNIQUE INDEX CONCURRENTLY stuff_name_idx
ON things (stuff, lower(name));
```
### Code demonstrating the behavior
```
db.things.insert({ stuff: 'stuff', name: 'thing' });
db.things.insert({ stuff: 'stuff', name: 'Thing' }, { onConflictUpdate: ['stuff', 'lower(name)'] });
```
## Expected behavior
A conflict causes the thing to be updated.
## Actual behavior
An error occurs, stating that `"lower(name)"` is not a column of the things table:
```
InternalServerError: column "lower(name)" does not exist
```
## Additional context
We were able to work around this by using this hack:
```
db.things.insert({ stuff: 'stuff', name: 'Thing' }, { onConflictUpdate: ['stuff", lower(name), "stuff'] });
```https://gitlab.com/dmfay/massive-js/-/issues/713Allow joins on body fields2021-09-27T12:13:59ZJohn Gerald Maagad AgbayaniAllow joins on body fieldsMassive JS does not support joins in the body level. It would be good to have this feature.
## Use case
### Schemas
Say I have the following schemas
#### users
```
{
id,
body: {
name,
age,
etc
}
}
```
#### transac...Massive JS does not support joins in the body level. It would be good to have this feature.
## Use case
### Schemas
Say I have the following schemas
#### users
```
{
id,
body: {
name,
age,
etc
}
}
```
#### transactions
```
{
id,
body: {
type,
user_id, // Refers to users.id
}
}
```
### Code
Example code usage that would allow body joins
#### 1
```
db.users.join({
transations: {
type: 'INNER',
on: {'body.user_id': 'id'}
}
}).find({})
```
#### 2
```
db.users.join({
transations: {
type: 'INNER',
on: {body: {user_id: 'id'}}
}
}).find({})
```https://gitlab.com/dmfay/massive-js/-/issues/706Allow adding "AS" alias to jsonb fields2021-09-27T12:13:59ZPizzaPartyIncAllow adding "AS" alias to jsonb fields## Summary
Currently, when you want to select a nested property from a jsonb object, you can add an alias to it only via "exprs" option.
## Example
### Database setup
```
CREATE TABLE things (
id SERIAL PRIMARY KEY,
notBody ...## Summary
Currently, when you want to select a nested property from a jsonb object, you can add an alias to it only via "exprs" option.
## Example
### Database setup
```
CREATE TABLE things (
id SERIAL PRIMARY KEY,
notBody jsonb
);
```
### Code demonstrating the behavior
```ts
db.things.find(
{
'notBody.firstNestedObject.secondNestedObject.someProperty !=': 'undesirable_value',
},
{
exprs: {
someProperty: `"notBody"#>>'{firstNestedObject,secondNestedObject,someProperty}'`,
},
},
);
```
This produces an SQL request like this one (no problem with the request, this is expected):
```sql
SELECT "notBody"#>>'{firstNestedObject,secondNestedObject,someProperty }' AS "someProperty" FROM "things"
WHERE "notBody"#>>'{firstNestedObject,secondNestedObject,someProperty}' <> 'undesirable_value'
```
## Expected behavior
Maybe something like this should produce the same SQL result. Otherwise you should always think about how will Massive transform criteria property and use that in exprs, so using similar access to nested properties in both criteria and options.fields seems reasonable.
```ts
db.things.find(
{
'notBody.firstNestedObject.secondNestedObject.someProperty !=': 'undesirable_value',
},
{
fields: [
'notBody.firstNestedObject.secondNestedObject.someProperty AS someProperty',
],
},
);
```
## Additional info
Currently example from "Expected behavior" will make a conversion from "notBody.firstNestedObject.secondNestedObject.someProperty" to ""notBody"#>>'{firstNestedObject,secondNestedObject,someProperty }'", but "AS" will be ignored, so column will be loaded under the name of "?column?".https://gitlab.com/dmfay/massive-js/-/issues/729parse-key/lex incorrectly matches a single `:` as a cast2021-09-27T12:13:59Zpaul-duffyparse-key/lex incorrectly matches a single `:` as a castA bug was introduced in 6.5 when allowing for column references on the rhs of the criteria. In my use case, values start with text that also match a column name. The value also includes a `:` which the lex method is treating as a cast ev...A bug was introduced in 6.5 when allowing for column references on the rhs of the criteria. In my use case, values start with text that also match a column name. The value also includes a `:` which the lex method is treating as a cast even though it's not `::`.
# Steps to reproduce
1. Create table `A` with columns [ `a_id`, `foo`]
2. Create table `B` with columns [ `b_id`, `bar`]
3. Execute a find on a join between `A` and `B`.
```
join({...}).find({
foo: "foo:1234:abc"
})
```
# Result
Invalid SQL result: `...WHERE "A"."foo" = "A"."foo"::1234abc`
# Potential Fix
Update parse-key.js lex method. Replace the case statement for ':' with
```
case ':':
// cast; new token, but we ignore the : characters since we only care
// about type
if (_.last(buffer) === ':') {
buffer.pop();
if (!hasCast) {
hasCast = true;
jsonAsText = true; // Explicit casts must use as-text operators
buffer = tokens[tokens.push([]) - 1];
}
} else {
buffer.push(char);
}
break;
```https://gitlab.com/dmfay/massive-js/-/issues/732Problem using JOIN with UUID ids (uuid_generate_v4)2021-09-27T12:13:59ZDaniel HenriqueProblem using JOIN with UUID ids (uuid_generate_v4)## Summary
When using the id as a UUID (uuid_generate_v4) and try to make a JOIN, got an error:
Cannot create property '_2677701375' on string '625429df-e9a9-490b-b0ec-3f9add962804'.
This fixes this problem, checking the object before bu...## Summary
When using the id as a UUID (uuid_generate_v4) and try to make a JOIN, got an error:
Cannot create property '_2677701375' on string '625429df-e9a9-490b-b0ec-3f9add962804'.
This fixes this problem, checking the object before build it
### Database setup
```
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE public.room (
id uuid NOT NULL DEFAULT uuid_generate_v4(),
CONSTRAINT room_pk PRIMARY KEY (id)
);
CREATE TABLE public.leaderboard (
id uuid NOT NULL DEFAULT uuid_generate_v4(),
room uuid NOT NULL,
CONSTRAINT leaderboard_pk PRIMARY KEY (id),
CONSTRAINT leaderboard_room_fk FOREIGN KEY (room) REFERENCES room(id)
);
```
### Code demonstrating the behavior
```
db.leaderboard.join({
room: {
type: 'INNER',
relation: 'room',
pk: 'id',
on: { id: 'leaderboard.room' },
},
}).find({});
```
## Expected behavior
Return the leaderboards and their room associated populated.
## Actual behavior
Receives an error:
Cannot create property '_2677701375' on string '625429df-e9a9-490b-b0ec-3f9add962804'.
## Additional context
I fixed this problem by debugging and updating the "decompose.js" file:
```
const uuid = require('uuid');
...
const isUUID = uuid.validate(obj[strid][c]);
const descendant = build(obj[strid][c] && !isUUID ? obj[strid][c] : {}, objSchema[c]);
```https://gitlab.com/dmfay/massive-js/-/issues/735findOne passing primary key with join returns array2021-09-27T12:13:59ZDarrin GrovefindOne passing primary key with join returns array## Summary
findOne returns an array when a primary key is passed and it's called with a join
## Example
### Code demonstrating the behavior
```
db.orders.findOne({id: orderId}); // returns an object
db.orders.findOne(orderId); // re...## Summary
findOne returns an array when a primary key is passed and it's called with a join
## Example
### Code demonstrating the behavior
```
db.orders.findOne({id: orderId}); // returns an object
db.orders.findOne(orderId); // returns an object
db.orders
.join({
orderLines: {
relation: 'orderLines',
type: 'LEFT OUTER',
},
})
.findOne({id: orderId}); // returns an object
db.orders
.join({
orderLines: {
relation: 'orderLines',
type: 'LEFT OUTER',
},
})
.findOne(orderId); // returns an array
```
## Expected behavior
Passing a primary key with a join should return an object.
Passing the primary key should have the same behavior as passing a criteria object with id.
## Additional context
Using Massive 6.9.0https://gitlab.com/dmfay/massive-js/-/issues/736Passing undefined to update updates all rows2021-07-17T18:54:59ZDarrin GrovePassing undefined to update updates all rows## Summary
Passing undefined to update updates all rows
## Example
### Code demonstrating the behavior
```
// This updates all rows with the new values
const id = undefined;
await this.db.users.update(id, {
firstName: 'First',
lastN...## Summary
Passing undefined to update updates all rows
## Example
### Code demonstrating the behavior
```
// This updates all rows with the new values
const id = undefined;
await this.db.users.update(id, {
firstName: 'First',
lastName: 'Last',
});
// This updates nothing
const id = undefined;
await this.db.users.update(
{id},
{
firstName: 'First1',
lastName: 'Last1',
},
);
```
## Expected behavior
Nothing should update in both cases
## Actual behavior
firstName and lastName are updated on all rows!
## Additional context
This seems like an extremely dangerous result for a minor miss.https://gitlab.com/dmfay/massive-js/-/issues/734How to write expressions in update fields?2021-05-13T23:08:46ZDan HultonHow to write expressions in update fields?The query I want to write is akin to:
`UPDATE "users" SET "login_count" = "login_count" + 1 WHERE "id" = 1;`
I cannot for the life of me find a way to use an expression in the changes object. I'm sure I can just do this with `db.query`...The query I want to write is akin to:
`UPDATE "users" SET "login_count" = "login_count" + 1 WHERE "id" = 1;`
I cannot for the life of me find a way to use an expression in the changes object. I'm sure I can just do this with `db.query`, but it feels like pretty basic functionality to not be part of the query builder, and I feel silly for not being able to figure out how to do it.
If this isn't something that can be done yet, I feel a little less silly, but I'd be happy to look into creating an MR to add it. Yes, this functionality introduces the possibility of SQL injection if used carelessly, but Massive already supports the `expr` field in query objects with appropriate warnings, and I figure something similar here could be sufficient.https://gitlab.com/dmfay/massive-js/-/issues/733Documentation Request: Clarification on How Functions Are called in the backg...2021-04-28T02:00:10ZOsman SekerlenDocumentation Request: Clarification on How Functions Are called in the background.Hi,
I was looking through the documentation however I couldn't see a detailed explanation on how the functions are used on my postgres db. It shows that it defines a function with the same name, however **I don't know if any extra js co...Hi,
I was looking through the documentation however I couldn't see a detailed explanation on how the functions are used on my postgres db. It shows that it defines a function with the same name, however **I don't know if any extra js code is executed, if it executes on the db or converted to js.** After digging through the issues I found this answer to a functions question: https://gitlab.com/dmfay/massive-js/-/issues/271#note_145756958
In the issue, OP is asking why his function doesn't work.
1. Places his function-create code at _db/scripts/my_test.sql_
1. Function does not exist in the database before massive initializes.
1. Initializes massive such as:
```js
massive.connectSync({
connectionString,
scripts: './db/scripts'
});
```
...and @dmfay goes on to explain it this way:
> Your script exists as a function in Massive, which, when you call it, executes the SQL in the script - namely, it creates the function in the database. It does *not* also load the new function into Massive. The first time you make the call to `my_test` massive executes that SQL, and a function is created (or recreated) in the back end. Since Massive loads up database-defined functions at load, it is *not* loaded as a property of Massive this way.
Having limited knowledge on postgres and massive, the approach of massive was not very clear to me. I learned later that it was not an ORM, but that's not very clear on the docs page for [Database Functions and Procedures](https://massivejs.org/docs/functions-and-scripts#database-functions-and-procedures)https://gitlab.com/dmfay/massive-js/-/issues/271Functions in sql files not working as expected2021-04-27T14:43:57ZMarius Skaar LudvigsenFunctions in sql files not working as expectedHi,
I have the following sql function:
```
CREATE OR REPLACE FUNCTION my_test (int) ...Hi,
I have the following sql function:
```
CREATE OR REPLACE FUNCTION my_test (int)
RETURNS int AS $$
BEGIN
IF $1 = 1 THEN
RETURN 10;
ELSE
RETURN 20;
END IF;
END;
$$ LANGUAGE plpgsql;
```
Which i expect to be able to run like this:
```
db.my_test(1,(err, res)=>{
// Do something
});
```
But this returns the following error:
```
Error: bind message supplies 1 parameters, but prepared statement "" requires 0
at DB.query (/home/marius/vimond/vcc-curation/node_modules/massive/lib/runner.js:22:11)
at Executable.invoke (/home/marius/vimond/vcc-curation/node_modules/massive/lib/executable.js:54:13)
at rootObject.(anonymous function) [as my_test] (/home/marius/vimond/vcc-curation/node_modules/massive/index.js:395:29)
at Object.<anonymous> (/home/marius/vimond/vcc-curation/build/server/app.js:17:17)
at Module._compile (module.js:541:32)
at Object.Module._extensions..js (module.js:550:10)
at Module.load (module.js:458:32)
at tryModuleLoad (module.js:417:12)
at Function.Module._load (module.js:409:3)
at Function.Module.runMain (module.js:575:10)
at startup (node.js:160:18)
at node.js:449:3
```
Currently I do this as a workaround:
```
massiveInstance.my_test((errr res)=>{
massiveInstance.loadFunctions((err, res)=>{
db.my_test(1,(err, res)=>{
// Success
});
});
});
```
I guess I'm doing something wrong or this is not supported. So I would be very happy to get some feedback on what I might be doing wrong.
Marius
https://gitlab.com/dmfay/massive-js/-/issues/730Does Massive support node 14 LTS2021-04-24T20:03:53Zshobhit vermaDoes Massive support node 14 LTSAfter updating the version of node to 14 LTS Massive stopped responding.After updating the version of node to 14 LTS Massive stopped responding.https://gitlab.com/dmfay/massive-js/-/issues/728JSONB key exists (`?`) queries2021-02-27T20:30:22ZAdamJSONB key exists (`?`) queriesWould you consider supporting JSONB key exists (`?`) queries.
In: lib/statement/operations.js
``` '?': {operator: '?'}, ```
```
'{"a":1, "b":2}'::jsonb ? 'b'
```Would you consider supporting JSONB key exists (`?`) queries.
In: lib/statement/operations.js
``` '?': {operator: '?'}, ```
```
'{"a":1, "b":2}'::jsonb ? 'b'
```https://gitlab.com/dmfay/massive-js/-/issues/727[Breaking Change] Upgrade from 6.6.0 to 6.6.4 seems to break because dependency2021-01-28T04:46:58Zgoliatone[Breaking Change] Upgrade from 6.6.0 to 6.6.4 seems to break because dependencyA recent build on my project failed due to an error from which I pulled this snippet of text:
```
MurmurHashV3 ReferenceError: TextEncoder is not defined
```
Looking around I saw that I had massive pined at `~6.6.0` and installed the la...A recent build on my project failed due to an error from which I pulled this snippet of text:
```
MurmurHashV3 ReferenceError: TextEncoder is not defined
```
Looking around I saw that I had massive pined at `~6.6.0` and installed the latest release `6.6.4`. One of the changes in that update was to upgrade the murmurhash lib from v1 to v2.
Looking at [their source code](https://github.com/perezd/node-murmurhash/blob/7a15d5e8244ccbfcab768e01e217e15322f1ffaa/murmurhash.js#L4) they import TextEncoder which was added to [node on V11](https://github.com/nodejs/node/blob/master/doc/changelogs/CHANGELOG_V11.md) and the application in question is on node `v10.14.2`.
Just a heads up that you might want to either provide a shim or maybe do a mayor release?
Thank you for your work, the library is great!