schema declaration & column types

124 notes in this subject, read out of this brain and free to use. This is page 1 of 3.

PgTable class-based schema declaration

Tables extend PgTable<TableName> class. Columns are declared as class properties using methods like this.serial(), this.varchar(), this.int(). The tableName() method returns the actual database table name as a string.

InferType for deriving TypeScript types from tables

TypeScript types can be inferred from table schemas using InferType<TableClass>. This provides full typing for query results. Example: export type User = InferType<UsersTable>.

Column type methods in v0.11.0

Column types in v0.11.0 are declared using instance methods: serial() for auto-incrementing integers, varchar(name, { size: number }) for strings with size limit, int() for integers, text() for text, and type(customType, name) for custom/enum types. All methods are called on the PgTable instance with this.

Primary key constraint in v0.11.0

Primary keys are declared by chaining .primaryKey() to a column definition. Example: this.serial('id').primaryKey().

Index declaration with uniqueIndex

Unique indexes are declared as class properties using this.uniqueIndex(column). Regular indexes are declared using this.index(column). Example: nameIndex = this.uniqueIndex(this.name).

Define enum in database with createEnum

Enums are declared using createEnum() with alias and values properties. Example: createEnum({ alias: 'popularity', values: ['unknown', 'known', 'popular'] }). This enum can then be used as a column type in tables.

Foreign key constraint in v0.11.0

Foreign keys are declared by chaining .foreignKey(ReferencedTable, callback) to a column definition. The callback receives the referenced table and should return the referenced column. Example: this.int('country_id').foreignKey(CountriesTable, (country) => country.id). Foreign key options include onDelete property, e.g., { onDelete: 'CASCADE' }.

Custom types for MySQL

To create custom non-native MySQL types, use the customType function with a type parameter specifying the data type. Define a dataType() method that returns the SQL type as a string. Example: const customText = customType<{ data: string }>({ dataType() { return "text"; } }); then use it in a table definition like customText("name").notNull().

Custom types for PostgreSQL

To create custom non-native PostgreSQL types, use the customType function with a type parameter specifying the data type. Define a dataType() method that returns the SQL type as a string. Example: const customText = customType<{ data: string }>({ dataType() { return "text"; } }); then use it in a table definition like customText("name").notNull().

PostgreSQL schemas declaration syntax

To declare PostgreSQL schemas and tables within a schema, import pgSchema from drizzle-orm-pg and call pgSchema("schema_name") to create a schema, then call the returned schema function with a table name and column definitions. Example: export const mySchema = pgSchema("my_schema"); export const users = mySchema("users", { id: serial("id").primaryKey(), name: text("name"), email: text("email") });

MySQL databases/schemas declaration syntax

To declare MySQL databases/schemas and tables within them, import mysqlSchema from drizzle-orm-mysql and call mysqlSchema("schema_name") to create a schema, then call the returned schema function with a table name and column definitions. Example: const mySchema = mysqlSchema("my_schema"); const users = mySchema("users", { id: serial("id").primaryKey(), name: text("name"), email: text("email") });

MySQL single-column unique constraints

MySQL unique constraints are defined at the column level using the .unique() method, similar to PostgreSQL. Use .unique() for no custom name or .unique('custom_name') for a custom constraint name. Example: name: text('name').notNull().unique() or state: text('state').unique('custom').

MySQL unique constraints example

MySQL single-column unique constraint example: const table = mysqlTable('table', { id: serial('id').primaryKey(), name: text('name').notNull().unique(), state: text('state').unique('custom'), field: text('field').unique('custom_field'), }). Multi-column example: const table = mysqlTable('cities1', { id: serial('id').primaryKey(), name: text('name').notNull(), state: text('state'), }, (t) => ({ first: unique().on(t.name, t.state), second: unique('custom_name1').on(t.name, t.state), })).

PostgreSQL NULLS NOT DISTINCT option

