How can I do Multiple Queries (build out a DB) in Node.JS?

Question

Really new to node.js and relatively new to PostgreSQL. For some reason this only creates the first table, not the second. I am probably totally doing this wrong. I can't seem to find any code examples that are similar enough to extrapolate an answer from. I want to create two tables. It seems I can only run one query and that's it. No more. It's suggested that my connection ends before the second can be executed. I don't know why. I can't find a single example of doing this in a dozen different Google searches.

var client = new pg.Client(connectionString);
client.connect();
var query = client.query('CREATE TABLE preferences(id SERIAL PRIMARY KEY, food VARCHAR(40) not null, preferred BOOLEAN)');
client.query('CREATE TABLE foods(id SERIAL PRIMARY KEY, food VARCHAR(40) not null, type VARCHAR(40) not null, spicy BOOLEAN, spiciness VARCHAR(10)');
query.on('end', function() { client.end(); });

I had doubts that I could just do another client.query and have it all execute at the end. I took this example out of a tutorial but the second table I added. So I'm just playing with it and trying to learn here with this test.

Just to state my ultimate end goal: I wanna ultimately create a whole script for building out the database completely full of all the necessary tables and data.

Error I get no matter what I try:

$ node models/database.js events.js:85 throw er; // Unhandled 'error' event ^ error: syntax error at end of input at Connection.parseE (~/Documents/test01/node_modules/pg/lib/connection.js:534:11) at Connection.parseMessage (~/Documents/test01/node_modules/pg/lib/connection.js:361:17) at Socket. (~/Documents/test01/node_modules/pg/lib/connection.js:105:22) at Socket.emit (events.js:107:17) at readableAddChunk (_stream_readable.js:163:16) at Socket.Readable.push (_stream_readable.js:126:10) at TCP.onread (net.js:538:20)

You only waited for one of them to end, not the other, so you likely ended the connection prematurely. — user400654
– user400654, Commented May 29, 2015 at 19:40
From what I gathered (tutorials out there are awful) the query.on('end'... part ends the connection to the db. It gets throw er; // Unhandled 'error' event in the response. I'm still really confused at the moment. — Nathan McKaskle
– Nathan McKaskle, Commented May 29, 2015 at 19:46
query.on(type, handler) adds an event handler for the passed in type, "end" in this case. When that event occurs for the query you attached it to, the handler fires. Therefore, the handler fires when the first query is complete, which is likely before the second is complete. — user400654
– user400654, Commented May 29, 2015 at 19:48

vitaly-t · Accepted Answer · 2017-04-01 17:19:49Z

4

The best way is to place all your requests into a transaction and execute it within the same connection.

Here's how you can do this with the help of pg-promise:

var pgp = require('pg-promise')(/*options*/);
var db = pgp(connection);

db.tx(t => {
        return t.batch([
            t.none('CREATE TABLE preferences(id SERIAL, etc...'),
            t.none('CREATE TABLE foods(id SERIAL PRIMARY KEY, etc...')
        ]);
    })
    .then(data => {
        // success;
    })
    .catch(error => {
        console.log(error); // print error;
    });

Better yet, is to place all SQL into external SQL files, for which the library provides automatic support.

edited Apr 1, 2017 at 17:19

answered Jun 2, 2015 at 18:24

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SneakyMummin Over a year ago

Yes, your library is awesome ;)

user400654 · Accepted Answer · 2015-06-04 17:35:30Z

2

You are trying to perform two asynchronous actions, so you'll need to wait until both actions are complete before you continue. One way of doing this is with a count variable.

var client = new pg.Client(connectionString);
client.connect();
var query1 = client.query('CREATE TABLE preferences(id SERIAL PRIMARY KEY, food VARCHAR(40) not null, preferred BOOLEAN)');
var query2 = client.query('CREATE TABLE foods(id SERIAL PRIMARY KEY, food VARCHAR(40) not null, type VARCHAR(40) not null, spicy BOOLEAN, spiciness VARCHAR(10))');
var count = 2;

function endHandler () {
   count--; // decrement count by 1
   if (count === 0) {
       // two queries have ended, lets close the connection.
       client.end();
   }
}

