Postgres SQL tag
npm install @kilbergr/pg-sqlThe library is still under development
A library to generate SQL queries for PostgreSQL with the use of sql template tag.
``typescript
const userId = 1;
const name = 'John Doe';
const queryConfig: QueryConfig = sql
SELECT * FROM users WHERE id = :${userId} AND name = :${name};
queryConfig.text; // SELECT * FROM users WHERE id = $1 AND name = $2
queryConfig.values; // [1, 'John Doe']
`
The library is extensible and allows you to add custom SQL functions.
`typescript
const tableName = 'my_schema.my_table';
const where = {
id: { $in: [1, 2, 3] },
created_at: { $gt: new Date('2020-01-01') },
};
const queryConfig: QueryConfig = sql
SELECT * FROM ${Identifier(tableName)}
${Where(where)};`
// SELECT * FROM "my_schema"."my_table" WHERE "id"
// IN ($1, $2, $3) AND "created_at" > $4
queryConfig.text;
// [1, 2, 3, new Date('2020-01-01')]
queryConfig.values;
`bash
npm install @kilbergr/pg-sql
yarn add @kilbergr/pg-sql
`
`typescript`
import { sql, Identifier, Where } from '@kilbergr/pg-sql';
The sql is a template tag that generates a QueryConfig object that can pg
be used as an input with the library.
The sql template tag allows to merging of multiple queries into a single query. sql
The template tag will automatically update the placeholder indexes
to match the new query.
`typescript
const queryOne = sql
SELECT * FROM users WHERE id = $1;
const queryTwo = sql
SELECT * FROM users WHERE name = $1;
const queryConfig: QueryConfig = sql
${queryOne}
UNION
${queryTwo};
// SELECT FROM users WHERE id = $1 UNION SELECT FROM users WHERE name = $2
queryConfig.text;
`
Bind a single value
A simple value can be bound to a query using the :${value} syntax.
`typescript
const userId = 1;
const name = 'John Doe';
const queryConfig: QueryConfig = sql
SELECT * FROM users WHERE id = :${userId} AND name = :${name};
queryConfig.text; // SELECT * FROM users WHERE id = $1 AND name = $2
queryConfig.values; // [1, 'John Doe']
`
Bind an array of values
An array of values can be spread using the ...${value} syntax.
`typescript
const userIds = [1, 2, 3];
const queryConfig: QueryConfig = sql
SELECT * FROM users WHERE id IN ...:${userIds};
queryConfig.text; // SELECT * FROM users WHERE id IN ($1, $2, $3)
queryConfig.values; // [1, 2, 3]
`
Bind a literal value
The values can be also bound in cases where native bindings are not supported.
In this such the value will be treated as a literal string. For example a
creation on a partitioned table. The :L${value} syntax or ...L${value} spread
syntax can be used.
`typescript
const partitionIds = ['my_value', 'my_other_value'];
const queryConfig: QueryConfig = sql
CREATE TABLE my_table_partition_one
PARTITION OF my_table FOR VALUES IN ...:L${partitionIds};
// CREATE TABLE my_table_partition_one
// PARTITION OF my_table FOR VALUES IN ('my_value', 'my_other_value')
queryConfig.text;
queryConfig.values; // []
`
Underlying functions
The value binding syntax is a shorthand for the Value function.
`typescript
const userId = 1;
const name = 'John Doe';
const queryConfig: QueryConfig = sql
SELECT * FROM users WHERE id = ${Value(userId)} AND name = ${Value(name)};
queryConfig.text; // SELECT * FROM users WHERE id = $1 AND name = $2
queryConfig.values; // [1, 'John Doe']
`
The value spread syntax is a shorthand for the Values function.
`typescript
const userIds = [1, 2, 3];
const queryConfig: QueryConfig = sql
SELECT * FROM users WHERE id IN ${Values(userIds, { spreadValues: true })};
queryConfig.text; // SELECT * FROM users WHERE id IN ($1, $2, $3)
queryConfig.values; // [1, 2, 3]
`
The literal value binding syntax is a shorthand for the Literal function.
`typescript
const partitionIds = ['my_value', 'my_other_value'];
const queryConfig: QueryConfig = sql
CREATE TABLE my_table_partition_one
PARTITION OF my_table FOR VALUES IN ${Literal(partitionIds)};
// CREATE TABLE my_table_partition_one
// PARTITION OF my_table FOR VALUES IN ('my_value', 'my_other_value')
queryConfig.text;
queryConfig.values; // []
`
The sql template tag can be extended with custom SQL functions or a SqlTemplateNode.SqlTemplateNode
The is a simple object with a method called parse. It allows you to
build more sophisticated SQL fragment builders. (More below)
See a list of built-in SQL functions:
The Value function is used to bind a value or a list of values to a query.
`typescript
const userId = 1;
const name = 'John Doe';
const queryConfig: QueryConfig = sql
SELECT * FROM users WHERE id = ${Value(userId)} AND name = ${Value(name)};
queryConfig.text; // SELECT * FROM users WHERE id = $1 AND name = $2
queryConfig.values; // [1, 'John Doe']
`
The Values function is used to bind an array of values to a query.
`typescript
const userIds = [1, 2, 3];
const queryConfig: QueryConfig = sql
SELECT * FROM users WHERE id IN ${Values(userIds, { spreadValues: true })};
queryConfig.text; // SELECT * FROM users WHERE id IN ($1, $2, $3)
queryConfig.values; // [1, 2, 3]
`
The Literal function is used to bind a literal value to a query in places where native bindings are not supported.
`typescript
const partitionIds = ['my_value', 'my_other_value'];
const queryConfig: QueryConfig = sql
CREATE TABLE my_table_partition_one
PARTITION OF my_table FOR VALUES IN ${Literal(partitionIds)};
// CREATE TABLE my_table_partition_one
// PARTITION OF my_table FOR VALUES IN ('my_value', 'my_other_value')
queryConfig.text;
queryConfig.values; // []
`
The Where function is used to build a WHERE clause from an object.
`typescript
const where = {
id: { $in: [1, 2, 3] },
created_at: { $gt: new Date('2020-01-01') },
};
const queryConfig: QueryConfig = sql
SELECT * FROM users
${Where(where)};
// SELECT * FROM users WHERE "id" IN ($1, $2, $3) AND "created_at" > $4
queryConfig.text;
// [1, 2, 3, new Date('2020-01-01')]
queryConfig.values;
`
The Where function supports the following operators:
- $eq - Equal$neq
- - Not Equal$gt
- - Greater than$gte
- - Greater than or equal$lt
- - Less than$lte
- - Less than or equal$in
- - In$nin
- - Not in$like
- - Like$ilike
- - ILike$or
- - Or$and
- - And
Example of writing a where clause:
`json`
{
"id": { "$eq": 1 },
"name": { "$like": "John%" },
"$or": {
"created_at": {
"$gt": "2020-01-01",
"$lt": "2020-12-31"
},
"updated_at": { "$gt": "2020-01-01" }
}
}
The In function is used to build an IN clause from an array of values.
`typescript
const userIds = [1, 2, 3];
const queryConfig: QueryConfig = sql
SELECT * FROM users WHERE id ${In(userIds)};
queryConfig.text; // SELECT * FROM users WHERE id IN ($1, $2, $3)
queryConfig.values; // [1, 2, 3]
`
A templating function for sql template tag is a factory that has to returnSqlTagParserContext
an anonymous function that accepts a object. Then it can do whatever it wants with the context and return a void. The SqlTagParserContext object is a builder that allows to addition of new SQL fragments to the query or bind
values.
See an example of how a Between` function can be implemented:
`typescript``
export function Between(from: NumericType, to: NumericType) {
return (context: SqlTagParserContext): void => {
context
.addKeyword('BETWEEN')
.bindValue(from)
.addKeyword('AND')
.bindValue(to);
};
}