pg-core/constraints

46 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

primaryKey constraint

In Drizzle, use `.primaryKey()` on a column to add a primary key constraint. A primary key indicates that a column or group of columns can be used as a unique identifier for rows in the table. Values must be both unique and not null.

DEFAULT constraint with value types

The DEFAULT clause specifies a default value for a column if no value is provided during an INSERT. The default value can be NULL, a string constant, a blob constant, a signed number, or any constant expression enclosed in parentheses. If no explicit DEFAULT clause is attached to a column definition, the default value is NULL.

DEFAULT constraint with .default() method

In Drizzle ORM, use the .default() method on a column to set a constant default value. Example: integer().default(42). To use a SQL expression as default, use sql template: integer().default(sql`24`).

UUID default with .defaultRandom()

For UUID columns, use .defaultRandom() to generate a random UUID, which translates to DEFAULT gen_random_uuid() in SQL. Alternatively, use .default(sql`gen_random_uuid()`).

NOT NULL constraint

The NOT NULL constraint enforces a column to not accept NULL values. By default, columns can hold NULL values. The NOT NULL constraint ensures a field always contains a value, meaning you cannot insert a new record or update a record without providing a value for this field.

NOT NULL constraint with .notNull() method

In Drizzle ORM, use the .notNull() method on a column to add the NOT NULL constraint. Example: integer().notNull().

UNIQUE constraint basic usage

The UNIQUE constraint ensures that all values in a column are different. Both UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. You can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

UNIQUE constraint with .unique() method

In Drizzle ORM, use the .unique() method on a column to add a UNIQUE constraint. Example: integer().unique(). You can optionally provide a custom constraint name: integer().unique('custom_name').

Composite UNIQUE constraint with table-level definition

To define a UNIQUE constraint on multiple columns, use the table-level unique() operator in the constraints array passed as the third argument to pgTable(). Example: unique().on(t.id, t.name) or unique('custom_name').on(t.id, t.name).

UNIQUE constraint with NULLS NOT DISTINCT (Postgres 15.0+)

In Postgres 15.0 and later, NULLS NOT DISTINCT is available for UNIQUE constraints. In Drizzle ORM, use the nullsNotDistinct() method on table-level unique() constraints: unique().on(t.id).nullsNotDistinct(). For column-level unique constraints, pass an options object: integer().unique('custom_name', { nulls: 'not distinct' }).

CHECK constraint

The CHECK constraint is used to limit the value range that can be placed in a column. If defined on a column, it allows only certain values for that column. If defined on a table, it can limit values in certain columns based on values in other columns in the row.

CHECK constraint with check() operator

In Drizzle ORM, use the check() operator in the constraints array passed as the third argument to pgTable(). The first parameter is the constraint name, the second is a SQL expression. Example: check('age_check1', sql`${table.age} > 21`).

PRIMARY KEY constraint

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 a single or multiple columns.

PRIMARY KEY constraint with .primaryKey() method

In Drizzle ORM, use the .primaryKey() method on a column to define a single-column primary key. Example: serial('id').primaryKey().

Composite PRIMARY KEY with primaryKey() operator

To define a primary key on multiple columns, use the primaryKey() operator in the constraints array passed as the third argument to pgTable(). The operator accepts a columns array. Example: primaryKey({ columns: [table.bookId, table.authorId] }). You can optionally specify a custom name: primaryKey({ name: 'custom_name', columns: [table.bookId, table.authorId] }).

FOREIGN KEY constraint

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field or collection of fields in one table that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

FOREIGN KEY constraint with .references() method

In Drizzle ORM, declare a foreign key in a column declaration by using the .references() method. Example: integer('author_id').references(() => user.id). The .references() method takes a callback that returns the referenced column.

Self-referencing FOREIGN KEY with AnyPgColumn

For self-referencing foreign keys, due to TypeScript limitations, you must either explicitly set the return type for the reference callback using AnyPgColumn or use a standalone foreignKey operator. Example: integer('parent_id').references((): AnyPgColumn => user.id).

Multi-column FOREIGN KEY with foreignKey() operator

To declare a foreign key on multiple columns, use the foreignKey() operator in the constraints array passed as the third argument to pgTable(). The operator requires columns and foreignColumns arrays. Example: foreignKey({ columns: [table.userFirstName, table.userLastName], foreignColumns: [user.firstName, user.lastName], name: 'custom_fk' }).

INDEX with index() operator

In Drizzle ORM, create an index using the index() operator in the constraints array passed as the third argument to pgTable(). The operator takes an index name as parameter. Example: index('name_idx').on(table.name).

UNIQUE INDEX with uniqueIndex() operator

In Drizzle ORM, create a unique index using the uniqueIndex() operator in the constraints array passed as the third argument to pgTable(). The operator takes an index name as parameter. Example: uniqueIndex('email_idx').on(table.email).

INDEX configuration methods and parameters

Drizzle ORM index() operator supports the following method chain: .on() or .onOnly() to specify columns, .concurrently() for concurrent index creation, .where(sql``) for partial indexes with SQL conditions, .with({ fillfactor: '70' }) for index options. Additionally, .using() specifies index type (e.g., 'btree') and accepts column specifications with .asc(), .nullsFirst(), .op() modifiers, or raw SQL expressions.

INDEX column modifiers: .asc(), .nullsFirst(), and .op()

When defining index columns, use .asc() for ascending order, .nullsFirst() for null handling, and .op() to specify operator class (e.g., .op('text_ops')). These modifiers apply to individual columns in the index definition.

INDEX with SQL expressions

Indexes can include SQL expressions as column specifications. Example: index('name').using('btree', table.column1.asc(), sql`lower(${table.column2})`, table.column1.op('text_ops')).

