This library assists developers to generate SQL queries for MySQL, PostgreSQL, SQLite and Microsoft SQL Server. It's designed for those who's interested in utilizing the true power of relational databases (not a "no-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, to generate "WHERE" expressions, etc.
- Generate SELECT, INSERT, UPDATE, DELETE and TRUNCATE queries from parts
This library provides the following functions:
- mysqlQuote(value: unknown, noBackslashEscapes=false)
- pgsqlQuote(value: unknown, unused=false)
- sqliteQuote(value: unknown, unused=false)
- mssqlQuote(value: unknown, 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@v1.0.1/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
- ReadableStream 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@v1.0.1/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}'
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@v1.0.1/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).
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).
ReadableStream
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.
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).
3. "${param}*"
, "${param}+"
, "${param},"
- Escape a list of identifiers (also can use backticks instead of quotes).
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@v1.0.1/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
The same as [3], but qualifies each identifier with specified parent name.
import {mysql as sql} from 'https://deno.land/x/polysql@v1.0.1/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
Square brackets will be replaced with parentheses. The parameter must be iterable.
import {mysql as sql} from 'https://deno.land/x/polysql@v1.0.1/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@v1.0.1/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))
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@v1.0.1/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@v1.0.1/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 <> '')
6. ${param}
or parent_name.${param}
(not enclosed) - Like (${param})
, but allows commas on top level.
import {mysql as sql} from 'https://deno.land/x/polysql@v1.0.1/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
7. {parent_name.${param}}
, {parent_name.${param},}
- Generate equations separated with commas (the parent_name
is optional).
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@v1.0.1/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@v1.0.1/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@v1.0.1/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
8. {parent_name.${param}&}
- Generate equations separated with "AND" operations (the parent_name
is optional).
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@v1.0.1/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))
9. {parent_name.${param}|}
- Generate equations separated with "OR" operations (the parent_name
is optional).
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@v1.0.1/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))
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@v1.0.1/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@v1.0.1/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
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@v1.0.1/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
*/
12. "${parent_name}.${param}*"
, (${parent_name}.${param})
, ${parent_name}.${param}
, {${parent_name}.${param}}
- Takes the parent_name
from a variable.
In [3b], [5], [6], [7], [8], [9] and [10] the parent qualifier name can be taken from a variable.
sql
template function returns object of Sql
class.
import {mysql as sql, Sql} from 'https://deno.land/x/polysql@v1.0.1/mod.ts';
let s: Sql = sql`SELECT 2*2`;
Sql
objects can be appended to and concatenated:
Sql.append(other: Sql): this
Sql.concat(other: Sql): Sql
append()
modifies current object, and concat()
returns a new one.
import {mysql as sql} from 'https://deno.land/x/polysql@v1.0.1/mod.ts';
const id = 10;
const s = sql`SELECT * FROM articles WHERE id='${id}'`;
const where = `name <> ''`;
const s2 = s.concat(sql` AND (${where})`); // concat() returns a new object
console.log('' + s2); // prints: SELECT * FROM articles WHERE id=10 AND (`name` <> '')
s.append(sql` AND (${where})`); // append() modifies current object
console.log('' + s); // prints the same
Also Sql
objects can be converted to bytes.
Sql.encode(putParamsTo?: unknown[], 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 ReadableStream
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@v1.0.1/mod.ts';
const message = 'a'.repeat(100);
const params: unknown[] = [];
const writer = Deno.stdout.writable.getWriter();
try
{ const sqlBytes = sql`SELECT '${message}'`.encode(params);
await writer.write(sqlBytes); // prints: SELECT ?
console.log('');
console.log(params); // prints: ['aaa...']
}
finally
{ writer.releaseLock();
}
If putParamsTo
is not provided, ReadableStream
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 allocated).
Sql
objects can be stringified.
Sql.toString(putParamsTo?: unknown[], mysqlNoBackslashEscapes=false): string
This function calls Sql.encode()
, and then converts the result to string.
Sql.toSqlBytesWithParamsBackslashAndBuffer(putParamsTo: unknown[]|undefined, mysqlNoBackslashEscapes: boolean, useBuffer: Uint8Array)
This function is the same as encode()
, but with 3 mandatory parameters.
This is for optimal support of this MySQL driver.
This public property of Sql
object 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 set to 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 to be a function name), and for other identifiers.
import {mysql as sql, SqlSettings, SqlMode} from 'https://deno.land/x/polysql@v1.0.1/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 HAVING JOIN LIMIT OFFSET ON SELECT WHERE
.
To print the default policy, you can do:
import {SqlSettings, SqlMode} from 'https://deno.land/x/polysql@v1.0.1/mod.ts';
let settings = new SqlSettings(SqlMode.MYSQL);
console.log('Identifiers: ', settings.idents);
console.log('Functions: ', settings.functions);
Please note, that quoting policy is only applied to safe SQL fragments that you embed using sql`(${expr})`
or sql`${expr}`
.
If you want to use custom policy all the time, you can write your own version of sql()
function:
import {Sql, SqlSettings, SqlMode, SqlTable} from 'https://deno.land/x/polysql@v1.0.1/mod.ts';
const DEFAULT_SETTINGS = new SqlSettings(SqlMode.MYSQL, '!bad forbidden', 'calc_stats');
const sql = new Proxy
( function sql(strings: TemplateStringsArray, ...params: unknown[])
{ return new Sql(DEFAULT_SETTINGS, [...strings], params);
} as Record<string, SqlTable> & {(strings: TemplateStringsArray, ...params: unknown[]): SqlTable},
{ get(_target, tableName)
{ if (typeof(tableName) != 'string')
{ throw new Error("Table name must be string");
}
return new SqlTable(DEFAULT_SETTINGS, tableName);
}
}
);
const expr = "calc_stats(bad, good)";
const s = sql`CALL ${expr}`;
console.log('' + s); // prints: CALL calc_stats(`bad`, good)
console.log('' + sql.messages.where('bad=0 AND good=1').select()); // prints SELECT * FROM `messages` WHERE (`bad`=0 AND good=1)
It's a good idea to create SqlSettings
object once, and reuse it, because creating new instance takes time (it builds words index).
As mentioned above there are the following string-template functions:
- mysql and mysqlOnly
- pgsql and pgsqlOnly
- sqlite and sqliteOnly
- mssql and mssqlOnly
These are actually fake functions implemented through Proxy
class. They act as string-template functions, but they're also act like objects from which
you can ask properties. Each property that you ask from the object returns instance of SqlTable
class that represents a table in your database, and you can
generate SELECT, INSERT, UPDATE, DELETE and TRUNCATE queries for this table.
import {mysql as sql} from 'https://deno.land/x/polysql@v1.0.1/mod.ts';
console.log('' + sql.messages.where("id=1").select()); // prints: SELECT * FROM `messages` WHERE (`id`=1)
SqlTable
class extends Sql
.
let table: SqlTable = sql[tableName];
SqlTable
class has the following methods:
- join(): this
- leftJoin(): this
- where(): this
- groupBy(): this
- select(): this
- update(): this
- delete(): this
- insert(): this
- insertFrom(): this
- truncate(): this
All these methods only log what you ask, and the actual query generation will happen when you convert the object to string or bytes.
import {mysql as sql} from 'https://deno.land/x/polysql@v1.0.1/mod.ts';
let s = sql.messages.join('content', 'c', 'content_id = c.id').where("id=1").select("c.*");
// Now s is object that remembers what joins, columns and conditions you wanted to generate
// So, let's convert the object to string to trigger the query generation
console.log('' + s); // prints: SELECT `c`.* FROM `messages` AS `b` INNER JOIN `content` AS `c` ON (`b`.content_id = `c`.id) WHERE (`b`.id=1)
SqlTable.join(tableName: string, alias='', onExpr: string|Sql=''): this
Adds an INNER (if onExpr
is given) or a CROSS join (if onExpr
is blank).
This method can be called multiple times.
SqlTable.leftJoin(tableName: string, alias: string, onExpr: string|Sql): this
Like join()
, but adds a LEFT JOIN.
SqlTable.where(whereExpr: string|Sql): this
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(groupByExprs: string|string[]|Sql, havingExpr: string|Sql=''): this
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(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 or array will represent *
-wildcard (select all columns).
OFFSET and LIMIT without ORDER BY are not supported on Microsoft SQL Server.
SqlTable.update(row: Record<string, unknown>): 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(): 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(rows: Iterable<Record<string, unknown>>, 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. If duplicate key, deletes the whole conflicting row, and inserts new in place of it.onConflictDo=='update'
is only supported for MySQL and SQLite. If duplicate key, updates the existing record with the new values.onConflictDo=='patch'
is only supported for MySQL. If duplicate key, updates only empty (null, 0 or '') columns of the existing record with the new values.
SqlTable.insertFrom(names: string[], select: Sql, on_conflict_do: ''|'nothing'|'replace' = ''): Sql
Generates "INSERT INTO (...) SELECT ..." query.
import {mysql as sql} from 'https://deno.land/x/polysql@v1.0.1/mod.ts';
let s = sql.t_log.insertFrom(['c1', 'c2'], sql.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)
SqlTable.truncate(): Sql
Generates TRUNCATE TABLE query where supported (all but SQLite), and for others generates "DELETE FROM".
SqlTable
class extends Sql
. You can also extend SqlTable
itself in order to add your own custom methods.
One practical reason for this is to override it's (only one) protected method called appendTableName()
.
This method controls how table names are generated in the queries.
In this method you can for example add prefixes to each table name, or to qualify some or all tables with schema prefix.
The default implementation in SqlTable
class looks like this:
protected appendTableName(tableName: string)
{ this.append(sql`"${tableName}"`);
return tableName;
}
This method is called every time a quoted table name must be appended to the query.
After this function appended the (converted) table name, it must then return this name without qualifiers.
The default implementation shown above doesn't change the name, and doesn't add qualifiers.
The following implementation will convert table names.
In this implementation i redefine the sql
proxy object in the same way that this library does.
import {SqlSettings, SqlMode, SqlTable} from 'https://deno.land/x/polysql@v1.0.1/mod.ts';
const SQL_SETTINGS_MYSQL = new SqlSettings(SqlMode.MYSQL);
class SqlTableCustom extends SqlTable
{ private schema = 'sc';
protected appendTableName(tableName: string)
{ tableName = 't_' + tableName;
this.append(!this.schema ? sql`"${tableName}"` : sql`"${this.schema}"."${tableName}"`);
return tableName;
}
}
const sql = new Proxy
( function sql(strings: TemplateStringsArray, ...params: unknown[])
{ return new SqlTableCustom(SQL_SETTINGS_MYSQL, '', [...strings], params);
} as Record<string, SqlTableCustom> & {(strings: TemplateStringsArray, ...params: unknown[]): SqlTableCustom},
{ get(_target, table_name)
{ if (typeof(table_name) != 'string')
{ throw new Error("Table name must be string");
}
return new SqlTableCustom(SQL_SETTINGS_MYSQL, table_name);
}
}
);
console.log('' + sql.messages.where('id=1').select()); // prints: SELECT * FROM `sc`.`t_messages` WHERE (`id`=1)
Another reason for adding custom methods to SqlTable
is to add query()
method that will cooperate with your database driver.
import {SqlSettings, SqlMode, SqlTable} from 'https://deno.land/x/polysql@v1.0.1/mod.ts';
const DEFAULT_SETTINGS = new SqlSettings(SqlMode.MYSQL);
/** Class for communication with database server (through your preferred driver)
**/
class Connection
{ constructor(private dsn: string)
{ // TODO: Lazy connect to server
console.log(`Connecting to ${this.dsn}`);
}
async query(sql: string, params: unknown[])
{ // TODO: Make actual query to the server
console.log(`Querying on ${this.dsn}: ${sql} -- `, params);
}
async close()
{ // TODO: disconnect
console.log(`Disconnecting from ${this.dsn}`);
}
}
/** Extend `SqlTable` to add custom methods
**/
class SqlTableCustom extends SqlTable
{ constructor(private connection: Connection, sqlSettings: SqlSettings, tableName: string, strings?: string[], params?: unknown[])
{ super(sqlSettings, tableName, strings, params);
}
/** My custom method on `SqlTable`
**/
query()
{ const putParamsTo: unknown[] = [];
return this.connection.query(this.toString(putParamsTo), putParamsTo);
}
}
/** Connect to the database, and return object for querying it.
`dsn` - "Data Source Name" (usually server host, port and database name)
**/
function connect(dsn: string)
{ const connection = new Connection(dsn);
return new Proxy
( function(strings: TemplateStringsArray, ...params: unknown[])
{ return new SqlTableCustom(connection, DEFAULT_SETTINGS, '', [...strings], params);
} as
Record<string, SqlTableCustom> &
{ (strings: TemplateStringsArray, ...params: unknown[]): SqlTableCustom;
[Symbol.asyncDispose](): Promise<void>;
},
{ get(_target, tableName)
{ if (typeof(tableName) != 'string')
{ if (tableName == Symbol.asyncDispose)
{ return () => connection.close();
}
throw new Error("Table name must be string");
}
return new SqlTableCustom(connection, DEFAULT_SETTINGS, tableName);
}
}
);
}
// Get connection
await using sql = connect('localhost:3306');
// Query
await sql`SELECT * FROM links WHERE url='${import.meta.url}'`.query(); // prints: Querying on localhost:3306: SELECT * FROM links WHERE url='...'
await sql.messages.where('id=1').select().query(); // prints: Querying on localhost:3306: SELECT * FROM `messages` WHERE (`id`=1)