Integer column type modes
The integer() column type accepts a mode option with these values: 'number' (default), 'boolean' (stored as 0/1), 'timestamp_ms', and 'timestamp' (returns Date).
Drizzle · SQLite · all subjects
sqlite-core column types
70 notes in this subject, read out of this brain and free to use. This is page 1 of 2.
Integer primary key with auto-increment
To make an integer column a primary key with auto-increment: integer({ mode: 'number' }).primaryKey({ autoIncrement: true })
Real column type
The real() column type stores floating point values as 8-byte IEEE floating point numbers.
Text column with enum
The text() column accepts an enum option: text({ enum: ['value1', 'value2'] }). This infers insert and select types but does not check runtime values.
SQLite storage classes
SQLite has five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. Drizzle has native support for all of them.
Text column with JSON mode
The text() column supports JSON mode: text({ mode: 'json' }). You can also use .$type<T>() for type inference: text({ mode: 'json' }).$type<{ foo: string }>()
Blob column modes
The blob() column accepts these mode options: default (returns Uint8Array), 'buffer', 'bigint', and 'json'. Use text({ mode: 'json' }) instead of blob({ mode: 'json' }) because blob mode does not support SQLite JSON functions.
Blob column with JSON and type inference
The blob() column with JSON mode supports .$type<T>() for compile-time type inference: blob({ mode: 'json' }).$type<{ foo: string }>(). This does not check runtime values but provides compile-time protection for defaults, insert, and select schemas.
Boolean column type
SQLite has no native boolean type. Use integer({ mode: 'boolean' }) to operate with boolean values in code while Drizzle stores them as 0 and 1 in the database.
Bigint column type
SQLite has no native bigint type. Use blob({ mode: 'bigint' }) to work with BigInt instances in code while Drizzle stores them as blob values in the database.
Numeric column type modes
The numeric() column accepts a mode option with these values: default, 'number', 'bigint', and 'string'.
Custom data type with .$type()
Every column builder has a .$type<T>() method to customize the data type. This is useful for unknown or branded types: integer().$type<UserId>() or blob().$type<Data>()
NOT NULL constraint
Use .notNull() on a column to add a NOT NULL constraint, which dictates that the column may not contain a NULL value.
Default value with literal
Use .default(value) to specify a default value: integer().default(42). The default is NULL if not specified.
Default value with SQL expression
Use .default(sql`...`) to specify a default value as a SQL expression: integer().default(sql`(abs(42))`)
Default value with CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP
Use special keywords as defaults: text().default(sql`(CURRENT_TIME)`), text().default(sql`(CURRENT_DATE)`), or text().default(sql`(CURRENT_TIMESTAMP)`)
$defaultFn() runtime default generation
$defaultFn() and $default() are aliases that generate defaults at runtime for all insert queries. They do not affect drizzle-kit behavior. Example: text().$defaultFn(() => createId())
$onUpdateFn() runtime update generation
$onUpdateFn() and $onUpdate() are aliases that generate defaults at runtime for all update queries. If no default is provided, the function is also called on insert. They do not affect drizzle-kit behavior. Example: text().$type<string | null>().$onUpdate(() => null)
customType imported from drizzle-orm/sqlite-core
SQLite exposes customType from drizzle-orm/sqlite-core. This function is used to define custom column types with custom SQL data types and JS/DB value transforms.
CustomTypeValues interface properties
CustomTypeValues interface defines the type helper structure for custom columns. Properties include: data (required, unknown type, the JS type after selecting or inserting), driverData (optional, unknown type, what the database driver accepts), driverOutput (optional, unknown type, what the driver returns, defaults to driverData), jsonData (optional, unknown type, what the field returns after JSON aggregation), config (optional, Record<string, any>, the config type for CustomTypeParams dataType generation), configRequired (optional, boolean, defaults to false, whether config argument should be required), notNull (optional, boolean, if the custom type should be notNull by default), and default (optional, boolean, if the custom type has a default value).
CustomTypeParams interface properties
CustomTypeParams<T extends CustomTypeValues> interface defines the custom type configuration. Required property: dataType (function that takes config and returns a string representing the database data type for migrations). Optional properties: toDriver (function mapping JS data to driver format, can return SQL), fromDriver (function transforming driver data to desired output format), fromJson (function transforming JSON-formatted data to desired format, used by relational queries and defaults to fromDriver), and forJsonSelect (function for modifying column selection inside JSON functions, used by relational queries).
customType basic int example
Example of defining a custom int type for SQLite: ```ts import { customType, sqliteTable } from 'drizzle-orm/sqlite-core'; const customInt = customType<{ data: number }>({ dataType() { return 'int'; }, }); export const users = sqliteTable('users', { id: customInt(), }); ```
customType basic text example
Example of defining a custom text type for SQLite: ```ts import { customType, sqliteTable } from 'drizzle-orm/sqlite-core'; const customText = customType<{ data: string }>({ dataType() { return 'text'; }, }); export const users = sqliteTable('users', { name: customText(), }); ```
fromDriver transformation function purpose
The fromDriver function is an optional mapping function that transforms data returned by the database driver to the desired column output format. For example, when using timestamps, it maps a string date representation to a JS Date object. This affects the shape of returned objects after queries.
fromJson transformation function purpose
The fromJson function is an optional mapping function used by relational queries and JSON functions to transform data returned from JSON in the database to the desired format. It defaults to the fromDriver function. For example, when querying a blob column via RQB or JSON functions, the result is returned as a hex string representation, which fromJson can convert to a Buffer.
forJsonSelect modification function purpose
The forJsonSelect function is an optional selection modifier function used by relational queries and JSON functions to modify how a column is selected inside JSON functions. It takes an SQL identifier and SQLGenerator and returns modified SQL. By default, numeric, decimal, bigint, and blob (via hex() function) types are cast to text. This ensures data integrity when converting to JSON format, particularly for large numbers that would lose precision if directly converted.
data type property in CustomTypeValues
The data property in CustomTypeValues is required and defines the TypeScript type of the column after selecting or inserting. Use data: string for text-like types (text, varchar) and data: number for numeric types (integer). This type inference guides what values the column accepts and returns.
driverData vs driverOutput in CustomTypeValues
In CustomTypeValues, driverData represents the type that the database driver accepts, while driverOutput represents the type that the driver returns. If driverOutput is not specified, it defaults to driverData. These are needed only when the driver's output and input types for a specific database data type differ.
customType with config and transformation functions example
Example of a custom hex type with config and transformation functions: ```ts import { customType, sqliteTable } from 'drizzle-orm/sqlite-core'; const customHex = customType<{ data: string; driverData: string; config: { mode: 'text' | 'blob' }; configRequired: false; }>({ dataType(config) { return config?.mode ? config.mode : 'text'; }, fromDriver(value: string): string { return value.startsWith('0x') ? value : `0x${value}`; }, toDriver(value: string): string { return value.replace(/^0x/, ''); }, }); export const users = sqliteTable('users', { wallet: customHex('wallet', { mode: 'text' }), }); ```
customType usage with table modifiers
Custom types defined with customType can be used with table modifiers like primaryKey() and notNull() just as built-in Drizzle ORM functions. Example: ```ts const usersTable = mysqlTable("users", { id: customInt().primaryKey(), name: customText().notNull(), wallet: customHex('wallet', { mode: 'text' }) }); ```
toDriver transformation function purpose
The toDriver function is an optional mapping function that transforms data from the desired JS/TS format to a format suitable for the database driver. For example, when using jsonb, it maps a JS object to a JSON string before writing to the database. The function takes the column's data type and returns either the driver data type or SQL.
generatedAlwaysAs() method in Drizzle
In Drizzle, you can specify the .generatedAlwaysAs() function on any column type and add a supported SQL query that will generate the column data. This function accepts a generated expression in two ways: using the sql tag to escape values, or using a callback to reference columns from a table.
Virtual generated columns: computed on read, no storage
Virtual (non-persistent) generated columns are computed dynamically whenever they are queried. They do not occupy storage space in the database. They are useful when you want computed values without the storage overhead.
Stored generated columns: computed on write, indexed and persistent
Stored (persistent) generated columns are computed when a row is inserted or updated and their values are stored in the database. They can be indexed and can improve query performance since the values do not need to be recomputed for each query.
Generated columns cannot be directly inserted or updated
You cannot directly insert or update values in a generated column. The values are automatically computed based on the generation expression.
Generated columns can be indexed and used in queries
Both virtual and stored generated columns can be indexed. Generated columns can be used in SELECT, INSERT, UPDATE, and DELETE statements. You can also specify NOT NULL and other constraints on generated columns.
generatedAlwaysAs() with sql tag example
You can use the sql tag with generatedAlwaysAs() when you want Drizzle to escape values for you. Example: generatedName: text("gen_name").generatedAlwaysAs(sql`'hello "world"!'`)
generatedAlwaysAs() with callback for column references
You can use a callback function with generatedAlwaysAs() when you need to reference columns from a table. Example: generatedName: text("gen_name").generatedAlwaysAs((): SQL => sql`'hi,' || ${test.name} || '!'`)
Stored generated column mode parameter
You can specify the mode of a generated column using the second parameter of generatedAlwaysAs(). Use { mode: "stored" } for stored generated columns or { mode: "virtual" } for virtual generated columns.
Complete generated columns schema example
Example schema with both stored and virtual generated columns: export const users = sqliteTable("users", { id: int(), name: text(), storedGenerated: text("stored_gen").generatedAlwaysAs( (): SQL => sql`${users.name} || 'hello'`, { mode: "stored" } ), virtualGenerated: text("virtual_gen").generatedAlwaysAs( (): SQL => sql`${users.name} || 'hello'`, { mode: "virtual" } ), }); This creates a table with both stored and virtual generated columns that concatenate the name column with 'hello'.
SQLite count() returns integer
In SQLite, the count() function result returns as an integer type at the database level.
DEFAULT constraint - basic values
The DEFAULT clause specifies a default value for a column if no value is provided during INSERT. If no explicit DEFAULT clause is attached, 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 enclosed in parentheses.
DEFAULT constraint - TypeScript syntax
To declare a column with a default value in Drizzle SQLite, use .default(value) for literal values or .default(sql`expression`) for SQL expressions. Example: integer('int1').default(42) or integer('int2').default(sql`(abs(42))`)
NOT NULL constraint
The NOT NULL constraint enforces a column to not accept NULL values. By default, a column can hold NULL values. Applying NOT NULL ensures a field always contains a value, preventing insertion or updates of records without providing a value for that field.
NOT NULL constraint - TypeScript syntax
To declare a NOT NULL column in Drizzle SQLite, use .notNull() on the column definition. Example: integer('numInt').notNull()
UNIQUE constraint - single column
The UNIQUE constraint ensures all values in a column are different. You can declare it with .unique() for an automatically named constraint or .unique('custom_name') for a custom name. A table can have many UNIQUE constraints but only one PRIMARY KEY constraint.
UNIQUE constraint - composite
To declare a composite UNIQUE constraint across multiple columns in Drizzle SQLite, use the unique() operator in the table's constraints array. Example: unique().on(table.id, table.name) or unique('custom_name').on(table.id, table.name)
CHECK constraint
The CHECK constraint limits the value range that can be placed in a column or across columns in a row. When defined on a column, it allows only certain values for that column. When defined on a table, it can limit values in certain columns based on values in other columns.
CHECK constraint - TypeScript syntax
To declare a CHECK constraint in Drizzle SQLite, use the check() function in the table's constraints array with a name and SQL condition. Example: check('age_check1', sql`${table.age} > 21`)
PRIMARY KEY constraint - basic
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values and cannot contain NULL values. A table can have only one primary key, which can consist of single or multiple columns.
PRIMARY KEY without autoIncrement
To declare a primary key column without auto-increment in SQLite with Drizzle, use .primaryKey() on the column. This generates CREATE TABLE with `id` integer PRIMARY KEY.
Composite PRIMARY KEY
To declare a composite primary key across multiple columns in Drizzle SQLite, use the primaryKey() operator in the table's constraints array. It accepts a columns property with an array of columns and an optional name property for custom naming. Example: primaryKey({ columns: [table.bookId, table.authorId] })
FOREIGN KEY constraint - basic
The FOREIGN KEY constraint prevents actions that would destroy links between tables. A FOREIGN KEY is a field or collection of fields in one table (child table) that refers to the PRIMARY KEY in another table (parent/referenced table).
FOREIGN KEY constraint - inline declaration
To declare a foreign key in a column definition in Drizzle SQLite, use .references() to point to another table's column. Example: authorId: integer('author_id').references(() => user.id)
FOREIGN KEY constraint - self-reference
To declare a self-referencing foreign key in Drizzle SQLite, you must either explicitly set the return type for the reference callback using type AnySQLiteColumn or use the standalone foreignKey() operator. Example: parentId: integer('parent_id').references((): AnySQLiteColumn => user.id)
FOREIGN KEY constraint - standalone operator
To declare foreign keys using the standalone foreignKey() operator in Drizzle SQLite, pass an object with columns (array of local columns), foreignColumns (array of referenced columns), and optional name property. Example: foreignKey({ columns: [table.parentId], foreignColumns: [table.id], name: 'custom_fk' })
FOREIGN KEY constraint - multi-column
To declare a multi-column foreign key in Drizzle SQLite, use the foreignKey() operator in the table's constraints array with columns and foreignColumns arrays. Example: foreignKey({ columns: [table.userFirstName, table.userLastName], foreignColumns: [user.firstName, user.lastName], name: 'custom_name' })
INDEX declaration
Drizzle ORM provides an index() function to declare indexes. Use index('name').on(table.column) to create an index. Optionally use .where(sql`...`) to create a partial index with a condition.
UNIQUE INDEX declaration
Drizzle ORM provides a uniqueIndex() function to declare unique indexes. Use uniqueIndex('name').on(table.column) to create a unique index that ensures uniqueness of indexed values.
SQLite table definition with schema example
Example of defining SQLite tables with Drizzle: `export const countries = sqliteTable('countries', { id: integer().primaryKey({ autoIncrement: true }), name: text() }); export const cities = sqliteTable('cities', { id: integer().primaryKey({ autoIncrement: true }), name: text(), countryId: integer('country_id').references(() => countries.id) });`