Classes for parsing and building SQL select queries in Node/Bun/Workers.
npm install sql-loveA Select object represents a SQL SELECT query and allows dynamically adding clauses including JOIN, WHERE, ORDER BY, LIMIT, OFFSET.
You can define a base query and pass it to the SelectBuilder constructor.
import { SelectBuilder } from 'sql-love';
const query = new SelectBuilder(`
SELECT u.id, u.fname, u.lname, u.email, p.phone
FROM users
LEFT JOIN phone_numbers p ON p.user_id = u.id
AND p.type = 'main'
WHERE u.is_active = 1
`);
if (email) {
query.where('u.email', email);
}
if (areaCode) {
query.where('p.phone', 'LIKE ?%', areaCode);
}
query.sortField(sortField, { name: 'u.lname', email: 'u.email' });
query.limit(limitTo);
const { sql, bindings } = query.compile({ engine: 'pg' });
//
// Then execute the SQL in your preferred client:
//
// mysql2:
connection.query(sql, bindings, (err, results, fields) => {});
// Prisma:
const result = await prisma.$queryRawUnsafe(sql, ...bindings);
// Cloudflare d1
const { results } = await env.DB.prepare(sql)
.bind(...bindings)
.all();Note: These are prepared statements so the values in the bindings array are
safe from SQL injection, with the caveat that you are in charge of quoting any
identifiers. For instance, don't populate u.email from user input in the
example above. But if you need dynamic identifiers for some reason, be sure to
use your SQL client's quoteIdentifier() function.
It is possible to add placeholders to the base query.
import { SelectBuilder } from 'sql-love';
const query = new SelectBuilder(
`
SELECT u.id, u.fname, u.lname, u.email, a.city, a.zip
FROM users
LEFT JOIN addresses a ON a.user_id = u.id
WHERE a.state = :state
AND a.city IN (:city)
`,
{ state: 'CA', city: ['San Francisco', 'Los Angeles'] }
);
const { sql, bindings } = query.compile();
/*
Then "sql" equals:
SELECT u.id, u.fname, u.lname, u.email, a.city, a.zip
FROM users
LEFT JOIN addresses a ON a.user_id = u.id
WHERE a.state = ?
AND a.city IN (?, ?)
And "bindings" equals:
['CA', 'San Francisco', 'Los Angeles']
*/You may specify a RMDBS engine to compile for. The default is "mysql".
Supported values are "mysql" | "pg" | "mssql" | "oracle". Each
RMDBS engine needs slightly different SQL depending on the syntax for binding
placeholders and for limit/offset.
import { SelectBuilder } from 'sql-love';
const query = new SelectBuilder('SELECT * FROM users')
.limit(10)
.page(3)
.where('id', 1);
query.compile({ engine: 'mysql' }).sql;
// SELECT * FROM users WHERE id = ? LIMIT 10 OFFSET 20
query.compile({ engine: 'pg' }).sql;
// SELECT * FROM users WHERE id = $1 LIMIT 10 OFFSET 20
query.compile({ engine: 'oracle' }).sql;
// SELECT * FROM users WHERE id = :1 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
query.compile({ engine: 'mssql' }).sql;
// SELECT * FROM users WHERE id = @P1 ORDER BY (SELECT NULL) OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
// Or set the default engine that compile() uses when none is given
SelectBuilder.setDefaultEngine('pg');Placeholders follow the most common driver for each engine: ? for mysql,
$1, $2... for pg, :1, :2... for oracle (node-oracledb) and @P1, @P2...
for mssql (Prisma's documented SQL Server raw query syntax; with mssql/tedious,
name the inputs P1, P2...). Pass bindingIndex to compile() to start
numbering at something other than 1.
Notes on limit/offset:
- MySQL has no OFFSET without LIMIT, so
query.offset(20)alone compiles toLIMIT 18446744073709551615 OFFSET 20(the documented "no limit" value). - Oracle uses the 12c+
OFFSET m ROWS FETCH NEXT n ROWS ONLYsyntax. - SQL Server requires ORDER BY and OFFSET before FETCH, so the query always
gets
OFFSET n ROWS(0 if unset) and, when there is no ORDER BY,ORDER BY (SELECT NULL)is added. Add your ownorderBy()for a deterministic order.
You should use prepared statements to run the sql and bindings returned by
SelectBuilder.compile(). This will protect you from SQL injection.
Using mysql2's client.query(sql, bindings) or Prisma's
prisma.$queryRawUnsafe(sql, ...bindings) are examples of using prepared
statements.
Note that even though the name prisma.$queryRawUnsafe implies that the
operation is unsafe, values are bound to a prepared statement and you would be
using prisma.$queryRawUnsafe as intended.
SQL Love also includes utility functions for running queries in mysql and Prisma so you don't have to remember these functions. See documentation for runPrisma and runMysql below.
Use the following methods to build queries. And see more examples below.
query.where(column, operator, value)- Require column satisfy operatorquery.where(column, value)- Require column equal a valuequery.where(expression)- Add an arbitrary WHERE expressionquery.where(columnValueRecord)- Add multiple conditionsquery.whereBetween(column, [min, max])- Require value BETWEEN, < or >query.orWhere(conditions)- Specify multiplewhere()s joined byORquery.having(column, operator, value)- Having column satisfy operatorquery.having(column, value)- Having column equal valuequery.having(expression)- Having an arbitrary expressionquery.having(columnValueRecord)- Add multiple conditionsquery.orHaving(expressions)- Multiplehaving()s joined by ORquery.orderBy(column, direction)- Add ORDER BY clausequery.sortField(field, mapNames, modifier)- Add ORDER BY clause for an allowed field in mapNamesquery.limit(num)- Limit by the given numberquery.offset(num)- Specify an offsetquery.page(num)- Automatically calculate offset based on limit and pagequery.columns(columnNames)- Add column names to fetch (aliasselect())query.column(columnName)- Add a column name to fetchquery.table(tableName)- Add a table to add to the FROM clausequery.tables(tableNames)- Add an array of tables to add to the FROM clausequery.from(tableName)- Alias fortable()query.join(expression)- Add a JOIN expressionquery.innerJoin(expression)- Add an INNER JOIN expressionquery.leftJoin(expression)- Add a LEFT JOIN expressionquery.fullJoin(expression)- Add a FULL JOIN expressionquery.rightJoin(expression)- Add a RIGHT JOIN expressionquery.crossJoin(expression)- Add a CROSS JOIN expressionquery.leftOuterJoin(expression)- Add a LEFT OUTER JOIN expressionquery.fullOuterJoin(expression)- Add a FULL OUTER JOIN expressionquery.rightOuterJoin(expression)- Add a RIGHT OUTER JOIN expressionquery.groupBy(column)- Group by a column or expression
// building the FROM clause
query.column('id');
query.column('"Media"."postId"');
query.column('CONCAT(first_name, " ", last_name) AS full_name');
query.columns(['id', 'first_name', 'last_name']);
// Ways to test against NULL
query.where('deleted_at', '=', null);
query.where('deleted_at', null);
query.where('deleted_at IS NULL');
query.where({ deleted_at: null });
query.where('deleted_at', '!=', null); // deleted_at IS NOT NULL
// undefined values throw an Error (use null to compare against NULL)
// Using LIKE
query.where('name', 'LIKE', 'son');
query.where('name', 'LIKE ?', 'son');
query.where('name', 'LIKE ?%', 'son');
query.where('name', 'LIKE %?', 'son');
query.where('name', 'LIKE %?%', 'son');
query.where('name', 'LIKE', '%son%');
query.where('name', 'ILIKE %?', 'son');
// Various operators
query.where('price', '=', 100);
query.where('price', '!=', 100);
query.where('price', '<>', 100);
query.where('price', '>', 100);
query.where({ 'price >': 100 });
query.where('price', 'BETWEEN', [100, 200]);
query.where('price', 'NOT BETWEEN', [100, 200]);
query.whereBetween('price', [100, 200]); // price BETWEEN 100 AND 200
query.whereBetween('price', [100, null]); // price > 100
query.whereBetween('price', [null, 200]); // price < 200
query.whereBetween('price', [null, null]); // clause is ignored
// Using IN and NOT IN
query.where('status', 'IN', ['pending', 'approved']);
query.where('status', ['pending', 'approved']);
query.where('status', '=', ['pending', 'approved']);
query.where('status', '!=', ['pending', 'approved']);
query.where('status', 'NOT IN', ['pending', 'approved']);
query.where({ status: ['pending', 'approved'] });
query.where({ 'status NOT IN': ['pending', 'approved'] });
query.where({ 'status !=': ['pending', 'approved'] });
// IN and NOT IN with an empty array add no condition at all
query.where('status', 'IN', []); // (omitted)
query.where({ 'status <>': ['pending', 'approved'] });
// Question marks for binding
query.where(
'users.id IN (SELECT user_id FROM roles WHERE customer_id IN (?, ?))',
[1, 2]
);
query.where(
'users.id IN (SELECT user_id FROM roles WHERE customer_id = ?)',
[1]
);
// with exactly one placeholder, a single non-array value also works
query.where('users.id IN (SELECT user_id FROM roles WHERE customer_id = ?)', 1);
query.having('COUNT(*) > ?', 2);
// (but "tags ?", "tags ?|" and "tags ?&" are Postgres jsonb operators)
query.where('tags ?', 'admin'); // tags ? $1
// Multiple conditions
query.where({
deleted_at: null,
'price >': 100,
'price <': 200,
color: ['blue', 'black'],
'stars BETWEEN': [4, 5],
conditions: ['new', 'used'],
});
// Note: .having() supports the same signatures as .where()
// Ways to use OR
query.orWhere([{ approved_at: null }, { denied_at: null }]);
query.orWhere({ approved_at: null, denied_at: null });
query.orWhere(['approved_at IS NULL', 'denied_at IS NULL']);
query.orWhere([
['approved_at', null],
['denied_at =', null],
]);
query.where('(approved_at IS NULL OR denied_at IS NULL)');
// Inside an array, each object is a group of conditions joined by AND
query.orWhere([{ status: 'active', 'age >=': 18 }, { role: 'admin' }]);
// WHERE ((status = ? AND age >= ?) OR role = ?)
// Note: .orHaving() supports the same signatures as .orWhere()
// Joins
query.innerJoin('phone_numbers p ON p.user_id = u.id');
query.leftJoin('phone_numbers p ON p.user_id = u.id AND p.type = ?', ['main']);
query.fullOuterJoin(
'phone_numbers p ON p.user_id = u.id AND p.type NOT IN(?, ?)',
['home', 'cell']
);
// Grouping
// Sorting
query.orderBy('modified_at'); // ORDER BY modified_at
query.orderBy('modified_at', 'DESC'); // ORDER BY modified_at DESC
query.orderBy('-modified_at'); // ORDER BY modified_at DESC
query.orderBy('COUNT(*)'); // ORDER BY COUNT(*)
query.orderBy('COUNT(*)', 'DESC'); // ORDER BY COUNT(*) DESC
query.orderBy('COUNT(*) DESC'); // ORDER BY COUNT(*) DESC
query.orderBy('modified_at DESC NULLS LAST'); // ORDER BY modified_at DESC NULLS LAST
query.orderBy('modified_at USING >'); // ORDER BY modified_at USING >
// Sorting with support for hyphens and mapping
// Only fields present in mapNames are allowed, so field names may safely
// come from user input (e.g. a "?sort=-created" query string param).
// Unknown fields emit a console.warn and add no ORDER BY clause.
query.sortField('-created', { created: 'created_at' }); // ORDER BY created_at DESC
query.sortField('-foo', { created: 'created_at' }); // console.warn; no ORDER BY added
query.sortField(
'-created_at',
{
created_at: 'posts.created_timestamp',
},
'NULLS LAST'
); // ORDER BY posts.created_timestamp DESC NULLS LAST
query.sortField('-created', {
created: 'created_at NULLS LAST',
'-created': 'created_at DESC NULLS LAST',
}); // ORDER BY created_at DESC NULLS LAST
query.sortField('-name', {
name: 'name COLLATE "jp_JP"',
}); // ORDER BY name COLLATE "jp_JP" DESC
// Pagination
query.limit(10).page(3); // "LIMIT 10 OFFSET 20" (or for mssql/oracle, "OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY")
// Cloning for creating similar queries
const query1 = new SelectBuilder();
query1.where('email', 'john@example.com');
const query2 = query1.getClone();
query1.table('users');
query2.table('contacts');
query1.compile().sql; // SELECT * FROM users WHERE email = ?
query2.compile().sql; // SELECT * FROM contacts WHERE email = ?One powerful feature of SelectBuilder is that it can construct a count query to determine the number of results that would have been returned if there were no LIMIT.
const query = new SelectBuilder('SELECT id, name FROM users LIMIT 5');
const { sql } = query.compileCount();
// SELECT COUNT(*) AS found_rows FROM users
const { sql } = query.compileCount({ countExpr: 'DISTINCT externalId' });
// SELECT COUNT(DISTINCT externalId) AS found_rows FROM usersWith queries that have a GROUP BY or HAVING clause, or that select
DISTINCT rows, the main query (minus its ORDER BY and LIMIT/OFFSET) will be
wrapped in a count query, so the result is the number of groups or distinct
rows. In that case countExpr is ignored.
const query = new SelectBuilder(`
SELECT category, COUNT(*)
FROM posts
GROUP BY category
HAVING COUNT(*) > 1
`);
// Regular query
const { sql } = query.compile();
// Count query
const { sql } = query.compileCount();
/*
SELECT COUNT(*) AS found_rows FROM (
SELECT category, COUNT(*)
FROM posts
GROUP BY category
HAVING COUNT(*) > 1
) AS subquery_results
*/SelectBuilder has a few other useful methods.
query.getClone()- Get an exact copy of this query objectquery.unjoin(tableName)- Remove a join expression by its table name (or table name and alias)query.reset(field)- Reset a single aspect of the query (e.g.'where'or'having')query.reset(fields)- Reset a few particular aspects of the query (e.g.['where', 'having'])query.reset()- Reset query to an empty state
new SelectBuilder(sql) uses regular expressions and is not a true parser.
The goal is to be fast and useful for 99% of situations.
Below are some limitations illustrated by example.
Most subqueries can be parsed but sub-subqueries don't work.
// ❌ WILL NOT WORK
const query = new SelectBuilder(`
SELECT * FROM categories_posts WHERE category_id IN(
SELECT id FROM categories WHERE client_id IN(
SELECT client_id FROM affiliations WHERE name LIKE 'test'
)
)`);
// ✅ WORKING EQUIVALENT
const query = new SelectBuilder(`SELECT * FROM categories_posts`);
const subquery = `SELECT id FROM categories WHERE client_id IN(
SELECT client_id FROM affiliations WHERE name LIKE 'test'
)`;
query.where(`category_id IN(${subquery})`);If you need to use SQL keywords in strings, use bindings.
// ❌ WILL NOT WORK
new SelectBuilder(`SELECT id, CONCAT('where ', column) FROM users`);
// ✅ WORKING EQUIVALENT
new SelectBuilder(`SELECT id, CONCAT(:prefix, column) FROM users`, {
prefix: 'where ',
});Nested logic can't be parsed properly.
// ❌ WILL NOT WORK
new SelectBuilder(`
SELECT * FROM users
WHERE (
fname = 'Matthew' AND (
lname LIKE '%john' OR lname LIKE 'john%'
) OR (
id > 0 AND is_active IS NOT NULL
)
)
`);
// ✅ WORKING EQUIVALENT
const query = new SelectBuilder(`SELECT * FROM users`);
query.orWhere([
"fname = 'Matthew' AND (lname LIKE '%john' OR lname LIKE 'john%')",
'id > 0 AND is_active IS NOT NULL',
]);There are several utility functions bundled with this library.
You can get detailed page and limit information from a query.
import { SelectBuilder, getPagination } from 'sql-love';
const query = new SelectBuilder('SELECT * FROM users').limit(10).page(1);
// ...
// run a count query to determine that there are 42 results
const count = 42;
const pagination = getPagination(query, count);
expect(pagination).toEqual({
page: 1,
prevPage: null,
nextPage: 2,
perPage: 10,
numPages: 5,
total: 42,
isFirst: true,
isLast: false,
});You can index a results set by a particular field.
import { extractIndexed } from 'sql-love';
const records = [
{ id: 1, name: 'John' },
{ id: 2, name: 'Jane' },
];
const indexed = extractIndexed('id', records);
/*
{
1: { id: 1, name: 'John' },
2: { id: 2, name: 'Jane' },
});
*/You can divide a results set into groups based on a particular field.
import { extractGrouped } from 'sql-love';
const records = [
{ id: 1, name: 'John', dept: 'Marketing' },
{ id: 2, name: 'Jane', dept: 'Finance' },
{ id: 3, name: 'Tim', dept: 'Marketing' },
];
const grouped = extractGrouped('dept', records);
/*
{
Marketing: [
{ id: 1, name: 'John', dept: 'Marketing' },
{ id: 3, name: 'Tim', dept: 'Marketing' },
],
Finance: [
{ id: 2, name: 'Jane', dept: 'Finance' }
],
}
*/You can create a lookup object based on a results set
const records = [
{ id: 1, name: 'John', age: 30 },
{ id: 2, name: 'Jane', age: 35 },
];
const nameById = extractLookup('id', 'name', records);
/*
{
'1': 'John',
'2': 'Jane',
}
*/runPrisma() will run a query using Prisma and return the results.
import { SelectBuilder, runPrisma } from 'sql-love';
import { prisma } from '~/db.server';
const query = new SelectBuilder('SELECT * FROM users');
query.where('dept', 'Marketing');
const rows = runPrisma(prisma, query, { engine: 'pg' });runPrismaWithCount() will run a query using Prisma and return the results,
found rows and pagination.
import { SelectBuilder, runPrismaWithCount } from 'sql-love';
import { prisma } from '~/db.server';
const query = new SelectBuilder('SELECT * FROM users')
.where('dept', 'Marketing')
.limit(2)
.page(5);
const { records, total, pagination } = await runPrismaWithCount(prisma, query, {
engine: 'pg',
});See getPagination for details on the pagination object.
runMysql() will run a query using mysql and return the results.
import { SelectBuilder, runMysql } from 'sql-love';
import { mysql } from 'mysql2';
const client = mysql.createConnection(config);
const query = new SelectBuilder('SELECT * FROM users');
query.where('dept', 'Marketing');
const rows = await runMysql(client, query);runMysqlWithCount() will run a query using mysql and return the results,
found rows and pagination.
import { SelectBuilder, runMysqlWithCount } from 'sql-love';
import { mysql } from 'mysql2';
const client = mysql.createConnection(config);
const query = new SelectBuilder('SELECT * FROM users')
.where('dept', 'Marketing')
.limit(2)
.page(5);
const { records, total, pagination } = await runMysqlWithCount(client, query);You can also use the mysql2 promise api:
import { SelectBuilder, runMysqlAsync, runMysqlAsyncWithCount } from 'sql-love';
import { mysql } from 'mysql2/promise';
const client = await mysql.createConnection(config);
const query = new SelectBuilder('SELECT * FROM users')
.where('dept', 'Marketing')
.limit(2)
.page(5);
const records = await runMysqlAsync(client, query);
// OR
const { records, total, pagination } = await runMysqlAsyncWithCount(
client,
query
);With some database clients such as Prisma, your recordsets may contain BigInt
objects. These are not safe to pass to JSON.stringify(). The following are
examples of the toSafeJson* utility functions.
import { toSafeJsonString, toSafeJsonRecords } from 'sql-love';
const records = [{ postCount: 12n }, { postCount: 9007199254740993n }];
toSafeJsonRecords(records);
// [
// { postCount: 12 },
// { postCount: "9007199254740993" },
// ]
toSafeJsonString(records);
// '[{"postCount":12},{"postCount":"9007199254740993"}]'
// As you can see, BigInt values too big for Number are converted to stringsThis code repository uses Bun for a package manager, test runner and builder.
