pg-script

Yet another SQL builder for JavaScript and Postgres. The API was designed to be very similar to writing plain SQL with almost no abstraction layers, while still maintaining some conveniences.

Installation

npm install pg-script

Usage

import { Pool } from 'pg'
import { DatabasePool } from 'pg-script'

const connectionString = process.env.DATABASE_URL
const pool = new Pool({ connectionString })
const db = new DatabasePool(pool)

async function main () {
  const status = 'published'

  // Run the query
  const { rows } = await db
    .SELECT`id, title`
    .FROM`posts`
    .WHERE`status = ${status}`
    .ORDER_BY`publish_date DESC`

  // Just generate the SQL and the parameters
  const [sql, params] = db
    .SELECT`id, title`
    .FROM`posts`
    .WHERE`status = ${status}`
    .ORDER_BY`publish_date DESC`
    .toSql()

  // Just generate the SQL
  const [sql, params] = db
    .SELECT`id, title`
    .FROM`posts`
    .WHERE`status = ${status}`
    .ORDER_BY`publish_date DESC`
    .toString()
}

Examples

Select

SELECT id, title FROM "posts" WHERE status = $1 ORDER BY publish_date DESC

const status = 'published'

db
  .SELECT`id, title`
  .FROM`posts`
  .WHERE`status = ${status}`
  .ORDER_BY`publish_date DESC`

// OR

db
  .SELECT`id, title`
  .FROM`posts`
  .WHERE({ status: 'published' })
  .ORDER_BY`publish_date DESC`

Update

UPDATE "posts" SET status = $1 WHERE id = $2

db
  .UPDATE`posts`
  .SET`status = ${status}`
  .WHERE`id = ${id}`

db
  .UPDATE`posts`
  .SET({ status: 'published' })
  .WHERE({ id: postId })

Delete

DELETE FROM "posts" WHERE id = $1

db
  .DELETE_FROM`posts`
  .WHERE`id = ${id}`
  .RETURNING`title`

Insert

INSERT INTO "posts" ("title", "status") VALUES ($1, $2) RETURNING id

db
  .INSERT_INTO`posts`
  .VALUES({
    title: 'Hello, world',
    status: 'published'
  })
  .RETURNING`id`