PostgreSQL Utilities

Schema Utilities

getTableConfig()

Extracts complete table configuration including columns, indexes, and constraints.

import { getTableConfig } from 'drizzle-orm/pg-core';
import { users } from './schema';

const config = getTableConfig(users);
console.log(config);
// {
//   columns: [... column objects],
//   indexes: [... index objects],
//   foreignKeys: [... foreign key objects],
//   checks: [... check constraint objects],
//   primaryKeys: [... primary key objects],
//   uniqueConstraints: [... unique constraint objects],
//   name: 'users',
//   schema: undefined,
//   policies: [... RLS policy objects],
//   enableRLS: false
// }

table

PgTable

required

The table to extract configuration from

columns

PgColumn[]

Array of column objects

indexes

Index[]

Array of index definitions

foreignKeys

ForeignKey[]

Array of foreign key constraints

checks

Check[]

Array of check constraints

primaryKeys

PrimaryKey[]

Array of primary key definitions

uniqueConstraints

UniqueConstraint[]

Array of unique constraints

name

string

Table name

schema

string | undefined

Schema name (undefined for public schema)

policies

PgPolicy[]

Array of Row Level Security policies

enableRLS

boolean

Whether Row Level Security is enabled

getViewConfig()

Extracts view configuration.

import { getViewConfig } from 'drizzle-orm/pg-core';
import { myView } from './schema';

const config = getViewConfig(myView);
console.log(config.name, config.schema, config.query);

view

PgView

required

The view to extract configuration from

getMaterializedViewConfig()

Extracts materialized view configuration.

import { getMaterializedViewConfig } from 'drizzle-orm/pg-core';
import { myMaterializedView } from './schema';

const config = getMaterializedViewConfig(myMaterializedView);

view

PgMaterializedView

required

The materialized view to extract configuration from

Constraint Builders

index()

Creates an index definition.

import { pgTable, serial, varchar, index } from 'drizzle-orm/pg-core';

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
  name: varchar('name', { length: 255 }),
}, (table) => [
  // Simple index
  index('email_idx').on(table.email),
  
  // Composite index
  index('name_email_idx').on(table.name, table.email),
  
  // With method
  index('name_idx').using('btree', table.name),
  
  // Partial index
  index('active_users_idx')
    .on(table.email)
    .where(sql`${table.active} = true`),
]);

name

string

required

Index name

Methods:

.on(...columns): Specify columns to index
.using(method, ...columns): Specify index method (btree, hash, gist, gin, etc.)
.where(condition): Create partial index
.asc() / .desc(): Sort order (for btree indexes)
.nullsFirst() / .nullsLast(): NULL ordering

uniqueIndex()

Creates a unique index.

import { pgTable, serial, varchar, uniqueIndex } from 'drizzle-orm/pg-core';

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
  username: varchar('username', { length: 255 }),
}, (table) => [
  uniqueIndex('email_unique_idx').on(table.email),
  uniqueIndex('username_unique_idx').on(table.username),
]);

Same API as index() but creates a unique index.

primaryKey()

Defines a primary key constraint (for composite keys).

import { pgTable, integer, varchar, primaryKey } from 'drizzle-orm/pg-core';

const userRoles = pgTable('user_roles', {
  userId: integer('user_id'),
  roleId: integer('role_id'),
  grantedAt: timestamp('granted_at'),
}, (table) => [
  primaryKey({ columns: [table.userId, table.roleId] }),
]);

config.columns

PgColumn[]

required

Columns that form the primary key

config.name

string

Constraint name

foreignKey()

Defines a foreign key constraint.

import { pgTable, integer, foreignKey } from 'drizzle-orm/pg-core';

const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  userId: integer('user_id'),
  categoryId: integer('category_id'),
}, (table) => [
  foreignKey({
    columns: [table.userId],
    foreignColumns: [users.id],
    name: 'posts_user_fk',
  })
    .onDelete('cascade')
    .onUpdate('cascade'),
  
  // Composite foreign key
  foreignKey({
    columns: [table.categoryId, table.userId],
    foreignColumns: [categories.id, categories.ownerId],
  }),
]);

config.columns

PgColumn[]

required

Local columns

config.foreignColumns

