MSSQL column types

WARNING

This page explains concepts available on drizzle versions 1.0.0-beta.2 and higher.

npm

yarn

pnpm

bun

npm i drizzle-orm@beta
npm i drizzle-kit@beta -D

yarn add drizzle-orm@beta
yarn add drizzle-kit@beta -D

pnpm add drizzle-orm@beta
pnpm add drizzle-kit@beta -D

bun add drizzle-orm@beta
bun add drizzle-kit@beta -D

We have native support for all of them, yet if that’s not enough for you, feel free to create custom types.

important

All examples in this part of the documentation do not use database column name aliases, and column names are generated from TypeScript keys.

You can use database aliases in column names if you want, and you can also use the casing parameter to define a mapping strategy for Drizzle.

You can read more about it here

int

Signed 4-byte integer

import { int, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
	int: int()
});

CREATE TABLE [table] (
	[int] int
);

import { sql } from "drizzle-orm";
import { int, mssqlTable } from "drizzle-orm/mssql-core";

export const table = pgTable('table', {
	int1: int().default(10),
});

CREATE TABLE [table] (
	[int1] int DEFAULT 10
);

smallint

smallint

Small-range signed 2-byte integer

import { smallint, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
	smallint: smallint()
});

CREATE TABLE [table] (
	[smallint] smallint
);

import { sql } from "drizzle-orm";
import { smallint, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
	smallint1: smallint().default(10),
});

CREATE TABLE [table] (
	[smallint1] smallint DEFAULT 10
);

tinyint

tinyint

Small-range signed 1-byte integer

import { tinyint, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
	tinyint: tinyint()
});

CREATE TABLE [table] (
	[tinyint] tinyint
);

import { sql } from "drizzle-orm";
import { tinyint, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
	tinyint1: tinyint().default(10),
});

CREATE TABLE [table] (
	[tinyint1] tinyint DEFAULT 10
);

bigint

bigint

Signed 8-byte integer

If you’re expecting values above 2^31 but below 2^53, you can utilise mode: 'number' and deal with javascript number as opposed to bigint.

import { bigint, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
	bigint: bigint({ mode: 'number' })
});

// will be inferred as `number`
bigint: bigint({ mode: 'number' })

// will be inferred as `bigint`
bigint: bigint({ mode: 'bigint' })

// will be inferred as `string`
bigint: bigint({ mode: 'string' })

CREATE TABLE [table] (
	[bigint] bigint
);

import { sql } from "drizzle-orm";
import { bigint, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
	bigint1: bigint({ mode: 'number' }).default(10)
});

CREATE TABLE [table] (
	[bigint1] bigint DEFAULT 10
);

---

bit

An integer data type that can take a value of 1, 0, or NULL

Drizzle will accept true or false as values instead of 1 and 0

import { bit, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
	bit: bit()
});

CREATE TABLE [table] (
	[bit] bit
);

---

text

text
Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31 - 1 (2,147,483,647)

For more info please refer to the official MSSQL docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.

import { text, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
  text: text()
});

// will be inferred as text: "value1" | "value2" | null
text: text({ enum: ["value1", "value2"] })

CREATE TABLE [table] (
	[text] text
);

ntext

text
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823).

For more info please refer to the official MSSQL docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.

import { text, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
  ntext: ntext()
});

// will be inferred as text: "value1" | "value2" | null
ntext: ntext({ enum: ["value1", "value2"] })

CREATE TABLE [table] (
	[ntext] ntext
);

varchar

varchar(n|max)
Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000

For more info please refer to the official MSSQL docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.

The length parameter is optional according to MSSQL docs.

import { varchar, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
  varchar1: varchar(),
  varchar2: varchar({ length: 256 }),
  varchar3: varchar({ length: 'max' })
});

// will be inferred as text: "value1" | "value2" | null
varchar: varchar({ enum: ["value1", "value2"] }),

CREATE TABLE [table] (
	[varchar1] varchar,
	[varchar2] varchar(256),
	[varchar3] varchar(max)
);

nvarchar

