null and undefined

better-drizzle gives the two values different meanings:

• undefined means "nothing here". In where, the field adds no condition. In data, the column is not written.
• null means SQL NULL. In where, it compiles to IS NULL. In data, it writes NULL.

The examples on this page add one nullable column to the users table from Getting started:

export const users = sqliteTable('users', {
	id: integer('id').primaryKey(),
	email: text('email').notNull().unique(),
	name: text('name').notNull(),
	active: integer('active', { mode: 'boolean' }).notNull().default(true),
	bio: text('bio'), // nullable
});

At a glance

undefined in where

A field whose value is undefined is skipped. It adds nothing to the SQL, as if the key were not there:

await client.users.findMany({
	where: { active: true, name: undefined },
});
// WHERE active = 1

The same applies everywhere a condition can appear:

• where: undefined, where: {}, and a where whose fields are all undefined produce no WHERE clause
• operator keys set to undefined (gt, in, contains, not, and the rest) are skipped: { id: { gt: undefined } } adds nothing
• AND, OR, and NOT drop entries that compile to nothing, and an empty combinator is dropped as a whole
• JSON path entries set to undefined are skipped

Empty filters on reads return unfiltered results

Because an all-undefined where is no filter at all, a read with a missing identifier does not fail. It returns rows it was not meant to:

const userId: number | undefined = session.userId; // undefined here

await client.users.findMany({ where: { id: userId } });
// every user

await client.users.findUnique({ where: { id: userId } });
// some row of the table, not null

findUnique, findFirst, and findOne all return the first matching row, and with no condition any row matches. Check identifiers before you query:

if (userId === undefined) throw new Error('Not signed in');

const user = await client.users.findUnique({ where: { id: userId } }).throw();

null in where

On a nullable column, null compiles to IS NULL. equals: null does the same, and not: null negates it:

const withoutBio = await client.users.findMany({
	where: { bio: null },
});

const withBio = await client.users.findMany({
	where: { bio: { not: null } },
});

null is only accepted where the column's type includes null. On a notNull() column such as name, name: null is a type error.

Negation and NULL

Other comparisons follow SQL's rules for NULL, so rows with a NULL value do not match them, negated or not:

• bio: { not: 'hello' } compiles to NOT (bio = 'hello'), which leaves out rows where bio is NULL
• bio: { in: ['a', null] } compiles to bio IN ('a', NULL), which never matches a NULL row

Add the NULL case explicitly when you want it:

await client.users.findMany({
	where: {
		OR: [{ bio: { not: 'hello' } }, { bio: null }],
	},
});

Relation filters

Relation filters treat null and empty objects like this:

// Users who have not written anything yet
const newUsers = await client.users.findMany({
	where: { posts: { none: {} } },
});

// Posts whose author row does not exist (useful with a nullable foreign key)
const orphans = await client.posts.findMany({
	where: { author: { is: null } },
});

See Relations for the full relation filter API.

JSON null vs SQL NULL

A JSONB column can hold SQL NULL (no document), and a document can hold JSON null at a path. They are filtered differently:

// The column itself is SQL NULL (nullable jsonb column)
await client.accounts.findMany({
	where: { settings: null },
});
// settings IS NULL

// The document stores an explicit JSON null at the path
await client.accounts.findMany({
	where: { settings: { json: { nickname: null } } },
});
// jsonb_typeof(settings #> '{nickname}') = 'null'

A path filter set to null does not match documents where the key is missing. See JSONB filters for not: null and missing keys.

In data, null for the whole column writes SQL NULL. null values inside the object are serialized as JSON null.

undefined and null in data

Write payloads are passed to Drizzle's insert().values() and update().set(), and follow Drizzle's rules:

// Only name changes; bio keeps its value
await client.users.update({
	where: { id: 1 },
	data: { name: input.name, bio: undefined },
});

// bio is cleared
await client.users.update({
	where: { id: 1 },
	data: { bio: null },
});

This makes optional form fields safe to pass straight through: an undefined field is left alone, and only an explicit null clears a value.

If every field in an update is undefined, there is nothing to set and Drizzle throws No values to set (for updateMany, only when some row matches).

For relations, null is a relation command rather than a column value: author: { set: null } and author: { disconnect: true } null the foreign key. See Relation writes.

Conditional filters

Since undefined fields are skipped, a filter built from optional inputs needs no if chains. Map each input to a condition or to undefined:

import type { WhereInput } from 'better-drizzle';

type UserSearch = {
	active?: boolean;
	search?: string;
	hasPosts?: boolean;
};

export function searchUsers(input: UserSearch) {
	const where = {
		active: input.active,
		name: input.search ? { contains: input.search } : undefined,
		posts: input.hasPosts ? { some: {} } : undefined,
	} satisfies WhereInput<typeof schema, 'users'>;

	return client.users.findMany({ where, orderBy: { id: 'asc' } });
}

With no inputs, searchUsers({}) returns every user. That is the intended result for a search screen, and exactly the wrong one for a write.

Check what a filter compiles to with .explain() before relying on it:

const { statements } = await client.users
	.findMany({ where: { active: undefined, name: 'Ada' } })
	.explain();
// statements[0].sql contains only the name condition

Writes with missing filters

For update, updateMany, delete, and deleteMany, a where that compiles to no condition is not run. The call returns null (update, delete) or { count: 0 } (updateMany, deleteMany) and touches no rows.

An update whose data contains relation writes is the exception: it first looks up the target row, so an empty where throws when the table has more than one row and updates the only row when it has exactly one.

The risk is a partly empty filter. The remaining conditions still apply, so the write reaches more rows than intended:

const userId: number | undefined = input.userId; // undefined here

await client.users.updateMany({
	where: { id: userId, active: true },
	data: { active: false },
});
// WHERE active = 1: every active user is deactivated

A plugin that adds a condition, such as tenant scoping, has the same effect: an otherwise empty where becomes the plugin's filter, and the write applies to every row it matches.

To update or delete every row on purpose, say so with an explicit condition:

import { sql } from 'drizzle-orm';

await client.users.updateMany({
	where: sql`1 = 1`,
	data: { active: false },
});

Guardrails

The rules plugin can reject these calls before they run:

• noUpdateManyWithoutWhere and noDeleteManyWithoutWhere catch an updateMany / deleteMany with no where; noUpdateWithoutWhere and noDeleteWithoutWhere do the same for update / delete
• noEmptyWhere also catches where: {}, and with treatEmptyAndOrAsEmpty an empty AND: [] / OR: []

import { rules } from 'better-drizzle/rules';

const client = better(db, {
	schema,
	plugins: [
		rules({
			noDeleteManyWithoutWhere: true,
			noUpdateManyWithoutWhere: true,
			noEmptyWhere: {
				level: 'error',
				operations: ['update', 'updateMany', 'delete', 'deleteMany'],
			},
		}),
	],
});

These rules look at the shape of where, not at its values. { id: undefined } has a key, so it is not empty to them. Validate identifiers in your own code before they reach a query.