pg-script
v0.3.2
Published
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.
Downloads
757
Readme
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`