mssql/column-types

72 notes in this subject, read out of this brain and free to use. This is page 1 of 2.

Example: MSSQL real with default

import { sql } from "drizzle-orm"; import { real, mssqlTable } from "drizzle-orm/mssql-core"; const table = mssqlTable('table', { real1: real(), real2: real().default(10.10) }); Creates: CREATE TABLE [table] ( [real1] real, [real2] real default 10.10 );

Example: MSSQL float with precision

import { sql } from "drizzle-orm"; import { float, mssqlTable } from "drizzle-orm/mssql-core"; const table = mssqlTable('table', { float1: float(), float2: float({ precision: 16 }) }); Creates: CREATE TABLE [table] ( [float1] float, [float2] float(16) );

Example: MSSQL primaryKey constraint

import { int, mssqlTable } from "drizzle-orm/mssql-core"; const table = mssqlTable('table', { id: int().primaryKey(), }); Creates: CREATE TABLE [table] ( [id] int PRIMARY KEY );

Example: MSSQL notNull constraint

import { int, mssqlTable } from "drizzle-orm/mssql-core"; const table = mssqlTable('table', { int: int().notNull(), }); Creates: CREATE TABLE [table] ( [int] int NOT NULL );

MSSQL bigint type and modes

The bigint type represents a signed 8-byte integer. It supports three modes via the mode parameter: 'number' infers as JavaScript number (use for values above 2^31 but below 2^53), 'bigint' infers as bigint (default), and 'string' infers as string. Import from drizzle-orm/mssql-core.

MSSQL text type

The text type represents variable-length non-Unicode data in the code page of the server with a maximum string length of 2^31 - 1 (2,147,483,647). It supports an enum parameter for type inference, but enum values are not validated at runtime. Import from drizzle-orm/mssql-core.

MSSQL char type

The char type represents fixed-size string data where n defines the string size in bytes and must be a value from 1 through 8,000. The length parameter is optional. It supports an enum parameter for type inference, but enum values are not validated at runtime. Import from drizzle-orm/mssql-core.

MSSQL binary type

The binary type represents fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes. The length parameter is optional. Import from drizzle-orm/mssql-core.

MSSQL numeric type

The numeric type represents fixed precision and scale numbers. When maximum precision is used, valid values are from -10^38 + 1 through 10^38 - 1. It supports optional precision and scale parameters. Import from drizzle-orm/mssql-core.

MSSQL decimal type

The decimal type is an alias of numeric. Import from drizzle-orm/mssql-core.

MSSQL real type

The real type is the ISO synonym for float(24). It can be used with .default() to set a default value. Import from drizzle-orm/mssql-core.

MSSQL float type

The float type supports an optional precision parameter n, which must be a value between 1 and 53 (default is 53). The parameter dictates the precision and storage size of the mantissa. Import from drizzle-orm/mssql-core.

MSSQL time type

The time type defines a time of day without time zone awareness based on a 24-hour clock. It supports mode parameter ('string' or 'date') and optional precision parameter. Import from drizzle-orm/mssql-core.

MSSQL date type

The date type represents a calendar date (year, month, day). It supports mode parameter which can be 'date' (infers as date) or 'string' (infers as string). Import from drizzle-orm/mssql-core.

NOT NULL constraint in MSSQL

The NOT NULL constraint dictates that a column may not contain a NULL value. Use .notNull() method on column definition.

Example: MSSQL int column with default

import { sql } from "drizzle-orm"; import { int, mssqlTable } from "drizzle-orm/mssql-core"; export const table = mssqlTable('table', { int1: int().default(10), }); Creates: CREATE TABLE [table] ( [int1] int DEFAULT 10 );

Example: MSSQL bigint with different modes

