column types

37 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

MySQL integer type: int

The int type is a 4-byte integer with a range of -2,147,483,648 to 2,147,483,647 when signed (default), or 0 to 4,294,967,295 when unsigned. All integer types are signed by default; adding { unsigned: true } restricts the column to non-negative values only but doubles the upper range.

MySQL integer type: tinyint

The tinyint type is a 1-byte integer with a range of -128 to 127 when signed (default), or 0 to 255 when unsigned. Add { unsigned: true } to restrict to non-negative values.

MySQL integer type: smallint

The smallint type is a 2-byte integer with a range of -32,768 to 32,767 when signed (default), or 0 to 65,535 when unsigned. Add { unsigned: true } to restrict to non-negative values.

MySQL integer type: mediumint

The mediumint type is a 3-byte integer with a range of -8,388,608 to 8,388,607 when signed (default), or 0 to 16,777,215 when unsigned. Add { unsigned: true } to restrict to non-negative values.

MySQL integer type: bigint

The bigint type is an 8-byte integer with a range of -2^63 to 2^63-1 when signed (default), or 0 to 2^64-1 when unsigned. Use the mode parameter to specify 'number', 'bigint', or 'string'. Example: bigint({ mode: 'number' }), bigint({ mode: 'number', unsigned: true }), bigint({ mode: 'bigint' }), bigint({ mode: 'string' }).

MySQL floating-point type: real

The real type is a floating-point number. It can be configured with precision and scale options. Example: real({ precision: 1 }) creates real(1), and real({ precision: 1, scale: 1 }) creates real(1, 1).

MySQL decimal type configuration

The decimal type stores an exact fixed-point number. DECIMAL(M, D) stores up to M total digits with D digits after the decimal point. Maximum precision is 65 digits. Drizzle decimal options: decimal() for default, decimal({ precision: 30, mode: 'number' }) or decimal({ precision: 30, mode: 'bigint' }) to specify mode, decimal({ precision: 30, scale: 10 }) to set both precision and scale.

MySQL floating-point type: double

The double type is an 8-byte double-precision floating-point number for approximate numeric data. It can be configured with precision and scale options. Example: double({ precision: 1 }) creates double(1), and double({ precision: 1, scale: 1 }) creates double(1,1).

MySQL floating-point type: float

The float type is a 4-byte single-precision floating-point number for approximate numeric data.

MySQL serial type

The serial type is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

MySQL binary type

The binary type stores a fixed-length byte string of exactly M bytes (default M=1). The length can be specified as binary({ length: 10 }). On insert, shorter values are right-padded with 0x00 bytes to reach M bytes; on retrieval, no padding is stripped. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations.

MySQL varbinary type

The varbinary type stores a variable-length byte string of up to M bytes. The length must be specified as varbinary({ length: 2 }). There is no padding for inserts and no bytes are stripped for retrievals. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations.

MySQL blob type

The blob type is a binary large object that can hold a variable amount of data with a maximum length of 65,535 bytes (64 KB). It can be configured with mode: 'buffer' (default, inferred as Buffer) or mode: 'string' (inferred as UTF-8 encoded string).

MySQL tinyblob type

The tinyblob type is a BLOB column with a maximum length of 255 bytes. It can be configured with mode: 'buffer' (default, inferred as Buffer) or mode: 'string' (inferred as UTF-8 encoded string).

MySQL mediumblob type

The mediumblob type is a BLOB column with a maximum length of 16,777,215 bytes (16 MB). It can be configured with mode: 'buffer' (default, inferred as Buffer) or mode: 'string' (inferred as UTF-8 encoded string).

MySQL longblob type

The longblob type is a BLOB column with a maximum length of 4,294,967,295 bytes (4 GB). It can be configured with mode: 'buffer' (default, inferred as Buffer) or mode: 'string' (inferred as UTF-8 encoded string).

MySQL char type

The char type stores a fixed-length string with a length from 0 to 255 characters (default 1). When CHAR values are stored, they are right-padded with spaces to the specified length. When retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled. You can define { enum: ['value1', 'value2'] } config to infer insert and select types, but it won't check runtime values.

MySQL varchar type

The varchar type stores variable-length strings up to M characters, where M ranges from 0 to 65,535. VARCHAR values are not padded when stored. Trailing spaces are retained when values are stored and retrieved. Specify length as varchar({ length: 2 }). You can define { enum: ['value1', 'value2'] } config to infer insert and select types, but it won't check runtime values.

MySQL text type

The text type is a TEXT column with a maximum length of 65,535 characters (64 KB). You can define { enum: ['value1', 'value2'] } config to infer insert and select types, but it won't check runtime values.

