Nagging Penguin Matriarchs

    node-postgres
    TypeScript icon, indicating that this package has built-in type declarations

    0.6.2 • Public • Published

    postgres

    PostgreSQL client for node.js.

    Install

    $ npm i node-postgres

    Status

    • client
    • pool
    • ssl
    • end
    • transactions

    Useage

    Client

    const { Client } = require('node-postgres');
     
    (async () => {
      const client = new Client({
        user: 'postgres',
        host: '127.0.0.1',
        database: 'test',
        password: 'esri@123',
        port: 5432
      });
      
      await client.connect();
     
      const res = await client.query('SELECT * from users');
      console.log(res);
      await client.end();
    })().catch(console.error);

    ssl

    const { Client, Pool } = require('node-postgres');
    const fs = require('fs');
     
    (async () => {
      const client = new Client({
        user: 'postgres',
        host: 'localhost',
        database: 'test',
        password: 'esri@123',
        port: 5432,
        ssl: {
          rejectUnauthorized: false,
          // ca: fs.readFileSync('c:/my/server.crt').toString(),
          key: fs.readFileSync('c:/my/server.key').toString(),
          cert: fs.readFileSync('c:/my/server.crt').toString(),
        }
      });
      
      await client.connect();
      
      const res = await client.query('SELECT * from users');
      console.log(res);
      await client.end();
    })().catch(console.error);

    Pool

    The client pool allows you to have a reusable pool of clients you can check out, use, and return. You generally want a limited number of these in your application and usually just 1. Creating an unbounded number of pools defeats the purpose of pooling at all.

    Checkout, use, and return

    const { Pool } = require('node-postgres');
     
    (async () => {
      const pool = new Pool({
        user: 'postgres',
        host: '127.0.0.1',
        database: 'test',
        password: 'esri@123',
        port: 5432
      });
      
      const client = await pool.connect();
      try {
        const res = await client.query('SELECT * from users');
        console.log(res);
      } catch (error) {
        console.log(error);
      } finally {
        client.release();
      }
    })().catch(console.error);

    You must always return the client to the pool if you successfully check it out, regardless of whether or not there was an error with the queries you ran on the client. If you don't check in the client your application will leak them and eventually your pool will be empty forever and all future requests to check out a client from the pool will wait forever.

    Single query

    If you don't need a transaction or you just need to run a single query, the pool has a convenience method to run a query on any available client in the pool. This is the preferred way to query with node-postgres if you can as it removes the risk of leaking a client.

    const { Pool } = require('node-postgres');
     
    (async () => {
      const pool = new Pool({
        user: 'postgres',
        host: '127.0.0.1',
        database: 'test',
        password: 'esri@123',
        port: 5432
      });
      
      const res = await pool.query('SELECT * from users');
      console.log(res);
    })().catch(console.error);

    Transation

    To execute a transaction with node-postgres you simply execute BEGIN / COMMIT / ROLLBACK queries yourself through a client. Because node-postgres strives to be low level and un-opinionated, it doesn't provide any higher level abstractions specifically around transactions.

    You must use the same client instance for all statements within a transaction. PostgreSQL isolates a transaction to individual clients. This means if you initialize or use transactions with the pool.query method you will have problems. Do not use transactions with the pool.query method.

    const { Pool } = require('node-postgres');
    const pool = new Pool({
      user: 'postgres',
      host: '127.0.0.1',
      database: 'test',
      password: 'esri@123',
      port: 5432
    });
     
    (async () => {
      // note: we don't try/catch this because if connecting throws an exception
      // we don't need to dispose of the client (it will be undefined)
      const client = await pool.connect()
      try {
        await client.query('BEGIN')
        await client.query(`INSERT INTO users(id, name) VALUES(1, 'zfx')`);
        await client.query(`INSERT INTO users(id, name) VALUES(2, 'zfx2')`);
        await client.query(`DELETE FROM users WHERE id=0`);
        await client.query('COMMIT');
      } catch (e) {
        await client.query('ROLLBACK')
        throw e
      } finally {
        client.release()
      }
    })().catch(console.error);

    Shutdown

    To shut down a pool call pool.end() on the pool. This will wait for all checked-out clients to be returned and then shut down all the clients and the pool timers.

    const { Pool } = require('node-postgres');
     
    (async () => {
      const pool = new Pool({
        user: 'postgres',
        host: '127.0.0.1',
        database: 'test',
        password: 'esri@123',
        port: 5432
      });
      
      const res = await pool.query('SELECT * from users');
      console.log(res);
      await pool.end()
      // will throw error
      await pool.query('SELECT * from users');
    })().catch(console.error);

    The pool will return errors when attempting to check out a client after you've called pool.end() on the pool.

    constructor

    client constructor options

    import http = require('http');
    import https = require('https');
     
    interface Options {
      user: string,
      password: string,
      database: string, 
      port: number, 
      // passed directly to node.TLSSocket, supports all tls.connect options
      ssl?: any, 
      // number of milliseconds to wait before timing out when connecting a new client
      // by default this is 0 which means no timeout
      connectionTimeoutMillis?: int,
    }

    pool constructor options

    Every field of the config object is entirely optional. The config passed to the pool is also passed to every client instance within the pool when the pool creates that client.

    interface Options {
      // all valid client config options are also valid here
      // in addition here are the pool specific configuration parameters:
     
      // number of milliseconds a client must sit idle in the pool and not be checked out
      // before it is disconnected from the backend and discarded
      // default is 10000 (10 seconds) - set to 0 to disable auto-disconnection of idle clients
      idleTimeoutMillis?: int,
      // maximum number of clients the pool should contain
      // by default this is set to 10.
      max?: int,
      // Determines the pool's action when no connections are available and the limit has 
      // been reached. If true, the pool will queue the connection request and call it 
      // when one becomes available. If false, the pool will immediately call back with 
      // an error. (Default: true)
      waitForConnections?: boolean, 
      // number of milliseconds to wait before timing out when waiting for connection.
      // by default this is 0 which means no timeout
      waitForConnectionsMillis?: int, 
      // The maximum number of connection requests the pool will queue before returning 
      // an error from getConnection. If set to 0, there is no limit to the number of 
      // queued connection requests. (Default: 0)
      queueLimit?: int,
    }

    Test

    $ npm test

    Install

    npm i node-postgres

    DownloadsWeekly Downloads

    2,966

    Version

    0.6.2

    License

    MIT

    Unpacked Size

    49.2 kB

    Total Files

    17

    Last publish

    Collaborators

    • fuxingzhang