DT
Drizzle Team•2mo ago
Maastonakki

SQLite self referencing table

Hi! I'm trying to achieve self referencing category structure in my application. Found this article from wiki: https://orm.drizzle.team/docs/indexes-constraints#foreign-key 
import { WithNumericId } from "@/lib/util-types";
import { relations } from "drizzle-orm";
import { foreignKey, int, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const categories = sqliteTable(
  "categories",
  {
    id: int().primaryKey({ autoIncrement: true }),
    name: text().notNull(),
    parentId: int(),
  },
  (t) => [
    foreignKey({
      columns: [t.parentId],
      foreignColumns: [t.id],
      name: "parentId_fk",
    }),
  ],
);

export const categoriesRelations = relations(categories, ({ many, one }) => ({
  parent: one(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: "subCategories",
  }),
  children: many(categories, {
    relationName: "parentId_fk",
  }),
}));
import { WithNumericId } from "@/lib/util-types";
import { relations } from "drizzle-orm";
import { foreignKey, int, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const categories = sqliteTable(
  "categories",
  {
    id: int().primaryKey({ autoIncrement: true }),
    name: text().notNull(),
    parentId: int(),
  },
  (t) => [
    foreignKey({
      columns: [t.parentId],
      foreignColumns: [t.id],
      name: "parentId_fk",
    }),
  ],
);

export const categoriesRelations = relations(categories, ({ many, one }) => ({
  parent: one(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: "subCategories",
  }),
  children: many(categories, {
    relationName: "parentId_fk",
  }),
}));
I'm getting error There is not enough information to infer relation "categories.children" If I name both relations to same, I'm able to get main categories, but not children with 
const categoriesFromDb = await db.query.categories.findMany({
    with: {
      children: true,
    },
    where: isNull(categories.parentId),
  });
const categoriesFromDb = await db.query.categories.findMany({
    with: {
      children: true,
    },
    where: isNull(categories.parentId),
  });
Drizzle ORM - Indexes & Constraints
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
1 Reply
focused_morning
focused_morning•5w ago
did you ever get an answer to this?

Did you find this page helpful?