MySQL tinytext type

The tinytext type is a TEXT column with a maximum length of 255 characters. You can define { enum: ['value1', 'value2'] } config to infer insert and select types, but it won't check runtime values.

MySQL mediumtext type

The mediumtext type is a TEXT column with a maximum length of 16,777,215 characters (16 MB). You can define { enum: ['value1', 'value2'] } config to infer insert and select types, but it won't check runtime values.

MySQL longtext type

The longtext type is a TEXT column with a maximum length of 4,294,967,295 characters (4 GB). You can define { enum: ['value1', 'value2'] } config to infer insert and select types, but it won't check runtime values.

MySQL boolean type

The boolean type is a synonym for TINYINT(1). A value of 0 is considered false, and non-zero values are considered true.

MySQL date type

The date type stores a date value in 'YYYY-MM-DD' format with a range of '1000-01-01' to '9999-12-31'. You can specify infer mode: date({ mode: 'date' }) to infer as Date, or date({ mode: 'string' }) to infer as string.

MySQL datetime type

The datetime type stores a date and time value in 'YYYY-MM-DD hh:mm:ss' format with a range of '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. It supports fractional seconds up to 6 digits. Configuration options: datetime({ mode: 'date' | 'string' }), datetime({ fsp: 0..6 }). Example: datetime({ mode: 'date', fsp: 6 }) creates datetime(6).

MySQL time type

The time type stores a time value in 'hh:mm:ss' format with a range of '-838:59:59' to '838:59:59'. It can represent time of day or elapsed time and supports fractional seconds up to 6 digits. Use time({ fsp: 6 }) to configure fractional seconds precision, which creates time(6).

MySQL year type

The year type is a 1-byte type for year values in YYYY format with a range of 1901 to 2155 and 0000.

MySQL timestamp type

The timestamp type stores a date and time value in 'YYYY-MM-DD hh:mm:ss' format with a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. It supports fractional seconds up to 6 digits. Configuration options: timestamp({ mode: 'date' | 'string' }), timestamp({ fsp: 0..6 }). Example: timestamp({ mode: 'date', fsp: 6 }) creates timestamp(6). Use timestamp().defaultNow() to set DEFAULT (now()).

MySQL json type

The json type is a native JSON data type that enables efficient access to data in JSON documents. JSON documents are automatically validated and stored in an optimized binary format that permits quick read access to document elements. Use .$type<..>() to specify the JSON object structure, e.g., json().$type<{ foo: string }>() or json().$type<string[]>(). The $type parameter provides compile time protection for default values, insert and select schemas but won't check runtime values.

MySQL enum type

The enum type is a string object with a value chosen from a list of permitted values enumerated at table creation time. Use mysqlEnum(['value1', 'value2', 'value3']) to define the allowed values.

$type method for customizing column data types

Every column builder has a .$type() method that allows you to customize the data type of the column. This is useful for unknown or branded types. Example: int().$type<UserId>() where UserId = number & { __brand: 'user_id' }, or json().$type<Data>() where Data = { foo: string; bar: number }.

NOT NULL constraint in MySQL

The NOT NULL constraint dictates that the associated column may not contain a NULL value. Use .notNull() on a column definition to apply this constraint.

DEFAULT clause in MySQL columns

The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided during INSERT. If there is no explicit DEFAULT clause, the default value is NULL. An explicit DEFAULT clause may specify NULL, a string constant, a blob constant, a signed-number, or any constant expression in parentheses. Use .default(value) or .default(sql`expression`) to set defaults.

$defaultFn and $default for runtime column defaults

$defaultFn() and $default() are aliases for generating defaults at runtime and using these values in all insert queries. These functions can utilize implementations such as uuid, cuid, cuid2, and others. This value does not affect drizzle-kit behavior; it is only used at runtime in drizzle-orm. Example: varchar({ length: 128 }).$defaultFn(() => createId()).

$onUpdate and $onUpdateFn for runtime column updates

$onUpdate() and $onUpdateFn() are aliases for generating defaults at runtime and using these values in all update queries. 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. This value does not affect drizzle-kit behavior; it is only used at runtime in drizzle-orm.

PRIMARY KEY constraint in MySQL

Use .primaryKey() on a column definition to set it as the primary key. This applies both the PRIMARY KEY constraint and NOT NULL.

AUTO_INCREMENT in MySQL

Use .autoincrement() on a column definition to apply the AUTO_INCREMENT constraint, which automatically generates a unique incrementing value for each new row inserted.

Give your agent this brain