How to use Node.js net.Socket to communicate with Postgresql database How to use terminal

Question

In the postgresql terminal, I can enter a command and get a response. For instance:

# create a new database database;

CREATE DATABASE

Above the input to the terminal is the command "create database newdatabase"; Database program responds with "CREATE DATABASE"

I am trying to do the same, but using node net.Socket. In other words, I want to issue commands for the postgresql server on which I am running on localhost: 5432 and get the response back through the socket.

My program successfully establishes a connection with the postgresql server and successfully deletes data in the kernel, but I never get a response (the data listener never starts). A new database is not created either.

I also quickly looked at what was happening on wirehark. It looks like the socket is getting set up. I see that my data is sent in clear text. The postgresql server then sends the ACK FIN ACK. I ACK FIN ACK and then the postgresql ACK server for the last time. Therefore, I know that the postgresql server does not send any data.

My question is: why does the postgresql server ignore the command that I am sending, and why the postgresql server does not send me any data, even if this data is simply an error.

const net = require('net'); const BlueBird = require('bluebird'); BlueBird.coroutine(function* () { var host = "127.0.0.1"; var port = "5432"; var idle_timeout = 10000; var MySocket = new net.Socket(); MySocket.setTimeout(idle_timeout); var data = yield new Promise( function resolver(resolve, reject) { MySocket.on('connect', function () { var flushed = MySocket.write("create database newdatabase;", "utf8"); console.log("Data flushed to kernel: " + flushed); }); MySocket.on('data', function (data) { console.log(data); resolve(data); }); MySocket.on('error', function (error) { reject(error); }); MySocket.connect(port, host); } ); return data; })() .then(function (data) { console.log(data); return data; }) .catch(function (error) { console.error(error); }) 

Answer 1

psql is a command line tool (REPL) that takes a command entered on one or more lines and parses it and sends it to the database.

PostgreSQL does not talk about the same text protocol over a socket on port 5432. More information about the PostgreSQL protocol can be found in the documentation .

Use the pg module to connect to Postgres using node and execute the queries:

 var pg = require('pg'); var conString = "postgres://username: password@localhost /database"; pg.connect(conString, function(err, client, done) { if(err) { return console.error('error fetching client from pool', err); } client.query('create database newdatabase', function(err, result) { console.log('CREATE DATABASE'); }); } 

Answer 2

Extending bolav's answer, with a simpler approach via pg-promise :

 var pgp = require('pg-promise')(/*options*/); var db = pgp("postgres://username: password@host :port/database"); db.query("CREATE DATABASE NewDatabase") .then(function (data) { // success }) .catch(function (error) { // error });