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-concat

v3.1.0

Published

It's just fancy string concatenation

Downloads

209

Readme

sql-concat

A MySQL query builder.

const q = require('sql-concat')

The only "breaking" change from 1.x to 2.x is that support for versions of node older than 6 was dropped.

Designed to...

  • Build queries programmatically
  • Allow simple combining of query parts and their associated parameters (as opposed to writing a long query string followed by a long array of parameter values)
  • Build queries for the mysqljs/mysql library (specifically, by expecting its rules for query values instead of MySQL's stored procedure parameters)

Features

  • Easily compose query parts - the query-builder object is immutable, so you can build up a base query and re-use it over and over again with small modifications (for example, with conditional where clauses or joins)
  • Not as overblown as knex, and allows more freedom in using string literals within query chunks
  • Queries should look good when printed out (newlines between clauses, subqueries indented with tabs)

Looks like

const q = require('sql-concat')
const minNumber = 0
const result = q.select('table1.some_boring_id, table2.something_interesting, mystery_table.surprise', q`LEAST(table1.whatever, ${minNumber}) AS whatever`)
	.from('table1')
	.join('table2', 'table1.some_boring_id = table2.id')
	.leftJoin('mystery_table', 'mystery_table.twister_reality = table2.probably_null_column')
	.where('table1.pants', 'fancy')
	.where('table1.britches', '>', 99)
	.build()

const expectedQuery = 'SELECT table1.some_boring_id, table2.something_interesting, mystery_table.surprise, LEAST(table1.whatever, ?) AS whatever\n'
		+ 'FROM table1\n'
		+ 'JOIN table2 ON table1.some_boring_id = table2.id\n'
		+ 'LEFT JOIN mystery_table ON mystery_table.twister_reality = table2.probably_null_column\n'
		+ 'WHERE table1.pants = ? AND table1.britches > ?'

result.sql // => expectedQuery

result.values // => [ 0, 'fancy', 99 ]

A cooler example

Showing off the composability/reusability of the query objects, plus some dynamic query building:


// A partial query that we can just leave here to reuse later:
const MOST_RECENT_SALE = q.select('item_sale.item_id, MAX(item_sale.date) AS `date`')
	.from('item_sale')
	.groupBy('item_sale.item_id')

function mostRecentSalePricesQuery(taxable, itemType) {
	const subquery = MOST_RECENT_SALE.where('taxable', taxable)

	let query = q.select('item.item_id, item.description, item.type, latest_sale.date AS latest_sale_date, latest_sale.price')
		.from('item')
		.join(subquery, 'latest_sale', 'latest_sale.item_id = item.item_id')

	// Dynamically add new clauses to the query as needed
	if (itemType) {
		query = query.where('item.item_type', itemType)
	}

	return query.build()
}

// Build those dynamic queries:

const taxableSpecialQuery = mostRecentSalePricesQuery(true, 'special')

const expectedTaxableSpecialQuery = ['SELECT item.item_id, item.description, item.type, latest_sale.date AS latest_sale_date, latest_sale.price',
	'FROM item',
	'JOIN (',
	'\tSELECT item_sale.item_id, MAX(item_sale.date) AS `date`',
	'\tFROM item_sale',
	'\tWHERE taxable = ?',
	'\tGROUP BY item_sale.item_id',
	') AS latest_sale ON latest_sale.item_id = item.item_id',
	'WHERE item.item_type = ?'].join('\n')

taxableSpecialQuery.sql // => expectedTaxableSpecialQuery
taxableSpecialQuery.values // => [ true, 'special' ]

const nonTaxableQuery = mostRecentSalePricesQuery(false)

const expectedNonTaxableQuery = ['SELECT item.item_id, item.description, item.type, latest_sale.date AS latest_sale_date, latest_sale.price',
	'FROM item',
	'JOIN (',
	'\tSELECT item_sale.item_id, MAX(item_sale.date) AS `date`',
	'\tFROM item_sale',
	'\tWHERE taxable = ?',
	'\tGROUP BY item_sale.item_id',
	') AS latest_sale ON latest_sale.item_id = item.item_id'].join('\n')

nonTaxableQuery.sql // => expectedNonTaxableQuery
nonTaxableQuery.values // => [ false ]

API

Because the mysql package already makes inserting so easy, this module is focused on SELECT queries. I've implemented new clauses as I've needed them, and it's pretty well fleshed out at the moment.

If you need a clause added that is not implemented yet, feel free to open a pull request. If you're not sure what the API should look like, open an issue and we can talk it through.

Clauses

Every clause method returns a new immutable q query object.

  • q.select(expression1, expression2, etc)
  • q.from(tablename | subquery, alias)
  • q.join(tablename | subquery, [alias], on_expression)
  • q.leftJoin(tablename | subquery, [alias], on_expression)
  • q.where(expression, [comparator, [value]])
  • q.orWhere(expression, [comparator, [value]])
  • q.whereLike(expression, value)
  • q.orWhereLike(expression, value)
  • q.having(expression, [comparator, [value]])
  • q.orHaving(expression, [comparator, [value]])
  • q.groupBy(expression1, expression2, etc)
  • q.orderBy(expression1, expression2, etc)
  • q.limit(offset)
  • q.forUpdate()
  • q.lockInShareMode()

expression strings are inserted without being parameterized, but you can also pass in tagged template strings to do anything special.

All values are automatically parameterized. If a value is NULL it will be automatically compared with IS, and if it's an array it will be automatically compared with IN():

const whereInResult = q.select('fancy')
    .from('table')
    .where('table.pants', [ 'fancy', 'boring' ])
    .build()

const whereInQuery = 'SELECT fancy\n'
        + 'FROM table\n'
        + 'WHERE table.pants IN(?)'

whereInResult.sql // => whereInQuery

whereInResult.values // => [ [ 'fancy', 'boring' ] ]

Put another way, calling q.select('column1, column2') is just as acceptable as calling q.select('column1', 'column2') and you should use whichever you prefer.

Clause order

Clauses are returned in the correct order no matter what order you call the methods in.

q.from('table').select('column').toString() // `SELECT column\nFROM table``

However, if you call a method multiple times, the values are concatenated in the same order you called them.

q.from('nifty')
	.select('snazzy')
	.select('spiffy')
	.select('sizzle')
	.toString() // `SELECT snazzy, spiffy, sizzle\nFROM nifty``

q.union(query) and q.unionAll(query)

The union and unionAll methods return a query object that only contains union and unionAll queries – once you start unioning queries together, you can keep unioning more queries, but you can't add any other clauses to them.

q.build()

Returns an object with these properties:

  • sql: a string containing the query, with question marks ? where escaped values should be inserted.
  • values: an array of values to be used with the query.

You can pass this object directly to the query method of the mysql library:

mysql.query(
	q.select('Cool!').build(),
	(err, result) => {
		console.log(result)
	}
)
q.select('column')
	.where('id', 3)
	.build() // { sql: `SELECT column\nWHERE id = ?`, values: [ 3 ]}

q.toString()

Returns a string with values escaped by sqlstring.

q.select('fancy')
    .from('table')
    .where('table.pants', [ 'what\'s up', 'boring' ])
    .toString() // => `SELECT fancy\nFROM table\nWHERE table.pants IN('what\\'s up', 'boring')`

Tagged template strings

sql-concat is also a template tag:

const rainfall = 3
const templateTagResult = q`SELECT galoshes FROM puddle WHERE rain > ${ rainfall }`

templateTagResult.sql // => `SELECT galoshes FROM puddle WHERE rain > ?`
templateTagResult.values // => [ 3 ]

You can pass these results into any method as a value. This allows you to properly parameterize function calls:

const shoeSize = 9
const functionCallResult = q.select('rubbers')
	.from('puddle')
	.where('rain', '>', 4)
	.where('size', q`LPAD(${ shoeSize }, 2, '0')`)
	.build()

const functionCallQuery = `SELECT rubbers\n`
	+ `FROM puddle\n`
	+ `WHERE rain > ? AND size = LPAD(?, 2, '0')`

functionCallResult.sql // => functionCallQuery

functionCallResult.values // => [ 4, 9 ]

Long-shot feature

Some syntax for generating nested clauses conditionally would be nice, so you could easily generate something like this dynamically:

WHERE important = ? AND (your_column = ? OR your_column = ? OR something_else LIKE ?)

Maybe something like:

const whereCondition = q.parenthetical('OR')
	.equal('your_column', true)
	.equal('your_column', randomVariable)
	.like('something_else', anotherVariable)

const query = q.select('everything')
	.from('table')
	.where('important', true)
	.where(whereCondition)

You can discuss this feature in Issue 3 if you're interested.

Running the tests

  1. clone the repo
  2. navigate to the cloned directory
  3. npm install
  4. npm test

License

WTFPL