SELECT

Select

SELECT

field

const names = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
});
// sql: SELECT "Mountain"."name" FROM "Mountain"
//
// const names: string[]
const mountains = await client.select({
   select: { 
     moutain: field(Moutain, 'name'),
   }
   from: table(Mountain),
});
// sql: SELECT "Mountain"."name" FROM "Mountain"
//
// const names: { mountain: string }[]

all (*)

const mountains = await client.select({
  select: all(Mountain),
  from: table(Mountain),
});
// sql: SELECT "Mountain".* FROM "Mountain"
//
// const mountains: Mountain[]
const mountains = await client.select({
  select: all(),
  from: table(Mountain),
});
// sql: SELECT * FROM "Mountain"
//
// const mountains: any[]

subSelect

const mountains = await client.select({
  select: {
    name: field(Moutain, 'name'),
    firstAscent: subSelect({
      select: min(field(Ascent, 'date')),
      from: table(Ascent),
      where: equal(field(Ascent, 'mountain'), field(Mountain, 'name'))
    }),
  },
  from: table(Mountain),
});
// sql: SELECT 
//         "Mountain"."name", 
//         (SELECT min("Ascent"."date") FROM "Ascent" WHERE "Ascent"."mountain" = "Mountain"."name")) 
//      FROM "Mountain"
//
// const mountains: { name: string, firstAscent: Date }[]

selectFirst

const mountains = await client.selectFirst({
  select: all(Mountain),
  from: table(Mountain),
});
// sql: SELECT "Mountain".* FROM "Mountain" LIMIT 1
//
// const mountains: Mountain

FROM

table

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
});
// sql: SELECT "Mountain"."name" FROM "Mountain"
//
// const mountains: string[]

alias

const mountainAlias = alias(table(Mountain), 'm')
const mountains = await client.select({
   select: field(mountainAlias, 'name'),
   from: mountainAlias,
});
// sql: SELECT "m"."name" FROM "Mountain" "m"
//
// const mountains: string[]

subSelect

const mountainSubSelect = subSelect({
   select: {
      country: field(Mountain, 'country'),
      count: count()
   },
   from: table(Mountain),
   groupBy: field(Mountain, 'country'),
});
const moutainSubSelectAlias = alias(mountainSubSelect, 'm')
const mountains = await client.select({
   select: field(moutainSubSelectAlias, 'country'),
   from: moutainSubSelectAlias,
   where: greaterThan(field(moutainSubSelectAlias, 'count'), 10),
});
// sql: SELECT "m"."country" 
//      FROM (
//        SELECT "Mountain"."country", count(*) 
//        FROM "Mountain" 
//        GROUP BY "Mountain"."country"
//      ) "m"
//      WHERE "m"."count" > 10  
//
// const mountains: string[]

JOIN

join

const mountains = await client.select({
   select: {
     mountain: field(Moutain, 'name'),
     date: field(Ascent, 'date'),
   },
   from: table(Mountain),
   join: [
     join(Ascent, equal(field(Mountain, 'firstAscent'), field(Ascent, 'id')))
   ],
});
// sql: SELECT "Mountain"."name", "Mountain"."country" 
//      FROM "Mountain"
//      JOIN "Mountain"."firstAscent" = "Ascent"."id"
//
// const mountains: { country: string, mountain: number }[]

leftJoin

const mountains = await client.select({
   select: {
     mountain: field(Moutain, 'name'),
     date: field(Ascent, 'date'),
   },
   from: table(Mountain),
   join: [
     leftJoin(Ascent, equal(field(Mountain, 'firstAscent'), field(Ascent, 'id')))
   ],
});
// sql: SELECT "Mountain"."name", "Mountain"."country" 
//      FROM "Mountain"
//      LEFT JOIN "Mountain"."firstAscent" = "Ascent"."id"
//
// const mountains: { country: string, mountain: number }[]

rightJoin

const mountains = await client.select({
   select: {
     mountain: field(Moutain, 'name'),
     date: field(Ascent, 'date'),
   },
   from: table(Mountain),
   join: [
     rightJoin(Ascent, equal(field(Mountain, 'firstAscent'), field(Ascent, 'id')))
   ],
});
// sql: SELECT "Mountain"."name", "Mountain"."country" 
//      FROM "Mountain"
//      RIGHT JOIN "Mountain"."firstAscent" = "Ascent"."id"
//
// const mountains: { country: string, mountain: number }[]

WHERE

equal

const mountains = await client.select({
   select: {
     country: field(Moutain, 'country'),
     mountain: field(Moutain, 'name'),
   },
   from: table(Mountain),
   where: equal(field(Mountain, 'country'), 'CH'),
});
// sql: SELECT "Mountain"."name", "Mountain"."country" 
//      FROM "Mountain"
//      WHERE "Mountain"."country" = 'CH'
//
// const mountains: { country: string, mountain: number }[]

notEqual

