dmfay / massive-js Goto Github PK

When multiple key constraints are defined for a table this error is thrown and the server will not start.

The issue is the query in ./lib/postgres.js in this.tableSQL. That query isn't specifically targeting primary keys.

https://www.npmjs.com/package/massive links to the C# version at https://github.com/robconery/massive

we really like jsonb but have had trouble using it with current drivers - Massive looks like an answer but we are stuck
so far the docs are mostly snippets - it's a bit confusing how it all works together in an app and we have several questions - here is some code for a real example - there are several questions in the code - can someone help and turn this into a working example thks

// in model.js
/* *** postgres setup
***************************************** */
var massive = require("massive");
var configure = function(done) {
var connectionString =
"postgres://ouruser:ourpassword@localhost/ourdb";
massive.connect({
connectionString : connectionString}, function(err, massiveInstance) {
app.set('db', massiveInstance);
done();
});
};

/* *** signups
***************************************** */
//-----
// first calling the insert(post) from express or hapi
// ?? need a wrapper function required like below ???

//in app.js
server.route({
method: 'POST',
path: '/api/signup',
handler: function(request, reply) {
insertSignup(request.payload)
.then(function(signup){
if (signup) {
//.....
}
})
}
})

// then in model.js where massive functions are
//first use a wrapper function to pass params from route
var signup = insertSignup(signup, function(err, result) {
// massive insert function
massive.connect({db : "myDb"}, function(err, db){
// ??? see above
db.savDoc(doc, {name: "signup"}, function(err,result){
// ??? what does result come back as ???
});
});
});

