npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

sql-thang

v0.2.0

Published

ES6 String Template SQL Placeholders

Downloads

4

Readme

SQL Thang

  • https://github.com/seanmiddleditch/sql-thang
  • by Sean Middleditch

A simple bare-bones SQL placholder library based on ES6 string templates.

It was inspired by https://github.com/felixfbecker/node-sql-template-strings.

Usage

The two most important functions in the library are sql and build.

The sql function is the template driver. Use it as an ES6 string template and it will return an array of functions, which is the intermediate representation of sql-thang.

const { sql } = require('sql-thang')
const stmt = sql`SELECT * FROM mytable WHERE foo=${value}`

The build function converts an array of functions into an object with a sql string and a params array, suitable for passing to a database backend. By default, the escaping rules match those of mysqljs, but the personality can be changed for Postgres (or any other database) compatibility.

const { build, sql } = require('sql-thang')
const value = 12345
const query = build(sql`SELECT * FROM mytable WHERE foo=${value}`)
// query.sql --    'SELECT * FROM mytable WHERE foo= ? '
// query.params -- [12345]

Note that the return value of the sql function is a special type of array using the SQL constructor. This allows safe embedding of SQL statements with proper escaping.

const fragment = sql`FROM mytable WHERE id=${id}`
const complete = build(sql`SELECT * ${fragment} AND title=${title}`)
// complete.sql --    'SELECT * FROM mytable WHERE id= ? AND title= ?
// complete.params -- [id, title]

The SQL type can also be detected using the instanceof operator for safe DB abstractions. The SQL type uses the Array prototype chain. This offers some convenience with manipulating, such as being able to concat or map them, though this should not regularly be needed outside of debugging purposes.

Special Values

sql-thang includes a few additional functions for special replacement needs. The three most common are literal, ident, and value.

The default behavior of sql-thang for any template replacement is equivalent to value.

The behavior of literal is to disable escaping or placeholder behavior and to insert raw unmodified text into the result.

const value = 'foo'
const query = build(sql`${value} ${ident(value)}`)
// query.sql --    '? foo'
// query.params -- ['foo']

The behavior of ident is to insert an escaped identifier or identifier placeholder in the resulting output. By default, using the mysqljs personality, an identifier is inserted into the SQL text as ?? and the identifier value is pushed into the params array. This can be overridden (see #Personalities below) for compatibility with database drivers like node-pg.

There are additionally two more replacement functions, list and keyed. These are used to splat an array of values or an object of key-value pairs into the output. Unlike with the default functionality provided by some database drivers for this purpose, these helpers also allow settings options for joining and separating characters, and for prefix or suffix strings that are only generated if the result would be non-empty. This is particularly useful for WHERE queries, for example. For keyed, both identifier and value will be escaped or use placeholders as appropriate for protection from injection attacks.

const params = {category: 7, title: 'test'}
const query = build(sql`SELECT * FROM post ${keyed(params, {prefix: 'WHERE', join: 'AND'})}`)
// query.sql --    'SELECT * FROM post WHERE ?? = ? AND ?? = ? '
// query.params -- ['category', 7, 'title', 'test']

The purpose of prefix is clear when an empty input (or input with only undefined values) is provided:

const params = {category: undefined}
const query = build(sql`SELECT * FROM post ${keyed(params, {prefix: 'WHERE', join: 'AND'})}`)
// query.sql --    'SELECT * FROM post'
// query.params -- []

Users of this functionality may with to write a helper like:

const where = (obj, opt = {}) =>
    keyed(obj, {join: 'AND', ...opt, prefix: 'WHERE'})

Note: while identifiers will be escaped, it's still an error to reference unknown columns in SQL. Always sanitize input objects and their keys before using them in a query, even with a libray like sql-thang!

More complex conditional replacement cases can be handled via plain ol' JavaScript and proper use of ident and literal.

Personalities

A secondary parameter passed to build is the personality used for compilation. It must be an option with two members: ident and value. Each will receive an input and a context object, which together can be used to escape any identifier or value. The functions should return the raw text that will be inserted into the SQL string returned by build.

An example Postgres personality might look like:

const postgre = {
    ident: id => `"${id.replace(/"/+, '""')}"`, // 'foo' -> '"foo"'
    value: (val, ctx) => `$${ctx.params.push(val)}` // val -> '$1' , params+=[val]
}

const col = 'foo'
const value = 123
const query = build(sql`SELECT * FROM mytable WHERE ${ident(col)}=${value}`, postgre)
// query.sql --    'SELECT * FROM mytable WHERE "foo" = $1 '
// query.params -- [123]

The context object provided as the second argument to the personality functions has two properites, personality and params. The personality refers back to the personality itself. The params array is the value that is returned by the build function; a personality can choose to escape an identifier or value and return that directly (for drivers that don't support placeholders, for example, or debug logging), or the personality can insert values into the params array and return an appropriate placeholder token.

The default personality is implemented roughly as:

const defaults = {
    ident: (id, ctx) => ('??', ctx.params.push(id)), // 'foo' -> '??' , params+=['foo']
    value: (val, ctx) => ('?', ctx.params.push(val)) // val -> '?' , params+=[val]
}

Custom Modifiers

Statements returned sql are just arrays of functions. The build function does relatively little; it constructs a context object and then maps the input array's functions over the context, returning the result joined by whitespace. In other words, a custom modifier is any function that takes a context object and returns some SQL text.

The modifiers like ident or literal or even the default value are thus implemented with the most obvious and trivial implementation. For example, the ident function is:

const ident = (id, ctx) => ctx.personality.ident(id, ctx)

If the provided modifiers aren't sufficient, custom modifiers can easily be written. The personality property of the context object can be used to safely quote identifiers and values, generating placeholders if necessary.

Note: it's recommended to ensure that spaces are placed around any generated SQL fragments in a modifier. This is done auotmatically between template expressions, but should be done within modifiers if they're composed of multiple pieces. Without spaces, seemingly independent template expressions might "join" together into new tokens or keywords in the final SQL text.