Array filters

Use array filters when a PostgreSQL array column (tags, roles, permissions) decides which rows you read, and you want typed operators instead of @>, &&, <@, and cardinality() in raw SQL.

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

• 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.

To change array contents, see array updates. To index array columns, see array indexing.

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.

Match elements

A plain array (or equals) is exact equality: same elements, same order, same duplicates. has asks whether one element is present, hasEvery / hasSome / hasNone take a list, and containedBy requires every element of the row to come from your list (a natural fit for permission checks). Enum arrays only accept their enum values:

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

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

await client.posts.findMany({ where: { tags: { hasEvery: ['drizzle', 'postgres'] } } }); // both
await client.posts.findMany({ where: { tags: { hasSome: ['drizzle', 'prisma'] } } }); // either
await client.posts.findMany({ where: { flags: { hasNone: ['archived', 'spam'] } } }); // neither

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

Values are bound as an array of the column's own type, so enum and uuid arrays need no casts: editors: { has: '00000000-0000-0000-0000-000000000001' } works as is.

Count elements

isEmpty and length compile to cardinality(). length takes a number or a range:

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

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

Negate and combine

not takes a value, null, or a nested array filter. Every operator in one object must hold, and AND / OR / NOT work across columns as usual:

await client.posts.findMany({ where: { tags: { not: ['draft'] } } }); // not this exact array
await client.posts.findMany({ where: { scores: { not: null } } }); // has a value at all
await client.users.findMany({ where: { roles: { not: { has: 'admin' } } } });

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

Use in relations and other helpers

Array filters work anywhere a where is accepted, including relation filters, include filters, count, exists, paginate, and bulk writes:

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

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

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

Filter individual 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 rows = await client.posts.findMany({
	where: {
		scores: { some: { gt: 100 }, every: { gte: 0 } },
		tags: { some: { startsWith: 'drizzle', 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.

Edge cases

Reference

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.

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.
• Element predicates. Containment operators compare whole elements for equality. Predicates on individual elements, such as "any score above 10", use some / every / none.
• No ordering by array contents. orderBy takes scalar columns.