const mountains = await client.select({
   select: {
     country: field(Moutain, 'country'),
     mountain: field(Moutain, 'name'),
   },
   from: table(Mountain),
   where: notEqual(field(Mountain, 'country'), 'CH'),
});
// sql: SELECT "Mountain"."name", "Mountain"."country" 
//      FROM "Mountain"
//      WHERE "Mountain"."country" != 'CH'
//
// const mountains: { country: string, mountain: number }[]

greaterThan

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: greaterThan(field(Mountain, 'height'), 1000),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."height" > 1000
//
// const mountains: string[]

greaterEqualThan

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: greaterEqualThan(field(Mountain, 'height'), 1000),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."height" >= 1000
//
// const mountains: string[]

lowerThan

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: lowerThan(field(Mountain, 'height'), 1000),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."height" < 1000
//
// const mountains: string[]

lowerEqualThan

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: lowerEqualThan(field(Mountain, 'height'), 1000),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."height" <= 1000
//
// const mountains: string[]

isNull

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: isNull(field(Mountain, 'firstAscent')),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."firstAscent" IS NULL
//
// const mountains: string[]

isNotNull

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: isNotNull(field(Mountain, 'firstAscent')),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."firstAscent" IS NOT NULL
//
// const mountains: string[]

in

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: isIn(field(Mountain, 'country'), ['CH', 'IT']),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."country" IN ('CH', 'IT')
//
// const mountains: string[]

isNotIn

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: isNotIn(field(Mountain, 'country'), ['CH', 'IT']),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."country" NOT IN ('CH', 'IT')
//
// const mountains: string[]

like

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: like(field(Mountain, 'name'), 'Matter%'),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."name" LIKE 'Matter%'
//
// const mountains: string[]

between

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: between(field(Mountain, 'height'), 1000, 2000),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."height" BETWEEN 1000 AND 2000
//
// const mountains: string[]

notBetween

const mountains = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   where: notBetween(field(Mountain, 'height'), 1000, 2000),
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      WHERE "Mountain"."height" NOT BETWEEN 1000 AND 2000
//
// const mountains: string[]

and

const mountains = await client.select({
   select: {
     country: field(Moutain, 'country'),
     mountain: field(Moutain, 'name'),
   },
   from: table(Mountain),
   where: and(
     equal(field(Mountain, 'country'), 'CH'),
     greaterEqualThan(field(Mountain, 'height'), 2000),
   ),
});
// sql: SELECT "Mountain"."name", "Mountain"."country"
//      FROM "Mountain"
//      WHERE "Mountain"."country" = 'CH' AND "Mountain"."height" > 2000
//
// const mountains: { country: string, mountain: number }[]

or

const mountains = await client.select({
   select: {
     country: field(Moutain, 'country'),
     mountain: field(Moutain, 'name'),
   },
   from: table(Mountain),
   where: or(
     equal(field(Mountain, 'country'), 'CH'),
     greaterEqualThan(field(Mountain, 'height'), 2000),
   ),
});
// sql: SELECT "Mountain"."name", "Mountain"."country"
//      FROM "Mountain"
//      WHERE "Mountain"."country" = 'CH' OR "Mountain"."height" > 2000
//
// const mountains: { country: string, mountain: number }[]

GROUP BY

const names = await client.select({
   select: {
     country: field(Moutain, 'country'),
     count: count(),
   },
   from: table(Mountain),
   groupBy: field(Mountain, 'country'),
});
// sql: SELECT "Mountain"."country", count(*)
//      FROM "Mountain"
//      GROUP BY "Mountain"."country"
//
// const names: { country: string, count: number }[]

HAVING BY

const countries = await client.select({
   select: field(Moutain, 'country'),
   from: table(Mountain),
   groupBy: field(Mountain, 'country'),
   havingBy: greaterThan(count(*), 1),
});
// sql: SELECT "Mountain"."country"
//      FROM "Mountain"
//      GROUP BY "Mountain"."country"
//      HAVING BY count(*) > 1
//
// const countries: string[]

ORDER BY

const names = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   orderBy: field(Mountain, 'name')
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      ORDER BY "Mountain"."name"
//
// const names: string[]
const names = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   orderBy: desc(field(Mountain, 'height'))
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      ORDER BY "Mountain"."height" DESC
//
// const names: string[]
const names = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   orderBy: [
     desc(field(Mountain, 'height')),
     asc(field(Mountain, 'name')),
   ]
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      ORDER BY "Mountain"."height" DESC, "Mountain"."name" ASC
//
// const names: string[]

LIMIT

const names = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   limit: 5,
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      LIMIT 5
//
// const names: string[]

OFFSET

const names = await client.select({
   select: field(Moutain, 'name'),
   from: table(Mountain),
   offset: 5,
});
// sql: SELECT "Mountain"."name"
//      FROM "Mountain"
//      OFFSET 5
//
// const names: string[]
PreviousrelationalNextINSERT

Last updated