DT
Drizzle TeamJustKira

Circular Reference Error when Using Self-Reference in Table Definition with Drizzle-ORM

I encountered an error while defining a PostgreSQL table using Drizzle-ORM. The issue arises when attempting to create a self-referencing array column that references the primary key of its own table. The error message suggests that there is an issue with type inference possibly due to the self-reference. 
import { createId } from "@paralleldrive/cuid2";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { z } from "zod";
import {
  pgTable,
  varchar,
  timestamp,
  jsonb,
  integer,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

const course = pgTable("course", {
  id: varchar("id")
    .primaryKey()
    .$defaultFn(() => createId()),
  name: varchar("name").unique().notNull(),
  description: varchar("description").notNull(),
  image_url: varchar("image_url"),
  content_object: jsonb("content_object"),
  version: integer("version").default(1).notNull(),
  dependencies_courses: varchar("dependencies_courses").array().references(() => course.id),
  
  created_at: timestamp("created_at", {
    precision: 3,
  })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
  updated_at: timestamp("updated_at")
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .$onUpdateFn(() => new Date())
    .notNull(),
});

const insertCourseSchema = createInsertSchema(course);
interface NewCourse extends z.infer<typeof insertCourseSchema> {}

const selectCourseSchema = createSelectSchema(course);

interface Course extends z.infer<typeof selectCourseSchema> {}

export { course, insertCourseSchema, selectCourseSchema };

export type { NewCourse, Course };
import { createId } from "@paralleldrive/cuid2";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { z } from "zod";
import {
  pgTable,
  varchar,
  timestamp,
  jsonb,
  integer,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

const course = pgTable("course", {
  id: varchar("id")
    .primaryKey()
    .$defaultFn(() => createId()),
  name: varchar("name").unique().notNull(),
  description: varchar("description").notNull(),
  image_url: varchar("image_url"),
  content_object: jsonb("content_object"),
  version: integer("version").default(1).notNull(),
  dependencies_courses: varchar("dependencies_courses").array().references(() => course.id),
  
  created_at: timestamp("created_at", {
    precision: 3,
  })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
  updated_at: timestamp("updated_at")
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .$onUpdateFn(() => new Date())
    .notNull(),
});

const insertCourseSchema = createInsertSchema(course);
interface NewCourse extends z.infer<typeof insertCourseSchema> {}

const selectCourseSchema = createSelectSchema(course);

interface Course extends z.infer<typeof selectCourseSchema> {}

export { course, insertCourseSchema, selectCourseSchema };

export type { NewCourse, Course };
'course' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.
'course' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.
Expected Behavior: I expect to define a self-referencing column (dependencies_courses) that can store an array of course IDs, referencing the id column of the same course table without causing a type inference issue.
J
JustKira•20d ago
import { serial, text, integer, foreignKey, pgTable, AnyPgColumn } from "drizzle-orm/pg-core";
export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  parentId: integer("parent_id").references((): AnyPgColumn => user.id)
});
// or
export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  parentId: integer("parent_id"),
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "custom_fk"
    }),
  };
});
import { serial, text, integer, foreignKey, pgTable, AnyPgColumn } from "drizzle-orm/pg-core";
export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  parentId: integer("parent_id").references((): AnyPgColumn => user.id)
});
// or
export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  parentId: integer("parent_id"),
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "custom_fk"
    }),
  };
});
found this on Drizzle docs https://orm.drizzle.team/docs/indexes-constraints which solved my problem
Drizzle ORM - Indexes & Constraints
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Want results from more Discord servers?
Add your server
More Posts
SQL many statements?Any ideas on how to debug this? ```Task migrations deno run -A src/lib/db/migrations.ts error: UncaQuerying a materialized view: relation does not existI define a materialized view as such: ``` export const usersMv = pgMaterializedView("users_mv").as( default to empty object for sqlite text field with JSON modeHow can I default a `mode: 'json'` text type to an empty object in sqlite? The following in a `sqliConverting a field from X type to Serial causes errorsI'm attempting to convert a field from type `text` to type `serial`. Here's the error I'm seeing: `