[BUG]: drizzle-kit introspect TypeError: Cannot read properties of undefined (reading 'toLowerCase')

[xkalf]

What version of drizzle-orm are you using?

0.3.10

What version of drizzle-kit are you using?

0.21.2

Describe the Bug

``bun version 1.1.8

C:\Users\hitech\code\nomad_db\node_modules\drizzle-kit\bin.cjs:15875
              const onUpdate = fk4.update_rule.toLowerCase();
                                               ^

TypeError: Cannot read properties of undefined (reading 'toLowerCase')
    at C:\Users\hitech\code\nomad_db\node_modules\drizzle-kit\bin.cjs:15875:48
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

Node.js v18.18.2

getting this error on bunx drizzle-kit introspect command

db schema is here

import { relations, sql } from "drizzle-orm";
import {
  boolean,
  date,
  index,
  integer,
  numeric,
  pgEnum,
  pgTableCreator,
  primaryKey,
  real,
  serial,
  text,
  time,
  timestamp,
  unique,
  varchar,
} from "drizzle-orm/pg-core";
import { type AdapterAccount } from "next-auth/adapters";

/**
 * This is an example of how to use the multi-project schema feature of Drizzle ORM. Use the same
 * database instance for multiple projects.
 *
 * @see https://orm.drizzle.team/docs/goodies#multi-project-schema
 */
export const createTable = pgTableCreator(
  (name) => `nomad_competition_${name}`,
);

export const users = createTable("user", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  firstname: varchar("firstname", { length: 255 }).notNull(),
  lastname: varchar("lastname", { length: 255 }).notNull(),
  wcaId: varchar("wca_id", { length: 255 }).unique(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  phone: integer("phone").notNull(),
  birthDate: date("birth_date").notNull(),
  emailVerified: timestamp("emailVerified", {
    mode: "date",
  }).default(sql`CURRENT_TIMESTAMP`),
  image: varchar("image", { length: 255 }),
  isAdmin: boolean("is_admin").notNull().default(false),
  password: varchar("password", { length: 255 }).notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  accounts: many(accounts),
  competitors: many(competitors),
}));

export const accounts = createTable(
  "account",
  {
    userId: varchar("userId", { length: 255 })
      .notNull()
      .references(() => users.id),
    type: varchar("type", { length: 255 })
      .$type<AdapterAccount["type"]>()
      .notNull(),
    provider: varchar("provider", { length: 255 }).notNull(),
    providerAccountId: varchar("providerAccountId", { length: 255 }).notNull(),
    refresh_token: text("refresh_token"),
    access_token: text("access_token"),
    expires_at: integer("expires_at"),
    token_type: varchar("token_type", { length: 255 }),
    scope: varchar("scope", { length: 255 }),
    id_token: text("id_token"),
    session_state: varchar("session_state", { length: 255 }),
  },
  (account) => ({
    compoundKey: primaryKey({
      columns: [account.provider, account.providerAccountId],
    }),
    userIdIdx: index("account_userId_idx").on(account.userId),
  }),
);

export const accountsRelations = relations(accounts, ({ one }) => ({
  user: one(users, { fields: [accounts.userId], references: [users.id] }),
}));

export const sessions = createTable(
  "session",
  {
    sessionToken: varchar("sessionToken", { length: 255 })
      .notNull()
      .primaryKey(),
    userId: varchar("userId", { length: 255 })
      .notNull()
      .references(() => users.id),
    expires: timestamp("expires", { mode: "date" }).notNull(),
  },
  (session) => ({
    userIdIdx: index("session_userId_idx").on(session.userId),
  }),
);

export const sessionsRelations = relations(sessions, ({ one }) => ({
  user: one(users, { fields: [sessions.userId], references: [users.id] }),
}));

export const verificationTokens = createTable(
  "verificationToken",
  {
    identifier: varchar("identifier", { length: 255 }).notNull(),
    token: varchar("token", { length: 255 }).notNull(),
    expires: timestamp("expires", { mode: "date" }).notNull(),
  },
  (vt) => ({
    compoundKey: primaryKey({ columns: [vt.identifier, vt.token] }),
  }),
);

