drizzle-team/drizzle-orm · error · Error

Insert select error: selected fields are not the same or are

Error message

Insert select error: selected fields are not the same or are in a different order compared to the table definition

What it means

Thrown by SQLiteInsertBuilder.select() (insert.ts:100) when the SELECT used for INSERT ... SELECT does not expose exactly the same field keys in the same order as the target table's columns. Drizzle compares keys via haveSameKeys (order-sensitive), so a different set of columns, a different order, or extra/missing aliases all fail.

Source

Thrown at drizzle-orm/src/sqlite-core/query-builders/insert.ts:100

	select(
		selectQuery: (qb: QueryBuilder) => SQLiteInsertSelectQueryBuilder<TTable>,
	): SQLiteInsertBase<TTable, TResultType, TRunResult>;
	select(selectQuery: (qb: QueryBuilder) => SQL): SQLiteInsertBase<TTable, TResultType, TRunResult>;
	select(selectQuery: SQL): SQLiteInsertBase<TTable, TResultType, TRunResult>;
	select(selectQuery: SQLiteInsertSelectQueryBuilder<TTable>): SQLiteInsertBase<TTable, TResultType, TRunResult>;
	select(
		selectQuery:
			| SQL
			| SQLiteInsertSelectQueryBuilder<TTable>
			| ((qb: QueryBuilder) => SQLiteInsertSelectQueryBuilder<TTable> | SQL),
	): SQLiteInsertBase<TTable, TResultType, TRunResult> {
		const select = typeof selectQuery === 'function' ? selectQuery(new QueryBuilder()) : selectQuery;

		if (
			!is(select, SQL)
			&& !haveSameKeys(this.table[Columns], select._.selectedFields)
		) {
			throw new Error(
				'Insert select error: selected fields are not the same or are in a different order compared to the table definition',
			);
		}

		return new SQLiteInsertBase(this.table, select, this.session, this.dialect, this.withList, true);
	}
}

export type SQLiteInsertWithout<T extends AnySQLiteInsert, TDynamic extends boolean, K extends keyof T & string> =
	TDynamic extends true ? T
		: Omit<
			SQLiteInsertBase<
				T['_']['table'],
				T['_']['resultType'],
				T['_']['runResult'],
				T['_']['returning'],
				TDynamic,
				T['_']['excludedMethods'] | K

View on GitHub (pinned to b7862528fd)

Solutions

Make the SELECT output match the target table's columns exactly, in the same declaration order.
If you need a subset, project missing columns as sql`null` (or a default) so the key set and order align.
Pass a raw SQL expression (sql`...`) instead of a builder to bypass the structural check when order/shape differs intentionally.

Example fix

// before — missing 'createdAt'
db.insert(users).select((qb) => qb.select({
  id: sourceUsers.id,
  name: sourceUsers.name,
}).from(sourceUsers));

// after — match target columns, same order
db.insert(users).select((qb) => qb.select({
  id: sourceUsers.id,
  name: sourceUsers.name,
  createdAt: sql`now()`,
}).from(sourceUsers));

Defensive patterns

Strategy: validation

Validate before calling

function assertInsertSelectShape(table, selectedFields) {
  const tableKeys = Object.keys(table[Symbol.for('drizzle:Columns')]);
  const selectKeys = Object.keys(selectedFields);
  if (tableKeys.length !== selectKeys.length
      || tableKeys.some((k, i) => k !== selectKeys[i])) {
    throw new Error('SELECT shape does not match target table columns/order');
  }
}

Prevention

Mirror the target table's column declaration order in the SELECT projection.
When adding a target column, update every INSERT...SELECT immediately.
Project missing columns as sql`null` rather than omitting them.

When it happens

Trigger: db.insert(t).select(qb => qb.select({...})) where the selected fields do not mirror the table's column list; selecting a subset; selecting in a different key order; using an aliased subquery whose selectedFields differ from the table. The check is skipped only when the select argument is a raw SQL object.

Common situations: Copying a subset of columns from a source table; reordering fields in a refactor; adding a new column to the target table without updating the select; using computed fields not present on the target.

Related errors

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