PostgreSQL unique constraints support a NULLS NOT DISTINCT option to restrict having more than one NULL value in a table. For single-column constraints, pass { nulls: 'not distinct' } as the second argument: .unique('custom_field', { nulls: 'not distinct' }). For multi-column constraints, chain the .nullsNotDistinct() method: unique('custom_name').on(t.name, t.state).nullsNotDistinct().

MySQL does not support NULLS NOT DISTINCT

MySQL does not support the NULLS NOT DISTINCT option for unique constraints, unlike PostgreSQL.

SQLite unique constraints example

SQLite single-column unique constraint example: const table = sqliteTable('table', { id: int('id').primaryKey(), name: text('name').notNull().unique(), state: text('state').unique('custom'), field: text('field').unique(), }). Multi-column example: const table = sqliteTable('table', { id: int('id').primaryKey(), name: text('name').notNull(), state: text('state'), }, (t) => ({ first: unique().on(t.name, t.state), second: unique('custom').on(t.name, t.state), })).

SQLite multi-column unique constraints

SQLite multi-column unique constraints are defined in the third parameter of sqliteTable using the unique() function. Syntax: unique().on(t.column1, t.column2) or unique('constraint_name').on(t.column1, t.column2). Example: first: unique().on(t.name, t.state) or second: unique('custom').on(t.name, t.state).

SQLite single-column unique constraints

SQLite unique constraints are defined at the column level using the .unique() method. Use .unique() for unnamed constraints or .unique('custom_name') for a named constraint. Example: name: text('name').notNull().unique() or state: text('state').unique('custom').

PostgreSQL single-column unique constraints

PostgreSQL unique constraints can be defined at the column level using the .unique() method. For a column with no custom name, use .unique(). For a custom constraint name, pass the name as a string: .unique('custom'). Example: name: text('name').notNull().unique() or state: char('state', { length: 2 }).unique('custom').

SQLite unique constraints as unique indexes

In SQLite, unique constraints are implemented as unique indexes internally. SQLite unique constraints are treated the same way as unique indexes since you can specify a name for the unique index in SQLite.

PostgreSQL unique constraints example

PostgreSQL single-column unique constraint example: const table = pgTable('table', { id: serial('id').primaryKey(), name: text('name').notNull().unique(), state: char('state', { length: 2 }).unique('custom'), field: char('field', { length: 2 }).unique('custom_field', { nulls: 'not distinct' }), }). Multi-column example: const table = pgTable('table', { id: serial('id').primaryKey(), name: text('name').notNull(), state: char('state', { length: 2 }), }, (t) => ({ first: unique('custom_name').on(t.name, t.state).nullsNotDistinct(), second: unique('custom_name1').on(t.name, t.state), })).

PostgreSQL multi-column unique constraints

PostgreSQL multi-column unique constraints are defined in the third parameter of pgTable using the unique() function. Syntax: unique('constraint_name').on(t.column1, t.column2). The constraint name can be omitted for unnamed constraints. Example: first: unique('custom_name').on(t.name, t.state).

$defaultFn() and $default() methods for column builders

Drizzle ORM v0.28.3 added $defaultFn() and $default() methods to column builders. These allow you to specify runtime default values for columns using any logic and implementation. For example, you can use cuid() for generating runtime defaults. This value only affects runtime behavior in drizzle-orm and does not affect drizzle-kit behavior.

Runtime default function example with cuid2

Example of using $defaultFn() with cuid2 for runtime defaults: ```ts import { varchar, mysqlTable } from "drizzle-orm/mysql-core"; import { createId } from '@paralleldrive/cuid2'; const table = mysqlTable('table', { id: varchar('id', { length: 128 }).$defaultFn(() => createId()), }); ``` This generates a unique ID at runtime whenever a new row is inserted without an explicit id value.

InferModel type deprecated in favor of explicit types

The InferModel type is deprecated in Drizzle ORM v0.28.3 in favor of the more explicit InferSelectModel and InferInsertModel types for better clarity about which model type is being inferred.

