Table of contents

1. From Raw String (Stringified Select Statement)
2. From Nothing
   1. Select
   2. Append Select
   3. Select from Select
3. From Tables
   1. Select star
   2. Select a field
   3. Select distinct
   4. Select star and a field
   5. Select a field and star
   6. Select star of aliases
   7. Select from sub-select
   8. Select from union
   9. Select from join
4. Don’t select the fields object directly
5. Control order of selection

import {
    fromNothing,
    dsql as sql,
    table,
    unionAll,
    fromStringifiedSelectStatement,
    selectStar,
    select,
    SafeString,
} from "../../src";

From Raw String (Stringified Select Statement)

const q = fromStringifiedSelectStatement<"a">(sql`SELECT 1 AS a`);

q.selectStar()
    .orderBy((f) => f.a)
    .stringify();

SELECT
  *
FROM
  (
    SELECT
      1 AS a
  )
ORDER BY
  `a`

From Nothing

Select

fromNothing({
    abc: sql`123 + 456`,
}).stringify();

SELECT
  123 + 456 AS `abc`

Append Select

fromNothing({
    abc: sql(123),
})
    .appendSelect((f) => ({
        def: sql`${f.abc} + 456`,
    }))
    .stringify();

SELECT
  123 AS `abc`,
  `abc` + 456 AS `def`

Select from Select

const initialData = fromNothing({
    it: sql(0),
});

Starting at query top

selectStar(
    select(["it"], initialData).where((f) => sql`${f.it} = 1`)
).stringify();

SELECT
  *
FROM
  (
    SELECT
      `it` AS `it`
    FROM
      (
        SELECT
          0 AS `it`
      )
    WHERE
      `it` = 1
  )

Starting at query root

initialData
    .select(["it"])
    .where((f) => sql`${f.it} = 1`)
    .selectStar()
    .stringify();

SELECT
  *
FROM
  (
    SELECT
      `it` AS `it`
    FROM
      (
        SELECT
          0 AS `it`
      )
    WHERE
      `it` = 1
  )

From Tables

We will use these tables

CREATE TABLE users(id int, age int, name string);
CREATE TABLE admins(id int, age int, name string);

Which are defined in typescript as

const users = table(["id", "age", "name", "country"], "users");

const admins = table(["id", "age", "name", "country"], "adm", "admins");

And a helper function

const MAX = (it: SafeString): SafeString => sql`MAX(${it})`;

Select star

users.selectStar().stringify();

SELECT
  *
FROM
  `users`

Select a field

From top

select(
    //
    (f) => ({ maxAge: MAX(f.age) }),
    users
).stringify();

SELECT
  MAX(`age`) AS `maxAge`
FROM
  `users`

From root

users.select((f) => ({ maxAge: MAX(f.age) })).stringify();

SELECT
  MAX(`age`) AS `maxAge`
FROM
  `users`

Select distinct

admins.select(["name"]).distinct().stringify();

SELECT
  DISTINCT `name` AS `name`
FROM
  `admins` AS `adm`

Select star and a field

users
    .selectStar()
    .appendSelect((f) => ({
        otherAlias: f.name,
    }))
    .stringify();

SELECT
  *,
  `name` AS `otherAlias`
FROM
  `users`

Select a field and star

admins
    .select((f) => ({
        otherAlias: f.adm.name,
    }))
    .appendSelectStar()
    .stringify();

SELECT
  `adm`.`name` AS `otherAlias`,
  *
FROM
  `admins` AS `adm`

Select star of aliases

admins.commaJoin(users).selectStarOfAliases(["users"]).stringify();

SELECT
  users.*
FROM
  `admins` AS `adm`,
  `users`

Select from sub-select

users.selectStar().select(["age"]).selectStar().stringify();

SELECT
  *
FROM
  (
    SELECT
      `age` AS `age`
    FROM
      (
        SELECT
          *
        FROM
          `users`
      )
  )

Select from union

unionAll([users.selectStar(), admins.selectStar()]).select(["age"]).stringify();

SELECT
  `age` AS `age`
FROM
  (
    SELECT
      *
    FROM
      `users`
    UNION ALL
    SELECT
      *
    FROM
      `admins` AS `adm`
  )

Select from join

users
    .join("LEFT", admins)
    .using(["id"])
    .select((f) => ({ "users.name": f.users.name, "adm.name": f.adm.name }))
    .stringify();

SELECT
  `users`.`name` AS `users.name`,
  `adm`.`name` AS `adm.name`
FROM
  `users`
  LEFT JOIN `admins` AS `adm` USING (`id`)

Don’t select the fields object directly

This is not valid. The typescript compiler will prevent this.

users
    // @ts-expect-error
    .select((f) => f);

Control order of selection

Although it works on most cases, order of selection is not guaranteed.

users
    .select((f) => ({
        abc: f.name,
        def: f.id,
    }))
    .stringify();

SELECT
  `name` AS `abc`,
  `id` AS `def`
FROM
  `users`

users
    .select((f) => ({
        ["123"]: f.age,
        name: f.name,
        ["456"]: f.id,
    }))
    .stringify();

SELECT
  `age` AS `123`,
  `id` AS `456`,
  `name` AS `name`
FROM
  `users`

To achieve control of the selection order, append each item individually.

users
    .select((f) => ({
        ["123"]: f.age,
    }))
    .appendSelect((f) => ({ name: f.name }))
    .appendSelect((f) => ({
        ["456"]: f.id,
    }))
    .stringify();

SELECT
  `age` AS `123`,
  `name` AS `name`,
  `id` AS `456`
FROM
  `users`

---

This document used eval-md