D1 false type-safety using TypeScript

As explained here: https://developers.cloudflare.com/d1/build-with-d1/d1-client-api/#typescript-support

D1 client API is fully-typed via the @cloudflare/workers-types package, and also supports generic types as part of its TypeScript API. A generic type allows you to provide an optional type parameter so that a function understands the type of the data it is handling.

When using the query statement methods stmt.all(), stmt.raw() and stmt.first(), you can provide a type representing each database row. D1’s API will return the result object with the correct type.

For example, providing an OrderRow type as a type parameter to stmt.all() will return a typed Array<OrderRow> object instead of the default Record<string, unknown> type:

D1 client API is fully-typed via the @cloudflare/workers-types package, and also supports generic types as part of its TypeScript API. A generic type allows you to provide an optional type parameter so that a function understands the type of the data it is handling.

When using the query statement methods stmt.all(), stmt.raw() and stmt.first(), you can provide a type representing each database row. D1’s API will return the result object with the correct type.

For example, providing an OrderRow type as a type parameter to stmt.all() will return a typed Array<OrderRow> object instead of the default Record<string, unknown> type:

but trying this out it's giving false type-safety. Imagine this type definition:

type PetRow = {
    id: number;
    type: "DOG" | "CAT";
    name: string;
    dateOfBirth: string;
    dateOfDeath: string;
    chipNumber: string;
}

type PetRow = {
    id: number;
    type: "DOG" | "CAT";
    name: string;
    dateOfBirth: string;
    dateOfDeath: string;
    chipNumber: string;
}

(note the camel case property names, and there is no breed field) and the following query:

  const result = await context.env.DB.prepare(
    `SELECT   id, type, name, date_of_birth, date_of_death, chip_number, breed
     FROM     pet
     ORDER BY name ASC
     LIMIT    100`
  ).all<PetRow>();

  const pets = result.results;

  const result = await context.env.DB.prepare(
    `SELECT   id, type, name, date_of_birth, date_of_death, chip_number, breed
     FROM     pet
     ORDER BY name ASC
     LIMIT    100`
  ).all<PetRow>();

  const pets = result.results;

(note the underscore case as it is stored like that in the database) Now the result is:

[
  {
    "id": 1,
    "type": "DOG",
    "name": "Simba",
    "date_of_birth": null,
    "date_of_death": null,
    "chip_number": '1234567890',
    "breed": 'Australian Shepard',
  },
  ..
]

[
  {
    "id": 1,
    "type": "DOG",
    "name": "Simba",
    "date_of_birth": null,
    "date_of_death": null,
    "chip_number": '1234567890',
    "breed": 'Australian Shepard',
  },
  ..
]

All properties (date_of_birth, date_of_death, chip_number, breed) in the query that do not match the type definition are still included in the result. It gives some false type-safety.

Cloudflare Docs

Query D1 · Cloudflare D1 docs

D1 client API allows you to interact with a D1 database from within a Worker.

1 Reply

johtso•8mo ago

maybe check out drizzle and/or drizzle-zod?

Gaming

Programming

D1 false type-safety using TypeScript