Table type inference methods example

Example of using table type inference methods: ```ts import { InferSelectModel, InferInsertModel } from 'drizzle-orm' const usersTable = pgTable('users', { id: serial('id').primaryKey(), name: text('name').notNull(), verified: boolean('verified').notNull().default(false), jsonb: jsonb('jsonb').$type<string[]>(), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), }); type SelectUser = typeof usersTable.$inferSelect; type InsertUser = typeof usersTable.$inferInsert; type SelectUser2 = InferSelectModel<typeof usersTable>; type InsertUser2 = InferInsertModel<typeof usersTable>; ``` Both approaches are valid: using $inferSelect/$inferInsert directly on the table, or using InferSelectModel/InferInsertModel helper types.

Table type inference with $inferSelect and $inferInsert

Drizzle ORM v0.28.3 added $inferSelect, table._.inferSelect, $inferInsert, and table._.inferInsert for convenient table model type inference. These can be used with the typeof operator to extract TypeScript types directly from table definitions.

SQLite text column JSON mode example

const test = sqliteTable('test', { dataTyped: text('data_typed', { mode: 'json' }).$type<{ a: 1 }>().notNull(), });

SQLite text column with JSON mode

SQLite now supports json mode for text columns. Use text('data_typed', { mode: 'json' }).$type<T>() to declare a text column that stores and retrieves JSON data with type safety.

MySQL datetime with mode: 'date' stores and retrieves UTC strings

MySQL datetime with mode: 'date' will now store dates in UTC strings and retrieve data in UTC as well to align with MySQL behavior for datetime. If you need different behavior and want to handle datetime mapping differently, use mode: 'string' or Custom Types implementation.

MySQL bigint unsigned option syntax

In MySQL, you can specify unsigned bigint type using the unsigned option: `bigint('id', { mode: 'number', unsigned: true })`.

Named primary key and foreign key example

Example of specifying custom names for composite primary key and foreign key: ```ts const table = pgTable('table', { id: integer('id'), name: text('name'), }, (table) => ({ cpk: primaryKey({ name: 'composite_key', columns: [table.id, table.name] }), cfk: foreignKey({ name: 'fkName', columns: [table.id], foreignColumns: [table.name], }), })); ```

Custom names for primary keys and foreign keys

Starting from v0.29.0, you can specify custom names for both primaryKey() and foreignKey() constraints using the name option. This helps avoid database engine truncation issues when constraint names exceed the 64-character limit. The old primaryKey() syntax is deprecated but still functional.

timestamp column type mode string returns behavior change

In v0.30.0, a bug was fixed where timestamp columns with mode string were being returned as Date objects instead of strings. The behavior now correctly depends on the selected mode for timestamp columns.

smallserial column insertions are not optional

Columns with the smallserial datatype in PostgreSQL now correctly require insertions to be non-optional, fixing issue where smallserial datatype insertions were incorrectly treated as optional.

$onUpdate example with SQL expressions and functions

Example showing $onUpdate usage: updateCounter uses $onUpdateFn(() => sql`update_counter + 1`) to increment a counter on update; updatedAt uses $onUpdate(() => new Date()) to set current date on update; alwaysNull uses $onUpdate(() => null) to always set null on update.

$onUpdate for columns in PostgreSQL, MySQL, and SQLite

The $onUpdate functionality adds a dynamic update value to a column. The function is called when a row is updated, and the returned value is used as the column value if none is provided. If no default or $defaultFn value is provided, the function is also called when the row is inserted. This value only affects runtime behavior in drizzle-orm and does not affect drizzle-kit behavior.

Postgres enum with custom schema example

import { pgSchema } from 'drizzle-orm/pg-core'; const mySchema = pgSchema('mySchema'); const colors = mySchema.enum('colors', ['red', 'green', 'blue']);

Custom schema support for Postgres enums

