stephen Posts - Answer Overflow

stephen

Explore posts from servers

•Created by stephen on 4/15/2024 in #help

Query `with` returning no results?

Hi! Wondering if anyone can see what I'm doing wrong here... I've got a recipes table that had a relation with recipeTags which, in turn, is related to tags. I've got a query that's returning the correct recipes, but the recipeTags is empty: I'm looking at: https://orm.drizzle.team/docs/rqb#find-many

const foundRecipes = await db.query.recipes.findMany({
    ...rest,
    where: eq(recipes.submittedBy, userId),
    columns: {
      id: true,
      ...
    },
    with: { recipeTags: { with: { tags: true } } },
    orderBy: (recipes) => [desc(recipes.createdDate)],
  });

const foundRecipes = await db.query.recipes.findMany({
    ...rest,
    where: eq(recipes.submittedBy, userId),
    columns: {
      id: true,
      ...
    },
    with: { recipeTags: { with: { tags: true } } },
    orderBy: (recipes) => [desc(recipes.createdDate)],
  });

// schema.ts

export const recipes = sqliteTable('recipes', {
  id: text('id').$default(() => createId()).primaryKey(),
  ...
});

export const recipesRelations = relations(recipes, ({ one, many }) => ({
  recipeTags: many(recipeTags),
  ...
}));

export const recipeTags = sqliteTable('recipe_tags', {
  recipeId: text('recipe_id'),
  tagId: text('tag_id'),
  ...
}, (table) => ({
  pk: primaryKey({ columns: [table.recipeId, table.tagId] }),
}));

export const recipeTagsRelations = relations(recipeTags, ({ one }) => ({
  recipes: one(recipes, { fields: [recipeTags.recipeId], references: [recipes.id] }),
  tags: one(tags, { fields: [recipeTags.tagId], references: [tags.id] }),
}));

export const tags = sqliteTable('tags', {
  id: text('id').$default(() => createId()).primaryKey(),
  name: text('name').notNull(),
  isPrivate: integer('is_private', { mode: 'boolean' }).$default(() => false),
  userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
  createdDate: text('created_date').$default(() => toISO8601(new Date())),
  updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
});

export const tagsRelations = relations(tags, ({ one, many }) => ({
  recipeTags: many(recipeTags),
  menuTags: many(menuTags),
  menuCourseTags: many(menuCourseTags),
  user: one(users, { fields: [tags.userId], references: [users.id] }),
}));

// schema.ts

export const recipes = sqliteTable('recipes', {
  id: text('id').$default(() => createId()).primaryKey(),
  ...
});

export const recipesRelations = relations(recipes, ({ one, many }) => ({
  recipeTags: many(recipeTags),
  ...
}));

export const recipeTags = sqliteTable('recipe_tags', {
  recipeId: text('recipe_id'),
  tagId: text('tag_id'),
  ...
}, (table) => ({
  pk: primaryKey({ columns: [table.recipeId, table.tagId] }),
}));

export const recipeTagsRelations = relations(recipeTags, ({ one }) => ({
  recipes: one(recipes, { fields: [recipeTags.recipeId], references: [recipes.id] }),
  tags: one(tags, { fields: [recipeTags.tagId], references: [tags.id] }),
}));

export const tags = sqliteTable('tags', {
  id: text('id').$default(() => createId()).primaryKey(),
  name: text('name').notNull(),
  isPrivate: integer('is_private', { mode: 'boolean' }).$default(() => false),
  userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
  createdDate: text('created_date').$default(() => toISO8601(new Date())),
  updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
});

export const tagsRelations = relations(tags, ({ one, many }) => ({
  recipeTags: many(recipeTags),
  menuTags: many(menuTags),
  menuCourseTags: many(menuCourseTags),
  user: one(users, { fields: [tags.userId], references: [users.id] }),
}));

5 replies

DTDrizzle Team

•Created by stephen on 3/21/2024 in #help

Schema is "wrong" resulting in "foreign key mismatch" error

