IF Commons GitHub

sql/typed/select

Written to: 2 requirements: 2 proved, 0 partly, 0 failing, 0 with no test recorded. 0053-sql-typed FR-004, 0053-sql-typed FR-005

The typed query builder (commons data 0053-sql-typed): from(table) and joins build a scope of typed columns, keyed by the names of the tables in the query; a join gives its table's columns as they are, or nullable where an outer join may leave them out; select names what is wanted and gives the type of a row; the clauses (where, group by, having, order by, limit, offset, distinct), set operations, subqueries and common table expressions follow. A query is data; toSql writes it for a dialect and run runs it through a driver.

const q = from(author)
  .leftJoin(book, (s) => eq(s.book.authorId, s.author.id))
  .where((s) => like(s.author.name, "A%"))
  .groupBy((s) => [s.author.id, s.author.name])
  .select((s) => ({ id: s.author.id, name: s.author.name, books: countOf(s.book.id) }))
  .orderBy((_, o) => [desc(o.books)]);
// rows: { id: number; name: string; books: number }[]

Source: commons/data/sql/typed/select.ts

3 of 7 exports are imported by a test of the run. A test that imports an export may not call it or check what it returns.

Fromclassno test imports it
class From<S extends Scope>

A query that has a table and may have joins and conditions, and has not yet said what it wants.

MemberTypeWhat it is
astOmit<SelectAst, "distinct" | "orderBy" | "projection" | "sets">
scopeS
join<N extends string, R, I, Out extends Scope>(kind: "CROSS" | "FULL" | "INNER" | "LEFT" | "RIGHT", table: Table<R, I, N>, on: ((s: never) => Expr<boolean>) | undefined, widen: Record<string, unknown>) => From<Out>
innerJoin<N extends string, R, I>(table: Table<R, I, N extends keyof S ? never : N>, on: (s: S & { readonly [A in N]: Columns<R>; }) => Expr<boolean>) => From<S & { readonly [A in N]: Columns<R>; }>INNER JOIN: the rows that have a partner.
leftJoin<N extends string, R, I>(table: Table<R, I, N extends keyof S ? never : N>, on: (s: S & { readonly [A in N]: Columns<R>; }) => Expr<boolean>) => From<S & { readonly [A in N]: NullableColumns<R>; }>LEFT JOIN: every row, and the columns of the table are null where there is no partner.
rightJoin<N extends string, R, I>(table: Table<R, I, N extends keyof S ? never : N>, on: (s: S & { readonly [A in N]: Columns<R>; }) => Expr<boolean>) => From<NullAll<S> & { readonly [A in N]: Columns<R>; }>RIGHT JOIN: every row of the table, and the columns of the tables before are null where there is no partner.
fullJoin<N extends string, R, I>(table: Table<R, I, N extends keyof S ? never : N>, on: (s: S & { readonly [A in N]: Columns<R>; }) => Expr<boolean>) => From<NullAll<S> & { readonly [A in N]: NullableColumns<R>; }>FULL JOIN: every row of both; the columns of either are null where there is no partner.
crossJoin<N extends string, R, I>(table: Table<R, I, N extends keyof S ? never : N>) => From<S & { readonly [A in N]: Columns<R>; }>CROSS JOIN: every row with every row.
where(condition: (s: S) => Expr<boolean>) => From<S>A condition on the rows; more than one is all of them.
groupBy(keys: (s: S) => readonly Expr<unknown>[]) => From<S>What the rows are grouped by.
having(condition: (s: S) => Expr<boolean>) => From<S>A condition on the groups.
select<P extends Projection>(projection: (s: S) => P) => Select<S, RowOfProjection<P>>What is wanted: a key and an expression for each column of the row.
fromfunction
function from<R, I, N extends string>(table: Table<R, I, N>): From<{ readonly [A in N]: Columns<R>; }>

Starts a query from a table.

Imported by 36 tests of the run (36 passed, 0 failed): the library's results

keyIsfunction
function keyIs<R, I, N extends string>(table: Table<R, I, N>, key: { readonly [K in keyof R & string]?: R[K] | undefined; }): Expr<boolean>

The condition that a row of a table is the one with a key: every column of the key equals its value.

Imported by 7 tests of the run (7 passed, 0 failed): the library's results

RowOfProjectiontypeno test imports it
type RowOfProjection<P extends Projection> = { [K in keyof P]: ValueOf<P[K]> };

The row a projection gives.

Runnerinterface
interface Runner

Where a query is run: a driver and the dialect it is written for.

MemberTypeWhat it is
driverDriver
dialectDialect

Imported by 51 tests of the run (51 passed, 0 failed): the library's results

Scopetypeno test imports it
type Scope = { readonly [alias: string]: { readonly [key: string]: Expr<unknown> } };

The columns that are in scope: the columns of each table of the query, by its name.

Selectclassno test imports it
class Select<S extends Scope, Row>

A query that says what it wants; Row is the type of a row it gives.

MemberTypeWhat it is
row?RowThe type of a row, for the type checker only.
astSelectAst
scopeS
keysreadonly string[]
distinct() => Select<S, Row>SELECT DISTINCT.
orderBy(order: (s: S, out: { readonly [K in keyof Row & string]: Expr<Row[K]>; }) => readonly Order[]) => Select<S, Row>The order: terms made of the columns in scope or of the columns of the row, by their keys.
limit(n: number) => Select<S, Row>At most this many rows.
offset(n: number) => Select<S, Row>Skip this many rows.
set(op: "EXCEPT" | "INTERSECT" | "UNION" | "UNION ALL", other: Select<Scope, Row>) => Select<S, Row>
union(other: Select<Scope, Row>) => Select<S, Row>The rows of both, without repeats; the other must give the same row.
unionAll(other: Select<Scope, Row>) => Select<S, Row>The rows of both, with repeats.
intersect(other: Select<Scope, Row>) => Select<S, Row>The rows that are in both.
except(other: Select<Scope, Row>) => Select<S, Row>The rows of this that are not in the other.
toSql(dialect: Dialect) => Result<string, TypedError>The SQL for a dialect, or why it cannot be written (a value the dialect cannot hold).
run(on: Runner) => Promise<readonly Row[]>Runs it and gives the rows, as the driver gives them with a bigint made a number.
first(on: Runner) => Promise<Row | undefined>At most one row: the first, or none.
asTable<N extends string>(alias: N) => Table<Row, never, N>As a table that a query may read from, under a name: FROM (select) AS name.
asCte<N extends string>(name: N) => Table<Row, never, N>As a common table expression: WITH name AS (select), which the query that uses it begins with.
asValue() => Expr<Row[keyof Row] | null>As a value, for a query whose select has one column and gives one row: (select).
exists() => Expr<boolean>EXISTS (select).
contains<T>(a: Expr<T>) => Expr<boolean>a IN (select), for a select of one column of the type of a.
excludes<T>(a: Expr<T>) => Expr<boolean>a NOT IN (select).

Titles, headings, exports, tables, columns and requirements. Esc closes.