// now to list the signups
// in app.js
server.route({
method: 'GET',
path: '/api/signupsnew',
.....
handler: function(request, reply) {
getnewSignups(request.payload)
.then(function(date){
if (signup) {

// then in model using massive
/* in db/queries/getNewSignups.sql
'select * from signups where created_at < $1'
*/
massive.connect({db : "myDb"}, function(err, db){
// ??? see above - sould this be connectionString ??
db.getNewSignups("date", function(err,result){
return result
// ???? is this right ? ? is result a cursor ?
});
});

see https://gist.github.com/cwgabel for code, logs, ddl.

mysql package version is 0.95 ，but mainline of mysql is 2.1.x. 2.1.x fix more bugs like auto reconnect etc. Do you have plan update mysql package

Please consider to support sql server via it's official driver.

We should have a filter list for exclusive table loads. Can become verbose, but it could also be quite helpful. The whitelist would be an array of exact table names:

massive.connect({db : "myDB", whitelist : ["table1", "table2", "table3"]}...

Right now we read in the files and drop them right on the root namespace. Would be better to have namespacing by directory. We currently have glob installed but not sure how to handle this best.

db.users.first({email: userEmail},{columns: ['email', 'secret']}).execute(function(res){
// res contains includes all columns
})

Is anyone else not getting results when issuing a find command with arguments? I'm trying to connect to MySql. The following code returns an empty array:

db.Users.find({id: 1}).execute(function(err, users) {
if(err) {
console.log(err);
//callback({error: err}, null);
}
else {
console.log(users[0].username);
//callback(null, users);
}
});

However the following returns the user:
db.run('SELECT * FROM Users WHERE id = 1',function(err, users) {
if(err) {
console.log(err);
//callback({error: err}, null);
}
else {
console.log(users[0].username);
//callback(null, users);
}
});

Also note the following returns the entire collection of users:
db.Users.find().execute(function(err, users) {
if(err) {
console.log(err);
//callback({error: err}, null);
}
else {
console.log(users[0].username);
//callback(null, users);
}
});

The execute function in mysql.js ....

client.query(sql, params, function(err, results){

The sql variable value = SELECT * FROM Users WHERE "id" = 1

Notice that field name id is in quotations, I don't think it should.

Is there something I'm doing wrong? or is it a bug? I haven't been able to identity the bug yet but I will keep looking.

Cool library, I am investigating this and I want to know it's limitations. Ofcourse the web api layer can handle parameter checking; but is there anything or any plans for safety checks for injections into existing scripts in the db folder?

Massive uses path.parse in the walkSqlFiles helper function. However this method is only available in node 0.12 (and not in 0.10).

This means that the users can't use the "SQL Files as Functions" functionality. If I place a .sql file in the "db" directory, massive will crash immediately with:

TypeError: Object #<Object> has no method 'parse'

I’m curious as to how other developers using massive.js handle schema changes over time in their applications. Do you just add timestamped ad-hoc sql migration scripts or are you creating custom up/down migration tools around massive.js?

Btw, thanks @robconery for writing this. I’m wrapping up my first project using this library and it’s been fantastic.

Pull out the function assignment from the file walking bits.

@robconery Earlier on, you had thought you wanted to fie walk/Function assignment pulled out of index.js. You have since moved it back. Did you want to keep it there now, or did you still want to pull it out?

It's two functions now - easier to read/understand, but potentially dirtying up index.js with noise.

What say you?

Great work on this library.

I see some tests around testing a timestamptz field with a js Date object in the where clause. But there should also be some testing around the other date/time types supported by Postgres.

In particular, recognize that the JS Date object is internally based on a UTC values (ms since 1970-01-01 UTC), but most of the functions work with time in the local time zone of the computer where the code is running. That means it doesn't map well to Postgres's date type - which might better be expressed as in JS as just an ISO8601 string such as '2015-12-31'.

Also, in the test, you use the value new Date(1980,1,1) - which is a valid date, but it's February first, due to the Date constructor months running 0-11 rather than 1-12. I think you may have meant new Date(1980,0,1)

Other than the where clause, I also recommend testing the results when these types are expressed in the select clause.

"Thar be dragons" applies to date/time like nothing else, and JavaScript is a bad-apple in general in this area. I recommend some strong focus on this now, as it will ward off a flood of user complaints down the road.

Just kidding

Right now it's a bit of a dump ground, needs some cleaning

For a <100MB db, with <100 tables and approx. 1000 stored procedures (procedures are mostly coming from postgis, geometry, and other default functions, only 7% of the procedures are custom):

loadTables: average 750ms
loadFunctions: average 15000ms

Connection string used: postgres://user:pass@host:port/database?ssl=true

massive.connect is being called before each request, passing that string as connection string.
Therefore, massive.loadTables (index.js) is being called every time as well as massive.loadFunctions

This is performed from localhost, measured with console.time and console.timeEnd, and using pg.connect is almost instantaneous.
Am I missing something?

Extensions being used:
postgis_topology, postgis, plv8, plpgsql, topology, fuzzystrmatch

Requested as its own ticket from issue #26

I have a function in postgresql as:

CREATE OR REPLACE FUNCTION "getOrderByOperator"(IN opid bigint)

Now, MassiveJS gives me a db.getOrderByOperator() to call. But, when I call it, it errors with [error: function getorderbyoperator(unknown) does not exist]. True, it does not exist, but only because it's (I'm assuming) lowercasing the whole thing rather than using it as-is.

Is this fixed? Fixable? Is there a workaround short of constraining to snake casing or abandoning MassiveJS altogether?

On spin up we read all tables from the database and attach to the root object. I think it would be nice to allow for a specification for which namespaces we want (default is all). This can be done in a number of ways:

• A single value: "myNamespace"
• An array: ["myNamespace", "myOtherNamespace"]
• Or an explicit declaration of "all": "*"

The last is the default. We could then fashion the Table Query to pull only the namespace specified.

is that possible? :)

Thanks for the great library.
As per title, does massive support upsert?

Joins and Transactions, they are implemented?

How can I use them?

When configuring Massive in the context of an application, it may make sense to allow for a synchronous initial load option. We're after a cached instance of Massive from which we can query and otherwise access our precious data throughout the application life-cycle.

Folks might be tempted to initialize massive (using massive.connect()) each and every time they want to pull some data, which adds the initialization overhead every time. Also, it will be more clear what is happening during initialization to clearly retrieve a db instance at application load, as part of configuration.

It's not the "Node" way, but we should be able to do this when it makes sense:

var db = MassiveSync.loadSync({connectionString: constr});
// Set a reference to the massive instance on Express' app:
app.set('db', massiveInstance);

i am having issues with overloaded functions, i guess they are not working properly...
when calling a function that is overloaded, it tells me for example:
bind message supplies 4 parameters, but prepared statement "" requires 3

thats because there is also a function with same name and 3 parameters...

Currently we load all functions on init, and this should have some rules to it so we avoid loading things from extensions (like pg_crypto). This should be a LIKE filter:

massive.connect({db: "myDB", excludeFunctions : "xx_%"}

We should also have an option for ignoring function load altogether:

massive.connect({db: "myDB", functions : false}

We should have an extra parameter to pass into connect() which specifies a blacklist pattern. This would fit a LIKE query that we could pass off to the Table Query:

massive.connect({db : "myDb", blacklist : "%__%"}...
``

The readme says the REPL can be started with the command bin massive. But in Linux (ubuntu 14.4) this doesn't work (I get a No command 'bin' found, did you mean: ...). Am I missing something?

However I successfully started the REPL in the normal way: ./bin/massive.js

I tried (by accident) to override db.user.insert and got the error bind message supplies 1 parameters, but prepared statement requires 2.

The library should either treat it as an error if a built in method is overridden or behave as expected.

Would be nice to have the ability to stub out more complicated queries (such as adding a Full Text field with an index) in the REPL. In addition, FT query syntax and any other helpful stuff to get people off the ground writing SQL.

I must be doing something wrong but I cannot figure out from the documentation how to prevent node from crashing when an insert fails.

var db       = require('massive');

var databaseUrl = process.env.DATABASE_URL || 'postgres://localhost/dev'

db.connect(databaseUrl, function(err, db){
  user = {
    email: '[email protected]',
    crypted_password: 'password',
    secret: 'hello',
  }

  db.users.insert(user).execute(function(err, other){
    console.log(err);
  });
});

node crashes:

events.js:72
throw er; // Unhandled 'error' event

My insert violates a unique constraint

We have Jekyll right here in Github with Github docs, but I think there's probably a better solution - AND I don't like the docs living as a branch in the project itself.

There are solutions like MkDocs which seems pretty neat, and there's also some homespun stuff we could do (see gh-pages theme currently). I like the idea of razor-simple, all in one page references (like Backbone).

I don't think our stuff is so complicated as to need a huge site, but I do like lots of examples and a quick start.

I noticed that this project does not support Oracle.

Please support Oracle.

Also, is this project still being actively developed? The last commit was over 4 hours ago.

When using CamelCased DB object names, there are a number of instances where the SQL generated is not double quote escaped, causing errors to be thrown.

For example, using Chinook:

db > db.Artist.findOne(1, function(err, result) { console.log(err); });
undefined
db > { [error: relation "artist" does not exist]

Places where I've found this issue so far are the table name, the columns and the default order by (i.e. the pk).

I'm not sure whether to include the custom order by, since it's a supplied string, so the delimiting could be placed in the calling code. On the one hand that feels like a potential gotcha, on the other hand, the whole point is to take advantage of postgres' sql engine, which means playing by its rules.

I think I should have a fix for this ready for a PR soon, but I want to ensure it's properly tested first.

PS: This is the first time I've raised an issue & worked on the fix, so please be gentle!

Right now we're just dropping things from public right on to the massive instance which works for many situations. I think it would be better to account for schemas as well - as that's a good approach for keeping your DB uncluttered when it gets to be rather large.

There are several reasons why one might want to use schemas:

• To allow many users to use one database without interfering with each other.
• To organize database objects into logical groups to make them more manageable.
• Third-party applications can be put into separate schemas so they do not collide with the names of other objects.

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

Our current tableSql pulls the schema (I think) so we have access to it. If the schema is public I say we do what we've been doing (pop it onto the massive instance). If it's not, namespace thus:

db.my_schema.my_table.find()

Encountered some problems with massive's table.insert function.

It works fine if I call it thusly:

tbl.insert(solnRows); 

(solnRows contains 225 rows, all 225 are inserted ok.) But if I call it like this:

solnRows.forEach(function (r) { tbl.insert(r); });

it only ever inserts exactly 10 rows, whilst remaining disturbingly silent about what might have happened with the other 215.

Trying to get to the bottom of the issue, I constructed my own bastardized version of the massive
insert function:

let insert_rows = function(tblName, data, next) {
    if(!data) throw "insert should be called with data";
    if (!_.isArray(data)) { data = [data]; }

    var delimitedColumnNames = _.map(_.keys(data[0]), function(key){return util.format('"%s"', key);});
    var sql = util.format("INSERT INTO %s (%s) VALUES\n", tblName, delimitedColumnNames.join(", "));
    var rows = _.map(data, function(r) { return "(" + _.map(_.values(r),formatValue).join(", ") + ")"; });
    sql += rows.join(",\n");
    sql += " RETURNING *";
    //console.log(sql);
    pg.connect(url, function (err,client, done) {
        client.query(sql, null, function (err, res) {
            done();  // return client to pool
            if (next) next(err,res);
        });
    });
};

where formatValue basically calls pg's prepareValue, and then quotes everything but numbers, booleans and nulls.

For some reason, the above function can be called either way, and always inserts the full 225 rows.
So, as yet I've still not been able to come up with any explanation at to why the native massive insert should fail (silently !!! eeek....) the way it does. Anybody got a clue?

Thanks,

gary

We have a number of possible config params one might pass in during the call to massive.connect(). We may want to have massive look for a config file for these if .connect() is called either without arguments, or with a configFile property specifying the path.

Hi guys,

Found an issue with the table save command.

  db.users.save({userId: userId, userName: newName, updated: dt }, function (err,rslt) {

where userId is the pk of the users table. It yielded the following error:

"Unhandled rejection error: inconsistent types deduced for parameter $1"

and the generated sql was:

UPDATE "users" SET "userName" = $1, "updated" = $2
WHERE "userId" = $1 RETURNING *

which is clearly wrong. Am I doing something wrong? I found a simple fix in the source was to simply remove a line from the table save method:

if(this.containsPk(args)){
    var pk = this.primaryKeyName();
    //it's an update, use the id to run it
    var where = {};
    where[pk] = args[pk];

commented out the following line:

    //delete args[pk];
    this.update(args,where,next);
}else{

which seems like it should be ok - yes?

It seems like the code is written with the implicit assumption that the pk field will always be the last, which in my case was not true (alas!).

thanks.

Reference: https://github.com/grncdr/node-any-db

So we don't have to install both mysql and pg module and the connection, transaction and sql execution interface is normalized between pg and mysql.

Am I reading this correctly that every time I want to run a particular pg function, I call massive.connect(), then loadTables() and loadFunctions() for all the tables, functions, etc. in my postgresql db?

Pretty please? =)