I generated my sqlite schema using drizzle but now that I'm trying to use it, I'm getting a foreign key error. Here's where it seems to error: I added

export const menuRecipes = sqliteTable('menu_recipes', {
  id: text('id').$default(() => createId()).primaryKey(),
  menuId: text('menu_id').references(() => menus.id, { onDelete: "cascade" }).notNull(),
  recipeId: text('recipe_id').references(() => recipes.id, { onDelete: "cascade" }).notNull(),
  addedBy: text('added_by').references(() => users.id),
  createdDate: text('created_date').$default(() => toISO8601(new Date())),
  updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
  menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onUpdate: "cascade" }),
});

export const menuRecipesRelations = relations(menuRecipes, ({ one }) => ({
  menu: one(menus, { fields: [menuRecipes.menuId], references: [menus.id] }),
  recipe: one(recipes, { fields: [menuRecipes.recipeId], references: [recipes.id] }),
  user: one(users, { fields: [menuRecipes.addedBy], references: [users.id] }),
  menuCourse: one(menuCourses, { fields: [menuRecipes.menuCourseId], references: [menuCourses.id] }),
}));


export const menuCourseRecipes = sqliteTable('menu_course_recipes', {
  menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onDelete: "cascade" }).notNull(),
  menuRecipeId: text('recipe_id').references(() => menuRecipes.id, { onDelete: "cascade" }).notNull(),
}, (t) => ({
  pk: primaryKey({ columns: [t.menuCourseId, t.menuRecipeId] }),
}));

export const menuCourseRecipesRelations = relations(menuCourseRecipes, ({ one }) => ({
  menuCourses: one(menuCourses, {
    fields: [menuCourseRecipes.menuCourseId],
    references: [menuCourses.id],
  }),
  menuRecipes: one(menuRecipes, {
    fields: [menuCourseRecipes.menuRecipeId],
    references: [menuRecipes.id],
  }),
}));

export const menuRecipes = sqliteTable('menu_recipes', {
  id: text('id').$default(() => createId()).primaryKey(),
  menuId: text('menu_id').references(() => menus.id, { onDelete: "cascade" }).notNull(),
  recipeId: text('recipe_id').references(() => recipes.id, { onDelete: "cascade" }).notNull(),
  addedBy: text('added_by').references(() => users.id),
  createdDate: text('created_date').$default(() => toISO8601(new Date())),
  updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
  menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onUpdate: "cascade" }),
});

export const menuRecipesRelations = relations(menuRecipes, ({ one }) => ({
  menu: one(menus, { fields: [menuRecipes.menuId], references: [menus.id] }),
  recipe: one(recipes, { fields: [menuRecipes.recipeId], references: [recipes.id] }),
  user: one(users, { fields: [menuRecipes.addedBy], references: [users.id] }),
  menuCourse: one(menuCourses, { fields: [menuRecipes.menuCourseId], references: [menuCourses.id] }),
}));


export const menuCourseRecipes = sqliteTable('menu_course_recipes', {
  menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onDelete: "cascade" }).notNull(),
  menuRecipeId: text('recipe_id').references(() => menuRecipes.id, { onDelete: "cascade" }).notNull(),
}, (t) => ({
  pk: primaryKey({ columns: [t.menuCourseId, t.menuRecipeId] }),
}));

export const menuCourseRecipesRelations = relations(menuCourseRecipes, ({ one }) => ({
  menuCourses: one(menuCourses, {
    fields: [menuCourseRecipes.menuCourseId],
    references: [menuCourses.id],
  }),
  menuRecipes: one(menuRecipes, {
    fields: [menuCourseRecipes.menuRecipeId],
    references: [menuRecipes.id],
  }),
}));

4 replies

DTDrizzle Team

•Created by stephen on 3/15/2024 in #help

Studio: Not Enough Info to Infer Relation

https://www.answeroverflow.com/m/1180072254214766632 Running into this issue:

bun run drizzle-kit studio
Error: There is not enough information to infer relation "__public__.auditLogs.users"

bun run drizzle-kit studio
Error: There is not enough information to infer relation "__public__.auditLogs.users"

export const users = sqliteTable('users', {
  id: text('id').$default(() => createId()).primaryKey(),
  username: text('username').unique().notNull(),
  email: text('email'),
  //...
});

export const usersRelations = relations(users, ({ one, many }) => ({
  auditLogs: many(auditLogs),
  //...
}));

export const auditLogs = sqliteTable('audit_logs', {
  userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
  action: text('action'),
  timeStamp: text('time_stamp').$default(() => toISO8601(new Date())),
}, (table) => ({
  unq: unique().on(table.userId, table.action, table.timeStamp),
}));

export const auditLogsRelations = relations(auditLogs, ({ one }) => ({
  users: one(users),
}));

export const users = sqliteTable('users', {
  id: text('id').$default(() => createId()).primaryKey(),
  username: text('username').unique().notNull(),
  email: text('email'),
  //...
});

export const usersRelations = relations(users, ({ one, many }) => ({
  auditLogs: many(auditLogs),
  //...
}));

export const auditLogs = sqliteTable('audit_logs', {
  userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
  action: text('action'),
  timeStamp: text('time_stamp').$default(() => toISO8601(new Date())),
}, (table) => ({
  unq: unique().on(table.userId, table.action, table.timeStamp),
}));

export const auditLogsRelations = relations(auditLogs, ({ one }) => ({
  users: one(users),
}));

Any ideas? Based on the stack overflow, the suggestion is that the relationship isn't on both sides, but it seems like I do in this case, no?

11 replies

DTDrizzle Team

•Created by stephen on 3/13/2024 in #help

Creating migrations with Enums present (postgres)

Hi! I'm new to drizzle but am trying to create migrations and running into troubles with the auto generation of migration files when an enum is present. For example:

const userStatusEnum = pgEnum('statusEnum', ["pending", "active", "archived"]);
export const users = pgTable('users', {
  id: text('id').$default(() => createId()).primaryKey(),
  username: text('username').unique().notNull(),
  status: userStatusEnum('status').$default(() => "pending"),
});

const userStatusEnum = pgEnum('statusEnum', ["pending", "active", "archived"]);
export const users = pgTable('users', {
  id: text('id').$default(() => createId()).primaryKey(),
  username: text('username').unique().notNull(),
  status: userStatusEnum('status').$default(() => "pending"),
});

When I generate a migration (e.g., npx drizzle-kit generate:pg), I get something like:

-- 0001_awesome_venus.sql
CREATE TABLE IF NOT EXISTS "users" (
  "id" text PRIMARY KEY NOT NULL,
  "username" text NOT NULL,
  "status" "statusEnum",
);

-- 0001_awesome_venus.sql
CREATE TABLE IF NOT EXISTS "users" (
  "id" text PRIMARY KEY NOT NULL,
  "username" text NOT NULL,
  "status" "statusEnum",
);

This fails however as "statusEnum" is not defined which means i need to make the following (manual) modifications to my migration file...

+ DROP TYPE IF EXISTS statusEnum;
+ CREATE TYPE statusEnum AS ENUM ('pending', 'active', 'archived');

CREATE TABLE IF NOT EXISTS "users" (
  "id" text PRIMARY KEY NOT NULL,
  "username" text NOT NULL,
- "status" "statusEnum",
+ "status" statusEnum DEFAULT 'pending',
);

+ DROP TYPE IF EXISTS statusEnum;
+ CREATE TYPE statusEnum AS ENUM ('pending', 'active', 'archived');

CREATE TABLE IF NOT EXISTS "users" (
  "id" text PRIMARY KEY NOT NULL,
  "username" text NOT NULL,
- "status" "statusEnum",
+ "status" statusEnum DEFAULT 'pending',
);

Is this anticipated? or is there a way to configure it so that these are done automatically?

3 replies

Gaming

Programming