Postgres enums can be created within a custom schema using pgSchema(). First import pgSchema from 'drizzle-orm/pg-core', create a schema instance with pgSchema('schemaName'), then call the .enum() method on that schema instance with the enum name and values array.

PostgreSQL line type with mode options

The PostgreSQL line type in Drizzle supports two modes: 'tuple' (default) which maps database Line{1,2,3} to [1,2,3] on select and accepts tuples on insert, and 'abc' which maps database Line{1,2,3} to { a: 1, b: 2, c: 3 } on select (representing equation Ax + By + C = 0) and accepts objects on insert. Defined as line('column_name') or line('column_name', { mode: 'abc' }).

PostgreSQL indexes API breaking changes in v0.31.0

The PostgreSQL indexes API was redesigned in Drizzle v0.31.0 to align with PostgreSQL documentation. The new API requires `.asc()`, `.desc()`, `.nullsFirst()`, and `.nullsLast()` to be specified on individual columns or expressions within the index definition, not on the index itself. The `.using()` method now takes an index type (e.g., 'btree', 'hnsw') as the first parameter followed by columns and expressions. The API separates `.on()` for standard indexes and `.using()` for specifying index methods.

PostGIS geometry type support

Drizzle v0.31.0 adds basic support for the PostgreSQL PostGIS extension's geometry type. No specific code is required to create the extension within Drizzle schema; the database must have PostGIS pre-installed. The geometry type is defined as geometry('column_name', { type: 'point' }) or with additional options like mode and srid.

Index manual naming requirement for expressions

When using indexes with SQL expressions in Drizzle, a name must be specified manually. index().on(table.id, table.email) will auto-generate a name, but index().on(sql`lower(${table.email})`) will error. Must use index('my_name').on(sql`lower(${table.email})`) to work correctly.

PostGIS geometry type options

The geometry type supports the following options: type (e.g., 'point', or any other PostGIS geometry type as a string), mode ('tuple' or 'xy', similar to point type behavior where 'tuple' maps to [x,y] and 'xy' maps to { x: 1, y: 2 }), and srid (e.g., 4000 for spatial reference system identifier). Example: geometry('geo_options', { type: 'point', mode: 'xy', srid: 4000 }).

PostgreSQL point type with mode options

The PostgreSQL point type in Drizzle supports two modes: 'tuple' (default) which maps database Point(1,2) to [1,2] on select and accepts tuples on insert, and 'xy' which maps database Point(1,2) to { x: 1, y: 2 } on select and accepts objects on insert. Defined as point('column_name') or point('column_name', { mode: 'xy' }).

pg_vector distance helper function implementation pattern

Custom pg_vector distance functions can be created following this pattern: export function l2Distance(column: SQLWrapper | AnyColumn, value: number[] | string[] | TypedQueryBuilder<any> | string): SQL { if (is(value, TypedQueryBuilder<any>) || typeof value === 'string') { return sql`${column} <-> ${value}`; } return sql`${column} <-> ${JSON.stringify(value)}`; }. This allows for flexible input types including arrays that are stringified to JSON.

pg_vector distance helper functions

Drizzle provides the following pg_vector helper functions imported from 'drizzle-orm': l2Distance(table.column, [3, 1, 2]) translates to <-> operator, l1Distance(table.column, [3, 1, 2]) translates to <+> operator, innerProduct(table.column, [3, 1, 2]) translates to <#> operator, cosineDistance(table.column, [3, 1, 2]) translates to <=> operator, hammingDistance(table.column, '101') translates to <~> operator, jaccardDistance(table.column, '101') translates to <%> operator. These functions accept number arrays, string arrays, strings, or TypedQueryBuilder select queries as values.

pg_vector index types and operator classes

For pg_vector indexes in Drizzle, the following operator classes are supported: vector_l2_ops (L2 distance), vector_ip_ops (inner product), vector_cosine_ops (cosine distance), vector_l1_ops (L1 distance, pg_vector 0.7.0+), bit_hamming_ops (Hamming distance, pg_vector 0.7.0+), and bit_jaccard_ops (Jaccard distance, pg_vector 0.7.0+). Indexes are created using .using('hnsw', table.column.op('operator_class')).

