PostgreSQL arrays

PostgreSQL arrays are the simplest way to store tags, roles, permissions, or feature flags on a row. Querying them is another story: @>, &&, <@, cardinality(), and a parameter that has to be cast to the right array type. None of it is checked by TypeScript, and every call site gets it slightly differently.

better-drizzle gives native array columns their own typed filter. The operators read like what you mean, the element type comes from your schema, and every filter compiles to the same PostgreSQL operators you would write by hand:

const rows = await client.posts.findMany({
	where: {
		tags: { has: 'drizzle', length: { lte: 5 } },
		editors: { hasSome: [currentUserId] },
		flags: { hasNone: ['archived', 'spam'] },
	},
});

What you get:

• Operators named after the question. has, hasEvery, hasSome, hasNone, containedBy, isEmpty, and length instead of @>, &&, <@, and cardinality().
• Element types from your schema. A text[] column takes strings, an integer[] takes numbers, and an enum array only accepts its enum values, with autocomplete.
• Correct parameter binding. Values are bound as arrays of the column's own type, so enum and uuid arrays work without manual casts.
• Index-friendly SQL. Containment and overlap compile to @>, &&, and <@, which a GIN index serves directly.
• Everywhere where works. findMany, count, exists, paginate, cursor, updateMany, deleteMany, and relation filters.
• Fail fast outside PostgreSQL. An array filter on another dialect throws instead of silently matching nothing.

Define array columns

Use Drizzle's .array() on any PostgreSQL column. Nothing else is needed: better-drizzle detects native array columns and switches their where type to the array filter.

import { integer, pgEnum, pgTable, text, uuid } from 'drizzle-orm/pg-core';

export const role = pgEnum('role', ['admin', 'editor', 'viewer']);

export const posts = pgTable('posts', {
	id: integer('id').primaryKey(),
	title: text('title').notNull(),
	tags: text('tags').array().notNull(),
	editors: uuid('editors').array().notNull(),
	roles: role('roles').array().notNull(),
	scores: integer('scores').array(),
	flags: text('flags').array(),
});

Only native array columns get these operators. A jsonb column typed as an array (jsonb().$type<string[]>()) is JSON, not a PostgreSQL array, so it keeps the regular JSONB filters.

Operators

length accepts a number or the comparable operators: equals, in, notIn, lt, lte, gt, gte, and not.

Operators in the same filter object are combined with AND.

Examples

Exact equality

Pass an array directly, or use equals. Equality is exact: same elements, same order, same duplicates.

await client.posts.findMany({ where: { tags: ['drizzle', 'orm'] } });
await client.posts.findMany({ where: { tags: { equals: ['drizzle', 'orm'] } } });

Membership with has

The most common question: does this row's array contain a value?

const admins = await client.users.findMany({
	where: { roles: { has: 'admin' } }, // 'admin' | 'editor' | 'viewer'
});

All, any, or none of a list

// tagged with both
await client.posts.findMany({ where: { tags: { hasEvery: ['drizzle', 'postgres'] } } });

// tagged with either
await client.posts.findMany({ where: { tags: { hasSome: ['drizzle', 'prisma'] } } });

// tagged with neither
await client.posts.findMany({ where: { flags: { hasNone: ['archived', 'spam'] } } });

Only allowed values with containedBy

containedBy is the inverse of hasEvery: every element in the row must come from your list. It is a natural fit for permission checks.

// posts whose roles are all ones the current user can manage
await client.posts.findMany({
	where: { roles: { containedBy: ['editor', 'viewer'] } },
});

Empty and non-empty arrays

const untagged = await client.posts.findMany({ where: { tags: { isEmpty: true } } });
const tagged = await client.posts.findMany({ where: { tags: { isEmpty: false } } });

Element counts with length

await client.posts.findMany({ where: { tags: { length: 3 } } });
await client.posts.findMany({ where: { tags: { length: { gte: 2, lte: 5 } } } });

Negation

not takes a value, null, or a nested array filter:

// does not equal this exact array
await client.posts.findMany({ where: { tags: { not: ['draft'] } } });

// has a value at all
await client.posts.findMany({ where: { scores: { not: null } } });

// does not contain 'admin'
await client.users.findMany({ where: { roles: { not: { has: 'admin' } } } });

Combining operators

Every operator in one object must hold. Mix them freely, and use AND / OR / NOT across columns like any other filter:

await client.posts.findMany({
	where: {
		tags: { has: 'drizzle', hasNone: ['deprecated'], length: { lte: 5 } },
		OR: [
			{ editors: { hasSome: [currentUserId] } },
			{ roles: { has: 'viewer' } },
		],
	},
});

