drizzle-team/drizzle-orm · error · Error

You tried to reference "${prop}" field from a subquery, whic

Error message

You tried to reference "${prop}" field from a subquery, which is a raw SQL field, but it doesn't have an alias declared. Please add an alias to the field using ".as('alias')" method.

What it means

SelectionProxyHandler.get() throws when you reference a property of a subquery/view selection that resolves to a raw SQL expression (instance of SQL, not SQL.Aliased) and the handler's sqlBehavior is 'error'. Raw SQL selections must be aliased via .as('name') so the proxy can expose them by name; without an alias there is no name to reference.

Source

Thrown at drizzle-orm/src/selection-proxy.ts:95

		const value: unknown = columns[prop as keyof typeof columns];

		if (is(value, SQL.Aliased)) {
			// Never return the underlying SQL expression for a field previously selected in a subquery
			if (this.config.sqlAliasedBehavior === 'sql' && !value.isSelectionField) {
				return value.sql;
			}

			const newValue = value.clone();
			newValue.isSelectionField = true;
			return newValue;
		}

		if (is(value, SQL)) {
			if (this.config.sqlBehavior === 'sql') {
				return value;
			}

			throw new Error(
				`You tried to reference "${prop}" field from a subquery, which is a raw SQL field, but it doesn't have an alias declared. Please add an alias to the field using ".as('alias')" method.`,
			);
		}

		if (is(value, Column)) {
			if (this.config.alias) {
				return new Proxy(
					value,
					new ColumnAliasProxyHandler(
						new Proxy(
							value.table,
							new TableAliasProxyHandler(this.config.alias, this.config.replaceOriginalName ?? false),
						),
					),
				);
			}
			return value;
		}

View on GitHub (pinned to b7862528fd)

Solutions

  1. Add .as('alias') to every raw SQL field in the subquery/view selection: { count: sql`COUNT(*)`.as('count') }.
  2. If you intend to reference the raw expression itself, use sql`...` directly in the outer query rather than going through the selection proxy.
  3. Use the column's actual aliased name when accessing the field.

Example fix

// before (throws: raw SQL without alias)
const sq = db.$with('sq').as((qb) =>
  qb.select({ total: sql`COUNT(*)` }).from(orders),
);
const rows = await db.with(sq).select({ t: sq.total }).from(sq);

// after
const sq = db.$with('sq').as((qb) =>
  qb.select({ total: sql`COUNT(*)`.as('total') }).from(orders),
);
Defensive patterns

Strategy: validation

Validate before calling

// Ensure raw SQL fields in subquery/view selections are aliased.
import { SQL } from 'drizzle-orm/sql/sql';
import { is } from 'drizzle-orm/entity';

function ensureRawSqlAliased(selection: Record<string, unknown>) {
  for (const [name, v] of Object.entries(selection)) {
    if (is(v, SQL) && !(v as any).isAliased) {
      throw new Error(`Raw SQL field "${name}" needs .as('${name}')`);
    }
  }
}

Type guard

import { SQL } from 'drizzle-orm/sql/sql';
import { is } from 'drizzle-orm/entity';

function isUnaliasedSql(v: unknown): boolean {
  return is(v, SQL) && !(v as any).isAliased;
}

Try / catch

try {
  const rows = await db.with(sq).select({ t: sq.total }).from(sq);
} catch (e) {
  if (e instanceof Error && /raw SQL field, but it doesn't have an alias/.test(e.message)) {
    // re-define the subquery field with .as('alias')
  } else throw e;
}

Prevention

When it happens

Trigger: Selecting sql`...` (raw expression) inside a subquery or view without .as('alias'), then accessing that field by name through the selection proxy. Using db.$with('name').as(qb => ({ field: sql`...` })) and later referencing the field. Creating a view with raw SQL columns and querying its fields.

Common situations: Building CTEs or subqueries with computed columns (e.g., sql`COUNT(*)`) and forgetting the alias. Migrating raw SQL selects into Drizzle's query builder.

Related errors

AI-assisted analysis of drizzle-team/drizzle-orm@b7862528fd (2026-08-03). Data as JSON: /data/errors/62ff7aba117976b9.json. Report an issue: GitHub.