PgColumn[]

required

Referenced columns in foreign table

config.name

string

Constraint name

Methods:

.onDelete(action): 'cascade' | 'set null' | 'set default' | 'restrict' | 'no action'
.onUpdate(action): Same options as onDelete

unique()

Defines a unique constraint.

import { pgTable, varchar, integer, unique } from 'drizzle-orm/pg-core';

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
  organizationId: integer('organization_id'),
}, (table) => [
  unique('email_per_org').on(table.email, table.organizationId),
]);

name

string

Constraint name

Methods:

.on(...columns): Columns that must be unique together

check()

Defines a check constraint.

import { pgTable, integer, check, sql } from 'drizzle-orm/pg-core';

const products = pgTable('products', {
  id: serial('id').primaryKey(),
  price: integer('price'),
  discount: integer('discount'),
  stock: integer('stock'),
}, (table) => [
  check('positive_price', sql`${table.price} > 0`),
  check('valid_discount', sql`${table.discount} >= 0 AND ${table.discount} <= 100`),
  check('stock_check', sql`${table.stock} >= 0`),
]);

name

string

required

Constraint name

condition

SQL

required

SQL condition that must be true

Row Level Security

pgPolicy()

Defines a Row Level Security policy.

import { pgTable, pgPolicy, sql } from 'drizzle-orm/pg-core';
import { authenticatedRole, anonymousRole } from './roles';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
}, (table) => [
  pgPolicy('users_select_policy', {
    for: 'select',
    to: authenticatedRole,
    using: sql`true`,
  }),
  
  pgPolicy('users_update_own', {
    for: 'update',
    to: authenticatedRole,
    using: sql`${table.id} = current_user_id()`,
  }),
]).enableRLS();

name

string

required

Policy name

config.for

'all' | 'select' | 'insert' | 'update' | 'delete'

Which operations the policy applies to

config.to

PgRole | PgRole[]

Which roles the policy applies to

config.using

SQL

USING clause (which rows are visible)

config.withCheck

SQL

WITH CHECK clause (which rows can be modified)

pgRole()

Defines a database role for use with RLS.

import { pgRole } from 'drizzle-orm/pg-core';

export const authenticatedRole = pgRole('authenticated');
export const anonymousRole = pgRole('anonymous');
export const serviceRole = pgRole('service_role');

name

string

required

Role name in the database

Sequences

pgSequence()

Defines a PostgreSQL sequence.

import { pgSequence } from 'drizzle-orm/pg-core';

export const userIdSeq = pgSequence('user_id_seq', {
  startWith: 1000,
  increment: 1,
  minValue: 1,
  maxValue: 999999,
  cache: 20,
  cycle: false,
});

// Use in table
export const users = pgTable('users', {
  id: integer('id').default(sql`nextval('user_id_seq')`).primaryKey(),
  name: varchar('name', { length: 255 }),
});

name

string

required

Sequence name

options

object

Sequence configuration:

startWith: Starting value
increment: Increment amount
minValue: Minimum value
maxValue: Maximum value
cache: Number of values to cache
cycle: Whether to cycle when reaching limits

Views

pgView()

Defines a PostgreSQL view.

import { pgView } from 'drizzle-orm/pg-core';

export const activeUsers = pgView('active_users').as((qb) =>
  qb.select({
    id: users.id,
    email: users.email,
  })
    .from(users)
    .where(eq(users.active, true))
);

// Use in queries
const result = await db.select().from(activeUsers);

name

string

required

View name

Methods:

.as(query): Define the view’s query
.existing(): Reference an existing view without creating it

pgMaterializedView()

Defines a PostgreSQL materialized view.

import { pgMaterializedView } from 'drizzle-orm/pg-core';

export const userStats = pgMaterializedView('user_stats').as((qb) =>
  qb.select({
    userId: users.id,
    postCount: sql<number>`count(${posts.id})`.as('post_count'),
  })
    .from(users)
    .leftJoin(posts, eq(users.id, posts.userId))
    .groupBy(users.id)
);

// Refresh the materialized view
await db.refreshMaterializedView(userStats);

name

string

required

Materialized view name

Methods:

.as(query): Define the view’s query
.existing(): Reference an existing materialized view

