README.md

class SqlTable extends Sql

Documentation Index

import {SqlTable} from "https://deno.land/x/[email protected]/mod.ts"

This class has

• 2 constructors
• 13 methods: as, join, leftJoin, where, groupBy, insert, insertFrom, select, update, delete, truncate, encode, toString
• 3 protected methods: appendTableName, genAlias, onJoinForeign
• 8 inherited members from Sql

🔧 constructor(cloneFrom: SqlTable)

🔧 constructor(sqlSettings: SqlSettings, tableName: string, strings?: string[], params?: unknown[])

⚙ as(tableAlias: string): this

Set table alias.

⚙ join(tableName: string, alias: string="", 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. The method returns a new SqlTable object that has everything from the original object, plus the new join.

⚙ leftJoin(tableName: string, alias: string, onExpr: string | Sql): this

Adds a LEFT JOIN. 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.

⚙ where(whereExpr: string | Sql): this

Adds WHERE condition for SELECT, UPDATE and DELETE queries. The method returns a new SqlTable object that has everything from the original object, plus the new condition. 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).

⚙ 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.

⚙ insert(rows: Iterable<Record<string, unknown>>, onConflictDo: "" | "nothing" | "replace" | "update" | "patch"=""): this

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.

⚙ insertFrom(names: string[], select: Sql, onConflictDo: "" | "nothing" | "replace"=""): this

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)

⚙ select(columns: string | string[] | Sql="", orderBy: OrderBy="", offset: number=0, limit: number=0): this

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.

⚙ update(row: Record<string, unknown>): this

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.

⚙ delete(): this

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).

⚙ truncate(): this

⚙ override encode(putParamsTo?: unknown[], mysqlNoBackslashEscapes: boolean=false, useBuffer?: Uint8Array, useBufferFromPos: number=0, defaultParentName?: Uint8Array): Uint8Array

If useBuffer is provided, and it has enough size, will encode to it, and return a useBuffer.subarray(0, N). Else, will return a subarray of a new Uint8Array. If useBufferFromPos is provided, will append to the useBuffer after this position.

⚙ override toString(putParamsTo?: unknown[], mysqlNoBackslashEscapes: boolean=false): string

⚙ protected appendTableName(tableName: string): string

This function is called every time a quoted table name must be appended to the query. Subclasses can override this function to convert table names and maybe add schema prefixes. The query generation starts when this object is asked to be converted to string or to bytes, so this function will not be called before this. This function must then return the converted table name without qualifiers. Default implementation:

this.append(sql`"${tableName}"`);
return tableName;

⚙ protected genAlias(name: string): string

⚙ protected onJoinForeign(_tableName: string, _alias: string, _columnName: string): string