Postgres.js: Fastest full featured PostgreSQL client for Node.js

Postgres.js: Fastest full featured PostgreSQL client for Node.js. Connection options postgres([url], [options]). Query sql` ` -> Promise. Stream sql` `.stream(fn) -> Promise. Dynamic query helpers sql() inside tagged template. File query sql.file(path, [args], [options]) -> Promise.

Getting started

Install

source-shell
$ npm install postgres

Use

source-js
const postgres = require('postgres')

const sql = postgres({ ...options }) // will default to the same as psql

await sql`
  select name, age from users
`
// > [{ name: 'Murray', age: 68 }, { name: 'Walter', age 78 }]

Connection options postgres([url], [options])

You can use either a postgres:// url connection string or the options to define your database connection properties. Options in the object will override any present in the url.

source-js
const sql = postgres('postgres://username:[email protected]:port/database', {
  host        : '',         // Postgres ip address or domain name
  port        : 5432,       // Postgres server port
  path        : '',         // unix socket path (usually '/tmp')
  database    : '',         // Name of database to connect to
  username    : '',         // Username of database user
  password    : '',         // Password of database user
  ssl         : false,      // True, or options for tls.connect
  max         : 10,         // Max number of connections
  timeout     : 0,          // Idle connection timeout in seconds
  types       : [],         // Array of custom types, see more below
  onnotice    : fn          // Defaults to console.log
  onparameter : fn          // (key, value) when server param change
  debug       : fn          // Is called with (connection, query, parameters)
  transform   : {
    column            : fn, // Transforms incoming column names
    value             : fn, // Transforms incoming row values
    row               : fn  // Transforms entire rows
  },
  connection  : {
    application_name  : 'postgres.js', // Default application_name
    ...                                // Other connection parameters
  }
})

More info for the ssl option can be found in the Node.js docs for tls connect options

Query sql -> Promise

A query will always return a Promise which resolves to either an array [...] or null depending on the type of query. Destructuring is great to immidiately access the first element.

source-js
const [new_user] = await sql`
  insert into users (
    name, age
  ) values (
    'Murray', 68
  )

  returning *
`

// new_user = { user_id: 1, name: 'Murray', age: 68 }

Query parameters

Parameters are automatically inferred and handled by Postgres so that SQL injection isn't possible. No special handling is necessarry, simply use JS tagged template literals as usual.

source-js
let search = 'Mur'

const users = await sql`
  select 
    name, 
    age 
  from users
  where 
    name like ${ search + '%' }
`

// users = [{ name: 'Murray', age: 68 }]

Stream sql .stream(fn) -> Promise

If you want to handle rows returned by a query one by one you can use .stream which returns a promise that resolves once there are no more rows.

source-js
await sql`
  select created_at, name from events
`.stream(row => {
  // row = { created_at: '2019-11-22T14:22:00Z', name: 'connected' }
})

// No more rows

Listen and notify

When you call listen, a dedicated connection will automatically be made to ensure that you receive notifications in realtime. This connection will be used for any further calls to listen.

source-js
sql.listen('news', payload => {
  const json = JSON.parse(payload)
  console.log(json.this) // logs 'is'
})

Notify can be done as usual in sql, or by using the sql.notify method.

sql.notify('news', JSON.stringify({ no: 'this', is: 'news' }))

Dynamic query helpers sql() inside tagged template

Postgres.js has a safe, ergonomic way to aid you in writing queries. This makes it easier to write dynamic inserts, selects, updates and where queries.

Insert

source-js
const user = {
  name: 'Murray',
  age: 68
}

sql`
  insert into users ${
    sql(user)
  }
`

Is translated into a safe query like this:

source-sql
insert into users (name, age) values ($1, $2)

Multiple inserts in one query

If you need to insert multiple rows at the same time it's also much faster to do it with a single insert. Simply pass an array of objects to sql().

source-js
const users = [{
  name: 'Murray',
  age: 68,
  garbage: 'ignore'
}, {
  name: 'Walter',
  age: 78
}]

sql`
  insert into users ${
    sql(users, 'name', 'age')
  }
`

Arrays sql.array(Array)

Postgres has a native array type which is similar to js arrays, but Postgres only allows the same type and shape for nested items. This method automatically infers the item type and translates js arrays into Postgres arrays.