export const cubeTypes = createTable("cube_types", {
  id: serial("id").primaryKey(),
  name: varchar("name").notNull(),
  image: varchar("image"),
  order: real("order").notNull().default(1),
});

export const cubeTypesRelations = relations(cubeTypes, ({ many }) => ({
  competitorsToCubeTypes: many(competitorsToCubeTypes),
  competitionsToCubeTypes: many(competitionsToCubeType),
}));

export const competitions = createTable("competitions", {
  id: serial("id").primaryKey(),
  name: varchar("name").notNull(),
  address: varchar("address").notNull(),
  addressLink: varchar("address_link"),
  maxCompetitors: integer("max_competitors").notNull(),
  startDate: date("start_date").notNull(),
  endDate: date("end_date").notNull(),
  registerStartDate: timestamp("register_start_date", {
    withTimezone: true,
  }),
  registerEndDate: timestamp("register_end_date", {
    withTimezone: true,
  }),
  contact: text("contact"),
  registrationRequirments: text("registration_requirments"),
  baseFee: numeric("base_fee").notNull().default("0"),
  guestFee: numeric("guest_fee").notNull().default("0"),
  freeGuests: integer("free_guests").notNull().default(0),
});

export const competitionsRelations = relations(competitions, ({ many }) => ({
  competitors: many(competitors),
  competitionsToCubeTypes: many(competitionsToCubeType),
  schedules: many(schedules),
  ageGroups: many(ageGroups),
  fees: many(fees),
}));

export const competitionsToCubeType = createTable(
  "competitions_to_cube_type",
  {
    cubeTypeId: integer("cube_type_id")
      .notNull()
      .references(() => cubeTypes.id),
    competitionId: integer("competition_id")
      .notNull()
      .references(() => competitions.id),
  },
  (t) => ({
    pk: primaryKey({
      columns: [t.competitionId, t.cubeTypeId],
    }),
  }),
);

export const competitionsToCubeTypeRelations = relations(
  competitionsToCubeType,
  ({ one }) => ({
    cubeType: one(cubeTypes, {
      fields: [competitionsToCubeType.cubeTypeId],
      references: [cubeTypes.id],
    }),
    competition: one(competitions, {
      fields: [competitionsToCubeType.competitionId],
      references: [competitions.id],
    }),
  }),
);

export const competitors = createTable(
  "competitors",
  {
    id: serial("id").primaryKey(),
    userId: varchar("user_id")
      .notNull()
      .references(() => users.id),
    competitionId: integer("competition_id")
      .notNull()
      .references(() => competitions.id),
    guestCount: integer("guest_count").notNull().default(0),
    description: varchar("description"),
    requestedAt: timestamp("requested_at").notNull().defaultNow(),
    verifiedAt: timestamp("verified_at"),
  },
  (t) => ({
    competitionIdUserIdUniq: unique().on(t.competitionId, t.userId),
  }),
);

export const competitorsRelations = relations(competitors, ({ one, many }) => ({
  user: one(users, {
    fields: [competitors.userId],
    references: [users.id],
  }),
  competition: one(competitions, {
    fields: [competitors.competitionId],
    references: [competitions.id],
  }),
  competitorsToCubeTypes: many(competitorsToCubeTypes),
  invoices: many(invoices),
}));

export const competitorsToCubeTypes = createTable(
  "competitors_to_cube_types",
  {
    competitorId: integer("competitor_id")
      .notNull()
      .references(() => competitors.id),
    cubeTypeId: integer("cube_type_id")
      .notNull()
      .references(() => cubeTypes.id),
  },
  (t) => ({
    pk: primaryKey({
      columns: [t.competitorId, t.cubeTypeId],
    }),
  }),
);

