We have native support for all of them, yet if that’s not enough for you, feel free to create custom types.
integer
integerintint4
Signed 4-byte integer
If you need integer autoincrement please refer to serial.
smallint
smallintint2
Small-range signed 2-byte integer
If you need smallint autoincrement please refer to smallserial.
bigint
bigintint8
Signed 8-byte integer
If you need bigint autoincrement please refer to bigserial.
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.
serial
serialserial4
Auto incrementing 4-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).
For more info please refer to the official PostgreSQL docs.
smallserial
smallserialserial2
Auto incrementing 2-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).
For more info please refer to the official PostgreSQL docs.
bigserial
bigserialserial8
Auto incrementing 8-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).
For more info please refer to the official PostgreSQL docs.
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.
boolean
PostgreSQL provides the standard SQL type boolean.
For more info please refer to the official PostgreSQL docs.
text
text
Variable-length(unlimited) character string.
For more info please refer to the official PostgreSQL docs.
You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.
varchar
character varying(n)varchar(n)
Variable-length character string, can store strings up to n characters (not bytes).
For more info please refer to the official PostgreSQL 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 PostgreSQL docs.
char
character(n)char(n)
Fixed-length, blank padded character string, can store strings up to n characters(not bytes).
For more info please refer to the official PostgreSQL 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 PostgreSQL docs.
numeric
numericdecimal
Exact numeric of selectable precision. Can store numbers with a very large number of digits, up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.
For more info please refer to the official PostgreSQL docs.
For more info please refer to the official PostgreSQL docs.
You can specify .$type<..>() for json object inference, it won’t check runtime values.
It provides compile time protection for default values, insert and select schemas.
jsonb
jsonb
Binary JSON data, decomposed.
For more info please refer to the official PostgreSQL docs.
You can specify .$type<..>() for json object inference, it won’t check runtime values.
It provides compile time protection for default values, insert and select schemas.
time
timetimetztime with timezonetime without timezone
Time of day with or without time zone.
For more info please refer to the official PostgreSQL docs.
timestamp
timestamptimestamptztimestamp with time zonetimestamp without time zone
Date and time with or without time zone.
For more info please refer to the official PostgreSQL docs.
You can specify either date or string infer modes:
The string mode does not perform any mappings for you. This mode was added to Drizzle ORM to provide developers
with the possibility to handle dates and date mappings themselves, depending on their needs.
Drizzle will pass raw dates as strings to and from the database, so the behavior should be as predictable as possible
and aligned 100% with the database behavior
The date mode is the regular way to work with dates. Drizzle will take care of all mappings between the database and the JS Date object
ℹ️
How mapping works for timestamp and timestamp with timezone:
As PostgreSQL docs stated:
In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication.
That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT).
An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone.
If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter,
and is converted to UTC using the offset for the timezone zone.
So for timestamp with timezone you will get back string converted to a timezone set in you Postgres instance.
You can check timezone using this sql query:
date
date
Calendar date (year, month, day)
For more info please refer to the official PostgreSQL docs.
You can specify either date or string infer modes:
interval
interval
Time span
For more info please refer to the official PostgreSQL docs.
enum
enumenumerated types
Enumerated (enum) types are data types that comprise a static, ordered set of values.
They are equivalent to the enum types supported in a number of programming languages.
An example of an enum type might be the days of the week, or a set of status values for a piece of data.
For more info please refer to the official PostgreSQL docs.
Customizing column 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:
Constraints & defaults
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.
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.
These functions can assist you in utilizing various implementations such as uuid, cuid, cuid2, and many more.
ℹ️
Note: This value does not affect the drizzle-kit behavior, it is only used at runtime in drizzle-orm
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
Not null
NOT NULL constraint dictates that the associated column may not contain a NULL value.
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.