##          ##
        ##      ##
      ##############
    ####  ######  ####
  ######################
  ##  ##############  ##
  ##  ##          ##  ##
        ####  ####

fastify-pg

Fastify PostgreSQL plugin inspired by fastify-postgres and typeorm.

Dependencies:

• fastify
• fastify-plugin
• pg

Install

npm i fastify fastify-plugin fastify-pg pg --save

Usage

const pg = require('fastify-pg')

or

import pg from 'fastify-pg'

Register plugin

fastify.register(pg, options)

This plugin will add the pg namespace in your Fastify instance, with the following properties:

const {
  Client, // client constructor for a single query
  pool, // pool instance
  connect, // function to get a connection from the pool
  query, // utility to perform a query WITHOUT a transaction
  transaction, // utility to perform multiple queries WITH a transaction
  QueryBuilder // query builder
} = fastify.pg

For examples, please see the fastify-postgres and node-postgres documentation.

QueryBuilder

const { QueryBuilder, transaction } = fastify.pg

.table(tableName)

await QueryBuilder
  .of(fastify.pg)
  .table('users')
  .execute()

TABLE users;

.insert()

await QueryBuilder
  .of(fastify.pg)
  .insert()
  .into('users')
  .defaultValues()
  .returning(['*'])
  .execute()

INSERT INTO users DEFAULT VALUES RETURNING *;

.select(elements)

await QueryBuilder
  .of(fastify.pg)
  .select(['*'])
  .from('users')
  .where('users.id = :id', {id: 1})
  .andWhere('users.is_active = :is_active', {is_active: true})
  .orWhere('users.deleted = :deleted', {deleted: false})
  .getMany()

SELECT * FROM users WHERE users.id = 1 AND users.is_active = true OR users.deleted = false;

await QueryBuilder
  .of(fastify.pg)
  .insert()
  .into('users')
  .columns(['first_name', 'last_name'])
  .values([[`'Artem'`, `'Tolstykh'`], [`'Maksym'`, `'Bezruchko'`]])
  .returning(['*'])
  .execute()

INSERT INTO users (first_name, last_name) VALUES ('Artem', 'Tolstykh'), ('Maksym', 'Bezruchko') RETURNING *;

.update(tableName)

await QueryBuilder
  .of(fastify.pg)
  .update('users')
  .set({first_name: `'Artem'`, last_name: `'Tolstykh'`})
  .where('id = :id', {id: 1})
  .execute()

UPDATE users SET first_name = 'Artem', last_name = 'Tolstykh' WHERE id = 1;

.delete()

await QueryBuilder
  .of(fastify.pg)
  .delete()
  .from('users')
  .execute()

DELETE FROM users;

await QueryBuilder
  .of(fastify.pg)
  .delete()
  .from('users')
  .where('id = :id', {id: 1})
  .returning(['*'])
  .execute()

DELETE FROM users WHERE id = 1 RETURNING *;

.innerJoin(tableName, expression)

await QueryBuilder
  .of(fastify.pg)
  .select(['users.*', 'photos.*'])
  .from('users')
  .innerJoin('photos', 'photos.user = users.id')
  .andWhere('photos.isRemoved = :isRemoved', {isRemoved: false})
  .where('users.name = :username', {username: `'arttolstykh'`})
  .getOne()

SELECT users.*, photos.* FROM users INNER JOIN photos ON photos.user = users.id AND photos.isRemoved = false WHERE users.name = 'arttolstykh';

transaction

transaction(async client => {
    const id 
        = await QueryBuilder
        .of(client, ['arttolstykh'])
        .insert()
        .into('users')
        .columns(['username'])
        .values([['$1']])
        .returning(['id'])
        .execute()
    // etc
    return id
})

BEGIN;
INSERT INTO users (username) VALUES ('arttolstykh') RETURNING id;
-- etc
COMMIT;

QueryBuilder API

• of
• with
• withRecursive
• insert
• table
• select
• update
• delete
• into
• from
• innerJoin
• set
• where
• andWhere
• orWhere
• columns
• values
• defaultValues
• limit
• offset
• groupBy
• orderBy
• onConflict
• returning
• getQuery
• execute
• getOne
• getMany

License

Licensed under MIT.