source-js
const types = sql`
  insert into types (
    integers,
    strings,
    dates,
    buffers,
    multi
  ) values (
    ${ sql.array([1,2,3,4,5]) },
    ${ sql.array(['Hello', 'Postgres']) },
    ${ sql.array([new Date(), new Date(), new Date()]) },
    ${ sql.array([Buffer.from('Hello'), Buffer.from('Postgres')]) },
    ${ sql.array([[[1,2],[3,4]][[5,6],[7,8]]]) },
  )
`

JSON sql.json(object)

source-js
const body = { hello: 'postgres' }

const [{ json }] = await sql`
  insert into json (
    body
  ) values (
    ${ sql.json(body) }
  )
  returning body
`

// json = { hello: 'postgres' }

File query sql.file(path, [args], [options]) -> Promise

Using an sql file for a query. The contents will be cached in memory so that the file is only read once.

source-js
sql.file(path.join(__dirname, 'query.sql'), [], {
  cache: true // Default true - disable for single shot queries or memory reasons
})

Transactions

BEGIN / COMMIT sql.begin(fn) -> Promise

Calling begin with a function will return a Promise which resolves with the returned value from the function. The function provides a single argument which is sql with a context of the newly created transaction. BEGIN is automatically called, and if the Promise fails ROLLBACK will be called. If it succeeds COMMIT will be called.

source-js
const [user, account] = await sql.begin(async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Alice'
    )
  `

  const [account] = await sql`
    insert into accounts (
      user_id
    ) values (
      ${ user.user_id }
    )
  `

  return [user, account]
})

SAVEPOINT sql.savepoint([name], fn) -> Promise

source-js
sql.begin(async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Alice'
    )
  `

  const [account] = (await sql.savepoint(sql => 
    sql`
      insert into accounts (
        user_id
      ) values (
        ${ user.user_id }
      )
    `
  ).catch(err => {
    // Account could not be created. ROLLBACK SAVEPOINT is called because we caught the rejection.
  })) || []

  return [user, account]
})
.then(([user, account])) => {
  // great success - COMMIT succeeded
})
.catch(() => {
  // not so good - ROLLBACK was called
})

Do note that you can often achieve the same result using WITH queries (Common Table Expressions) instead of using transactions.

Types

You can add ergonomic support for custom types, or simply pass an object with a { type, value } signature that contains the Postgres oid for the type and the correctly serialized value.

Adding Query helpers is the recommended approach which can be done like this:

source-js
const sql = sql({
  types: {
    rect: {
      to        : 1337,
      from      : [1337],
      serialize : ({ x, y, width, height }) => [x, y, width, height],
      parse     : ([x, y, width, height]) => { x, y, width, height }
    }
  }
})

const [custom] = sql`
  insert into rectangles (
    name,
    rect
  ) values (
    'wat',
    ${ sql.types.rect({ x: 13, y: 37: width: 42, height: 80 }) }
  )
  returning *
`

// custom = { name: 'wat', rect: { x: 13, y: 37: width: 42, height: 80 } }

Teardown / Cleanup

To ensure proper teardown and cleanup on server restarts use sql.end({ timeout: null }) before process.exit()

Calling sql.end() will reject new queries and return a Promise which resolves when all queries are finished and the underlying connections are closed. If a timeout is provided any pending queries will be rejected once the timeout is reached and the connections will be destroyed.

Sample shutdown using Prexit

source-js
import prexit from 'prexit'

prexit(async () => {
  await sql.end({ timeout: 5 })
  await new Promise(r => server.close(r))
})

Unsafe queries sql.unsafe(query, [args], [options]) -> promise

If you know what you're doing, you can use unsafe to pass any string you'd like to postgres. Please note that this can lead to sql injection if you're not careful.

source-js
sql.unsafe('select ' + danger + ' from users where id = ' + dragons)

Errors

Errors are all thrown to related queries and never globally. Errors comming from Postgres itself are always in the native Postgres format, and the same goes for any Node.js errors eg. coming from the underlying connection.

There are also the following errors specifically for this library.

MESSAGE_NOT_SUPPORTED

X (X) is not supported

Whenever a message is received from Postgres which is not supported by this library. Feel free to file an issue if you think something is missing.

MAX_PARAMETERS_EXCEEDED

Max number of parameters (65534) exceeded

