selectstar
Safe, idiomatic query generation for Postgres.
Installation
npm install selectstar
Usage
selectstar
is a library for generating sophisticated SQL within a Javascript
or TypeScript program, while still making it easy to avoid SQL injection
attacks and other common mistakes when generating queries with string
concatenation.
import { sql } from 'selectstar';
sql`SELECT 1`;
selectstar
uses tagged template literals to generate an object that you can
pass to node-postgres as a parameterized query. Above, we are generating the
simplest possible Postgres query, and it results in a simple object of two keys:
{
text: 'SELECT 1',
values: []
}
You can pass this directly into the .query
methods of pg
's Client
and
Pool
instances:
await client.query(sql`SELECT 1`)
Parameters
If you pass templated variables into the query, they will appear in the resulting object as query parameters:
const id = 802728;
sql`
SELECT id, first_name, last_name
FROM users
WHERE id = ${id}
`;
// returns:
{
text: `
SELECT id, first_name, last_name
FROM users
WHERE id = $1
`,
values: [802728]
}
This introduces a layer of safety, because query parameters can't be used for sql injection attacks. You can insert any value into the template that Postgres supports, which includes strings, numbers, arrays, objects, and dates.
selectstar
is ignorant of the underlying query semantics, which means that it
will handle more sophisticated Postgres queries gracefully as well:
// use a CTE, selectstar doesn't care:
const query = sql`
WITH place_ids (id) AS (
SELECT p1.id
FROM places AS p1
JOIN places AS p2 ON p2.id = ${place_id}
WHERE
ST_Intersects(p1.the_geom, p2.the_geom)
)
SELECT * FROM places JOIN place_ids ON place_ids.id = places.id
`;
Templating (or: Dynamic Queries)
Sometimes you will want to generate a query that has a dynamic number of
variables, or optionally includes snippets. selectstar
offers some tools to
make this easy and safe.
template
to construct query fragments:
Use The template
function will create a snippet of SQL that can be inserted into
larger queries. These templates can contain variables, which will be lazily
evaluated when the query is constructed:
import { subDays } from 'date-fns';
import { sql, template } from 'selectstar';
const yesterday = subDays(new Date(), 1); // subtract 1 day from today
const updatedSinceYesterday = template`updated_at > ${yesterday}`;
const query = sql`SELECT * FROM users WHERE ${updatedSinceYesterday}`;
// query =
{
text: `SELECT * FROM users WHERE updated_at > $1`.
values: [yesterday]
}
identifier
to dynamically-specify column or table names:
Use The identifier
function will let you safely specify constants inline within a
query in places where parameters can't be used or are undesirable. Identifiers
are escaped using the same algorithm as node-postgres. There are two main uses
for identifier
: specifying dynamic columns or dynamic table names:
const vesselType = "submarines"; // or "spaceships", or "sailboats", etc
const query = `SELECT id, name FROM ${identifier(vesselType)}`;
// query =
{
text: `SELECT id, name FROM "submarines"`,
values: []
}
list
to generate lists of dynamic SQL:
Use The list
function will let you transform a list of data into dynamic SQL. This
combines well with the previous functions:
const rows = [
{ id: 1, first: "Phillip", last: "Fry" },
{ id: 2, first: "Turanga", last: "Leela" },
];
const rowSql = ({ id, first, last }) =>
template`(${id}, ${first}, ${last}, now())`;
const query = `
INSERT INTO users (id, first_name, last_name, created_at) VALUES
${list(rows.map(rowSql))}
`;
// query =
{
text: `
INSERT INTO users (id, first_name, last_name, created_at) VALUES
($1, $2, $3, now()),
($4, $5, $6, now())
`
}
The default separator for list
is the literal string ", "
. You can change
this by passing a different separator as the second argument:
const criteria = [
template`updated_at > ${yesterday}`,
template`home_town = ${hometown}`
];
const query = `
SELECT * FROM users WHERE
${list(criteria, ' AND ')}
`
// query =
{
text: `
SELECT * FROM users WHERE
updated_at > $1 AND home_town = $2
`,
values: [yesterday, hometown]
}
Beware: Do not pass dynamic or potentially-unsafe values to separator
.
Values passed to this function are treated as literal safe values. Recommend
that you only pass ", "
, " AND "
, and " OR "
to this second argument. The
default is ", "
, which is appropriate in most circumstances.
Use a function as an escape hatch
You may need to do something that isn't covered by the tools above, but you still need the safety of parameterized queries. In this case, you can pass a function to a parameter, and it will be evaluated when the query is generated.
The function takes a single argument: an entrypoint that lets you generate
dynamic SQL. Under the hood this is how the template
function works.
function columns(sql) {
const cols = ['id', 'name', 'speed'];
return sql`${list(cols.map(identifier))}`;
}
const query = `SELECT ${columns} FROM starships`;
// query =
{
text: `SELECT "id", "name", "speed" FROM starships`,
values: []
}
unsafe
if you have no other choice
Use Sometimes you need to turn all the safeties off. In this case you can use the
unsafe
function to insert literal SQL into your queries. unsafe
takes a
string, which will be inserted wholesale into the resulting query. Use at your
own risk, selectstar
cannot help you if you screw up.
const columns = ['id', 'name', 'speed'];
const query = `SELECT ${unsafe(columns.join(', '))} FROM starships`;
// query =
{
text: `SELECT id, name, speed FROM starships`,
values: []
}
Streaming
selectstar
is agnostic about how you use the generated query. As a result you
can use the result in some interesting ways. For example, the pg-query-stream
library allows you to pull records out of the database through a Nodejs readable
object stream. This lets you perform streaming transformations on large numbers
of records as they are pulled from the database without requiring a ton of
memory.
import QueryStream from 'pg-query-stream';
import { sql } from 'selectstar';
const query = `SELECT id, first_name, last_name FROM users`;
const stream = new QueryStream(query.text, query.values);
await db.query(stream);
// In Node, readable streams are also async iterators, which we can loop over
// with `for await (...`
for await (const user of stream) {
console.log(`[${user.id}] ${user.first_name} ${user.last_name}`);
}
// Logs:
// [1] Phillip Fry
// [2] Turanga Leela
// ...
This is especially useful if you want to do an ETL process where you're querying data out of a database to be stored in another format or in a different DBMS. This pairs ideally with the naushon stream transformation library, which is used in the example below:
import { eduction, partitionBy } from 'naushon';
const query = `SELECT * FROM vessels`;
const stream = new QueryStream(query.text, query.values);
await db.query(stream);
// Pour the results into tables based on their type using partitionBy:
for await (const vessels of eduction(partitionBy(v => v.type), stream)) {
const vessel = vessels[0];
const cols = Object.keys(vessel);
const rows = vessels.map(v => template`(${list(cols.map(c => v[c]))})`);
await db.insert(sql`
INSERT INTO ${identifier(vessel.type)} (${list(cols.map(identifier))})
VALUES ${list(rows)}
`);
}
Rationale
SQL is a powerful language with rich semantics. Many object-relational and
fluent query builder systems either only offer a subset of these semantics or
obscure them behind complex forms. The intention behind selectstar
is to give
developers as thin an interface for interacting with a SQL server as possible
while still making it easy to create safe dynamic queries.
This library builds on the approach of simple-postgres, which makes it
easier to interact with a database with no configs. Rather than combine the
concern of connecting with a database and querying the database, selectstar
leaves the client connection up to you. One advantage of this approach is that
selectstar
does not have a direct dependency on postgres (it has no
dependencies at all), so you may upgrade the client library independently of
this one.
Also, because selectstar
divorces query generation from query execution, you
may use the two in different contexts: the program or module that generates the
SQL may not know about the underlying SQL connection. You may also use
selectstar
-generated queries with other Postgres-related libraries, like
pg-query-stream.
selectstar
is not a replacement for other query-generation tools like knex or
an ORM, it's another tool in the toolbox for constructing powerful, idiomatic
SQL queries.
It is possible that selectstar
will work with different SQL clients or servers
but that is not its intended or supported use case at this time.