[FEATURE]: Option to return null fields as undefined instead of null

[xl0]

Describe what you want

Heya. Maybe I'm missing the big picture, but I feel it would be easier to use Drizzle if it was possible to return null fields as undefined, equivalent to not returning them at all I guess.

Any chance this could be added as an option to everything that returns records?

[csimmons0]

return null fields as undefined, equivalent to not returning them at all I guess

I don't think that returning null fields as undefined is equivalent to not returning them for someone who is writing Javascript rather than Typescript, and that could be a problem.

Imagine some JS code that looks like this pseudocode:

// Assume the `foo` table has a nullable column `bar`.
// Get the same row twice, but the second time, exclude the `bar` column from the result.
result1 = db.query.foo.findFirst()
result2 = db.query.foo.findFirst({columns: {bar: false}})

console.log(result1.bar)
console.log(result2.bar)

Suppose that the row's bar is NULL in the database. With drizzle's current behavior, the first value logged is "null" while the second is "undefined". If we enabled the proposed option, both logs would output "undefined". With the proposed option enabled, JS code would lose the ability to distinguish a null value from a column that was excluded from a query.

~TS doesn't have this problem because the type inference distinguishes the two cases by not including the excluded column in the fields of the type that the query returns. The type checker wouldn't let you write the second log line.~

~All that said, JS users could just keep the default behavior or use this feature at their own peril. This feature would be great for me as a TS user whose codebase uses undefined everywhere and never uses null.~

EDIT: There are potential drawbacks that apply to TS just as much as to JS; see this comment.

[xl0]

Let's say I have a table for some objects. In my code, I want to manipulate the objects, create new ones, etc. If I don't define a custom interface for the objects, the type can be derived by drizzle and it will be something like

interface Foo {
    id: string;
    prop1: string|null;
    prop2: boolean|null;
    ...
    prop10: string|null
}

So if I want to add a new object to my list/dictionary of objects, I have to define all the fields, which is tedious:

interface Foo {
    id: string;
    prop1?: string;
    prop2:? boolean;
    ...
    prop10?: string;
}

And now I'm free to add a new object as { id: 123, prop1: foo } with only the props I want to set, use them in my code, and insert them into the database. If I want to update just prop7 in the database, I can do update().set() , or insert() with a simple { id: 123, prop7: asd }, undefined fields are handled as expected on insert/update.

This works, but I have to define those interfaces for all tables (with optional extra fields for with) and cast everything returned from drizzle to my interface types.

I'm not very experienced with TS/drizzle, but feel this is a pretty common use pattern. Am I missing a better way?

[mirismaili]

Working with undefiends is more comfortable than nulls in both TypeScript and JavaScript:

• In TypeScript, undefined-able properties and parameters, easily can be flagged as optional by just using a simple ? mark. But null-able properties are not necessarily optional and should be defined as Type | null.

• In JavaScript, default parameters (in functions parameter-list) and default properties (in destructed objets) are preferred over undefineds, but not over nulls.

---

UPDATE

And in JSON schemas: null:

{
  "properties": {
    "icon": {"type": ["string", "null"]},
  },
  "required": ["icon"]
}

undefined:

{
  "properties": {
    "icon": {"type": "string"},
  }
}

And in some validators: TypeBox:

Type.Union([Type.String(), Type.Null()])

vs.

Type.Optional(Type.String())

[paulsohn]

return null fields as undefined, equivalent to not returning them at all I guess

I don't think that returning null fields as undefined is equivalent to not returning them for someone who is writing Javascript rather than Typescript, and that could be a problem.

Imagine some JS code that looks like this pseudocode:

// Assume the `foo` table has a nullable column `bar`.
// Get the same row twice, but the second time, exclude the `bar` column from the result.
result1 = db.query.foo.findFirst()
result2 = db.query.foo.findFirst({columns: {bar: false}})

console.log(result1.bar)
console.log(result2.bar)

Suppose that the row's bar is NULL in the database. With drizzle's current behavior, the first value logged is "null" while the second is "undefined". If we enabled the proposed option, both logs would output "undefined". With the proposed option enabled, JS code would lose the ability to distinguish a null value from a column that was excluded from a query.

TS doesn't have this problem because the type inference distinguishes the two cases by not including the excluded column in the fields of the type that the query returns. The type checker wouldn't let you write the second log line.

All that said, JS users could just keep the default behavior or use this feature at their own peril. This feature would be great for me as a TS user whose codebase uses undefined everywhere and never uses null.

Just a nit, but I suppose result1.bar would yield Cannot read properties of null error...?

[MickL]

I am consistently getting type errors and need to transform all null values to undefined manually, very cumberstone!

