Skip to content

Client

Jump to bottom Edit New page
dmaltzan edited this page Oct 21, 2013 · 66 revisions

Your main interface point with the PostgreSQL server. Client is used to create & dispatch queries to Postgres. Client also emits events from Postgres for 'LISTEN/NOTIFY' processing and non-critical error and notice messages from the server.

Constructors

note: Client instances created via the constructor do not participate in connection pooling. To take advantage of connection pooling (recommended) please use the pg object.

new Client(string url): Client

new Client(string domainSocketFolder): Client

Creates a new, unconnected client from a url based connection string postgres://user:password@host:port/database or from the location of a domain socket folder /tmp or /var/run/postgres.

Internally the connection string is parsed and a config object is created with the same defaults as outlined below. All parts of the connection string url are optional. This is handy for use in managed hosting like Heroku.

example

    var client = new Client('postgres://brian:mypassword@localhost:5432/dev');
    var client = new Client('postgres://brian@localhost/dev'); //will use defaults
    var client = new Client(process.env.DATABASE_URL); //something like this should get you running with heroku
    var client = new Client('/tmp');  //looks for the socket file /tmp/.s.PGSQL.5432

new Client(object config) : Client

Creates a new, unconnected instance of a Client configured via supplied configuration object.

parameters

  • object config: can contain any of the following optional properties
    • string user:
      • default value: process.env.USER
      • PostgreSQL user
    • string database:
      • default value: process.env.USER
      • database to use when connecting to PostgreSQL server
    • string password:
      • default value: null
      • user's password for PostgreSQL server
    • number port:
      • default value: 5432
      • port to use when connecting to PostgreSQL server
      • will support unix domain sockets in future
      • used to initialize underlying net.Stream()
    • string host:
      • default value: null
      • host address of PostgreSQL server
      • used to initialize underlying net.Stream()
    • bool ssl:
      • default value: false
      • whether to try SSL/TLS to connect to server

example

    var client = new Client({
      user: 'brianc',
      password: 'boom!',
      database: 'test',
      host: 'example.com',
      port: 5313
    });

Methods

connect(optional function callback) : null

Initializes Client's internal Connection object & net.Stream() instance. Starts communication with PostgreSQL server including password negotiation. If a callback is supplied it will be called with an instance of Error if an error was encountered during the connection procedure, otherwise it will be called with null for a single parameter after a connection to PostgreSQL server is established and the client is ready to dispatch queries.

note: Clients created via the pg#connect method are already connected and should not have their #connect method called.

end() : null

Immediately sends a termination message to the PostgreSQL server and closes the underlying net.Stream().

note: Clients created via the pg#connect method will be automatically disconnected or placed back into the connection pool and should not have their #end method called.

Simple queries

query(string text, optional function callback) : Query

Simply: Creates a query object, queues it for execution, and returns it.

In more detail: Adds a Query to the Client's internal query queue. The query is executed as a simple query within PostgresSQL, takes no parameters, and it is parsed, bound, executed, and all rows are streamed backed to the Client in one step within the PostgreSQL server. For more detailed information you can read the PostgreSQL protocol documentation.

parameters

  • string text: the query text
  • optional function callback: optionally provided function which will be passed the error object (if the query raises an error) or the entire result set buffered into memory. note: do not provide this function for large result sets unless you're okay with loading the entire result set into memory
  • function callback(object error, object result)
    • Called only if provided
    • buffers all rows into memory before calling
      • rows only buffered if callback is provided
      • can impact memory when buffering large result sets (i.e. do not provide a callback)
    • used as a shortcut instead of subscribing to the row query event
    • if passed, query will still raise the row and end events but will no longer raise the error event

    • parameters

      • object error:
        • null if there was no error
        • if PostgreSQL encountered an error during query execution, the message will be called here
      • object result:
        • and object containing the following properties:
          • array rows:
            • an array of all rows returned from the query
            • each row is equal to one object passed to the Query#row callback

examples

simple query without callback
    var client = new Client({user: 'brianc', database: 'test'});
    client.connect();
    //query is executed once connection is established and
    //PostgreSQL server is ready for a query
    var query = client.query("SELECT name FROM users");
    query.on('row', function(row) {
      console.log(row.name);
    });
    query.on('end', client.end.bind(client)); //disconnect client manually
simple query with optional row callback
    var client = new Client({user: 'brianc', database: 'test'});
    client.on('drain', client.end.bind(client)); //disconnect client when all queries are finished
    client.connect();

    //query is executed once connection is established and
    //PostgreSQL server is ready for a query
    var query = client.query("SELECT name FROM users", function(err, result) {
      console.log(result.rows[0].name);
    })

Parameterized Queries

query( object config, optional function callback) : Query

query(string queryText, array values, optional function callback): Query

Creates an unnamed query object, queues it for execution, and returns it.

If name is provided within the config object the query will be executed as a prepared statement. Otherwise, if values is provided within the config object the query will be executed as a parameterized query. If Otherwise, it will behave in the same manner as a simple query.

