Low-level native bindings to PostgreSQL libpq
npm install libpq
Node native bindings to the PostgreSQL libpq C client library. This module attempts to mirror _as closely as possible_ the C API provided by libpq and provides the absolute minimum level of abstraction. It is intended to be extremely low level and allow you the same access as you would have to libpq directly from C, except in node.js! The obvious trade-off for being "close to the metal" is having to use a very "c style" API in JavaScript.
If you have a good understanding of libpq or used it before hopefully the methods within node-libpq will be familiar; otherwise, you should probably spend some time reading the official libpq C library documentation to become a bit familiar. Referencing the libpq documentation directly should also provide you with more insight into the methods here. I will do my best to explain any differences from the C code for each method.
I am also building some higher level abstractions to eventually replace the pg.native portion of node-postgres. They should help as reference material.
This module relies heavily on nan and wouldn't really be possible without it. Mucho thanks to the node-nan team.
You need libpq installed & the pg_config program should be in your path. You also need node-gyp installed.
``bash`
$ npm install libpq
> Note: for Node.js equal or greater to version 10.16.0 you need to have at least OpenSSL 1.1.1 installed.
`js`
var Libpq = require('libpq');
var pq = new Libpq();
Libpq provides a few different connection functions, some of which are "not preferred" anymore. I've opted to simplify this interface a bit into a single __async__ and single __sync__ connnection function. The function accepts an connection string formatted as outlined in this documentation in section 31.1.1. If the parameters are not supplied, libpq will automatically use environment variables, a pgpass file, and other options. Consult the libpq documentation for a better rundown of all the ways it tries to determine your connection parameters.
I personally __always__ connect with environment variables and skip supplying the optional connectionParams. Easier, more 12 factor app-ish, and you never risk hard coding any passwords. YMMV. :smile:
##### pq.connect([connectionParams:string], callback:function)
Asyncronously attempts to connect to the postgres server.
- connectionParams is an optional stringcallback
- is mandatory. It is called when the connection has successfully been established.
__async__ Connects to a PostgreSQL backend server process.
This function actually calls the PQconnectdb blocking connection method in a background thread within node's internal thread-pool. There is a way to do non-blocking network I/O for some of the connecting with libpq directly, but it still blocks when your local file system looking for config files, SSL certificates, .pgpass file, and doing possible dns resolution. Because of this, the best way to get _fully_ non-blocking is to juse use libuv_queue_work and let node do it's magic and so that's what I do. This function _does not block_.
##### pq.connectSync([connectionParams:string])
Attempts to connect to a PostgreSQL server. __BLOCKS__ until it either succeedes, or fails. If it fails it will throw an exception.
- connectionParams is an optional string
##### pq.finish()
Disconnects from the backend and cleans up all memory used by the libpq connection.
##### pq.errorMessage():string
Retrieves the last error message from the connection. This is intended to be used after most functions which return an error code to get more detailed error information about the connection. You can also check this _before_ issuing queries to see if your connection has been lost.
##### pq.socket():int
Returns an int representing the file descriptor for the socket used internally by the connection
##### pq.exec(commandText:string)
__sync__ sends a command to the backend and blocks until a result is received.
- commandText is a required string of the query.
##### pq.execParams(commandText:string, parameters:array[string])
__snyc__ sends a command and parameters to the backend and blocks until a result is received.
- commandText is a required string of the query.parameters
- is a required array of string values corresponding to each parameter in the commandText.
##### pq.prepare(statementName:string, commandText:string, nParams:int)
__sync__ sends a named statement to the server to be prepared for later execution. blocks until a result from the prepare operation is received.
- statementName is a required string of name of the statement to prepare.commandText
- is a required string of the query.nParams
- is a count of the number of parameters in the commandText.
##### pq.execPrepared(statementName:string, parameters:array[string])
__sync__ sends a command to the server to execute a previously prepared statement. blocks until the results are returned.
- statementName is a required string of the name of the prepared statement.parameters
- are the parameters to pass to the prepared statement.
##### pq.describePrepared(statementName:string)
__sync__ sends a command to the server to describe a previously prepared statement. blocks until the results are returned. Use and pq.paramtype to obtain information about the parameters and pq.nfields, pq.fname and pq.ftype about the result columns of the prepared statement.
In libpq the async command execution functions _only_ dispatch a request to the backend to run a query. They do not start result fetching on their own. Because libpq is a C api there is a somewhat complicated "dance" to retrieve the result information in a non-blocking way. node-libpq attempts to do as little as possible to abstract over this; therefore, the following functions are only part of the story. For a complete tutorial on how to dispatch & retrieve results from libpq in an async way you can view the complete approach here
##### pq.sendQuery(commandText:string):boolean
__async__ sends a query to the server to be processed.
- commandText is a required string containing the query text.
Returns true if the command was sent succesfully or false if it failed to send.
##### pq.sendQueryParams(commandText:string, parameters:array[string]):boolean
__async__ sends a query and to the server to be processed.
- commandText is a required string containing the query text.parameters
- is an array of parameters as strings used in the parameterized query.
Returns true if the command was sent succesfully or false if it failed to send.
##### pq.sendPrepare(statementName:string, commandText:string, nParams:int):boolean
__async__ sends a request to the backend to prepare a named statement with the given name.
- statementName is a required string of name of the statement to prepare.commandText
- is a required string of the query.nParams
- is a count of the number of parameters in the commandText.
Returns true if the command was sent succesfully or false if it failed to send.
##### pq.sendQueryPrepared(statementName:string, parameters:array[string]):boolean
__async__ sends a request to execute a previously prepared statement.
- statementName is a required string of the name of the prepared statement.parameters
- are the parameters to pass to the prepared statement.
##### pq.getResult():boolean
Parses received data from the server into a struct and sets a pointer internally to the connection object to this result. __warning__: this function will __block__ if libpq is waiting on async results to be returned from the server. Call pq.isBusy() to determine if this command will block.
Returns true if libpq was able to read buffered data & parse a result object. Returns false if there are no results waiting to be parsed. Generally doing async style queries you'll call this repeadedly until it returns false and then use the result accessor methods to pull results out of the current result set.
After a command is run in either sync or async mode & the results have been received, node-libpq stores the results internally and provides you access to the results via the standard libpq methods. The difference here is libpq will return a pointer to a PGresult structure which you access via libpq functions, but node-libpq stores the most recent result within itself and passes the opaque PGresult structure to the libpq methods. This is to avoid passing around a whole bunch of pointers to unmanaged memory and keeps the burden of properly allocating and freeing memory within node-libpq.
##### pq.resultStatus():string
Returns either PGRES_COMMAND_OK or PGRES_FATAL_ERROR depending on the status of the last executed command.
##### pq.resultErrorMessage():string
Retrieves the error message from the result. This will return null if the result does not have an error.
##### pq.resultErrorFields():object
Retrieves detailed error information from the current result object. Very similar to PQresultErrorField() except instead of passing a fieldCode and retrieving a single field, retrieves all fields from the error at once on a single object. The object returned is a simple hash, _not_ an instance of an error object. Example: if you wanted to access PG_DIAG_MESSAGE_DETAIL you would do the following:
`js`
console.log(pq.errorFields().messageDetail)
##### pq.clear()
Manually frees the memory associated with a PGresult pointer. Generally this is called for you, but if you absolutely want to free the pointer yourself, you can.
##### pq.ntuples():int
Retrieve the number of tuples (rows) from the result.
##### pq.nfields():int
Retrieve the number of fields (columns) from the result.
##### pq.fname(fieldNumber:int):string
Retrieve the name of the field (column) at the given offset. Offset starts at 0.
##### pq.ftable(fieldNumber:int):int
Retrieve the Oid of the table at the given offset. Offset starts at 0.
##### pq.ftablenum(fieldNumber:int):int
Retrieve the column number (within its table) of the field at the given offset. Offset starts at 0.
Query-result column numbers start at 0, but table columns have nonzero numbers.
##### pq.ftype(fieldNumber:int):int
Retrieve the Oid of the field (column) at the given offset. Offset starts at 0.
##### pq.getvalue(tupleNumber:int, fieldNumber:int):string
Retrieve the text value at a given tuple (row) and field (column) offset. Both offsets start at 0. A null value is returned as the empty string ''.
##### pq.getisnull(tupleNumber:int, fieldNumber:int):boolean
Returns true if the value at the given offsets is actually null. Otherwise returns false. This is because pq.getvalue() returns an empty string for both an actual empty string and for a null value. Weird, huh?
##### pq.nparams():int
Returns the number of parameters a prepared statement expects.
##### pq.paramtype(paramNumber:int):int
Returns the Oid of the prepared statement's parameter at the given offset.
##### pq.cmdStatus():string
Returns the status string associated with a result. Something akin to INSERT 3 0 if you inserted 3 rows.
##### pq.cmdTuples():string
Returns the number of tuples (rows) affected by the command. Even though this is a number, it is returned as a string to mirror libpq's behavior.
These functions don't have a direct match within libpq. They exist to allow you to monitor the readability or writability of the libpq socket based on your platforms equivilant to select(). This allows you to perform async I/O completely from JavaScript.
##### pq.startReader()
This uses libuv to start a read watcher on the socket open to the backend. As soon as this socket becomes readable the pq instance will emit a readable event. It is up to you to call pq.consumeInput() one or more times to clear this read notification or it will continue to emit read events over and over and over. The exact flow is outlined [here] under the documentation for PQisBusy.
##### pq.stopReader()
Tells libuv to stop the read watcher on the connection socket.
##### pq.writable(callback:function)
Call this to make sure the socket has flushed all data to the operating system. Once the socket is writable, your callback will be called. Usefully when using PQsetNonBlocking and PQflush for async writing.
These are all documented in detail within the libpq documentation and function almost identically.
##### pq.consumeInput():boolean
Reads waiting data from the socket. If the socket is not readable and you call this it will __block__ so be careful and only call it within the readable callback for the most part.
Returns true if data was read. Returns false if there was an error. You can access error details with pq.errorMessage().
##### pq.isBusy():boolean
Returns true if calling pq.consumeInput() would block waiting for more data. Returns false if all data has been read from the socket. Once this returns false it is safe to call pq.getResult()
##### pq.setNonBlocking(nonBlocking:boolean):boolean
Toggle the socket blocking on _write_. Returns true if the socket's state was succesfully toggled. Returns false if there was an error.
- nonBlocking is true to set the connection to use non-blocking writes. false to use blocking writes.
##### pq.flush():int
Flushes buffered data to the socket. Returns 1 if socket is not write-ready at which case you should call pq.writable with a callback and wait for the socket to be writable and then call pq.flush() again. Returns 0 if all data was flushed. Returns -1 if there was an error.
##### pq.notifies():object
Checks for NOTIFY messages that have come in. If any have been received they will be in the following format:
`js`
var msg = {
relname: 'name of channel',
extra: 'message passed to notify command',
be_pid: 130
}
##### pq.putCopyData(buffer:Buffer):int
After issuing a successful command like COPY table FROM stdin you can start putting buffers directly into the databse with this function.
- buffer Is a required node buffer of text data such as Buffer('column1\tcolumn2\n')
Returns 1 if sent succesfully. Returns 0 if the command would block (only if you have called pq.setNonBlocking(true)). Returns -1 if there was an error sending the command.
##### pq.putCopyEnd([errorMessage:string])
Signals the backed your copy procedure is complete. If you pass errorMessage it will be sent to the backend and effectively cancel the copy operation.
- errorMessage is an _optional_ string you can pass to cancel the copy operation.
Returns 1 if sent succesfully. Returns 0 if the command would block (only if you have called pq.setNonBlocking(true)). Returns -1 if there was an error sending the command.
##### pq.getCopyData(async:boolean):Buffer or int
After issuing a successfuly command like COPY table TO stdout gets copy data from the connection.
Returns a node buffer if there is data available.
Returns 0 if the copy is still in progress (only if you have called pq.setNonBlocking(true)). Returns -1 if the copy is completed. Returns -2 if there was an error.
- async is a boolean. Pass false to __block__ waiting for data from the backend. _defaults to false_
##### pq.escapeLiteral(input:string):string
Exact copy of the PQescapeLiteral function within libpq. Requires an established connection but does not perform any I/O.
##### pq.escapeIdentifier(input:string):string
Exact copy of the PQescapeIdentifier function within libpq. Requires an established connection but does not perform any I/O.
##### pq.cancel():true -or- string
Issues a request to cancel the currently executing query _on this instance of libpq_. Returns true if the cancel request was sent. Returns a string error message if the cancel request failed for any reason. The string will contain the error message provided by libpq.
##### pq.serverVersion():number
Returns the version of the connected PostgreSQL backend server as a number.
Pipeline mode allows sending multiple queries to the server without waiting for results, significantly reducing round-trip latency. These functions are only available when compiled against PostgreSQL 14 or later client libraries.
##### pq.pipelineModeSupported():boolean
Returns true if pipeline mode is supported (compiled against PostgreSQL 14+), false otherwise.
##### pq.enterPipelineMode():boolean
Enters pipeline mode on the connection. In pipeline mode, you can send multiple queries using the async send functions (sendQuery, sendQueryParams, etc.) without waiting for results.
Returns true if successful, false if failed. Throws an error if pipeline mode is not supported.
##### pq.exitPipelineMode():boolean
Exits pipeline mode. Can only be called when the pipeline is empty (all results have been processed).
Returns true if successful, false if failed.
##### pq.pipelineStatus():int
Returns the current pipeline status:
- PQ.PIPELINE_OFF (0): Not in pipeline modePQ.PIPELINE_ON
- (1): In pipeline modePQ.PIPELINE_ABORTED
- (2): Pipeline aborted due to error
##### pq.pipelineSync():boolean
Sends a synchronization point in the pipeline. The server will process all queries up to this point and send their results before processing any further queries. This is essential for error handling - if a query fails, all subsequent queries until the next sync point are skipped.
Returns true if successful, false if failed.
##### pq.sendFlushRequest():boolean
Sends a request for the server to flush its output buffer. Useful when you want to receive results before sending a sync.
Returns true if successful, false if failed.
#### Pipeline Mode Constants
`js`
PQ.PIPELINE_OFF // 0 - Not in pipeline mode
PQ.PIPELINE_ON // 1 - In pipeline mode
PQ.PIPELINE_ABORTED // 2 - Pipeline aborted due to error
#### Pipeline Mode Example
`js
var PQ = require('libpq');
var pq = new PQ();
pq.connectSync();
if (!pq.pipelineModeSupported()) {
console.log('Pipeline mode requires PostgreSQL 14+ client libraries');
process.exit(1);
}
// Enter pipeline mode
pq.enterPipelineMode();
pq.setNonBlocking(true);
// Send multiple queries without waiting
pq.sendQueryParams('SELECT $1::int', ['1']);
pq.sendQueryParams('SELECT $1::int', ['2']);
pq.sendQueryParams('SELECT $1::int', ['3']);
// Mark end of pipeline batch
pq.pipelineSync();
pq.flush();
// Process results asynchronously
pq.on('readable', function() {
pq.consumeInput();
while (!pq.isBusy()) {
if (!pq.getResult()) break;
var status = pq.resultStatus();
if (status === 'PGRES_TUPLES_OK') {
console.log('Result:', pq.getvalue(0, 0));
} else if (status === 'PGRES_PIPELINE_SYNC') {
// All results received
pq.stopReader();
pq.exitPipelineMode();
pq.finish();
}
}
});
pq.startReader();
`
`sh`
$ npm test
To run the tests you need a PostgreSQL backend reachable by typing psql with no connection parameters in your terminal. The tests use environment variables to connect to the backend.
An example of supplying a specific host the tests:
`sh``
$ PGHOST=blabla.mydatabasehost.com npm test
The MIT License (MIT)
Copyright (c) 2014 Brian M. Carlson
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.