query1.on('end', endHandler);
query2.on('end', endHandler);

Note that in this case you could likely just combine the two statements into one, assuming postgres supports that and that functionality is enabled in your system.

client.query('CREATE TABLE preferences(id SERIAL PRIMARY KEY, food VARCHAR(40) not null, preferred BOOLEAN);CREATE TABLE foods(id SERIAL PRIMARY KEY, food VARCHAR(40) not null, type VARCHAR(40) not null, spicy BOOLEAN, spiciness VARCHAR(10));')

edited Jun 4, 2015 at 17:35

answered May 29, 2015 at 20:49

user400654

95.1k16 gold badges168 silver badges188 bronze badges

11 Comments

Nathan McKaskle Over a year ago

(count === 0) Doesn't three equals signs refer to an equivalence in type?

Nathan McKaskle Over a year ago

Yeah this one doesn't seem to work, same error: events.js:85 throw er; // Unhandled 'error' event

user400654 Over a year ago

three = is type and value. i generally always use 3.

user400654 Over a year ago

can you give the complete stack trace?

Nathan McKaskle Over a year ago

$ node models/database.js events.js:85 throw er; // Unhandled 'error' event ^ error: syntax error at end of input at Connection.parseE (~/Documents/test01/node_modules/pg/lib/connection.js:534:11) at Connection.parseMessage (~/Documents/test01/node_modules/pg/lib/connection.js:361:17) at Socket.<anonymous> (~/Documents/test01/node_modules/pg/lib/connection.js:105:22) at Socket.emit (events.js:107:17) at readableAddChunk (_stream_readable.js:163:16) at Socket.Readable.push (_stream_readable.js:126:10) at TCP.onread (net.js:538:20)

|

Gepser Hoil · Accepted Answer · 2015-05-29 21:05:30Z

1

You need to manage multiple 'query' variables. In your case the code could look like this:

    var client = new pg.Client(connectionString);
        client.connect();
    var queryPreferences = client.query('CREATE TABLE preferences(id SERIAL PRIMARY KEY, food VARCHAR(40) not null, preferred BOOLEAN)');

    var foods = function() { 
        var queryFoods = client.query('CREATE TABLE foods(id SERIAL PRIMARY KEY, food VARCHAR(40) not null, type VARCHAR(40) not null, spicy BOOLEAN, spiciness VARCHAR(10)');
        queryFoods.on('end', function() { client.end(); });
    }

    queryPreferences.on('end', foods);

In this way you are running the second query until you finish the first one and then you are closing the connection.

edited May 29, 2015 at 21:05

answered May 29, 2015 at 20:50

Gepser Hoil

4,2765 gold badges26 silver badges36 bronze badges

6 Comments

user400654 Over a year ago

One thing i'm unsure of with your answer is whether or not the 'end' event works similar to a promise, in that if the end event has already occurred, will it immediately trigger any new events, or will it just do nothing because no event has happened since the handler was defined. however, in this case, i don't think it will matter because client is likely sending the requests in series rather than parallel, or the db will respond in series rather than parallel.

Gepser Hoil Over a year ago

Thanks @KevinB and yeah, that was the point. Sending requests in parallel so all you have to worry about is closing the connection at the end of the last request. Note that the second event end it's subscribed to the queryFoods emitter.

user400654 Over a year ago

Right, my concern was, what if queryFoods finishes first? but like i said, likely a non-issue due to this being a db connection, which generally are first in first out unless using separate connections.

Gepser Hoil Over a year ago

Oh, now I understand and I'm with you. Probably is not an issue but to be sure I edited my answer. I think that could give me some peace about the order of the requests.

Nathan McKaskle Over a year ago

Same with this one: events.js:85 throw er; // Unhandled 'error' event – Apparently I'm doing something pretty new here. Not sure if anybody's created a JS to create a bunch of tables. I wanna ultimately create a whole script for building out the database completely full of all the necessary tables and data.

|

Collectives™ on Stack Overflow

How can I do Multiple Queries (build out a DB) in Node.JS?

3 Answers 3

1 Comment

11 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

11 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related