Skip to main content

SELECT

For WHERE examples, please see the WHERE section.

Simple SELECT​

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

/**
 * @return T[]
 */
await db.select({
  from: 'test',
});

SQL Query

SELECT * FROM `test`

SELECT (columns: string[])​

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

/**
 * @return T[]
 */
await db.select({
  columns: ['foo'],
  from: 'test',
});

SQL Query

SELECT `foo` FROM `test`

SELECT (columns: string)​

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

/**
 * @return T[]
 */
await db.select({
  columns: 'COUNT(*) AS total',
  from: 'test',
});

SQL Query

SELECT COUNT(*) AS total FROM `test`

SELECT (limit: number)​

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

/**
 * @return T[]
 */
await db.select({
  from: 'test',
  limit: 10,
});

SQL Query

SELECT * FROM `test` LIMIT ?
-- params: [10]
  • By using limit: 1, it will return a single row (T | null).

SELECT (offset: number)​

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  from: 'test',
  limit: 10,
  offset: 20,
});

SQL Query

SELECT * FROM `test` LIMIT ? OFFSET ?
-- params: [10, 20]
  • By using limit: 1, it will return a single row.

SELECT (orderBy: [string, 'ASC' | 'DESC'])​

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  from: 'test',
  orderBy: ['id'],
  // orderBy: ['id', 'ASC'],
  // orderBy: ['id', 'DESC'],
});

SQL Query

SELECT * FROM `test` ORDER BY `id` ASC

SELECT (groupBy: string)​

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  columns: 'age, COUNT(*) AS total',
  from: 'users',
  groupBy: 'age',
});

SQL Query

SELECT age, COUNT(*) AS total FROM `users` GROUP BY `age`

SELECT (join: JoinOptions | JoinOptions[])​

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  columns: ['users.name', 'users.age'],
  from: 'preferences',
  join: {
    type: 'left',
    table: 'users',
    on: {
      a: 'preferences.userId',
      b: 'users.id',
    },
  },
});

SQL Query

SELECT `users`.`name`, `users`.`age`
FROM `preferences`
LEFT JOIN `users` ON `preferences`.`userId` = `users`.`id`
  • You can insert multiple JOIN by using them within an array.

Available options​

export type SelectOptions = {
  distinct?: boolean;
  columns?: string | string[];
  from: string;
  join?: JoinOptions | JoinOptions[];
  where?: WhereClause;
  limit?: number;
  offset?: number;
  groupBy?: string;
  orderBy?: [string] | [string, 'ASC' | 'DESC'];
  params?: unknown[];
};

export type JoinOptions = {
  type: 'left' | 'right' | 'inner' | 'cross';
  table: string;
  on: {
    a: string;
    b: string;
  };
  outer?: boolean;
};

Type Assertion​

The select method provides type assertion.

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

interface User {
  id: number;
  name: string;
}

/**
 * @return User[]
 */
const users = await db.select<User>({
  from: 'test',
});

/**
 * @return User | null
 */
const user = await db.select<User>({
  from: 'test',
  limit: 1,
});
note

The return type as T | null or T[] is determined by limit, not the type assertion itself:

  • If no limit is set or it's greater than 1: T[] will be returned.
  • Setting limit: 1: T | null will be returned.