export const competitorsToCubeTypesRelations = relations(
  competitorsToCubeTypes,
  ({ one }) => ({
    competitor: one(competitors, {
      fields: [competitorsToCubeTypes.competitorId],
      references: [competitors.id],
    }),
    cubeType: one(cubeTypes, {
      fields: [competitorsToCubeTypes.cubeTypeId],
      references: [cubeTypes.id],
    }),
  }),
);

export const schedules = createTable("schedules", {
  id: serial("id").primaryKey(),
  name: varchar("name").notNull(),
  startTime: time("start_time").notNull(),
  endTime: time("end_time").notNull(),
  date: date("date").notNull(),
  cutOff: varchar("cut_off"),
  timeLimit: varchar("time_limit"),
  competitorLimit: integer("competitor_limit"),
  competitionId: integer("competition_id")
    .references(() => competitions.id)
    .notNull(),
});

export const schedulesRelations = relations(schedules, ({ one }) => ({
  competition: one(competitions, {
    fields: [schedules.competitionId],
    references: [competitions.id],
  }),
}));

export const ageGroups = createTable("age_groups", {
  id: serial("id").primaryKey(),
  name: varchar("name").notNull(),
  start: integer("start").notNull(),
  end: integer("end"),
  order: real("order").notNull().default(1),
  competitionId: integer("competition_id")
    .notNull()
    .references(() => competitions.id),
  cubeTypeId: integer("cube_type_id")
    .references(() => cubeTypes.id)
    .notNull(),
});

export const ageGroupsRelations = relations(ageGroups, ({ one }) => ({
  competition: one(competitions, {
    fields: [ageGroups.competitionId],
    references: [competitions.id],
  }),
  cubeType: one(cubeTypes, {
    fields: [ageGroups.cubeTypeId],
    references: [cubeTypes.id],
  }),
}));

export const paymentsTypeEnum = pgEnum("payment_type", ["qpay"]);

export const payments = createTable("qpay", {
  type: paymentsTypeEnum("type").notNull().primaryKey().unique(),
  accessToken: text("access_token").notNull(),
  refreshToken: text("refresh_token").notNull(),
  accessExpiresAt: timestamp("access_expires_at", {
    mode: "date",
    withTimezone: true,
  }).notNull(),
  refreshExpiresAt: timestamp("refresh_expires_at", {
    mode: "date",
    withTimezone: true,
  }).notNull(),
});

export const invoices = createTable("invoices", {
  id: serial("id").primaryKey(),
  invoiceCode: varchar("invoice_code"),
  amount: numeric("amount").notNull(),
  isPaid: boolean("is_paid").notNull().default(false),
  competitorId: integer("competitor_id")
    .references(() => competitors.id)
    .notNull(),
  userId: varchar("user_id")
    .notNull()
    .references(() => users.id),
});

export const invoicesRelations = relations(invoices, ({ one }) => ({
  competitor: one(competitors, {
    fields: [invoices.competitorId],
    references: [competitors.id],
  }),
}));

export const fees = createTable("fees", {
  id: serial("id").primaryKey(),
  cubeTypeId: integer("cube_type_id")
    .notNull()
    .references(() => cubeTypes.id),
  amount: numeric("amount").default("0").notNull(),
  competitionId: integer("competition_id").references(() => competitions.id),
});

export const feesRelation = relations(fees, ({ one }) => ({
  cubeType: one(cubeTypes, {
    fields: [fees.cubeTypeId],
    references: [cubeTypes.id],
  }),
  competition: one(competitions, {
    fields: [fees.competitionId],
    references: [competitions.id],
  }),
}));

Expected behavior

No response

Environment & setup

No response

[smndtrl]

I encountered the same issue. It seems like that tableForeignKeys contains results for dbIndexes despite both queries being in different calling order.

tableForeignKeys 15851 dbIndexes 16011

Not going to dig any further - my skills end here

Edit: Could it be relation to transaction pooling mechanisms?

[smndtrl]

Okay, I could confirm for myself.