examples

paramatarized query with config object
    var client = new Client({user: 'brianc', database: 'test'});
    client.on('drain', client.end.bind(client)); //disconnect client when all queries are finished
    client.connect();

    var query = client.query({
      text: 'SELECT name FROM users WHERE email = $1',
      values: ['[email protected]']
    });

    query.on('row', function(row) {
      //do something w/ yer row data
      assert.equal('brianc', row.name);
    });
paramaterized query using string/array initialization
 
    var client = new Client({user: 'brianc', database: 'test'});
    client.on('drain', client.end.bind(client)); //disconnect client when all queries are finished
    client.connect();

     var again = client.query("SELECT name FROM users WHERE email = $1", ['[email protected]']);

    again.on('row', function(row) {
      //do something else
      assert.equal('brianc', row.name);
    });
paramaterized query with optional callback supplied
    
    var client = new Client({user: 'brianc', database: 'test'});
    client.on('drain', client.end.bind(client)); //disconnect client when all queries are finished
    client.connect();

    //object config method
    var queryConfig = {
      text: 'SELECT name FROM users WHERE email = $1',
      values: ['[email protected]']
    };
    client.query(queryConfig, function(err, result) {
      assert.equal('brianc', result.rows[0]);
    });

    //text/params method
    client.query('SELECT name FROM users WHERE email = $1', ['[email protected]'], function(err, result) {
      assert.equal('brianc', result.rows[0].name);
    });

Prepared statements

query(object config, optional function callback) : Query

(See Prepared Statements for a more detailed discussion of Prepared Statements in node-postgres.)

Creates a named query object, queues it for execution, and returns it.:

  • If and only if name is provided within the config object does query result in a prepared statement.
  • If text and name are provided within the config, the query will result in the creation of a prepared statement.
  • If value and name provided within the config, the prepared statement will be executed. (Note: if the prepared statement takes no parameters, use value:[].)

PostgreSQL server caches prepared statements by name on a per (postgres) session basis. Subsequent queries may refer to the prepared statement by name, and the PostgresQL server instance can skip the preparation step.

examples

prepared statement reuse
    var client = new Client({user: 'brianc', database: 'test'});
    client.on('drain', client.end.bind(client)); //disconnect client when all queries are finished
    client.connect();

    var first = client.query({
      text: "SELECT email FROM users WHERE name = $1",
      values: ['brianc'],
      name: 'email from name'
    });
    first.on('row', function(row) {
      assert.equal("[email protected]", row.email);
    });

    var second = client.query({
      name: 'email from name',
      values: ['brianc']
    });
    second.on('row', function(row) {
      assert.equal("[email protected]", row.email);
    });

    //can still supply a callback method
    var third = client.query({name: 'email from name', values: ['brianc']}, function(err, result) {
      assert.equal('[email protected]', result.rows[0].email);
    });

parameters

  • object config: can contain any of the following optional properties
    • string text:
      • The text of the query
      • example: select name from user where email = $1
    • string name:
      • The name of the prepared statement
      • Can be used to reference the same statement again later and is used internally to cache and skip the preparation step
    • array values:
  • optional function callback: callback function
    • function callback(object error, object result)
      • Called only if provided
      • buffers all rows into memory before calling
        • rows only buffered if callback is provided
        • can impact memory when buffering large result sets (i.e. do not provide a callback)
      • used as a shortcut instead of subscribing to the row query event
      • if passed, query will still raise the row and end events but will no longer raise the error event

      • parameters

        • object error:
          • null if there was no error
          • if PostgreSQL encountered an error during query execution, the message will be called here
        • object result:
          • and object containing the following properties:
            • array rows:
              • an array of all rows returned from the query
              • each row is equal to one object passed to the Query#row callback

Bulk data load

