extra-sql-builder

Install

1npm install --save extra-sql-builder
2# or
3yarn add extra-sql-builder

Motivation

It is hard to create SQL statements programmatically:

• Concatenating strings is ugly and dangerous.
• Traditional SQL builders are not intuitive.
• ORMs are too indirect and have poor performance.

Usage

1// INSERT INTO my_table (id, value)
2// VALUES (1, 'hello'), (2, 'world');
3
4const cond = true
5
6const result1 = sql(
7  INSERT_INTO('my_table', ['id', 'value'])
8, VALUES(
9    [integer(1), text('hello')],
10    cond && [integer(2), text('world')]
11  )
12)
13
14// or
15const result2 = sql(
16 'INSERT INTO my_table (id, value)'
17, VALUES(
18    [integer('1'), text('hello')]
19  , cond && [integer('2'), text('world')]
20  )
21)
22
23// or
24const result3 = sql`
25  INSERT INTO my_table (id, value)
26  VALUES (1, 'hello')
27  ${cond && `, (2, 'world')`};
28`
29
30// or
31const result4 = `
32  INSERT INTO my_table (id, value)
33  ${VALUES(
34    [integer('1'), text('hello')]
35  , cond && [integer('2'), text('world')]
36  )};
37`
38
39// or
40const values = VALUES([integer('1'), text('hello')])
41if (cond) {
42  values.values.push([integer('2'), text('world')])
43}
44
45const result5 = `
46  INSERT INTO my_table (id, value)
47  ${values};
48`

API

sql

1function sql(...fragments: Array<string | Falsy>): string
2function sql(strings: TemplateStringsArray, ...values: unknown[]): string
3function sql(...args: unknown[]): string

ParameterCollector

1class ParameterCollector<T> {
2  constructor(prefix: string)
3
4  add(value: T): string
5  toRecord(): Record<string, T>
6  toArray(): T[]
7}

Examples

Named parameters

1const collector = new ParameterCollector('$param')
2
3query(
4  sql`
5    INSERT INTO table (value)
6    VALUES (${collector.add(123)})
7         , (${collector.add(456)})
8  `
9  // INSERT INTO table (value)
10  // VALUES ($param1)
11  //      , ($param2)
12, collector.toRecord()
13  // {
14  //   param1: 123
15  // , param2: 456
16  // }
17)

Indexed parameters

1const collector = new ParameterCollector('$')
2
3query(
4  sql`
5    INSERT INTO table (value)
6    VALUES (${collector.add(123)})
7         , (${collector.add(456)})
8  `
9  // INSERT INTO table (value)
10  // VALUES ($1)
11  //      , ($2)
12, collector.toArray()
13  // [123, 456]
14)

Values

1function boolean(val: boolean): string
2function nullableBoolean(val: Nullable<boolean>): string
3
4function integer(val: number): string
5function nullableInteger(val: Nullable<number>): string
6
7function json(val: object): string
8function nullableJson(val: Nullable<object>): string
9
10function text(val: string): string
11function nullableText(val: Nullable<string>): string

Fragments

1function AND(condition: string): string
2function DELETE_FROM(table: string): string
3function FROM(...tables: Array<string | Falsy>): string
4function FULL_OUTER_JOIN(table: string): string
5function GROUP_BY(...fields: Array<string | Falsy>): string
6function HAVING(condition: string): string
7function INNER_JOIN(table: string): string
8function INSERT_INTO(table: string, fields: Array<string | Falsy>): string
9function INTO(table: string): string
10function LEFT_OUTER_JOIN(table: string): string
11function LIMIT(limit: number): string
12function OFFSET(offset: number): string
13function ON(condition: string): string
14function OR(condition: string): string
15function ORDER_BY(...fields: Array<string | Falsy>): string
16function RIGHT_OUTER_JOIN(table: string): string
17function SELECT(...fields: Array<string | Falsy>): string
18function SET(...statements: string[]): string
19function UNION(all: unknown = false): string
20function UPDATE(table: string): string
21function VALUES<T extends string[] | Falsy>(...values: [T, ...T[]]): string
22function WHERE(condition: string): string

FAQ

What about SQL injection?

As long as you don't take user input as a parameter, there will be no SQL injection vulnerability.