The postgres protocol doesn't allow more than 65534 (16bit) parameters. If you run into this issue there are various workarounds such as using sql([...]) to escape values instead of passing them as parameters.

SASL_SIGNATURE_MISMATCH

Message type X not supported

When using SASL authentication the server responds with a signature at the end of the authentication flow which needs to match the one on the client. This is to avoid man in the middle attacks. If you receive this error the connection was canceled because the server did not reply with the expected signature.

NOT_TAGGED_CALL

Query not called as a tagged template literal

Making queries has to be done using the sql function as a tagged template. This is to ensure parameters are serialized and passed to Postgres as query parameters with correct types and to avoid SQL injection.

AUTH_TYPE_NOT_IMPLEMENTED

Auth type X not implemented

Postgres supports many different authentication types. This one is not supported.

CONNECTION_CLOSED

write CONNECTION_CLOSED host:port

This error is thrown if the connection was closed without an error. This should not happen during normal operation, so please create an issue if this was unexpected.

CONNECTION_ENDED

write CONNECTION_ENDED host:port

This error is thrown if the user has called sql.end() and performed a query afterwards.

CONNECTION_DESTROYED

write CONNECTION_DESTROYED host:port

This error is thrown for any queries that were pending when the timeout to sql.end({ timeout: X }) was reached.

NodeJS Development Company-Hire Node JS developer from the most prominent NodeJS development company, Mobiweb and get remarkable Node.js app development services.

Are you looking to hire the most talented and expert Node JS developers for your valuable web application projects and mobile app development projects or you want to migrate application on Node JS framework? Then you have to hire Node JS developer from leading offshore Node JS development company Mobiweb Technologies. We have a team of developers with extensive experience in developing Node JS based applications whether it is web based or mobile app based.

You have to hire Node JS developer from prestigious and expert Node JS development company Mobiweb Technologies. They are tech enthusiasts with new and latest programming ideas, web development technologies and industry trends.

Node JS is the best JavaScript for utilizing in real-time applications. If you are stressed of using low level web sockets or protocols then with the incredible speed of Node JS you can easily develop real-time applications. According to the business perspective, Node JS is highly advantageous for any online business or business website, so it is very difficult for companies or business owners to avoid Node JS for their web application projects. For the best results in your Node JS development project you must have to hire Node JS developer from the prestigious web development company- Mobiweb Technologies.

Vue.js is an extensively popular JavaScript framework with which you can create powerful as well as interactive interfaces. Vue.js is the best framework when it comes to building a single web and mobile apps.

We, at HireFullStackDeveloperIndia, implement the right strategic approach to offer a wide variety through customized Vue.js development services to suit your requirements at most competitive prices.

Vue.js is an open-source JavaScript framework that is incredibly progressive and adoptive and majorly used to build a breathtaking user interface. Vue.js is efficient to create advanced web page applications.

Vue.js gets its strength from the flexible JavaScript library to build an enthralling user interface. As the core of Vue.js is concentrated which provides a variety of interactive components for the web and gives real-time implementation. It gives freedom to developers by giving fluidity and eases the integration process with existing projects and other libraries that enables to structure of a highly customizable application.

Vue.js is a scalable framework with a robust in-build stack that can extend itself to operate apps of any proportion. Moreover, vue.js is the best framework to seamlessly create astonishing single-page applications.

Our Vue.js developers have gained tremendous expertise by delivering services to clients worldwide over multiple industries in the area of front-end development. Our adept developers are experts in Vue development and can provide the best value-added user interfaces and web apps.

We assure our clients to have a prime user interface that reaches end-users and target the audience with the exceptional user experience across a variety of devices and platforms. Our expert team of developers serves your business to move ahead on the path of success, where your enterprise can have an advantage over others.

Here are some key benefits that you can avail when you decide to hire vue.js developers in USA from HireFullStackDeveloperIndia:

• A team of Vue.js developers of your choice
• 100% guaranteed client satisfaction
• Integrity and Transparency
• Free no-obligation quote
• Portal development solutions
• Interactive Dashboards over a wide array of devices
• Vue.js music and video streaming apps
• Flexible engagement model
• A free project manager with your team
• 24*7 communication with your preferred means

If you are looking to hire React Native developers in USA, then choosing HireFullStackDeveloperIndia would be the best as we offer some of the best talents when it comes to Vue.js.