SQL Query Builder 🧩

A lightweight, function-based, chainable SQL query builder for Node.js projects using MySQL/MariaDB pool connections and raw queries. Perfect for building dynamic filters, pagination, and safe SQL operations — without a full-blown ORM.

📦 Features

• ✅ Function-based & chainable API
• ✅ Safe parameterized queries (? bindings)
• ✅ Works seamlessly with MySQL pool.execute / pool.query
• ✅ Supports: SELECT, INSERT, UPDATE, DELETE
• ✅ Clean dynamic filter generation
• ✅ Pagination & ordering support
• ✅ Grouping (.groupBy(...)) & HAVING (.having(...))
• ✅ Total-count in one call (.build("count"))
• ✅ SQL Injection Protection with input validation
• ✅ Smart WHERE clause detection (handles existing WHERE clauses)
• ✅ Comprehensive error handling with clear messages

🔧 Installation

npm install @trithanka/sql-builder

🚀 Usage

🔍 SELECT Examples

Basic SELECT with Filters (Without Count)

const { createSelectBuilder } = require('@trithanka/sql-builder');

const { sql, values } = createSelectBuilder('SELECT * FROM users')
  .where('status = ?', 'active')
  .where('age >= ?', 18)
  .where('created_at >= ?', '2024-01-01')
  .orderBy('created_at', 'DESC')
  .paginate(10, 0)
  .build();

console.log('SQL:', sql);
console.log('Values:', values);
// Output:
// SQL: SELECT * FROM users WHERE status = ? AND age >= ? AND created_at >= ? ORDER BY created_at DESC LIMIT ? OFFSET ?
// Values: ['active', 18, '2024-01-01', 10, 0]

const [rows] = await pool.execute(sql, values);

Advanced SELECT with Grouping and Count

const { createSelectBuilder } = require('@trithanka/sql-builder');

const { 
  sql, 
  values, 
  countSql, 
  countValues 
} = createSelectBuilder(`
    SELECT seller_id, COUNT(*) AS sales_count, SUM(amount) AS total_sales
    FROM orders
`)
  .where('order_date >= ?', '2024-01-01')
  .where('status = ?', 'completed')
  .groupBy('seller_id')
  .having('COUNT(*) >= ?', 5)
  .having('SUM(amount) >= ?', 1000)
  .orderBy('total_sales', 'DESC')
  .paginate(20, 40)
  .build('count');

console.log('Main SQL:', sql);
console.log('Main Values:', values);
console.log('Count SQL:', countSql);
console.log('Count Values:', countValues);

// Get paginated results
const [rows] = await pool.execute(sql, values);

// Get total count for pagination
const [[{ total }]] = await pool.execute(countSql, countValues);
console.log('Total records:', total);

SELECT with Existing WHERE Clause

const { createSelectBuilder } = require('@trithanka/sql-builder');

// Base SQL already has WHERE clause
const { sql, values } = createSelectBuilder(`
    SELECT * FROM users 
    WHERE status = 'active' AND role = 'admin'
`)
  .where('age > ?', 25)
  .where('department = ?', 'IT')
  .orderBy('name', 'ASC')
  .build();

console.log('SQL:', sql);
// Output: SELECT * FROM users WHERE status = 'active' AND role = 'admin' AND age > ? AND department = ? ORDER BY name ASC

Complex SELECT with Multiple Conditions

const { createSelectBuilder } = require('@trithanka/sql-builder');

const filters = {
  status: 'active',
  role: 'user',
  fromDate: '2024-01-01',
  toDate: '2024-12-31',
  minAge: 18,
  maxAge: 65,
  department: 'engineering',
  limit: 50,
  offset: 0
};

const { sql, values, countSql, countValues } = createSelectBuilder(`
    SELECT u.id, u.name, u.email, u.created_at, 
           COUNT(o.id) as order_count, SUM(o.amount) as total_spent
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
`)
  .where('u.status = ?', filters.status)
  .where('u.role = ?', filters.role)
  .where('u.age >= ?', filters.minAge)
  .where('u.age <= ?', filters.maxAge)
  .where('u.department = ?', filters.department)
  .where('u.created_at >= ?', filters.fromDate)
  .where('u.created_at <= ?', filters.toDate)
  .groupBy('u.id, u.name, u.email, u.created_at')
  .having('COUNT(o.id) > ?', 0)
  .orderBy('total_spent', 'DESC')
  .paginate(filters.limit, filters.offset)
  .build('count');

