Drizzle leftJoin returns nullable pet rows

Drizzle ORM version zero point forty-five does exactly what SQL says here. The `pets.name` column is required in the table. The left join still returns Linus with `pets` set to null, because Linus has no matching pet row. That is the surprise to keep in your head: a required column only tells you what a pet row looks like after a pet row exists.

export const pets = pgTable("pets", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  ownerId: integer("owner_id")
    .notNull()
    .references(() => users.id),
});

{
  users: {
    id: 2,
    name: "Linus",
  },
  pets: null,
}

Start from the package install, not from a copied snippet. This lab uses Drizzle ORM with the PostgreSQL driver, so the code uses `drizzle-orm/node-postgres` and the `pg` package. The install capture is short because the lockfile already pins the project. If you are following along in a different project, the important part is the driver match: the query builder code below is the SQL-like Drizzle API, not the relational query API.

Saved lockfile

+ drizzle-orm@0.45.3
+ drizzle-kit@0.31.11
+ pg@8.23.0
+ typescript@5.9.2

"dependencies": {
  "drizzle-orm": "0.45.3",
  "pg": "8.23.0"
},
"devDependencies": {
  "drizzle-kit": "0.31.11",
  "typescript": "5.9.2"
}

The seed is deliberately tiny. Ada has a pet named Byte. Linus has no pet. That is enough to make the join shape visible, because one result row matches the right table and one result row misses the right table. The schema also gives `pets.name` a `notNull()` rule, so the lab includes the exact mismatch that causes confusion in a real codebase.

[✓] Your SQL migration file
was generated

[✓] migrations applied
to drizzle_manual_leftjoin

seeded users [ "Ada", "Linus" ]
seeded pets [ "Byte" ]

This is the smallest real query. A left join is a SQL join that keeps every row from the left table, even when the right table has no matching row. In this query, `users` is the left table. `pets` is the joined table. Read the chain as plain SQL: select, from users, left join pets, on user id equals pet owner id. Drizzle returns Ada with a pet object and Linus with `pets` set to null.

const wholeTable = await db
  .select()
  .from(users)
  .leftJoin(pets, eq(users.id, pets.ownerId))
  .orderBy(users.id);

users: { id: 1, name: "Ada" },
pets: { id: 1, name: "Byte", ownerId: 1 }

users: { id: 2, name: "Linus" },
pets: null

The manual says the same thing before the lab does. In the full-table select, Drizzle keeps the `users` object present and makes the `pets` object nullable. Nullable means the TypeScript type allows `null` because the query can return no value there. The type is doing you a favor here. TypeScript makes you handle Linus before you reach for `pets.name`.

The generated SQL matters because the TypeScript type follows the SQL rule. Drizzle emits `from users left join pets on users.id equals pets.owner_id`. SQL keeps the user row. SQL leaves the pet columns empty when the pet row is missing. Drizzle carries that empty right side into TypeScript as `null`. Once you connect those two facts, the rest of the API stops feeling mysterious.

select "users"."name", "pets"."id", upper("pets"."name")
from "users"
left join "pets" on "users"."id" = "pets"."owner_id"
order by "users"."id"

Here is the first rule you reuse. When the query selects whole tables, the joined table becomes one nullable object. The type check says `WholeRow["pets"]` is either an object with `id`, `name`, and `ownerId`, or null. `pets.name` can be required inside a pet row. The pet row itself can still be missing. So the safe access pattern is to check the `pets` object first, then read fields from the pet object.

type WholeRow = (typeof wholeTable)[number];
type WholeTableCheck = Expect<
  Equal<
    WholeRow["pets"],
    {
      id: number;
      name: string;
      ownerId: number;
    } | null
  >
>;

tsc --noEmit

type assertions passed

The second rule shows up when you select individual fields. The result no longer has a `pets` object. The result has fields like `petId` and `petName`. Those fields are nullable, because Linus has no pet row to supply an id or a name. This is the shape you usually want for tables, exports, and API responses where one flat row is easier to consume than two nested objects.

{
  userName: "Linus",
  petId: null,
  petName: null,
  upperPetName: null,
}

The third rule is the one people usually miss. A raw `sql` expression is a hand-written SQL fragment inside a Drizzle select. Drizzle cannot infer the nullable result of `upper(pets.name)` for you. The docs show the untyped expression as `unknown`. The typed version writes `sql`, and the Linus row returns `upperPetName` as null. If the raw expression depends on the joined table, make the raw expression type nullable unless the SQL expression itself guarantees a value.

const partial = await db.select({
  userName: users.name,
  petId: pets.id,
  petName: pets.name,
  upperPetName: sql<string | null>`upper(${pets.name})`,
});

This is the part people get wrong. `notNull()` describes a column on a row that exists. A left join can still produce no joined row. When no pet row exists for Linus, Drizzle has no `pets.name` value to read, so `petName` is null in the partial select. Put another way, the table definition answers one question: can a saved pet row have a missing name? The left join answers a different question: did this user match any pet row?

name: text("name").notNull()

{
  userName: "Linus",
  petId: null,
  petName: null,
}

This is the part people get wrong with computed fields. Drizzle tracks table columns through the join. A raw SQL fragment is your own expression, so you write the result type yourself. If the expression reads from a left-joined table, put `null` in that type. The type parameter is documentation for the compiler and for the next person reading the query. It says this computed value disappears when the joined row disappears.

unknownUpperName: sql`upper(${pets.name})`,
annotatedUpperName: sql<string | null>`upper(${pets.name})`,

type UnknownRawSqlCheck =
  Expect<Equal<PartialRow["unknownUpperName"], unknown>>;
type AnnotatedRawSqlCheck =
  Expect<Equal<PartialRow["annotatedUpperName"], string | null>>;

This is the part people get wrong with grouped objects. Drizzle has a helper shape where a nested object can become null as a group, instead of making every field nullable beside each other. GitHub issue sixteen oh three reports an edge case in that shape. The lab still reproduces it on version zero point forty-five. The joined branding row exists, `logoUrl` has a value, and the grouped `brand` object still comes back null. If you use grouped objects around nullable fields, write a small reproduction like this before you trust the shape in application code.

[
  {
    companyName: "Acme",
    brand: null,
  }
]
brand is null true
logoUrl survived null

Issue twenty forty-five is a side note, because the issue is about filters. The runtime row after `isNotNull(products.color)` only prints `"black"`, but the selected TypeScript type still includes `null` in the lab. That is useful context for Drizzle nullability, but the left-join rule still comes from the docs and the lab query. Keep the mental model narrow: SQL can remove rows at runtime, while TypeScript sometimes keeps the wider column type.

where(isNotNull(products.color))

$ bun gotcha-2045.ts
[
  {
    color: "black",
  }
]

FilteredColorCheck:
string | null

The reusable pattern is small. Select the joined table when you want the right side as one nullable object. Select fields when you want nullable scalar values. For a raw computed field, write the nullable type yourself. In this lab, the same missing pet row produces `pets: null`, `petId: null`, and `upperPetName: null`. That is the whole Drizzle left-join rule you need before you build the larger query.

await db.select().from(users).leftJoin(pets, eq(users.id, pets.ownerId));

await db.select({
  userName: users.name,
  petId: pets.id,
  petName: pets.name,
  upperPetName: sql<string | null>`upper(${pets.name})`,
}).from(users).leftJoin(pets, eq(users.id, pets.ownerId));