Using Supavisor for connection pooling only works when using session mode and not transaction mode. For Supabase users, switching to port 5432 instead of 6543 should be enough to mitigate

[stabildev]

Same issue here w/ supabase port 6543

const onUpdate = fk4.update_rule.toLowerCase();
                                               ^

TypeError: Cannot read properties of undefined (reading 'toLowerCase')

[Yash-pede]

A Fix is

This worked for me

export default defineConfig({
  schema: "./lib/supabase/schema.ts",
  dialect: "postgresql",
  out: "./migrations",
  dbCredentials: {
    url: process.env.DATABASE_URL || "",
    database: "postgres",
    port: 5432,
    host: "aws-0-ap-south-1.pooler.supabase.com",
    user: "postgres.user",
    password: process.env.PW || "",
  },
});

[shubham2105]

This worked for me.

[stabildev]

Ended up switching to node-postgres / pg because there were so many issues with postgres

[andrews1022]

Unfortunately, the suggestions in this thread are not working for me. Here is my current setup:

What version of drizzle-orm are you using? "drizzle-orm": "^0.31.2"

What version of drizzle-kit are you using? "drizzle-kit": "^0.22.7"

What version of node are you using? v20.9.0

I am on Ubuntu:

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.4 LTS
Release:    22.04
Codename:   jammy

Commands:

"generate": "drizzle-kit generate",
"push": "drizzle-kit push"

Running npm run generate works fine :heavy_check_mark:

Running npm run push produces the following error:

$ npm run push

> afk-teams@0.1.0 push
> drizzle-kit push

drizzle-kit: v0.22.7
drizzle-orm: v0.31.2

No config path provided, using default path
Reading config file '/home/andrew/Documents/web-dev/personal/nextjs-14/projects/afk-teams/drizzle.config.ts'
Using 'postgres' driver for database querying
[⣯] Pulling schema from database...
/home/andrew/Documents/web-dev/personal/nextjs-14/projects/afk-teams/node_modules/drizzle-kit/bin.cjs:21701
              const onUpdate = fk4.update_rule.toLowerCase();
                                               ^

TypeError: Cannot read properties of undefined (reading 'toLowerCase')
    at /home/andrew/Documents/web-dev/personal/nextjs-14/projects/afk-teams/node_modules/drizzle-kit/bin.cjs:21701:48
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

Node.js v20.9.0

.env file:

DATABASE_URL="postgres://postgres.[PROJECT]:[PASSWORD]@aws-0-us-west-1.pooler.supabase.com:6543/postgres"

GOOGLE_CLIENT_ID="..."
GOOGLE_CLIENT_SECRET="..."

NEXTAUTH_SECRET="..."
NEXTAUTH_URL="http://localhost:3000/"

I got the value for DATABASE_URL by going to https://supabase.com/dashboard/project/[PROJECT], clicking on the green Connect button, ensuring 'Display connection pooler' is checked and the Mode is set to Transaction. I then went to the ORMs tab, changed Tool to Drizzle, and copied / updated the value as displayed in this dialog.

drizzle config file, located at the root and called drizzle.config.ts:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/drizzle/schema.ts",
  dialect: "postgresql",
  out: "./src/drizzle/migrations",
  dbCredentials: {
    url: process.env.DATABASE_URL || ""
  }
});

schema file, located at src/drizzle/schema.ts:

import { boolean, integer, pgTable, primaryKey, text, timestamp, uuid } from "drizzle-orm/pg-core";
import type { AdapterAccount } from "next-auth/adapters";

export const users = pgTable("user", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: text("name"),
  email: text("email").notNull(),
  emailVerified: timestamp("emailVerified", { mode: "date" }),
  image: text("image")
});

export const accounts = pgTable(
  "account",
  {
    userId: text("userId")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    type: text("type").$type<AdapterAccount>().notNull(),
    provider: text("provider").notNull(),
    providerAccountId: text("providerAccountId").notNull(),
    refresh_token: text("refresh_token"),
    access_token: text("access_token"),
    expires_at: integer("expires_at"),
    token_type: text("token_type"),
    scope: text("scope"),
    id_token: text("id_token"),
    session_state: text("session_state")
  },
  (account) => ({
    compoundKey: primaryKey({
      columns: [account.provider, account.providerAccountId]
    })
  })
);