Policies are automatically enabled when added to a table

If you add a policy to a table using pgPolicy, RLS will be enabled automatically. There is no need to explicitly enable RLS when adding policies to a table.

Define roles with pgRole

Use pgRole('roleName', options) to define roles in Drizzle. Available options include createRole (boolean), createDb (boolean), and inherit (boolean). If a role already exists in your database and you do not want drizzle-kit to manage it, call .existing() on the role.

pgPolicy full API reference

pgPolicy('policyName', options) creates a Row-Level Security policy. Options are: as (string, 'permissive' or 'restrictive'), to (string or pgRole, specifies the role; possible values are 'public', 'current_role', 'current_user', 'session_user', or a role name or pgRole object), for (string, defines which commands this policy applies to: 'all', 'select', 'insert', 'update', 'delete'), using (SQL expression applied to the USING part), withCheck (SQL expression applied to the WITH CHECK part).

Link policy to existing table with .link()

Use pgPolicy('policyName', options).link(existingTable) to attach a policy to an existing table in the database. This is useful when working with database providers like Neon or Supabase where you need to add policies to their existing tables.

RLS on views with securityInvoker

To apply RLS policies on views, use pgView().with({ securityInvoker: true }) in the view definition.

crudPolicy for Neon

Import crudPolicy from 'drizzle-orm/neon' to create a simplified RLS policy for Neon. Use crudPolicy({ role: pgRole, read: boolean, modify: boolean }) where role is a pgRole object, read controls select permission, and modify controls insert, update, and delete permissions. This generates multiple underlying pgPolicy objects.

Neon predefined roles and functions

Neon provides predefined roles imported from 'drizzle-orm/neon': authenticatedRole (pgRole('authenticated').existing()) and anonymousRole (pgRole('anonymous').existing()). The authUid function is available as a helper: authUid(userIdColumn) returns sql`(select auth.user_id() = ${userIdColumn})`.

Supabase predefined roles

Supabase provides predefined roles imported from 'drizzle-orm/supabase': anonRole (pgRole('anon').existing()), authenticatedRole (pgRole('authenticated').existing()), serviceRole (pgRole('service_role').existing()), postgresRole (pgRole('postgres_role').existing()), and supabaseAuthAdminRole (pgRole('supabase_auth_admin').existing()).

Supabase predefined tables and functions

The 'drizzle-orm/supabase' import includes predefined tables and SQL helpers. authUsers is a table in the 'auth' schema with id (uuid). realtimeMessages is a table in the 'realtime' schema with id (bigserial, bigint mode), topic (text), and extension (text with enum ['presence', 'broadcast', 'postgres_changes']). Available SQL helpers are authUid (sql`(select auth.uid())`) and realtimeTopic (sql`realtime.topic()`).

Supabase example: foreign key to auth.users

import { foreignKey, pgPolicy, pgTable, text, uuid } from 'drizzle-orm/pg-core'; import { sql } from 'drizzle-orm/sql'; import { authenticatedRole, authUsers } from 'drizzle-orm/supabase'; export const profiles = pgTable( 'profiles', { id: uuid().primaryKey().notNull(), email: text().notNull(), }, (table) => [ foreignKey({ columns: [table.id], foreignColumns: [authUsers.id], name: 'profiles_id_fk', }).onDelete('cascade'), pgPolicy('authenticated can view all profiles', { for: 'select', to: authenticatedRole, using: sql`true`, }), ] );

Enable RLS on table with pgTable.withRLS()

To enable Row-Level Security on a Postgres table without adding policies, use pgTable.withRLS('tableName', { columns }). When no policy exists for a table, a default-deny policy is used, meaning no rows are visible or can be modified. Operations that apply to the whole table such as TRUNCATE and REFERENCES are not subject to row security.

Define custom role with options

Example of creating an admin role with createRole, createDb, and inherit options set to true.

Mark existing role to skip drizzle-kit management

Example of marking an existing role in the database so drizzle-kit does not try to manage it.

pgPolicy example with all available properties

Full example showing pgPolicy definition with as, to, for, using, and withCheck properties.

crudPolicy expansion to individual pgPolicy objects

Example showing that crudPolicy({ role: admin, read: true, modify: false }) expands to four pgPolicy objects: one for insert with withCheck: sql`false`, one for update with using and withCheck as sql`false`, one for delete with using: sql`false`, and one for select with using: sql`true`.

Neon example: use predefined roles in policies

Example showing how to use Neon's authenticatedRole in a pgPolicy.

Supabase example: link policy to existing realtime table

Example showing how to create a pgPolicy and link it to the Supabase realtimeMessages table using .link().

Supabase example: use predefined roles in policies

Example showing how to use Supabase's serviceRole in a pgPolicy.

createDrizzle wrapper for Supabase transactions with RLS

Example showing a createDrizzle function that wraps admin and client Drizzle instances and provides an rls method for executing transactions with RLS context. The function sets JWT claims, user ID, and local role using SQL config before executing the transaction, then resets the config after completion.

Define indexes in table configuration callback

Pass a second callback function to pgTable() that receives the table object and returns an array of index definitions. Example: pgTable('posts', {...}, (table) => [uniqueIndex('slug_idx').on(table.slug), index('title_idx').on(table.title)])

Foreign key references with references()

Use .references(() => otherTable.columnName) on a column to create a foreign key constraint. The function receives the column to reference. Example: invitee: t.integer().references((): AnyPgColumn => users.id) creates a foreign key to the users table's id column.

enableRLS() deprecated for pgTable.withRLS()

The `.enableRLS()` method on tables has been deprecated in v1.0. Users must use `pgTable.withRLS('table_name', { ... })` instead. This applies to Row-Level Security configuration for PostgreSQL tables.

Give your agent this brain