SQLite self referencing table

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",
  }),
}));

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),
  });