pg_vector extension support

Drizzle v0.31.0 adds support for the PostgreSQL pg_vector extension. Users can specify indexes for pg_vector and use pg_vector functions for querying and ordering. No specific code is required to create the extension within Drizzle schema; the database must have the pg_vector extension pre-installed. Vector column type is defined as vector('column_name', { dimensions: 3 }).

PostgreSQL indexes new API with .using() method

The current PostgreSQL indexes API using `.using()` syntax: index('name').using('btree', table.column1.asc(), sql`lower(${table.column2})`, table.column1.op('text_ops')).where(sql``).with({ fillfactor: '70' }). The `.using()` method accepts an index type as the first parameter, followed by columns and SQL expressions. The `.op()` method specifies operator classes for columns.

PostgreSQL indexes new API with .on() method

The current PostgreSQL indexes API using `.on()` syntax: index('name').on(table.column1.asc(), table.column2.nullsFirst(), ...).concurrently().where(sql``).with({ fillfactor: '70' }) or index('name').onOnly(table.column1.desc().nullsLast(), table.column2, ...).concurrently().where(sql``).with({ fillfactor: '70' }). Order-related methods (.asc(), .desc(), .nullsFirst(), .nullsLast()) must be chained on individual column references within .on().

MySQL generated columns without table column references

MySQL generated columns can be created without referencing table columns using just a sql template or string: generatedName: text('gen_name').generatedAlwaysAs(sql`hello`) or generatedName1: text('gen_name1').generatedAlwaysAs('hello').

PostgreSQL generated columns without table column references

PostgreSQL generated columns can be created without referencing table columns using just a sql template or string: generatedName: text('gen_name').generatedAlwaysAs(sql`hello world!`) or generatedName1: text('gen_name1').generatedAlwaysAs('hello world!').

PostgreSQL generated columns with generatedAlwaysAs()

Generated columns can be specified on any column supported by PostgreSQL using the generatedAlwaysAs() method. The method accepts either a SQL template or a callback function returning SQL. Example: contentSearch: tsVector('content_search', { dimensions: 3 }).generatedAlwaysAs((): SQL => sql`to_tsvector('english', ${test.content})`).

PostgreSQL identity columns with generatedAlwaysAsIdentity()

Identity columns are the recommended way to specify sequences in PostgreSQL schemas instead of using the outdated serial type. Use the generatedAlwaysAsIdentity() function to create identity columns. Example: id: integer("id").primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }). All properties available for sequences can be specified in the generatedAlwaysAsIdentity() function, and custom names for sequences can also be specified.

PostgreSQL pgSequence in custom schema

Sequences can be defined within custom schemas: export const customSchema = pgSchema('custom_schema'); export const customSequence = customSchema.sequence("name");

PostgreSQL pgSequence with parameters

PostgreSQL sequences can be created with parameters: startWith, maxValue, minValue, cycle, cache, and increment. Example: export const customSequence = pgSequence("name", { startWith: 100, maxValue: 10000, minValue: 100, cycle: true, cache: 10, increment: 2 });

MySQL generated columns with stored and virtual modes

MySQL generated columns support both stored and virtual options. Example with stored mode: generatedName: text('gen_name').generatedAlwaysAs((): SQL => sql`${schema2.users.name} || 'hello'`, { mode: 'stored' }). Example with virtual mode: generatedName1: text('gen_name1').generatedAlwaysAs((): SQL => sql`${schema2.users.name} || 'hello'`, { mode: 'virtual' }).

PostgreSQL pgSequence with no parameters

PostgreSQL sequences can be created with no parameters specified: export const customSequence = pgSequence("name");

SQLite generated columns with stored and virtual modes

SQLite generated columns support both stored and virtual options, as documented in SQLite documentation on generated columns.

Give your agent this brain