import { bigint, mssqlTable } from "drizzle-orm/mssql-core"; export const table = mssqlTable('table', { bigint: bigint({ mode: 'number' }) // infers as number, bigint: bigint({ mode: 'bigint' }) // infers as bigint, bigint: bigint({ mode: 'string' }) // infers as string });

Example: MSSQL nvarchar with json mode

import { nvarchar, mssqlTable } from "drizzle-orm/mssql-core"; export const table = mssqlTable('table', { nvarchar: nvarchar({ mode: 'json' }) // infers as json });

Example: MSSQL time with precision and mode

import { time, mssqlTable } from "drizzle-orm/mssql-core"; const table = mssqlTable('table', { time1: time(), time2: time({ mode: 'string' }), time3: time({ precision: 6 }), time4: time({ precision: 6, mode: 'date' }) }); Creates: CREATE TABLE [table] ( [time1] time, [time2] time, [time3] time(6), [time4] time(6) );

Example: MSSQL $type() for branded types

type UserId = number & { __brand: 'user_id' }; type Data = { foo: string; bar: number; }; const users = mssqlTable('users', { id: int().$type<UserId>().primaryKey(), jsonField: json().$type<Data>(), });

Example: MSSQL $onUpdateFn() with dynamic values

import { int, datetime2, text, mssqlTable } from "drizzle-orm/mssql-core"; const table = mssqlTable('table', { updateCounter: int().default(sql`1`).$onUpdateFn((): SQL => sql`${table.updateCounter} + 1`), updatedAt: datetime2({ mode: 'date', precision: 3 }).$onUpdate(() => new Date()), alwaysNull: text().$type<string | null>().$onUpdate(() => null), });

Example: MSSQL binary with length

import { binary, mssqlTable } from "drizzle-orm/mssql-core"; const table = mssqlTable('table', { binary: binary(), binary1: binary({ length: 256 }) }); Creates: CREATE TABLE [table] ( [binary] binary, [binary1] binary(256) );

Example: MSSQL varbinary with length options

import { varbinary, mssqlTable } from "drizzle-orm/mssql-core"; const table = mssqlTable('table', { varbinary: varbinary(), varbinary1: varbinary({ length: 256 }), varbinary2: varbinary({ length: 'max' }) }); Creates: CREATE TABLE [table] ( [varbinary] varbinary, [varbinary1] varbinary(256), [varbinary2] varbinary(max) );

Example: MSSQL numeric with precision and scale

import { numeric, mssqlTable } from "drizzle-orm/mssql-core"; export const table = mssqlTable('table', { numeric1: numeric(), numeric2: numeric({ precision: 100 }), numeric3: numeric({ precision: 100, scale: 20 }) }); Creates: CREATE TABLE [table] ( [numeric1] numeric, [numeric2] numeric(100), [numeric3] numeric(100, 20) );

MySQL unique case-insensitive email with uniqueIndex

To implement unique and case-insensitive email handling in MySQL, create a unique index on the lowercased email column using uniqueIndex('emailUniqueIndex').on(lower(table.email)). Define a custom lower function that returns sql`(lower(${email}))` with parentheses around the lower expression, where email is of type AnyMySqlColumn. Functional indexes are supported in MySQL starting from version 8.0.13. The generated migration creates a CONSTRAINT with UNIQUE((lower(`email`))).

MSSQL arktype data types reference

MSSQL data type mappings for arktype follow the MSSQL column builders. The full reference is available on the MSSQL column types documentation page.

real column type

The real column type represents a floating-point data type. Import from drizzle-orm/mssql-core. ISO synonym for float(24). Can be used with .default() to specify a default floating-point value.

int column type

The int column type represents a signed 4-byte integer. Import from drizzle-orm/mssql-core. Can be used with .default() to specify a default value.

tinyint column type

The tinyint column type represents a small-range signed 1-byte integer. Import from drizzle-orm/mssql-core. Can be used with .default() to specify a default value.

bigint column type with modes

The bigint column type represents a signed 8-byte integer. Import from drizzle-orm/mssql-core. It accepts a mode parameter: 'number' for values between 2^31 and 2^53 (inferred as JavaScript number), 'bigint' for full bigint range (inferred as bigint), or 'string' (inferred as string). Default mode is 'bigint'.

bit column type

The bit column type represents an integer data type that can take a value of 1, 0, or NULL. Drizzle accepts true or false as values instead of 1 and 0. Import from drizzle-orm/mssql-core.

text column type

The text column type represents variable-length non-Unicode data with a maximum string length of 2^31 - 1 (2,147,483,647 characters). Import from drizzle-orm/mssql-core. Supports { enum: ["value1", "value2"] } config to infer insert and select types, but does not enforce runtime validation.

ntext column type

The ntext column type represents variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823 characters). Import from drizzle-orm/mssql-core. Supports { enum: ["value1", "value2"] } config to infer insert and select types, but does not enforce runtime validation.

varchar column type with length and max

The varchar column type represents variable-size string data. Import from drizzle-orm/mssql-core. The length parameter is optional and can be a value from 1 through 8,000 bytes, or the string 'max'. When no length is specified, it renders as varchar. When length: 'max' is used, it renders as varchar(max). Supports { enum: ["value1", "value2"] } config to infer insert and select types, but does not enforce runtime validation.

nvarchar column type with length and mode

The nvarchar column type represents variable-size Unicode string data where the length value defines the string size in byte-pairs. Import from drizzle-orm/mssql-core. The length parameter is optional. Supports mode: 'json' to infer the column as JSON type. Supports { enum: ["value1", "value2"] } config to infer insert and select types, but does not enforce runtime validation.

char column type with length

The char column type represents fixed-size string data. Import from drizzle-orm/mssql-core. The length parameter is optional and must be a value from 1 through 8,000 bytes. When no length is specified, it renders as char(1). Supports { enum: ["value1", "value2"] } config to infer insert and select types, but does not enforce runtime validation.

nchar column type with length

The nchar column type represents fixed-size Unicode string data where the length value defines the string size in byte-pairs. Import from drizzle-orm/mssql-core. The length parameter is optional and must be a value from 1 through 4,000. When no length is specified, it renders as nchar(1). Supports { enum: ["value1", "value2"] } config to infer insert and select types, but does not enforce runtime validation.

binary column type with length

The binary column type represents fixed-length binary data. Import from drizzle-orm/mssql-core. The length parameter is optional and can be a value from 1 through 8,000 bytes, representing the storage size.

varbinary column type with length and max

The varbinary column type represents variable-length binary data. Import from drizzle-orm/mssql-core. The length parameter is optional and can be a value from 1 through 8,000 bytes, or the string 'max' to indicate a maximum storage size of 2^31-1 bytes.

numeric column type with precision and scale

The numeric column type represents fixed precision and scale numbers. Import from drizzle-orm/mssql-core. Valid values range from -10^38 + 1 through 10^38 - 1 when maximum precision is used. Accepts optional precision parameter (default varies), and optional scale parameter for the number of decimal places.

decimal column type

The decimal column type is an alias for numeric in MSSQL. Import from drizzle-orm/mssql-core. It represents fixed precision and scale numbers with the same parameters and behavior as numeric.

float column type with precision

The float column type represents a floating-point data type. Import from drizzle-orm/mssql-core. Accepts optional precision parameter (n) between 1 and 53, which specifies the number of bits used to store the mantissa in scientific notation and dictates precision and storage size. Default precision is 53.

time column type with precision and mode

The time column type defines a time of day without time zone awareness on a 24-hour clock. Import from drizzle-orm/mssql-core. Accepts optional precision parameter (0-7) for fractional seconds. Accepts mode parameter: 'string' to infer as string, or 'date' to infer as Date object.

date column type with mode

The date column type represents a calendar date (year, month, day). Import from drizzle-orm/mssql-core. Accepts mode parameter: 'date' to infer as Date object, or 'string' to infer as string.

datetime column type with mode

The datetime column type defines a date combined with a time of day with fractional seconds on a 24-hour clock. Import from drizzle-orm/mssql-core. Accepts mode parameter: 'date' to infer as Date object, or 'string' to infer as string. MSSQL documentation recommends avoiding datetime for new work in favor of time, date, datetime2, or datetimeoffset.

datetime2 column type with mode

The datetime2 column type defines a date combined with a time of day on a 24-hour clock. Import from drizzle-orm/mssql-core. It is an extension of datetime with a larger date range and larger default fractional precision with optional user-specified precision. Accepts mode parameter: 'date' to infer as Date object, or 'string' to infer as string.

datetimeoffset column type with mode

The datetimeoffset column type defines a date combined with a time of day on a 24-hour clock with time zone awareness based on Coordinated Universal Time (UTC). Import from drizzle-orm/mssql-core. Accepts mode parameter: 'date' to infer as Date object, or 'string' to infer as string.

PRIMARY KEY constraint

A primary key constraint indicates that a column or group of columns can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null. Use .primaryKey() method on column builders.

Drizzle MSSQL imports location

All MSSQL column type functions and mssqlTable are imported from drizzle-orm/mssql-core.

smallint column type

The smallint column type represents a small-range signed 2-byte integer. Import from drizzle-orm/mssql-core. Can be used with .default() to specify a default value.

CustomTypeParams.dataType function

The 'dataType' function is required in CustomTypeParams. It returns a string representing the database data type (e.g., 'jsonb', 'text', 'varchar(256)', 'numeric(2,3)'). It receives a config parameter which can be undefined unless configRequired is true.

CustomTypeValues.notNull property

The 'notNull' property is a boolean. If set to true, the custom data type will be notNull by default.

CustomTypeValues.default property

The 'default' property is a boolean. If set to true, the custom data type has a default value.

customType import for MSSQL

customType is imported from 'drizzle-orm/mssql-core' for creating custom column types in MSSQL.

CustomTypeParams.toDriver function

The 'toDriver' function in CustomTypeParams is optional and transforms inputs from the desired code format to one suitable for the database driver. It takes T['data'] and returns T['driverData'] | SQL.

CustomTypeParams.fromDriver function

The 'fromDriver' function in CustomTypeParams is optional and transforms data returned by the driver to the desired column's output format. It takes the driver output type and returns T['data'].

CustomTypeParams.fromJson function

The 'fromJson' function in CustomTypeParams is optional and transforms data returned as transformed to JSON in the database to the desired format. It is used by relational queries and JSON functions. It defaults to the fromDriver function.

CustomTypeParams.forJsonSelect function

The 'forJsonSelect' function in CustomTypeParams is optional and modifies the selection of a column inside JSON functions. It takes an identifier (SQL) and sql (SQLGenerator) and returns SQL. Following types are cast to text by default: binary, varbinary, time, datetime, decimal, float, bigint.

Custom type basic usage pattern

Custom types are used by defining them as constants with customType<> generic, then using them as column definitions in table schemas. They support all standard column modifiers and function identically to built-in Drizzle ORM types.

customType basic syntax example: int

A custom int type in MSSQL is defined as: const customInt = customType<{ data: number }>({ dataType() { return 'int'; } });

Give your agent this brain