nvarchar(n|max)
Variable-size string data. The value of n defines the string size in byte-pairs

For more info please refer to the official MSSQL docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.

The length parameter is optional according to MSSQL docs.

import { nvarchar, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
  nvarchar1: nvarchar(),
  nvarchar2: nvarchar({ length: 256 }),
});

// will be inferred as text: "value1" | "value2" | null
nvarchar: nvarchar({ enum: ["value1", "value2"] }),

// will be inferred as `json`
nvarchar: nvarchar({ mode: 'json' })

CREATE TABLE [table] (
	[nvarchar1] nvarchar,
	[nvarchar2] nvarchar(256)
);

char

char(n)

Fixed-size string data. n defines the string size in bytes and must be a value from 1 through 8,000

For more info please refer to the official MSSQL docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.

The length parameter is optional according to MSSQL docs.

import { char, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
  char1: char(),
  char2: char({ length: 256 }),
});

// will be inferred as text: "value1" | "value2" | null
char: char({ enum: ["value1", "value2"] }),

CREATE TABLE [table] (
	[char1] char,
	[char2] char(256)
);

nchar

nchar(n)

Fixed-size string data. n defines the string size in byte-pairs, and must be a value from 1 through 4,000

For more info please refer to the official MSSQL docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.

The length parameter is optional according to MSSQL docs.

import { nchar, mssqlTable } from "drizzle-orm/mssql-core";

export const table = mssqlTable('table', {
  nchar1: nchar(),
  nchar2: nchar({ length: 256 }),
});

// will be inferred as text: "value1" | "value2" | null
nchar: nchar({ enum: ["value1", "value2"] }),

CREATE TABLE [table] (
	[nchar1] nchar,
	[nchar2] nchar(256)
);

---

binary

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.

import { binary, mssqlTable } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	binary: binary(),
	binary1: binary({ length: 256 })
});

CREATE TABLE [table] (
	[binary] binary,
	[binary1] binary(256)
);

varbinary

Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes

import { varbinary, mssqlTable } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	varbinary: varbinary(),
	varbinary1: varbinary({ length: 256 }),
	varbinary2: varbinary({ length: 'max' })
});

CREATE TABLE [table] (
	[varbinary] varbinary,
	[varbinary1] varbinary(256),
	[varbinary2] varbinary(max)
);

---

numeric

numeric

Fixed precision and scale numbers. When maximum precision is used, valid values are from -10^38 + 1 through 10^38 - 1

For more info please refer to the official MSSQL docs.

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 })
//   numericNum: numeric({ mode: 'number' }),
//   numericBig: numeric({ mode: 'bigint' }),
});

CREATE TABLE [table] (
	[numeric1] numeric,
	[numeric2] numeric(100),
	[numeric3] numeric(100, 20)
);

decimal

An alias of numeric.

real

The ISO synonym for real is float(24).

For more info please refer to the official MSSQL docs.

import { sql } from "drizzle-orm";
import { real, mssqlTable } from "drizzle-orm/mssql-core";  

const table = mssqlTable('table', {
	real1: real(),
	real2: real().default(10.10)
});

CREATE TABLE [table] (
	[real1] real,
	[real2] real default 10.10
);

float

float [ (n) ] Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.

For more info please refer to the official MSSQL docs.

import { sql } from "drizzle-orm";
import { float, mssqlTable } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	float1: float(),
	float1: float({ precision: 16 })
});

CREATE TABLE [table] (
	[float1] float,
	[float2] float(16)
);

---

time

time

Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

For more info please refer to the official MSSQL docs.

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' })
});

CREATE TABLE [table] (
	[time1] time,
	[time2] time,
	[time3] time(6),
	[time4] time(6)
);

date

date

Calendar date (year, month, day)

For more info please refer to the official MSSQL docs.

import { date, mssqlTable } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	date: date(),
});

CREATE TABLE [table] (
	[date] date
);

You can specify either date or string infer modes:

// will infer as date
date: date({ mode: "date" }),

// will infer as string
date: date({ mode: "string" }),

datetime

datetime

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

MSSQL docs

