Published on

Null OR Undefined?

Authors

I was creating a stablecoin payment service named "StablePay" and it utilizes Drizzle ORM for managing SQLite database. To establish the integrity of data, The service is hugely leveraged Zod to create a schema and validate data format. And something interesting happened when I was debugging the code.

Zod Validation

In case if anyone doesn't know Zod: Zod is a "TypeScript-first schema validation with static type inference" library used to verify the soundness of data. JavaScript has two primitive data types to express "nothingness" (null and undefined), which are mapped to z.optional() and z.nullable(), respectively. Or you can just use z.nullish() to validate a field can be either set to null or not just defined (refer to TypeScript nullish).

Sematic Difference Between null And undefined

null means the variant is Intentional emptiness, while undefined means the variant is just uninitialized.

Take a quick example, say there's a cup with no water in it, that is null, representing the current empty state. If the cup is not existed, that will be a undefined, representing the cup is just not defined in current scene.

Cup generated by OpenAI ChatGPT

The sematic difference between null and undefined also makes behavior difference when serializing a JS object into JSON. Any key mapped to a undefined value in the object will be omitted in JSON.stringify, while a key with null value will not be omitted but instead be serialized with the JSON native null value.

> JSON.stringify({key: undefined})
'{}'
> JSON.stringify({key: null})
'{"key":null}'

Defined Table

A typical rational database (RDB), SQLite for example, stores and organizes data in predefined tables with rows and columns. In this scenario, since each column is defined, if a value is not set in the row, NULL will be applied.

In another word, a NULL in SQLite should be mapped to null in JavaScript, instead of undefined. Take an example table as below.

idnameemailphone_number
1Alicealice@example.comNULL
2BobNULL090-1234-5678
3Carolcarol@example.com080-9876-5432

Representing in Drizzle ORM schema will be like:

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core'

export const usersTable = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  email: integer(),
  phone_number: text(),
})

Drizzle has built-in support for Zod schema validation since 1.0 RC. To create a zod validation schema based on drizzle schema, we will use the following code to create one.

import { z } from 'zod/v4'
import { createSelectSchema } from 'drizzle-orm/zod'

const userSelectSchema = createSelectSchema(usersTable)

Now let's try to apply the schema. Turns out it will failed if any field is missing in the object. That's becasue for a SELECT action, any column is empty in a row will be represented as NULL, thus the generated zod schema use z.nullable() to represented the intention of emptiness.

// failed because missing `phone_number` field
> userSelectSchema.parse({id: 1, name: "Alice", email: null})
ZodError: [
  {
    "expected": "string",
    "code": "invalid_type",
    "path": [
      "phone_number"
    ],
    "message": "Invalid input: expected string, received undefined"
  }
]

// passed
> userSelectSchema.parse({id: 1, name: "Alice", email: null, phone_number: "123-456-7890"})
{ id: 1, name: 'Alice', email: null, phone_number: '123-456-7890' }

// also passed
> userSelectSchema.parse({id: 1, name: "Alice", email: null, phone_number: null})
{ id: 1, name: 'Alice', email: null, phone_number: null }

Sometimes it's hard to avoid creating a custom type in drizzle for convenience, but currently the zod schema generation is not supported on custom type in drizzle, which means in this scenario, you have to extend the schema on your own. Take an example as below.

const isoDateTime = customType<{ data: Date; driverData: string }>({
  dataType: () => 'text',
  toDriver: (value: Date): string => value.toISOString(),
  fromDriver: (value: string): Date => new Date(value),
})

export const usersTable = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  created_at: isoDateTime(),
})

const userInsertSchema = createInsertSchema(usersTable)

Since we are using custom type in the table, the generated userInsertSchema on created_at will be inferred as unknown. We have to extend the generated zod schema. Example one as below.

const userInsertSchema = createInsertSchema(usersTable, {
  createdAt: z.datetime().nullish(), // both are fine in the case of INSERT, drizzle will handle it.
})

const userSelectSchema = createSelectSchema(usersTable, {
  createdAt: z.datetime().nullable(), // <-- use z.nullable() instead of z.optional()
})

const userUpdateSchema = createUpdateSchema(usersTable, {
  createdAt: z.datetime().nullish(), // if null -> column NULL, undefined -> column not updated
})

Conclusion

So the rule is actually quite simple: use null when the field is defined but intentionally empty, and use undefined when the field can be omitted.

In the view of rational database like SQLite, since every table is predefined, there's no undefined but NULL. So nullish validation on custom type should be careful handled next time creating when using drizzle orm and using built-in zod validation schema generation.

Links