Client

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.

• methods
  • connect
  • end
  • query (simple)
  • query (parameterized)
  • query (prepared statement)
• events
  • drain
  • error
  • notification
  • notice
  • end

Constructors

note: Client instances created via the constructor do not participate in pg's connection pooling. To take advantage of connection pooling (recommended) please use either pg-pool or a pooling utility such as pgbouncer.

new Client(): Client

This is the preferred way to create a client - let the client read its connection parameters out of environment variables: the client will read host, database, user, password, etc from the same environment variables used by postgres utilities

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

varclient=newClient('postgres://brian:mypassword@localhost:5432/dev');varclient=newClient('postgres://brian@localhost/dev');//will use defaultsvarclient=newClient(process.env.DATABASE_URL);//something like this should get you running with herokuvarclient=newClient('/tmp');//looks for the socket file /tmp/.s.PGSQL.5432

Caution :

Url strings don't allow to pass special characters like # If you have some in your password, don't use a connection string, use a config object and pass it as { host: 'foo', password: 'blah#blah' }

new Client(object config) : Client

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

parameters

• objectconfig: can contain any of the following optional properties
  • stringuser:
    • default value: process.env.USER
    • PostgreSQL user
  • stringdatabase:
    • default value: process.env.USER
    • database to use when connecting to PostgreSQL server
  • stringpassword:
    • default value: null
    • user's password for PostgreSQL server
  • numberport:
    • default value: 5432
    • port to use when connecting to PostgreSQL server
    • used to initialize underlying net.Stream()
  • stringhost:
    • default value: localhost
    • host address of PostgreSQL server (or a path such as /var/run/postgresql for Unix sockets)
    • note: localhost still uses TCP (instead of Unix) sockets for the non-native connector
    • used to initialize underlying net.Stream()
  • bool/objectssl:
    • default value: false
    • whether to try SSL/TLS to connect to server
    • if you wish to alter any SSL connection parameters, while using the the postgres javascript client implementation, pass the same options as tls.connect(). Default values for tls.connect() options are overridden by this module, pass them explicitly. Eg: to use SSL certificate verification, pass values to the ca parameter and set the rejectUnauthorized paramether to true
  • stringapplication_name:
    • default value: process.env.PGAPPNAME
    • name displayed in the pg_stat_activity view and included in CSV log entries
  • stringfallback_application_name:
    • default value: false
    • fallback value for the application_name configuration parameter

tcp example

varclient=newClient({user: 'brianc',password: 'boom!',database: 'test',host: 'example.com',port: 5313});

domain socket example

Will look for the Unix Domain Socket at /tmp/.s.PGSQL.5313 and connect with the rest of the supplied credentials:

varclient=newClient({user: 'brianc',password: 'boom!',database: 'test',host: '/tmp',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 a pool 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 a pool will be automatically disconnected or placed back into the connection pool and should not have their #end method called directly.

---

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

• stringtext: the query text
• optional functioncallback: 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
  • if passed, query will still raise the row and end events but will no longer raise the error event
  • parameters
    • objecterror:
      • null if there was no error
      • if PostgreSQL encountered an error during query execution, the message will be called here
    • objectresult:
      • the result of the query, containing the same properties as the Result object in end event of Query.

examples

simple query with row callback

varclient=newClient({user: 'brianc',database: 'test'});client.on('drain',client.end.bind(client));//disconnect client when all queries are finishedclient.connect();//query is executed once connection is established and//PostgreSQL server is ready for a queryvarquery=client.query("SELECT name FROM users",function(err,result){console.log(result.rows[0].name);})

simple query with promise

varclient=newClient();client.query('SELECT NOW() as right_now').then(res=>console.log(res.rows[0].right_now)).then(()=>client.end())

---

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

parameterized query with config object

varclient=newClient({user: 'brianc',database: 'test'});client.on('drain',client.end.bind(client));//disconnect client when all queries are finishedclient.connect();varquery=client.query({text: 'SELECT name FROM users WHERE email = $1',values: ['brianc@example.com']},function(err,result){console.log(result.rows[0].name)// output: brianc});

parameterized query using string/array initialization

varclient=newClient({user: 'brianc',database: 'test'});client.on('drain',client.end.bind(client));//disconnect client when all queries are finishedclient.connect();client.query("SELECT name FROM users WHERE email = $1",['brianc@example.com'],function(err,result){console.log(result.rows[0].name)// output: brianc});

parameterized query with optional callback supplied

varclient=newClient({user: 'brianc',database: 'test'});client.on('drain',client.end.bind(client));//disconnect client when all queries are finishedclient.connect();//object config methodvarqueryConfig={text: 'SELECT name FROM users WHERE email = $1',values: ['brian@example.com']};client.query(queryConfig,function(err,result){assert.equal('brianc',result.rows[0]);});//text/params methodclient.query('SELECT name FROM users WHERE email = $1',['brian@example.com'],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 values and name provided within the config, the prepared statement will be executed. (Note: if the prepared statement takes no parameters, use values: [].)

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

varclient=newClient({user: 'brianc',database: 'test'});client.on('drain',client.end.bind(client));//disconnect client when all queries are finishedclient.connect();varfirst=client.query({text: "SELECT email FROM users WHERE name = $1",values: ['brianc'],name: 'email from name'});first.on('row',function(row){assert.equal("brian@example.com",row.email);});varsecond=client.query({name: 'email from name',values: ['brianc']});second.on('row',function(row){assert.equal("brian@example.com",row.email);});//can still supply a callback methodvarthird=client.query({name: 'email from name',values: ['brianc']},function(err,result){assert.equal('brian@example.com',result.rows[0].email);});

parameters

• objectconfig: can contain any of the following optional properties
  • stringtext:
    • The text of the query
    • example:select name from user where email = $1
  • stringname:
    • 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
  • arrayvalues:
    • The values to supply as parameters
    • Values may be any object type supported by the Client
• optional functioncallback: callback function
  • function callback(object error, object result)
    • Called only if provided
    • 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
      • objecterror:
        • null if there was no error
        • if PostgreSQL encountered an error during query execution, the message will be called here
      • objectresult:
        • the result of the query, containing the same properties as the Result object in end event of Query.

---

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

varclient=newClient({user: 'brianc',database: 'postgres'});client.connect();varusers=client.query("select * from user");varsuperdoods=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 returnusers.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

varclient=newClient({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

varclient1=newClient(...)varclient2=newClient(...)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 calledsetTimeout(function(){client2.query("NOTIFY boom",function(){client2.end();});},1000);

---

notice : object notice

Emitted from PostgreSQL server when non-critical events happen, for example a RAISE NOTICE statement in a plpgsql function. When using connection pooling, be sure to attach the handler only once per client.

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

varclient=newClient(...)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));

---

end :

Emitted when the connection is finished. It is useful when the pooling mechanism is external to pg.

example

 client.on('end', function(){console.log("Client was disconnected.");

---

◄ Back (API - pg)Next (API - pg.Query) ►