# easy-pg > easy-pg is "easy to use" deferred PostgreSQL client for node.js Latest version **2.2.0** (published 2017-05-16) · BSD license · 0 weekly downloads ## Install ```sh npm install easy-pg pnpm add easy-pg yarn add easy-pg bun add easy-pg ``` ## Health **Score 15/100 (F)** — status: abandoned. Positive: no vulnerabilities. Warnings: low downloads; no types; no esm support. Negative: abandoned; low maintenance score. ## Facts | | | |---|---| | Version | 2.2.0 | | Published | 2017-05-16 | | First published | 2013-07-19 | | Weekly downloads | 0 | | License | BSD | | TypeScript types | none | | Module format | CommonJS | | Node | >=4 | | Dependencies | 3 | | Known vulnerabilities | 0 | | Install scripts | no | | GitHub stars | 4 | | Maintainers | bender, bosy.princ, wision | | Keywords | postgres, postgre, pg, libpq, database, deferred, client | ## Links - npm: https://www.npmjs.com/package/easy-pg - Repository: https://github.com/falsecz/easy-pg - Homepage: https://github.com/falsecz/easy-pg#readme - Issues: https://github.com/falsecz/easy-pg/issues - npm.io page: https://npm.io/package/easy-pg ## Dependencies (3) - [glob](https://npm.io/package/glob.md) ^7.0.5 - [async](https://npm.io/package/async.md) ^1.5.2 - [debug](https://npm.io/package/debug.md) ^2.1.0 ## Alternatives - [launchdarkly-js-client-sdk](https://npm.io/package/launchdarkly-js-client-sdk.md) — 2.5M weekly downloads - [@elastic/elasticsearch](https://npm.io/package/@elastic/elasticsearch.md) — 2.1M weekly downloads - [@c8y/client](https://npm.io/package/@c8y/client.md) — 15.3K weekly downloads - [@signaldb/maverickjs](https://npm.io/package/@signaldb/maverickjs.md) — 1.7K weekly downloads - [@bbc/http-transport-cache](https://npm.io/package/@bbc/http-transport-cache.md) — 1.2K weekly downloads ## Recent versions - 2.2.0 (latest) — 2017-05-16 - 2.0.1 (beta4) — 2015-06-16 - 2.1.1 — 2017-04-25 - 2.1.0 — 2017-04-25 - 2.0.2 — 2016-09-02 - 2.0.0 — 2016-06-28 - 1.0.5 — 2015-03-11 - 1.0.4 — 2015-02-19 - 1.0.3 — 2015-01-30 - 1.0.2 — 2014-11-07 - 1.0.1 — 2014-08-04 - 1.0.0 — 2014-07-09 - 0.3.1 — 2014-02-07 - 0.2.8 — 2013-09-17 - 0.2.6 — 2013-08-13 - … 13 more at https://npm.io/package/easy-pg/versions ## README #easy-pg [![Build Status](https://travis-ci.org/falsecz/easy-pg.png?branch=master)](https://travis-ci.org/falsecz/easy-pg) [![Dependency Status](https://david-dm.org/falsecz/easy-pg.png)](https://david-dm.org/falsecz/easy-pg) easy-pg is "easy to use" deferred PostgreSQL client for node.js providing some frequently used querying functions. It prevents queries from not being processed due to unexpected minor errors such as temporary loss of connection. Easy-pg stacks queries during transactions as well to revive whole transaction in the case of interrupted connection. ##Installation npm install -S easy-pg npm install -S pg # peer dependency ##Examples ###Simple Connection Simple example of connecting to postgres instance, running a query and disconnecting. Client is created as deferrer client thus it's not connected until the first query is requested. In this example number 7 is inserted into table called numbers, column number. Client is disconnected right after the query result is known. ```coffeescript epg = require "easy-pg" client = epg "pg://postgres@localhost/myapp_test" client.on "ready", () -> console.log "Client is connected" client.on "end", () -> console.log "Client is disconnected" # not connected so far, it's deferred client! # client creates connection only with the first query #insert number into specified table and disconnect client.insert "numbers", {number: 7}, (err, res) -> console.log err if err? console.log res if res? client.end() ``` Previous code results in: Client is connected {id: 1, number: 7} Client is disconnected ###Connection Parameters & Options You can pass connection string or object into easy-pg constructor with connection options. These options are processed by client (if known) and transparently forwarded to postgres instance later. ```coffeescript epg = require "easy-pg" #connection string conString = "pg://postgres@localhost/myapp_test?opt1=val1&opt2=val2&opt3=val3" #the same connection object conObject = protocol: "pg:" user: "postgres" host: "localhost" db: "myapp_test" options: { opt1: val1 opt2: val2 opt3: val3 } #both following is correct client = epg conString client = epg conObject ``` Following connection parameters and options can be used: * Connection parameters * protocol (required) * user * password * host (required) * port * db (required) * Connection options * lazy -set to "no" or "false" to force the client to connect immediately * poolSize -max number of connections in the pool * dateStyle -instead of (in SQL) commonly used SET DATESTYLE * searchPath -instead of (in SQL) commonly used SET SEARCH_PATH * pgVersion -version of the postgreSQL instance, set automatically Full connection string may look like this: "pg://postgres:123456@localhost:5432/myapp_test?lazy=no&dateStyle=iso, mdy&searchPath=public&poolSize=1&hang=no", where hang is not handled by easy-pg, but may be processed by postgres instance. Connection options are checked and applied every time the client is (re)connected, thus once you for example set dateStyle, it is kept set until the client is disconnected and destroyed. Even if the connection is temporarily lost. ###Disconnection Client creates connection to specified postgres instance automatically, however disconnection has to be done manually. Easy-pg provides two functions for client disconnection or termination. Function end can be used to disconnect client with the last processed query. This way of client disconnection should be used in common cases. In the case of stuck, kill can be used to terminate the client immediately, but there is a risk of unpredictable behavior. Both functions emit end event. ```coffeescript epg = require "easy-pg" client = epg "pg://postgres@localhost/myapp_test" # not connected yet client.queryAll "SELECT * FROM table" # auto-connecting, client is going to send the query client.end() # connected, query is being processed # end() is waiting until the query is finished client.kill() # connected, query is being processed, we don't want to wait anymore # client is terminated immediately, error could occur # not connected, end event is emitted ``` ###Client Events There are 3 events emitted by easy-pg client: * ready * end * error (client throws an ordinary Error if error listener is not registered, as shown in the following code) ```coffeescript epg = require "easy-pg" client = epg "pg://postgres@localhost/myapp_test" # an ordinary Error can be thrown here client.on "ready", () -> console.log "Client is connected" client.on "end", () -> console.log "Client is disconnected" # an ordinary Error can still be thrown here client.on "error", (err) -> console.log "Client error: " + err # error event can be emitted here ``` Error event is emitted just in the case of fatal error (syntax error, etc.). For example, if postgres server is restarted while processing query and the query fails, client reconnects itself and tries to process this query again without emitting or throwing any error. ###Making Queries Any kind of queries can be created and sent by easy-pg client, even with parameter binding. Queries can be easily made using following functions: * query * queryAll * queryOne These functions differ just in the data format of their results. Function query returns raw result of the query containing number of rows, table id, etc. QueryAll returns only array of all rows of the query result and queryOne returns only the first entry (row) of this array. They can be used as shown in the code: ```coffeescript epg = require "easy-pg" client = epg "pg://postgres@localhost/myapp_test" client.query "SET DATESTYLE = iso" client.query "SELECT * FROM table", (err, res) -> # do sth. in callback... client.query "SELECT $1 FROM $2", ["*", "table"] # bind some parameters... client.query "SELECT $1 FROM $2", ["*", "table"], (err, res) -> # do sth. in callback... client.queryAll "SET DATESTYLE = iso" client.queryAll "SELECT * FROM table", (err, res) -> # do sth. in callback... client.queryAll "SELECT $1 FROM $2", ["*", "table"] # bind some parameters... client.queryAll "SELECT $1 FROM $2", ["*", "table"], (err, res) -> # do sth. in callback... client.queryOne "SET DATESTYLE = iso" client.queryOne "SELECT * FROM table", (err, res) -> # do sth. in callback... client.queryOne "SELECT $1 FROM $2", ["*", "table"] # bind some parameters... client.queryOne "SELECT $1 FROM $2", ["*", "table"], (err, res) -> # do sth. in callback... ``` ###Built-in Querying Functions Easy-pg provides some well known querying functions as well to make your work easier and source code cleaner. Implemented querying functions are insert, update, upsert, delete and paginate. All these functions can be called with "One" postfix (e.g. updateOne) to make them return only the first row of the result. ```coffeescript epg = require "easy-pg" client = epg "pg://postgres@localhost/myapp_test" # db contains table "numbers" with column "number" # table, value # returns array of inserted rows client.insert "numbers", {number: 0} # insert one row client.insert "numbers", {number: 4}, (err, res) -> # do sth. in callback... client.insert "numbers", [{number: 1}, {number: 2}, {number: 3}] # insert 3 rows client.insert "numbers", [{number: 1}, {number: 2}, {number: 3}], (err, res) -> # do sth. in callback... # table, value, where # returns array of updated rows client.update "numbers", {number: 99}, "number = 0" # replaces number 0 by 99 client.update "numbers", {number: 99}, "number = 0", (err, res) -> # do sth. in callback... client.update "numbers", {number: 99}, "number = $1", [1] # replaces number 1 by 99 client.update "numbers", {number: 99}, "number = $1", [1], (err, res) -> # do sth. in callback... # table, value, where # returns object with .operation (insert/update) and .rows[] (array of rows) client.upsert "numbers", {number: 9}, "number = 9" # inserts number 9 client.upsert "numbers", {number: 9}, "number = 9", (err, res) -> # do sth. in callback... client.upsert "numbers", {number: 9}, "number = $1", [9] # replaces number 9 by 9 client.upsert "numbers", {number: 9}, "number = $1", [9], (err, res) -> # do sth. in callback... # table # returns array of deleted rows client.delete "numbers" # deletes table "numbers" client.delete "numbers", (err, res) -> # do sth. in callback... client.delete "numbers", "number = 0" # deletes rows with 0 client.delete "numbers", "number = 0", (err, res) -> # do sth. in callback... client.delete "numbers", "number = $1", [1] # deletes rows with 1 client.delete "numbers", "number = $1", [1], (err, res) -> # do sth. in callback... # offset, limit, columns, query result (table), orderBy # returns object with offsets and array of rows in .data[] client.paginate 0, 10, "number", "numbers", "_id" # lists first 10 rows of the given table client.paginate 0, 10, "number", "numbers", "_id", (err, res) -> # do sth. in callback... client.paginate 0, 10, "_id, number", "numbers WHERE _id > $1", "_id", [9] # the same with ids > 9 client.paginate 0, 10, "_id, number", "numbers WHERE _id > $1", "_id", [9], (err, res) -> # do sth. in callback... ``` ###Transactions Transactions are also carefully handled by easy-pg. Once the transaction is started, all queries are saved into transaction stack until the final commit or rollback is called. In the case of temporary connection loss or other minor error, whole transaction is revived and processed again. Following functions can be used to control the transaction flow: * begin * savepoint * commit * rollback See the source code below to understand the use of these functions. ```coffeescript epg = require "easy-pg" client = epg "pg://postgres@localhost/myapp_test" client.begin() # begins transaction, client.query "BEGIN" can be used instead client.begin (err, res) -> # do sth. in callback... client.savepoint "my_savepoint" # creates savepoint for rollback to savepoint client.savepoint "my_savepoint", (err, res) -> # do sth. in callback... client.commit() # commits changes in db client.commit (err, res) -> # do sth. in callback... client.rollback() # rolls back to closest begin client.rollback (err, res) -> # do sth. in callback... client.rollback "my_savepoint" # rolls back to "my_savepoint" client.rollback "my_savepoint", (err, res) -> # do sth. in callback... ``` Stacks are used to allow the client proper handling of nested transactions! Pseudocode below shows an example of succesfully revived transaction. COMMANDS STACK begin B query1 QB begin BQB query2 QBQB query3 QQBQB rollback QB query4 QQB <-- connection err commit ---- restart ---- begin B query1 QB query4 QQB commit ###Acceptable Errors Minor errors were mentioned in the text above. All messages sent by PostgreSQL server contain error codes to inform client about the state of the database. Most of these codes are not handled, just forwarded through callback-err, except of 3 error code classes: * 00 Successful Completion * 08 Connection Exception, 08P01 (Protocol Violation) is excluded * 57 Operator Intervention These 3 types of errors only forces the client to restart current connection and continue in query queue processing later. More information about PostgreSQL error codes can be found here. ###Notes and Tips Sometimes, pg returns null instead of date-time values. It's not a mistake, it really does. To solve this issue, simply append following option into easy-pg connection string: "?dateStyle=iso, mdy" Easy-pg doesn't support queries containing IN and binded array of parameters right now, but you can use akin query instead. epg.queryAll "SELECT * FROM numbers WHERE number IN ($1)", [[1, 2, 3]], () -> ... not supported epg.queryAll "SELECT * FROM numbers WHERE number = ANY ($1)", [[1, 2, 3]], () -> ... works well --- _Source: https://npm.io/package/easy-pg · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._