customType basic syntax example: text
A custom text type in MSSQL is defined as: const customText = customType<{ data: string }>({ dataType() { return 'text'; } });
mssql/column-types
72 notes in this subject, read out of this brain and free to use. This is page 2 of 2.
customType with driver data transforms example: datetime2
A custom datetime2 type with configuration and fromDriver transform: const customTimestamp = customType<{ data: Date; driverData: string; config: { precision?: number }; configRequired: true }>({ dataType(config) { const precision = typeof config.precision !== 'undefined' ? ` (${config.precision})` : ''; return `datetime2${precision}`; }, fromDriver(value: string): Date { return new Date(value); } });
customType column usage with modifiers
Custom types work with standard column modifiers like primaryKey(), notNull(), and can be used in table definitions the same as built-in types.
CustomTypeValues.data property
The 'data' property defines the TypeScript type of the column after selecting or inserting. Use 'data: string' for text/varchar types and 'data: number' for integer types. This property is required.
CustomTypeValues.driverOutput property
The 'driverOutput' property is a type helper representing what type the database driver returns for a specific database data type. This is optional and only needed if the driver's output type differs from driverData. It defaults to driverData.
CustomTypeValues.driverData property
The 'driverData' property is a type helper representing what type the database driver accepts for a specific database data type.
CustomTypeValues.jsonData property
The 'jsonData' property is a type helper representing what type a field returns after being aggregated to JSON.
CustomTypeValues.config property
The 'config' property defines what config type should be used for CustomTypeParams 'dataType' generation. It is of type Record<string, any>.
CustomTypeValues.configRequired property
The 'configRequired' property is a boolean that specifies whether the config argument should be required or not. It defaults to false.
generatedAlwaysAs() with persisted mode
To create a persisted computed column in Drizzle, use `generatedAlwaysAs()` with `{ mode: "persisted" }` as the second argument. This instructs SQL Server to persist the computed value on disk. The example shows a total column calculated as `${orders.quantity} * ${orders.unitPrice}` with `{ mode: "persisted" }`.
MSSQL computed columns overview
MSSQL supports two types of computed columns: virtual computed columns that are calculated when queried without occupying storage space, and persisted computed columns that store the computed value on disk and can be indexed when the expression is deterministic.
generatedAlwaysAs() for virtual computed columns
In Drizzle, specify `.generatedAlwaysAs()` on a column and pass a SQL expression to create a virtual computed column. Virtual computed columns are calculated when queried and do not occupy storage space. The example shows creating a fullName column by concatenating firstName and lastName columns using `generatedAlwaysAs((): SQL => sql\`concat(${users.firstName}, ' ', ${users.lastName})\`)`.