SQL Query Builder

Type-safe SQL query builder for Node.js. No ORM overhead, full SQL control.

Features

• Type-safe query building
• SQL injection prevention
• PostgreSQL, MySQL, SQLite support
• Raw SQL escape hatch
• Transaction support
• Query logging

Installation

npm install sql-query-builder

Quick Start

import { createQueryBuilder } from 'sql-query-builder';

const db = createQueryBuilder({
  dialect: 'postgres',
  connection: process.env.DATABASE_URL,
});

// Select
const users = await db
  .select('users')
  .columns(['id', 'name', 'email'])
  .where({ active: true })
  .orderBy('created_at', 'desc')
  .limit(10)
  .execute();

// Insert
const newUser = await db
  .insert('users')
  .values({ name: 'John', email: 'john@example.com' })
  .returning(['id', 'name'])
  .execute();

// Update
await db
  .update('users')
  .set({ name: 'Jane' })
  .where({ id: 1 })
  .execute();

// Delete
await db
  .delete('users')
  .where({ id: 1 })
  .execute();

Complex Queries

// Joins
const posts = await db
  .select('posts', 'p')
  .columns(['p.id', 'p.title', 'u.name as author'])
  .join('users', 'u', 'p.author_id = u.id')
  .where({ 'p.published': true })
  .execute();

// Subqueries
const activeAuthors = await db
  .select('users')
  .columns(['*'])
  .whereIn('id', 
    db.select('posts')
      .columns(['author_id'])
      .where({ published: true })
  )
  .execute();

// Aggregations
const stats = await db
  .select('orders')
  .columns([
    'status',
    db.raw('COUNT(*) as count'),
    db.raw('SUM(total) as revenue'),
  ])
  .groupBy('status')
  .having(db.raw('COUNT(*) > 10'))
  .execute();

Transactions

await db.transaction(async (trx) => {
  const user = await trx
    .insert('users')
    .values({ name: 'John' })
    .returning(['id'])
    .execute();

  await trx
    .insert('profiles')
    .values({ user_id: user[0].id, bio: 'Hello' })
    .execute();
});

Raw SQL

// When you need full control
const result = await db.raw(`
  SELECT u.*, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  WHERE u.created_at > $1
  GROUP BY u.id
  HAVING COUNT(p.id) > $2
`, [new Date('2024-01-01'), 5]);

License

MIT