npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

easy-pg

v2.2.0

Published

easy-pg is "easy to use" deferred PostgreSQL client for node.js

Downloads

56

Readme

#easy-pg Build Status Dependency Status

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.

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.

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.

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)
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:

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.

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.

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