export const sessions = pgTable("session", {
  sessionToken: text("sessionToken").primaryKey(),
  userId: text("userId")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  expires: timestamp("expires", { mode: "date" }).notNull()
});

export const verificationTokens = pgTable(
  "verificationToken",
  {
    identifier: text("identifier").notNull(),
    token: text("token").notNull(),
    expires: timestamp("expires", { mode: "date" }).notNull()
  },
  (verificationToken) => ({
    compositePk: primaryKey({
      columns: [verificationToken.identifier, verificationToken.token]
    })
  })
);

export const authenticators = pgTable(
  "authenticator",
  {
    credentialID: text("credentialID").notNull().unique(),
    userId: text("userId")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    providerAccountId: text("providerAccountId").notNull(),
    credentialPublicKey: text("credentialPublicKey").notNull(),
    counter: integer("counter").notNull(),
    credentialDeviceType: text("credentialDeviceType").notNull(),
    credentialBackedUp: boolean("credentialBackedUp").notNull(),
    transports: text("transports")
  },
  (authenticator) => ({
    compositePK: primaryKey({
      columns: [authenticator.userId, authenticator.credentialID]
    })
  })
);

export const teams = pgTable("team", {
  teamId: uuid("teamId").defaultRandom().notNull().primaryKey(),
  userId: text("userId")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("createdAt", { mode: "date" }).defaultNow(),
  updatedAt: timestamp("updatedAt", { mode: "date" }).defaultNow(),
  deletedAt: timestamp("deletedAt", { mode: "date" }).defaultNow(),
  teamName: text("teamName").notNull(),
  gameMode: text("gameMode").notNull(),
  numberOfLikes: integer("numberOfLikes").default(0),
  isLikedByCurrentUser: boolean("isLikedByCurrentUser").default(false),
  isBookmarkedByCurrentUser: boolean("isBookmarkedByCurrentUser").default(false),
  heroAtPosition1: text("heroAtPosition1"),
  heroAtPosition2: text("heroAtPosition2"),
  heroAtPosition3: text("heroAtPosition3"),
  heroAtPosition4: text("heroAtPosition4"),
  heroAtPosition5: text("heroAtPosition5"),
  beast: text("beast"),
  description: text("description"),
  comments: text("comments").default("[]"),
  numberOfComments: integer("numberOfComments").default(0)
});

export const comments = pgTable("comment", {
  commentId: uuid("commentId").defaultRandom().notNull().primaryKey(),
  userId: text("userId")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  teamId: uuid("teamId")
    .notNull()
    .references(() => teams.teamId, { onDelete: "cascade" }),
  commentText: text("commentText").notNull(),
  createdAt: timestamp("createdAt", { mode: "date" }).defaultNow(),
  updatedAt: timestamp("updatedAt", { mode: "date" }).defaultNow(),
  deletedAt: timestamp("deletedAt", { mode: "date" }).defaultNow(),
  numberOfLikes: integer("numberOfLikes").default(0),
  replies: text("replies").default("[]")
});

The code for the user, account, session, and authenticator comes from the next-auth docs here. The code for the team and comment tables I wrote myself.

Database connection file, located at src/drizzle/config.ts:

import "dotenv/config";

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const connectionString = process.env.DATABASE_URL || "";

// Disable prefetch as it is not supported for "Transaction" pool mode
export const client = postgres(connectionString, { prepare: false });
export const db = drizzle(client);

This code comes from the Supabase documentation, as seen here.

Here is a link to my repo as well.

[andrews1022]

A Fix is

This worked for me

