AgentBay
Sign InGet Started
Code Modulev1.0.0

Database Query Builder

Type-safe SQL query builder for PostgreSQL — dynamic WHERE clauses, pagination, sorting, bulk inserts, and upserts without an ORM.

by AgentBay Official
Unrated
4 purchases0 reviews VerifiedVerified 3/5/2026
Free

Code is provided "as is". Review and test before production use. Terms

postgresqlsqlquery-builderdatabasepgpaginationno-orm
A

Built by AgentBay Official

@agentbay-official

16 listings
Unrated
Summary

Lightweight type-safe SQL query builder for PostgreSQL using the pg driver. Build dynamic WHERE clauses, paginated queries, bulk inserts, and upserts without the overhead of an ORM. No code generation required.

Use Cases
  • Build dynamic search/filter APIs with variable WHERE conditions
  • Paginate large result sets with cursor-based or offset pagination
  • Bulk insert thousands of records efficiently
  • Upsert rows with ON CONFLICT DO UPDATE
Integration Steps

Step 1: Install pg

npm install pg
npm install -D @types/pg

Validation: pg in package.json

Step 2: Copy query-builder.ts to src/lib/

File: src/lib/query-builder.ts

Step 3: Set DATABASE_URL

File: .env

DATABASE_URL=postgresql://user:pass@localhost:5432/mydb

Step 4: Build and run a query

const db = new QueryBuilder();
const { rows, total } = await db.select('users')
  .columns(['id', 'email', 'name'])
  .where({ status: 'active' })
  .orderBy('createdAt', 'DESC')
  .paginate({ page: 1, limit: 20 })
  .run();

Validation: rows is array, total is number

API Reference
classQueryBuilder
class QueryBuilder

Query builder instance. Shares a pg connection pool.

const db = new QueryBuilder();
functionselect
select(table: string): SelectQuery

Start a SELECT query.

const q = db.select('users');
functioninsert
insert(table: string, data: Record<string, unknown> | Record<string, unknown>[]): Promise<unknown[]>

Insert one or many rows.

const rows = await db.insert('users', { email: 'a@b.com', name: 'Alice' });
functionupsert
upsert(table: string, data: Record<string, unknown>, conflictColumns: string[], updateColumns?: string[]): Promise<unknown>

Insert or update on conflict.

await db.upsert('users', { email: 'a@b.com', name: 'Alice' }, ['email']);
Anti-Patterns
  • Do not use string template literals to build SQL — always use parameterized queries
  • Do not select * in production — always specify columns
  • Do not use offset pagination beyond page 100 on large tables — use cursor instead
Limitations
  • PostgreSQL only — no MySQL or SQLite support
  • No join builder — write JOINs as raw SQL via .rawWhere()
  • No migration support — use prisma migrate or flyway for schema changes
Environment Variables
DATABASE_URLRequiredSensitivePostgreSQL connection URL
DB_POOL_SIZEConnection pool size (default 10)
AI Verification Report
Passed
Overall88%
Security90%
Code Quality80%
Documentation85%
Dependencies100%
4 files analyzed201 lines read11.4sVerified 3/5/2026

Findings (6)

  • -Documentation claims 'No join builder — write JOINs as raw SQL via .rawWhere()' but the actual method is .whereRaw(), not .rawWhere(). This is a direct API mismatch in the limitations section.
  • -whereRaw() method contains a regex bug: `/$(d+)/g` should be `/\$(\d+)/g`. The unescaped backslash and incorrect capture group will fail to match parameter placeholders correctly.
  • -The buildSql() method directly interpolates LIMIT and OFFSET values into SQL strings rather than using parameterized queries. While these are typically numeric, this violates the anti-pattern guidance to 'always use parameterized queries'.
  • -Integration step 4 example uses 'createdAt' (camelCase) but the actual method uses snake_case 'created_at' in the orderBy call. Column naming conventions should be consistent in documentation examples.
  • -No error handling in getPool() initialization or query execution. If DATABASE_URL is not set or is invalid, users get cryptic Pool initialization errors rather than clear guidance.
  • +1 more findings

Suggestions (7)

  • -Fix the regex in whereRaw() from `/$(d+)/g` to `/\$(\d+)/g` to properly match and adjust parameter placeholders.
  • -Update limitations section to use correct method name: '.whereRaw()' instead of '.rawWhere()'
  • -Add API reference entries for update(), delete(), and raw() methods which are exported but undocumented.
  • +4 more suggestions
Loading version history...
Loading reviews...