sql-pg

library10.4.1jsnpmunverified

sql-pg is a PostgreSQL query builder and ORM-like library built on top of the `pg` driver. It allows complex queries using tagged template literals (`` sql`...` ``) that look like native SQL and are secure by design (parameterized queries). Provides simple data manipulation methods (insert, update, delete) and selection methods (any, many, one, oneOrNone) inspired by pg-promise. Current stable version is 10.4.1, released on GitHub. Release cadence is irregular, with a major version bump to 10.0.0 in early 2023. Key differentiators: uses ES modules or CommonJS, works with PostgreSQL only, has a small API surface, and supports custom tag helpers. Peer dependencies required: pg, debug, and dotenv-safe.

database

npm install sql-pg

github ↗package ↗homepage ↗

INSTALL

IMPORT

SIG · SQL-PG

sql-pg

databasejavascriptv10.4.1

harness data pending

Install & Compatibility

Where this runs

No compatibility data collected yet for this library.

Code

Verified usage

Verified import paths — ran on the pinned version, not inferred.

default (sql-pg)

✓ import sql from 'sql-pg'

✗ const sql = require('sql-pg')()

The library returns a function that must be called to initialize. Using require() without calling returns the function, not the object.

Initialize with options

✓ import sql from 'sql-pg' const db = sql({ connectionString: 'postgres://...' })

✗ const db = require('sql-pg')({ connectionString: 'postgres://...' })

The default export is a factory function. CommonJS users must call it.

sql tag (tagged template literal)

✓ sql`SELECT * FROM users WHERE id = ${id}`

✗ sql('SELECT * FROM users WHERE id = ' + id, values)

The sql tag is a function property of the initialized db object. Do not use string concatenation.

Shows basic initialization, insert, selection, tagged template query, and transaction usage.

import sql from 'sql-pg' const db = sql() // uses DATABASE_URL env var // Insert a user const id = await db.insert('users', { name: 'Alice', email: 'alice@example.com' }) // Select all users const users = await db.any('users', ['name', 'email']) console.log(users) // Complex query using sql tag const name = 'Alice' const result = await db.oneOrNone( db.sql`SELECT * FROM users WHERE name = ${name}` ) console.log(result) // Transaction db.task(async t => { await t.insert('users', { name: 'Bob' }) await t.insert('users', { name: 'Charlie' }) })

Debug

Known issues

breakingsql-pg v10 requires pg@^8.7.1 and Node.js >=14.0.0. Older Node.js versions not supported.

fix

Upgrade Node.js to v14 or later and pg to 8.7.1+.

affects: >=10.0.0

breakingThe `sql` function is no longer directly exported. Instead, it is a property of the initialized db object. Using `require('sql-pg').sql` will fail.

fix

Use `db.sql` after calling the factory function.

affects: >=10.0.0

deprecatedThe `migrations` feature was deprecated in v9 and removed in v10. Use an external migration tool like node-pg-migrate or db-migrate.

fix

Remove any usage of `sql.migrate()` or migration-related config.

affects: >=10.0.0

gotchaThe `insert` method returns the inserted row's ID column value. The column is assumed to be 'id'. Use `returning` option to customize.

fix

Pass `{ returning: 'user_id' }` as third argument to insert if your primary key is not 'id'.

affects: all

gotchaThe `any` method always returns an array, even if zero rows. Use `one` or `oneOrNone` for single row expectations.

fix

Use `db.one(...)` for exactly one row, `db.oneOrNone(...)` for zero or one row.

affects: all

Errors

Common errors & fixes

TypeError: sql is not a function

Importing sql-pg with default import without calling the factory function.

fix

Correct: `const sql = require('sql-pg')()` with parentheses.

error: relation "users" does not exist

Table name is not properly quoted or case-sensitive.

fix

Use identifiers with proper quoting: `db.sql`${db.sql.identifier('Users')}``

Cannot find module 'pg'

Missing peer dependency pg.

fix

Run `npm install pg` (and debug, dotenv-safe if needed).

Upgrade

Version history

10.4.1latest on npm

Audit

Dependencies

pgrequiredPostgreSQL database driver

debugoptionalDebugging utility

dotenv-safeoptionalEnvironment variable loading

Agent activity

10 hits · last 30 days

node