Classes for parsing and building SQL select queries in Node
npm install sql-love
A 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.sort(sortField);
query.limit(limitTo);
const { sql, bindings } = query.compile();
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 that 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, we don't recommend populating u.email
from user input in the example above. But if you do, be sure to use your
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 compiler engine to use. The default is "mysql"
.
import { SelectBuilder, setDefaultEngine } from 'sql-love';
const query = new SelectBuilder('SELECT * FROM users')
.limit(10)
.page(3)
.where('id', 1);
query.compile({ engine: 'mysql' }).sql;
query.compile({ engine: 'sqlite' }).sql;
// SELECT * FROM users WHERE id = ? OFFSET 20 LIMIT 10
query.compile({ engine: 'pg' }).sql;
// SELECT * FROM users WHERE id = $1 OFFSET 20 LIMIT 10
query.compile({ engine: 'mssql' }).sql;
query.compile({ engine: 'oracle' }).sql;
// SELECT * FROM users WHERE id = ? OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
// Or you can specify the default engine to use in the compile function
setDefaultEngine('pg');
You should use prepared statements to run the sql and bindings returned by
SelectBuilder.compile()
. This will protect you from SQL injection.
Using mysqlClient.query(sql, bindings)
and
prisma.$queryRawUnsafe(sql, ...bindings)
are examples of using prepared
statements.
Note that even though the name prisma.$queryRawUnsafe(sql, ...bindings)
implies that the query is unsafe, it is actually safe because
prisma.$queryRawUnsafe
uses prepared statements.
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 operator -
query.where(column, value)
- Require column equal a value -
query.where(expression)
- Add an arbitrary WHERE expression -
query.where(columnValueRecord)
- Add multiple conditions -
query.whereBetween(column, [min, max])
- Require value BETWEEN, < or > -
query.orWhere(conditions)
- Specify multiplewhere()
s joined byOR
-
query.having(column, operator, value)
- Having column satisfy operator -
query.having(column, value)
- Having column equal value -
query.having(expression)
- Having an arbitrary expression -
query.having(columnValueRecord)
- Add multiple conditions -
query.orHaving(expressions)
- Multiplehaving()
s joined by OR -
query.orderBy(column)
- Add ORDER BY clause -
query.sortField(column, mapNames)
- Add ORDER BY clause with mapNames -
query.limit(num)
- Limit by the given number -
query.offset(num)
- Specify an offset -
query.page(num)
- Automatically calculate offset based on limit and page -
query.columns(columnNames)
- Add column names to fetch (aliasselect()
) -
query.column(columnName)
- Add a column name to fetch -
query.table(tableName)
- Specify the table in the FROM clause -
query.from(tableName)
- Same as above -
query.join(expression)
- Add a JOIN expression -
query.innerJoin(expression)
- Add an INNER JOIN expression -
query.leftJoin(expression)
- Add a LEFT JOIN expression -
query.fullJoin(expression)
- Add a FULL JOIN expression -
query.rightJoin(expression)
- Add a RIGHT JOIN expression -
query.crossJoin(expression)
- Add a CROSS JOIN expression -
query.leftOuterJoin(expression)
- Add a LEFT OUTER JOIN expression -
query.fullOuterJoin(expression)
- Add a FULL OUTER JOIN expression -
query.rightOuterJoin(expression)
- Add a RIGHT OUTER JOIN expression -
query.groupBy(column)
- Group by a column or expression
// The following are equivalent
query.where('deleted_at', '=', null);
query.where('deleted_at', '=', undefined);
query.where('deleted_at', null);
query.where('deleted_at', undefined);
query.where('deleted_at IS NULL');
query.where({ deleted_at: null });
query.where({ deleted_at: undefined });
// the following demonstrate ways to use 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%');
// The following demonstrate ways to use 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
// The following demonstrate ways to use 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'] });
query.where({ 'status <>': ['pending', 'approved'] });
// the following demonstrates how to use 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 = ?)',
// even if there is only one placeholder, you must use an array
[1]
);
// The following demonstrates using objects to specify 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()
// The following demonstrate equivalent 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)');
// Note: .orHaving() supports the same signatures as .orWhere()
// The following demonstrate 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.outerJoin('phone_numbers p ON p.user_id = u.id AND p.type NOT IN(?, ?)', [
'home',
'cell',
]);
// The following demonstrates pagination
query.limit(10).page(3); // LIMIT 10 OFFSET 20
// The following demonstrates cloning
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 users
With queries that have a "HAVING" clause, the main query will be wrapped in a count query.
const query = new SelectBuilder(`
SELECT category, COUNT(*)
FROM posts
GROUP BY category
HAVING COUNT(*) > 1
`);
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 object -
query.unjoin(tableName)
- Remove a join expression -
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 ', expr) FROM users`);
// ✅ WORKING EQUIVALENT
new SelectBuilder(`SELECT id, CONCAT(:prefix, expr) 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 pagination = getPagination(query, 42);
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 = [{ count: 12n }, { count: 9007199254740993n }];
toSafeJsonRecords(records);
// [
// { count: 12 },
// { count: "9007199254740993" },
// ]
toSafeJsonString(records);
// '[{"count":12},{"count":"9007199254740993"}]'
// Note that BigInt values too big for Number are converted to strings