Skip to content

Instantly share code, notes, and snippets.

@qcp

qcp/README.md

Last active October 28, 2025 15:44
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save qcp/a5c41775987200262c062ff8b4ef18ad to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save qcp/a5c41775987200262c062ff8b4ef18ad to your computer and use it in GitHub Desktop.
Download ZIP
JS SQL Strings Template
Raw
README.md

Example how to use JS temlate strings as SQL query generator

VS code extention for highliting

Purpose:

  • Generate SQL query template with сcomplex logic and sub-templates
  • Save params TS typing (unlike template engine mustache/handlebars/eta)
  • Safe value injection

Example:

export const buildMetricCte = (metric: IQueryMetricParams, params: IBaseQueryParams) => sql`
select 
  ${sql.safe(sqlMetrics[metric.key].column)} as ${sql.doubleQuote(metric.qid)}
from ${sql.safe(sqlMetrics[metric.key].table)}
where 1 = 1
  ${buildFilters(metric.filters, params.localFilters, params.globalFilters)}
`

export const query = (params: ISingleParams) => {
  const { metric, ...options } = params
  return sql`
    with src as (
      ${buildMetricCte(metric, options)}
    )
    select *
    from src
  `
}
Raw
index.test.ts
// @vitest-environment node
import { expect, it } from 'vitest'
import { sql } from './templater'
it('SQL escaping', () => {
expect(sql.escape(1)).eq('1')
expect(sql.escape(1.100001)).eq('1.100001')
expect(sql.escape(-1.100001)).eq('-1.100001')
expect(sql.escape('some string')).eq('\'some string\'')
expect(sql.escape('some string \' with singlequote')).eq('\'some string \\\' with singlequote\'')
expect(sql.escape(null)).eq('NULL')
expect(sql.escape(undefined)).eq('NULL')
expect(sql.escape({ hi: 2 })).eq(`'{"hi":2}'`)
expect(sql.escape([1, 2, '3'])).eq(`1, 2, '3'`)
})
it('SQL helpers', () => {
expect(sql.singleQuote('hi').toString()).eq('\'hi\'')
expect(sql.singleQuote('hi\'t').toString()).eq('\'hi\\\'t\'')
expect(sql.doubleQuote('hi').toString()).eq('"hi"')
expect(sql.doubleQuote('hi"t').toString()).eq('"hi\\"t"')
expect(sql.safe('hi"t').toString()).eq('hi"t')
expect(sql.safe('hi\'t').toString()).eq('hi\'t')
expect(sql.safe('hi`t').toString()).eq('hi`t')
})
it('nested SQL with padding', () => {
const nestedSql = sql`
select
1
, 2
from table
`
const finalSql = sql`
with src (
${nestedSql}
)
select *
from src
`
const wantedSql = sql`
with src (
select
1
, 2
from table
)
select *
from src
`
expect(finalSql.toString()).eq(wantedSql.toString())
})
it('list of nested SQL with padding', () => {
const array = [1, 2, 3]
const finalSql = sql`
with src (
${array.flatMap((a, i) => [
i != 0 ? sql`union all` : sql``,
sql`select ${a} as t from table`,
])}
)
select *
from src
`
const wantedSql = sql`
with src (
select 1 as t from table
union all
select 2 as t from table
union all
select 3 as t from table
)
select *
from src
`
expect(finalSql.toString()).eq(wantedSql.toString())
})
Raw
index.ts
class SafeValue<T> {
#value: T
constructor(value: T) {
this.#value = value
}
toString() {
return `${this.#value}`
}
}
/** Get leading space count */
function getLeadingSpaceCount(text: string): number {
const match = text.match(/^([ ]*)/)
return match?.at(1)?.length ?? 0
}
/** Get last line ending space count */
function getLastLineEndSpaceCount(text: string): number {
const match = text.match(/.*\n?([ ]*)$/)
return match?.at(1)?.length ?? 0
}
type LinePadding = { spaceCount: number, padFirstLine: boolean }
class SafeSql {
#value: string
constructor(value: string) {
this.#value = value
}
#normalize({ spaceCount, padFirstLine }: LinePadding) {
const lines = this.#value.split('\n')
// Remove empty lines
.filter(line => line.trim() != '')
const minLeadingSpaces = Math.min(...lines.map(getLeadingSpaceCount))
return lines
// Pad lines if needed, (for nested text)
.map((line, idx) => ((idx > 0 || padFirstLine) ? ' '.repeat(spaceCount) : '') + line)
// Remove global left padding
.map(line => line.slice(minLeadingSpaces))
.join('\n')
}
toString(pad?: LinePadding) {
return this.#normalize(pad ?? { spaceCount: 0, padFirstLine: false })
}
}
function safe<T>(value: T): SafeValue<T> {
return new SafeValue<T>(value)
}
function singleQuote(value: string): SafeValue<string> {
return new SafeValue('\'' + value.replaceAll(`'`, `\\'`) + '\'')
}
function doubleQuote(value: string): SafeValue<string> {
return new SafeValue('"' + safe(value.replaceAll(`"`, `\\"`)) + '"')
}
function escape(value: unknown): string {
if (value == null)
return 'NULL'
switch (typeof value) {
case 'boolean':
return value ? 'TRUE' : 'FALSE'
case 'number':
return `${value}`
case 'bigint':
return `'${value}'`
case 'object':
return Array.isArray(value)
? value.map(a => escape(a)).join(', ')
: singleQuote(JSON.stringify(value)).toString()
case 'string':
return singleQuote(value).toString()
case 'undefined':
return 'NULL'
default:
throw new Error(`Unknown type: ${typeof value}`)
}
};
type ISqlParams = boolean | number | string | undefined | null | object | SafeValue<unknown> | SafeSql
function sql(strings: TemplateStringsArray, ...values: ISqlParams[]) {
let str = ''
for (const [i, s] of strings.entries()) {
str += s
if (i < values.length) {
const value = values[i]
if (value instanceof SafeValue)
str += value.toString()
else if (value instanceof SafeSql)
str += value.toString({
spaceCount: getLastLineEndSpaceCount(str),
padFirstLine: false, // Cause of parent template has it
})
else if (Array.isArray(value) && value.every(v => v instanceof SafeSql))
str += new SafeSql(value.join('\n')).toString({
spaceCount: getLastLineEndSpaceCount(str),
padFirstLine: false, // Cause of parent template has it
})
else
str += escape(value)
}
}
return new SafeSql(str)
}
sql.escape = escape
sql.safe = safe
sql.singleQuote = singleQuote
sql.doubleQuote = doubleQuote
export { sql }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment