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.
npm install pg-scriptYet 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.
``
npm install pg-script
`typescript
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
.SELECTid, title
.FROM
.WHERE
.ORDER_BY
// Just generate the SQL and the parameters
const [sql, params] = db
.SELECTid, title
.FROM
.WHERE
.ORDER_BY
.toSql()
// Just generate the SQL
const sql = db
.SELECTid, title
.FROM
.WHERE
.ORDER_BY
.toString()
}
`sql`
SELECT id, title FROM "posts" WHERE status = $1 ORDER BY publish_date DESC
`typescript
const status = 'published'
db
.SELECTid, title
.FROM
.WHERE
.ORDER_BY
// OR
db
.SELECTid, title
.FROM
.WHERE({ status: 'published' })
.ORDER_BY
`sql`
UPDATE "posts" SET status = $1 WHERE id = $2
`typescriptposts
db
.UPDATE
.SET
.WHERE
`typescriptposts
db
.UPDATE
.SET({ status: 'published' })
.WHERE({ id: postId })
`sql`
DELETE FROM "posts" WHERE id = $1
`typescriptposts
db
.DELETE_FROM
.WHERE
.RETURNING
`sql`
INSERT INTO "posts" ("title", "status") VALUES ($1, $2) RETURNING id
`typescriptposts
db
.INSERT_INTO
.VALUES({
title: 'Hello, world',
status: 'published'
})
.RETURNING
`sql`
SELECT id, name FROM "users" WHERE (EXISTS (SELECT id FROM "posts" WHERE author_id = users.id)) ORDER BY name ASC
`typescriptid, name
db
.SELECT
.FROM
.WHERE(EXISTS(SELECT.FROMposts.WHEREauthor_id = users.id))name ASC
.ORDER_BY
Without helper methods, running the query returns the default pg result:
`typescriptid, name
// Destructuring rows
const { rows, rowCount } = await db
.SELECT
.FROM
// Destructuring the first row
const { rows: [firstRow] } = await db
.SELECTname
.FROM
.WHERE
Helper methods are available to reduce the need for destructuring for common use cases.
Returns the first result. If there is none, throws an error. Accepts an optional error message.
`typescriptname
const row = await db
.SELECT
.FROM
.WHERE
.find({ error: })`
Returns the first result. If there is none, returns null instead.
`typescriptname
const row = await db
.SELECT
.FROM
.WHERE
.first()
Returns the rows of the result.
`typescriptid, name
const rows = await db
.SELECT
.FROM
.list()
Returns the rows along with a count. Useful for pagination.
`typescript
const pageNumber = 0
const pageSize = 10
const { rows, count } = await db
.SELECTid, name
.FROM
.page(pageNumber, pageSize)