// Execute both queries
const [rows] = await pool.execute(sql, values);
const [[{ total }]] = await pool.execute(countSql, countValues);

console.log(`Found ${rows.length} users out of ${total} total`);

🆕 INSERT Examples

const { buildInsertQuery } = require('@trithanka/sql-builder');

// Single record insert
const { sql, values } = buildInsertQuery('users', {
  name: 'John Doe',
  email: '[email protected]',
  age: 30,
  status: 'active'
});

console.log('SQL:', sql);
console.log('Values:', values);
// Output:
// SQL: INSERT INTO users (name, email, age, status) VALUES (?, ?, ?, ?)
// Values: ['John Doe', '[email protected]', 30, 'active']

await pool.execute(sql, values);

✏️ UPDATE Examples

const { buildUpdateQuery } = require('@trithanka/sql-builder');

// Update with single condition
const { sql, values } = buildUpdateQuery(
  'users',
  { 
    name: 'John Smith', 
    email: '[email protected]',
    updated_at: new Date().toISOString()
  },
  'id = ?',
  [101]
);

console.log('SQL:', sql);
console.log('Values:', values);
// Output:
// SQL: UPDATE users SET name = ?, email = ?, updated_at = ? WHERE id = ?
// Values: ['John Smith', '[email protected]', '2024-01-15T10:30:00.000Z', 101]

await pool.execute(sql, values);

❌ DELETE Examples

const { buildDeleteQuery } = require('@trithanka/sql-builder');

// Delete with single condition
const { sql, values } = buildDeleteQuery('users', 'id = ?', [101]);

console.log('SQL:', sql);
console.log('Values:', values);
// Output:
// SQL: DELETE FROM users WHERE id = ?
// Values: [101]

await pool.execute(sql, values);

🛡️ Security Features

SQL Injection Protection

// ❌ This will throw an error (SQL injection attempt)
try {
  createSelectBuilder('SELECT * FROM users')
    .orderBy('id; DROP TABLE users; --', 'ASC')
    .build();
} catch (error) {
  console.log('SQL injection prevented:', error.message);
}

// ✅ This works safely
createSelectBuilder('SELECT * FROM users')
  .orderBy('id', 'ASC')
  .build();

Input Validation

// ❌ Invalid pagination values
try {
  createSelectBuilder('SELECT * FROM users')
    .paginate(-5, -10)
    .build();
} catch (error) {
  console.log('Invalid pagination rejected:', error.message);
}

// ❌ Invalid column names
try {
  createSelectBuilder('SELECT * FROM users')
    .orderBy('invalid;column;name', 'ASC')
    .build();
} catch (error) {
  console.log('Invalid column name rejected:', error.message);
}

Smart WHERE Detection

// Handles existing WHERE clauses correctly
const { sql } = createSelectBuilder(`
    SELECT * FROM users 
    WHERE status = 'active' -- This comment won't interfere
    AND role = 'admin'
`)
  .where('age > ?', 25)
  .build();

console.log('SQL:', sql);
// Output: SELECT * FROM users WHERE status = 'active' AND role = 'admin' AND age > ?

📁 Folder Structure

src/
├── selectBuilder.js    # SELECT query builder with count support
├── insertBuilder.js    # INSERT query builder
├── updateBuilder.js    # UPDATE query builder
├── deleteBuilder.js    # DELETE query builder
└── index.js           # Main exports

🧠 When Should You Use This?

• When you're using raw SQL (pool.execute) and need reusable filters
• When you want full control without the overhead of an ORM like Sequelize
• When building admin panels, dashboards, reports, or public APIs
• When writing secure SQL using parameter binding
• When you need both data and count queries for pagination
• When you want comprehensive input validation and SQL injection protection

🧪 Coming Soon

• .whereIn(field, [...values])
• .between(field, from, to)
• .like(field, pattern)
• .isNull(field) / .isNotNull(field)
• TypeScript support
• Support for other databases (PostgreSQL, SQLite)

📃 License

MIT © Trithanka