fastify / fastify-postgres Goto Github PK

Hi guys,

I suggest to use pg-native instead of pg, for high performance. It comes with the same interface of pg.

I think it can be great to add a transaction helper
Can do it next week, it's just a reminder for me or if someone wants to do it.

connect is not function

i try to follow the documentation but i got error .. how can i fix it thanks

MY controller

const fastify = require('fastify')()
fastify.register(require('fastify-postgres'), {
  connectionString: 'postgres://username:pass@localhost:5432/db'
})

exports.getAllProduct = async ( req, reply ) => {
	fastify.pg.connect(onConnect)

	function onConnect ( err, client, release ) {
		if ( err ) return reply.send( err )

		client.query(
			'SELECT id, first_name, last_name FROM person',
			function onResult ( err, result ) {
				release()
				reply.send(err || result)
			}
		)
	}
}

routes

async function routes( fastify, options ) {
	const ProductController = require('../controllers/product')
	fastify.get('/', ProductController.getAllProduct)
}

module.exports = routes;

const fastify = require('fastify')({
	logger: true
})

fastify.register(require('./api/routes/product'), {
	prefix: '/product'
});


fastify.listen(3000, ( err, address ) => {
	if ( err ) {
		fastify.log.error( err )
		process.exit( 1 )
	}
	fastify.log.info(`server listening on address ${ address }`)
})

THE ERROR

{
    "statusCode": 500,
    "error": "Internal Server Error",
    "message": "Cannot read property 'connect' of undefined"
}

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the bug has not already been reported

Fastify version

4.0.0

Plugin version

5.0.0

Node.js version

16.16.0

Operating system

macOS

Operating system version (i.e. 20.04, 11.3, 10)

12.4

Description

When attempting to use the transact route option using typescript, passing in the transact route options and referencing pg from the request object throws typescript type errors. This is because because pg has not been added to FastifyRequest and

interface FastifyPostgresRouteOptions {
  transact: boolean | string;
}

has not been added for pg for RouteShorthandOptions

Steps to Reproduce

fastify.post(
    '/',
    {
      pg: { transact: true }, // Throws type error for RouteShorthandOptions
    },
    async (req, _reply) => {
      req.pg.query('SELECT * FROM table') // Throws type error for FastifyRequest
    }
  );

Expected Behavior

Using the route transact option should not cause compile errors in typescript

I am writing a web mapping framework which uses fastify-postgres in many of the data endpoints. Certain endpoints are hit very frequently (hundreds of times from a single user).

Would you recommend to connect, query and release?

Or would you recommend to connect and store the connection in a global and then just query, query, query.

Both methods are working fine for me but I am concerned the connect handshake is taking time while with the always connected solution I am worried what happens if a connection errs.

The devDependency tap was updated from 12.1.1 to 12.1.2.

🚨 View failing branch.

This version is covered by your current version range and after updating it in your project the build failed.

tap is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Your Greenkeeper Bot 🌴

The devDependency tap was updated from 12.1.2 to 12.1.3.

🚨 View failing branch.

This version is covered by your current version range and after updating it in your project the build failed.

tap is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Your Greenkeeper Bot 🌴

