--- title: "Type System Verification" description: "Type system verification validates compile-time correctness across Drizzle's table definitions, query builders, and database drivers. It uses assertion utilities such as Expect and Equal to verify ..." last_updated: "2026-10-12T08:14:00.74232+00:00" canonical_url: "https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/testing-and-tooling/type-system-verification" ---
Relevant source files The following files were used as context for generating this wiki page: - [drizzle-orm/type-tests/geldb/tables.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/tables.ts) - [drizzle-orm/type-tests/mysql/tables.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/tables.ts) - [drizzle-orm/type-tests/geldb/no-strict-null-checks/test.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/no-strict-null-checks/test.ts) - [drizzle-orm/type-tests/geldb/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/select.ts) - [drizzle-orm/type-tests/singlestore/tables.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/tables.ts) - [drizzle-orm/type-tests/sqlite/tables.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/tables.ts) - [drizzle-orm/type-tests/pg/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/select.ts) - [drizzle-orm/type-tests/mysql/db-rel.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/db-rel.ts) - [drizzle-orm/type-tests/singlestore/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/select.ts) - [drizzle-orm/type-tests/pg/no-strict-null-checks/test.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/no-strict-null-checks/test.ts) - [drizzle-orm/type-tests/sqlite/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/select.ts) - [drizzle-orm/type-tests/singlestore/no-strict-null-checks/test.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/no-strict-null-checks/test.ts) - [drizzle-orm/type-tests/mysql/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/select.ts) - [drizzle-orm/type-tests/mysql/no-strict-null-checks/test.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/no-strict-null-checks/test.ts) - [drizzle-orm/type-tests/mysql/1000columns.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/1000columns.ts) - [drizzle-orm/type-tests/sqlite/no-strict-null-checks/test.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/no-strict-null-checks/test.ts) - [drizzle-orm/type-tests/pg/db-rel.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/db-rel.ts) - [drizzle-orm/type-tests/mysql/subquery.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/subquery.ts) - [integration-tests/type-tests/join-nodenext/singlestore.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/integration-tests/type-tests/join-nodenext/singlestore.ts) - [drizzle-orm/type-tests/common/aliased-table.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/common/aliased-table.ts) - [drizzle-orm/type-tests/pg/subquery.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/subquery.ts) - [drizzle-orm/type-tests/geldb/db-rel.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/db-rel.ts) - [integration-tests/type-tests/join-nodenext/gel.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/integration-tests/type-tests/join-nodenext/gel.ts) - [drizzle-orm/type-tests/mysql/db.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/db.ts) - [drizzle-orm/type-tests/singlestore/set-operators.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/set-operators.ts) - [drizzle-orm/type-tests/singlestore/1000columns.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/1000columns.ts) - [drizzle-orm/type-tests/geldb/subquery.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/subquery.ts) - [drizzle-orm/type-tests/knex/index.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/knex/index.ts) - [drizzle-orm/type-tests/geldb/db.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/db.ts) - [drizzle-orm/type-tests/pg/db.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/db.ts)
## Overview Type system verification validates compile-time correctness across Drizzle's table definitions, query builders, and database drivers. It uses assertion utilities such as `Expect` and `Equal` to verify that inferred types match expected shapes for PostgreSQL, MySQL, SQLite, SingleStore, and Gel databases. Sources: [drizzle-orm/type-tests/mysql/db-rel.ts:2-2](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/db-rel.ts#L2-L2), [drizzle-orm/type-tests/pg/db-rel.ts:2-2](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/db-rel.ts#L2-L2), [drizzle-orm/type-tests/geldb/db-rel.ts:2-2](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/db-rel.ts#L2-L2), [drizzle-orm/type-tests/singlestore/set-operators.ts:1-2](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/set-operators.ts#L1-L2), [drizzle-orm/type-tests/common/aliased-table.ts:1-1](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/common/aliased-table.ts#L1-L1) ## Type Testing Assertions and Harness ### Test Harness Mechanics and Assertion Utilities Type tests across dialects utilize the `Expect` and `Equal` utility types imported from `type-tests/utils.ts` to enforce precise type checking at compile time. These assertions validate query execution results, relational queries, and schema projections without emitting runtime code. ```typescript import { type Equal, Expect } from 'type-tests/utils.ts'; ``` Sources: [drizzle-orm/type-tests/mysql/db-rel.ts:2-2](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/db-rel.ts#L2-L2), [drizzle-orm/type-tests/pg/db-rel.ts:2-2](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/db-rel.ts#L2-L2), [drizzle-orm/type-tests/geldb/db-rel.ts:2-2](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/db-rel.ts#L2-L2), [drizzle-orm/type-tests/common/aliased-table.ts:1-1](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/common/aliased-table.ts#L1-L1) Query results and builder inferences are wrapped inside block scopes to isolate variable declarations across different test cases. Each test block executes a database query or schema operation using dialect-specific drivers and schema modules. ```typescript { const res = db('test').select(); Expect, typeof test.$inferSelect[]>>; } ``` Sources: [drizzle-orm/type-tests/knex/index.ts:20-23](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/knex/index.ts#L20-L23) > [!NOTE] > Type test files do not execute queries against a live database during standard type-checking passes; instead, drivers are frequently instantiated using `.mock()` or connection pool stubs to verify AST builder output and inference boundaries. Sources: [drizzle-orm/type-tests/common/aliased-table.ts:16-18](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/common/aliased-table.ts#L16-L18) ### Cross-Dialect Test Structure The type-testing harness organizes suites by database dialect and common utility modules. Each directory contains dedicated schema definitions and query verification scripts that test relational finders, table aliases, view integration, and third-party query builder typings like Knex. | Dialect / Module | Source File Path | Primary Verification Target | | :--- | :--- | :--- | | PostgreSQL Relational | `drizzle-orm/type-tests/pg/db-rel.ts` | Relational query builder, `findMany`, `with`, `columns`, and `extras` inference | | MySQL Relational | `drizzle-orm/type-tests/mysql/db-rel.ts` | Cross-dialect driver integration, `placeholder` typing, and nested relations | | Gel Database | `drizzle-orm/type-tests/geldb/db-rel.ts` | Gel client driver initialization and relational schema projections | | Common Aliases | `drizzle-orm/type-tests/common/aliased-table.ts` | Table aliases, view joins, and multi-dialect select structure validation | | Knex Integration | `drizzle-orm/type-tests/knex/index.ts` | `Knexify` table declaration augmentation and `PromiseOf` resolution | Sources: [drizzle-orm/type-tests/mysql/db-rel.ts:1-104](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/db-rel.ts#L1-L104), [drizzle-orm/type-tests/pg/db-rel.ts:1-121](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/db-rel.ts#L1-L121), [drizzle-orm/type-tests/common/aliased-table.ts:1-232](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/common/aliased-table.ts#L1-L232), [drizzle-orm/type-tests/geldb/db-rel.ts:1-118](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/db-rel.ts#L1-L118), [drizzle-orm/type-tests/knex/index.ts:1-23](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/knex/index.ts#L1-L23) ## Column and Table Schema Verification ### Builder Column Configurations and Defaults Compile-time verification ensures that table column builders properly enforce type safety across scalar types, default values, and custom type definitions. In MySQL, SQLite, Gel, and SingleStore type tests, column defaults accept static values or callbacks via `$default` and `$defaultFn`, rejecting mismatched value types at compile time. ```typescript { mysqlTable('test', { id: int('id').$default(() => 1), id2: int('id').$defaultFn(() => 1), // @ts-expect-error - should be number id3: int('id').$default(() => '1'), // @ts-expect-error - should be number id4: int('id').$defaultFn(() => '1'), }); } ``` Sources: [drizzle-orm/type-tests/mysql/tables.ts:746-754](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/tables.ts#L746-L754), [drizzle-orm/type-tests/sqlite/tables.ts:425-433](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/tables.ts#L425-L433), [drizzle-orm/type-tests/geldb/tables.ts:853-861](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/tables.ts#L853-L861) > [!CAUTION] > Using `$default` with a factory function that returns a type conflicting with the underlying column data type bypasses runtime type guards and triggers `@ts-expect-error` compiler errors during schema declaration tests. Sources: [drizzle-orm/type-tests/mysql/tables.ts:749-752](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/tables.ts#L749-L752), [drizzle-orm/type-tests/geldb/tables.ts:856-859](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/tables.ts#L856-L859) Custom column configurations validate required and optional parameters through generic constraints on `customType`. When `configRequired: true` is specified, the builder requires configuration arguments to be supplied. ```typescript { const customRequiredConfig = customType<{ data: string; driverData: string; config: { length: number }; configRequired: true; }>({ dataType(config) { Expect>; return `varchar(${config.length})`; }, toDriver(value) { Expect>(); return value; }, fromDriver(value) { Expect>(); return value; }, }); customRequiredConfig('t', { length: 10 }); customRequiredConfig({ length: 10 }); // @ts-expect-error - config is required customRequiredConfig('t'); // @ts-expect-error - config is required customRequiredConfig(); } ``` Sources: [drizzle-orm/type-tests/mysql/tables.ts:803-831](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/tables.ts#L803-L831), [drizzle-orm/type-tests/singlestore/tables.ts:792-837](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/tables.ts#L792-L837), [drizzle-orm/type-tests/sqlite/tables.ts:476-506](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/tables.ts#L476-L506) ### Enum Array and Scalar Type Inference Column definitions containing enumerated values verify tuple inference for both standard and `as const` assertions. In SQLite and SingleStore table tests, enum types correctly surface their allowed string literals on `.enumValues`. ```typescript { const test = sqliteTable('test', { test1: text('test', { length: 255, enum: ['a', 'b', 'c'] as const }).notNull(), test2: text('test', { length: 255, enum: ['a', 'b', 'c'] }).notNull(), test3: text('test', { enum: ['a', 'b', 'c'] as const }).notNull(), test4: text('test', { enum: ['a', 'b', 'c'] }).notNull(), }); Expect>; Expect>; Expect>; Expect>; } ``` Sources: [drizzle-orm/type-tests/sqlite/tables.ts:450-460](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/tables.ts#L450-L460), [drizzle-orm/type-tests/singlestore/tables.ts:633-633](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/tables.ts#L633-L633) > [!TIP] > When defining columns without explicitly providing column names as the first argument, Drizzle infers the table property key as the database column name, verified via `typeof table['id']['_']['name']`. Sources: [drizzle-orm/type-tests/geldb/tables.ts:901-908](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/tables.ts#L901-L908), [drizzle-orm/type-tests/singlestore/tables.ts:930-937](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/tables.ts#L930-L937) ## Query Result Inference and Nullability ### Projection Shapes and Operator Mappings Type inference validation for `SELECT` projection shapes ensures that raw SQL expressions, column selections, and operator results resolve to exact TypeScript types across all database dialects. When executing projections with `db.select()`, expressions mapped via template literals or custom mapping functions propagate their types directly to the resulting tuple array. For instance, `sql` templates combined with `.mapWith()` map raw driver values into structured objects or specific scalar types such as numbers, booleans, or custom record shapes. ```typescript const allOperators = await db .select({ col2: sql`5 - ${users.id} + 1`, // unknown col3: sql`${users.id} + 1`, // number col33: sql`${users.id} + 1`.mapWith(users.id), // number col34: sql`${users.id} + 1`.mapWith(mapFunkyFuncResult), // number col4: sql`one_or_another(${users.id}, ${users.class})`, // string | number col5: sql`true`, // unknown col6: sql`true`, // boolean col7: sql`random()`, // number col8: sql`some_funky_func(${users.id})`.mapWith(mapFunkyFuncResult), // { foo: string } col9: sql`greatest(${users.createdAt}, ${sql.param(new Date(), users.createdAt)})`, // unknown col10: sql`date_or_false(${users.createdAt}, ${ sql.param( new Date(), users.createdAt, ) })`, // Date | boolean col11: sql`${users.age1} + ${age}`, // unknown col12: sql`${users.age1} + ${sql.param(age, users.age1)}`, // unknown col13: sql`lower(${users.class})`, // unknown col14: sql`length(${users.class})`, // number count: sql`count(*)::int`, // number }) .from(users) ``` Sources: [drizzle-orm/type-tests/geldb/select.ts:412-436](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/select.ts#L412-L436), [drizzle-orm/type-tests/pg/select.ts:411-435](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/select.ts#L411-L435), [drizzle-orm/type-tests/singlestore/select.ts:354-373](https://github.com/blade-47/drizzle-orm/blob/HEAD/drizzle-orm/type-tests/singlestore/select.ts#L354-L373) > [!NOTE] > Unannotated `sql`` template expressions without explicit type generics or `.mapWith()` calls resolve to `unknown` rather than defaulting to `any` or primitive types. Sources: [drizzle-orm/type-tests/geldb/select.ts:414-414](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/select.ts#L414-L414), [drizzle-orm/type-tests/pg/select.ts:413-413](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/select.ts#L413-L413), [drizzle-orm/type-tests/sqlite/select.ts:367-367](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/select.ts#L367-L367) ### Conditional SPREAD Properties and Optional Fields Conditional object spreading inside select projections correctly introduces optional property modifiers into the resulting row type. When an object property is conditionally included using a boolean flag, the resulting type infers an optional field modifier rather than an explicit `undefined` union type. ```typescript { const authenticated = false as boolean; const result = await db .select({ id: users.id, ...(authenticated ? { city: users.homeCity } : {}), }) .from(users); Expect< Equal< { id: number; city?: number; }[], typeof result > >; } ``` Sources: [drizzle-orm/type-tests/mysql/select.ts:526-545](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/select.ts#L526-L545) > [!WARNING] > Choking query modifiers such as `.where()` or `.for()` enforce strict AST builder constraints: invoking `.where()` multiple times or chaining configuration options like `.for('share', { noWait: true, skipLocked: true })` triggers compilation errors via `@ts-expect-error`. Sources: [drizzle-orm/type-tests/mysql/select.ts:547-608](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/select.ts#L547-L608) ## Relational Joins and Aliased Scopes ### Subquery Type Inference and Constraint Validation Type definitions for derived tables and subqueries enforce strict structural verification across dialect implementations, ensuring that joined scopes, set operators, and inline selections preserve correct nullability and projection shapes. When queries are aliased via `.as('aliasName')`, their exported shape maps precisely to the columns selected within the builder block. For example, subqueries built with explicit grouping or aggregation correctly propagate column types and handle nullable projections resulting from outer or left join operations. ```typescript const n1 = db .select({ id: users.id, name: users.name, authorId: users.managerId, count1: sql`count(1)::int`.as('count1'), }) .from(users) .groupBy(users.id, users.name, users.managerId) .as('n1'); ``` Sources: [drizzle-orm/type-tests/mysql/subquery.ts:21-30](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/subquery.ts#L21-L30), [drizzle-orm/type-tests/pg/subquery.ts:21-30](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/subquery.ts#L21-L30) > [!WARNING] > Attempting to project multiple columns inside a scalar subquery passed to `.select()` triggers a compile-time `DrizzleTypeError` containing the message `'You can only select one column in the subquery'`, preventing invalid multi-column scalar expansions. Sources: [drizzle-orm/type-tests/mysql/subquery.ts:118-124](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/subquery.ts#L118-L124), [drizzle-orm/type-tests/pg/subquery.ts:118-124](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/subquery.ts#L118-L124) ### Aliased Table Scopes and NodeNext Join Resolution Aliasing tables using the dialect-specific `alias()` helper enables self-joins and complex graph relationships while maintaining distinct object keys in result tuples. Under NodeNext module resolution and mock configurations across SingleStore, Gel, PostgreSQL, MySQL, and SQLite, table joins against aliased references correctly namespace the resulting row objects. | Dialect Module | Alias Helper Import Path | View Constructor | Mock Driver Method | | :--- | :--- | :--- | :--- | | **PostgreSQL** | `~/pg-core/alias.ts` | `pgView('name')` | `pgd.mock()` | | **MySQL / SingleStore** | `~/mysql-core/alias.ts` | `mysqlView('name')` | `mysqld.mock()` | | **SQLite** | `~/sqlite-core/alias.ts` | `sqliteView('name')` | `sqlited.mock()` | | **Gel / GelDB** | `~/gel-core/index.ts` | — | `drizzle.mock()` | Sources: [drizzle-orm/type-tests/common/aliased-table.ts:1-31](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/common/aliased-table.ts#L1-L31), [integration-tests/type-tests/join-nodenext/gel.ts:1-21](https://github.com/blade47/type-tests/join-nodenext/gel.ts#L1-L21), [integration-tests/type-tests/join-nodenext/singlestore.ts:1-20](https://github.com/blade47/type-tests/join-nodenext/singlestore.ts#L1-L20) When performing an `innerJoin` or `leftJoin` between a primary table and an aliased table or view, the inferred result structure indexes both the base table and the alias or view identifier as separate properties, with left-joined secondary relations correctly typed as nullable unions (`T | null`). ```typescript const pgRes = await pg.select().from(pgUsers).leftJoin(pgAlias, eq(pgAlias.id, pgUsers.id)); ``` Sources: [drizzle-orm/type-tests/common/aliased-table.ts:32-32](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/common/aliased-table.ts#L32-L32) > [!NOTE] > View definitions created via `.as((qb) => qb.select().from(...))` inherit the complete column schema of their underlying source tables, allowing view aliases to participate in joins identically to base table aliases. Sources: [drizzle-orm/type-tests/common/aliased-table.ts:20-22](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/common/aliased-table.ts#L20-L22), [drizzle-orm/type-tests/common/aliased-table.ts:36-38](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/common/aliased-table.ts#L36-L38) ## Strict Null Check Compiler Compatibility ### Column Builder Schema under Relaxed Null Settings To verify type correctness when TypeScript's `strictNullChecks` flag is disabled, the test suite defines standard table structures across PostgreSQL, MySQL, SQLite, SingleStore, and GelDB. Each table specification includes primary keys, generated columns, default functions, not-null constraints, nullable descriptions, and database default values. | Column Name | Data Type | Constraint / Modifier | | :--- | :--- | :--- | | `id` | `text` | Primary Key, Generated Always (`.generatedAlwaysAs('genstr')`) | | `intId` | `integer` | Primary Key, Generated Identity (`.generatedAlwaysAsIdentity()`) | | `int2Id` | `integer` | Generated Default Identity (`.generatedByDefaultAsIdentity()`) | | `name` | `text` | Default Function (`.$defaultFn(() => '' as string)`) | | `title` | `text` | Not Null (`.notNull()`) | | `description` | `text` | Nullable (implicit) | | `dbdef` | `text` | Database Default (`.default('dbdefval')`) | Sources: [drizzle-orm/type-tests/geldb/no-strict-null-checks/test.ts:4-19](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/no-strict-null-checks/test.ts#L4-L19), [drizzle-orm/type-tests/pg/no-strict-null-checks/test.ts:4-19](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/no-strict-null-checks/test.ts#L4-L19), [drizzle-orm/type-tests/mysql/no-strict-null-checks/test.ts:4-15](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/no-strict-null-checks/test.ts#L4-L15), [drizzle-orm/type-tests/singlestore/no-strict-null-checks/test.ts:4-15](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/no-strict-null-checks/test.ts#L4-L15), [drizzle-orm/type-tests/sqlite/no-strict-null-checks/test.ts:4-15](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/no-strict-null-checks/test.ts#L4-L15) > [!WARNING] > Attempting to pass explicit values to generated columns such as `id` or `intId` during update and insert operations correctly triggers compiler errors guarded by `// @ts-expect-error`, verifying that database-generated fields remain protected even when strict null checks are turned off. Sources: [drizzle-orm/type-tests/geldb/no-strict-null-checks/test.ts:23-28](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/no-strict-null-checks/test.ts#L23-L28), [drizzle-orm/type-tests/pg/no-strict-null-checks/test.ts:33-41](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/no-strict-null-checks/test.ts#L33-L41), [drizzle-orm/type-tests/mysql/no-strict-null-checks/test.ts:37-44](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/no-strict-null-checks/test.ts#L37-L44), [drizzle-orm/type-tests/sqlite/no-strict-null-checks/test.ts:37-44](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/no-strict-null-checks/test.ts#L37-L44) ### Mutation Validation and Partial Inserts Mock database instances created via `drizzle.mock()` evaluate insert and update payloads against the column definitions. Omitting optional fields, nullable attributes, and default-backed properties is permitted across all supported dialects. ```typescript db.insert(test).values({ title: 'title', }); ``` Sources: [drizzle-orm/type-tests/geldb/no-strict-null-checks/test.ts:104-105](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/no-strict-null-checks/test.ts#L104-L105), [drizzle-orm/type-tests/pg/no-strict-null-checks/test.ts:104-105](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/no-strict-null-checks/test.ts#L104-L105), [drizzle-orm/type-tests/mysql/no-strict-null-checks/test.ts:64-65](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/no-strict-null-checks/test.ts#L64-L65), [drizzle-orm/type-tests/singlestore/no-strict-null-checks/test.ts:66-67](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/no-strict-null-checks/test.ts#L66-L67), [drizzle-orm/type-tests/sqlite/no-strict-null-checks/test.ts:64-65](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/no-strict-null-checks/test.ts#L64-L65) ## Compiler Stress and Set Operators ### Large-Scale Schema Inference Type checking scalability under extreme column counts is verified using generated schemas containing up to 1,000 columns per table. Both MySQL and SingleStore definitions declare extensive numeric and identifier fields with primary keys, autoincrement flags, and default values to validate compiler overhead and memory consumption during type instantiation. ```typescript mysqlTable('test', { col0: double('col1').primaryKey().autoincrement().default(0), col1: double('col1').primaryKey().autoincrement().default(0), col2: double('col1').primaryKey().autoincrement().default(0), col3: double('col1').primaryKey().autoincrement().default(0), col4: double('col1').primaryKey().autoincrement().default(0), col5: double('col1').primaryKey().autoincrement().default(0), col6: double('col1').primaryKey().autoincrement().default(0), col8: double('col1').primaryKey().autoincrement().default(0), col9: double('col1').primaryKey().autoincrement().default(0), col10: double('col1').primaryKey().autoincrement().default(0), col11: double('col1').primaryKey().autoincrement().default(0), col12: double('col1').primaryKey().autoincrement().default(0), col13: double('col1').primaryKey().autoincrement().default(0), col14: double('col1').primaryKey().autoincrement().default(0), col15: double('col1').primaryKey().autoincrement().default(0), col16: double('col1').primaryKey().autoincrement().default(0), col18: double('col1').primaryKey().autoincrement().default(0), col19: double('col1').primaryKey().autoincrement().default(0), col20: double('col1').primaryKey().autoincrement().default(0), col21: double('col1').primaryKey().autoincrement().default(0), ``` Sources: [drizzle-orm/type-tests/mysql/1000columns.ts:3-23](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/1000columns.ts#L3-L23) ```typescript singlestoreTable('test', { col0: double('col1').primaryKey().autoincrement().default(0), col1: double('col1').primaryKey().autoincrement().default(0), col2: double('col1').primaryKey().autoincrement().default(0), col3: double('col1').primaryKey().autoincrement().default(0), col4: double('col1').primaryKey().autoincrement().default(0), col5: double('col1').primaryKey().autoincrement().default(0), col6: double('col1').primaryKey().autoincrement().default(0), col8: double('col1').primaryKey().autoincrement().default(0), col9: double('col1').primaryKey().autoincrement().default(0), col10: double('col1').primaryKey().autoincrement().default(0), col11: double('col1').primaryKey().autoincrement().default(0), col12: double('col1').primaryKey().autoincrement().default(0), col13: double('col1').primaryKey().autoincrement().default(0), col14: double('col1').primaryKey().autoincrement().default(0), col15: double('col1').primaryKey().autoincrement().default(0), col16: double('col1').primaryKey().autoincrement().default(0), col18: double('col1').primaryKey().autoincrement().default(0), col19: double('col1').primaryKey().autoincrement().default(0), col20: double('col1').primaryKey().autoincrement().default(0), col21: double('col1').primaryKey().autoincrement().default(0), ``` Sources: [drizzle-orm/type-tests/singlestore/1000columns.ts:3-23](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/1000columns.ts#L3-L23) > [!NOTE] > These stress files do not execute runtime queries; their sole purpose is to push the TypeScript type engine to its limits when evaluating massive object types and union structures generated by dialect builders. Sources: [drizzle-orm/type-tests/mysql/1000columns.ts:1-23](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/mysql/1000columns.ts#L1-L23), [drizzle-orm/type-tests/singlestore/1000columns.ts:1-23](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/1000columns.ts#L1-L23) ### Set Operator Combinations and Type Safety SingleStore set operators (`union`, `unionAll`, `intersect`, `except`, and `minus`) enforce strict compile-time validation of column counts and compatible data types across combining statements. Mismatched selections are intercepted by `// @ts-expect-error` assertions. | Set Operator Method / Function | Associated Type Constraint | Error Handling for Column / Type Mismatches | | :--- | :--- | :--- | | `.union()` / `union()` | Equivalent projection count and compatible types | Intercepted via `// @ts-expect-error` | | `.unionAll()` / `unionAll()` | Equivalent projection count and compatible types | Intercepted via `// @ts-expect-error` | | `.intersect()` / `intersect()` | Equivalent projection count and compatible types | Intercepted via `// @ts-expect-error` | | `.except()` / `.minus()` | Equivalent projection count and compatible types | Intercepted via `// @ts-expect-error` | Sources: [drizzle-orm/type-tests/singlestore/set-operators.ts:8-66](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/set-operators.ts#L8-L66), [drizzle-orm/type-tests/singlestore/set-operators.ts:159-234](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/set-operators.ts#L159-L234) > [!WARNING] > Combining statements that alter column order or omit fields between queries will fail type verification; the engine requires identical tuple shapes or explicitly compatible mapped types across every branch of a set operation. Sources: [drizzle-orm/type-tests/singlestore/set-operators.ts:162-181](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/set-operators.ts#L162-L181), [drizzle-orm/type-tests/singlestore/set-operators.ts:201-234](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/singlestore/set-operators.ts#L201-L234) ## Related - [Table and Column Models](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/core-architecture/table-and-column-models) - [Query Builder Foundation](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/core-architecture/query-builder-foundation) - [Integration Test Harness](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/testing-and-tooling/integration-test-harness) ## Sitemap See the full [sitemap](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/llms.txt) for all pages in this wiki.