Very simplified example, my event handler needs to return an interface with optional values:

interface ItemResponse {
   name: string;
   color?: string;
   size?: string;
}

What I want:

export default defineEventHandler(async (event): Promise<ItemResponse[]> => {
    return useDb().select().from(items);
});

What I have to do:

export default defineEventHandler(async (event): Promise<ItemResponse[]> => {
    const items = const useDb().select().from(items);

    return items.map(item => ({
        ...item,
        color: item.color | undefined,
        size: item.size | undefined,
    }))
});

It would be great to be able to configure Drizzle to NOT return values that are NULL in the database.

[greatsolace]

It would be great to be able to configure Drizzle to NOT return values that are NULL in the database.

Agreed.

[jacwright]

I'm running into this issue as well and am having to do the workarounds others have stated.

When I insert an object and leave out a field (it is undefined), I expect it to come back out of the database the same way, with that field being undefined. It is changed to null.

interface MyObj {
  foo: string;
  bar?: string;
}

db.insert(myTable).values({ foo: 'foobar' });
const items = db.select().from(myTable);
console.log(items); // [ { foo: 'foobar', bar: null } ]
// no longer matches my interface MyObj even though it did going in

[ngryman]

For those interested in converting Drizzle-inferred type fields from nullable (e.g., string | null) to optional (e.g., string | undefined):

type NullToUndefined<T> = {
  [K in keyof T]: T[K] extends null
    ? undefined
    : T[K] extends (infer U)[]
      ? NullToUndefined<U>[]
      : Exclude<T[K], null> | ([null] extends [T[K]] ? undefined : never);
};

type User = NullToUndefined<typeof userTable.$inferSelect>;

You can then cast queries return type to this type:

const user = (await db.query.user.findFirst()) as User | undefined;

---

On a side note, the Drizzle team should really take a look at this issue. undefined is a first-class citizen in TypeScript, whereas null is not. This makes it difficult to integrate with other libraries expecting optional values, which makes Drizzle overall DX worse than necessary.

[daweijs]

agree, null should never be used in javascript (typeof null === 'object')

there is no difference between handling a NULL database property and a a property that was not in the SELECT clause NULL is the database way of representing the "absence of value". how do you represent the absence of value in JS ? with undefined

even if I insert an "undefined" the database will store a NULL ...

[logan272]

This is such an annoying issue.

const abc = pgTable('abc', {
  id: uuid('id').primaryKey().notNull().defaultRandom(),
  a: text('a'),
  b: text('b'),
  c: text('b'),
});

type Abc = InferSelectModel<typeof chatTable>;

Currently, the inferred type Abc is:

{id: string; a: string | null; b: string | null; c: string | null;}

I would prefer it to be:

{id: string; a?: string; b?: string; c?: string;}

As a result, whenever I create an Abc value, I have to explicitly specify all the null fields:

const a: Abc = {
  a: "a str",
  b: null,
  c: null,
};

If Abc were defined as {id: string; a?: string; b?: string; c?: string;}, I could simply do:

const a: Abc = {
  a: "a str",
};

Working with null values is quite cumbersome!

[gemue-parndt]

This is such a big point. It's really annoying to deal with this typing thing. The standard should be undefined so we don't have to explicitly define null values.

Really looking forward to see this feature in drizzle.

[MaelAbgrall]

agree, null should never be used in javascript (typeof null === 'object')

there is no difference between handling a NULL database property and a a property that was not in the SELECT clause NULL is the database way of representing the "absence of value". how do you represent the absence of value in JS ? with undefined

even if I insert an "undefined" the database will store a NULL ...

It's where this can be tricky... I'm personally using undefined as a way to say if a field is updated or not (eg: Null to remove, undefined to ignore when doing an update). So changing this behaviour will break my apps.

But at the same time, I do agree that for output, I'm re-mapping every Null field into undefined.

This would be a huge breaking change, but having a simpler .nullToUndefined() built-in would be more compatible than changing how the default results are returned.

[ashfaqnisar]

Hi, Just wanted to express support for this issue. It would be really helpful to have this addressed. Thanks to the maintainers and contributors for all your work!

[transitive-bullshit]

I was really surprised that Drizzle didn't already work this way, so I created a PoC of this by forking drizzle here: https://github.com/drizzle-team/drizzle-orm/pull/4521

Here's an example of standalone usage: https://github.com/transitive-bullshit/drizzle-optionals-test

Note that I won't be maintaining this fork for others as it's for my own personal use, but hopefully this proof-of-concept pushes the maintainers to take a closer look at this issue.

[csimmons0]

I liked this idea at first, but I am now wary of it. Drizzle's current behavior, with the separation of null and undefined, allows us to differentiate between:

