Formed in 2009, the Archive Team (not to be confused with the archive.org Archive-It Team) is a rogue archivist collective dedicated to saving copies of rapidly dying or deleted websites for the sake of history and digital heritage. The group is 100% composed of volunteers and interested parties, and has expanded into a large amount of related projects for saving online and digital history.
javascript template literals to format sql
Transforms a template literal in an object that can be read by node-postgres.
Features
- Written in Typescript
- Lightweight (less than 50 lines of code)
- Fully tested (100% coverage)
- Works with nested sql tags
- Works with conditions inside expressions
- Compatible with node-postgres, with a useful shorthand
Installation
npm install @sequencework/sql --save
(or with yarn, yarn add @sequencework/sql)
Usage
const sql = require('@sequencework/sql')
const yearRange = [1983, 1992]
const query = sql`
select * from movies
where
year >= ${yearRange[0]}
and year <= ${yearRange[1]}
`
// query looks like this:
// {
// text: 'select * from books where author = $1 and year = $2',
// values: [1983, 1992]
// }You can also use conditions:
const sql = require('@sequencework/sql')
const findBookByAuthor = author => sql`
select * from books
${
// if author is undefined, it is ignored in the query
author && sql`where author = ${author}`
}
`
// findBookByAuthor() looks like this:
// {
// text: 'select * from books',
// values: []
// }
// findBookByAuthor('steinbeck') looks like this:
// {
// text: 'select * from books where author = $1',
// values: ['steinbeck']
// }undefined. If it is false, it will be added as a value.
const filterThisYear = false
// does not work as expected
sql`
select * from books
${filterThisYear && sql`where year = 2018`}
`
// instead you should do
sql`
select * from books
${filterThisYear ? sql`where year = 2018` : undefined}
`It's also possible to pass raw, unescaped data to your queries. For that, use sql.raw:
const tableName = 'books'
const query = sql`select * from ${sql.raw(tableName)}`Example with node-postgres
We start by creating a function:
// movies.js
const sql = require('@sequencework/sql')
const listMoviesByYear = async (db, yearRange) => {
const { rows } = await db.query(sql`
select * from movies
where
year >= ${yearRange[0]}
and year <= ${yearRange[1]}
`)
return rows
}
module.exports = { listMoviesByYear }Then, we create a singleton for the connection pool, like recommended by brianc, node-postgres's creator.
// db.js
const { Pool } = require('pg')
// we create a singleton here for the connection pool
const db = new Pool()
module.exports = dbFinally, we connect everything:
// main.js
const db = require('./db')
const { listMoviesByYear } = require('./movies')
const main = async () => {
const movies = await listMoviesByYear(db, [1983, 1992])
console.log(movies)
}
main()We can even create a transaction (useless in this example, but it's just to show that our previous function is reusable):
const main = async () => {
// we get a client
const client = await db.connect()
try {
await client.query('BEGIN')
const movies = await listMoviesByYear(client, [1983, 1992])
await client.query('COMMIT')
} catch (e) {
await client.query('ROLLBACK')
} finally {
client.release()
}
console.log(movies)
}Shorthand for postgres
Since we
const sql = require('@sequencework/sql/pg') // ⚠️ we import @sequencework/sql/pg
// main export stays the same
const query = sql`select * from movies where id = ${id}`
// sql.raw is also there
const booksTable = 'books'
const booksQuery = sql`select * from ${sql.raw(booksTable)}`
// default pg result object: https://node-postgres.com/api/result
const { rows, rowCount } = await sql.query(db)`select * from movies`
// helpers
const movies = await sql.many(db)`select * from movies`
const movie = await sql.one(db)`select * from movies where id = ${id}`
const nbMovie = await sql.count(
db
)`update from movies set name = ${name} where id = ${id}`You can then rewrite the previous listMoviesByYear function in a much more concise way
const sql = require('@sequencework/sql/pg') // ⚠️ we import @sequencework/sql/pg
const listMoviesByYear = async (db, yearRange) => sql.many(db)`
select * from movies
where
year >= ${yearRange[0]}
and year <= ${yearRange[1]}
`Usage with TypeScript
sql comes with its TypeScript declaration file. You can directly use it within your TypeScript projects:
import sql = require('@sequencework/sql')
const yearRange: ReadonlyArray<number> = [1983, 1992]
const query = sql`
select * from movies
where
year >= ${yearRange[0]}
and year <= ${yearRange[1]}
`More
This package is inspired by the great sql-template-strings. Some interesting features that we were missing:
- nested
sqltags - ignore
undefinedexpressions insql
So we made this
