Overview
Introduction
Defining database schemas in TypeScript allows you to structure your tables, columns, primary keys, indexes, and constraints with full type safety. By declaring your schema using table functions, you gain automatic type inference for both querying and inserting records.
Defining Tables and Columns
To declare a table, use the table creation function corresponding to your database engine, such as pgTable, mysqlTable, sqliteTable, gelTable, or singlestoreTable. Pass the database table name as the first argument and an object containing your columns as the second argument.
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
export const users = pgTable('users_table', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
age: integer('age1').notNull(),
});Adding Primary Keys
Primary keys uniquely identify each row in a table. You can define them directly on an individual column or configure composite primary keys using an extra configuration callback (the third argument of the table function).
import { pgTable, text, primaryKey } from 'drizzle-orm/pg-core';
export const users = pgTable(
'users_table',
{
class: text('class').notNull(),
subClass: text('sub_class').notNull(),
name: text('name'),
},
(users) => [
primaryKey({ columns: [users.class, users.subClass] }),
]
);Creating Indexes and Unique Constraints
Indexes speed up data retrieval, while unique constraints ensure that column values remain distinct across rows. You can declare indexes and unique constraints inside the extra configuration function.
import { pgTable, text, index, uniqueIndex, unique } from 'drizzle-orm/pg-core';
export const users = pgTable(
'users_table',
{
id: serial('id').primaryKey(),
email: text('email').notNull(),
role: text('role'),
},
(users) => [
index('user_role_idx').on(users.role),
uniqueIndex('user_email_idx').on(users.email),
unique('custom_unique_name').on(users.email, users.role),
]
);Setting Up Constraints and Foreign Keys
Constraints enforce rules on your data, such as check conditions or relationships between tables using foreign keys.
import { pgTable, serial, integer, text, check, foreignKey } from 'drizzle-orm/pg-core';
export const users = pgTable(
'users_table',
{
id: serial('id').primaryKey(),
age: integer('age').notNull(),
classSubClass: text('sub_class'),
},
(users) => [
check('legal_age', sql`${users.age} > 18`),
foreignKey({
columns: [users.classSubClass],
foreignColumns: [classes.subClass],
}).onUpdate('cascade').onDelete('cascade'),
]
);Tip
Use .notNull() on columns that are required, and .default() or .defaultNow() for automatic values.
Note
Depending on your chosen dialect, extra configuration callbacks accept either arrays or objects. Ensure you match the syntax expected by your database module.