export default defineConfig({
  schema: "./lib/supabase/schema.ts",
  dialect: "postgresql",
  out: "./migrations",
  dbCredentials: {
    url: process.env.DATABASE_URL || "",
    database: "postgres",
    port: 5432,
    host: "aws-0-ap-south-1.pooler.supabase.com",
    user: "postgres.user",
    password: process.env.PW || "",
  },
});

Also, @Yash-pede is this from your repo notion-clone? If so, I noticed that your .env file is included with your repo, and your database credentials are fully exposed. I would strongly recommend updating your .gitignore to specify .env along with .env*.local:

# local env files
.env
.env*.local

[smndtrl]

Unfortunately, the suggestions in this thread are not working for me. Here is my current setup:

So you didn't change the port to 5432 and expect it to work without?

[andrews1022]

Unfortunately, the suggestions in this thread are not working for me. Here is my current setup:

So you didn't change the port to 5432 and expect it to work without?

I did try changing the port to 5432. That's why I said the suggestions in the thread are not working for me...

[smndtrl]

Okay, good to know. Your .env just still shows port 6543.

With it not working on 5432, it would mean that your problem is not necessarily related to what other people have been experiencing related to the supabase connection pooler.

[shubham2105]

You should probably refer to the documentations here, i was facing a similar issue with port and when i referenced to docs,they had changes in the way dbCredentials are to be written, you should definitely check it out once. I am pasting snip of my drizzle.config file below.

import {defineConfig} from 'drizzle-kit'; import * as dotenv from 'dotenv';

dotenv.config({path: '.env'});

if(!process.env.DATABASE_URL){ console.log("Cannot find Database URL") }

export default defineConfig({ schema: './src/app/lib/supabase/schema.ts', out: './migrations', dialect: 'postgresql', dbCredentials:{ url: process.env.DATABASE_URL || '', port: 5432, database: 'postgres', host: 'aws-0-ap-southeast-1.pooler.supabase.com', user: 'postgres.mqfucddewuigxsddyfon', password: process.env.PW || '', }, })

[lajbel]

having the same trouble following this guide

https://orm.drizzle.team/learn/migrate/migrate-from-prisma

my config:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
    dialect: "postgresql",
    out: "./src/drizzle",
    schema: "./src/drizzle/schema.ts",
    dbCredentials: {
        url: process.env.DATABASE_URL!,
    },
    verbose: true,
    strict: true,
});

[andrews1022]

so, i did come up with a solution to my problem, but i will advise that it isn't a great one.

what i ended up doing was dropping all of my tables in supabase, and re-ran the generate and push commands, and i haven't had any issues since. my guess is that there was a discrepancy between my drizzle schema definitions and the actual structure of the tables in supabase.

i certainly understand that dropping all of my tables isn't the ideal solution. to be honest, i am surprised it worked as well. the reason why did it is i thought perhaps a fresh start would help, and since this app is still in development, and i am the only user, i thought it was ok to do. if it should occur again, especially once deployed, as well as if i should get a regular user base (which i'm hoping to get), then i will absolutely look into alternative solutions.

[uwumarogie]

I need a solution without dropping the tables.

[tomber]

I managed to reproduce the error and resolution by adding this configuration and changing the port. My config:

import 'dotenv/config'
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './supabase/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
    database: 'postgres',
    // port: 6543, // Gives error: TypeError: Cannot read properties of undefined (reading 'toLowerCase')
    port: 5432, // This works
    host: process.env.DATABASE_HOST!,
    user: process.env.DATABASE_USER!,
    password: process.env.DATABASE_PASSWORD!,
  },
})

[BeNikk]

Changing the port did work for me, just make sure you are changing it in the DB url and everywhere else as well

[glebshkut]

Is there a solution for this if I'm not using Docker?

import { config } from 'dotenv';
import { defineConfig } from 'drizzle-kit';
config({ path: '.env' });
export default defineConfig({
  schema: './db/schema.ts',
  out: './utils/supabase/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
    port: 5432, // doesn't help
  },
});

[glebshkut]

actually changing the port in DATABASE_URL inside .env helped

UPD: i don't think that works long term