sql-love
v1.0.4
Published
Build SQL queries with JavaScript
Downloads
230
Maintainers
Readme
sql-love
Classes for parsing and building SQL select queries in Node
Installation
npm install sql-love
Table of Contents
SelectBuilder
A Select object represents a SQL SELECT query and allows dynamically adding clauses including JOIN, WHERE, ORDER BY, LIMIT, OFFSET.
Parsing base SQL
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: 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.
Parsing with placeholders
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']
*/
RDBMS engine
You may specify a RMDBS engine to compile for. The default is "mysql"
.
Supported values are "mysql" | "sqlite" | "pg" | "mssql" | "oracle"
. Each
RMDBS engine needs slightly different SQL depending on the syntax for binding
placeholders and for limit/offset.
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');
SQL injection
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.
Building the query
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 byOR
query.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)
- Add ORDER BY clausequery.sortField(column, mapNames)
- Add ORDER BY clause with 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)
- Specify the table in the FROM clausequery.from(tableName)
- Same as abovequery.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
More examples
// 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', '[email protected]');
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 = ?
Counting results
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
`);
// 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
*/
Other methods
SelectBuilder has a few other useful methods.
query.getClone()
- Get an exact copy of this query objectquery.unjoin(tableName)
- Remove a join expressionquery.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
Parser limitations
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.
Nested subqueries
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})`);
Keywords in strings
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 OR and AND clauses
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',
]);
Utility functions
There are several utility functions bundled with this library.
getPagination
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,
});
extractIndexed
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' },
});
*/
extractGrouped
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' }],
}
*/
extractLookup
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 and runPrismaWithCount
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 and runMysqlWithCount
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
);
toSafeJson
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 strings