Bulk data load to or from database server are implemented by use of COPY FROM STDIN and COPY TO STDOUT queries (postgressql's documentation). This queries works with data in different way comparing to SELECT/INSERT/UPDATE.

  • Data from client to server is transferred through special channel, instead of being placed in query directly
  • Data from server is returned to client through special channel.
  • Data transferred to or from server is not structured, it's just text (formatted as CSV, or something like)

All this makes inconvenient to use query method, so pair of methods exists: copyFrom, copyTo. Both of them takes query text as argument and returns objects, that has interface of Stream. Stream is used as channel to transfer data to and from server, handler errors, and signal/be signaled about data ending

####copyFrom(string queryText):WritableStream

Method intended for loading data to server. It sends queryText to server. Query has to be valid COPY FROM statement. Method returns instance of WritableStream. It is implemented in lib/copystream.js and behaves like normal WritableStream. While bulk data load all interactions with database server are made using it.

  • To send data to server WritableStream.write method has to be used.
  • To notify server that all data has been sent, stream has to be closed with WritabkeStream.end method.
  • On error 'error' event is emitted on stream.
  • On successful end 'close' event is emitted on stream.

Server expects from client sequence of rows encoded in CSV-like text. Rows can be sent in any convenient way (by chunks of any size). It's not required, to match end of row and end of chunk.

Example:

var client = new Client({user: 'brianc', database: 'test'});
client.connect();
var stream = client.copyFrom("COPY names (user_name, age) FROM STDIN WITH CSV");
stream.on('close', function () {
  console.log("Data inserted sucessfully");
});
stream.on('error', function (error) {
  console.log("Sorry, error happens", error);
});
stream.write("user1,10\n");
//it's possible to combine more than one row in one write operation
stream.write("user2,20\nuser3,30\n");
//it's not required to match row ending to write operation
stream.write("user4");
stream.write(",40\nuser5,50\n");
//tell the sever that we've sent all data we have
stream.end();

####copyTo(string queryText):ReadableStream

Method intended to get data from database server. Its first and only argument is query text - COPY TO STDIN statement. As result, method returns ReadableStream (implemented in lib/copystream.js) with standard interface (ReadableStream). All interactions with server are done with use of this stream.

  • When error occurs, 'error' event is emitted.
  • When after last row 'end' event is emitted.
  • When chunk of data is received 'data' event is emitted.

Server sends row by row, so normally one row produces one 'data' event. But, pause/resume calls may break this rule, making ReadableStream to buffer more than one row in its internals. This also may cause program to require large chunk of memory (there is no way to ask the postgres server to pause sending data, so the best thing, that can be done by stream after calling pause, is to buffer data in its internal buffer)

Warning: It's impossible to stop data stream from server, until it ends (the only way is to cancel query, that is not atomic operation, and some more data chunks may be received after sending cancel message to sever), so if you decide, that you don't need no more data -- just ignore it until the end.

Example:

var client = new Client({user: 'brianc', database: 'test'});
var rows = new Buffer(0);
var stream = client.copyTo("COPY names (user_name, age) TO STDIN WITH CSV");
stream.on('end', function () {
  console.log("Data received sucessfully", rows.toString().split('\n'));
});
stream.on('error', function (error) {
  console.log("Sorry, error happens", error);
});
stream.on('data', function (chunk) {
  //collect rows in array.
  rows = Buffer.concat([rows, chunk]);
});

pauseDrain / resumeDrain

Pair of methods used to pause and resume Client from raising its drain event when its query queue is emptied. The drain event signifies the Client has no more pending queries and can safely be returned back to a client pool. Normally, drain will be emitted These methods come in handy for doing async work between queries or within a transaction and disabling the Client from alerting anyone it has gone idle.

example

var client = new Client(/*connection params*/);
client.connect();
client.on('drain', function() {
  console.log('client has drained');
});
client.pauseDrain();
client.query("SELECT NOW() AS when", function(err, result) {
  console.log("first");
  setTimeout(function() {
    client.query("SELECT NOW() AS when", function(err, result) {
      console.log("second");
      client.resumeDrain(); //now client will emit drain
    });
  }, 1000);
});
//output: 
// first
// second
// client has drained

Events

drain :

Raised when the internal query queue has been emptied and all queued queries have been executed. Useful for disconnecting the client after running an undetermined number of queries.

example
    var client = new Client({user: 'brianc', database: 'postgres'});
    client.connect();
    var users = client.query("select * from user");
    var superdoods = client.query("select * from superman");
    client.on('drain', client.end.bind(client));
    //carry on doing whatever it was you wanted with the query results once they return
    users.on('row', function(row){ ...... });

error : object error

Raised when the client recieves an error message from PostgreSQL or when the underlying stream raises an error. The single parameter passed to the listener will be the error message or error object.

example
    var client = new Client({user: 'not a valid user name', database: 'postgres'});
    client.connect();
    client.on('error', function(error) {
      console.log(error);
    });

notification : object message

Used for "LISTEN/NOTIFY" interactions. You can do some fun pub-sub style stuff with this.

example
   var client1 = new Client(...)
   var client2 = new Client(...)
   client1.connect();
   client2.connect();
   client1.on('notification', function(msg) {
     console.log(msg.channel);  //outputs 'boom'
     client1.end();
   });
   client1.query("LISTEN boom");
   //need to let the first query actually complete
   //client1 will remain listening to channel 'boom' until its 'end' is called
   setTimeout(function() {
      client2.query("NOTIFY boom", function() {
        client2.end();
      });
   }, 1000);

notice : object notice

Emitted from PostgreSQL server when non-critical events happen. Libpq printf's these out to stdout if the behavior is not overridden. Yucky. Thankfully node-postgres overrides the default behavior and emits an event (instead of printing to stdout) on the client which received the notice event.

example
    var client = new Client(...)
    client.on('notice', function(msg) {
      console.log("notice: %j", msg);
    });
    //create a table with an id will cause a notice about creating an implicit seq or something like that...
    client.query('create temp table boom(id serial, size integer)');
    client.on('drain', client.end.bind(client));
Add a custom footer
Add a custom sidebar
Clone this wiki locally