with.ts — drizzle-orm Source File

Architecture documentation for with.ts, a typescript file in the drizzle-orm codebase. 5 imports, 0 dependents.

File typescript DrizzleORM SQLDialects 5 imports 1 functions

Entity Profile

DrizzleORM→ SQLDialects→ with.ts — drizzle-orm Source File

Dependency Diagram

graph LR
  9d72e653_ecf4_1aed_b4d8_15adf68056d6["with.ts"]
  9a56da77_7540_380c_f8b9_d8bde4914940["db.ts"]
  9d72e653_ecf4_1aed_b4d8_15adf68056d6 --> 9a56da77_7540_380c_f8b9_d8bde4914940
  25b05299_f48f_bceb_01ca_1343d330a8f7["utils.ts"]
  9d72e653_ecf4_1aed_b4d8_15adf68056d6 --> 25b05299_f48f_bceb_01ca_1343d330a8f7
  5c46beaf_8b59_d2e3_def3_8af6daf1fccd["index.ts"]
  9d72e653_ecf4_1aed_b4d8_15adf68056d6 --> 5c46beaf_8b59_d2e3_def3_8af6daf1fccd
  be483a7f_d5d7_7a9b_9a13_44a4a6aafbbd["sql.ts"]
  9d72e653_ecf4_1aed_b4d8_15adf68056d6 --> be483a7f_d5d7_7a9b_9a13_44a4a6aafbbd
  c9460fa2_b68e_584b_2be3_a4db38ea6f7c["index.ts"]
  9d72e653_ecf4_1aed_b4d8_15adf68056d6 --> c9460fa2_b68e_584b_2be3_a4db38ea6f7c
  style 9d72e653_ecf4_1aed_b4d8_15adf68056d6 fill:#6366f1,stroke:#818cf8,color:#fff

Relationship Graph

Source Code

import type { Equal } from 'type-tests/utils.ts';
import { Expect } from 'type-tests/utils.ts';
import { gt, inArray, like } from '~/sql/expressions/index.ts';
import { sql } from '~/sql/sql.ts';
import { integer, sqliteTable, text } from '~/sqlite-core/index.ts';
import { db } from './db.ts';

const orders = sqliteTable('orders', {
	id: integer('id').primaryKey(),
	region: text('region').notNull(),
	product: text('product').notNull(),
	amount: integer('amount').notNull(),
	quantity: integer('quantity').notNull(),
	generated: text('generatedText').generatedAlwaysAs(sql``),
});

{
	const regionalSales = db
		.$with('regional_sales')
		.as(
			db
				.select({
					region: orders.region,
					totalSales: sql<number>`sum(${orders.amount})`.as('total_sales'),
				})
				.from(orders)
				.groupBy(orders.region),
		);

	const topRegions = db
		.$with('top_regions')
		.as(
			db
				.select({
					region: orders.region,
					totalSales: orders.amount,
				})
				.from(regionalSales)
				.where(
					gt(
						regionalSales.totalSales,
						db.select({ sales: sql`sum(${regionalSales.totalSales})/10` }).from(regionalSales),
					),
				),
		);

	const result = db
		.with(regionalSales, topRegions)
		.select({
			region: orders.region,
			product: orders.product,
			productUnits: sql<number>`sum(${orders.quantity})`,
			productSales: sql<number>`sum(${orders.amount})`,
		})
		.from(orders)
		.where(inArray(orders.region, db.select({ region: topRegions.region }).from(topRegions)))
		.all();

	Expect<
		Equal<{
			region: string;
			product: string;
			productUnits: number;
			productSales: number;
		}[], typeof result>
	>;

	const allOrdersWith = db.$with('all_orders_with').as(db.select().from(orders));
	const allFromWith = await db.with(allOrdersWith).select().from(allOrdersWith);

	Expect<
		Equal<{
			id: number;
			region: string;
			product: string;
			amount: number;
			quantity: number;
			generated: string | null;
		}[], typeof allFromWith>
	>;

	const regionalSalesWith = db.$with('regional_sales_with').as(db.select().from(regionalSales));
	db.with(regionalSalesWith).select().from(regionalSalesWith).where(like(regionalSalesWith.totalSales, 'abc'));
}

{
	const providers = sqliteTable('providers', {
		id: integer().primaryKey(),
		providerName: text().notNull(),
	});

	const sq1 = db.$with('providers_sq', {
		name: providers.providerName,
	}).as(sql`select provider_name as name from providers`);
	const q1 = await db.with(sq1).select().from(sq1);
	Expect<Equal<typeof q1, { name: string }[]>>;

	const sq2 = db.$with('providers_sq', {
		nested: {
			id: providers.id,
		},
	}).as(() => sql`select id from providers`);
	const q2 = await db.with(sq2).select().from(sq2);
	Expect<Equal<typeof q2, { nested: { id: number } }[]>>;

	// @ts-expect-error
	db.$with('providers_sq', { name: providers.providerName }).as(db.select().from(providers));
	// @ts-expect-error
	db.$with('providers_sq', { name: providers.providerName }).as((qb) => qb.select().from(providers));
}