Enum and uuid arrays

Values are bound as an array of the column's own type, so enum and uuid arrays need no casts, and the element type narrows to the enum's values:

await client.posts.findMany({
	where: {
		roles: { hasEvery: ['admin', 'editor'] }, // typos fail to compile
		editors: { has: '00000000-0000-0000-0000-000000000001' },
	},
});

Inside relation filters

Array filters work anywhere a where is accepted, including nested relation filters:

const authors = await client.users.findMany({
	where: {
		posts: { some: { tags: { has: 'drizzle' } } },
	},
	include: {
		posts: { where: { tags: { hasSome: ['drizzle', 'postgres'] } } },
	},
});

Every helper that takes where

const drizzlePosts = { tags: { has: 'drizzle' } } as const;

const total = await client.posts.count({ where: drizzlePosts });

const any = await client.posts.exists({ where: drizzlePosts });

const {
	data,
	pagination: { hasNext },
} = await client.posts.paginate({ where: drizzlePosts, limit: 20 });

const { count } = await client.posts.updateMany({
	where: drizzlePosts,
	data: { flags: ['featured'] },
});

Filter array elements

Use some, every, and none when the question is about a predicate on an individual element rather than exact membership. Their nested filter is typed from the array element: numeric arrays expose comparisons, string arrays expose patterns and mode, and enum arrays keep their literal values.

const posts = await client.posts.findMany({
	where: {
		scores: { some: { gt: 100 } },
		ages: { every: { gte: 0 } },
		emails: {
			some: { endsWith: '@rockfeller.com.br', mode: 'insensitive' },
		},
		flags: { none: { equals: 'archived' } },
	},
});

An empty array has no matching element, so some is false while every and none are true. A NULL array never matches these operators. A NULL item does not satisfy the nested predicate: it fails every, is ignored by some, and does not prevent none.

For some equality/list predicates and every equality/list predicates, better-drizzle emits PostgreSQL array operators (@>, &&, <@) that a GIN index can use. none is a negated containment/overlap condition, so the normal GIN operator class cannot narrow it selectively. Comparisons, patterns, compound filters, and nested not use unnest(); they also cannot use that GIN path. Use a normalized child table when those predicates are a primary access path on a large dataset.

Mutate arrays

Array mutations are PostgreSQL-only, atomic updates. They work in update, updateMany, updateEach, upsert, and explicit-object or callback upsertMany updates. A field takes either its normal replacement value or one mutation object.

await client.posts.update({
	where: { id: postId },
	data: {
		tags: { append: ['drizzle', 'orm'] },
		roles: { prepend: 'editor' },
		flags: { remove: ['archived', 'spam'] },
		scores: {
			replace: [
				{ from: 0, to: 1 },
				{ from: -1, to: 0 },
			],
		},
	},
});

addUnique adds only values that are not already present. It is still one SQL statement, preserves the order of the first input occurrence, and leaves duplicates that already exist in the stored array untouched:

await client.posts.update({
	where: { id: postId },
	data: { tags: { addUnique: ['drizzle', 'orm', 'drizzle'] } },
});

Use exactly one operator per field. Mutation lists must contain at least one non-null item. replace accepts either one { from, to } pair or a list of pairs, which runs from left to right. remove removes every occurrence, as PostgreSQL's array_remove() does. Direct replacement remains available:

await client.posts.update({
	where: { id: postId },
	data: { tags: [] },
});

For a NULL stored array, PostgreSQL's native functions keep their native semantics. addUnique also leaves NULL unchanged rather than treating it as an empty array.

Semantics worth knowing

NULL arrays never match an operator

A NULL array is not an empty array. has, hasEvery, hasSome, hasNone, containedBy, isEmpty, length, and a nested not all evaluate to SQL NULL for it, so the row is left out, even for hasNone and isEmpty: false.

Match NULL explicitly with equals: null (or null) and exclude it with not: null:

await client.posts.findMany({ where: { scores: null } }); // IS NULL
await client.posts.findMany({ where: { scores: { not: null } } }); // IS NOT NULL

Containment ignores order and duplicates

has, hasEvery, hasSome, hasNone, and containedBy treat the array as a set, as PostgreSQL does: ['a', 'b'] contains ['b', 'a'], and duplicates do not change the result. Exact equality (a plain array or equals) compares order and duplicates too.

Empty lists

An empty list is valid and follows PostgreSQL:

length counts every element

length and isEmpty use PostgreSQL cardinality(), which counts all elements across every dimension, so a 2x3 array has a length of 6. Duplicates are counted.

