Big News: Socket raises $60M Series C at a $1B valuation to secure software supply chains for AI-driven development.Announcement →
Sign In

@trithanka/sql-builder

Package Overview
Dependencies
Maintainers
1
Versions
12
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

@trithanka/sql-builder - npm Package Compare versions

Comparing version
1.0.8
 to
2.0.0
+9
-4
exaples/select.js

@@ -14,3 +14,4 @@ const { createSelectBuilder } = require('../src');

const { sql, values } = builder
// Regular data query
const { sql, values, countSql, countValues } = builder
.where('status = ?', filters.status)

@@ -20,7 +21,11 @@ .where('role = ?', filters.role)

.where('created_at <= ?', filters.toDate)
.groupBy('role') // optional group
.having('COUNT(*) > ?', 1) // optional having
.orderBy('created_at', 'desc')
.paginate(filters.limit, filters.offset)
.build();
.build('count'); // pass 'count' if you also want count SQL
console.log(sql);
console.log(values);
console.log('Main SQL:', sql);
console.log('Main Values:', values);
console.log('Count SQL:', countSql);
console.log('Count Values:', countValues);
+1
-1
package.json
{
"name": "@trithanka/sql-builder",
"version": "1.0.8",
"version": "2.0.0",
"description": "A lightweight, function-based, chainable SQL query builder for Node.js using MySQL pool connections.",

@@ -5,0 +5,0 @@ "main": "src/index.js",

+22
-16
README.md

@@ -21,2 +21,4 @@ # SQL Query Builder 🧩

- ✅ Pagination & ordering support
- ✅ **Grouping** (`.groupBy(...)`) & **HAVING** (`.having(...)`)
- ✅ **Total-count** in one call (`.build("count")`)

@@ -35,3 +37,3 @@ ---

### 🔍 SELECT (with filters, order, pagination)
### 🔍 SELECT (filters, grouping, pagination, total count)

@@ -41,19 +43,23 @@ ```js

const filters = {
status: 'active',
role: 'admin',
fromDate: '2024-01-01',
toDate: '2024-12-31'
};
const { sql, values } = createSelectBuilder('SELECT * FROM users')
.where('status = ?', filters.status)
.where('role = ?', filters.role)
.where('created_at >= ?', filters.fromDate)
.where('created_at <= ?', filters.toDate)
.orderBy('created_at', 'desc')
const {
sql,
values,
countSql,
countValues
} = createSelectBuilder(`
SELECT seller_id, COUNT(*) AS sales_count
FROM orders
`)
.where('order_date >= ?', '2025-01-01')
.groupBy('seller_id')
.having('COUNT(*) >= ?', 5)
.orderBy('sales_count', 'DESC')
.paginate(10, 0)
.build();
.build('count');
await pool.execute(sql, values);
// Paginated rows
const [rows] = await pool.execute(sql, values);
// Total number of seller groups
const [[{ total }]] = await pool.execute(countSql, countValues);
```

@@ -60,0 +66,0 @@ 

+86
-51
src/selectBuilder.js
function createSelectBuilder(baseSql) {
let sql = baseSql;
const whereClauses = [];
const values = [];
let orderByClause = '';
let limitClause = '';
let offsetClause = '';
const builder = {
where(condition, value) {
if (value !== undefined && value !== null && value !== '') {
whereClauses.push(condition);
values.push(value);
}
return builder;
},
orderBy(column, direction = 'ASC') {
if (column) {
orderByClause = ` ORDER BY ${column} ${direction.toUpperCase()}`;
}
return builder;
},
paginate(limit, offset = 0) {
if (limit !== undefined) {
limitClause = ' LIMIT ?';
offsetClause = ' OFFSET ?';
values.push(Number(limit), Number(offset));
}
return builder;
},
build() {
let finalSql = sql;
if (whereClauses.length > 0) {
finalSql += ' WHERE ' + whereClauses.join(' AND ');
}
finalSql += orderByClause;
finalSql += limitClause;
finalSql += offsetClause;
return { sql: finalSql, values };
let sql = baseSql;
const whereClauses = [];
const values = [];
let groupByClause = '';
const havingClauses = [];
let orderByClause = '';
let limitClause = '';
let offsetClause = '';
const builder = {
where(condition, value) {
if (value != null && value !== '') {
whereClauses.push(condition);
values.push(value);
}
};
return builder;
}
module.exports = createSelectBuilder;
return builder;
},
groupBy(...columns) {
if (columns.length) {
groupByClause = ' GROUP BY ' + columns.join(', ');
}
return builder;
},
having(condition, value) {
if (condition && value != null) {
havingClauses.push(condition);
values.push(value);
}
return builder;
},
orderBy(column, direction = 'ASC') {
if (column) {
orderByClause = ` ORDER BY ${column} ${direction.toUpperCase()}`;
}
return builder;
},
paginate(limit, offset = 0) {
if (limit != null) {
limitClause = ' LIMIT ?';
offsetClause = ' OFFSET ?';
values.push(Number(limit), Number(offset));
}
return builder;
},
build(mode) {
// 1) Build the “data” SQL
let finalSql = sql;
if (whereClauses.length) {
finalSql += ' WHERE ' + whereClauses.join(' AND ');
}
if (groupByClause) {
finalSql += groupByClause;
}
if (havingClauses.length) {
finalSql += ' HAVING ' + havingClauses.join(' AND ');
}
finalSql += orderByClause + limitClause + offsetClause;
// 2) If count mode, wrap without pagination
if (mode === 'count') {
// remove LIMIT & OFFSET values
const countValues = values.slice(0, values.length - 2);
// rebuild inner query (no pagination)
let inner = sql;
if (whereClauses.length) inner += ' WHERE ' + whereClauses.join(' AND ');
if (groupByClause) inner += groupByClause;
if (havingClauses.length) inner += ' HAVING ' + havingClauses.join(' AND ');
// you can omit ORDER BY in count, but including it won’t change the count
const countSql = `SELECT COUNT(*) AS total FROM (${inner}) AS cnt`;
return { sql: finalSql, values, countSql, countValues };
}
return { sql: finalSql, values };
}
};
return builder;
}
module.exports = createSelectBuilder;