• Reading a column that had the value NULL versus omitting that column from a SELECT clause.
• Setting a column's value to NULL versus omitting that column from an UPDATE clause.

The ability to distinguish these cases from one another--especially the ability to distinguish updating a column to null vs leaving it unmodified--is important. Granted, the original request was scoped only to reads, not updates. There may be a good way to achieve what the OP was looking for; I just want to make sure we choose a solution that doesn't backfire on us.

[transitive-bullshit]

@csimmons0 agree that it's not 100% compatible with the existing version, but imho I find it preferable for a more natural TS API for the 95% happy path use case.

Reading a column that had the value NULL versus omitting that column from a SELECT clause.

Yep; that's one of the core intentions of this feature request. In TS, you're referring to key-optional properties versus value-optional properties. ({ foo?: string | undefined } vs { foo: string | undefined } vs the current ugly { foo: string | null }).

I'm sure there are use cases that need this differentiation (I'm ignore these in my fork because I don't need them). This would especially be important for backwards compat.

That being said, I'd prefer to be able to opt-in to this behavior – at least for Select types and values – as opposed to currently where the only sane workaround I could find to support this was to fork drizzle.

Setting a column's value to NULL versus omitting that column from an UPDATE clause.

This is not an issue with my fork assuming I'm understanding correctly. I got around it on the types side by differentiating GetColumnData types for query (select) vs write (insert / update) operations. And on the data side, undefined / omitted values are still not included in the update set (see this line which hasn't changed between the two versions https://github.com/transitive-bullshit/drizzle-orm/blob/b3d80e03e8801ccf42e5107369d1dcbd0152f885/drizzle-orm/src/utils.ts#L116). You can still set fields to null in inserts/updates, and any field which has been explicitly set to null will have a NULL value in the database.

So for insert/update values, optional fields are nullable and key-optional.

For select values, optional fields are key-optional.

---

With all that being said, I still realize this would have serious repercussions for backwards compatibility, so here are some options to address these issues and not break backwards compat...

Maybe make this change opt-in?

The hard part is that this touches really core types and data plumbing, so it'd "infect" the types throughout the codebase and make internal DX and external examples more complicated / less consistent :sigh:

[gemue-parndt]

I would really like to see it as an opt-in feature. For those who want undefined instead of null they can enable it so it won't break the compatibility. Others are happy to not writing the ugly | null everytime.

[suiramdev]

Drizzle is lacking some important stuff. I need that feature too.

[MickL]

10 months old issue, 57 upvotes, still no reaction from Drizzle team ...

This is what I use atm:

/**
 * @deprecated Remove when this is fixed: https://github.com/drizzle-team/drizzle-orm/issues/2745
 */
export const removeNull = <T>(obj: T): NonNull<T> => {
  if (!!obj && typeof obj === 'object' && !Array.isArray(obj)) {
    // Process object entries recursively
    return Object.entries(obj).reduce((acc, [key, value]) => {
      const cleanedValue = removeNull(value);
      if (cleanedValue !== null) {
        // eslint-disable-next-line
        (acc as any)[key] = cleanedValue; // Safe cast since we're building a new object
      }
      return acc;
    }, {} as NonNull<T>);
  } else if (Array.isArray(obj)) {
    // Process each array element recursively
    return obj.map((item) => removeNull(item)) as NonNull<T>;
  }

  // Return other primitives as-is
  return obj as NonNull<T>;
};

Usage:

  const item = await useDb().query.customers.findFirst({
     // ...
  });

  return removeNull(item);

[BB-FilZembowicz]

I too hate nulls in results -- especially since typeof null == "object" :)

But as a reminder -- most of the drizzle team is in Ukraine, missiles flying overhead on many sleepless nights, working off of generator power when they have it -- so I would give them a little bit of leeway and understanding if features don't get resolved quickly.

[AndriiSherman]

Hey everyone!

I've created this message to send in a batch to all opened issues we have, just because there are a lot of them and I want to update all of you with our current work, why issues are not responded to, and the amount of work that has been done by our team over ~8 months.

I saw a lot of issues with suggestions on how to fix something while we were not responding – so thanks everyone. Also, thanks to everyone patiently waiting for a response from us and continuing to use Drizzle!

We currently have 4 major branches with a lot of work done. Each branch was handled by different devs and teams to make sure we could make all the changes in parallel.

---

First branch is drizzle-kit rewrite

All of the work can be found on the alternation-engine branch. Here is a PR with the work done: https://github.com/drizzle-team/drizzle-orm/pull/4439

