PostgreSQL Utilities

Utility functions and helpers for PostgreSQL schema and query operations

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}`);