polysql
This library assists developers to generate SQL queries for MySQL, PostgreSQL, SQLite and Microsoft SQL Server. It’s designed for those who interested in utilizing the true power of relational databases (not a “non-SQL” SQL). It tries to make queries safe, and migration to different database engine easier.
This library can:
- Quote SQL literals (string, blob, date, …)
- Form certain parts in an SQL query, like names-values in INSERT, in UPDATE, to generate “WHERE” expressions, etc.
- Generate SELECT, INSERT, UPDATE and DELETE queries from parts
Quote SQL literals
This library provides the following functions:
- mysqlQuote(value: any, noBackslashEscapes=false)
- pgsqlQuote(value: any, unused=false)
- sqliteQuote(value: any, unused=false)
- mssqlQuote(value: any, unused=false)
Usually you need to import only one of these functions into your project.
import {mysqlQuote as sqlQuote} from 'https://deno.land/x/polysql/mod.ts';
console.log(sqlQuote(import.meta.url));
Function mysqlQuote()
has second parameter called noBackslashEscapes
.
If it’s true, backslashes in SQL string literals will be assumed not to have special meaning, so mysqlQuote()
will not double backslashes.
It’s important to provide the correct value to this parameter.
Remember that the value of this parameter can change during server session, if user executes a query like SET sql_mode='no_backslash_escapes'
.
The “value” parameter can be one of the following types:
- null, undefined, Javascript functions and Symbol objects produce
NULL
literal - boolean produces
FALSE
orTRUE
literals (0
or1
for Microsoft SQL Server) - number and bigint is printed as is
- Date produces string like
2021-08-26
or2021-08-26 10:00:00
or2021-08-26 10:00:00.123
- typed arrays (like Uint8Array) produce literals like
x'00112233'
(0x00112233
for Microsoft SQL Server) - Sql object will print a string with it’s query
- Deno.Reader will be rejected with exception
- other types will be converted to strings and printed as an SQL string literal
import {mysqlQuote as sqlQuote} from 'https://deno.land/x/polysql/mod.ts';
console.log(sqlQuote(null)); // prints: NULL
console.log(sqlQuote(false)); // prints: FALSE
console.log(sqlQuote(123)); // prints: 123
console.log(sqlQuote('Message')); // prints: 'Message'
console.log(sqlQuote('It\'s another message')); // prints: 'It''s another message'
console.log(sqlQuote(new Date(2000, 0, 1))); // prints: '2000-01-01'
console.log(sqlQuote(new Uint8Array([1, 2, 3]))); // prints: x'010203'
console.log(sqlQuote({id: 1, value: 1.5})); // prints: '{"id":1,"value":1.5}'
Produce parts of SQL queries
This library provides the following string-template functions:
- mysql and mysqlOnly
- pgsql and pgsqlOnly
- sqlite and sqliteOnly
- mssql and mssqlOnly
Usually you need to import only one of these functions into your project.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
let message = `It's the message`;
let number = 0.1;
let column = 'The number';
console.log('' + sql`SELECT '${message}', '${number}' AS "${column}"`); // prints: SELECT 'It''s the message', 0.1 AS `The number`
*Only
allows you to use all the supported features for that SQL dialect, even those that are not supported for other dialects.
Tags without *Only
throw exception if you ask a feature that is not supported by all of MySQL, PostgreSQL, Sqlite and Microsoft SQL Server. So you can switch to different dialect later (e.g. from mysql
to mssql
).
You can mark backtick-quoted Javascript strings with the sql
tag, as in example above, and dollar-brace parameters in this string will be escaped.
How each parameter is escaped depends on quotes that you used in your SQL string, to quote this parameter (in the example above i quoted message
and number
with apostrophes, and column
with double-quotes).
'${param}'
- Escape an SQL value.
1. If the parameter is a string, characters inside it will be properly escaped (if you use mysql
, a mysqlNoBackslashEscapes argument of toString()
or encode()
will be respected - see below).
If the value is a number, quotes around it will be removed.
If it’s a null
, or an undefined
, a Javascript function or a Symbol, it will be substituted with NULL
literal.
If it’s boolean false
or true
, it will be substituted with FALSE
or TRUE
(0
or 1
on Microsoft SQL Server).
Date
objects will be printed as SQL dates.
Typed arrays will be printed like x'0102...'
(0x0102...
on Microsoft SQL Server).
Deno.Reader
objects will be put to putParamsTo
array, if it’s provided to toString()
or encode()
- see below, and the value will be replaced with ‘?’ character.
If putParamsTo
not provided, exception will be thrown.
Objects will be JSON-stringified.
"${param}"
or \`${param}\`
- Escape an identifier (column, table or routine name, etc.).
2. For MySQL double quotes will be replaced with backticks. For others, backticks (if you used them) will be converted to quotes.
Identifier cannot contain ASCII 0 characters (required for PostgreSQL).
"${param}*"
, "${param}+"
, "${param},"
- Escape a list of identifiers (also can use backticks instead of quotes).
3. Generates comma-separated list of quoted identifiers from iterable collection “param”.
"${param}*"
- if the collection is empty, generates *
character (as in SELECT * FROM ...
).
"${param}+"
- throws exception if the collection is empty.
"${param},"
- doesn’t generate any output, if the collection is empty. If it’s not empty, prints a comma after the last identifier.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
let noNames: never[] = [];
let names = ['one', 'two'];
console.log(sql`SELECT "${noNames}*"` + ''); // prints: SELECT *
console.log(sql`SELECT "${names}*"` + ''); // prints: SELECT `one`, `two`
console.log(sql`SELECT "${noNames}," three` + ''); // prints: SELECT three
console.log(sql`SELECT "${names}," three` + ''); // prints: SELECT `one`, `two`, three
"parent_name.${param}*"
, "parent_name.${param}+"
, "parent_name.${param},"
3b. The same as [3], but qualifies each identifier with specified parent name.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
let noNames: never[] = [];
let names = ['one', 'two'];
console.log(sql`SELECT "t1.${noNames}*"` + ''); // prints: SELECT *
console.log(sql`SELECT "t1.${names}*"` + ''); // prints: SELECT `t1`.`one`, `t1`.`two`
console.log(sql`SELECT "t1.${noNames}," three` + ''); // prints: SELECT three
console.log(sql`SELECT "t1.${names}," three` + ''); // prints: SELECT `t1`.`one`, `t1`.`two`, three
[${param}]
- Generate list of SQL values.
4. Square brackets will be replaced with parentheses. The parameter must be iterable.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const ids = [10, 11, 12];
let s = sql`SELECT * FROM articles WHERE id IN [${ids}]`;
console.log('' + s); // prints: SELECT * FROM articles WHERE id IN (10,11,12)
If there are no items in the collection, it generates (NULL)
.
If items in the collection are also iterable, this will generate multidimensional list. 2-Dimensional lists are only supported by MySQL and PostgreSQL. More than 2 dimensions are only supported by MySQL.
import {mysqlOnly as sql} from 'https://deno.land/x/polysql/mod.ts';
const list = [[10, 1], [11, 3], [12, 8]];
let s = sql
` SELECT *
FROM articles AS a
INNER JOIN article_versions AS av ON a.id = av.article_id
WHERE (av.article_id, av.article_version) IN [${list}]
`;
console.log('' + s); // prints: ...WHERE (av.article_id, av.article_version) IN ((10,1),(11,3),(12,8))
(${param})
or (parent_name.${param})
- Embed a safe SQL expression.
5. The inserted SQL fragment will be validated, so it doesn’t contain the following characters (unless quoted): @ $ # ? : [ ] { } ;
, \0
-char, commas except in parentheses, comments, unterminated literals, unbalanced parentheses. Identifiers in this SQL fragment will be quoted according to chosen policy (see below).
Strings in the SQL fragment are always treated as mysqlNoBackslashEscapes
(backslash is regular character), so to represent a string with a new line, you need const expr = "Char_length('Line\n')"
, not const expr = "Char_length('Line\\n')"
.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const expr = "Char_length('Line\n')";
let s = sql`SELECT (${expr})`;
console.log('' + s);
It’s possible to prefix all unqualified identifiers in the SQL fragment with a parent qualifier:
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const expr = "article_id = 10 AND `article_version` = 1 AND a.name <> ''";
let s = sql
` SELECT a.name, av.*
FROM articles AS a
INNER JOIN article_versions AS av ON a.id = av.article_id
WHERE (av.${expr})
`;
console.log('' + s); // prints ...WHERE (`av`.article_id = 10 AND `av`.`article_version` = 1 AND `a`.name <> '')
${param}
or parent_name.${param}
(not enclosed) - Like (${param})
, but allows commas on top level.
6. import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const columns = "name, value";
let s = sql`SELECT ${columns} FROM something WHERE id=1`;
console.log('' + s); // prints: SELECT `name`, `value` FROM something WHERE id=1
{parent_name.${param}}
, {parent_name.${param},}
- Generate equations separated with commas (the parent_name
is optional).
7. The first form throws exception, if there are no fields in the param. The Second form doesn’t complain, and prints comma after the last field.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const row = {name: 'About all', author: 'Johnny'};
let s = sql`UPDATE articles AS a SET {a.${row}} WHERE id=1`;
console.log('' + s); // prints: UPDATE articles AS a SET `a`.`name`='About all', `a`.`author`='Johnny' WHERE id=1
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const row = {name: 'About all', author: 'Johnny'};
let s = sql`UPDATE articles AS a SET {a.${row},} article_date=Now() WHERE id=1`;
console.log('' + s); // prints: UPDATE articles AS a SET `a`.`name`='About all', `a`.`author`='Johnny', article_date=Now() WHERE id=1
If a value is an Sql
object, it’s expression will be used.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const row = {name: 'About all', author: sql`Get_author(id)`};
let s = sql`UPDATE articles AS a SET {a.${row}} WHERE id=1`;
console.log('' + s); // prints: UPDATE articles AS a SET `a`.`name`='About all', `a`.`author`=Get_author(`a`.id) WHERE id=1
{parent_name.${param}&}
- Generate equations separated with “AND” operations (the parent_name
is optional).
8. Converts braces to parentheses. If the param
contains no fields, this will be converted to a FALSE
literal.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const row = {name: 'About all', author: sql`Get_author(id)`};
let s = sql`SELECT * FROM articles AS a WHERE {a.${row}&}`;
console.log('' + s); // prints: SELECT * FROM articles AS a WHERE (`a`.`name`='About all' AND `a`.`author`=Get_author(`a`.id))
{parent_name.${param}|}
- Generate equations separated with “OR” operations (the parent_name
is optional).
9. Converts braces to parentheses. If the param
contains no fields, this will be converted to a TRUE
literal.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const row = {name: 'About all', author: sql`Get_author(id)`};
let s = sql`SELECT * FROM articles AS a WHERE {a.${row}|}`;
console.log('' + s); // prints: SELECT * FROM articles AS a WHERE (`a`.`name`='About all' OR `a`.`author`=Get_author(`a`.id))
{left_parent_name.right_parent_name.${param}}
10. In [7], [8] and [9], you can specify 2 parent qualifiers: one for the left-hand side of the equation, and one for the right. Any of the names can be empty.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const row = {name: 'About all', author: sql`Get_author(id)`};
let s = sql`SELECT * FROM articles AS a INNER JOIN article_content AS ac ON a.id = ac.article_id WHERE {a.ac.${row}&}`;
console.log('' + s); // prints: SELECT * FROM articles AS a INNER JOIN article_content AS ac ON a.id = ac.article_id WHERE (`a`.`name`='About all' AND `a`.`author`=Get_author(`ac`.id))
Example of left name empty:
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const row = {name: 'About all', author: sql`Get_author(id)`};
let s = sql`UPDATE articles AS a SET {.a.${row}} WHERE id=1`;
console.log('' + s); // prints: UPDATE articles AS a SET `name`='About all', `author`=Get_author(`a`.id) WHERE id=1
<${param}>
- Generate names and values for INSERT statement.
11. Parameter must be iterable object that contains rows to insert. Will print column names from the first row. On following rows, only columns from the first row will be used.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
let rows =
[ {value: 10, name: 'text 1'},
{value: 11, name: 'text 2'},
];
console.log('' + sql`INSERT INTO t_log <${rows}> AS excluded ON DUPLICATE KEY UPDATE t_log.name = excluded.name`);
/* prints:
INSERT INTO t_log (`value`, `name`) VALUES
(10,'text 1'),
(11,'text 2') AS excluded ON DUPLICATE KEY UPDATE t_log.name = excluded.name
*/
"${parent_name}.${param}*"
, (${parent_name}.${param})
, ${parent_name}.${param}
, {${parent_name}.${param}}
- Takes the parent_name
from a variable.
12. In [3b], [5], [6], [7], [8], [9] and [10] the parent qualifier name can be taken from a variable.
Sql
object
About The sql
template function returns object of Sql
class.
import {mysql as sql, Sql} from 'https://deno.land/x/polysql/mod.ts';
let s: Sql = sql`SELECT 2*2`;
The Sql
objects can be concatenated:
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
const id = 10;
let s = sql`SELECT * FROM articles WHERE id='${id}'`;
const where = `name <> ''`;
s = s.concat(sql` AND (${where})`);
console.log('' + s); // prints: SELECT * FROM articles WHERE id=10 AND (`name` <> '')
Also the Sql
objects can be stringified, or converted to bytes.
Sql.toString(putParamsTo?: any[], mysqlNoBackslashEscapes=false): string
Sql.encode(putParamsTo?: any[], mysqlNoBackslashEscapes=false, useBuffer?: Uint8Array, useBufferFromPos=0): Uint8Array
Also they have public property called sqlSettings
, that contains the chosen SQL dialect (SqlMode) and quoting policy, that allows to whitelist identifiers in SQL fragments.
Sql.sqlSettings: SqlSettings
If you create the Sql
object using mysql
template function, it’s sqlSettings.mode
will be SqlMode.MYSQL
, for pgsql
it will be SqlMode.PGSQL
, etc.
If you assign a different SqlSettings
object before calling toString()
or encode()
, that different SQL dialect and policy will be used.
The quoting policy has either a whitelist or a blacklist of allowed identifiers, that can remain unquoted. There’re 2 separate lists for functions (any identifier that is followed by a parenthesis is considered a function name), and for other identifiers.
import {mysql as sql, SqlSettings, SqlMode} from 'https://deno.land/x/polysql/mod.ts';
const value1 = "The string is: 'name'. The backslash is: \\";
const value2 = 123.4;
const value3 = null;
const expr1 = "id=10 AND value IS NOT NULL"; // we have 6 raw identifiers here: id, AND, value, IS, NOT, NULL
let select = sql`SELECT '${value1}', '${value2}', '${value3}' FROM t WHERE (${expr1})`;
console.log(select+''); // SELECT 'The string is: ''name''. The backslash is: \\', 123.4, NULL FROM t WHERE (`id`=10 AND `value` IS NOT NULL)
console.log(select.toString(undefined, true)); // SELECT 'The string is: ''name''. The backslash is: \', 123.4, NULL FROM t WHERE (`id`=10 AND `value` IS NOT NULL)
select.sqlSettings = new SqlSettings(SqlMode.MYSQL, 'id not'); // in expr1 will quote: AND, value, IS, NULL
console.log(select+''); // SELECT 'The string is: ''name''. The backslash is: \\', 123.4, NULL FROM t WHERE (id=10 `AND` `value` `IS` NOT `NULL`)
select.sqlSettings = new SqlSettings(SqlMode.PGSQL, '!id not'); // in expr1 will quote: id, NOT
console.log(select+''); // SELECT 'The string is: ''name''. The backslash is: \', 123.4, NULL FROM t WHERE ("id"=10 AND value IS "NOT" NULL)
To create a new SqlSettings
object, that can be assigned to Sql.sqlSettings
property, provide the SQL dialect (SqlMode), the whitelist/blacklist of idents
, and the same for functions
.
If the list starts with !
-char - it’s the blacklist. Identifiers in the list are separated with spaces.
SqlSettings.constructor(mode: SqlMode, idents?: string, functions?: string)
If idents
and/or functions
argument is omitted or undefined
, the default value is used.
For idents
the default value is: AGAINST AND AS ASC BETWEEN CASE CHAR DAY DESC DISTINCT ELSE END HOUR INTERVAL IS LIKE MATCH MICROSECOND MINUTE MONTH NOT NULL OR SECOND SEPARATOR THEN WEEK WHEN XOR YEAR
.
For functions
is: ! FROM JOIN ON SELECT WHERE
.
To print the default policy, you can do:
import {SqlSettings, SqlMode} from 'https://deno.land/x/polysql/mod.ts';
let settings = new SqlSettings(SqlMode.MYSQL);
console.log('Identifiers: ', settings.idents);
console.log('Functions: ', settings.functions);
Sql.encode() function
Sql.encode(putParamsTo?: any[], mysqlNoBackslashEscapes=false, useBuffer?: Uint8Array, useBufferFromPos=0): Uint8Array
This function converts the SQL query to Uint8Array
, that you probably need to pass to your SQL driver.
You can pass an array to the putParamsTo
parameter, so long strings and long typed arrays, and Deno.Reader
objects, that appear in quoted '${value}'
parameters, will be put to this array,
and their SQL representation will be produced as ?
character.
import {mysql as sql} from 'https://deno.land/x/polysql/mod.ts';
let message = 'a'.repeat(100);
let params: any[] = [];
console.log(sql`SELECT '${message}'`.toString(params)); // prints: SELECT ?
console.log(params); // prints: ['aaa...']
If putParamsTo
is not provided, Deno.Reader
objects will be rejected with exception.
The mysqlNoBackslashEscapes
parameter is only respected when using MySQL dialect.
If it’s true, backslashes in SQL string literals will be assumed not to have special meaning, so mysql`'${value}'`
will not double backslashes.
It’s important to provide the correct value to this parameter.
Remember that the value of this parameter can change during server session, if user executes a query like SET sql_mode='no_backslash_escapes'
.
If useBuffer
parameter is provided, and there’s enough space in this buffer, this buffer will be used and a useBuffer.subarray()
of it will be returned from sql.encode()
.
If it’s not big enough, a new buffer will be allocated, as usual.
If useBufferFromPos
parameter is provided together with the useBuffer
, so the produced query will be appended after that position in the buffer, and the contents of useBuffer
before this position will be the part of returned query (even if a new buffer was used).
Sql.toString() function
Sql.toString(putParamsTo?: any[], mysqlNoBackslashEscapes=false): string
It calls Sql.encode()
, and then converts the result to string.
Generate SELECT, INSERT, UPDATE and DELETE queries from parts
This library provides the following constant objects:
- mysqlTables and mysqlOnlyTables
- pgsqlTables and pgsqlOnlyTables
- sqliteTables and sqliteOnlyTables
- mssqlTables and mssqlOnlyTables
Usually you need to import only one of these into your project.
import {mysqlTables as sqlTables} from 'https://deno.land/x/polysql/mod.ts';
console.log('' + sqlTables.messages.where("id=1").select()); // prints: SELECT * FROM `messages` WHERE (`id`=1)
*OnlyTables
allows you to use all the supported features for that SQL dialect, even those that are not supported for other dialects.
*Tables
(without Only
) throw exception if you ask a feature that is not supported by all of MySQL, PostgreSQL, Sqlite and Microsoft SQL Server.
So you can switch to different dialect later (e.g. from mysqlTables
to mssqlTables
).
All the provided *Tables
(and *OnlyTables
) objects are Proxy
objects. Every property you ask from them becomes a table name, and it’s resolved to a SqlTable
object.
let table: SqlTable = sqlTables[tableName];
The SqlTable
class has the following methods:
- join(): SqlTable
- leftJoin(): SqlTable
- where(): SqlTable
- groupBy(): SqlTable
- select(): Sql
- update(): Sql
- delete(): Sql
- insert(): Sql
- insertFrom(): Sql
The 5 latter methods return Sql
objects, with the final query.
SqlTable.join()
SqlTable.join(tableName: string, alias='', onExpr: string|Sql=''): SqlTable
Adds an INNER (if onExpr
is given) or a CROSS join (if onExpr
is blank).
This method can be called multiple times.
The method returns a new SqlTable
object that has everything from the original object, plus the new join.
import {mysqlTables as sqlTables} from 'https://deno.land/x/polysql/mod.ts';
console.log('' + sqlTables.messages.join('content', 'c', 'content_id = c.id').where("id=1").select("c.*"));
// prints: SELECT `c`.* FROM `messages` AS `b` INNER JOIN `content` AS `c` ON (`b`.content_id = `c`.id) WHERE (`b`.id=1)
SqlTable.leftJoin()
SqlTable.leftJoin(tableName: string, alias: string, onExpr: string|Sql): SqlTable
Like join()
, but adds a LEFT JOIN.
SqlTable.where()
SqlTable.where(whereExpr: string|Sql): SqlTable
Adds WHERE condition for SELECT, UPDATE and DELETE queries.
You can call sqlTable.select()
, sqlTable.update()
and sqlTable.delete()
only after calling sqlTable.where()
, or an exception will be thrown.
To explicitly allow working on the whole table, call sqlTable.where('')
(with empty condition).
SqlTable.groupBy()
SqlTable.groupBy(groupByExprs: string|string[]|Sql, havingExpr: string|Sql=''): SqlTable
Adds GROUP BY expressions, and optionally a HAVING expression to the SELECT query.
If groupByExprs
is a string or an Sql
object, it will represent a safe SQL fragment that contains comma-separated list of column expressions.
If it’s string[]
, it will be treated as array of column names.
SqlTable.select()
SqlTable.select(columns: string|string[]|Sql='', orderBy: OrderBy='', offset=0, limit=0): Sql
type OrderBy = string | Sql | {columns: string[], desc?: boolean}
Generates a SELECT query.
If columns
parameter is a string or an Sql
object, it will represent columns as a safe SQL fragment.
If it’s string[]
, it will be treated as array of column names.
Empty string, Sql or array will represent *
-wildcard (select all columns).
OFFSET and LIMIT without ORDER BY are not supported on Microsoft SQL Server.
SqlTable.update()
SqlTable.update(row: Record<string, any>): Sql
Generates an UPDATE query. You can update with joins, but if the first join is a LEFT JOIN, such query is not supported by PostgreSQL. Columns of the base table (not joined) will be updated.
SqlTable.delete()
SqlTable.delete(): Sql
Generates a DELETE query. You can delete with joins, but if the first join is a LEFT JOIN, such query is not supported by PostgreSQL. Will delete from the base table (not joined).
SqlTable.insert()
SqlTable.insert(rows: Iterable<Record<string, any>>, onConflictDo: ''|'nothing'|'replace'|'update'|'patch' = ''): Sql
Generates an INSERT query.
onConflictDo=='nothing'
is only supported for MySQL, PostgreSQL and SQLite. Ignores (doesn’t insert) conflicting rows (if unique constraint fails).onConflictDo=='replace'
is only supported for MySQL and SQLite.onConflictDo=='update'
is only supported for MySQL. If duplicate key, updates the existing record with the new values.onConflictDo=='patch'
is only supported for MySQL If duplicate key, updates empty (null, 0 or ”) columns of the existing record with the new values.
SqlTable.insertFrom()
SqlTable.insertFrom(names: string[], select: Sql, on_conflict_do: ''|'nothing'|'replace' = ''): Sql
Generates “INSERT INTO (…) SELECT …” query.
import {mysqlTables as sqlTables} from 'https://deno.land/x/polysql/mod.ts';
let s = sqlTables.t_log.insertFrom(['c1', 'c2'], sqlTables.t_log_bak.where('id<=100').select(['c1', 'c2']));
console.log('' + s); // prints: INSERT INTO `t_log` (`c1`, `c2`) SELECT `c1`, `c2` FROM `t_log_bak` WHERE (`id`<=100)