Indexing arrays

Why GIN

A regular B-tree index orders whole values, so it can answer "is the array exactly ['a', 'b']" but not "does the array contain 'a'". A GIN (Generalized Inverted Index) index stores every element separately, pointing at the rows that contain it, like the index at the back of a book. A containment or overlap query then reads only the lists for the elements you ask about, instead of opening every row.

PostgreSQL's default GIN operator class for arrays (array_ops) supports exactly the operators better-drizzle generates:

Create it with Drizzle

Declare the index next to the table and generate the migration with drizzle-kit as usual. The API changed in drizzle-orm 0.31:

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

export const posts = pgTable(
	'posts',
	{
		id: integer('id').primaryKey(),
		tags: text('tags').array().notNull(),
		roles: text('roles').array().notNull(),
	},
	(table) => [
		index('posts_tags_gin').using('gin', table.tags),
		index('posts_roles_gin').using('gin', table.roles),
	],
);

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

export const posts = pgTable(
	'posts',
	{
		id: integer('id').primaryKey(),
		tags: text('tags').array().notNull(),
		roles: text('roles').array().notNull(),
	},
	(table) => ({
		tagsGin: index('posts_tags_gin').on(table.tags).using(sql`gin`),
		rolesGin: index('posts_roles_gin').on(table.roles).using(sql`gin`),
	}),
);

Both produce the same SQL:

create index "posts_tags_gin" on "posts" using gin ("tags");

On a large table that is already in production, build the index without blocking writes with create index concurrently. It cannot run inside a transaction, and Drizzle's migrator wraps migrations in one, so run that statement as a one-off script outside the migrator. .concurrently() on the index builder only changes the generated SQL.

Check that the index is used

better-drizzle generates the same @> / && / <@ predicates you would write by hand, so the planner makes the same choice for both. The bun run bench:arrays suite checks exactly that: identical rows and a plan that uses the GIN index on both sides. Use .explain() on your own queries and look for a Bitmap Index Scan on the GIN index:

const {
	statements: [plan],
} = await client.posts
	.findMany({ where: { tags: { has: 'drizzle' } } })
	.explain();

Bitmap Heap Scan on posts
  Recheck Cond: (tags @> '{drizzle}'::text[])
  ->  Bitmap Index Scan on posts_tags_gin
        Index Cond: (tags @> '{drizzle}'::text[])

A Seq Scan on posts instead means there is no usable index, or that the table is small enough that PostgreSQL decided scanning it is cheaper. On tiny tables that is expected and fine.

Benchmark parity

bun run bench:arrays seeds 100,000 rows, validates result parity against a manual Drizzle statement for the GIN-backed has predicate and addUnique, then benchmarks every mutation with the same UPDATE … RETURNING shape.

On PostgreSQL 16, Bun 1.4.0, and a local 13th-gen i7, one run measured:

Absolute timings vary with hardware, cache state, and PostgreSQL settings; use the paired ratio from your own run. The native mutations remain one atomic SQL statement. append and prepend intentionally expose the normal repository update overhead because they grow the measured row; they are not a claim that the SQL primitive itself is slower.

Length and emptiness

length and isEmpty compile to cardinality(column), which the GIN index cannot serve. If you filter on them often, add an expression index. drizzle-orm 0.31+ accepts an sql expression in on(); on 0.30, add it in a SQL migration:

(table) => [
	index('posts_tags_gin').using('gin', table.tags),
	index('posts_tags_count').on(sql`cardinality(${table.tags})`),
],

create index posts_tags_count on posts ((cardinality(tags)));

Trade-offs

GIN indexes are larger than B-tree indexes and make writes to the column slower, because each element gets its own entry. PostgreSQL softens this with a pending list (fastupdate, on by default) that batches new entries. For read-heavy filters such as tags, roles, and permissions, the trade is almost always worth it. For a write-heavy column you rarely filter on, skip the index.

Validation with Zod

The Zod plugin understands array columns: create and update schemas expect arrays of the element type, and where schemas accept the array filter operators with the same element type.

Limits and dialect support

• PostgreSQL only. An array filter object on SQLite or MySQL throws ARRAY_QUERY_UNSUPPORTED (status 400). Plain equality with an array value still goes through the regular filter path.
• Native array columns only. JSON columns typed as arrays use the JSONB filters instead.
• Whole-element comparisons. Operators compare array elements for equality. Predicates on individual elements, such as "any score above 10", need raw SQL with ANY / unnest, which you can pass as a Drizzle sql fragment in where.
• No ordering by array contents. orderBy takes scalar columns.