As you can see, it has 167k added lines of code and 67k removed, which means we've completely rewritten the drizzle-kit alternation engine, the way we handle diffs for each dialect, together with expanding our test suite from 600 tests to ~9k test units for all different types of actions you can do with kit. More importantly, we changed the migration folder structure and made commutative migrations, so you won't face complex conflicts on migrations when working in a team.

What's left here:

• We are finishing handling defaults for Postgres, the last being geometry (yes, we fixed the srid issue here as well).
• We are finishing commutative migrations for all dialects.
• We are finishing up the command, so the migration flow will be as simple as drizzle-kit up for you.

Where it brings us:

• We are getting drizzle-kit into a new good shape where we can call it [email protected]!

Timeline:

• We need ~2 weeks to finish all of the above and send this branch to beta for testing.

Second big branch is a complex one with several HUGE updates

• Bringing Relational Queries v2 finally live. We've done a lot of work here to actually make it faster than RQBv1 and much better from a DX point of view. But in implementing it, we had to make another big rewrite, so we completely rewrote the drizzle-orm type system, which made it much simpler and improved type performance by ~21.4x:

(types instantiations for 3300 lines production drizzle schema + 990 lines relations)

TS v5.8.3: 728.8k -> 34.1k
TS v5.9.2: 553.7k -> 25.4k

You can read more about it here.

What's left here:

• We have 1 issue with TS that is already in progress of being fixed. The issue and Post about fixing.

Where it brings us:

• We are getting drizzle-orm into a new good shape where we can call it [email protected]!

Breaking changes:

• We will have them, but we will have open channels for everyone building on top of drizzle types, so we can guide you through all the changes.

Third branch is adding support for CockroachDB and MSSQL dialects

Support for them is already in the alternation-engine branch and will be available together with the drizzle-kit rewrite.

Summary

All of the work we are doing is crucial and should be done sooner rather than later. We've received a lot of feedback and worked really hard to find the best strategies and decisions for API, DX, architecture, etc., so we can confidently mark it as v1 and be sure we can improve it and remain flexible for all the features you are asking for, while becoming even better for everyone building on top of the drizzle API as well.

We didn't want to stay with some legacy decisions and solutions we had, and instead wanted to shape Drizzle in a way that will be best looking ahead to 2025–2026 trends (v1 will get proper effect support, etc.).

We believe that all of the effort we've put in will boost Drizzle and benefit everyone using it.

Thanks everyone, as we said, we are here to stay for a long time to build a great tool together!

Timelines

We are hoping to get v1 for drizzle in beta this fall and same timeline for latest. Right after that we can go through all of the issues and PRs and resond everyone. v1 for drizzle should close ~70% of all the bug tickets we have, so on beta release we will start marking them as closed!

[raldone01]

@MickL Can you provide the definition of NonNull?

[mirismaili]

Unfortunately, it seems it's not patchable even using a customType. For example:

export const Varchar = customType<{
  data: string | undefined
  driverData: string | null
  notNull: true
  config?: PgVarcharConfig
}>({
  dataType: (config) => (config?.length ? `varchar(${config.length})` : 'varchar'),
  toDriver: (value?: string) => value ?? null,
  fromDriver: (value: string | null) => value ?? undefined,
})

fixes the types, but DOESN'T WORK at run time. Because, fromDriver isn't called for null values!

[ArianSha]

+1

[menestrello]

Really an hugely annoying missing feature. Enough to contemplate using other tools. And many are coming up.

[a5ket]

Would be really good to have such feature and avoid adapting all the types for possible null values

[peterjwest]

For anyone looking for an intermediate solution, I'm using this to translate input/output:

export type ConvertType<From, To, Type> = Type extends From
    ? To
    : Type extends (infer U)[]
    ? NullToUndefined<U>[]
    : Type extends Record<string, unknown>
    ? { [K in keyof Type]: NullToUndefined<Type[K]> }
    : Type;

export type NullToUndefined<T> = ConvertType<null, undefined, T>;
export type UndefinedToNull<T> = ConvertType<undefined, null, T>;

export function fromNulls<T>(object: T): NullToUndefined<T> {
    if (object === null || object === undefined) return undefined as NullToUndefined<T>;
    if (object.constructor.name === 'Object' || Array.isArray(object)) {
        for (const key in object) {
            object[key] = fromNulls(object[key]) as (T & {})[Extract<keyof (T & {}), string>];
        }
    }
    return object as  NullToUndefined<T>;
}

export function toNulls<T>(object: T): UndefinedToNull<T> {
    if (object === null || object === undefined) return null as UndefinedToNull<T>;
    if (object.constructor.name === 'Object' || Array.isArray(object)) {
        for (const key in object) {
            object[key] = toNulls(object[key]) as (T & {})[Extract<keyof (T & {}), string>];
        }
    }
    return object as UndefinedToNull<T>;
}