Avoid using datetime for new work. Instead, use the time, date, datetime2, and datetimeoffset data types. These types align with the SQL Standard, and are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

For more info please refer to the official MSSQL docs.

import { datetime, mssqlTable } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	datetime: datetime(),
});

CREATE TABLE [table] (
	[datetime] datetime
);

You can specify either date or string infer modes:

// will infer as date
datetime: datetime({ mode: "date" }),

// will infer as string
datetime: datetime({ mode: "string" }),

datetime2

datetime2

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

For more info please refer to the official MSSQL docs.

import { datetime2, mssqlTable } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	datetime2: datetime2(),
});

CREATE TABLE [table] (
	[datetime2] datetime2
);

You can specify either date or string infer modes:

// will infer as date
datetime2: datetime2({ mode: "date" }),

// will infer as string
datetime2: datetime2({ mode: "string" }),

datetimeoffset

datetimeoffset

Defines a date that is combined with a time of a day based on a 24-hour clock like datetime2, and adds time zone awareness based on Coordinated Universal Time (UTC).

For more info please refer to the official MSSQL docs.

import { datetimeoffset, mssqlTable } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	datetimeoffset: datetimeoffset(),
});

CREATE TABLE [table] (
	[datetimeoffset] datetimeoffset
);

You can specify either date or string infer modes:

// will infer as date
datetimeoffset: datetimeoffset({ mode: "date" }),

// will infer as string
datetimeoffset: datetimeoffset({ mode: "string" }),

---

Customizing data type

Every column builder has a .$type() method, which allows you to customize the data type of the column.

This is useful, for example, with unknown or 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>(),
});

Default value

The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL.

An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses.

import { sql } from "drizzle-orm";
import { int, mssqlTable, text } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	integer: integer().default(42),
	text: text().default('text'),
});

CREATE TABLE [table] (
	[integer1] integer DEFAULT 42,
	[text] text DEFAULT 'text',
);

When using $default() or $defaultFn(), which are simply different aliases for the same function, you can generate defaults at runtime and use these values in all insert queries.

Note: This value does not affect the drizzle-kit behavior, it is only used at runtime in drizzle-orm

import { text, mssqlTable } from "drizzle-orm/mssql-core";
import { createId } from '@paralleldrive/cuid2';

const table = mssqlTable('table', {
	id: text().$defaultFn(() => createId()),
});

When using $onUpdate() or $onUpdateFn(), which are simply different aliases for the same function, you can generate defaults at runtime and use these values in all update queries.

Adds a dynamic update value to the column. The function will be called when the row is updated, and the returned value will be used as the column value if none is provided. If no default (or $defaultFn) value is provided, the function will be called when the row is inserted as well, and the returned value will be used as the column value.

Note: This value does not affect the drizzle-kit behavior, it is only used at runtime in drizzle-orm

import { int, timestamp, text, mssqlTable } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	updateCounter: int().default(sql`1`).$onUpdateFn((): SQL => sql`${table.update_counter} + 1`),
	updatedAt: timestamp({ mode: 'date', precision: 3 }).$onUpdate(() => new Date()),
    	alwaysNull: text().$type<string | null>().$onUpdate(() => null),
});

Not null

NOT NULL constraint dictates that the associated column may not contain a NULL value.

import { int, mssqlTable } from "drizzle-orm/mssql-core";

const table = mssqlTable('table', {
	int: int().notNull(),
});

CREATE TABLE [table] (
	[int] int NOT NULL
);

Primary key

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.

import { int, mssqlTable } from "drizzle-orm/mssql-core";

const table = pgTable('table', {
	id: int().primaryKey(),
});

CREATE TABLE [table] (
	[id] int PRIMARY KEY
);

PostgreSQL

MySQL

SQLite

SingleStore

MSSQL

CockroachDB

MSSQL column types

int

smallint

tinyint

bigint

---

bit

---

text

ntext

varchar

nvarchar

char

nchar

---

binary

varbinary

---

numeric

decimal

real

float

---

time

date

datetime

datetime2

datetimeoffset

---

Customizing data type

Default value

Not null

Primary key