CODEWITHSUNDEEP
node.jspostgresql
Nathan McKaskle
Nathan McKaskle

Reputation: 3063

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

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)

Upvotes: 3

Views: 9011

Answers (3)

vitaly-t
vitaly-t

Reputation: 25820

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.

Upvotes: 4

Kevin B
Kevin B

Reputation: 95031

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));')

Upvotes: 2

Gepser Hoil
Gepser Hoil

Reputation: 4226

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.

Upvotes: 1

Related Questions