Array Utilities

arrayOverlaps()

Checks if PostgreSQL arrays have overlapping elements.

import { sql } from 'drizzle-orm';
import { arrayOverlaps } from 'drizzle-orm/pg-core';

const result = await db.select()
  .from(posts)
  .where(arrayOverlaps(posts.tags, ['typescript', 'drizzle']));

arrayContains()

Checks if a PostgreSQL array contains all specified elements.

import { arrayContains } from 'drizzle-orm/pg-core';

const result = await db.select()
  .from(posts)
  .where(arrayContains(posts.tags, ['typescript']));

arrayContained()

Checks if a PostgreSQL array is contained by another array.

import { arrayContained } from 'drizzle-orm/pg-core';

const result = await db.select()
  .from(posts)
  .where(arrayContained(posts.tags, ['typescript', 'javascript', 'drizzle']));

Type Inference Helpers

InferSelectModel

Infers the TypeScript type for selected rows.

import type { InferSelectModel } from 'drizzle-orm';
import { users } from './schema';

type User = InferSelectModel<typeof users>;
// { id: number; name: string; email: string | null; ... }

InferInsertModel

Infers the TypeScript type for insert operations.

import type { InferInsertModel } from 'drizzle-orm';
import { users } from './schema';

type NewUser = InferInsertModel<typeof users>;
// { id?: number; name: string; email?: string | null; ... }

Migration Utilities

sql.empty()

Creates an empty SQL object.

import { sql } from 'drizzle-orm';

const empty = sql.empty();

sql.join()

Joins multiple SQL fragments.

import { sql } from 'drizzle-orm';

const fragments = [sql`id = 1`, sql`name = 'John'`];
const joined = sql.join(fragments, sql` AND `);
// Results in: id = 1 AND name = 'John'

sql.raw()

Creates SQL from a raw string (use with caution - no escaping).

import { sql } from 'drizzle-orm';

const query = sql.raw('SELECT * FROM users');

Warning: sql.raw() does not escape values. Use parameterized queries instead:

// Good: Parameterized
const safe = sql`SELECT * FROM users WHERE id = ${userId}`;

// Bad: SQL injection risk
const unsafe = sql.raw(`SELECT * FROM users WHERE id = ${userId}`);

PostgreSQL

MySQL

SQLite

Core APIs

Drizzle Kit

Schema Utilities

getTableConfig()

getViewConfig()

getMaterializedViewConfig()

Constraint Builders

index()

uniqueIndex()

primaryKey()

foreignKey()

unique()

check()

Row Level Security

pgPolicy()

pgRole()

Sequences

pgSequence()

Views

pgView()

pgMaterializedView()

Array Utilities

arrayOverlaps()

arrayContains()

arrayContained()

Type Inference Helpers

InferSelectModel

InferInsertModel

Migration Utilities

sql.empty()

sql.join()

sql.raw()

Build docs developers (and LLMs) love

PostgreSQL

MySQL

SQLite

Core APIs

Drizzle Kit

​Schema Utilities

​getTableConfig()

​getViewConfig()

​getMaterializedViewConfig()

​Constraint Builders

​index()

​uniqueIndex()

​primaryKey()

​foreignKey()

​unique()

​check()

​Row Level Security

​pgPolicy()

​pgRole()

​Sequences

​pgSequence()

​Views

​pgView()

​pgMaterializedView()

​Array Utilities

​arrayOverlaps()

​arrayContains()

​arrayContained()

​Type Inference Helpers

​InferSelectModel

​InferInsertModel

​Migration Utilities

​sql.empty()

​sql.join()

​sql.raw()

Build docs developers (and LLMs) love

Schema Utilities

getTableConfig()

getViewConfig()

getMaterializedViewConfig()

Constraint Builders

index()

uniqueIndex()

primaryKey()

foreignKey()

unique()

check()

Row Level Security

pgPolicy()

pgRole()

Sequences

pgSequence()

Views

pgView()

pgMaterializedView()

Array Utilities

arrayOverlaps()

arrayContains()

arrayContained()

Type Inference Helpers

InferSelectModel

InferInsertModel

Migration Utilities

sql.empty()

sql.join()

sql.raw()