[BUG]: Weird output from SQL queries with d1-http

[nicholasgriffintn]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.38.2

What version of drizzle-kit are you using?

0.30.1

Other packages

No response

Describe the Bug

I'm gettting weird output from SQL queries where all the data is coming through as id for some reason.

For example, with this query:

const existingUserEmail = await db.query.user.findFirst({
        where: eq(user.email, providerUser.email),
      });

      console.log("existingUserEmail", existingUserEmail);

I'm getting back:

existingUserEmail {
  id: {
    id: 1,
    name: 'Nicholas Griffin',
    avatar_url: '',
    email: '',
    github_username: '',
    company: '',
    site: '',
    location: '',
    bio: '',
    twitter_username: '',
    created_at: '2024-12-15 15:13:04',
    updated_at: '2024-12-15 15:13:04',
    setup_at: null,
    terms_accepted_at: null
  },
  name: undefined,
  avatar_url: undefined,
  email: undefined,
  github_username: undefined,
  company: undefined,
  site: undefined,
  location: undefined,
  bio: undefined,
  twitter_username: undefined,
  created_at: undefined,
  updated_at: undefined,
  setup_at: undefined,
  terms_accepted_at: undefined
}

I'm trying to use d1-http as I can't use a binding with the framework, my config is here:

https://github.com/nicholasgriffintn/fosdem-pwa/blob/main/drizzle.config.ts

Also configuration with a proxy here:

https://github.com/nicholasgriffintn/fosdem-pwa/blob/main/app/server/db/index.ts

I've been scratching my head for ages, any ideas? Much appreciated if you do!

[alvin0]

@nicholasgriffintn Thank you for providing your reference source code. It seems I can resolve your issue. Please refer to the source code below.

import { drizzle } from "drizzle-orm/sqlite-proxy";
import * as schema from "./schema";

// Destructure and validate environment variables
const {
  CLOUDFLARE_D1_ACCOUNT_ID,
  CLOUDFLARE_D1_DATABASE_ID,
  CLOUDFLARE_D1_TOKEN,
} = process.env;

if (
  !CLOUDFLARE_D1_ACCOUNT_ID ||
  !CLOUDFLARE_D1_DATABASE_ID ||
  !CLOUDFLARE_D1_TOKEN
) {
  throw new Error("Missing required Cloudflare D1 environment variables.");
}

// Define TypeScript types for the API responses
type D1ResponseInfo = {
  code: number;
  message: string;
};

type D1Response = {
  result: {
    meta: {
      changed_db: boolean;
      changes: number;
      duration: number;
      last_row_id: number;
      rows_read: number;
      rows_written: number;
      size_after: number;
    };
    results: Array<unknown>;
    success: boolean;
  }[];
  errors: D1ResponseInfo[];
  messages: D1ResponseInfo[];
  success: boolean;
};

// Initialize the drizzle ORM with a custom fetch function
export const databaseD1 = drizzle(
  async (sql: string, params: any[], method: string) => {
    // console.log("method ", method);
    // Determine the endpoint based on the method
    const endpoint = method === "values" ? "raw" : "query";
    const url = `https://api.cloudflare.com/client/v4/accounts/${CLOUDFLARE_D1_ACCOUNT_ID}/d1/database/${CLOUDFLARE_D1_DATABASE_ID}/${endpoint}`;

    // Make the POST request to the Cloudflare D1 API
    const response = await fetch(url, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${CLOUDFLARE_D1_TOKEN}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ sql, params }),
    });

if (response.status !== 200)
      throw new Error(
        `Error from sqlite proxy server: ${response.status} ${
          response.statusText
        }\n${JSON.stringify(await response.json())}`
      );


    const responseJson = (await response.json()) as D1Response;

    // Check for HTTP errors
    if (!responseJson.success) {
      throw new Error(
        `Error from Cloudflare D1: ${response.status} ${
          response.statusText
        }\n${JSON.stringify(responseJson)}`
      );
    }

    const qResult = responseJson.result[0];
    const rows = qResult.results.map((r: any) => Object.values(r));

    return { rows: method == "all" ? rows : rows[0] };
  },
  { schema, logger: true }
);

[nicholasgriffintn]

Thanks, that's awesome! Did the trick looking at it, that was a real head scratch, you saved me.