Postgres.js

The fastest full featured PostgreSQL client for Node.js, Deno, Bun and Clou...

SQLDenoBun
porsager/postgrespostgres

README

Fastest full PostgreSQL nodejs client

- 🚀 Fastest full-featured node & deno client
- 🏷 ES6 Tagged Template Strings at the core
- 🏄‍♀️ Simple surface API
- 🖊️ Dynamic query support
- 💬 Chat and help on Gitter
- 🐦 Follow on Twitter



Getting started



Good UX with Postgres.js

Installation

  1. ```bash
  2. $ npm install postgres
  3. ```

Usage

Create your sql database instance
  1. ```js
  2. // db.js
  3. import postgres from 'postgres'

  5. const sql = postgres({ /* options */ }) // will use psql environment variables

  7. export default sql
  8. ```

Simply import for use elsewhere
  1. ```js
  2. // users.js
  3. import sql from './db.js'

  5. async function getUsersOver(age) {
  6.   const users = await sql`
  7.     select
  8.       name,
  9.       age
  10.     from users
  11.     where age > ${ age }
  12.   `
  13.   // users = Result [{ name: "Walter", age: 80 }, { name: 'Murray', age: 68 }, ...]
  14.   return users
  15. }


  18. async function insertUser({ name, age }) {
  19.   const users = await sql`
  20.     insert into users
  21.       (name, age)
  22.     values
  23.       (${ name }, ${ age })
  24.     returning name, age
  25.   `
  26.   // users = Result [{ name: "Murray", age: 68 }]
  27.   return users
  28. }
  29. ```

ESM dynamic imports


The library can be used with ESM dynamic imports as well as shown here.

  1. ```js
  2. const { default: postgres } = await import('postgres')
  3. ```

Table of Contents


Connection
Queries
Building queries
Advanced query methods
Transactions
Data Transformation
Listen & notify
Realtime subscribe
Numbers, bigint, numeric
Result Array
Connection details
Custom Types
Teardown / Cleanup
Error handling
TypeScript support
Reserving connections
Changelog


Connection


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. Options will fall back to the same environment variables as psql.

  1. ```js
  2. const sql = postgres('postgres://username:password@host:port/database', {
  3.   host                 : '',            // Postgres ip address[s] or domain name[s]
  4.   port                 : 5432,          // Postgres server port[s]
  5.   database             : '',            // Name of database to connect to
  6.   username             : '',            // Username of database user
  7.   password             : '',            // Password of database user
  8.   ...and more
  9. })
  10. ```

More options can be found in the Connection details section.

Queries


await sql... -> Result[]


Postgres.js utilizes Tagged template functions to process query parametersbefore interpolation. Using tagged template literals benefits developers by:

1. Enforcing safe query generation
2. Giving the sql function powerful utility and query building features.

Any generic value will be serialized according to an inferred type, and replaced by a PostgreSQL protocol placeholder $1, $2, .... The parameters are then sent separately to the database which handles escaping & casting.

All queries will return a Result array, with objects mapping column names to each row.

  1. ```js
  2. const xs = await sql`
  3.   insert into users (
  4.     name, age
  5.   ) values (
  6.     'Murray', 68
  7.   )

  9.   returning *
  10. `

  12. // xs = [{ user_id: 1, name: 'Murray', age: 68 }]
  13. ```

Please note that queries are first executed when awaited – or instantly by using [.execute()](#execute).


Query parameters


Parameters are automatically extracted and handled by the database so that SQL injection isn't possible. No special handling is necessary, simply use tagged template literals as usual.

  1. ```js
  2. const name = 'Mur'
  3.     , age = 60

  5. const users = await sql`
  6.   select
  7.     name,
  8.     age
  9.   from users
  10.   where
  11.     name like ${ name + '%' }
  12.     and age > ${ age }
  13. `
  14. // users = [{ name: 'Murray', age: 68 }]
  15. ```

Be careful with quotation marks here. Because Postgres infers column types, you do not need to wrap your interpolated parameters in quotes like '${name}'. This will cause an error because the tagged template replaces ${name} with $1 in the query string, leaving Postgres to do the interpolation. If you wrap that in a string, Postgres will see '$1' and interpret it as a string as opposed to a parameter.


Dynamic column selection


  1. ```js
  2. const columns = ['name', 'age']

  4. await sql`
  5.   select
  6.     ${ sql(columns) }
  7.   from users
  8. `

  10. // Which results in:
  11. select "name", "age" from users
  12. ```

Dynamic inserts


  1. ```js
  2. const user = {
  3.   name: 'Murray',
  4.   age: 68
  5. }

  7. await sql`
  8.   insert into users ${
  9.     sql(user, 'name', 'age')
  10.   }
  11. `

  13. // Which results in:
  14. insert into users ("name", "age") values ($1, $2)

  16. // The columns can also be given with an array
  17. const columns = ['name', 'age']

  19. await sql`
  20.   insert into users ${
  21.     sql(user, columns)
  22.   }
  23. `
  24. ```

You can omit column names and simply execute sql(user) to get all the fields from the object as columns. Be careful not to allow users to supply columns that you do not want to be inserted.

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().

  1. ```js
  2. const users = [{
  3.   name: 'Murray',
  4.   age: 68,
  5.   garbage: 'ignore'
  6. },
  7. {
  8.   name: 'Walter',
  9.   age: 80
  10. }]

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

  14. // Is translated to:
  15. insert into users ("name", "age") values ($1, $2), ($3, $4)

  17. // Here you can also omit column names which will use object keys as columns
  18. await sql`insert into users ${ sql(users) }`

  20. // Which results in:
  21. insert into users ("name", "age") values ($1, $2), ($3, $4)
  22. ```

Dynamic columns in updates

This is also useful for update queries
  1. ```js
  2. const user = {
  3.   id: 1,
  4.   name: 'Murray',
  5.   age: 68
  6. }

  8. await sql`
  9.   update users set ${
  10.     sql(user, 'name', 'age')
  11.   }
  12.   where user_id = ${ user.id }
  13. `

  15. // Which results in:
  16. update users set "name" = $1, "age" = $2 where user_id = $3

  18. // The columns can also be given with an array
  19. const columns = ['name', 'age']

  21. await sql`
  22.   update users set ${
  23.     sql(user, columns)
  24.   }
  25.   where user_id = ${ user.id }
  26. `
  27. ```