MSSQL column types

import Section from '@mdx/Section.astro'; import Callout from '@mdx/Callout.astro'; import Npm from '@mdx/Npm.astro';

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

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 </Callout>

int

Signed 4-byte integer

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

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

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

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

smallint

smallint

Small-range signed 2-byte integer

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

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

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

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

tinyint

tinyint

Small-range signed 1-byte integer

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

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

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

```sql
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.

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

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

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

```sql
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

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

```sql
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.

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

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

```sql
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.

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

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

```sql
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.

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"] }),

```sql
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.

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

```sql
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.

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

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

```sql
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.

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

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

```sql
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.

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

```sql
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

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

```sql
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.

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

```sql
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.

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

```sql
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.

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

```sql
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.

const table = mssqlTable('table', { time1: time(), time2: time({ mode: 'string' }), time3: time({ precision: 6 }), time4: time({ precision: 6, mode: 'date' }) });

```sql
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.

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

```sql
CREATE TABLE [table] (
	[date] 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.

<Callout title='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.
</Callout>

For more info please refer to the official MSSQL **[docs.](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver17#description)**
<Section>
```typescript
import { datetime, mssqlTable } from "drizzle-orm/mssql-core";

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

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

// 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.](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver17#datetime2-description)**
<Section>
```typescript
import { datetime2, mssqlTable } from "drizzle-orm/mssql-core";

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

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

// 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.](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver17#datetimeoffset-description)**
<Section>
```typescript
import { datetimeoffset, mssqlTable } from "drizzle-orm/mssql-core";

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

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

// 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:
```ts
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.

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

```sql
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.

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.

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

Not null

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

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

```sql
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.

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

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