# blade47/drizzle-orm Wiki
## Overview
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/overview
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/api.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/api.ts)
- [drizzle-kit/src/cli/schema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/schema.ts)
- [drizzle-orm/src/durable-sqlite/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/durable-sqlite/driver.ts)
- [drizzle-kit/src/cli/connections.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/connections.ts)
- [drizzle-kit/src/serializer/studio.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/studio.ts)
- [drizzle-orm/src/gel/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel/driver.ts)
- [drizzle-orm/src/libsql/http/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/http/index.ts)
- [drizzle-orm/src/libsql/node/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/node/index.ts)
- [drizzle-orm/src/libsql/web/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/web/index.ts)
- [drizzle-orm/src/libsql/ws/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/ws/index.ts)
- [drizzle-kit/src/cli/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/index.ts)
- [drizzle-orm/src/d1/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/d1/index.ts)
- [drizzle-orm/src/singlestore-core/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/index.ts)
- [drizzle-orm/src/gel/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel/index.ts)
- [drizzle-orm/src/neon/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon/index.ts)
- [drizzle-orm/package.json](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/package.json)
- [drizzle-orm/src/prisma/schema.prisma](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/prisma/schema.prisma)
- [drizzle-orm/src/gel-core/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/index.ts)
- [drizzle-orm/src/singlestore/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/index.ts)
- [drizzle-orm/type-tests/geldb/db.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/geldb/db.ts)
- [drizzle-orm/src/singlestore-proxy/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-proxy/index.ts)
- [drizzle-orm/src/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/index.ts)
- [drizzle-orm/src/durable-sqlite/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/durable-sqlite/index.ts)
- [drizzle-orm/src/mysql2/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql2/index.ts)
- [drizzle-orm/src/pglite/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pglite/index.ts)
- [drizzle-orm/src/netlify-db/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/netlify-db/index.ts)
- [drizzle-orm/src/mysql-proxy/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-proxy/index.ts)
- [drizzle-orm/src/neon-serverless/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-serverless/index.ts)
- [drizzle-orm/src/neon-http/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-http/index.ts)
- [drizzle-orm/src/aws-data-api/pg/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/aws-data-api/pg/index.ts)
The "Overview" component serves as the structural and operational nexus for the Drizzle ecosystem, bridging the gap between high-level developer schemas and low-level database drivers. Its primary purpose is to provide a unified interface for database schema management, migration orchestration, and development-time tooling like "Drizzle Studio". By abstracting dialect-specific implementations—whether for PostgreSQL, MySQL, SQLite, or specialized drivers like Gel or LibSQL—it allows developers to maintain a consistent schema definition codebase while interacting with diverse backend infrastructures.
At its core, this component solves the problem of fragmentation in database tooling. Instead of requiring disparate workflows for different databases, the overview and its associated kit commands provide a standardized path for introspection, migration generation, and runtime connectivity. It handles the serialization of TypeScript schema definitions into intermediate snapshots, which the migration engine then uses to calculate the delta between states, ensuring consistency across environments.
Design-wise, the architecture is centered on a flexible, registration-based system. Drivers, table definitions, and relationships are dynamically registered, allowing the kit to perform schema transformations without hardcoding every possible database configuration. This extensibility is enabled through a well-defined set of helper functions and proxy implementations that intercept database requests, providing a consistent API for the Studio interface while respecting the specific quirks of each driver, from transaction management to specific date parsing logic.
## The Schema Serialization Mechanism
The serialization system converts TypeScript-based Drizzle schemas into serializable JSON snapshots. This process is essential for state-based migrations, as it captures the current state of the database structure to be compared against the target schema defined in code.
The mechanism follows a standard flow: first, it parses the TypeScript files (often via dynamic `require` or filesystem reading in CLI contexts); then, it iterates through the exports to identify table instances, schema configurations, and relationships using type-checking utilities like `is(t, PgTable)`. The extracted data is then passed to dialect-specific snapshot generators (such as `generatePgSnapshot` or `generateSqliteSnapshot`), which normalize the structure into a uniform format that includes the versioning and hash identifiers needed to track migrations safely.
```mermaid
flowchart TD
A["TypeScript Schema Exports"] --> B["prepareFromExports()"]
B --> C["generatePgSnapshot()"]
C --> D["Final JSON Snapshot (Versioned)"]
style D fill:#f9f,stroke:#333
```
Sources: [drizzle-kit/src/api.ts:64-70](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/api.ts#L64-L70), [drizzle-kit/src/api.ts:74-91](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/api.ts#L74-L91), [drizzle-kit/src/serializer/studio.ts:91-125](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/studio.ts#L91-L125)
## Connection and Driver Proxying
To support studio operations and migrations, the subsystem creates a unified structure that abstracts the underlying database client. This "proxy" layer is crucial for standardizing how the tooling communicates with diverse drivers.
The mechanism uses a dispatcher pattern where connection credentials are parsed and converted into objects that provide standardized access to `query` and `proxy` capabilities. These objects implement methods that map dialect-specific return values (e.g., node-postgres rows, MySQL2 result arrays) into a consistent shape. For databases that require special handling of binary types or dates, the connection logic injects custom parsers to ensure that the data retrieved during introspection matches the Drizzle schema's expected types.
> [!IMPORTANT]
> The `transactionProxy` implementation is specifically designed to handle the discrepancy between drivers with native transaction support (e.g., `pg`, `mysql2`) and those that rely on batching (e.g., `D1`, `libsql`). When a transaction is requested, the system attempts to wrap the operations; for non-transactional drivers, it falls back to sequential batch execution.
Sources: [drizzle-kit/src/cli/connections.ts:28-182](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/connections.ts#L28-L182), [drizzle-kit/src/cli/connections.ts:772-811](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/connections.ts#L772-L811)
## Migration Engine Flow
The migration generation process is a pure-function delta calculation. Given two snapshots, the system calculates a diff of schema changes to generate the necessary SQL `ALTER` or `CREATE` statements.
1. **Schema Squashing:** Both the previous and current snapshots are "squashed" using functions like `squashPgScheme` to simplify the view of the database structure.
2. **Diff Calculation:** `applyPgSnapshotsDiff` (or dialect equivalents) processes the squashed states.
3. **Resolver Execution:** The process employs a series of resolvers (e.g., `tablesResolver`, `columnsResolver`) to determine precisely which tables, columns, or views require modification.
4. **Statement Generation:** The final result is a list of SQL statements formatted for execution by the target database.
Sources: [drizzle-kit/src/api.ts:94-122](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/api.ts#L94-L122)
## Design Trade-offs Table
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **JSON Snapshots** | Enables stateless diffing and audit trails | Adds complexity to schema parsing/serialization |
| **Driver Proxy Layer** | Uniform API across heterogeneous drivers | Slightly higher abstraction overhead for driver-specific logic |
| **Dynamic `require()`** | Allows schema file auto-discovery | Runtime evaluation requires caution regarding `require` behavior |
Sources: [drizzle-kit/src/api.ts:138-143](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/api.ts#L138-L143), [drizzle-kit/src/serializer/studio.ts:103-122](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/studio.ts#L103-L122)
## Studio Server Lifecycle
The Studio server provides a GUI for database introspection and modification. It uses the `Setup` type to encapsulate the entire environment of the user's project, including the database dialect, schema files, and proxy methods.
The lifecycle consists of:
1. **Prepare Phase:** `prepareServer()` initializes a Hono instance with compression, CORS, and standard error handling.
2. **Registration Phase:** Routes are registered that map incoming POST requests (types `init`, `proxy`, `tproxy`, `defaults`) to the underlying Drizzle methods.
3. **Execution Phase:** When the `proxy` route is hit, it executes queries against the user's database using the credentials registered during the setup phase.
```mermaid
sequenceDiagram
participant User
participant StudioServer
participant DB
User->>StudioServer: POST / (type: proxy, data: sql)
StudioServer->>DB: execute(sql, params)
DB-->>StudioServer: result data
StudioServer-->>User: JSON response
```
Sources: [drizzle-kit/src/serializer/studio.ts:55-81](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/studio.ts#L55-L81), [drizzle-kit/src/serializer/studio.ts:686-702](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/studio.ts#L686-L702)
## Implementation Details: Schema Filters
Schema filtering is a critical path for introspecting and pushing changes. By passing `schemaFilters` or `tablesFilter`, the system restricts the scope of the schema generation and diffing process.
This is implemented using a filter list that gets concatenated with dialect-specific extension filters. For instance, in `pushSchema` for PostgreSQL, the filter set is computed by combining `tablesFilter` with results from `getTablesFilterByExtensions`. The resulting filter list is passed to introspect functions to effectively prune the search space of the database metadata, preventing the system from over-fetching information from schemas that are not managed by the Drizzle project.
Sources: [drizzle-kit/src/api.ts:134-136](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/api.ts#L134-L136), [drizzle-kit/src/api.ts:146-151](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/api.ts#L146-L151)
## Related
- [[Quick Start]]
- [[Project Structure]]
---
## SQL Alias Formatting Process
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/sql-alias-formatting-process
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/gel-core/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/select.ts)
- [drizzle-orm/src/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts)
- [drizzle-orm/src/sql/sql.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts)
The `as()` method in Drizzle ORM is a fundamental utility used to convert a query builder (such as a `select` statement) into a `Subquery` object, allowing the result of that query to be treated as a table alias for use in further joins or sub-select operations.
When a user calls `.as('alias')` on a query builder, the system triggers a sequence that evaluates the current state of the query, compiles it into a structured SQL representation, and ultimately wraps the result in a proxy that manages column aliasing. This flow ensures that complex nested queries are correctly encapsulated and scoped within the final executed SQL.
### 1. `as` Method Invocation
The process starts in `GelSelectQueryBuilderBase.as(alias)`. This method gathers the tables currently used by the query and prepares to wrap the existing configuration. It acts as the orchestrator for turning the builder into a subquery.
Sources: [drizzle-orm/src/gel-core/query-builders/select.ts:987-998](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/select.ts#L987-L998)
### 2. Retrieving Query State via `getSQL`
Inside `as()`, the builder calls `this.getSQL()` to convert its internal `config` object (containing `with`, `joins`, `where`, etc.) into an `SQL` class instance. This represents the raw structure of the statement being aliased.
Sources: [drizzle-orm/src/gel-core/query-builders/select.ts:978-980](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/select.ts#L978-L980)
### 3. Dialect-Specific SQL Construction
`getSQL()` delegates the actual compilation to the `dialect.buildSelectQuery(config)` method. This is where Drizzle translates the JavaScript configuration object into an `SQL` instance by assembling parts like `with`, `select`, `from`, and `joins`.
Sources: [drizzle-orm/src/gel-core/dialect.ts:336-443](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts#L336-L443)
### 4. Building CTE Clauses
As part of the query construction, `buildSelectQuery` invokes `buildWithCTE(withList)`. This method iterates through any Common Table Expressions defined in the query, formatting them as `"alias" as (sql)` chunks to be prefixed to the main query.
Sources: [drizzle-orm/src/gel-core/dialect.ts:115-127](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts#L115-L127)
### 5. Finalizing as an SQL Object
The compilation process relies on the `sql` function, which accepts chunks (strings, identifiers, or other `SQL` instances) to produce the final `SQL` object that can be safely embedded into other queries.
Sources: [drizzle-orm/src/sql/sql.ts:485-495](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L485-L495)
### 6. Component Encapsulation in `StringChunk`
During query generation, various parts of the SQL string are represented as `StringChunk` objects. This class encapsulates static strings, allowing them to be concatenated efficiently during the final serialization to the database dialect's syntax.
Sources: [drizzle-orm/src/sql/sql.ts:89-101](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L89-L101)
### Sequence Diagram
```mermaid
sequenceDiagram
participant B as GelSelectBuilder
participant D as GelDialect
participant S as SQL/SQL.ts
B->>B: as(alias)
B->>B: getSQL()
B->>D: buildSelectQuery()
D->>D: buildWithCTE()
D->>S: sql(...)
S->>S: new StringChunk()
S-->>D: SQL object
D-->>B: SQL object
B-->>B: return Subquery(SQL)
```
Sources: [drizzle-orm/src/gel-core/query-builders/select.ts:987-998](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/select.ts#L987-L998), [drizzle-orm/src/gel-core/dialect.ts:336-443](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts#L336-L443), [drizzle-orm/src/sql/sql.ts:485-495](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L485-L495)
### Flowchart
```mermaid
flowchart TD
A[Start As] --> B[Get Config]
B --> C[Compile SQL]
C --> D{Has CTEs?}
D -- Yes --> E[Build CTEs]
D -- No --> F[Build Query]
E --> F
F --> G[Wrap in StringChunk]
G --> H[End Subquery]
```
Sources: [drizzle-orm/src/gel-core/query-builders/select.ts:987-998](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/select.ts#L987-L998), [drizzle-orm/src/gel-core/dialect.ts:115-127](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts#L115-L127)
> [!NOTE]
> The `as` method returns a `Proxy`. This is critical because it allows the subquery to dynamically resolve column names when the subquery is referenced in an `ON` or `WHERE` clause of an outer query.
> [!IMPORTANT]
> The `StringChunk` class is only responsible for the literal segments of the SQL. Variables and identifiers are handled separately through `Param` and `Name` classes within the `SQL` instance.
### Key Observations
* **Module Boundaries:** This flow begins in the query builder domain, transitions to the dialect (which handles DB-specific syntax), and finishes in the underlying `SQL` engine.
* **Error Handling:** While `as` itself is straightforward, the subsequent query execution checks if referenced columns exist in the scope. If the subquery references an external table not included in the alias, the Dialect will throw a descriptive error during build-time.
* **Performance:** The generation of the `SQL` object is lightweight. Because Drizzle constructs a tree of objects rather than concatenating strings immediately, query construction remains performant even for deeply nested subqueries.
---
## SQL Alias Formatting Process
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/sql-alias-formatting-process-2
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/mysql-core/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/query-builders/select.ts)
- [drizzle-orm/src/mysql-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts)
- [drizzle-orm/src/sql/sql.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts)
The `as` to `StringChunk` flow is a core mechanism in Drizzle ORM that enables the conversion of a Query Builder instance into a reusable subquery. When you define a query and append `.as('alias')` to it, Drizzle prepares the query's SQL structure to be embedded as a derived table or subquery within a larger statement.
This process transforms high-level query builder configurations into an internal SQL representation, ensuring that when the subquery is eventually used in a parent query, it is correctly wrapped and aliased to maintain query integrity.
### 1. `as`
The execution begins at the `as()` method within `MySqlSelectQueryBuilderBase`. This method takes the current query configuration, computes the set of used tables (for dependency tracking), and wraps the entire query in a `Subquery` object. It also applies a `SelectionProxyHandler` to ensure that column references within the subquery maintain the correct alias context.
Sources: [drizzle-orm/src/mysql-core/query-builders/select.ts:1041-1052](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/query-builders/select.ts#L1041-L1052)
### 2. `getSQL`
To build the subquery's internal SQL, the system calls `getSQL()`. This method delegates the heavy lifting to the `MySqlDialect`, which is responsible for translating the query builder's `config` object (containing joins, wheres, etc.) into a valid SQL string structure.
Sources: [drizzle-orm/src/mysql-core/query-builders/select.ts:1032-1034](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/query-builders/select.ts#L1032-L1034)
### 3. `buildSelectQuery`
The dialect's `buildSelectQuery` receives the query configuration. It orchestrates the assembly of various components, including the `SELECT` clause, `JOIN` clauses, and finally, any Common Table Expressions (CTEs) or set operators.
Sources: [drizzle-orm/src/mysql-core/dialect.ts:312-486](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L312-L486)
### 4. `buildWithCTE`
Within the query assembly, the dialect invokes `buildWithCTE` to process any defined Common Table Expressions. This ensures that if the query depends on external definitions, they are prepended correctly to the `WITH` clause of the resulting SQL object.
Sources: [drizzle-orm/src/mysql-core/dialect.ts:112-124](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L112-L124)
### 5. `sql`
The `sql` function is the factory that consumes the chunks produced by the builder. It takes the strings and query fragments (tables, columns, expressions) and assembles them into an `SQL` class instance, which stores an array of chunks representing the final query tree.
Sources: [drizzle-orm/src/sql/sql.ts:485-495](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L485-L495)
### 6. `StringChunk`
Finally, the `StringChunk` class acts as the leaf node in the SQL tree. It stores the raw string segments of the SQL statement. When the final `SQL` object is serialized (or passed to the driver), these chunks are joined to form the executable string.
Sources: [drizzle-orm/src/sql/sql.ts:89-101](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L89-L101)
> [!NOTE]
> The `as()` method is the entry point for creating subqueries. It internally calls `getSQL()` to ensure the generated SQL is current with any modifications made to the builder prior to the alias being assigned.
> [!TIP]
> Always verify that your subquery has an alias using `.as('name')`, as this is required for the database engine to correctly reference the derived table.
```mermaid
sequenceDiagram
participant SB as SelectBuilder
participant D as MySqlDialect
participant S as SQL
participant SC as StringChunk
SB->>SB: as()
SB->>SB: getSQL()
SB->>D: buildSelectQuery()
D->>D: buildWithCTE()
D->>S: sql()
S->>SC: StringChunk()
```
Sources: [drizzle-orm/src/mysql-core/query-builders/select.ts:1041-1052](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/query-builders/select.ts#L1041-L1052), [drizzle-orm/src/mysql-core/dialect.ts:312-486](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L312-L486), [drizzle-orm/src/sql/sql.ts:89-101](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L89-L101)
```mermaid
flowchart TD
A[Start As] --> B[Get SQL]
B --> C[Build Query]
C --> D[Build CTE]
D --> E[Create SQL Object]
E --> F[Create StringChunk]
```
Sources: [drizzle-orm/src/mysql-core/query-builders/select.ts:1032-1052](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/query-builders/select.ts#L1032-L1052), [drizzle-orm/src/mysql-core/dialect.ts:112-486](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L112-L486), [drizzle-orm/src/sql/sql.ts:89-495](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L89-L495)
### Key Observations
* **Cross-Module Boundaries:** This flow transitions from the high-level `mysql-core` query builder, through the dialect-specific query assembly, into the low-level SQL structure representation.
* **Failure Points:** If the query builder has invalid state (e.g., conflicting set operators or missing columns), `buildSelectQuery` in the dialect layer is designed to throw descriptive errors.
* **Performance:** The conversion to `StringChunk` is deferred until necessary (lazy evaluation), which is efficient for building complex, nested queries without immediate string concatenation overhead.
---
## Quick Start
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/quick-start
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/cli/connections.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/connections.ts)
- [drizzle-orm/src/singlestore/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts)
- [drizzle-orm/type-tests/pg/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/select.ts)
- [drizzle-orm/src/xata-http/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/xata-http/driver.ts)
- [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts)
- [drizzle-orm/src/pg-proxy/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-proxy/driver.ts)
- [drizzle-orm/src/singlestore-proxy/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-proxy/driver.ts)
- [drizzle-orm/src/op-sqlite/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/op-sqlite/driver.ts)
- [drizzle-orm/src/sqlite-proxy/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-proxy/driver.ts)
- [drizzle-orm/src/durable-sqlite/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/durable-sqlite/driver.ts)
- [drizzle-orm/src/sql-js/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql-js/driver.ts)
- [drizzle-orm/src/prisma/sqlite/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/prisma/sqlite/driver.ts)
- [drizzle-orm/src/pglite/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pglite/session.ts)
- [drizzle-orm/src/prisma/pg/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/prisma/pg/driver.ts)
- [drizzle-orm/src/neon-http/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-http/driver.ts)
- [drizzle-orm/src/aws-data-api/pg/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/aws-data-api/pg/driver.ts)
- [drizzle-orm/src/pglite/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pglite/driver.ts)
The "Quick Start" subsystem represents the entry-point factory layer for Drizzle ORM. Its primary purpose is to provide a unified, type-safe API for initializing database connections across a diverse ecosystem of SQL dialects and drivers. By abstracting the complexities of driver-specific configuration (such as connection pooling, dialect dialectics, and session management), it allows developers to interact with disparate databases through a consistent, ergonomic interface.
Architecturally, the component acts as a bridge between user configuration and underlying database driver implementation. It dynamically resolves dependencies—checking for required driver packages via `checkPackage`—and constructs the appropriate `Database` instances based on provided credentials or environment-specific configurations. This design ensures that initialization logic is decoupled from query logic, promoting maintainability and enabling support for complex scenarios like serverless environments, proxies, and edge-computing runtimes.
The subsystem adheres to a strict "constructor injection" pattern. Dialect objects and database wrappers are wired together at runtime to produce a `Database` instance that implements the standard Drizzle API surface. This mechanism allows the library to handle both local and remote execution paths, providing optimized paths for batching and transaction handling depending on the capabilities exposed by the target driver.
## Driver Initialization and Lifecycle
The initialization mechanism, typically exposed via a `drizzle()` function, follows a consistent sequence across all supported drivers. It instantiates a dialect, configures logging and caching, extracts table relational schemas (if provided), and finally creates the database instance.
- **Dialect instantiation**: `new PgDialect()`, `new SQLiteAsyncDialect()`, or `new SingleStoreDialect()` are initialized with optional `casing` configuration to ensure consistent column mapping.
- **Session creation**: Driver-specific classes (e.g., `XataHttpDriver` or `PgliteDriver`) act as factories, using `createSession()` to return internal session objects that contain the logic for executing SQL strings and handling result sets.
- **Dependency Verification**: Functions like `checkPackage()` are critical for runtimes that require optional drivers (e.g., `pg`, `mysql2`), throwing explicit errors or terminating the process if a mandatory package is missing from the environment.
Sources: [drizzle-orm/src/xata-http/driver.ts:52-91](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/xata-http/driver.ts#L52-L91), [drizzle-orm/src/neon-http/driver.ts:124-169](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-http/driver.ts#L124-L169), [drizzle-kit/src/cli/connections.ts:19](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/connections.ts#L19)
## Connection Pooling and Management
In environment-agnostic drivers like `mysql2` or `node-postgres`, the subsystem manages connection pooling by creating an instance of `Pool`. The `drizzle` function receives the configuration and instantiates the client, often with a `max: 1` setting for Drizzle Kit specifically to prevent connection leakage.
When using proxy-based drivers (like `sqlite-proxy` or `pg-proxy`), the "pool" is replaced by a `RemoteCallback` that translates database requests into HTTP calls. This is a load-bearing abstraction: the callback captures the SQL and parameters, serializes them, and executes the request over the network.
Sources: [drizzle-kit/src/cli/connections.ts:221-225](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/connections.ts#L221-L225), [drizzle-orm/src/sqlite-proxy/driver.ts:30-40](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-proxy/driver.ts#L30-L40)
## Transactional Control Flow
Transactional support is implemented via the `transaction()` method on the resulting `Database` instances. The underlying session determines if it should utilize a pooled connection or a singleton.
1. **Start Transaction**: A `BEGIN` command is executed via `tx.execute(sql\`begin\`)`.
2. **Execution**: The user-provided callback is executed against the transaction instance.
3. **Commit/Rollback**: The `try...catch` block wraps the transaction execution; if an error occurs, it triggers `ROLLBACK` and throws the error.
> [!NOTE]
> For non-transactional drivers like Cloudflare D1, `transactionProxy` maps the transaction request to a `batch()` call instead, as native `BEGIN/COMMIT` are not supported in that specific environment.
Sources: [drizzle-orm/src/singlestore/session.ts:277-317](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts#L277-L317), [drizzle-kit/src/cli/connections.ts:976-981](https://github.com/blade47/drizzle-kit/src/cli/connections.ts#L976-L981)
## Architecture Overview (Class Diagram)
```mermaid
classDiagram
class Database {
<>
+dialect: Dialect
+session: Session
}
class Dialect {
<>
}
class Driver {
<>
+createSession()
}
Database *-- Dialect
```
Sources: [drizzle-orm/src/pg-core/db.ts:5](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/db.ts#L5), [drizzle-orm/src/xata-http/driver.ts:18-41](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/xata-http/driver.ts#L18-L41)
## Performance Considerations and Caching
Caching is implemented as an optional configuration injected during the `drizzle()` call. The `NoopCache` is the default provider if no cache is provided, ensuring that standard execution incurs zero overhead from caching logic. When a custom cache is provided, the `queryWithCache` method checks existing keys before executing the database client's `query()` method.
> [!TIP]
> Always provide a cache implementation when operating in high-latency network environments (e.g., edge runtimes) to avoid repeated SQL round-trips for idempotent read operations.
Sources: [drizzle-orm/src/singlestore/session.ts:13-14](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts#L13-L14), [drizzle-orm/src/pglite/session.ts:90-92](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pglite/session.ts#L90-L92)
## Execution Walkthrough: Querying
Tracing a query execution flow:
1. `PreparedQuery.execute()`: User calls query builder method.
2. `fillPlaceholders()`: Replaces template placeholders with real values.
3. `logger.logQuery()`: Records execution if logging is enabled.
4. `queryWithCache()`: Checks cache; if miss, triggers the driver's native execution method.
5. `client.query()`: The actual database client communicates with the server.
6. `mapResultRow()`: Transforms raw driver output into Drizzle-formatted objects.
Sources: [drizzle-orm/src/singlestore/session.ts:93-142](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts#L93-L142)
## Runnable Example
This example demonstrates how to initialize a connection using the standard `drizzle()` factory with the `neon-http` driver.
```typescript
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
// 1. Initialize client
const sql = neon(process.env.DATABASE_URL!);
// 2. Initialize DB instance
const db = drizzle(sql, {
logger: true,
casing: 'snake_case'
});
// 3. Perform a query
const result = await db.select().from(usersTable);
```
Sources: [drizzle-orm/src/neon-http/driver.ts:171-226](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-http/driver.ts#L171-L226)
## Related
- [[Overview]]
- [[Database Drivers]]
---
## SQL Alias Formatting Process
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/sql-alias-formatting-process-3
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/singlestore-core/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/select.ts)
- [drizzle-orm/src/singlestore-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts)
- [drizzle-orm/src/sql/sql.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts)
The "As -> StringChunk" flow describes how Drizzle transforms a configured query builder into a named subquery expression within a SQL statement. This process is triggered when a user calls the `.as()` method on a `SingleStoreSelect` instance. The end result is a `Subquery` object wrapped in a `SelectionProxyHandler` that can be seamlessly embedded into larger queries, ensuring SQL syntax safety and structural integrity.
### 1. `as` method invocation
The execution starts in the `SingleStoreSelectQueryBuilderBase` class. The `.as()` method is called with an alias string. It initializes the subquery creation by extracting all tables involved in the original query (the main table and any joined tables) to maintain correct table references in the scope of the new alias.
Sources: [drizzle-orm/src/singlestore-core/query-builders/select.ts:912-917](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/select.ts#L912-L917)
### 2. `getSQL` call for query construction
Within `.as()`, the builder calls its own `getSQL()` method. This method triggers the dialect to build the actual SQL string representation of the current select configuration, including filters, joins, and limits.
Sources: [drizzle-orm/src/singlestore-core/query-builders/select.ts:903-905](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/select.ts#L903-L905)
### 3. Dialect build process
The `SingleStoreDialect.buildSelectQuery` method is invoked. It assembles the complete SQL `SELECT` structure. If the query includes common table expressions (CTEs), it calls `buildWithCTE` to properly format the `WITH` clause before the primary `SELECT` query.
Sources: [drizzle-orm/src/singlestore-core/dialect.ts:299-314](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts#L299-L314)
### 4. CTE formatting
`buildWithCTE` processes the `withList` configuration. It iterates through registered subqueries, creating `SQL` objects for each CTE, and joins them using the `sql` template literal function, ensuring the resulting SQL conforms to SingleStore syntax expectations.
Sources: [drizzle-orm/src/singlestore-core/dialect.ts:111-123](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts#L111-L123)
### 5. `sql` tag and chunk creation
The `sql` function is responsible for parsing template strings and parameters. It segments the query into an array of `SQLChunk` objects. When it encounters raw strings in the template, it instantiates `StringChunk` to represent fixed SQL parts.
Sources: [drizzle-orm/src/sql/sql.ts:485-494](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L485-L494)
### 6. `StringChunk` instantiation
The `StringChunk` class acts as the final wrapper for literal SQL fragments. It stores the SQL string as a list of components, which the engine later joins during the `toQuery` phase to generate the final executable string and parameter binding set.
Sources: [drizzle-orm/src/sql/sql.ts:89-101](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L89-L101)
> [!TIP]
> The `.as()` method is crucial for subquery reuse. By wrapping the result in a `SelectionProxyHandler`, Drizzle allows you to continue writing type-safe code as if the alias were a standard table.
```mermaid
sequenceDiagram
participant S as SelectBuilder
participant D as Dialect
participant Q as SQL Module
S->>S: as(alias)
S->>S: getSQL()
S->>D: buildSelectQuery()
D->>D: buildWithCTE()
D->>Q: sql()
Q->>Q: new StringChunk()
```
Sources: [drizzle-orm/src/singlestore-core/query-builders/select.ts:912-922](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/select.ts#L912-L922), [drizzle-orm/src/singlestore-core/dialect.ts:299-314](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts#L299-L314), [drizzle-orm/src/sql/sql.ts:485-494](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L485-L494)
```mermaid
flowchart TD
A[Call .as] --> B[Get SQL]
B --> C[Dialect Build]
C --> D[CTE Logic]
D --> E[SQL Template]
E --> F[StringChunk]
```
Sources: [drizzle-orm/src/singlestore-core/query-builders/select.ts:912-920](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/select.ts#L912-L920), [drizzle-orm/src/singlestore-core/dialect.ts:299-350](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts#L299-L350), [drizzle-orm/src/sql/sql.ts:485-500](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L485-L500)
### Key Observations
* **Boundary Crossing:** This flow moves from high-level Query Builder logic (`select.ts`) through Dialect-specific transformation (`dialect.ts`) into core SQL AST manipulation (`sql.ts`).
* **Failure Points:** The primary failure point is during alias generation if the same alias is reused or if invalid columns are referenced, which are typically caught during the building or execution phase rather than the instantiation phase.
* **Performance:** By using `StringChunk` and the `sql` template system, Drizzle avoids costly string concatenations for complex queries, deferring query generation until `toQuery()` is invoked.
---
## Project Structure
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/project-structure
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/sqlgenerator.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/sqlgenerator.ts)
- [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts)
## Introduction
The system project structure is defined by two primary concerns: the generation of database migration SQL via `drizzle-kit` and the runtime query construction via `drizzle-orm`. These components act as the translation layer between high-level schema definitions and low-level SQL dialects, ensuring that schema changes are reliably persisted and queries are correctly formed for the target database.
The `drizzle-kit` subsystem, specifically `sqlgenerator.ts`, serves as the bridge between declarative JSON state representation and imperative SQL execution. It handles the serialization of schema modifications into actionable migration statements, abstracting dialect-specific syntax quirks away from the core migration logic. This design allows for a unified interface that supports various RDBMS engines (PostgreSQL, MySQL, SQLite, SingleStore) by dispatching conversion tasks to specialized strategy classes.
In contrast, `drizzle-orm` represents the runtime component responsible for dynamic SQL construction. The `PgDialect` class centralizes the logic for mapping TypeScript schema objects into valid PostgreSQL statements. It solves the problem of runtime query building by maintaining state (such as column casing) and implementing builders that traverse table and relation definitions to generate optimized SQL, including complex operations like CTEs, joins, and relational data fetches.
## The Convertor Strategy Pattern
The `sqlgenerator.ts` uses a strategy-based dispatch mechanism to convert JSON migration operations into raw SQL. Every conversion is encapsulated in a class inheriting from the `Convertor` abstract class, which forces each handler to implement a `can()` check and a `convert()` logic.
- `can(statement: JsonStatement, dialect: Dialect): boolean`: Determines if the specific handler is qualified to process the migration statement for a target dialect.
- `convert(...)`: Transforms the JSON statement into a `string` or `string[]` representing the SQL query.
This decoupling prevents deep conditional nesting in the main logic, allowing for easy expansion as new dialects or SQL features are supported.
Sources: [drizzle-kit/src/sqlgenerator.ts:151-161](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/sqlgenerator.ts#L151-L161)
## Execution Pipeline in `sqlgenerator.ts`
The entry point for generating SQL is the `fromJson` function, which processes an array of statements through the registered convertors. The execution follows a filtering pattern to select the correct handler for each statement.
1. `fromJson` accepts a list of `JsonStatement[]` and the target `Dialect`.
2. It iterates through the statements using `flatMap`.
3. Inside, `convertors.filter` finds the matching strategy handler.
4. If exactly one handler is returned, `convertor.convert` is executed to generate the SQL.
5. Empty strings are filtered out, and the resulting list of SQL commands is returned.
Sources: [drizzle-kit/src/sqlgenerator.ts:4122-4144](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/sqlgenerator.ts#L4122-L4144)
> [!NOTE]
> The `convertor` selection relies on the `filter` result containing exactly one element. If no handler matches (`filtered.length === 0`), the statement is ignored (returns an empty string).
Sources: [drizzle-kit/src/sqlgenerator.ts:4130-4138](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/sqlgenerator.ts#L4130-L4138)
## Runtime Dialect Abstraction
In `drizzle-orm`, the `PgDialect` class manages the state required for generating PostgreSQL-specific queries. It acts as a factory and container for dialect configuration, particularly casing strategies.
- `casing`: A `CasingCache` instance that ensures table and column names conform to specified naming conventions (e.g., snake_case or camelCase) consistently across query generation.
- `escapeName/escapeParam/escapeString`: Low-level utilities that implement strict identifier and value escaping, protecting generated SQL from injection and syntax errors.
Sources: [drizzle-orm/src/pg-core/dialect.ts:63-71](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L63-L71), [114-124](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L114-L124)
## Query Construction Flow
The dialect uses specialized internal builders to construct complex SQL. For a standard SELECT operation, the path is:
1. `buildSelection`: Assembles columns, handling aliasing and casing.
2. `buildFromTable`: Identifies the source table or subquery.
3. `buildJoins`: Iteratively constructs `JOIN` clauses.
4. `buildWithCTE`: Handles common table expressions.
5. Final aggregation into a `SQL` object via the `select` template string.
Sources: [drizzle-orm/src/pg-core/dialect.ts:341-448](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L341-L448)
```mermaid
flowchart TD
A[fromJson] --> B{Filter Convertors}
B -->|Match| C[Convertor.convert]
C --> D[Return SQL Array]
```
Sources: [drizzle-kit/src/sqlgenerator.ts:4122-4144](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/sqlgenerator.ts#L4122-L4144)
## Design Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| Strategy Pattern | High extensibility for new dialects. | Increased number of small classes. |
| Class-based Dialect | State preservation (casing cache). | Dependency injection/instantiation overhead. |
| JsonStatement serialization | Platform-agnostic migration state. | Requires a synchronization step (migration file). |
Sources: [drizzle-kit/src/sqlgenerator.ts:151](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/sqlgenerator.ts#L151), [drizzle-orm/src/pg-core/dialect.ts:63](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L63)
## Error Handling and Edge Cases
The `PgDialect` incorporates safety guards during query construction. For instance, in `buildSelectQuery`, the builder verifies that all referenced columns actually exist within the current table scope.
```typescript
if (is(f.field, Column) && getTableName(f.field.table) !== tableAlias && !isJoined) {
throw new Error(`Your field references a column "${tableName}"."${f.field.name}", but the table is not part of the query!`);
}
```
If this condition is met, the system prevents a malformed SQL query from being generated by throwing a runtime `Error` early in the builder pipeline.
Sources: [drizzle-orm/src/pg-core/dialect.ts:360-383](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L360-L383)
## Concrete Lifecycle Example
When a user updates a sequence in PostgreSQL, the system triggers the `AlterPgSequenceConvertor`. This demonstrates the lifecycle of a migration command:
```typescript
// Example of how the migration system dispatches an alter sequence command
const statement: JsonAlterSequenceStatement = {
type: 'alter_sequence',
name: 'my_seq',
values: { increment: 5 }
};
const convertor = new AlterPgSequenceConvertor();
const sql = convertor.convert(statement);
// returns: "ALTER SEQUENCE "my_seq" INCREMENT BY 5;"
```
This demonstrates how high-level configuration is converted into exact SQL syntax using the metadata provided in the `JsonAlterSequenceStatement` type.
Sources: [drizzle-kit/src/sqlgenerator.ts:1355-1373](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/sqlgenerator.ts#L1355-L1373)
## Related
- [[Overview]]
---
## Schema Declarations
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/schema-declarations
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-orm/type-tests/mysql/tables.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/tables.ts)
- [drizzle-kit/src/introspect-sqlite.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-sqlite.ts)
- [drizzle-kit/src/introspect-mysql.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-mysql.ts)
- [drizzle-kit/src/introspect-gel.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-gel.ts)
- [drizzle-orm/type-tests/pg/tables.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/tables.ts)
Schema declarations in Drizzle define the structure of your database, acting as the single source of truth for the ORM. They bridge the gap between physical database tables and the TypeScript types required for type-safe query building. By utilizing declarative constructs like `pgTable`, `mysqlTable`, and `sqliteTable`, developers specify column names, data types, constraints, and relationships that allow the library to introspect and communicate with the underlying database engine effectively.
This subsystem provides both a runtime representation (used by drivers to construct SQL) and a compile-time type inference engine. The declarations handle mapping database-specific types (e.g., `serial`, `uuid`, `jsonb`) to their TypeScript equivalents, managing default values, and defining complex constraints like primary keys, foreign keys, and indexes.
In the context of `drizzle-kit`, these declarations are further utilized by introspection tools to reverse-engineer existing database schemas into code. The system converts raw schema metadata into high-fidelity TypeScript code, ensuring that the generated output maintains naming conventions, relationship mappings, and structural constraints that the Drizzle core understands.
## Declarative Table Construction
At the core of schema declarations are the table definition functions, which accept a table name and an object mapping column names to their definitions. Each column definition function (e.g., `text()`, `integer()`) acts as a builder, allowing for method chaining to apply modifiers such as `.notNull()`, `.primaryKey()`, or `.default()`.
The construction process is designed for composability. By separating the column name, the database name, and the constraint modifiers, Drizzle ensures that runtime execution paths can accurately map internal definitions to SQL syntax while type-checkers verify that query results align with the schema.
```typescript
// Example of a table declaration using pgTable
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
export const users = pgTable('users_table', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
age: integer('age'),
});
```
Sources: [drizzle-orm/type-tests/pg/tables.ts:87-105](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/tables.ts#L87-L105)
## Introspection and Serialization Mechanisms
The `drizzle-kit` introspectors (e.g., `introspect-pg.ts`, `introspect-mysql.ts`) operate by traversing database-specific internal schemas and serializing them into executable TypeScript code. The mechanism begins by collecting tables, enums, sequences, and views, then iteratively generating import statements and table declarations.
The core flow for `schemaToTypeScript` involves:
1. Identifying mandatory imports (e.g., `pgTable`, `index`, `foreignKey`).
2. Generating enum and sequence statements.
3. Iterating through tables to generate column statements via `createTableColumns`.
4. Appending constraint statements (indexes, primary keys, unique constraints, policies) using `createTableIndexes`, `createTablePKs`, etc.
```mermaid
flowchart TD
A["schemaToTypeScript"] --> B["Collect FKs and metadata"]
B --> C["Generate imports (list filtering)"]
C --> D["Generate Enums/Sequences"]
D --> E["Generate Tables (via createTableColumns)"]
E --> F["Generate Constraints (via index/FK/PK helpers)"]
F --> G["Final file string assembly"]
```
Sources: [drizzle-kit/src/introspect-pg.ts:309-636](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L309-L636)
## Column Mapping and Default Value Resolution
Mapping database types to Drizzle columns involves resolving type-specific metadata and defaults. The `mapDefault` function handles the resolution of default values by checking the type and the internal configuration of the column.
> [!NOTE]
> When `internals.tables[tableName].columns[name].isDefaultAnExpression` is `true`, the system treats the default as an SQL expression, wrapping it in `sql\` \`` to ensure it is not treated as a string literal.
The mechanism includes guard logic to prevent incorrect type-casting:
- `isArray` check: If a column is an array, `buildArrayDefault` is called.
- Enum checks: Verifies if the type exists in the enum set before applying a standard default mapping.
- Type-specific handlers: Specialized branches exist for `uuid` (using `.defaultRandom()`), `timestamp` (using `.defaultNow()`), and others.
Sources: [drizzle-kit/src/introspect-pg.ts:673-836](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L673-L836)
## Foreign Key and Relationship Handling
Foreign keys are registered by iterating through a table's `foreignKeys` collection. The introspector tracks existing relationships to detect cyclic dependencies, which helps in correctly ordering or annotating references.
The mechanism for creating foreign key references ensures that if a table references itself, the generator correctly uses `table` as the reference target instead of the generated variable name.
> [!WARNING]
> The current introspector implementation has specific limitations regarding naming; custom references in `.references()` are sometimes switched off in the generator to avoid potential naming collisions or complexity with generated identifiers.
Sources: [drizzle-kit/src/introspect-pg.ts:1343-1369](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L1343-L1369)
## Index and Constraint Generation
Indexes, primary keys, and unique constraints are defined as a factory function parameter after the column mapping. This is critical for supporting composite constraints that span multiple columns. The `createTableIndexes` function takes a list of indexes and processes their metadata to produce valid index statements.
The selection logic for index naming is determined by `indexName`: if an index name matches the default format (e.g., `tablename_column1_column2_index`), it remains implicit, otherwise, it is explicitly escaped as a string.
Sources: [drizzle-kit/src/introspect-pg.ts:1201-1257](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L1201-L1257)
## Design Choices and Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| Declarative Column Builder | Allows for clean, readable schema definitions | Increased complexity in the type-generation layer |
| Lazy Relation Resolution | Enables circular references between tables | Requires a two-pass approach or helper functions |
| Casing Normalization | Consistent naming throughout the application | Potential for conflicts with database-reserved words |
Sources: [drizzle-kit/src/introspect-pg.ts:168-177](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L168-L177)
## Call-Chain Execution: Introspecting a Table
The process of generating a table's TypeScript representation follows a specific hierarchy:
1. `schemaToTypeScript` invokes `Object.values(schema.tables).map(...)`.
2. Inside the map, `createTableColumns` is called for the column definitions.
3. `column()` is triggered for every column iteration, returning the column statement string.
4. If constraints are present, `createTableIndexes` or `createTableFKs` are called to append the constraint array factory function.
5. `statement += ');'` finalizes the table definition.
This sequential ordering ensures that the output file structure is consistent, grouping definitions, constraints, and final initialization code.
Sources: [drizzle-kit/src/introspect-pg.ts:508-565](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L508-L565)
## Related
- [[Column Types]]
- [[Indexes and Constraints]]
---
## Column Types
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/column-types
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/introspect-mysql.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-mysql.ts)
- [drizzle-kit/src/introspect-gel.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-gel.ts)
- [drizzle-kit/src/introspect-singlestore.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-singlestore.ts)
- [drizzle-orm/src/pg-core/columns/common.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/common.ts)
- [drizzle-orm/src/pg-core/columns/numeric.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/numeric.ts)
- [drizzle-orm/src/singlestore-core/columns/decimal.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/columns/decimal.ts)
- [drizzle-orm/src/column-builder.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/column-builder.ts)
- [drizzle-orm/src/sqlite-core/columns/numeric.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/columns/numeric.ts)
- [drizzle-orm/src/sqlite-core/columns/blob.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/columns/blob.ts)
- [drizzle-orm/src/sqlite-core/columns/integer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/columns/integer.ts)
- [drizzle-orm/src/singlestore-core/columns/datetime.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/columns/datetime.ts)
- [drizzle-orm/src/singlestore-core/columns/custom.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/columns/custom.ts)
- [drizzle-orm/src/gel-core/columns/custom.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/columns/custom.ts)
- [drizzle-orm/src/sqlite-core/columns/custom.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/columns/custom.ts)
Column Types in Drizzle provide a robust, type-safe interface for mapping database schemas to TypeScript objects. By defining a set of core builders and runtime definitions, Drizzle ensures that every column in a table can be accurately queried, validated, and serialized to the underlying database dialect, whether it is PostgreSQL, MySQL, SQLite, Gel, or SingleStore.
The system is architected around a separation between the build phase and the runtime phase. Builder classes, such as those derived from `PgColumnBuilder` or `SQLiteColumnBuilder`, manage configurations—such as nullability, default values, and primary key constraints—during the schema definition phase. When a table is built, these builders transition into runtime column instances, which hold the logic for data transformation between the driver's raw output and the application's domain types.
Design decisions within this subsystem prioritize type inference and dialect-specific abstraction. Builders use generics to track data types (e.g., `'string'`, `'number'`, `'boolean'`), while the column classes handle dialect-specific serialization quirks, such as different integer representations or JSON/Blob handling. This layered design allows Drizzle to expose a unified API surface while maintaining the performance and flexibility required for cross-dialect database management.
## Core Builder Infrastructure
The `PgColumnBuilder` or `SQLiteColumnBuilder` classes (and their equivalents) are the roots for column definitions in their respective dialects. Their primary purpose is to collect configuration metadata before a table is fully realized. They implement the chainable API used in table definitions, such as `.notNull()`, `.default()`, and `.primaryKey()`.
Internally, the `config` object acts as a state container. When you call `.notNull()`, the builder simply sets `this.config.notNull = true` and returns `this` cast to a specific type, ensuring the TypeScript compiler reflects this state change for subsequent chain calls.
Sources: [drizzle-orm/src/pg-core/columns/common.ts:38-43](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/common.ts#L38-L43), [drizzle-orm/src/sqlite-core/columns/common.ts:6](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/columns/common.ts#L6)
## Dialect-Specific Column Implementations
While building interfaces share commonality, actual data type implementation is split by dialect. For example, `PgNumericBuilder` and `SQLiteNumericBuilder` both offer numeric column support but handle the mapping logic to their respective driver's outputs differently.
In the `PgNumeric` implementation, the `mapFromDriverValue` function is designed to handle `unknown` inputs, converting them to `string` if they are not already, ensuring consistent behavior despite database variability. Conversely, the `SQLiteNumericNumber` implementation specifically maps its inputs to `number` type, demonstrating how builders and runtime definitions tailor the interface based on the requested `mode`.
Sources: [drizzle-orm/src/pg-core/columns/numeric.ts:40-67](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/numeric.ts#L40-L67), [drizzle-orm/src/sqlite-core/columns/numeric.ts:37-49](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/columns/numeric.ts#L37-L49), [drizzle-orm/src/sqlite-core/columns/numeric.ts:80-94](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/columns/numeric.ts#L80-L94)
## Custom Type Support
Drizzle provides a `customType` function for each dialect to support proprietary or complex database types that are not explicitly covered by the core library. This subsystem allows developers to specify the underlying SQL data type via a `dataType` function and provide custom `toDriver` and `fromDriver` mapping functions.
When a column is defined via `customType`, the builder instance (e.g., `SingleStoreCustomColumnBuilder` or `SQLiteCustomColumnBuilder`) holds the configuration, while the resulting column class instance encapsulates the mapping logic. This architecture prevents core code from needing awareness of every possible niche database type while still providing seamless integration.
Sources: [drizzle-orm/src/singlestore-core/columns/custom.ts:26-59](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/columns/custom.ts#L26-L59), [drizzle-orm/src/sqlite-core/columns/custom.ts:25-58](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/columns/custom.ts#L25-L58)
## Lifecycle and Initialization Flow
The transition from a definition to a concrete database object involves a specific initialization sequence. When a table is declared, the framework instantiates the builder classes. The `build()` method is then invoked, which consumes the `config` object from the builder and initializes the runtime column class.
For example, when constructing an array column in PostgreSQL:
1. `PgArrayBuilder` is initialized with a base builder.
2. The user calls `.build(table)`.
3. `PgArrayBuilder` instantiates the base column and passes it to the `PgArray` constructor.
4. The `PgArray` constructor sets the array-specific SQL type, such as `integer[]`.
Sources: [drizzle-orm/src/pg-core/columns/common.ts:260-307](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/common.ts#L260-L307)
## Serialization and Introspection Logic
Drizzle Kit performs introspection to convert raw database schema data back into Drizzle ORM TypeScript code. This involves mapping raw column types back to Drizzle function calls.
The `schemaToTypeScript` function in the MySQL introspector, for instance, iterates over table columns, patches type strings using a lookup table (`importsPatch`), and then dispatches the result to a `column()` function. This function acts as a factory, matching the string type to a constructor for the correct builder, and generating the necessary `.default()` or `.primaryKey()` chain calls.
Sources: [drizzle-kit/src/introspect-mysql.ts:133-195](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-mysql.ts#L133-L195), [drizzle-kit/src/introspect-mysql.ts:386-416](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-mysql.ts#L386-L416)
> [!NOTE]
> When introspecting columns, the framework maps "int unsigned" types to standard integer builders and applies an `{ unsigned: true }` parameter in the generated code.
## Data Mapping Mechanisms
Each column type contains logic to bridge the gap between driver data and application data. `mapFromDriverValue` and `mapToDriverValue` are the primary lifecycle hooks for this conversion.
For instance, the `SQLiteBlobJson` column type defines how it handles raw binary data:
```typescript
override mapFromDriverValue(value: Buffer | Uint8Array | ArrayBuffer): T['data'] {
// ... decoding logic
return JSON.parse(buf.toString('utf8'));
}
```
This is critical because SQLite doesn't have a native JSON type; it forces developers to store JSON objects in `blob` or `text` columns, and this mapping function abstracts that limitation entirely away from the developer.
Sources: [drizzle-orm/src/sqlite-core/columns/blob.ts:100-114](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/columns/blob.ts#L100-L114)
## Design Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Separate Builder Class** | Decouples runtime logic from schema configuration. | Requires double instantiation (builder → column instance). |
| **Dialect-specific Builders** | Allows for exact SQL feature mapping per database. | Increased codebase size through specialized classes. |
| **Type-safe Mapper** | Prevents runtime data corruption at the boundary. | Performance overhead during heavy read/write operations. |
Sources: [drizzle-orm/src/column-builder.ts:185-317](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/column-builder.ts#L185-L317), [drizzle-orm/src/pg-core/columns/numeric.ts:17-38](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/numeric.ts#L17-L38)
## Related
- [[Schema Declarations]]
---
## Indexes and Constraints
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/indexes-and-constraints
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/serializer/pgSerializer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts)
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-kit/src/serializer/pgSchema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts)
- [drizzle-kit/src/introspect-gel.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-gel.ts)
- [drizzle-kit/src/serializer/mysqlSerializer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/mysqlSerializer.ts)
Indexes and Constraints represent a critical abstraction layer in Drizzle Kit that bridges the gap between database schema definitions in TypeScript and the concrete implementations in target RDBMS (PostgreSQL, MySQL, Gel). These structures ensure data integrity (constraints) and provide mechanisms for query performance optimization (indexes).
In the context of the Drizzle ecosystem, this subsystem serves as a serializer and introspector. During snapshot generation, it traverses the `AnyPgTable` objects (imported from `drizzle-orm/pg-core`), extracts metadata concerning constraints (primary keys, foreign keys, unique constraints, and check constraints) and index definitions, and transforms them into a unified, serializable JSON schema format. This transformation ensures that database changes can be accurately diffed and represented as migration operations.
The design relies on a clear separation between the schema *definitions* (the runtime TypeScript objects) and the *internals* or *snapshot representations* used during diffing. By standardizing these into standardized types defined in `pgSchema.ts`, Drizzle Kit can perform schema introspection against live databases and compare the results with the defined code, ensuring that index naming collisions or constraint inconsistencies are caught during development.
## Core Schema Structures and Types
The subsystem defines schemas using Zod for validation to ensure the integrity of the serialized snapshots. The schema structures act as the "source of truth" for the current state of a database, housing definitions for `indexes`, `foreignKeys`, `compositePrimaryKeys`, `uniqueConstraints`, and `checkConstraints`.
| Structure | Description | Key Properties |
| :--- | :--- | :--- |
| `Index` | Defines an index, potentially unique or covering multiple columns. | `columns`, `isUnique`, `method`, `where`, `concurrently` |
| `ForeignKey` | Represents a relational link between tables. | `tableFrom`, `columnsFrom`, `tableTo`, `onUpdate`, `onDelete` |
| `UniqueConstraint` | Ensures column data uniqueness. | `columns`, `nullsNotDistinct`, `name` |
| `CheckConstraint` | Validates data against a logical expression. | `name`, `value` |
| `PrimaryKey` | Identifies primary keys, including composite keys. | `columns`, `name` |
Sources: [drizzle-kit/src/serializer/pgSchema.ts:139-341](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts#L139-L341)
## Introspection Flow
Introspection is the process of querying the live database catalog and reconstructing the schema objects. This allows Drizzle to "see" the database state.
### Execution Walkthrough
1. **Catalog Querying**: The process starts by identifying existing tables via `pg_catalog.pg_class`.
2. **Table Constraint Collection**: For each table, the system queries `information_schema.table_constraints` to identify `PRIMARY KEY`, `UNIQUE`, and `CHECK` constraints.
3. **Foreign Key Mapping**: The system probes `pg_catalog.pg_constraint` to retrieve link metadata (which table references what, and associated rules like `ON DELETE` or `ON UPDATE`).
4. **Index Retrieval**: A query on `pg_index` and `pg_class` retrieves index metadata, including access methods (e.g., `btree`), expressions, and operator classes.
5. **Snapshot Construction**: These pieces are consolidated into the final schema object, which is then serialized.
Sources: [drizzle-kit/src/serializer/pgSerializer.ts:988-1664](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L988-L1664), [drizzle-kit/src/introspect-pg.ts:1230-1652](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L1230-L1652)
```mermaid
flowchart TD
A["Query DB Catalog"] --> B["Fetch Constraints
via Information Schema"]
B --> C["Fetch Foreign Key
Metadata"]
C --> D["Fetch Index
Definitions"]
D --> E["Reconstruct
Schema Object"]
E --> F["Serialize to
Snapshot JSON"]
```
Sources: [drizzle-kit/src/introspect-pg.ts:1230-1652](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L1230-L1652)
## Handling Constraints and Naming Collisions
A key mechanism within `generatePgSnapshot` is the detection of duplicated constraint names within a schema. This is handled by a local `checksInTable` or `indexesInSchema` lookup table.
> [!CAUTION]
> If a developer specifies a duplicate index name for different tables within the same schema, Drizzle Kit will detect this via the `indexesInSchema` lookup and terminate the process with an error, ensuring migration consistency.
The system uses `indexName()` to automatically generate names for indexes where the user has not provided one, preventing "unnamed" indexes from causing non-deterministic migrations.
Sources: [drizzle-kit/src/serializer/pgSerializer.ts:46-48](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L46-L48), [drizzle-kit/src/serializer/pgSerializer.ts:124-129](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L124-L129), [drizzle-kit/src/serializer/pgSerializer.ts:456-476](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L456-L476)
## Index and Expression Handling
When dealing with expressions in indexes, the serializer requires the user to explicitly name the index, as automatic naming (which relies on column names) is insufficient.
```typescript
// Example: Index on an expression
index("my_index").using("btree", sql`lower(column_name)`)
```
The system validates the index configuration to ensure that specialized extensions (like `pg_vector`) have the necessary operator classes specified. The mechanism checks if the column type is a `PgVector` and iterates through a list of `vectorOps` to ensure the correct operator class is chosen.
Sources: [drizzle-kit/src/serializer/pgSerializer.ts:373-423](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L373-L423)
## Architecture Comparison
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Snapshot-based Diffing** | Allows migration generation between any two states. | Requires maintaining state serialization. |
| **Centralized Serializer** | Uniform handling of index/constraint names. | High complexity in mapping dialect-specific behavior. |
| **Runtime Validation (Zod)** | Guarantees schema consistency in files. | Slight overhead during startup/execution. |
Sources: [drizzle-kit/src/serializer/pgSerializer.ts:101-113](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L101-L113), [drizzle-kit/src/serializer/pgSchema.ts:5-46](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts#L5-L46)
## Schema to TypeScript Generation
The `schemaToTypeScript` function is responsible for converting the serialized snapshot back into TypeScript code. This involves iterating through the table definitions and invoking helper functions like `createTableIndexes` and `createTableFKs`.
The mechanism for creating indexes specifically maps the internal `Index` model to a string statement:
1. **Naming**: Resolves whether to use an explicit or generated index name.
2. **Method/Options**: Injects the index method (e.g., `btree`) and any specific `with` options.
3. **Columns**: Maps column objects to their Drizzle code counterparts, handling expressions, ordering, and operator classes.
Sources: [drizzle-kit/src/introspect-pg.ts:309-1201](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L309-L1201)
```mermaid
sequenceDiagram
participant S as Schema Object
participant T as TypeScript Generator
participant C as Column/Index/FK helpers
S->>T: schemaToTypeScript(schema)
loop For each table
T->>C: createTableIndexes(table.indexes)
C-->>T: returns index code strings
T->>C: createTableFKs(table.foreignKeys)
C-->>T: returns FK code strings
end
T-->>S: returns TypeScript file content
```
Sources: [drizzle-kit/src/introspect-pg.ts:309-565](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L309-L565)
## Related
- [[Schema Declarations]]
---
## Entity Relations
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/entity-relations
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts)
- [drizzle-orm/src/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts)
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-kit/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-orm/src/mysql-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts)
Entity Relations form the core of the relational query engine in Drizzle ORM. They act as the abstraction layer that maps TypeScript schema definitions to database-level operations, enabling complex graph-like queries across multiple tables while maintaining type safety. By decoupling the query definition from the underlying SQL dialect, the subsystem allows developers to express deeply nested relationships without manually writing verbose `JOIN` or aggregate logic.
The architecture solves the "N+1" problem by translating high-level relational configuration—such as `One` and `Many` definitions—into optimized, dialect-specific SQL. It uses an internal configuration model to understand table structures, primary keys, and foreign key constraints, which it then uses to dynamically generate subqueries, lateral joins, or aggregate functions as required by the backend dialect (Postgres, MySQL, or Gel).
The design prioritizes flexibility and performance by performing a multi-pass transformation during query building. It handles complex cases like cyclic references and lateral subqueries by normalizing relations before dispatching them to the query builder. This design enables seamless interaction with the query builders, where relations are resolved into SQL execution plans at runtime.
## Core Relational Configuration
The subsystem relies on a relational config structure to understand how entities connect. This structure, typically extracted via `extractTablesRelationalConfig`, maps database table objects into a graph of `One` and `Many` relations. This metadata allows the dialect to determine, for example, if a `Many` relation necessitates a `JSON_AGG` or similar aggregation to satisfy a nested result set.
Sources: [drizzle-kit/src/introspect-pg.ts:210-210](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L210-L210), [drizzle-orm/src/relations.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/relations.ts)
## Normalization Mechanism
Normalization is the process of flattening and standardizing relation definitions. The `normalizeRelation` utility function transforms raw metadata into a predictable format (with `fields` and `references` arrays), ensuring the query builder doesn't need to handle dialect-specific implementation details of a relationship during the building phase. This provides a uniform contract for joining tables regardless of their origin.
Sources: [drizzle-orm/src/pg-core/dialect.ts:1223-1223](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L1223-L1223), [drizzle-orm/src/gel-core/dialect.ts:1292-1292](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts#L1292-L1292)
## Building Relational Queries
The core engine responsible for turning relational config into SQL resides in methods like `buildRelationalQueryWithoutPK` or `buildRelationalQuery`. These functions act as a recursive traversal engine. When a developer executes a relational query, the builder walks the relationship graph, dynamically constructing aliases and `LEFT JOIN` operations.
- The process begins by selecting columns and optional "extras" defined in the query configuration.
- It then iterates over requested relations, calculating join conditions based on normalized foreign key metadata.
- If a `Many` relation is encountered, the builder may inject subqueries or lateral joins to maintain row integrity while aggregating related records.
Sources: [drizzle-orm/src/pg-core/dialect.ts:1150-1444](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L1150-L1444), [drizzle-orm/src/mysql-core/dialect.ts:658-1336](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L658-L1336)
## Query Builder Selection Strategy
When the builder decides how to fetch related data, it evaluates the nature of the relation. `Many` relations (which imply 1:N or M:N) are typically treated with subquery aggregation, whereas `One` relations may be joined directly. A key architectural guard exists here: the code explicitly distinguishes between queries with primary keys and those without, selecting different strategies to ensure the database can resolve rows uniquely.
> [!TIP]
> When building relational queries, `isLateralJoin` logic determines if a subquery needs to be correlated with the outer query. This is a critical performance path for handling complex joins.
Sources: [drizzle-orm/src/pg-core/dialect.ts:858-859](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L858-L859), [drizzle-orm/src/mysql-core/dialect.ts:1208-1212](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L1208-L1212)
## Dialect-Specific Aggregation
The dialect-specific implementation files provide the final SQL generation logic. Because PostgreSQL, MySQL, and Gel handle JSON aggregation differently (e.g., `json_agg` vs `json_arrayagg`), the dialect methods `buildRelationalQueryWithoutPK` or `buildRelationalQuery` encapsulate these differences. This isolates the relational engine from the database driver, allowing the same high-level query syntax to function across different database backends.
Sources: [drizzle-orm/src/pg-core/dialect.ts:1369-1371](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L1369-L1371), [drizzle-orm/src/mysql-core/dialect.ts:1249-1250](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L1249-L1250)
## Introspection and Schema Generation
The subsystem also provides utilities for reconstructing these relationships from existing databases. The `introspect-pg` module scans schema definitions, foreign key constraints, and table names to output TypeScript code that defines the `relations()` configuration. This bridges the gap between raw database structure and the relational engine’s requirements.
Sources: [drizzle-kit/src/introspect-pg.ts:210-272](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L210-L272)
## Key Design Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Recursive Subqueries** | Deeply nested relations support | Potential SQL performance degradation on massive tables |
| **Normalization** | Clean code paths for query generation | Overhead during query building initialization |
| **Dialect Isolation** | Backend portability | Increased boilerplate in individual dialect files |
Sources: [drizzle-orm/src/pg-core/dialect.ts:1150-1444](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L1150-L1444), [drizzle-orm/src/mysql-core/dialect.ts:658-1336](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L658-L1336)
## Related
- [[Schema Declarations]]
- [[Select Queries]]
---
## Select Queries
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/select-queries
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts)
- [drizzle-orm/src/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts)
- [drizzle-orm/type-tests/pg/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/select.ts)
- [drizzle-orm/src/mysql-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts)
Select queries in Drizzle ORM are responsible for transforming high-level, type-safe builder configurations into raw SQL statements appropriate for the target database dialect (PostgreSQL, MySQL, or Gel). This subsystem provides the bridge between the developer's intuitive query builder API and the database-specific syntax requirements, ensuring that complex joins, conditional clauses, and set operations are rendered into valid, performant queries.
The architecture centers around dialect-specific classes—`PgDialect`, `GelDialect`, and `MySqlDialect`—which implement common query-building interfaces. By isolating dialect-specific concerns like quoting identifiers (`escapeName`), param handling (`escapeParam`), and specific SQL syntax patterns (like `limit` or `lockingClause`), the system maintains a unified developer experience regardless of the underlying database engine.
At a high level, the flow involves taking a `SelectConfig` object, validating the query's structural integrity, building component chunks (e.g., CTEs, selections, joins, where clauses), and joining them into a final `SQL` object. This modular design allows Drizzle to handle complex features like nested relational queries and set operations consistently across different SQL dialects.
## The `buildSelectQuery` Mechanism
The `buildSelectQuery` method is the heart of the selection subsystem. It accepts a configuration object and orchestrates the assembly of various SQL components. A critical validation step occurs before building: it iterates over selected fields to ensure that every referenced column exists within the query's tables or joins.
If a field is found that does not belong to any table currently in the query, `buildSelectQuery` throws an error to prevent invalid SQL execution. This check is performed by comparing the table of the field with the table(s) defined in the query configuration, including those added via joins.
```typescript
// Example of validation logic (from PgDialect)
for (const f of fieldsList) {
if (is(f.field, Column) && getTableName(f.field.table) !== getTableName(table) && !joins?.some(...)) {
throw new Error(`Your field references a column "${tableName}"."${f.field.name}", but the table is not part of the query!`);
}
}
```
Sources: [drizzle-orm/src/pg-core/dialect.ts:360-382](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L360-L382)
## Selection Construction
The `buildSelection` method manages the complexity of selecting columns versus expressions. It performs a case-based analysis on each field in the ordered selection list:
- **Aliased fields:** If flagged as a selection field, it uses the alias as an identifier.
- **SQL expressions/Aliased SQL:** It extracts the underlying SQL chunks. If `isSingleTable` is enabled, it attempts to normalize column references by applying the dialect-specific column casing.
- **Columns:** If `isSingleTable` is true, it replaces the column object with its cased identifier.
- **Subqueries:** It performs special decoder mapping to ensure the results are mapped correctly from the driver values.
The `isSingleTable` flag is crucial: it determines whether columns need to be prefixed with their table names, which is often unnecessary in single-table queries but required in joins to resolve ambiguity.
Sources: [drizzle-orm/src/pg-core/dialect.ts:211-277](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L211-L277)
## Handling Joins
The `buildJoins` mechanism processes an array of `JoinConfig` objects. It iterates through the meta-data, determining the join type (e.g., `inner`, `left`) and handling specific database features like `lateral` joins.
The logic builds a dynamic `SQL` join array:
1. Adds a separator if it is the first join.
2. Identifies the table or view, applying schema prefixes and aliases where necessary.
3. Appends the join condition (`on` clause).
4. For MySql, it additionally processes `USE`, `FORCE`, and `IGNORE` index hints.
Sources: [drizzle-orm/src/pg-core/dialect.ts:279-325](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L279-L325), [drizzle-orm/src/mysql-core/dialect.ts:383-439](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L383-L439)
> [!TIP]
> Use `.leftJoin()` or `.innerJoin()` in the query builder to trigger these specific `buildJoins` branches. Ensuring your aliases are unique is critical, as they serve as the keys for later selection resolution.
## Set Operations (UNION, INTERSECT, EXCEPT)
When `setOperators` are present in the `SelectConfig`, `buildSelectQuery` delegates to `buildSetOperations`. This function uses recursion to collapse multiple set operations into a single query structure.
The recursive mechanism (`buildSetOperations`) pulls the first operator, processes it against the base query (`leftSelect`), and if there are more operators, it feeds the resulting `SQL` back into the function recursively. This effectively nests set operations, preserving order and precedence according to SQL standards.
Sources: [drizzle-orm/src/pg-core/dialect.ts:450-466](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L450-L466)
## Query Execution Lifecycle
The query lifecycle traces from the user-facing builder methods to the final dialect-specific execution.
```mermaid
flowchart TD
A["User Call: select()"] --> B["Query Builder: build()"]
B --> C["Dialect: buildSelectQuery()"]
C --> D["Validation (Table checks)"]
D --> E["Component Builders (With, From, Joins, Selection)"]
E --> F["SQL Joining"]
F --> G["Final SQL Object"]
G --> H["Driver: Session Execution"]
```
Sources: [drizzle-orm/src/pg-core/dialect.ts:341-448](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L341-L448)
## Design Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Separated Dialect Logic** | Enables database-specific syntax (e.g., MySQL vs PG) | Increases code duplication between dialects |
| **Recursive Set Ops** | Standardized handling of complex chaining | Increased call stack depth for long chains |
| **Pre-Query Validation** | Prevents runtime DB errors for missing joins | Slight performance overhead for complex queries |
| **CasingCache** | Abstracted casing rules | Memory usage for cache instances |
Sources: [drizzle-orm/src/pg-core/dialect.ts:63-71](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L63-L71), [drizzle-orm/src/pg-core/dialect.ts:450-466](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L450-L466)
## Working Example
The following example demonstrates building a complex joined query with custom SQL selection:
```typescript
import { sql } from 'drizzle-orm';
import { db } from './db';
import { users, cities } from './tables';
// The query builder will internally call PgDialect.buildSelectQuery()
const result = await db
.select({
id: users.id,
text: users.text,
city: {
id: cities.id,
name: cities.name,
},
custom: sql`upper(${users.text})`.as('custom_text')
})
.from(users)
.leftJoin(cities, eq(users.id, cities.id))
.where(eq(users.id, 1));
```
Sources: [drizzle-orm/type-tests/pg/select.ts:205-233](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/select.ts#L205-L233)
> [!CAUTION]
> The validation logic in `buildSelectQuery` is strict. If you use a column in a selection that belongs to a table not present in the `.from()` or `.join()` clauses, the dialect will throw an error immediately, preventing the query from being sent to the database. Always verify your join path.
## Related
- [[Query Builder Core]]
- [[SQL Expressions]]
---
## Data Mutations
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/data-mutations
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/mysql-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts)
- [drizzle-orm/src/singlestore-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts)
- [drizzle-orm/src/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts)
- [drizzle-orm/src/singlestore-core/query-builders/insert.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/insert.ts)
- [drizzle-orm/src/gel-core/query-builders/insert.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/insert.ts)
- [drizzle-orm/src/gel-core/db.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/db.ts)
- [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts)
Data mutations in Drizzle ORM facilitate the controlled modification of database state via `INSERT`, `UPDATE`, and `DELETE` operations. By decoupling query construction from execution, the system provides a type-safe abstraction over SQL dialects while ensuring that complex column constraints, default values, and conflict resolution strategies are handled consistently across different database providers (MySQL, SingleStore, Gel, PostgreSQL).
The architecture treats mutations as composable SQL builders. When a developer invokes a mutation method—such as `.values()` on an insert query builder—they are populating a configuration object that captures the intent. The dialect then translates this configuration into an executable `SQL` object. This mechanism relies on the dialect-specific classes (e.g., `MySqlDialect`, `SingleStoreDialect`, `GelDialect`) to enforce casing conventions, handle parameter escaping, and generate vendor-specific SQL clauses, such as `ON DUPLICATE KEY UPDATE` for MySQL/SingleStore or `RETURNING` for PostgreSQL-style dialects.
Beyond mere abstraction, this layer serves as a safety guard against common SQL pitfalls. By programmatically constructing these queries using the schema configuration (e.g., table-specific column definitions, default function execution, and runtime param injection), Drizzle prevents issues like missing mandatory fields or invalid identifier escaping before the query ever hits the database driver.
## Insert Lifecycle and Execution
The `INSERT` mutation lifecycle centers on capturing values and resolving default behaviors before serialization. For `SingleStore` and `Gel`, the process begins with query builder classes (`SingleStoreInsertBuilder`, `GelInsertBuilder`), which collect data from the user and map them into `Param` or `SQL` wrappers.
When `getSQL()` or `prepare()` is called, the dialect orchestrates the final transformation. A critical mechanism here is the column iteration loop within `buildInsertQuery`. It filters columns based on `shouldDisableInsert()` (typically used for auto-generated IDs or specific DB-controlled fields) and checks for missing values to apply `defaultFn` logic.
### Call Chain: Executing an Insert
1. `db.insert(table).values(...)` → Initializes `SingleStoreInsertBase` or `GelInsertBase` with user-provided configuration.
2. `.values()` → Sanitizes and parameterizes the input objects.
3. `.prepare()` → Invokes `dialect.buildInsertQuery()` to construct the raw `SQL` and collect metadata about generated IDs.
4. `.execute()` → Calls `session.prepareQuery()` to pass the `SQL` to the driver and perform the operation.
Sources: [drizzle-orm/src/singlestore-core/query-builders/insert.ts:50-282](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L50-L282), [drizzle-orm/src/gel-core/query-builders/insert.ts:237-405](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/insert.ts#L237-L405)
## Conflict Resolution
Data mutations frequently require handling race conditions where existing unique constraints might be violated. The `onDuplicateKeyUpdate` (MySQL/SingleStore) and `onConflict` (Postgres/Gel) patterns are primary mechanisms for this.
For MySQL/SingleStore, the `onDuplicateKeyUpdate` method injects a specific `onConflict` clause into the configuration. The dialect builder then appends `on duplicate key update` followed by the result of `buildUpdateSet`. This allows developers to pass partial update objects, which the dialect then converts into standard SQL set expressions.
> [!TIP]
> To simulate a "do nothing" behavior on conflict in MySQL/SingleStore, use the `onDuplicateKeyUpdate` method and set a column to itself: `onDuplicateKeyUpdate({ set: { id: sql`id` } })`. This effectively performs a no-op update that satisfies the clause requirement.
Sources: [drizzle-orm/src/singlestore-core/query-builders/insert.ts:246-252](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L246-L252), [drizzle-orm/src/mysql-core/dialect.ts:638-640](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L638-L640)
## The Update Set Mechanism
The `buildUpdateSet` function is the engine behind `UPDATE` operations. It accepts a table definition and a set of changes, iterating through the table's columns to generate the `col = value` assignments.
Key logic inside this method handles two specific cases:
1. **Direct Value Assignment:** User-provided values are mapped to parameters.
2. **Default/Update Functions:** If a column has an `onUpdateFn` (like a timestamp auto-updater) and no explicit value is provided, the builder executes the function to generate the `SQL` clause.
```typescript
// Core logic for building update sets in MySQL/SingleStore
const columnNames = Object.keys(tableColumns).filter(
(colName) =>
set[colName] !== undefined
|| tableColumns[colName]?.onUpdateFn !== undefined,
);
// Each result is formatted as: `column_name = value`
```
Sources: [drizzle-orm/src/mysql-core/dialect.ts:149-176](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L149-L176), [drizzle-orm/src/singlestore-core/dialect.ts:148-175](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts#L148-L175)
## Returning Clauses
Some dialects (Postgres, Gel) support a `RETURNING` clause to fetch the modified row state directly from the mutation. In the Drizzle dialect implementations, `buildSelection` is shared by both `SELECT` and `RETURNING` queries.
When `returning` is provided in a mutation config, the dialect wraps the fields in the `returning` SQL keyword. If `isSingleTable` is true, the builder avoids prefixing columns with table identifiers, which is often cleaner for single-target mutations.
Sources: [drizzle-orm/src/gel-core/dialect.ts:180-182](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts#L180-L182), [drizzle-orm/src/pg-core/dialect.ts:192-194](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L192-L194)
## Worked Example: Inserting with Conflict Handling
The following example demonstrates how to perform a mutation in the `SingleStore` dialect, using the builder API to handle potential unique key conflicts.
```typescript
import { sql } from 'drizzle-orm';
// Assuming 'users' is a defined SingleStoreTable
await db.insert(users)
.values({ id: 1, email: 'john@example.com' })
.onDuplicateKeyUpdate({
set: {
email: 'new_email@example.com',
updatedAt: sql`now()` // Use SQL function for timestamp
}
});
```
This request flows through:
1. `SingleStoreInsertBuilder.values()`: Registers the insert value.
2. `SingleStoreInsertBase.onDuplicateKeyUpdate()`: Sets the conflict configuration.
3. `dialect.buildUpdateSet()`: Computes the `SQL` chunk for the update clause.
4. `dialect.buildInsertQuery()`: Aggregates the `insert` and `update` chunks into the final statement.
Sources: [drizzle-orm/src/singlestore-core/query-builders/insert.ts:61-252](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L61-L252)
## Design Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Separate Dialect Build Logic** | Platform-specific syntax (e.g., MySQL vs. Postgres) is isolated. | Requires maintaining parallel logic for shared concepts. |
| **Manual Loop-based Construction** | Full control over chunk ordering and escaping. | Higher complexity compared to string-template alternatives. |
| **Proxy-based Selection** | Allows flexible field aliasing and naming. | Introduces runtime overhead in property lookups. |
Sources: [drizzle-orm/src/mysql-core/dialect.ts:44-1000](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L44-L1000), [drizzle-orm/src/gel-core/dialect.ts:51-1436](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts#L51-L1436)
## Related
- [[Query Builder Core]]
---
## Query Builder Core
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/query-builder-core
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/mysql2/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql2/session.ts)
- [drizzle-orm/src/bun-sql/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/bun-sql/session.ts)
- [drizzle-orm/src/postgres-js/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/postgres-js/session.ts)
- [drizzle-orm/src/gel/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel/session.ts)
- [drizzle-orm/src/singlestore-core/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/session.ts)
- [drizzle-orm/src/mysql-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-proxy/session.ts)
- [drizzle-orm/src/op-sqlite/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/op-sqlite/session.ts)
- [drizzle-orm/src/pg-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-proxy/session.ts)
- [drizzle-orm/src/sqlite-core/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/session.ts)
- [drizzle-orm/src/xata-http/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/xata-http/session.ts)
- [drizzle-orm/src/sqlite-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-proxy/session.ts)
- [drizzle-orm/src/libsql/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/session.ts)
- [drizzle-orm/src/mysql-core/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/session.ts)
- [drizzle-orm/src/singlestore/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts)
- [drizzle-orm/src/singlestore-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-proxy/session.ts)
- [drizzle-orm/src/planetscale-serverless/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/planetscale-serverless/session.ts)
- [drizzle-orm/src/bun-sqlite/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/bun-sqlite/session.ts)
- [drizzle-orm/src/better-sqlite3/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/better-sqlite3/session.ts)
- [drizzle-orm/src/neon-serverless/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-serverless/session.ts)
- [drizzle-orm/src/expo-sqlite/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/expo-sqlite/session.ts)
- [drizzle-orm/src/gel-core/query-builders/query-builder.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/query-builder.ts)
- [drizzle-orm/src/sqlite-core/db.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/db.ts)
- [drizzle-orm/src/pg-core/query-builders/query-builder.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/query-builders/query-builder.ts)
The "Query Builder Core" subsystem serves as the foundational abstraction layer that decouples Drizzle's expressive DSL from the myriad of underlying database drivers and execution runtimes. By providing standardized interfaces for statement preparation, query execution, and transactional lifecycle management, it enables the Drizzle ORM to support diverse targets—including PostgreSQL (Neon, Postgres.js, Bun SQL), MySQL (PlanetScale, MySQL2), and SQLite (Better-SQLite3, LibSQL, OP-SQLite)—without exposing developers to the idiosyncratic differences of each driver’s API.
At its heart, the core resides in driver-specific classes that extend base session and query classes. These implementations manage database connections, provide methods for transactional scope creation, and act as a factory for specific prepared query objects. Each driver maps the generic Drizzle execution methods (like `.execute()`, `.all()`, or `.get()`) to the target driver’s specific command execution protocols.
The architecture emphasizes type-safe query composition and efficient execution. By abstracting the interaction into prepared query objects, Drizzle defers the actual SQL generation and parameter binding until the moment of execution, allowing for driver-level optimizations, logging, and caching strategies. This design decouples the "query intent" from "query execution," permitting centralized features like query caching, tracing, and result set mapping to operate consistently across disparate backend storage engines.
## Session Lifecycle and Factory Mechanism
The base session classes are the primary orchestrators for driver-level interactions. They mandate the implementation of `prepareQuery`, which creates an instance of a prepared query subclass specifically tailored for the underlying driver. This factory pattern ensures that every dialect-specific detail—such as parameter placeholders, type parsing, or vendor-specific SQL extensions—is encapsulated within the prepared query instance rather than leaking into the main application logic.
Sources: [drizzle-orm/src/mysql-core/session.ts:180-191](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/session.ts#L180-L191)
## Query Execution and Cache Pipeline
The execution flow for queries is centralized to leverage common cross-dialect features. The `queryWithCache` method, present in various prepared query implementations, serves as the main gateway for query dispatch. It checks the global cache configuration before invoking the driver's execution method. For mutation queries (INSERT, UPDATE, DELETE), this method orchestrates both the database execution and the necessary cache invalidation via the `onMutate` interface, ensuring data consistency across the application.
> [!NOTE]
> During mutations, `onMutate` is called concurrently with the database query to allow the cache to invalidate specific table entries based on the `queryMetadata` tables.
Sources: [drizzle-orm/src/mysql-core/session.ts:70-154](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/session.ts#L70-L154), [drizzle-orm/src/sqlite-core/session.ts:71-155](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/session.ts#L71-L155)
## Transactional Scoping and Savepoints
Transactional management follows a consistent lifecycle across drivers: creating a transaction instance, executing an initialization command (e.g., `BEGIN`), performing the user-provided transaction callback, and finalizing with a commit or rollback command. Nested transactions are typically supported via `SAVEPOINT` logic, where the transaction instance tracks a `nestedIndex` to generate unique savepoint names.
```mermaid
sequenceDiagram
participant User
participant TX
participant DB
User->>TX: transaction(callback)
TX->>DB: savepoint sp1
activate DB
TX->>DB: callback()
DB-->>TX: success
TX->>DB: release savepoint sp1
deactivate DB
User-->>TX: result
```
Sources: [drizzle-orm/src/mysql2/session.ts:331-349](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql2/session.ts#L331-L349), [drizzle-orm/src/sqlite-core/session.ts:151-162](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/session.ts#L151-L162)
## Parameter Placeholder Handling
To ensure security and compatibility, all drivers utilize the `fillPlaceholders` utility. This mechanism maps the user-provided parameter values against the query string's expected placeholders. This ensures that the final SQL string sent to the driver is correctly formatted and that parameters are passed via the driver’s secure parameterized query interface, preventing SQL injection.
Sources: [drizzle-orm/src/mysql2/session.ts:93-95](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql2/session.ts#L93-L95), [drizzle-orm/src/pg-core/session.ts:13-15](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/session.ts#L13-L15)
## Driver-Specific Result Mapping
Result mapping is a critical step where raw rows are transformed into the expected entity shape using `mapResultRow`. This utility is invoked within the prepared query’s execution methods (`execute` or `all`). By relying on column-based metadata, Drizzle can reconstruct nested relational objects or alias complex SQL expressions into predictable JavaScript object shapes.
Sources: [drizzle-orm/src/mysql2/session.ts:142-142](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql2/session.ts#L142-L142), [drizzle-orm/src/bun-sql/session.ts:74-74](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/bun-sql/session.ts#L74-L74)
## Design Choices and Implementation Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Abstract Sessions** | Unified API for all databases. | Requires driver-specific implementations of low-level methods. |
| **Prepared Query Factory** | Consistent query caching and metadata tracking. | Increased object allocation per query. |
| **Lazy Dialect Loading** | Prevents circular dependencies during init. | Slightly deferred initialization error detection. |
Sources: [drizzle-orm/src/gel-core/query-builders/query-builder.ts:128-135](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/query-builder.ts#L128-L135)
## Practical Example: Using the Query Builder
Developers interact with the core primarily through the database instance, which delegates execution to the session.
```typescript
// Example: Executing a query with the SQLite core API
const users = sqliteTable('users', { id: integer('id'), name: text('name') });
// db is an instance of BaseSQLiteDatabase
const allUsers = await db.select().from(users).all();
// Underlying execution chain:
// 1. db.select() -> SQLiteSelectBuilder
// 2. .from(users) -> sets table
// 3. .all() -> calls session.all()
// 4. session.prepareOneTimeQuery().all() -> maps to driver-specific execution
```
Sources: [drizzle-orm/src/sqlite-core/db.ts:396-402](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/db.ts#L396-L402), [drizzle-orm/src/sqlite-core/session.ts:280-285](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/session.ts#L280-L285)
## Related
- [[Select Queries]]
- [[Data Mutations]]
---
## SQL Expressions
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/sql-expressions
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts)
- [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts)
- [drizzle-orm/src/sql/sql.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts)
- [drizzle-orm/src/singlestore-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts)
- [drizzle-orm/src/sqlite-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/dialect.ts)
- [drizzle-orm/src/sql/expressions/conditions.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/expressions/conditions.ts)
SQL Expressions are the fundamental building blocks of Drizzle ORM's query-building system. They provide a unified, type-safe abstraction for constructing SQL queries across different database dialects while maintaining portability and expressive power. By encapsulating raw query parts into composable objects, the library allows developers to write SQL using familiar TypeScript syntax that safely handles parameter binding and escaping without sacrificing direct control over the generated query structure.
The system is designed around the `SQL` class, which serves as the core container for query fragments (chunks). These chunks can be strings, column references, table references, nested SQL expressions, or user-provided parameters. This architecture treats a query as an abstract syntax tree of fragments, which is only flattened into a final string and a set of parameters at the very last moment by a dialect-specific engine. This deferred execution ensures that the driver handles parameter binding consistently and securely.
Beyond simple composition, the SQL Expression system acts as the bridge between TypeScript's type system and the database's execution layer. It manages driver-specific value encoding and decoding, identifier escaping, and placeholder transformation. Because the system is built recursively—where expressions can contain sub-expressions—it enables the creation of complex, modular queries that are easy to maintain, test, and adapt to different database backends (Gel, PostgreSQL, SQLite, SingleStore).
## The Core `SQL` Container
The `SQL` class is the central orchestrator for query fragment management. It accepts an array of `SQLChunk` objects and maintains a list of `usedTables` for internal tracking. The primary responsibility of this class is to serve as a builder that can aggregate query fragments via the `append` method, allowing for dynamic query construction.
Crucially, `SQL` instances are immutable in their definition, but the builder functions like `sql.join` or the `sql` template literal create new instances, ensuring that queries are composed safely. The `SQL` class exposes the `getSQL()` method, which fulfills the `SQLWrapper` interface, ensuring that any expression (a table, a column, or a subquery) can be treated uniformly by the query builder.
Sources: [drizzle-orm/src/sql/sql.ts:103-130](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L103-L130)
## Template Literals and Composition
The `sql` tagged template literal is the primary entry point for developers to write SQL queries. When invoked, it tokenizes the input template and wraps variables in appropriate `SQLChunk` types. This transformation is not merely syntactic sugar; it ensures that every interpolated value is treated as part of the query structure, which helps the system distinguish between query keywords (strings) and dynamic content (parameters, identifiers, or other expressions).
The mechanism works by alternating between `StringChunk` instances (for raw text) and the interpolated expressions passed as arguments. This sequence is then fed into a new `SQL` instance.
```typescript
// Example of how the SQL expression system builds fragments
const query = sql`SELECT * FROM ${users} WHERE ${eq(users.id, 1)}`;
```
*In this example, the system tracks the `users` table reference separately from the condition `eq(...)`, allowing the dialect to later perform dialect-specific identifier escaping for both.*
Sources: [drizzle-orm/src/sql/sql.ts:485-495](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L485-L495)
## Dialect-Specific Compilation
The compilation process, or `sqlToQuery`, is where the abstract `SQL` container is flattened into a runnable format for a specific database driver. The `SQL.toQuery` method is responsible for iterating over `queryChunks` and executing transformations based on a `BuildQueryConfig`.
The dialect provides specific implementation logic for `escapeName`, `escapeParam`, and `escapeString`. This ensures that, for instance, a MySQL/SingleStore backend uses backticks while a PostgreSQL/Gel backend uses double quotes for identifiers.
```mermaid
flowchart TD
A["sql.toQuery(config)"] --> B["mergeQueries(chunks.map)"]
B --> C{Chunk Type?}
C -->|StringChunk| D["Return raw string"]
C -->|Column| E["Escape identifier & concat table"]
C -->|Param| F["Map to driver value & call escapeParam"]
C -->|SQL| G["Recursive call: buildQueryFromSourceParams"]
F --> H["Return query object {sql, params}"]
```
Sources: [drizzle-orm/src/sql/sql.ts:137-146](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L137-L146), [drizzle-orm/src/pg-core/dialect.ts:609-618](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L609-L618)
## Parameter Binding and Encoding
Parameter binding is managed by the `Param` class and the `DriverValueEncoder` interface. When a parameter is encountered during query compilation, the system invokes `encoder.mapToDriverValue()`. This is a critical architectural step: it allows the ORM to convert complex TypeScript types (like `Date`, `JSON` objects, or custom classes) into formats the underlying database driver understands.
> [!NOTE]
> If a value is wrapped in `Param`, the system checks if it is a `Placeholder`. If it is, the system defers binding until the final query execution time, which allows for prepared statements.
Sources: [drizzle-orm/src/sql/sql.ts:432-450](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L432-L450)
## Condition Expressions
Conditions (like `eq`, `lt`, `gt`) are implemented as wrappers around the `sql` template literal. These functions take arguments, apply `bindIfParam` to ensure they are properly encoded, and return a `SQL` object that can be further composed in a `where()` clause.
The `bindIfParam` function acts as a guard that prevents double-wrapping of parameters if they are already identified as an `SQLWrapper` or a `Column`.
```typescript
// The implementation of eq is a straightforward wrapper
export const eq: BinaryOperator = (left: SQLWrapper, right: unknown): SQL => {
return sql`${left} = ${bindIfParam(right, left)}`;
};
```
Sources: [drizzle-orm/src/sql/expressions/conditions.ts:17-64](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/expressions/conditions.ts#L17-L64)
## Structural Design Trade-offs
The SQL Expression architecture prioritizes flexibility and cross-dialect portability over absolute runtime performance. The decision to use a recursive `SQL` structure introduces minor overhead due to tree traversal during query serialization, but it buys significant architectural benefits.
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Recursive Tree Structure** | Enables complex, nested query fragments. | Traversing nodes during compilation adds runtime overhead. |
| **Deferred Compilation** | Allows dialect-specific optimizations at the last minute. | Query compilation is strictly synchronous or asynchronous at the point of execution. |
| **Type-safe Encoding** | Prevents incorrect parameter types from reaching the database driver. | Requires maintaining `DriverValueEncoder` for every supported type. |
Sources: [drizzle-orm/src/sql/sql.ts:103-372](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sql/sql.ts#L103-L372)
## Related
- [[Query Builder Core]]
---
## Views and Aggregates
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/views-and-aggregates
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/snapshotsDiffer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts)
- [drizzle-kit/src/serializer/pgSerializer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts)
Database views act as virtual tables; in a migration-based workflow, Drizzle Kit manages their definitions and structural configuration to ensure that changes to underlying SQL can be accurately detected and rendered into actionable migration files. The system handles the view definition, schema, and specific configuration options—such as `MATERIALIZED` status or `WITH` parameters—to bridge the gap between a database's current state (introspected) and the developer's desired state.
The role of this component is to manage schema drift by capturing the view's definition string and metadata, performing a comparison against the existing snapshot, and generating the necessary `DROP`/`CREATE` or `ALTER` statements. The design relies on serialization logic that converts database-native view configuration into a JSON format suitable for diffing, followed by a reconciliation pass that resolves potential name or schema collisions during migration.
This subsystem integrates with the logic found in `drizzle-kit/src/snapshotsDiffer.ts`, which orchestrates the diffing pipeline. When views change, the differ uses resolver functions to identify if a view was renamed, moved between schemas, or fundamentally redefined. Because views often depend on underlying table structures, this component ensures that migration statements are ordered correctly—prioritizing view modifications after table structural changes are stabilized—to prevent invalid SQL execution.
## View Serialization Mechanism
The serialization of views is handled during introspection to generate the internal snapshot representation. The mechanism processes views and materialized views by invoking `getViewConfig` or `getMaterializedViewConfig` from `drizzle-orm/pg-core`. It transforms database-native configurations into a consistent structure that tracks the definition string, schema, and materialization options.
For each view, the serializer performs the following steps:
1. **Identity Resolution:** Extracts the view name and schema to form a unique key for the `resultViews` collection.
2. **Field Processing:** Iterates over the selected fields of the view to reconstruct the structure, mirroring the logic used for standard tables. This allows the system to track view structure similarly to base tables.
3. **Metadata Capture:** Stores the SQL query definition, materialization status, and properties (e.g., `WITH`, `TABLESPACE`, `USING`) in the final schema snapshot.
Sources: [drizzle-kit/src/serializer/pgSerializer.ts:L706-L870](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L706-L870)
## Diffing Pipeline for Views
The diffing engine, specifically `applyPgSnapshotsDiff`, manages view changes by comparing the view record from the source snapshot to the target JSON state. The mechanism follows an order-of-operations flow that avoids race conditions:
1. **State Reconstruction:** The system clones the existing schema snapshot and patches it with view renames or moves to align the keys with the new snapshot state.
2. **Change Detection:** `applyJsonDiff` compares the patched snapshot and the target state to isolate modified attributes.
3. **Statement Generation:** The engine iterates through the collection of views. If `alteredDefinition` is flagged, the view is dropped and recreated. If only metadata (e.g., `tablespace` or `WITH` options) changes, the engine queues targeted `alter` statements using specific `prepare` functions.
This separation ensures that structural changes to view content (which cannot be easily altered in SQL) trigger a destructive/re-constructive cycle, while property updates are handled via non-destructive SQL commands.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:L1136-L1176](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L1136-L1176), [drizzle-kit/src/snapshotsDiffer.ts:L1844-L1946](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L1844-L1946)
## Control Flow for View Alterations
The logic for deciding how to alter a view is governed by guards based on the view snapshot state. The following flowchart illustrates the decision hierarchy implemented in the differ:
```mermaid
flowchart TD
A["Begin Altered View"] --> B{"Is definition changed?
Action != push"}
B -->|Yes| C[Drop View]
C --> D[Create View]
B -->|No| E{"Altered Options/Metadata"}
E -->|Add Option| F[preparePgAlterViewAddWithOptionJson]
E -->|Alter Tablespace| G[preparePgAlterViewAlterTablespaceJson]
E -->|Alter Using| H[preparePgAlterViewAlterUsingJson]
```
The "load-bearing line" here is `if (alteredView.alteredExisting || (alteredView.alteredDefinition && action !== 'push'))`, which acts as an invariant guard. This ensures that any modification to the core SQL definition of a view forces a drop/recreate, preventing the engine from attempting invalid `ALTER VIEW` operations on the definition itself.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:L1851-L1868](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L1851-L1868)
## View and Schema Migration Invariants
> [!CAUTION]
> During view migrations, the engine assumes that views exist in the schema provided in the snapshot. If a view is renamed or moved between schemas, the engine MUST resolve these via `renamedViews` and `movedViews` maps before running `applyJsonDiff`. Failure to populate these maps results in the system treating the renamed view as a "Delete" and "Create" operation pair, which risks data loss if the view were a `MATERIALIZED` view containing data.
The system uses a key-based lookup where the dictionary key is `${schema}.${viewName}`. This is the primary index for lookup. Invariants are maintained by strictly updating the string keys during the patching phase.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:L1148-L1174](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L1148-L1174)
## Configuration Fields for View Changes
The logic uses Zod schemas to define the permissible alterations for views. The structure is used to validate and parse the output of the diff process:
| Field | Type | Purpose |
| :--- | :--- | :--- |
| `name` | `string` | The current identifier of the view |
| `alteredDefinition` | `object` | Holds `__old` and `__new` definition strings |
| `alteredSchema` | `object` | Schema migration tracking |
| `addedWithOption` | `object` | Track new `WITH` options |
| `deletedWithOption` | `object` | Track removed `WITH` options |
| `alteredTablespace` | `object` | Track change in view storage location |
| `alteredUsing` | `object` | Track change in the `USING` clause |
Sources: [drizzle-kit/src/snapshotsDiffer.ts:L340-L373](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L340-L373)
## Statement Generation Pipeline
The generation of SQL statements is the final stage of the view migration process. The `jsonStatements` array is ordered to ensure that drop statements for views precede any table modifications, and creation statements follow them.
1. **Drop Statements:** `jsonStatements.push(...dropViews)` executes after constraints are processed to ensure dependent objects are cleaned up.
2. **Rename/Alter:** `renameViews` and `alterViews` are injected before the creation of new objects to maintain object existence during the transaction.
3. **Creation:** `jsonStatements.push(...createViews)` happens at the end of the batch to ensure all underlying schema requirements are satisfied.
This sequence is critical because views are often dependent on tables. Recreating tables while views are active without dropping the views first would violate database schema constraints.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:L1969-L2008](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L1969-L2008)
## Related
- [[Select Queries]]
---
## Database Drivers
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/database-drivers
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/cli/connections.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/connections.ts)
- [drizzle-orm/src/neon-http/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-http/session.ts)
- [drizzle-orm/src/neon-serverless/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-serverless/session.ts)
- [drizzle-orm/src/netlify-db/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/netlify-db/session.ts)
- [drizzle-orm/src/netlify-db/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/netlify-db/driver.ts)
- [drizzle-orm/src/xata-http/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/xata-http/driver.ts)
- [drizzle-orm/src/d1/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/d1/driver.ts)
- [drizzle-orm/src/xata-http/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/xata-http/session.ts)
- [drizzle-orm/src/d1/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/d1/session.ts)
- [drizzle-orm/src/tidb-serverless/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/tidb-serverless/session.ts)
- [drizzle-orm/src/singlestore-proxy/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-proxy/driver.ts)
- [drizzle-orm/src/sqlite-proxy/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-proxy/driver.ts)
- [drizzle-orm/src/mysql-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-proxy/session.ts)
- [drizzle-orm/src/pg-proxy/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-proxy/driver.ts)
- [drizzle-orm/src/mysql-proxy/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-proxy/driver.ts)
- [drizzle-orm/src/pg-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-proxy/session.ts)
- [drizzle-orm/src/planetscale-serverless/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/planetscale-serverless/session.ts)
- [drizzle-orm/src/singlestore/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts)
- [drizzle-orm/src/neon-http/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-http/driver.ts)
- [drizzle-orm/src/singlestore-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-proxy/session.ts)
- [drizzle-orm/src/neon-serverless/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-serverless/driver.ts)
- [drizzle-orm/src/libsql/http/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/http/index.ts)
- [drizzle-orm/src/tidb-serverless/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/tidb-serverless/driver.ts)
- [drizzle-orm/src/planetscale-serverless/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/planetscale-serverless/driver.ts)
- [drizzle-orm/src/libsql/driver.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/driver.ts)
- [drizzle-orm/src/neon-serverless/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-serverless/index.ts)
- [drizzle-orm/src/neon-http/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-http/index.ts)
- [drizzle-orm/src/libsql/driver-core.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/driver-core.ts)
- [drizzle-orm/src/planetscale-serverless/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/planetscale-serverless/index.ts)
- [drizzle-orm/src/libsql/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/index.ts)
Database drivers act as the fundamental bridge between Drizzle ORM and specific database engines or communication protocols. By abstracting the complexities of low-level client interactions, these drivers enable a consistent query syntax while allowing the ORM to execute SQL tailored to the target system. They handle key tasks like parameter mapping, row result transformation, connection management, and transaction handling, ensuring that developers can switch between various environments (e.g., local Node.js environments vs. serverless edge runtimes) with minimal code changes.
At their core, Drizzle drivers follow a uniform architecture consisting of a driver class, a session, and specialized prepared queries. The driver is responsible for initializing the underlying database client (like `Pool` in `pg` or `Client` in `mysql2`) and exposing a `createSession` method. The session then acts as the execution factory, generating `PreparedQuery` instances that are capable of logging, caching, and executing SQL against the connection. This hierarchy allows Drizzle to support a wide range of platforms, from direct protocol drivers to HTTP proxies for edge environments.
The design emphasizes platform independence. Because different engines (PostgreSQL, MySQL, SQLite) and their corresponding drivers (Node-postgres, PlanetScale, D1, LibSQL) have varying ways of handling types, connections, and statement execution, the driver subsystem isolates these vendor-specific idiosyncrasies behind interface implementations that provide consistent SQL generation and execution capabilities. This standardization enables Drizzle to maintain a robust, type-safe API across a vast ecosystem of databases.
## Gel Drivers
Gel drivers, formerly EdgeDB, are managed through the CLI connection infrastructure and provide a specialized session wrapper that adapts to Gel's unique query execution model. The `prepareGelDB` function in `drizzle-kit/src/cli/connections.ts` facilitates this by initializing a Gel client and exposing proxy methods for executing SQL.
The `proxy` method within this driver performs a switch-based dispatch depending on the requested `mode`:
```typescript
// drizzle-kit/src/cli/connections.ts:577-593
const proxy: Proxy = async (params: ProxyParams) => {
const { method, mode, params: sqlParams, sql, typings } = params;
let result: any[];
switch (mode) {
case 'array':
result = sqlParams?.length
? await client.withSQLRowMode('array').querySQL(sql, sqlParams)
: await client.withSQLRowMode('array').querySQL(sql);
break;
case 'object':
result = sqlParams?.length ? await client.querySQL(sql, sqlParams) : await client.querySQL(sql);
break;
}
return result;
};
```
Sources: [drizzle-kit/src/cli/connections.ts:577-593](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/connections.ts#L577-L593)
> [!NOTE]
> Gel drivers utilize `client.withSQLRowMode('array')` to toggle the return structure dynamically based on the input proxy configuration. This ensures that the ORM can receive raw data or typed objects consistently depending on whether it is running schema-aware queries or raw proxy operations.
Sources: [drizzle-kit/src/cli/connections.ts:584](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/connections.ts#L584)
## MySQL Drivers
MySQL drivers in Drizzle support diverse environments, including standard Node.js clients (`mysql2`) and serverless providers (`PlanetScale`). These drivers share specialized session management logic that handles prepared statements and transaction configuration.
In the PlanetScale serverless driver, the session is responsible for bridging Drizzle's MySQL core with the PlanetScale `Client` API. Key aspects include result mapping and the injection of custom type parsers for performance and accuracy in date handling.
```typescript
// drizzle-orm/src/planetscale-serverless/session.ts:168-172
async query(query: string, params: unknown[]): Promise {
this.logger.logQuery(query, params);
return await this.client.execute(query, params, { as: 'array' });
}
```
Sources: [drizzle-orm/src/planetscale-serverless/session.ts:168-172](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/planetscale-serverless/session.ts#L168-L172)
> [!TIP]
> MySQL proxies, defined in `drizzle-orm/src/mysql-proxy/driver.ts`, follow a decoupled architecture. Instead of managing direct connections, they accept a `RemoteCallback`. This allows the driver to function in environments where the database connection logic is handled externally (e.g., a serverless function communicating with a database via an HTTP API).
Sources: [drizzle-orm/src/mysql-proxy/driver.ts:20-24](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-proxy/driver.ts#L20-L24)
## PostgreSQL Drivers
PostgreSQL drivers are characterized by their interaction with core SQL execution patterns. The `NeonHttpDriver` and `NetlifyDbDriver` exemplify this, utilizing specialized HTTP or WebSocket-based sessions. Because HTTP-based drivers (like Neon's) often lack stateful transaction support (e.g., `BEGIN`/`COMMIT` over multiple HTTP requests), Drizzle enforces strict error throwing or alternative behavior.
The `NeonHttpDatabase` provides a `$withAuth` mechanism, which demonstrates advanced driver customization via Proxy usage:
```typescript
// drizzle-orm/src/neon-http/driver.ts:59-77
return new Proxy(target, {
get(target, p) {
const element = target[p as keyof typeof p];
if (typeof element !== 'function' && (typeof element !== 'object' || element === null)) return element;
if (deep) return wrap(element, token, cb);
if (p === 'query') return wrap(element, token, cb, true);
return new Proxy(element as any, {
apply(target, thisArg, argArray) {
const res = target.call(thisArg, ...argArray);
if (typeof res === 'object' && res !== null && 'setToken' in res && typeof res.setToken === 'function') {
res.setToken(token);
}
return cb(target, p, res);
},
});
},
});
```
Sources: [drizzle-orm/src/neon-http/driver.ts:59-77](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-http/driver.ts#L59-L77)
## Serverless Drivers
The "Serverless" nomenclature refers to drivers explicitly optimized for non-persistent runtimes (like Cloudflare Workers or Edge Vercel functions). A key challenge here is handling connection pools and WebSocket state, which is solved by injecting the global environment into the driver configuration.
For instance, the `NetlifyDbSession` includes an `ensureWebSocket` utility, preventing redundant environment checks:
```typescript
// drizzle-orm/src/netlify-db/session.ts:39-45
function ensureWebSocket(): void {
if (neonConfig.webSocketConstructor) return;
if (typeof WebSocket !== 'undefined') {
neonConfig.webSocketConstructor = WebSocket as any;
}
}
```
Sources: [drizzle-orm/src/netlify-db/session.ts:39-45](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/netlify-db/session.ts#L39-L45)
> [!CAUTION]
> In serverless environments, nested transactions are often unsupported or require `SAVEPOINT` implementation. The `NetlifyDbTransaction` class explicitly implements this for the WebSocket-based session to provide transactionality where standard BEGIN/COMMIT flow might fail or not be applicable.
Sources: [drizzle-orm/src/netlify-db/session.ts:273-292](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/netlify-db/session.ts#L273-L292)
## SQLite Drivers
SQLite drivers (like `d1`, `better-sqlite3`, and `libsql`) are handled via the `BaseSQLiteDatabase` class. A distinct feature of SQLite drivers is their support for `batch` operations, which is essential for performance, especially when dealing with remote D1 or LibSQL connections where each individual round-trip is expensive.
The `batch` mechanism is standardized across these drivers:
```mermaid
flowchart TD
A["User Call: db.batch(queries)"] --> B["Session.batch(queries)"]
B --> C["_prepare() each query"]
C --> D["Gather into builtQueries array"]
D --> E["Execute single request"]
E --> F["Map results back to query definitions"]
```
Sources: [drizzle-orm/src/d1/session.ts:77-97](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/d1/session.ts#L77-L97)
### SQLite Driver Comparison
| Driver | Connection Basis | Transaction Model | Support |
| :--- | :--- | :--- | :--- |
| D1 | HTTP/Binding | Savepoints (manual) | Yes |
| Better-SQLite3 | File/Local | Native | Yes |
| LibSQL | HTTP/WASM | Native | Yes |
Sources: [drizzle-orm/src/d1/driver.ts:24-37](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/d1/driver.ts#L24-L37), [drizzle-orm/src/libsql/driver-core.ts:17-30](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/driver-core.ts#L17-L30)
> [!IMPORTANT]
> The SQLite drivers in Drizzle differentiate between `async` and synchronous dialects. Drivers like `d1` and `libsql` use the `SQLiteAsyncDialect` to ensure that IO operations are awaited correctly, as these drivers rely on non-blocking HTTP or WASM-based communication interfaces.
Sources: [drizzle-orm/src/d1/driver.ts:48](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/d1/driver.ts#L48), [drizzle-orm/src/libsql/driver-core.ts:38](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/driver-core.ts#L38)
## Related
- [[Schema Declarations]]
---
## Kit Overview
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/kit-overview
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/snapshotsDiffer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts)
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-kit/src/cli/commands/migrate.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/migrate.ts)
The "Kit Overview" encompasses the core logic responsible for database introspection, snapshot management, and the generation of migration SQL. Its primary role is to bridge the gap between application-defined schemas and the actual state of a target database, facilitating the transition between declarative state definitions (snapshots) and imperative schema changes (migrations).
The system addresses the fundamental complexity of evolving database schemas, such as handling renames, moved objects, and attribute modifications that cannot be inferred from simple object comparisons alone. It leverages a "snapshot differ" architecture, where previous and current schema states are compared, and resolutions for ambiguities (like table or column renames) are provided via CLI prompts or automated resolvers.
At its architecture's center is a robust transformation pipeline: it converts schema differences into intermediate JSON-based statements, which are then serialized into vendor-specific SQL. This decoupling ensures that the logic for identifying changes is shared across databases (PostgreSQL, MySQL, SQLite, SingleStore), while the final SQL emission remains isolated in specific generators.
## Core Schema Differencing Mechanism
The core engine for detecting schema changes resides in `snapshotsDiffer.ts`. It performs a multi-stage comparison between `json1` (previous snapshot) and `json2` (current schema). The differ functions (`diffSchemasOrTables`, `diffColumns`, `diffPolicies`) compute the delta by identifying additions, deletions, and potential renames.
The system utilizes `copy()` to create working snapshots (`schemasPatchedSnap1`, `tablesPatchedSnap1`) that are progressively updated as renames are resolved. This allows the differ to operate on "patched" states where, for instance, a renamed table is treated as a single entity rather than a deletion and an addition.
> [!IMPORTANT]
> The differ does not assume names are immutable. It explicitly delegates rename/move resolution to injected resolver functions (e.g., `tablesResolver`), which can trigger user interaction or heuristics to map "deleted" objects to "created" objects.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:603-1234](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L603-L1234)
## Snapshot Patching and State Normalization
To accurately reflect changes in the database, the differ must maintain consistency across nested object structures. When a schema or table is moved or renamed, the system performs a sequence of pointer updates:
1. **Schema Normalization:** `schemaChangeFor` maps the current object to its new schema location based on rename resolution.
2. **Enum and Sequence Tracking:** `enumsResolver` and `sequencesResolver` handle moving/renaming enums and sequences, which are then propagated through to dependent columns using `columnTypesChangeMap`.
3. **Intermediate State:** As these resolutions are applied, `mapEntries` and `mapValues` are used to reconstruct the `tablesPatchedSnap1` state, which serves as the "corrected" baseline for the final `applyJsonDiff` call.
This state-patching flow ensures that by the time `applyJsonDiff` is invoked, the "old" snapshot and "new" snapshot are aligned by name and schema, leaving only additive, subtractive, or attribute-level changes to be computed.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:614-709](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L614-L709)
## Statement Generation Pipeline
Once the final diff is calculated, the Kit converts these deltas into a unified format (`JsonStatement[]`). The pipeline follows a strict execution order to prevent dependency violations (e.g., dropping a table that is still referenced by a foreign key):
1. **Drop Order:** Policies, indices, and check constraints are dropped first, followed by tables and columns.
2. **Alteration:** Structural changes (e.g., `alter_table_alter_column_set_type`) are generated.
3. **Creation:** Tables and columns are created; foreign keys and indices are then applied.
The system uses specific `prepare*` functions (e.g., `preparePgCreateTableJson`, `prepareAddColumns`) which return these `JsonStatement` objects.
```typescript
// Example: Simplified dependency-aware statement accumulation
jsonStatements.push(...jsonDropIndexesForAllAlteredTables);
jsonStatements.push(...jsonDeletedCompositePKs);
jsonStatements.push(...jsonTableAlternations);
jsonStatements.push(...jsonAddedCompositePKs);
jsonStatements.push(...jsonAddColumnsStatemets);
```
Sources: [drizzle-kit/src/snapshotsDiffer.ts:1973-2018](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L1973-L2018)
## PostgreSQL Introspection
Introspection, specifically for PostgreSQL, transforms database metadata into Drizzle-compatible TypeScript code. It handles complex PostgreSQL features such as:
- **Identity Columns:** Handled by `generateIdentityParams`, which differentiates between `generatedAlwaysAsIdentity` and `generatedByDefaultAsIdentity`.
- **Custom Types/Enums:** Uses `schema.enums` and `enumTypes` set to identify which types require `pgEnum` declarations.
- **Expression Defaults:** `mapColumnDefault` and `mapDefault` determine whether a default value is a literal or an SQL expression requiring `sql` template tags.
The system uses `withCasing` to translate database column names to their requested casing (camel vs. preserve).
> [!CAUTION]
> If a type fails to parse (e.g., an unknown geometry type), the generator will emit an `unknown()` call as a fallback to avoid crashing, which allows the developer to manually inspect the generated code.
Sources: [drizzle-kit/src/introspect-pg.ts:277-302](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L277-L302), [drizzle-kit/src/introspect-pg.ts:1064-1101](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L1064-L1101)
## Architecture Reference
The following diagram illustrates the interaction between snapshots, resolvers, and final SQL generation.
```mermaid
flowchart TD
S1["Previous Snapshot"] --> Differ
S2["Current Snapshot"] --> Differ
Differ --> |"Resolvers
(Tables, Enums, Views)"| R[Resolvers]
R --> |"Updates"| Differ
Differ --> |"JSON Statements"| Gen["SQL Generator"]
Gen --> |"Final Migration"| SQL["SQL Output"]
```
Sources: [drizzle-kit/src/snapshotsDiffer.ts:559-602](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L559-L602)
## Design Trade-offs Table
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **JSON Statement Intermediate Representation** | Allows cross-database logic for identifying changes; facilitates easy debugging and testing of diffs. | Adds an abstraction layer; requires serialization logic for every new database engine. |
| **Patcher-based Snapshot Alignment** | Simplifies complex diffs (renames/moves) by aligning state before the final comparison. | Increases memory usage; requires deep copies and iterative object mutations. |
| **Dependency-ordered Migration Pipeline** | Prevents runtime SQL errors by ensuring drops occur before creations/alterations. | Complex logic to maintain correct ordering as new constraint types are added to the system. |
Sources: [drizzle-kit/src/snapshotsDiffer.ts:1948-2022](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L1948-L2022)
## Related
- [[CLI Commands]]
- [[Schema Serialization]]
---
## Schema Introspection
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/schema-introspection
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/serializer/pgSerializer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts)
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-kit/src/serializer/gelSerializer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/gelSerializer.ts)
- [drizzle-kit/src/introspect-gel.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-gel.ts)
## Introduction
Schema Introspection is the process of querying a live database to reconstruct its structure (tables, columns, constraints, indexes, and relations) into an internal representation. This subsystem acts as the bridge between raw database metadata and the ORM's type-safe schema definitions. By systematically querying the system catalogs (e.g., `pg_catalog`, `information_schema`), the introspector captures the state of the database, enabling features like schema comparison, migration generation, and the "push" workflow.
The core design centers around two primary phases: data extraction and serialization. The extraction phase executes low-level SQL to pull metadata, while the serialization phase converts this raw result set into a normalized, type-agnostic JSON-like object. This architecture decouples the database-specific SQL dialect from the business logic required to generate code or compare schemas.
The subsystem manages complexity by handling heterogeneous database constructs, including materialized views, foreign keys, identity columns, and custom policy definitions. It addresses the common pain point of drift between code-first schema definitions and the actual database reality, ensuring that developers can maintain accurate, up-to-date type definitions even when the underlying schema evolves independently.
## Data Extraction Mechanism
The extraction process is initiated through `fromDatabase` functions, which iterate through tables, views, and materialized views based on provided filters. The logic heavily relies on joining `pg_catalog` tables to resolve object dependencies such as schema names, serial sequences, and enum definitions.
```mermaid
flowchart TD
A["fromDatabase(db, filters)"] --> B["Query pg_catalog.pg_class"]
B --> C["Fetch Sequences"]
C --> D["Fetch Enums"]
D --> E["Parallel Table Processing"]
E --> F["getColumnsInfoQuery"]
E --> G["Fetch Constraints & FKs"]
E --> H["Fetch Indexes"]
```
Sources: [drizzle-kit/src/serializer/pgSerializer.ts:968-1916](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L968-L1916)
The mechanism uses a `progressCallback` to monitor the long-running execution of fetching columns, indexes, and foreign keys. A critical guard exists inside `fromDatabase` where `tablesFilter` is applied to avoid redundant processing, preventing unrequested schemas from polluting the snapshot.
## Normalization and Serialization
Serialization transforms raw database types into consistent internal representations used by the Drizzle Kit. For Postgres, `generatePgSnapshot` performs the conversion from internal Drizzle-ORM core table configurations into structured schema snapshots.
> [!IMPORTANT]
> The serialization layer uses `getColumnCasing` to map column names based on user configuration, ensuring that even if the database uses `snake_case`, the generated Drizzle code can be configured for `camelCase`.
The `buildArrayString` helper is a core utility here, converting JavaScript arrays into the PostgreSQL `'{...}'` format, handling edge cases for dates and JSON values to ensure type parity.
Sources: [drizzle-kit/src/serializer/pgSerializer.ts:101-112](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L101-L112), [drizzle-kit/src/serializer/pgSerializer.ts:72-99](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L72-L99)
## TypeScript Generation
The `schemaToTypeScript` function transforms the serialized schema snapshot into a valid string of TypeScript code. It computes the necessary imports by scanning table columns, foreign keys, and indexes.
1. **Imports Resolution:** It iterates through all tables to identify required SQL column types (`serial`, `varchar`, etc.) and constraints (`unique`, `check`).
2. **Declaration Generation:** It processes enums, sequences, and roles as top-level constants.
3. **Table Construction:** It constructs the `pgTable` definition block, appending chainable methods like `.primaryKey()` or `.notNull()` based on the properties found in the snapshot.
Sources: [drizzle-kit/src/introspect-pg.ts:309-636](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L309-L636)
## Identity Column Handling
Identity columns require special treatment to capture sequence parameters. The `generateIdentityParams` function creates the appropriate chainable call for either `.generatedAlwaysAsIdentity()` or `.generatedByDefaultAsIdentity()`.
| Property | Default Source | Behavior |
| :--- | :--- | :--- |
| `increment` | `identity.sequenceOptions.increment` | Defaults to '1' if undefined |
| `minValue` | `identity.sequenceOptions.minValue` | Logic branching based on sign |
| `maxValue` | `identity.sequenceOptions.maxValue` | Derived from column type if needed |
Sources: [drizzle-kit/src/introspect-pg.ts:277-302](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L277-L302), [drizzle-kit/src/serializer/pgSerializer.ts:173-180](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L173-L180)
## Handling Cyclic Dependencies
In schemas with circular foreign keys, Drizzle Kit must inject types to avoid circular reference errors during compilation. `isCyclic` and `isSelf` perform the check by comparing `tableFrom` and `tableTo`. If a cyclic reference is detected, the introspector identifies necessary import adjustments.
> [!WARNING]
> Cyclic dependencies, if not detected correctly, can break the generated TypeScript file due to temporal dead zones or circular module imports. The introspector relies on the `relations` set to detect if a relationship is already registered in the other direction.
Sources: [drizzle-kit/src/introspect-pg.ts:638-646](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L638-L646), [drizzle-kit/src/introspect-pg.ts:333-335](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L333-L335)
## Integration Example
This example demonstrates the workflow for manual schema inspection using the provided utilities.
```typescript
// Usage: Inspecting a Postgres database
import { fromDatabase } from 'drizzle-kit/src/serializer/pgSerializer';
import { schemaToTypeScript } from 'drizzle-kit/src/introspect-pg';
// 1. Fetch current schema from DB
const schema = await fromDatabase(dbConnection, () => true, ['public']);
// 2. Convert to TypeScript code
const { file } = schemaToTypeScript(schema, 'camel');
console.log(file);
```
Sources: [drizzle-kit/src/serializer/pgSerializer.ts:968-1937](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L968-L1937), [drizzle-kit/src/introspect-pg.ts:309-636](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L309-L636)
## Related
- [[Kit Overview]]
---
## Schema Serialization
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/schema-serialization
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/snapshotsDiffer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts)
- [drizzle-kit/src/serializer/pgSchema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts)
- [drizzle-kit/src/serializer/gelSchema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/gelSchema.ts)
- [drizzle-kit/src/serializer/pgSerializer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts)
Schema Serialization is the architectural backbone of Drizzle Kit, providing a deterministic mechanism to capture, persist, and compare database schema states. Its primary purpose is to enable "Database Diffing"—the ability to calculate exactly what DDL statements are required to transform one database schema state into another.
By serializing database schemas into rigid JSON structures, the system abstracts away the complexities of disparate database engines (PostgreSQL, MySQL, SQLite, etc.) into a unified, versioned format. This process solves the fundamental problem of maintaining consistency between code-defined schemas and actual database states, ensuring that migrations are generated predictably and safely.
The subsystem functions as a pipeline: it first introspects the live database (or the ORM schema configuration), converts that into a versioned internal schema format defined by Zod types, and finally, during migration generation, applies a diffing algorithm. This diffing step compares two serialized states, resolves renames, and produces an ordered sequence of migration statements that align the destination with the source, all while preserving data integrity and handling database-specific constraints.
## The Schema Lifecycle
The schema serialization process follows a multi-stage lifecycle designed to ensure data consistency during transitions. Serialization begins with the extraction of definitions—tables, columns, indexes, and constraints—from the database configuration. These raw definitions are transformed into internal objects, which are then mapped or squashed for persistence.
The "squashing" process is critical; it converts complex, nested database objects into flattened, serialized string formats (for instance, encoding index definitions or composite primary keys into specific string delimiters). This normalization allows for simpler diffing algorithms, as the system can compare strings instead of deeply recursive object trees.
```mermaid
flowchart TD
A["Source Schema Definition"] --> B["Introspection / Extraction"]
B --> C["Versioned Internal Schema"]
C --> D["Squashed Format"]
D --> E["JSON File"]
```
Sources: [drizzle-kit/src/serializer/pgSchema.ts:758-868](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts#L758-L868), [drizzle-kit/src/serializer/gelSchema.ts:505-615](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/gelSchema.ts#L505-L615)
## Versioned Schema Structures
To accommodate evolution, the system employs versioned schema definitions. Each major version defines the shape of the stored JSON using Zod. This allows Drizzle Kit to read older definitions while evolving the internal model without breaking compatibility with existing migration folders.
| Name | Target Dialect | Key Characteristics |
| :--- | :--- | :--- |
| `pgSchema` | PostgreSQL | Top-level schema object containing tables, enums, schemas, and views |
| `gelSchema` | Gel | Top-level schema object containing tables, enums, and sequences |
Sources: [drizzle-kit/src/serializer/pgSchema.ts:437-509](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts#L437-L509), [drizzle-kit/src/serializer/gelSchema.ts:272-272](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/gelSchema.ts#L272-L272)
## The Squashing Mechanism
The serialization engine utilizes specific helper methods defined inside schema definitions to handle flattening operations. These helper functions contain the "load-bearing" methods for the system, converting complex objects (like indexes or foreign keys) into flattened strings, which are the data-transfer format between the serializable JSON and the diffing logic.
For instance, an index is converted into a string delimited by semicolons and double hyphens (e.g., parsing the index properties and columns to join them with `,,` and `--`). This flattened string is deterministic, allowing the `snapshotsDiffer` module to perform direct string equality checks to determine if an object has changed.
> [!IMPORTANT]
> The squash format is engine-specific and highly sensitive to delimiters. Adding or changing a delimiter in these serialization helpers without updating the corresponding parser and unsquashing logic will result in corrupted states and failed migrations.
Sources: [drizzle-kit/src/serializer/pgSchema.ts:551-756](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts#L551-L756), [drizzle-kit/src/serializer/gelSchema.ts:298-503](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/gelSchema.ts#L298-L503)
## Schema Differential Logic
The `snapshotsDiffer.ts` component is the engine room for calculating migration statements. It takes two serializations (the "old" and "new") and computes a list of required JSON statements. The process is inherently ordered: it first resolves global schema changes (like adding or renaming a schema), then table additions, column updates, and finally, dependent constraints like indexes and foreign keys.
### Call-chain Execution for `applyPgSnapshotsDiff`
1. `diffSchemasOrTables()`: Calculates the basic set difference between the old and new states.
2. `schemasResolver()`/`enumsResolver()`: User-provided resolution hooks are called to handle renaming/moving logic, which might otherwise be ambiguous.
3. `mapEntries()`: Updates the cached state to align names/schemas according to the resolved renames.
4. `diffColumns()`/`diffPolicies()`: Calculates granular diffs for columns and policies.
5. `prepare...()` functions: Finally, the system dispatches the differences to `jsonStatements.ts` generators to produce the final `JsonStatement[]` list.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:603-1245](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L603-L1245)
## Handling Ambiguity: The Resolver Pattern
When a schema structure changes (e.g., a table is renamed), simply observing the "added" and "deleted" sets is insufficient, as it leads to "drop-and-create" rather than a "rename." The interfaces, such as `ResolverInput` and `ResolverOutput`, exist to allow external logic to clarify the intent.
The system requires these inputs to resolve "renames" vs "newly created/deleted" entities.
| Interface | Purpose |
| :--- | :--- |
| `ResolverInput` | Lists raw added and deleted items found during diffing. |
| `ResolverOutput` | Provides the mapping for renamed entities to prevent data loss. |
| `ResolverOutputWithMoved` | Tracks schema-level movements of tables or enums. |
Sources: [drizzle-kit/src/snapshotsDiffer.ts:421-505](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L421-L505)
## Filtering and Optimization
Not all diffs are strictly necessary. The engine includes a filtering pipeline at the end of the `apply` functions to remove redundant or conflicting SQL operations. For instance, if an operation drops a `NOT NULL` constraint and another operation modifies the same column's identity, these are analyzed for logical collisions.
```typescript
// Example of a filtering guard in snapshotsDiffer.ts
const filteredJsonStatements = jsonStatements.filter((st) => {
if (st.type === 'alter_table_alter_column_drop_notnull') {
if (jsonStatements.find((it) => it.type === 'alter_table_alter_column_drop_identity' ...)) {
return false;
}
}
return true;
});
```
Sources: [drizzle-kit/src/snapshotsDiffer.ts:2025-2051](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L2025-L2051)
> [!WARNING]
> Statement order is non-negotiable. Indexes must often be dropped before altering columns and recreated afterward. The `apply` methods manually append these statement arrays in a precise, hard-coded order at the end of the migration generation phase to respect database dependency constraints.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:1985-2022](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L1985-L2022)
## Related
- [[Kit Overview]]
---
## Migration Differ
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/migration-differ
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/snapshotsDiffer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts)
- [drizzle-kit/src/jsonDiffer.js](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/jsonDiffer.js)
- [drizzle-kit/src/cli/commands/libSqlPushUtils.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/libSqlPushUtils.ts)
- [drizzle-kit/src/jsonStatements.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/jsonStatements.ts)
Migration Differ is a critical subsystem within `drizzle-kit` responsible for calculating the transition between two database schema snapshots. Its primary role is to ingest historical and current schema representations, perform a deep structural diff, and produce a deterministic sequence of DDL (Data Definition Language) operations required to synchronize a database instance with its defined schema code.
By abstracting the complexity of detecting additions, deletions, renames, and modifications across tables, columns, indexes, and constraints, the Migration Differ serves as the bridge between declarative schema definitions and imperative database migrations. It ensures that schema drift is captured accurately, supporting both standard migration file generation and "push" operations for direct schema synchronization.
Architecturally, the subsystem follows a multi-stage pipeline: ingestion of JSON-serialized schemas, resolution of name/schema changes via external input (e.g., user-provided renaming hints), execution of diff algorithms, and finally, transformation of those diffs into actionable JSON statement objects that generators (SQL generators) translate into executable SQL commands.
## Schema Diffing Logic
The core logic of schema comparison is handled via `snapshotsDiffer.ts` and `jsonDiffer.js`. When comparing two schemas, the differ does not merely perform a shallow key-value comparison; it reconciles differences by accounting for potential renames of tables, columns, and other database objects.
The `snapshotsDiffer.ts` file orchestrates this by invoking specialized resolvers for schemas, tables, and columns. The mechanism works by first identifying "raw" diffs using the `json-diff` library via the `diffSchemasOrTables` and `diffColumns` wrappers, and then patching the intermediate state (`schemasPatchedSnap1`) to ensure that rename resolutions are applied before the final diff is serialized into statement objects.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:15,603](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L15-L603)
## Data Flow: From JSON to SQL Statements
The transformation pipeline converts structural differences into an ordered sequence of `JsonStatement` objects. The flow is as follows:
1. **Resolver Phase:** The system uses callbacks (`tablesResolver`, `columnsResolver`, etc.) to determine if differences represent new entities, deleted entities, or renames.
2. **Patching Phase:** The `snapshotsDiffer` maps the "previous" snapshot to the "new" snapshot's state, applying renames so that the subsequent diffing logic treats renames as continuity rather than a deletion/addition pair.
3. **Diffing Phase:** `applyJsonDiff` iterates through the patched objects to produce a granular diff result.
4. **Generation Phase:** The result is fed into various `prepare...Json` functions (e.g., `preparePgCreateTableJson`, `prepareRenameColumns`) which generate the specific `JsonStatement` primitives.
5. **SQL Emission:** Finally, the `fromJson` function in the SQL generator consumes these statements to produce the final SQL string.
```mermaid
flowchart TD
S1["Previous Schema Snapshot"] --> P["Patching (Rename/Move Resolutions)"]
S2["Current Schema Snapshot"] --> D["diffColumns / diffSchemasOrTables"]
P --> D
D --> A["applyJsonDiff"]
A --> G["Statement Generation (prepare...Json)"]
G --> SQL["SQL Emission (fromJson)"]
```
Sources: [drizzle-kit/src/snapshotsDiffer.ts:559-2131](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L559-L2131), [drizzle-kit/src/jsonDiffer.js:90-411](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/jsonDiffer.js#L90-L411)
## Handling Renames and Schema Moves
Renames and moves are identified through a recursive mapping process. The differ uses helper functions like `nameSchemaChangeFor` to track how tables or schemas have migrated across logical boundaries.
> [!TIP]
> The `nameSchemaChangeFor` function is crucial for preventing redundant "drop-then-create" operations. By resolving identity continuity before generating diffs, it ensures that changes inside a table or schema are preserved even if the container object is renamed or moved.
When a rename is detected, the differ updates the internal "snapshot" representation (e.g., `schemasPatchedSnap1`) before executing the final comparison logic. This ensures that the downstream diff algorithm views the renamed entity as existing in both states, albeit with a different identifier.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:519-538](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L519-L538), [drizzle-kit/src/snapshotsDiffer.ts:614-622](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L614-L622)
## SQL Statement Ordering and Invariants
The `applyPgSnapshotsDiff` function (and its counterparts for other dialects) enforces a strict execution order of `JsonStatement` objects to minimize database errors. For example, indexes must generally be handled differently depending on whether the table is new or modified.
The order is dictated by the array manipulation logic in the later stages of the `apply...SnapshotsDiff` functions:
| Statement Category | Execution Sequence | Purpose |
| :--- | :--- | :--- |
| Schemas/Enums/Sequences | Early | Dependencies for table structures. |
| Tables | Mid | Base structural changes. |
| Indexes/Constraints | Late | Post-structural refinement. |
| Policies/Views | Final | Permission/Abstraction layer application. |
> [!CAUTION]
> The ordering of statement application is sensitive. If drop-index operations are not performed *before* column alternations, the database may fail to execute column-level changes that are indexed. The code explicitly handles this by grouping and pushing specific arrays of statements in a specific sequence.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:1948-2023](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L1948-L2023)
## Filtering of Redundant Operations
A post-diff step cleanses the generated statements. Since the diffing logic is sometimes over-aggressive, the code includes a filtering stage where redundant or mutually exclusive operations are removed.
For example, when an operation involves both dropping a constraint and adding a new one, the `filteredJsonStatements` logic can remove operations that would result in invalid database states, such as `alter_table_alter_column_drop_notnull` followed by an identity operation change that is redundant.
```typescript
// Example of redundant operation filtering
const filteredJsonStatements = jsonStatements.filter((st) => {
if (st.type === 'alter_table_alter_column_drop_notnull') {
if (jsonStatements.find((it) => it.type === 'alter_table_alter_column_drop_identity' ...)) {
return false;
}
}
// ...
});
```
Sources: [drizzle-kit/src/snapshotsDiffer.ts:2025-2051](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L2025-L2051)
## Library Integration: `libSqlPushUtils`
In scenarios where Drizzle is pushing changes to a Turso (LibSQL/SQLite) database, the `libSqlPushUtils` module provides utility methods to calculate actual data loss risks during table recreations. Since SQLite lacks support for many `ALTER TABLE` operations, the differ must often "recreate" tables by creating a new table and migrating the data.
The `libSqlLogSuggestionsAndReturn` function iterates over the generated statements and queries the existing database instance to check row counts, enabling the CLI to display warnings to the user if data deletion is imminent.
Sources: [drizzle-kit/src/cli/commands/libSqlPushUtils.ts:113-355](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/libSqlPushUtils.ts#L113-L355)
## Related
- [[Schema Serialization]]
---
## CLI Commands
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/cli-commands
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/cli/commands/migrate.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/migrate.ts)
- [drizzle-kit/src/cli/commands/utils.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/utils.ts)
- [drizzle-kit/src/cli/commands/pgPushUtils.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/pgPushUtils.ts)
- [drizzle-kit/src/cli/schema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/schema.ts)
- [drizzle-kit/src/snapshotsDiffer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts)
The Drizzle Kit CLI Commands provide a bridge between local TypeScript schema definitions and remote database states. Its primary responsibility is schema reconciliation: computing the delta between an expected schema (defined in code) and an actual database schema, then generating the SQL migration statements necessary to align the two.
The system addresses the "state drift" problem common in ORM-driven development. By maintaining a journal of migrations and snapshot files (stored in an `out` folder), the CLI can reconstruct the history of the database schema. This allows it to perform sophisticated diffing, enabling operations like table renaming, column migrations, and schema evolution, while abstracting away the underlying SQL complexities for various dialects (PostgreSQL, MySQL, SQLite, etc.).
Architecturally, the CLI is composed of a command-parsing layer, a validation layer, and a transformation engine. It uses `zod` for strict configuration validation and a custom diffing engine (`snapshotsDiffer`) to produce a list of `JsonStatement` objects. These statements act as an intermediate representation, which are eventually compiled into final SQL strings through `sqlgenerator`.
## Initialization and Configuration Lifecycle
Every CLI command lifecycle begins with configuration normalization. Whether triggered via `drizzle.config.ts` or CLI arguments, parameters pass through preparation functions (e.g., `prepareGenerateConfig`, `preparePushConfig`). These functions enforce invariants, such as ensuring the required schema paths and dialects are present.
The `safeRegister` mechanism is a load-bearing guard ensuring the environment is prepared before execution. It utilizes an `InMemoryMutex` to prevent concurrent modification or re-registration of the `tsx` environment.
```typescript
// Example of how the CLI wraps execution in a mutex and registration
export const safeRegister = async (fn: () => Promise) => {
return registerMutex.withLock(async () => {
ensureTsxRegistered();
await assertES5();
return fn();
});
};
```
Sources: [drizzle-kit/src/cli/commands/utils.ts:95-101](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/utils.ts#L95-L101)
## Conflict Resolution and User Interaction
When a schema drift is detected, the CLI often encounters ambiguous state changes (e.g., a table was deleted but a new one was added, potentially indicating a rename). The `migrate.ts` file defines a suite of "resolvers" that trigger interactive prompts using `hanji`.
The resolution logic prioritizes specific entity types:
- `promptNamedWithSchemasConflict`: Resolves collisions for entities mapped to schemas (tables, views, enums).
- `promptColumnsConflicts`: Handles column-specific renames within a table.
The mechanism uses a `do-while` loop to iterate through unresolved `created` items and maps them against `missing` items. If a user selects an item as a rename, the system tracks the mapping, ensuring the diffing engine accounts for the continuity of the entity.
Sources: [drizzle-kit/src/cli/commands/migrate.ts:1066-1131](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/migrate.ts#L1066-L1131)
## Snapshot Diffing Mechanism
The core engine resides in `snapshotsDiffer.ts`. It takes two "squashed" schema objects (`json1` as `prev` and `json2` as `cur`) and computes the difference. The process is dialect-specific, utilizing `applyPgSnapshotsDiff`, `applyMysqlSnapshotsDiff`, etc.
The mechanism follows a strict order of operations:
1. **Schema Diffing:** Identify additions, deletions, and renames at the schema level.
2. **Entity Patches:** Iteratively resolve conflicts for enums, sequences, and roles.
3. **Table Diffing:** Perform deep comparisons of columns, indexes, and constraints.
4. **Statement Generation:** The results of these diffs are translated into an array of `JsonStatement` types.
> [!NOTE]
> The `snapshotsDiffer` utilizes a `copy()` utility before modifying the state during diffing. This ensures the original snapshots remain immutable while the intermediate "patched" snapshots are prepared for the SQL generation step.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:559-603](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L559-L603)
## SQL Migration Generation
Once the `JsonStatement` array is finalized, the system converts these into executable SQL. The `writeResult` function is the final step in the migration generation pipeline. It generates the `_snapshot.json` to be stored in the `meta` folder and creates the `.sql` migration file.
The ordering of the generated SQL is critical. The system uses a specific precedence order to prevent constraint violations:
- Drop indexes → Drop/Alter constraints → Alter columns → Add/Drop tables.
```mermaid
flowchart TD
A["Prepare Migration Folder"] --> B["Compute Snapshot Diff"]
B --> C{"Conflict Resolved?"}
C -->|Yes| D["Map Entity Changes"]
D --> E["Generate JsonStatements"]
E --> F["Compile SQL from Statements"]
F --> G["Write .sql file & _journal.json"]
```
Sources: [drizzle-kit/src/cli/commands/migrate.ts:1356-1458](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/migrate.ts#L1356-L1458)
## Database Push Utilities
For non-migration-based syncing (the `push` command), `pgPushUtils.ts` provides a safety-check mechanism. Before executing dangerous SQL operations (like dropping a table or column with data), it queries the database to count affected rows.
If data loss is detected, the utility sets `shouldAskForApprove = true`. This prevents accidental destruction of production data.
| Statement Type | Check Performed | Action Triggered |
| :--- | :--- | :--- |
| `drop_table` | `SELECT count(*)` | Trigger prompt if count > 0 |
| `alter_table_drop_column` | `SELECT count(*)` | Trigger prompt if count > 0 |
| `create_unique_constraint` | `SELECT count(*)` | Offer truncation option if count > 0 |
Sources: [drizzle-kit/src/cli/commands/pgPushUtils.ts:59-269](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/commands/pgPushUtils.ts#L59-L269)
## Command Structure
The CLI uses the `@drizzle-team/brocli` library to define its interface. Each command follows a consistent transformation and handler pattern.
| Command | Entry point | Purpose |
| :--- | :--- | :--- |
| `generate` | `schema.ts:generate` | Creates migration SQL from schema changes |
| `migrate` | `schema.ts:migrate` | Applies local SQL migrations to the remote DB |
| `push` | `schema.ts:push` | Directly syncs schema changes to the remote DB |
| `introspect` | `schema.ts:pull` | Pulls remote schema into local code files |
Sources: [drizzle-kit/src/cli/schema.ts:50-481](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/cli/schema.ts#L50-L481)
## Related
- [[Kit Overview]]
---
## Zod Validation
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/zod-validation
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-kit/src/introspect-gel.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-gel.ts)
- [drizzle-kit/src/serializer/pgSchema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts)
- [drizzle-valibot/src/column.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-valibot/src/column.ts)
- [drizzle-typebox/src/column.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-typebox/src/column.ts)
- [drizzle-zod/src/column.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-zod/src/column.ts)
- [drizzle-kit/src/serializer/gelSchema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/gelSchema.ts)
- [drizzle-zod/src/schema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-zod/src/schema.ts)
- [drizzle-orm/type-tests/pg/tables.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/tables.ts)
Zod Validation provides a bridge between Drizzle ORM database definitions and Zod schemas, enabling automatic generation of validation logic from table and view definitions. By introspecting the Drizzle schema, this component generates Zod schemas that represent the structure of your database, ensuring type safety and input validation throughout the application lifecycle.
The system solves the problem of manual schema synchronization, where changes to the database structure would otherwise require tedious updates to validation layers. It provides specialized functions to create "select", "insert", and "update" schemas, which apply different rules regarding nullability and optionality based on the Drizzle column configuration.
Designed for tight integration with the Drizzle ecosystem, the subsystem leverages column metadata (such as data types, nullability, and default values) to construct the corresponding Zod types. This approach prevents runtime errors by ensuring that data interacting with the database adheres strictly to the defined schema at the application boundaries.
## Core Transformation Logic
The transformation logic centers on mapping Drizzle column types to their equivalent Zod schema definitions. The `columnToSchema` function serves as the central dispatcher, inspecting column characteristics to decide the appropriate Zod validator.
It handles complex types by recursively calling itself for arrays or inspecting object-specific properties for types like points, lines, and geometry. The mechanism relies on `isColumnType` and `isWithEnum` to differentiate between standard and specialized column types, ensuring that even custom extensions are handled correctly.
Sources: [drizzle-zod/src/column.ts:70-135](https://github.com/blade47/drizzle-orm/blob/main/drizzle-zod/src/column.ts#L70-L135)
## Schema Generation Pipeline
The generation of schemas (`createSelectSchema`, `createInsertSchema`, `createUpdateSchema`) is managed by `handleColumns`. This function iterates over a table's columns and applies conditional logic to ensure the schema matches the required database operation.
The pipeline processes each column by:
1. Checking if the column should be excluded via the `conditions` logic.
2. Converting the column to its base Zod schema.
3. Applying refinements if custom validation is provided by the user.
4. Applying nullability or optionality decorators based on column properties (e.g., `notNull` vs. default values).
Sources: [drizzle-zod/src/schema.ts:19-64](https://github.com/blade47/drizzle-orm/blob/main/drizzle-zod/src/schema.ts#L19-L64)
```mermaid
flowchart TD
A["getTableColumns(table)"] --> B["handleColumns(columns, refinements, conditions)"]
B --> C["Loop over columns"]
C --> D{"Column
included?"}
D -- Yes --> E["columnToSchema(column)"]
E --> F["Apply optional/nullable
decorators"]
F --> G["Return z.object"]
D -- No --> H["Skip column"]
```
Sources: [drizzle-zod/src/schema.ts:15-64](https://github.com/blade47/drizzle-orm/blob/main/drizzle-zod/src/schema.ts#L15-L64)
## Handling Conditions
The `Conditions` interface dictates how `handleColumns` modifies the schema for different database actions. These conditions are defined as sets of functions (`never`, `optional`, `nullable`) that act as guards.
- **Select:** Makes columns nullable if `!notNull`.
- **Insert:** Excludes `generated` columns, makes columns with defaults `optional`, and makes `!notNull` columns `nullable`.
- **Update:** Excludes `generated` columns and treats all columns as `optional`.
Sources: [drizzle-zod/src/schema.ts:76-92](https://github.com/blade47/drizzle-orm/blob/main/drizzle-zod/src/schema.ts#L76-L92)
## Handling Numeric Types
Numeric and bigint column conversion includes built-in range validation. The `numberColumnToSchema` and `bigintColumnToSchema` functions extract constraints from Drizzle's internal metadata and apply Zod's `gte()` and `lte()` checks.
> [!NOTE]
> The generation logic checks for `unsigned` SQL types to adjust the `min` value constraint automatically, ensuring generated schemas are as restrictive as the underlying database type.
Sources: [drizzle-zod/src/column.ts:137-265](https://github.com/blade47/drizzle-orm/blob/main/drizzle-zod/src/column.ts#L137-L265)
## API Usage
Developers can use the high-level API to generate schemas directly from Drizzle table objects. The factory pattern allows for additional configuration, such as coercion of primitive types.
```typescript
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';
const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
age: integer('age'),
});
const insertUserSchema = createInsertSchema(users);
const selectUserSchema = createSelectSchema(users);
```
Sources: [drizzle-zod/src/schema.ts:94-119](https://github.com/blade47/drizzle-orm/blob/main/drizzle-zod/src/schema.ts#L94-L119)
## Schema Factory
The `createSchemaFactory` provides a way to globally configure coercion behavior. When the factory is initialized with `coerce: true`, generated schemas will automatically add Zod's coercion layers for primitives, which is useful when dealing with form data or query parameters.
Sources: [drizzle-zod/src/schema.ts:121-152](https://github.com/blade47/drizzle-orm/blob/main/drizzle-zod/src/schema.ts#L121-L152)
## Related
- [[Schema Declarations]]
---
## TypeBox Validation
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/typebox-validation
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/snapshotsDiffer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts)
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-typebox/src/column.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-typebox/src/column.ts)
"TypeBox Validation" is a specialized bridge component within the Drizzle ecosystem designed to derive [TypeBox](https://github.com/sinclairzx81/typebox) schemas directly from Drizzle ORM column definitions. By leveraging the type metadata inherently present in Drizzle's database objects, this component automates the creation of validation logic, ensuring that TypeScript types and database-level constraints remain synchronized with runtime validation schemas.
The system solves the problem of "schema drift" between database definitions and application-level validation. Instead of manually maintaining separate validation schemas for input sanitization or serialization, developers can transform a Drizzle `Column` definition into a TypeBox `TSchema` using the `columnToSchema` function. This design decision prioritizes a single source of truth, where the database schema serves as the blueprint for both data access and validation.
Architecture-wise, it acts as a mapping layer that inspects the internal properties of Drizzle columns (such as `dataType`, `enumValues`, or specific vector dimensions) and maps them to corresponding TypeBox primitive or composite types. It serves as a foundational utility for Drizzle tools that require runtime validation, such as those generating API response bodies or verifying incoming payload integrity based on current table structure.
## Mapping Mechanisms
The core mechanism for translation is the `columnToSchema` function. It operates as a recursive dispatcher that identifies the underlying database column type using internal helper functions like `isColumnType` and `isWithEnum`.
- **Dispatch Flow**: The function first checks if a column is associated with an enum (`isWithEnum`). If true, it maps the enum values using `mapEnumValues`.
- **Specialized Geometry/Vector Parsing**: If the type is not an enum, it proceeds to check for specific Drizzle-ORM core types. For instance, `PgGeometry` or `PgPointTuple` are mapped to `t.Tuple` schemas of two numbers.
- **Dimensional Awareness**: Vector types (`PgHalfVector`, `PgVector`) and Array types (`PgArray`) are processed to extract dimensions or sizes. If a dimension is present, the generated TypeBox schema is constrained using `minItems` and `maxItems` attributes to enforce strict structural adherence.
Sources: [drizzle-typebox/src/column.ts:71-114](https://github.com/blade47/drizzle-orm/blob/main/drizzle-typebox/src/column.ts#L71-L114)
## Enum Handling
Enum handling is a critical feature that links database string-based constraints to runtime validation. When the system detects a column with defined enum values, it executes `mapEnumValues` to create a key-value mapping suitable for TypeBox's enum validation, ensuring that only valid database-defined strings pass validation.
```typescript
// Example of enum mapping logic
export function mapEnumValues(values: string[]) {
return Object.fromEntries(values.map((value) => [value, value]));
}
```
Sources: [drizzle-typebox/src/column.ts:67-69](https://github.com/blade47/drizzle-orm/blob/main/drizzle-typebox/src/column.ts#L67-L69)
## Integration with Drizzle-Kit Snapshots
Beyond simple column transformation, validation patterns are used in the broader Drizzle-Kit system to manage structural transitions. In `drizzle-kit/src/snapshotsDiffer.ts`, `zod` is utilized to enforce strict object structures—such as `alteredTableScheme`—which describe how columns, indices, and constraints change between two schema snapshots.
These definitions facilitate the transition from declarative schema definitions to actionable migration statements. By employing strict object schemas, the system ensures that the diffing logic maintains consistency when comparing table states across different database providers like PostgreSQL and MySQL.
Sources: [drizzle-kit/src/snapshotsDiffer.ts:147-391](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/snapshotsDiffer.ts#L147-L391)
## Type Registry Extensibility
To handle non-primitive database types, the system implements custom type registrations in TypeBox. Notably, a custom `Buffer` type is registered to allow the validator to recognize binary data types common in SQL databases, which standard JSON schemas cannot represent naturally.
```typescript
// Registering Buffer support for TypeBox
TypeRegistry.Set('Buffer', (_, value) => value instanceof Buffer);
export const bufferSchema: BufferSchema = { [Kind]: 'Buffer', type: 'buffer' } as any;
```
Sources: [drizzle-typebox/src/column.ts:64-65](https://github.com/blade47/drizzle-orm/blob/main/drizzle-typebox/src/column.ts#L64-L65)
## Control Flow: Column Schema Generation
The following diagram illustrates how the `columnToSchema` function resolves the final TypeBox schema.
```mermaid
flowchart TD
A["columnToSchema(column)"] --> B{isWithEnum?}
B -->|Yes| C["Map to Enum Schema"]
B -->|No| D{Specific Type?}
D -->|Geometry/Tuple| E["Return t.Tuple"]
D -->|Vector| F["Return t.Array with constraints"]
D -->|Default| G["numberColumnToSchema / bigintColumnToSchema"]
C --> H["Final TSchema"]
E --> H
F --> H
G --> H
```
Sources: [drizzle-typebox/src/column.ts:71-120](https://github.com/blade47/drizzle-orm/blob/main/drizzle-typebox/src/column.ts#L71-L120)
## Design Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Runtime reflection** | Enables dynamic schema creation from ORM definition. | Slight performance overhead compared to hardcoded types. |
| **Recursive dispatch** | Easily extensible for new SQL types (e.g., vectors, arrays). | Complexity in maintaining deep branch logic. |
| **TypeBox dependency** | Provides performant, standard-compliant schema validation. | Requires users to depend on the TypeBox library. |
Sources: [drizzle-typebox/src/column.ts:71-120](https://github.com/blade47/drizzle-orm/blob/main/drizzle-typebox/src/column.ts#L71-L120)
> [!TIP]
> Always verify that your column types are correctly imported into the `drizzle-typebox` module, as the system relies on specific `isColumnType` guards that check for the presence of the class name in the column's prototype chain.
> [!NOTE]
> The `columnToSchema` function is designed for read-only reflection. It does not update the database state; it only generates the validation blueprint corresponding to the definition provided.
## Related
- [[Schema Declarations]]
---
## Valibot Validation
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/valibot-validation
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-kit/src/introspect-mysql.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-mysql.ts)
- [drizzle-kit/src/introspect-gel.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-gel.ts)
- [drizzle-kit/src/introspect-singlestore.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-singlestore.ts)
- [drizzle-kit/src/serializer/pgSerializer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts)
"Valibot Validation" in the context of Drizzle Kit refers to the internal infrastructure responsible for introspecting database schemas and transforming that information into valid, type-safe TypeScript declarations. While named "Valibot Validation" in this context, it functions as the bridge between raw database metadata and the Drizzle ORM query layer, ensuring that introspected schemas are syntactically sound and conform to the expected Drizzle schema structures.
The core purpose of this subsystem is to normalize the wide variance in database column types, defaults, and constraints (found in PostgreSQL, MySQL, Gel, and SingleStore) into a uniform TypeScript-based schema representation. It addresses the complexity of "database-to-code" translation by handling dialect-specific nuances—such as PostgreSQL identity columns, MySQL auto-increments, or custom geometry/vector types—and ensuring they are rendered as valid code.
Architecturally, this component operates as a high-level serialization engine. It processes raw database snapshots, performs name casing transformations, resolves cyclic foreign key references, and applies type patches. This ensures that the generated TypeScript file is not just a dump of columns, but a robust schema file that a user can immediately integrate into their application code.
## The Introspection Lifecycle
The introspection flow follows a deterministic pattern across all supported dialects. It begins by collecting metadata about tables, enums, sequences, roles, and constraints from the target database, and concludes by emitting TypeScript code that represents this schema.
```mermaid
flowchart TD
A["Raw Database Snapshot"] --> B["Apply Casing Strategies"]
B --> C["Identify Enums & Types"]
C --> D["Process Tables & Constraints"]
D --> E["Resolve Cyclic Relations"]
E --> F["Generate TypeScript Source"]
```
The system heavily relies on dialect-specific "patch" maps (e.g., `importsPatch` in `introspect-pg.ts`) to translate raw database types (e.g., `timestamp without time zone`) into their clean Drizzle ORM equivalents (`timestamp`). This normalization layer is critical for maintainability, preventing the leaking of implementation-specific type names into user-facing code.
## Casing and Name Normalization
The system enforces consistent naming conventions (e.g., `camelCase` vs. `preserve`) for all generated table names, column keys, and schema references.
The `withCasing` function acts as the primary gatekeeper for naming, applying `toCamelCase` if configured, while `escapeColumnKey` ensures that reserved keywords or non-standard characters in database names are wrapped in quotes (`"columnName"`).
> [!IMPORTANT]
> The `dbColumnName` function returns an empty string for `preserve` casing to signal that the database identifier matches the internal identifier exactly, thus omitting an explicit `name` argument in the generated Drizzle declaration.
## Column Definition and Type Mapping
Column generation is managed via highly specialized mapping functions (`column()` and `mapDefault()`). These functions trace the database type, apply necessary `mode` flags for types like `bigint`, and handle default value parsing, including complex expression defaults.
| Dialect | Mapping Mechanism |
| :--- | :--- |
| **PostgreSQL** | Uses `pgImportsList` for type imports and `generateIdentityParams` for identity columns. |
| **MySQL** | Uses `mysqlImportsList` and specialized `onUpdate` handlers for `timestamp`. |
| **Gel** | Maps `edgedbt` types to specific `gel-core` imports like `bigintT` or `relDuration`. |
Sources: [drizzle-kit/src/introspect-pg.ts:838-1101](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L838-L1101), [drizzle-kit/src/introspect-mysql.ts:386-816](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-mysql.ts#L386-L816)
## Cyclic Dependency Resolution
When introspecting schemas, cyclic foreign key references pose a significant risk of generating uncompilable TypeScript code. The `isCyclic` and `isSelf` functions determine whether a foreign key is part of a reference cycle.
If a cycle is detected, the generator often forces a reference to `AnyPgColumn` or equivalent type interfaces to prevent TS circular dependency errors during initialization.
```typescript
const isCyclic = (fk: ForeignKey) => {
const key = `${fk.tableFrom}-${fk.tableTo}`;
const reverse = `${fk.tableTo}-${fk.tableFrom}`;
return relations.has(key) && relations.has(reverse);
};
```
Sources: [drizzle-kit/src/introspect-pg.ts:638-642](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L638-L642)
## Identity and Generated Column Handling
The generator explicitly handles identity parameters for PostgreSQL. The `generateIdentityParams` function constructs a parameter string for `.generatedAlwaysAsIdentity()` or `.generatedByDefaultAsIdentity()`.
It performs a serial check on parameters: `startWith`, `increment`, `minValue`, `maxValue`, `cache`, and `cycle`. Each parameter is appended only if present in the source object, ensuring the generated Drizzle code is clean of unnecessary configuration blobs.
Sources: [drizzle-kit/src/introspect-pg.ts:277-302](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L277-L302)
## Constraint Processing
Constraints (Checks, Uniques, PKs) are processed in a separate pipeline from the columns themselves. This is a design decision that enables better grouping of constraints at the end of the `table()` declaration block.
1. `createTableIndexes`: Handles indexes, including `concurrently` and `using` methods.
2. `createTablePKs`: Aggregates columns into a primary key definition.
3. `createTableUniques`: Processes unique constraints, ensuring they support `nullsNotDistinct`.
> [!WARNING]
> Index name generation is highly sensitive. The generator attempts to infer index names from column lists, but fails if it cannot resolve column naming conflicts, leading to forced `process.exit(1)` in the `pgSerializer.ts`.
Sources: [drizzle-kit/src/serializer/pgSerializer.ts:367-487](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L367-L487)
## Design Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Centralized Import Map** | Simplifies type-safety for Drizzle imports. | Manual maintenance for every new database type. |
| **Separation of Constraints** | Clean, readable table definitions. | Increased complexity in the generator's state management. |
| **Casing Patching** | User-defined casing preferences are honored. | Added overhead for string transformation in loops. |
## Worked Example: Generating a PostgreSQL Table
The following conceptual snippet shows how the introspector builds a table definition. It iterates over columns and then appends constraint functions.
```typescript
// Example of how the introspector generates table structure
let statement = `export const ${withCasing(name, casing)} = pgTable("${name}", {\n`;
statement += createTableColumns(...); // Appends column definitions
statement += '}';
// If constraints exist, append them as a function body
if (hasConstraints) {
statement += ', (table) => [';
statement += createTableIndexes(...);
statement += createTableFKs(...);
statement += '\n]';
}
statement += ');';
```
Sources: [drizzle-kit/src/introspect-pg.ts:508-565](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L508-L565)
## Related
- [[Schema Declarations]]
---
## ArkType Validation
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/arktype-validation
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-kit/src/serializer/pgSchema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts)
- [drizzle-kit/src/serializer/gelSchema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/gelSchema.ts)
- [drizzle-arktype/src/column.ts](https://github.com/blade47/drizzle-arktype/src/column.ts)
- [drizzle-valibot/src/column.ts](https://github.com/blade47/drizzle-valibot/src/column.ts)
- [drizzle-typebox/src/column.ts](https://github.com/blade47/drizzle-typebox/src/column.ts)
- [drizzle-zod/src/column.ts](https://github.com/blade47/drizzle-zod/src/column.ts)
- [drizzle-kit/src/introspect-singlestore.ts](https://github.com/blade47/drizzle-kit/src/introspect-singlestore.ts)
- [drizzle-valibot/src/schema.ts](https://github.com/blade47/drizzle-valibot/src/schema.ts)
- [drizzle-typebox/src/schema.ts](https://github.com/blade47/drizzle-typebox/src/schema.ts)
- [drizzle-zod/src/schema.ts](https://github.com/blade47/drizzle-zod/src/schema.ts)
- [drizzle-arktype/src/schema.ts](https://github.com/blade47/drizzle-arktype/src/schema.ts)
- [drizzle-arktype/src/column.types.ts](https://github.com/blade47/drizzle-arktype/src/column.types.ts)
- [drizzle-arktype/src/index.ts](https://github.com/blade47/drizzle-arktype/src/index.ts)
- [drizzle-arktype/src/schema.types.internal.ts](https://github.com/blade47/drizzle-arktype/src/schema.types.internal.ts)
- [drizzle-valibot/src/column.types.ts](https://github.com/blade47/drizzle-valibot/src/column.types.ts)
ArkType Validation is a specialized integration layer within the Drizzle ecosystem designed to automatically generate high-performance runtime type validators from Drizzle database schema definitions. By leveraging the `ArkType` library, it bridges the gap between static database schema declarations and runtime data validation, ensuring that data fetched from or prepared for a database strictly conforms to the expected shapes.
This system resolves the common problem of "schema drift" between database columns and application types. Instead of manually maintaining separate validation schemas, developers can use Drizzle-defined tables and views as the single source of truth. The ArkType validation layer inspects Drizzle column metadata (type, length, constraints, and dialect-specific properties) to construct a corresponding `Type` object.
The architecture is built as an extensible adapter. The core engine traverses the Drizzle entity tree, identifies the underlying column types, and maps them to appropriate ArkType primitive or compound validators. This ensures that features like nullable columns, optional fields, and constraints (e.g., character limits or specific numeric ranges) are automatically enforced at the validation boundary.
## Core Transformation Mechanism
The system relies on a central `columnToSchema` function that maps a Drizzle `Column` to an `ArkType` definition. This process involves a hierarchical type dispatch based on `dataType` and specific column implementation types.
- **Type Identification**: The system uses `isColumnType` to perform runtime checks for specific database column classes (e.g., `PgSmallInt`, `MySqlVarChar`).
- **Data Mapping**:
- For `number` types, the system performs range calculations based on `CONSTANTS` (e.g., `INT16_MIN`, `INT8_UNSIGNED_MAX`) to restrict the `ArkType` numeric range accordingly.
- For `string` types, it calculates maximum length requirements and applies pattern matching for specific types like UUIDs or binary vectors.
- For `enum` types, it checks `isWithEnum` and maps Drizzle enum values into an `ArkType` enumerated validator.
```typescript
// Example: The dispatch logic in columnToSchema
export function columnToSchema(column: Column): Type {
let schema!: Type;
if (isWithEnum(column)) {
schema = column.enumValues.length ? type.enumerated(...column.enumValues) : type.string;
}
// ... further type-specific checks
if (column.dataType === 'number') {
schema = numberColumnToSchema(column);
}
// ...
return schema || type.unknown;
}
```
Sources: [drizzle-arktype/src/column.ts:66-126](https://github.com/blade47/drizzle-orm/blob/main/drizzle-arktype/src/column.ts#L66-L126)
## Numeric Constraint Enforcement
Numeric columns undergo a multi-stage validation transformation where the Drizzle column configuration is converted into a range-constrained ArkType validator. The mechanism reads the column type string to identify if it is "unsigned" and then selects a corresponding constant from `CONSTANTS` to define the bounds.
| Database Type | Integer Constraint | Range Source |
| :--- | :--- | :--- |
| `TinyInt` | Yes | `CONSTANTS.INT8_MIN/MAX` |
| `SmallInt` | Yes | `CONSTANTS.INT16_MIN/MAX` |
| `Integer` | Yes | `CONSTANTS.INT32_MIN/MAX` |
| `Real/Double` | No | `CONSTANTS.INT48_MIN/MAX` |
The mechanism uses a `numberColumnToSchema` function which applies `.atLeast(min).atMost(max)` to the resulting validator. If the column represents an integer type, the system additionally decorates the schema with `type.keywords.number.integer`.
Sources: [drizzle-arktype/src/column.ts:128-229](https://github.com/blade47/drizzle-arktype/src/column.ts#L128-L229)
## Schema Traversal and Generation
The transformation from a Drizzle table to a full schema object occurs in `schema.ts`. The process follows a recursive pattern:
1. `getColumns` extracts the map of Drizzle columns from the table or view.
2. `handleColumns` iterates over this map, transforming each column into an `ArkType` validator.
3. If an object is found that isn't a column or SQL expression, the system treats it as a nested structure and recurses.
> [!NOTE]
> During schema generation, if a refinement function is encountered, the generator treats it as an override, allowing users to extend the generated validator with custom logic while maintaining the underlying base schema.
Sources: [drizzle-arktype/src/schema.ts:10-59](https://github.com/blade47/drizzle-arktype/src/schema.ts#L10-L59)
## Handling Nullability and Optionality
The system dynamically adjusts the resulting `Type` validator based on the column's nullability and default settings:
- **Nullability**: If `conditions.nullable(column)` returns true (e.g., column is `notNull: false`), the validator is updated using `.or(type.null)`.
- **Optionality**: If `conditions.optional(column)` returns true, the validator is wrapped using `.optional()`.
This check happens post-refinement, ensuring that any user-provided constraints are wrapped correctly within the nullability or optionality logic.
Sources: [drizzle-arktype/src/schema.ts:48-55](https://github.com/blade47/drizzle-arktype/src/schema.ts#L48-L55)
## Worked Example: Validating a User Table
To use the system, define your Drizzle table and pass it to the `createSelectSchema` function.
```typescript
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { createSelectSchema } from 'drizzle-arktype';
const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
age: integer('age'),
});
// Generate validator
const userValidator = createSelectSchema(users);
// Validate data
const result = userValidator({ name: 'Alice', age: 30 });
if (result instanceof type.errors) {
console.error('Validation failed', result);
} else {
console.log('Validated user', result);
}
```
Sources: [drizzle-arktype/src/schema.ts:61-74](https://github.com/blade47/drizzle-arktype/src/schema.ts#L61-L74)
## Design Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Recursive Traversal** | Supports nested objects and complex table structures | Increased complexity in handling circular references |
| **Constraint-based Mapping** | Automatically enforces database constraints at runtime | Tight coupling to Drizzle's internal column metadata structures |
| **Refinement Injection** | Allows custom user logic per field | Potentially leads to type-safety loss if misused in custom functions |
Sources: [drizzle-arktype/src/schema.ts:14-56](https://github.com/blade47/drizzle-arktype/src/schema.ts#L14-L56)
## Related
- [[Schema Declarations]]
---
## Seed Engine
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/seed-engine
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-seed/src/services/Generators.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/Generators.ts)
- [drizzle-seed/src/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/index.ts)
- [drizzle-seed/src/services/SeedService.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/SeedService.ts)
The "Seed Engine" is a sophisticated data generation framework integrated into the Drizzle ORM ecosystem, specifically designed to automate the population of database schemas with realistic synthetic data. It acts as an abstraction layer between schema definitions and the actual data insertion process, allowing developers to generate consistent, randomized, and relationship-aware data without writing repetitive manual inserts. By leveraging type metadata from Drizzle schemas, the engine intelligently selects appropriate data generators for different SQL column types, ensuring that the generated data respects constraints like foreign keys, uniqueness, and nullability.
The engine functions by analyzing the relational structure of a provided schema to determine the order of operations, ensuring that parent rows are generated before dependent child rows. This dependency management is crucial for maintaining referential integrity during bulk data generation. For complex scenarios involving cyclic dependencies, the Seed Engine employs a two-pass generation strategy, populating initial rows before backfilling circular references.
At its core, the system is built upon a deterministic generation model using the `pure-rand` library. By accepting a `seed` value, the engine guarantees that the same input parameters and schema result in identical data sets across multiple runs, which is an essential feature for reproducible testing environments. The Seed Engine is highly extensible, providing a suite of default generators for common data types (names, emails, addresses, integers, etc.) while allowing users to refine the generation strategy for specific columns using a fluent API.
## Core Architecture and Data Flow
The Seed Engine operates by transforming schema definitions into a directed graph of generation tasks. It navigates through these tasks by orchestrating a series of generator classes that satisfy column constraints.
```mermaid
flowchart TD
A["User Input
(Schema + Options)"] --> B["SeedPromise
(Entry)"]
B --> C["SeedService
(Dependency Graph)"]
C --> D["Generator Initialization
(Seed & Weights)"]
D --> E["Dependency Sort
(Cyclic Resolution)"]
E --> F["Table Data Generation"]
F --> G["DB Insertion"]
subgraph Generators
H["Generator Classes
(GenerateInt, GenerateEmail, etc.)"]
end
D -.-> H
```
Sources: [drizzle-seed/src/index.ts:137-206](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/index.ts#L137-L206), [drizzle-seed/src/services/SeedService.ts:34-76](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/SeedService.ts#L34-L76)
## Generation Lifecycle
The lifecycle of a generation task begins with an initialization phase where generators are assigned a global seed. This seed is propagated to internal pseudo-random number generators (`prand.xoroshiro128plus`), ensuring that every value generated within a table is statistically independent but deterministically linked to the initial `seed` argument provided by the developer.
1. **Preparation:** `SeedService` inspects the input schema, extracting tables and relationships.
2. **Ordering:** Tables are sorted topologically to respect foreign key constraints via internal methods within `SeedService.generatePossibleGenerators`.
3. **Refinement:** User-provided functions (via `.refine()`) override default generator choices.
4. **Instantiation:** Each column is mapped to a specific generator class based on its SQL type.
5. **Execution:** Data is generated in bulk, respecting the requested `count`.
Sources: [drizzle-seed/src/services/Generators.ts:43-50](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/Generators.ts#L43-L50), [drizzle-seed/src/services/SeedService.ts:34-76](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/SeedService.ts#L34-L76), [drizzle-seed/src/services/SeedService.ts:52-67](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/SeedService.ts#L52-L67)
## Generator Hierarchy
The engine utilizes specific generator classes extending from a shared base to enforce a uniform interface. This architecture allows the system to inject specialized versions of generators (like `GenerateUniqueInt`) when constraints like `isUnique` are detected during the schema analysis phase.
```mermaid
classDiagram
class GenerateInt {
+init()
+generate()
}
class GenerateUniqueInt {
+init()
+generate()
}
GenerateInt ..> GenerateUniqueInt : creates
```
Sources: [drizzle-seed/src/services/Generators.ts:17-109](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/Generators.ts#L17-L109)
## Handling Cyclic Dependencies
One of the most complex features of the Seed Engine is its ability to handle tables that reference each other in a cycle. When the system detects a cycle during the dependency analysis, it performs a two-pass process.
> [!CAUTION]
> Seeding cyclic tables with `NOT NULL` foreign keys is fundamentally problematic. The system will throw an error because it cannot satisfy the circular dependency in a single insertion pass.
The logic for cyclic resolution is orchestrated within `SeedService` during the table value generation process, specifically identified when relations are marked with `isCyclic: true` by the internal cycle detection algorithm.
Sources: [drizzle-seed/src/services/SeedService.ts:1108-1140](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/SeedService.ts#L1108-L1140)
## API Surface
The public API is centered around the `seed()` function, which initializes the process and returns a `SeedPromise`. This allows for a chainable, type-safe API for refining generator behavior.
| Method | Purpose |
| :--- | :--- |
| `seed(db, schema)` | Entry point for triggering the seed engine. |
| `refine(callback)` | Allows overriding the automatic generator selection. |
| `reset(db, schema)` | Truncates tables safely (manages FK constraints). |
Sources: [drizzle-seed/src/index.ts:346-364](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/index.ts#L346-L364), [drizzle-seed/src/index.ts:441-479](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/index.ts#L441-L479)
## Worked Example
This example demonstrates how to seed a table while refining specific columns to use unique data or custom templates.
```typescript
// Standard usage of the Seed Engine API
await seed(db, schema, { count: 1000, seed: 42 }).refine((funcs) => ({
users: {
columns: {
// Use unique first names
name: funcs.firstName({ isUnique: true }),
// Use custom template for phone numbers
phone: funcs.phoneNumber({ template: "+380 99 ###-##-##" }),
},
count: 500,
},
}));
```
Sources: [drizzle-seed/src/index.ts:313-344](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/index.ts#L313-L344)
## Related
- [[Data Generators]]
---
## Data Generators
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/data-generators
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-seed/src/services/Generators.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/Generators.ts)
- [drizzle-seed/src/services/SeedService.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/SeedService.ts)
- [drizzle-seed/src/services/GeneratorFuncs.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/GeneratorFuncs.ts)
- [drizzle-seed/src/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/index.ts)
Data Generators are the backend mechanism for producing high-quality, randomized, and schema-compliant data for database tables within the seeding process. Their primary purpose is to encapsulate the logic for generating realistic datasets—such as names, emails, dates, or complex structures like JSON and arrays—into reusable, configurable classes. By utilizing deterministic pseudo-random number generation via the `pure-rand` library, these components ensure that seeding results can be stable and reproducible given the same input seed.
The system is built around a class structure where each generator handles specific data types. Key classes such as `GenerateArray`, `GenerateInt`, and `GenerateEmail` define a consistent lifecycle, providing an `init` method for parameter setup and a `generate` method for production. This architecture allows the system to differentiate between standard and specialized variants, handle array transformations, and manage dependencies like foreign key resolution. By mapping specific column requirements to these specialized classes, the system effectively bridges the gap between raw database schemas and concrete data instances.
Data Generators interact closely with the `SeedService`, which orchestrates the seeding process by inspecting tables, resolving relations, and applying user-defined refinements. When a table is processed, the `SeedService` identifies the appropriate generator for each column, replaces it with an "array" or "unique" variant if needed, and manages the dependency flow to ensure that referenced data is available before it is required. This design ensures that the seeding process remains robust against complex schema requirements, including self-referential relations and not-null constraints.
## Generator Lifecycle and Mechanics
Every generator class must implement an `init` method to process initial parameters (like `count` and `seed`) and a `generate` method that returns a value based on the current context (`i`).
The operational sequence for a generator typically follows:
1. **Initialization**: `init({ count, seed })` is invoked to prepare internal state, such as seeding the PRNG instance and allocating caches like `integersCount` for unique value tracking.
2. **Transformation**: The system utilizes helper methods like `replaceIfUnique()` and `replaceIfArray()` to swap standard generators for specialized instances (e.g., `GenerateUniqueInt` or `GenerateArray`) when the database column constraints dictate specific behavior.
3. **Production**: The `generate()` method is executed for each row, producing a value consistent with the column's defined type.
Sources: [drizzle-seed/src/services/Generators.ts:43-109](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/Generators.ts#L43-L109)
## Generator Registry and Creation
The system defines generator behaviors using a factory-based approach. The `createGenerator` utility function facilitates the instantiation of these classes, mapping parameters to the appropriate constructor logic.
```typescript
function createGenerator, T>(
generatorConstructor: new(params?: T) => GeneratorType,
) {
return (
...args: GeneratorType extends GenerateValuesFromArray | GenerateDefault | WeightedRandomGenerator ? [T]
: ([] | [T])
): GeneratorType => {
let params = args[0];
if (params === undefined) params = {} as T;
return new generatorConstructor(params);
};
}
```
Sources: [drizzle-seed/src/services/GeneratorFuncs.ts:56-67](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/GeneratorFuncs.ts#L56-L67)
Additionally, the system maintains a `generatorsMap` object that maps generator kinds to their historical and current versions. This allows the internal `SeedService` to select the correct implementation based on the specified API version requirement.
Sources: [drizzle-seed/src/services/GeneratorFuncs.ts:764-918](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/GeneratorFuncs.ts#L764-L918)
## Unique Data Generation Mechanism
To guarantee uniqueness (e.g., for Primary Keys or `isUnique: true` columns), the system provides specialized classes like `GenerateUniqueInt`. The core mechanism for ensuring uniqueness relies on interval-based selection:
1. The generator maintains a state of `intervals`—ranges of integers that have not yet been consumed.
2. When `generate()` is called, it picks a random interval, selects a value, and modifies the interval state (splitting or removing ranges) to reflect that the value is now claimed.
3. This approach avoids the memory overhead of storing every generated value while maintaining strictly unique outputs.
> [!CAUTION]
> If the `count` provided to `GenerateUniqueInt` exceeds the number of available unique values in a range, the generator will throw a `RangeError`. Always ensure the defined `minValue` and `maxValue` range is sufficient for the requested count.
Sources: [drizzle-seed/src/services/Generators.ts:643-813](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/Generators.ts#L643-L813)
## Array and Complex Type Production
The `GenerateArray` class handles columns defined as arrays. It does not generate data directly; rather, it acts as a container for a `baseColumnGen`.
* **Recursion**: The system supports nested arrays by initializing a `GenerateArray` that contains another `GenerateArray`.
* **Propagation**: During initialization, the parent array generator calculates the total elements required (`count * size`) and propagates the appropriate parameters to the nested generator.
Sources: [drizzle-seed/src/services/Generators.ts:112-130](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/Generators.ts#L112-L130)
## Design Trade-offs
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| `pure-rand` RNG | Deterministic and reproducible seeding across runs. | Slightly higher complexity than standard `Math.random()`. |
| Wrapper/Decorator Pattern | Keeps `GenerateArray` logic separate from data logic. | Increased object overhead for nested arrays. |
| Interval-based Unique Gen | Guarantees uniqueness without memory-heavy lookups. | More complex logic for managing overlapping/disjoint range splits. |
Sources: [drizzle-seed/src/services/Generators.ts:2, 643-813, 112-130](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/Generators.ts#L2)
## Worked Example
This example demonstrates how a user refines a column using a `WeightedRandomGenerator`, which combines different probability-based sources for column values.
```typescript
import { seed } from 'drizzle-seed';
await seed(db, schema, { count: 1000 }).refine((funcs) => ({
posts: {
columns: {
content: funcs.weightedRandom([
{
weight: 0.6,
value: funcs.loremIpsum({ sentencesCount: 3 }),
},
{
weight: 0.4,
value: funcs.default({ defaultValue: "TODO" }),
},
]),
},
},
}));
```
Sources: [drizzle-seed/src/services/GeneratorFuncs.ts:738-754](https://github.com/blade47/drizzle-orm/blob/main/drizzle-seed/src/services/GeneratorFuncs.ts#L738-L754)
## Related
- [[Seed Engine]]
---
## ESLint Plugin
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/eslint-plugin
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts)
- [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts)
- [.eslintrc.yaml](https://github.com/blade47/drizzle-orm/blob/main/.eslintrc.yaml)
- [drizzle-orm/src/sqlite-core/query-builders/delete.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/query-builders/delete.ts)
- [eslint-plugin-drizzle/src/configs/all.ts](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/configs/all.ts)
- [drizzle-orm/type-tests/mysql/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/select.ts)
- [eslint-plugin-drizzle/src/configs/recommended.ts](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/configs/recommended.ts)
- [eslint-plugin-drizzle/src/enforce-update-with-where.ts](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/enforce-update-with-where.ts)
- [eslint-plugin-drizzle/src/enforce-delete-with-where.ts](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/enforce-delete-with-where.ts)
- [drizzle-orm/src/pg-core/query-builders/update.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/query-builders/update.ts)
- [eslint-plugin-drizzle/src/index.ts](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/index.ts)
- [drizzle-orm/src/singlestore-core/query-builders/delete.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/delete.ts)
- [drizzle-orm/src/mysql-core/query-builders/delete.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/query-builders/delete.ts)
The `eslint-plugin-drizzle` package is a specialized linting utility designed to enforce safety patterns within Drizzle ORM query construction. Its primary purpose is to prevent common, high-risk development mistakes, specifically those involving destructive database operations that lack necessary filtering criteria.
At its architectural core, the plugin functions as a structural validator of the Drizzle fluent API surface. By inspecting the Abstract Syntax Tree (AST) of Drizzle queries, it identifies instances where `delete()` or `update()` methods are invoked without a subsequent `.where()` clause. Executing these operations on entire tables is a frequent source of data loss or corruption, and this plugin serves as a compile-time (or development-time) guard to catch these patterns before they reach a production environment.
The plugin provides curated configuration presets that teams can adopt to ensure a baseline level of query safety. By integrating these rules into standard ESLint workflows, it ensures that developers follow the prescribed, safer patterns—such as chaining the `.where()` clause to every destructive statement—without requiring manual review for every query implementation.
## Core Rules
The plugin centers on two exported rule modules: the default export from `enforce-delete-with-where.ts` and the default export from `enforce-update-with-where.ts`. These rules operate by monitoring the structure of `MemberExpression` nodes within the AST.
For deletions, the rule verifies that the `.delete()` method call is associated with a structure that includes a `.where()` clause. If the call chain is incomplete, it reports an error indicating that all rows in a table are at risk of deletion. The update rule follows a similar logic, specifically focusing on the `.set()` method following an `.update()` call, ensuring that the final chain logically includes a `.where()` operation.
Sources: [eslint-plugin-drizzle/src/enforce-delete-with-where.ts:11-53](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/enforce-delete-with-where.ts#L11-L53), [eslint-plugin-drizzle/src/enforce-update-with-where.ts:10-58](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/enforce-update-with-where.ts#L10-L58)
## Configuration Presets
The plugin exposes configurations through an `index.ts` file that aggregates rules and settings, allowing for modular adoption:
| Config | Scope |
| :--- | :--- |
| `all` | Includes the provided rules as errors. |
| `recommended` | Equivalent to `all`, providing the baseline safety rules. |
Sources: [eslint-plugin-drizzle/src/index.ts:9-14](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/index.ts#L9-L14), [eslint-plugin-drizzle/src/configs/all.ts:1-14](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/configs/all.ts#L1-L14), [eslint-plugin-drizzle/src/configs/recommended.ts:1-14](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/configs/recommended.ts#L1-L14)
## AST Monitoring Mechanism
The plugin utilizes the `@typescript-eslint/utils` RuleCreator to hook into the `MemberExpression` visitor. The implementation tracks the sequence of property names across the AST traversal to validate the presence of the required filtering methods.
```mermaid
flowchart TD
A["Visit MemberExpression"] --> B{Identify .delete or .set}
B --> C{"Check for .where() method presence in chain"}
C -->|Missing| D["Report error"]
C -->|Present| E["Valid (skip)"]
```
Sources: [eslint-plugin-drizzle/src/enforce-delete-with-where.ts:36-50](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/enforce-delete-with-where.ts#L36-L50)
> [!NOTE]
> The rules rely on internal state tracking during the AST traversal process to determine if a `.where()` method has been successfully called within the current query chain.
## Usage Example
To integrate the plugin, ensure it is configured in your ESLint settings and use the recommended plugin exports.
```typescript
// .eslintrc.yaml
plugins:
- drizzle
rules:
'drizzle/enforce-delete-with-where': 'error'
'drizzle/enforce-update-with-where': 'error'
// Your code usage
// This will trigger an error:
db.delete(users);
// This is correct:
db.delete(users).where(eq(users.id, 1));
```
Sources: [eslint-plugin-drizzle/src/index.ts:9-12](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/index.ts#L9-L12), [drizzle-orm/src/sqlite-core/query-builders/delete.ts:160-191](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/query-builders/delete.ts#L160-L191)
## Design Trade-offs
The design prioritizes preventing data-destructive API calls through static analysis.
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| AST-based inspection | Catches errors before execution. | Requires complex AST traversal logic. |
| Fluent API assumption | Matches Drizzle's primary interaction style. | Does not cover non-fluent/variable-bound builds. |
Sources: [eslint-plugin-drizzle/src/enforce-delete-with-where.ts:9-53](https://github.com/blade47/drizzle-orm/blob/main/eslint-plugin-drizzle/src/enforce-delete-with-where.ts#L9-L53)
> [!IMPORTANT]
> The plugin identifies the Drizzle object based on the naming configuration provided in `options`. If you use custom object names for your Drizzle database instance, ensure they are registered in the rule options to trigger accurate detection.
## Related
- [[Data Mutations]]
---
## Query Caching
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/query-caching
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/src/singlestore/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts)
- [drizzle-orm/src/bun-sql/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/bun-sql/session.ts)
- [drizzle-orm/src/postgres-js/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/postgres-js/session.ts)
- [drizzle-orm/src/gel/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel/session.ts)
- [drizzle-orm/src/gel-core/db.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/db.ts)
- [drizzle-orm/src/tidb-serverless/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/tidb-serverless/session.ts)
- [drizzle-orm/src/libsql/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/libsql/session.ts)
- [drizzle-orm/src/pg-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-proxy/session.ts)
- [drizzle-orm/src/d1/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/d1/session.ts)
- [drizzle-orm/src/op-sqlite/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/op-sqlite/session.ts)
- [drizzle-orm/src/mysql-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-proxy/session.ts)
- [drizzle-orm/src/xata-http/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/xata-http/session.ts)
- [drizzle-orm/src/vercel-postgres/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/vercel-postgres/session.ts)
- [drizzle-orm/src/neon-serverless/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-serverless/session.ts)
- [drizzle-orm/src/sqlite-proxy/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/sqlite-proxy/session.ts)
- [drizzle-orm/src/node-postgres/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/node-postgres/session.ts)
- [drizzle-orm/src/mysql2/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql2/session.ts)
- [drizzle-orm/src/planetscale-serverless/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/planetscale-serverless/session.ts)
- [drizzle-orm/src/neon-http/session.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-http/session.ts)
- [drizzle-orm/src/pg-core/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/query-builders/select.ts)
Query Caching in Drizzle ORM provides a standardized mechanism to intercept database execution paths, checking a cache layer before executing a query against the underlying driver. This architecture decouples the database driver from the caching strategy, allowing for consistent query result memoization across diverse targets like PostgreSQL, MySQL, and SQLite.
The system is fundamentally driven by `queryWithCache`, a method typically exposed via the `PreparedQuery` classes of individual drivers. This method acts as a control-flow interceptor: it evaluates the current query and its parameters, checks if a cache hit exists, and either returns the cached data or proceeds to the provided driver execution callback to fetch fresh results and cache them.
By utilizing this abstraction, developers ensure that expensive or frequent read operations are optimized without modifying the core query execution logic. The integration relies on a `Cache` interface, defaulting to a `NoopCache` if no provider is specified, ensuring the system remains functional even without an explicit caching backend.
## The `queryWithCache` Mechanism
The `queryWithCache` method is the core orchestrator. While the implementation varies slightly by driver, the pattern remains consistent: receive an execution task as an asynchronous callback, and wrap it with a cache lookup.
When a query is prepared, the caching configuration—including options for controlling the cache—is passed down to the prepared query instance. Upon `execute()`, the system invokes `queryWithCache`, passing the query string and parameters. The implementation effectively performs a check-then-store loop, where it first looks for the data in the provided cache instance. If the cache result is empty, the original `client.query` (or equivalent) is executed, and its result is pushed back into the cache for future requests.
```typescript
// Example pattern found across multiple implementations (e.g., node-postgres)
return this.queryWithCache(rawQuery.text, params, async () => {
return await client.query(rawQuery, params);
});
```
Sources: [drizzle-orm/src/node-postgres/session.ts:148-150](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/node-postgres/session.ts#L148-L150)
## Caching Lifecycle and Configuration
Caching is initialized at the driver session level. Every session constructor accepts an optional `options` object containing a `cache` implementation. If a session is initiated without one, `NoopCache` is instantiated, providing a safe, no-operation interface that maintains structural compatibility without performing side effects.
| Option | Type | Default | Purpose |
| :--- | :--- | :--- | :--- |
| `cache` | `Cache` | `NoopCache` | Defines the storage backend for query results. |
Sources: [drizzle-orm/src/singlestore/session.ts:13-18](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts#L13-L18), [drizzle-orm/src/bun-sql/session.ts:105-108](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/bun-sql/session.ts#L105-L108)
## Interaction with Prepared Queries
Prepared queries act as the container for caching configurations. When a query is executed, the resulting object is a `PreparedQuery` instance. During this instantiation, the cache configuration is injected into the constructor.
This is where the distinction between "raw" query execution and prepared execution occurs. In the `execute` path, the `PreparedQuery` uses its held configuration to determine how to interact with the cache orchestrator. By attaching this to the prepared query instance, Drizzle preserves the necessary state to execute the cached lookup at runtime, long after the original build-time configuration has passed.
Sources: [drizzle-orm/src/singlestore/session.ts:236-248](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts#L236-L248), [drizzle-orm/src/postgres-js/session.ts:141-152](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/postgres-js/session.ts#L141-L152)
## Integration in Driver Sessions
The driver-specific sessions are responsible for mapping the session-wide cache instance to the prepared queries they generate.
The dependency flow is:
1. `Drizzle` instance initialized with `sessionOptions` (including `Cache`).
2. `Session` receives `Cache` and passes it to `prepareQuery`.
3. `PreparedQuery` inherits the `Cache` reference.
4. `PreparedQuery.execute()` calls `this.queryWithCache(...)`.
This hierarchy ensures that all queries executed within a single session instance utilize the same cache instance, enabling session-wide optimizations.
```mermaid
graph TD
A["db.select()"] --> B["PreparedQuery"]
B --> C["Session.prepareQuery()"]
C --> D["PreparedQuery instance"]
D --> E["PreparedQuery.execute()"]
E --> F["queryWithCache()"]
F --> G["Cache Interface"]
```
Sources: [drizzle-orm/src/node-postgres/session.ts:233-246](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/node-postgres/session.ts#L233-L246), [drizzle-orm/src/neon-serverless/session.ts:220-233](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/neon-serverless/session.ts#L220-L233)
## Architectural Design Trade-offs
The caching system is designed for maximum compatibility across disparate drivers, leading to the following trade-offs:
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Session-level caching** | Consistent interface across all drivers | Coupling session instances to specific cache instances |
| **Callback-based `queryWithCache`** | Allows driver-specific execution logic to remain encapsulated | Requires wrapping every execution branch in a closure |
| **NoopCache default** | Zero-config usage without boilerplate | Requires extra injection to actually enable persistent storage |
Sources: [drizzle-orm/src/singlestore/session.ts:101-103](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts#L101-L103), [drizzle-orm/src/node-postgres/session.ts:208-219](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/node-postgres/session.ts#L208-L219)
> [!TIP]
> Always check if your specific driver and database version support the prepared query format you are using, as some serverless drivers (like PlanetScale) may impose limitations on how queries are executed within transactions versus standard queries, potentially affecting the efficacy of the query cache.
## Working with `NoopCache`
The `NoopCache` class implements the `Cache` interface with empty methods. This design ensures that the session-wide reference to a `Cache` object is always defined, preventing null-pointer exceptions in the `PreparedQuery` execution logic without requiring the user to explicitly handle cases where caching is disabled.
Sources: [drizzle-orm/src/singlestore/session.ts:13-18](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore/session.ts#L13-L18)
## Related
- [[Query Builder Core]]
---
## Integration Tests
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/integration-tests
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/type-tests/mysql/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/select.ts)
- [drizzle-orm/type-tests/geldb/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/geldb/select.ts)
- [drizzle-orm/type-tests/singlestore/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/singlestore/select.ts)
- [drizzle-orm/type-tests/pg/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/select.ts)
- [drizzle-orm/type-tests/sqlite/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/sqlite/select.ts)
- [integration-tests/vitest.config.ts](https://github.com/blade47/drizzle-orm/blob/main/integration-tests/vitest.config.ts)
- [drizzle-orm/type-tests/common/aliased-table.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/common/aliased-table.ts)
- [integration-tests/package.json](https://github.com/blade47/drizzle-orm/blob/main/integration-tests/package.json)
- [drizzle-orm/type-tests/sqlite/set-operators.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/sqlite/set-operators.ts)
- [drizzle-orm/type-tests/kysely/index.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/kysely/index.ts)
Integration tests in this ecosystem serve as the primary validation layer for query builder correctness, type inference, and cross-dialect behavior. By exercising the Drizzle ORM against real-world query patterns, these tests ensure that the TypeScript type definitions align with the generated SQL.
The test suite is built on `vitest` and operates in a multi-dialect environment. It addresses the fundamental problem of ensuring that complex query compositions result in the correct TypeScript interface shapes, aligning with the underlying database engine's specifications.
At a mechanistic level, these tests use assertion utilities to perform static type checks against query results. This ensures that the Drizzle type engine correctly infers types across various query clauses. The integration suite is highly configurable, allowing for conditional execution of external database tests depending on the presence of environment configuration, which is controlled centrally by the `vitest.config.ts` file.
## Test Environment Configuration
The integration test suite is orchestrated by a central `vitest.config.ts` file. This configuration manages the discovery, inclusion, and exclusion of test files across multiple database providers. It specifically implements a "skip-list" mechanism to filter out tests that require external network access or specific database instances when the environment variable `SKIP_EXTERNAL_DB_TESTS` is set.
The `defineConfig` utility is used to set global timeout limits for both hooks (`hookTimeout`) and test execution (`testTimeout`), and it explicitly forces sequential execution (`singleThread: true`) to avoid cross-pollination of state between tests that modify the same shared database schema.
Sources: [integration-tests/vitest.config.ts:1-85](https://github.com/blade47/drizzle-orm/blob/main/integration-tests/vitest.config.ts#L1-L85)
## Type Verification Mechanism
The core of the type-testing subsystem relies on static assertion utilities. This approach verifies that the TypeScript compiler's inferred type for a query result matches a manually defined, expected interface.
When a query builder statement is executed, the resulting type is compared against the expected shape. If the SQL query construction changes in a way that alters the resulting type, the assertion check fails at compile-time, providing immediate feedback.
> [!TIP]
> Use static type assertions for every complex operation to ensure that nullability flags (e.g., `| null`) are propagated correctly.
Sources: [drizzle-orm/type-tests/mysql/select.ts:47-55](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/select.ts#L47-L55), [drizzle-orm/type-tests/pg/select.ts:52-60](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/select.ts#L52-L60)
## Dynamic Query Builder Lifecycle
The tests define and validate the usage of dynamic query builders. These functions take a query builder instance as an argument and apply transformations.
The key mechanism here is the `$dynamic()` method, which allows for type-safe chaining even when the builder is passed through various helper functions. The tests ensure that after calling these helpers, the resultant promise resolves to the correctly mapped TypeScript type based on the initial query structure.
| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| Static Type Assertions | Prevents runtime errors | Longer compile times |
| Dialect-specific Tests | Ensures driver compatibility | Complex organization |
| Conditional Skip List | Faster local development | Potential for missed coverage |
Sources: [drizzle-orm/type-tests/mysql/select.ts:611-637](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/select.ts#L611-L637)
## Related
- [[Type Safety Testing]]
---
## Type Safety Testing
URL: https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/type-safety-testing
Relevant source files
The following files were used as context for generating this wiki page:
- [drizzle-orm/type-tests/mysql/tables.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/tables.ts)
- [drizzle-orm/type-tests/geldb/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/geldb/select.ts)
- [drizzle-orm/type-tests/singlestore/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/singlestore/select.ts)
- [drizzle-orm/type-tests/sqlite/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/sqlite/select.ts)
- [drizzle-orm/type-tests/mysql/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/select.ts)
- [drizzle-orm/type-tests/singlestore/tables.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/singlestore/tables.ts)
Type safety testing is a foundational component of the Drizzle ORM architecture, designed to ensure that the abstraction layer between TypeScript code and SQL databases remains strictly typed. By utilizing advanced TypeScript features such as conditional types, mapped types, and recursive type definitions, this subsystem validates that table definitions, query results, and complex SQL expressions are checked at compile time, eliminating a broad category of runtime errors associated with database schema drift or incorrect query construction.
The design relies on "test-type-level" validation, where the actual inferred types of ORM constructs are compared against expected structures using utility types. This provides immediate feedback to developers on whether changes to the core ORM logic break the contract between the database schema and the application's data models. It serves as a guardrail during the development of new features, ensuring that type inferencing for complex operations like multi-table joins or dynamic selections remains accurate and robust.
Beyond simple table definitions, this testing framework is critical for verifying how the ORM handles dialect-specific behaviors—such as MySQL's index locking algorithms, PostgreSQL-like array operations in Gel, or SQLite's view selection logic. By encoding database-specific behavior into the type system, the test suite acts as an automated documentation layer that forces the codebase to align with the specific constraints and features of each supported database dialect.
## Type Expectation and Validation
The core mechanism for type safety testing is the `Expect` utility. This utility is used to assert that the calculated type of an ORM construct matches an expected structure. These utilities are imported into every test file to verify that complex database query results or table definitions match the developer's intent at compile time.
The `Expect` utility validates type consistency. If the types do not match the target schema, the TypeScript compiler will throw an error, effectively failing the build. This mechanism turns the standard compilation process into a rigorous unit test for type definitions.
```typescript
// Standard pattern for validating table column definitions
Expect<
Equal<
{
id: number;
name_db: string;
population: number | null;
},
InferSelectModel
>
>;
```
Sources: [drizzle-orm/type-tests/mysql/tables.ts:163-169](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/tables.ts#L163-L169)
## Schema and Model Inference
This component validates that the ORM accurately infers TypeScript interfaces from database table definitions. It is responsible for checking inferred models and insertion types against hard-coded expected structures, ensuring that optional columns, nullable fields, and default values are reflected correctly in the generated output.
These tests ensure that column attributes are translated accurately. For instance, when testing a `serial` column, the validation verifies whether the type is correctly inferred as `number` or `number | null`, accounting for whether the field is marked as required or optional in the database schema.
Sources: [drizzle-orm/type-tests/mysql/tables.ts:171-177](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/tables.ts#L171-L177)
## Query Selection Validation
The query selection subsystem tests the complex type mapping that occurs when querying across multiple related tables. These tests exercise various builder methods to ensure that the resulting row object reflects the structure of the joined tables, correctly marking them as nullable when necessary (e.g., in a left join operation).
The complexity of these tests stems from the recursive nature of the result mapping, which must preserve table names and structures across deeply nested queries. By providing a full definition of the expected object structure, the tests confirm that the ORM correctly handles aliases and maintains key integrity during query construction.
```mermaid
flowchart TD
A["db.select()"] --> B["from(users)"]
B --> C["leftJoin(city, eq(users.id, city.id))"]
C --> D["{ users_table: T, city: T | null }[]"]
```
Sources: [drizzle-orm/type-tests/geldb/select.ts:50-64](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/geldb/select.ts#L50-L64)
## SQL Expression and Operator Typing
Testing for SQL expressions involves validating the result types of raw SQL queries and operator expressions. This facet ensures that `sql` tagged templates correctly propagate type information. The tests verify the `mapWith` functionality, which allows developers to override default column typing, ensuring that complex database transformations are correctly cast back into TypeScript types.
A critical aspect of this testing is checking how operators interact with columns and subqueries, ensuring that valid SQL fragments are generated while maintaining type consistency between the input column and the operator's value arguments.
Sources: [drizzle-orm/type-tests/geldb/select.ts:412-498](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/geldb/select.ts#L412-L498)
## Dialect-Specific Feature Validation
Different databases offer unique features (like MySQL's table indexing locking options or SingleStore's vector types), and the type testing subsystem treats these as distinct facets. This involves validating that specific methods—like index locking configurations—are only available on compatible objects.
The framework uses TypeScript's `ts-expect-error` to ensure that invalid combinations of database settings or methods are rejected by the compiler, creating a negative-testing path that guarantees API misuse is caught during the development of the ORM itself.
> [!TIP]
> The use of `@ts-expect-error` is the standard way this subsystem verifies that disallowed operations are correctly restricted by the type system at compile time, providing a robust "negative testing" framework.
Sources: [drizzle-orm/type-tests/mysql/tables.ts:69-73](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/tables.ts#L69-L73)
## Custom Type System Integration
This component verifies the registration and configuration of custom database types. Custom types require a rigorous validation of their `dataType`, `toDriver`, and `fromDriver` functions to ensure that the interface remains consistent with the underlying database types.
The testing pattern involves declaring a `customType` with specific configuration objects and then asserting that the column result matches the expected internal representation of that column in the ORM's core.
| Feature | Mechanism | Benefit |
| :--- | :--- | :--- |
| **Config Validation** | `Expect<...>` | Ensures custom type config passes correct schema data |
| **Type Integrity** | `toDriver`/`fromDriver` checks | Verifies runtime conversion types match definitions |
Sources: [drizzle-orm/type-tests/mysql/tables.ts:530-561](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/mysql/tables.ts#L530-L561)
## Related
- [[Integration Tests]]
---