The example below does not work because the query result is in the property rows, not result.
So it really should be const { rows } = await client.query(

fastify.get('/user/:id', async (req, reply) => {
  const client = await fastify.pg.connect()
  const { result } = await client.query(
    'SELECT id, username, hash, salt FROM users WHERE id=$1', [req.params.id],
  )
  client.release()
  return result
})

Can you update the pg.transact example to use more than one query? Transactions usually have more than one query...

The devDependency fastify was updated from 2.4.1 to 2.5.0.

🚨 View failing branch.

This version is covered by your current version range and after updating it in your project the build failed.

fastify is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Your Greenkeeper Bot 🌴

As the title says

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the bug has not already been reported

Fastify version

4.0.2

Plugin version

5.0.0

Node.js version

16.15.0

Operating system

macOS

Operating system version (i.e. 20.04, 11.3, 10)

12.4

Description

I was looking for how to disable rejectUnauthorized (or how to work with SSL in general) and found nothing in the documentation.

Enabling SSL by passing ssl=true as a query parameter worked, but only got me to the database rejecting a self-signed cert.

Further, I imagined that passing the standard structure for parameters into the config hash might work... I got as far as this (somewhat janky) config... which unfortunately didn't work.

fastify.register(require('@fastify/postgres'), {
  connectionString: process.env.DATABASE_URL + '?ssl=true',
  ssl: {
    rejectUnauthorized: false
  }
})

The specific use case is 'connect to a Heroku-hosted DB from a Heroku-hosted Fastify app' which is likely a common use case. Since the latest Postgres library defaults to rejectUnauthorized being 'true' I'm guessing this is what 'broke' things. (Though of course this behavior is more correct.)

Steps to Reproduce

Try to configure SSL.

Expected Behavior

I'd find some documentation for how to configure SSL.

Might be better suited for this package to add pg as a peer dependency to allow developers to use their preferred version of pg and to avoid mixing pg versions.

Any specific thoughts on this?

The plugin hardcode the name decorated as "pg", then you could not add any other postgres instance into it.

Maybe optional name field in option could override the decorating name is nice.

I'm imagining something like the following:

fastify.pg.transact(async (client) => {
  // all queries being done by this client are done within a transaction.
}) 

It would be nice to have a pre-made example on how to include fastify.pg on the index file and accessing the database from a handler file.

We still include all tests in our bundle, so don't skip those.

The tests are failing on Node 11 on travis.

I cannot understand why, so I'm disabling them.

It would be fantastic if someone could pick this up.

I have been upgrading fastify v2 to v3, and along with it, all the plugins we use.

In fastify-postgres v2, we were able to use both a named connection and an unnamed connection at the same time. We used fastify.pg for the primary db and fastify.pg.read for the read replica.

In the latest version, I noticed that there is a restriction placed to prevent such usage. While we are perfectly fine with changing the former to a named connection, I would like to know if there was any reason behind this change. Perhaps a design pattern that I am unaware of, that I would like to know.

Thanks.

I'd like to use https://www.npmjs.com/package/pg-range with my fastify-postgres.

By default, pg-range is patching the pg in order to work.

var pg = require("pg");
require("pg-range").install(pg);

Currently, I can't override the pg (at least properly) used by fastify-postgres

Could we make an option for overriding it ?

Improve coverage to reach 100%

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the issue has not already been raised

Issue

In the README, there is a section about pg-native support which states the following:

If you want maximum performance you can install pg-native, and pass native: true to the plugin options.

Is this claim of maximum performance still true in 2023? Does it still make sense to recommend pg-native to users?

My own findings:

According to this benchmark, the difference in performance is minimal, and actually js pg beats pg-native in 3 out of 4 tests. I've also heard anecdotal evidence from several engineers that they saw no difference in real life performance when switching between pg and pg-native.

On the other hand, there is a clear downside to using pg-native: setup can be a real pain.

Unless there's some evidence that pg-native is indeed still better, I propose changing the README to reflect the current situation, so users won't think they need pg-native for good performance.

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the bug has not already been reported

Fastify version

4.0.0

Plugin version

5.2.0

Node.js version

19.4

Operating system

macOS

Operating system version (i.e. 20.04, 11.3, 10)

13

Description

Currently, fastify schema validation and the pg: {transact: true} option are not compatible.

The validation schema is executed before the transaction initialization, which is expected behavior. However, when an error response with code FST_ERR_VALIDATION is sent, it triggers the onSend handler of the fastify-postgres hook, which assumes the initialization of a client.

This cause the Cannot read properties of null (reading 'release') error.

Steps to Reproduce

Configure your server:

fastify.post('/strictValidateFail', {
      schema: {
        body: {
          type: 'object',
          properties: {
            hello: { type: 'string' }
          },
          required: ['hello']
        }
      },
      pg: { transact: true }
    }, async (req, reply) => {
      await req.pg.query('THIS QUERY SHOULD NEVER BE EXECUTED WITH WRONG BODY', [])
      return 'never catched'
    });

Send a wrong body post

const response = await fastify.inject({
      url: '/strictValidateFail',
      method: 'POST',
      body: { notValid: 'json input' }
    })

the result is:

{"statusCode":400,"error":"Bad Request","message":"Cannot read properties of null (reading 'release')"}

Expected Behavior

The response should be

{"statusCode":400,"code":"FST_ERR_VALIDATION","error":"Bad Request","message":"body must have required property 'hello'"}

When i use the example code and run node server i get the error TypeError: fastify.register is not a function

const fastify = require('fastify');

fastify.register(require('fastify-postgres'), {
    connectionString: 'postgres://[email protected]/testdb'
});

// ...

The plugin does not work with the latest version of Fastify.

throw new Error(fastify-plugin - expected '${version}' fastify version, '${fastifyVersion}' is installed) Error: fastify-plugin - expected '>=0.13.1' fastify version, '1.0.0-rc.2' is installed at plugin (.../node_modules/fastify-plugin/index.js:24:13)

🚀 Feature Proposal

In most applications I've seen:

fastify.get('/something', function (req, reply) {
  return this.pg.transact(async (client) => {
      const { rows } = await client.query('SELECT id, username, hash, salt FROM users WHERE id=$1', [req.params.id])
      return rows
  })
})

I think we could greatly simplify this by having some dedicated syntax to avoid developer errors

3rd argument

fastify.get('/something', { pg: { transact: true } }, function (req, reply, client) {
  const { rows } = await client.query('SELECT id, username, hash, salt FROM users WHERE id=$1', [req.params.id])
  return rows
})

or even better

fastify.register(require('fastify-postgres'), {
  connectionString: 'postgres://postgres@localhost/postgres',
  wrapRoutesWithTransaction: true
})

fastify.get('/something', function (req, reply, client) {
  const { rows } = await client.query('SELECT id, username, hash, salt FROM users WHERE id=$1', [req.params.id])
  return rows
})

This can be implemented by having an 'onRoute' hook that wraps the handler.

Decorator

fastify.get('/something', { pg: { transact: true } }, function (req, reply) {
  const { rows } = await req.pg.query('SELECT id, username, hash, salt FROM users WHERE id=$1', [req.params.id])
  return rows
})

or even better

fastify.register(require('fastify-postgres'), {
  connectionString: 'postgres://postgres@localhost/postgres',
  wrapRoutesWithTransaction: true
})

fastify.get('/something', function (req, reply) {
  const { rows } = await req.pg.query('SELECT id, username, hash, salt FROM users WHERE id=$1', [req.params.id])
  return rows
})

This can be implemented by having a 'preHandler' hook that starts the transaction and an 'onSend' hook that commits or roll back it.

What do you prefer? The decorator is probably the more idiomatic approach, even it it will require a bit more work to implement.

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the issue has not already been raised

Issue

Why should i also install the pg module in addition to the @fastify/postgres module?

We are seeing this issue recently, where PostgreSQL has not been under much load but all DB statements are getting connection time-outs. We have set a DB_CONNECTTION_TIMEOUT set at 10 secs.

host: DB_HOST,
    port: DB_PORT,
    database: DB_NAME,
    user: DB_USER,
    password: DB_PASSWORD,
    pg: pg,
    max: 15,
    connectionTimeoutMillis: 10000,
    statementTimeout: 10000,
   keepAlive: true,
   idleTimeoutMillis: 10000

err: {
message: "timeout exceeded when trying to connect"
stack: "Error: timeout exceeded when trying to connect
at Timeout.setTimeout [as _onTimeout] (/app/node_modules/pg-pool/index.js:199:27)
at ontimeout (timers.js:436:11)
at tryOnTimeout (timers.js:300:5)
at listOnTimeout (timers.js:263:5)
at Timer.processTimers (timers.js:223:10)"
type: "Error"
}

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the bug has not already been reported

Fastify version

4.26.2

Plugin version

5.2.2

Node.js version

v18.20.0

Operating system

Linux

Operating system version (i.e. 20.04, 11.3, 10)

12

Description

I am working on a file structure consisting of server.js, route.js and profile.js and am trying to implement a PATCH request within profile.js to save data into the database.
Originally, I had the following code:

fastify.patch('/', {pg: {transact: true}}, async (request) => {
// Code
}

And attempted to implement a query:

try {
    // ...
    const edit = await request.pg.query(`UPDATE wallets
                                         SET username = $1
                                         WHERE phonenumber = $2
                                         AND useractive = true;`, [requestBody.username, accountId]);
    if (edit.rowCount === 0) {
        return {
            success: false,
            error: [
                {
                    message: "Account not found",
                    code: 400
                }
            ]
        }
    }
    // ...
} catch (e) {
    await request.pg.query("ROLLBACK;");
}

However, I am encountering a TypeError: Cannot read properties of null (reading 'query').
I've tried various alternatives, such as renaming {pg: {transact: true}} to {pg: {transact: 'accEdit'}} and changing the query structure from request.pg.query(...) to request.pg['accEdit'].query(...).
Despite these changes, I'm either receiving no errors from fastify-pg (though the test fails and changes are still made) or I'm getting an error stating that fastify.transact doesn't exist.
In addition to assistance with these errors, I would appreciate a practical documentation with Error Handling.

Steps to Reproduce

'use strict'

/**
 *
 * @param fastify
 * @return {Promise<void>}
 */
const editAccount = async (fastify) => {
    fastify.patch('/', {pg: {transact: 'accEdit'}}, async (request) => {
        try {
            const param = request.params.accountId;
            const authKey = request.headers["authorization"] || null;
            const authUsername = request.headers["x-auth-username"] || null;
            const requestBody = request.body || null;
            if (!authKey || !authUsername || !param || !requestBody) {
                return {
                    success: false,
                    error: [
                        {
                            message: "Account not found",
                            code: 400
                        }
                    ]
                };

            const edit = await request.pg.query(`UPDATE wallets
                                                 SET username = $1
                                                 WHERE phonenumber = $2
                                                   AND accountactive = true;`, [requestBody.username, accountId]);
            if (edit.rowCount === 0) {
                return {
                    success: false,
                    error: [
                        {
                            message: "Account not found",
                            code: 400
                        }
                    ]
                }
            }
            fastify.done(); // Thats the thing that makes the test error
            await request.pg.query("COMMIT;")
        } catch (e) {
            await fastify.pg.query("ROLLBACK;");
            fastify.log.error("There was an error updating the Data!\n"+e)

        } finally {
            await fastify.pg.release;
        }
    })
}

module.exports = editAccount;

Expected Behavior

It should get the error and handle it and not commit it to the Database

As of 772ff91 ( version 1.2.1 ), this script would not lead to working tests:

git clone https://github.com/fastify/fastify-postgres.git
cd fastify-postgres
npm i
npm run postgres &
sleep 10
npm test

Note: my docker pg image is cached and instantiated for a while before tests starts.

The tests logs:

> [email protected] test /path/to/fastify-postgres
> standard && tap test.js

test.js ...............................pg-native not installed, can't use native option - fallback to pg module
...ERROR:  relation "users" does not exist at character 13
STATEMENT:  INSERT INTO users(username) VALUES($1) RETURNING id
test.js ............................................. 35/40 11s
 fastify.pg.test use transact util with promise
 not ok (unnamed test)
   stack: |-
     Error: relation "users" does not exist
         at module.exports.Client._readError (/path/to/fastify-postgres/node_modules/pg-native/index.js:155:13)
         at module.exports.Client._emitResult (/path/to/fastify-postgres/node_modules/pg-native/index.js:178:12)
         at module.exports.Client._read (/path/to/fastify-postgres/node_modules/pg-native/index.js:220:31)
         at PQ.emit (events.js:182:13)
         at PQ.EventEmitter.emit (domain.js:442:20)
   message: relation "users" does not exist
   severity: ERROR
   code: 42P01
   position: '13'
   file: parse_relation.c
   line: '1160'
   routine: parserOpenTable
 
 fastify.pg.test use transact util with promise
 not ok test unfinished
   stack: |
     Object.<anonymous> (test.js:257:1)
   test: fastify.pg.test use transact util with promise
   at:
     line: 257
     column: 1
     file: test.js
     function: Object.<anonymous>
   source: |
     test('fastify.pg.test use transact util with promise', t => {
 
 not ok child test left in queue: t.test fastify.pg.test use transact util with callback
 not ok child test left in queue: t.test fastify.pg.test use transact util with commit callback
 not ok test count !== plan
   +++ found                                                          
   --- wanted                                                          
   -12                                                                
   +14                                                                
   results:
     ok: false
     count: 14
     pass: 11
     fail: 3
     bailout: false
     todo: 0
     skip: 0
     plan:
       start: 1
       end: 12
       skipAll: false
       skipReason: ''
       comment: ''
     failures:
       - ok: false
         id: 12
         time: 10028.298
         name: fastify.pg.test use transact util with promise
       - ok: false
         id: 13
         name: >-
           child test left in queue: t.test fastify.pg.test use transact util with
           callback
         parent: ''
       - ok: false
         id: 14
         name: >-
           child test left in queue: t.test fastify.pg.test use transact util with
           commit callback
         parent: ''

total ............................................... 35/40
 

 35 passing (11s)
 5 failing

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the bug has not already been reported

Fastify version

4.x.x

Plugin version

5.2.2

Node.js version

18.x

Operating system

Linux

Operating system version (i.e. 20.04, 11.3, 10)

22.04

Description

Hi, I just found out this weird behavior about pg-pool and it turned out a known issue on its repo.
When we shutdown/restart PostgreSQL instance, this module will force close our Fastify app! As my understanding, this happens due to lack of error handler in pg-pool, resulting unexpected termination event when we wrap the query logic using try catch. Due to my poor explanation, please read this issue: brianc/node-postgres#2439.

Steps to Reproduce

1. Deploy PostgreSQL
2. Deploy Fastify Instance with PostgreSQL support
3. Shutdown PostgreSQL

Expected Behavior

No response

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the bug has not already been reported

Fastify version

3.0.0

Plugin version

No response

Node.js version

16.16

Operating system

Windows

Operating system version (i.e. 20.04, 11.3, 10)

11.3

Description

There seems to be a typo in the Custom Postgres approach section where there "in" is typed as "oin"

Steps to Reproduce

N/A

Expected Behavior

N/A

🐛 Bug Report

I'm using async/await while inserting values into the database, but it's inserting the values twice for one table. I'm also receiving this error message in console Error: Release called on client which has already been released to the pool.

To Reproduce

Steps to reproduce the behavior:

1. I've created the project using fastify-cli fastify lang=ts generate
2. Installed fastify-postgres with npm i fastify-postgres
3. Added code in support.ts

import fastifyPostgres from "fastify-postgres";

fastify.register(fastifyPostgres, {
  connectionString: 'postgres://postgres@localhost/postgres'
})

4. Added code in routes\organization\create.ts (only added the handler portion)

handler: function (request, reply) {
      const {
        phone,
        organization_name,
        email,
        full_name,
        password,
        aadhar_number,
      }: any = request.body;

      bcrypt.genSalt(10, function (err, salt) {
        bcrypt.hash(password, salt, async function (err, hash) {
          if (err) {
            return reply.code(500).send({ success: false, error: err });
          }

          try {
            client = await fastify.pg.connect();

            // First create the organization
            const { rows, rowCount } = await client.query(
              "SELECT id FROM public.organizations ORDER BY id DESC LIMIT 1"
            );

            let org_id = 10000;
            if (rowCount > 0) {
              const temp = parseInt(rows[0].id);
              org_id = temp + 1;
            }

            // The below query executes twice
            await client.query(
              SQL`INSERT INTO public.organizations(id, title, phone, email) VALUES(${org_id}, ${organization_name}, ARRAY['91', ${phone}], ${email})`
            );

            // Second create the manager account
            const empid = "KTGEMP".concat(org_id.toString()).concat("01");
            const res = await client.query(
              "SELECT id FROM public.roles WHERE role = 'manager'"
            );
            await client.query(
              SQL`INSERT INTO public.employees(id, full_name, phone, email, password, role_id, aadhar_number, access_level, org_id) VALUES(${empid}, ${full_name}, ARRAY['91', ${phone}], ${email}, ${hash}, ${res.rows[0].id}, ${aadhar_number}, 20, ${org_id})`
            );

            client.release();
            return reply
              .code(201)
              .setCookie("token", fastify.createTokens({ id: empid }))
              .send({
                success: true,
                user: { email, org_id, role: "manager" },
              });
          } catch (error) {
            client.release();
            return reply.code(500).send({
              success: false,
              error,
            });
          }
        });
      });
    },
  });

Expected behavior

I expect organizations table to have 1 record and employees table to have one record. But right now, organization table has 2 records, while employees table has 1 record.

Your Environment

• node version: v14.15.4
• fastify version: >=3.10.1
• os: Windows, Linux
• fastify postgres: >=3.3.0
• any other relevant information